HotXLS: database export to spreadsheet reports in Delphi

Convertir el resultado de una consulta en un informe de Excel son en realidad tres problemas bajo un mismo abrigo. Cada tipo de campo de Delphi tiene que aterrizar en una celda como el tipo de Excel correcto, la fila de cabecera tiene que leerse como un informe y no como un volcado de esquema, y los números, las fechas y el dinero tienen que llevar formatos que sobrevivan al viaje. Omita cualquiera de ellos y el archivo se sigue abriendo, sigue pareciendo plausible, y sigue fallando en el momento en que un usuario de finanzas selecciona una columna y espera una suma que nunca aparece. Los valores se escribieron como texto, Excel los trata como etiquetas, y nunca se lanzó ninguna excepción para avisarle

HotXLS es una biblioteca nativa de hoja de cálculo en Object Pascal que escribe archivos XLS y XLSX directamente desde Delphi y C++Builder, sin ninguna automatización de Excel de por medio. Ofrece dos rutas desde un TDataset hasta un libro de trabajo: el componente TDataToXLS listo para usar, y un bucle escrito a mano contra la API del libro de trabajo. No son intercambiables. El componente es un ciudadano VCL construido sobre la fachada XLS, de modo que la elección correcta depende de dónde se ejecute el código y de qué formato de archivo espere el consumidor. Lo que sigue son ambas rutas, el punto en el que el componente deja de ser la herramienta adecuada, y cómo mantener intactos los tipos de campo elija la que elija

Los tipos de campo son el verdadero contrato de exportación

Antes de cualquier llamada a la API, decida cómo aterriza cada tipo de campo de Delphi en una celda. Una celda que recibe una cadena de Delphi sigue siendo una cadena. HotXLS no adivina que '1,234.50' pretendía ser un número, y no debería hacerlo, porque el reanálisis dependiente de la configuración regional es exactamente lo que convierte una coma decimal alemana en un separador de miles en un servidor en inglés. El patrón fiable es asignar mediante los accesores tipados: AsFloat o AsCurrency para campos numéricos, AsDateTime para fechas de modo que la celda contenga un número de serie de fecha de Excel auténtico en lugar de una cadena con formato, y AsString solo para campos que sean realmente texto

El tratamiento de los valores nulos merece una decisión explícita y no un comportamiento por defecto. Convertir un valor de campo con VarToStr transforma un NULL de SQL en una cadena vacía, que es una celda de texto, mientras que omitir la asignación deja la celda verdaderamente vacía, que es lo que esperan AVERAGE, COUNT y los consumidores de tablas dinámicas. Para las columnas de dinero, decida antes de escribir el bucle si NULL significa cero o desconocido. Las dos opciones se muestran de forma idéntica en cuanto alguien da formato a la columna, y la diferencia cambia todos los agregados que se calculen más adelante

La ruta del componente: TDataToXLS en aplicaciones VCL

Para una aplicación VCL clásica con una consulta ya conectada a un módulo de datos, TDataToXLS es la ruta de una sola llamada. Recorre cualquier descendiente de TDataset, ya sea FireDAC, ADO, IBX o cualquier otro que implemente la interfaz abstracta de dataset, y produce una hoja de cálculo con estilo, con títulos de cabecera, fuentes, bordes, subtotales de grupo opcionales, y división automática en hojas para conjuntos de resultados grandes

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // cualquier descendiente de TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // títulos, no nombres de columna en bruto
    Exporter.GroupFields.Add('CustomerID');   // bloque de subtotal por cliente
    Exporter.RowsPerSheet := 50000;           // por debajo del límite de filas de BIFF8
    Exporter.VisibleFieldsOnly := True;             // respeta Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Dos propiedades cargan aquí con la mayor parte del peso de producción. HeaderSource := hsDisplayLabel escribe el DisplayLabel de cada campo en lugar del nombre de columna SQL en bruto, de modo que el libro de trabajo dice "Customer Name" en lugar de CUST_NM. RowsPerSheet existe porque el componente escribe BIFF8, cuya cuadrícula se detiene en 65.536 filas por 256 columnas; establecerlo en 50.000 divide un conjunto de resultados grande entre varias hojas antes de que el límite del formato lo trunque. El aspecto se gestiona mediante las propiedades HeaderFont, DetailFont, GroupColor y de estilo de borde, y el conjunto DisableFormat desactiva categorías enteras de formato cuando el consumidor quiere celdas sin formato. Para cualquier necesidad a medida, los eventos AfterCell y AfterRow le entregan el rango recién escrito para su posprocesamiento

Dónde se detiene el componente

Hay tres restricciones incorporadas en el diseño de TDataToXLS, y conocerlas de antemano evita un rediseño incómodo dos sprints más tarde

• Es un componente VCL en el sentido pleno. Su unidad importa Forms, Controls y Dialogs, de modo que enlazarlo en un job de consola o en un servicio de Windows arrastra la VCL al binario. Las unidades núcleo del libro de trabajo no tienen esa dependencia. Solo necesitan Windows, Classes, SysUtils y Variants, razón por la cual el código del lado servidor debería usar en su lugar el bucle que se muestra a continuación
• Está construido sobre la fachada XLS. El componente rellena un IXLSWorkbook y escribe .xls (BIFF8). No existe ninguna propiedad que lo cambie a salida OOXML
• Sus eventos hablan el dialecto XLS. El parámetro Cell: IXLSRange de AfterCell pertenece al modelo de objetos XLS, de modo que la personalización por celda escrita ahí es código de estilo XLS incluso si el archivo se convierte después a .xlsx

Producir .xlsx a partir de la salida del componente

Cuando el consumidor insiste en .xlsx pero la lógica de exportación ya reside en TDataToXLS, la función puente de la unidad lxXlsxExport convierte el libro de trabajo ya rellenado en una sola llamada:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// el componente expone el IXLSWorkbook que rellenó
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Trate el puente como un transportador de datos tabulares, no como un conversor de fidelidad completa. Copia valores, fórmulas, formatos numéricos, colores de relleno, atributos de fuente, anchos de columna y ajustes de vista. Deliberadamente no copia bordes, rangos combinados, comentarios, gráficos ni formatos condicionales. Para una cuadrícula plana de cabecera más filas eso es exactamente suficiente. Para un informe con estilo no lo es, y la solución honesta es generar el XLSX directamente en lugar de parchear el archivo convertido

El bucle escrito a mano para servicios y jobs por lotes

El código del lado servidor debería apuntar directamente a TXLSXWorkbook. Observe la diferencia de ciclo de vida entre las dos fachadas antes de copiar ningún ejemplo. El TXLSWorkbook del lado XLS se mantiene a través de una interfaz con recuento de referencias y no debe liberarse manualmente, mientras que TXLSXWorkbook es una clase sencilla que requiere try..finally Free. Mezclar las dos convenciones es una forma fiable de generar una fuga de memoria o un doble Free

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // transmite el XML de la hoja directamente al zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Las líneas que importan son las asignaciones tipadas y la comprobación IsNull. Las fechas llegan como números de serie de fecha, los importes llegan como dobles, y las fechas de pedido NULL permanecen verdaderamente vacías en lugar de convertirse en cadenas vacías. StreamingWrite := True cambia solo la ruta de guardado: el XML de la hoja de cálculo se transmite directamente al contenedor zip en lugar de ensamblarse primero como una única cadena grande, lo que aplana el pico de memoria en el momento de SaveAs para recuentos de filas de seis cifras. Todos los métodos de guardado también tienen una sobrecarga TStream, de modo que el libro de trabajo puede pasar directamente a una respuesta HTTP sin tocar el disco. El artículo sobre escritura en streaming y jobs por lotes recorre ese patrón de despliegue, y el artículo sobre rendimiento con libros de trabajo grandes cubre qué hacer cuando el número de filas sigue creciendo

Este bucle es también la ruta que escala entre hilos. Ambos motores son escritores nativos de Object Pascal, flujos de registros BIFF8 por un lado y zip OOXML más XML por el otro, de modo que ninguna parte de una exportación toca la automatización COM ni necesita una licencia de Excel en el servidor. Lo que eso le proporciona es paralelismo sin un cuello de botella de instancia única, siempre que cada hilo construya su propio libro de trabajo. Los objetos de libro de trabajo no son seguros para hilos en uso compartido, así que la regla es una instancia por exportación, nunca una compartida protegida por un bloqueo

Vale la pena conocer un límite antes de diseñar en torno a él. La cuadrícula de XLSX se detiene en 1.048.576 filas por 16.384 columnas, así que la división en hojas que RowsPerSheet gestiona en el lado XLS rara vez es necesaria aquí. Un libro de trabajo de un millón de filas tampoco es casi nunca lo que quiere un consumidor humano. Cuando el conjunto de resultados es genuinamente tan grande, un archivo delimitado suele ser el contrato mejor, y el artículo sobre exportación a CSV y TSV cubre los delimitadores, el comportamiento del BOM, y la advertencia sobre evaluación de fórmulas que se aplica ahí

Elegir un punto de partida

Si la exportación vive en una herramienta de escritorio VCL y la salida .xls es aceptable, empiece con TDataToXLS y su soporte de agrupación. Es el código más reducido, y el puente a través de SaveXLSWorkbookAsXLSX está ahí para cuando alguien pida .xlsx más adelante, siempre que acepte los límites de fidelidad ya descritos. Si el código se ejecuta sin supervisión, o el consumidor exige .xlsx desde el principio, escriba el bucle. Ambas rutas se distribuyen con proyectos de demostración funcionales y forman parte del paquete HotXLS Delphi Component