HotXLS: charts, images, and drawing objects in Delphi

Cualquier cosa que flote sobre una cuadrícula de hoja de cálculo (un gráfico, un logotipo, un sello, un cuadro de llamada) es un objeto de dibujo, y un objeto de dibujo se define por dos cosas: qué es, y dónde está anclado. El ancla es la parte que la gente hace mal. Un gráfico no vive en una celda; se sitúa en un rectángulo fijado a un tramo de filas y columnas, y los datos que representa son un conjunto separado de referencias A1 de las que el ancla no sabe nada. Mueva el marco y el gráfico se queda donde estaba. Inserte filas debajo y el marco se desliza hacia abajo con ellas. Mantener esos dos sistemas de coordenadas separados es la mayor parte de lo que hace que el código de dibujo se comporte bien

HotXLS es una biblioteca nativa de Object Pascal que lee y escribe XLS y XLSX sin automatización de Excel, y lleva dos modelos de dibujo separados porque los dos formatos de archivo almacenan los dibujos de forma distinta. El formato BIFF8 .xls mantiene los gráficos en sus propias hojas dedicadas y las formas flotantes en un stream de OfficeArt adjunto a la hoja de cálculo. El formato OOXML .xlsx puede incrustar un gráfico dentro de la cuadrícula, anclado a un rectángulo de celdas, junto al mismo tipo de imágenes y formas flotantes. El modelo de objetos refleja esa división, y los fallos que merece la pena documentar vienen todos de aplicar las reglas de un formato al otro

Qué contenedor puede llevar qué

La elección del contenedor tiene que llegar antes de cualquier código de gráfico, porque los tipos de objeto disponibles difieren entre los dos:

• XLS (BIFF8): los gráficos viven en hojas de gráfico dedicadas creadas mediante AddChartSheet en la colección Sheets. Las imágenes, los cuadros de texto, los rectángulos, los óvalos, y las líneas son formas OfficeArt gestionadas a través de la colección Shapes de la hoja. No hay ninguna API para incrustar un gráfico dentro de una cuadrícula de hoja normal
• XLSX (OOXML): los gráficos pueden incrustarse directamente en una hoja con TXLSXWorksheet.AddChart, anclados a un rectángulo de celdas, o colocarse en una hoja de gráfico dedicada con TXLSXWorkbook.AddChartSheet. Las imágenes entran con AddImage o AddImageFromFile, y las etiquetas flotantes con AddTextBox

Así que un requisito expresado como "una hoja de panel con el gráfico al lado de los números" es en realidad un requisito para .xlsx. Solo puede aproximarlo en .xls empujando el gráfico a su propia hoja, lo que cambia cómo navega el usuario por el archivo y cambia cómo tiene que comportarse su código. La hoja que devuelve el AddChartSheet del lado XLS es un subflujo de gráfico, no una cuadrícula: escribir en ella con Cells.Item produce un stream de dibujo inconsistente que se genera sin error y que Excel luego descarta al abrir. El gráfico simplemente desaparece, y nada en el registro de compilación dice por qué. Trate la hoja devuelta como exclusiva de gráfico y toda la clase de informes de "gráfico ausente" desaparece

Incrustar un gráfico en una hoja XLSX

La ruta XLSX es la que tiene margen de maniobra, y es donde los dos sistemas de coordenadas del principio se vuelven concretos. El rectángulo de ancla que se pasa a AddChart se expresa en filas y columnas de hoja y fija dónde se sitúa el marco del gráfico. Los datos de la serie se expresan como referencias A1 absolutas que incluyen el nombre de la hoja. Son independientes: puede mover el marco al otro extremo de la hoja y seguirá representando las mismas celdas

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Chart: TXLSXChart;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Sheet.Cells[1, 1].Value := 'Region';
    Sheet.Cells[1, 2].Value := 'Revenue';
    Sheet.Cells[2, 1].Value := 'East';
    Sheet.Cells[2, 2].Value := 1184350;
    Sheet.Cells[3, 1].Value := 'Central';
    Sheet.Cells[3, 2].Value := 902210;
    Sheet.Cells[4, 1].Value := 'West';
    Sheet.Cells[4, 2].Value := 1010675;

    // Marco anclado a las filas 6..22, columnas 1..8
    Chart := Sheet.AddChart(xlsxChartColumn, 'Revenue by Region', 6, 1, 22, 8);
    Chart.AddSeries('Revenue', 'Sales!$A$2:$A$4', 'Sales!$B$2:$B$4');
    Chart.ValueAxisTitle := 'USD';

    Sheet.AddImageFromFile(1, 5, 'logo.png');
    Book.SaveAs('dashboard.xlsx');
  finally
    Book.Free;
  end;
end;

El argumento que muerde es la cadena de rango entregada a AddSeries. Es un literal, capturado en el momento de la llamada, y no tiene ni idea de que después podría añadir veinte filas más de datos. Constrúyalo a partir de un recuento de filas que calculó después de escribir los datos, nunca antes. Los gráficos de dispersión y de burbujas sobrecargan los mismos dos argumentos con significados distintos: el rango de categorías ahora suministra los valores X y el rango de valores suministra Y, y el radio de la burbuja viene de una tercera referencia fijada mediante BubbleSizeRange en el TXLSXChartSeries devuelto. Lea la llamada como "X, Y, tamaño" en lugar de "categorías, valores" en cuanto salga de la familia de columnas y barras

TXLSXChartType abarca los gráficos de columnas, barras, líneas, tarta, área, anillos, dispersión, burbujas, y radar, lo que cubre el repertorio habitual de informes. Para un gráfico a página completa sin cuadrícula alrededor, Book.AddChartSheet devuelve una hoja cuya propiedad IsChartSheet es true. Es la contrapartida en .xlsx de la hoja de gráfico heredada y lleva la misma expectativa: no escriba contenido de celda en ella

Las imágenes entran como bytes, y se dimensionan en EMU

Hay dos sobrecargas para insertar una imagen, y confundirlas es el error de imagen que más aparece en la revisión de código. AddImage(ARow, ACol, AData, AFormat) quiere los bytes de imagen ya codificados en AData: el contenido en bruto de un PNG, JPEG, GIF, o BMP. Páselo una ruta de archivo y habrá almacenado una cadena de cuarenta bytes que ningún visor puede decodificar, que es exactamente el informe de icono de imagen rota que no quiere depurar después del despliegue. Cuando el origen es un archivo en disco, llame en su lugar a AddImageFromFile y deje que la biblioteca lea los bytes y clasifique el formato por usted

Luego viene el dimensionado. DrawingML no mide en píxeles; mide en English Metric Units, donde 914400 EMU hacen una pulgada y, a 96 DPI, 9525 EMU hacen un píxel. El objeto TXLSXImage expone WidthEMU y HeightEMU, así que un logotipo pensado para renderizar a 180 por 60 píxeles necesita 1714500 por 571500 EMU. Ponga esa conversión en una constante con nombre y calcule contra ella. Los números mágicos como 1714500 dispersos por el código son ilegibles y silenciosamente incorrectos la primera vez que alguien cambie el DPI de destino. La fila y la columna de ancla, por cierto, son de base 1, coincidiendo con el resto de la API de celdas en lugar de con la aritmética EMU de base 0

Hojas de gráfico y formas en archivos XLS heredados

En el lado BIFF8, la sobrecarga más rica de AddChartSheet toma el tipo de gráfico, los títulos de eje, y un array abierto de registros TXLSChartSeriesInfo, donde cada registro lleva un nombre y un rango de categorías y de valores como cadenas. Las formas flotantes son un asunto aparte: van en la propia hoja de datos, a través de su colección Shapes, no en la hoja de gráfico

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // con recuento de referencias: no llamar a Free
  Data := Book.Sheets.Add;
  Data.Name := 'Data';
  Data.Cells.Item[1, 1].Value := 'Month';
  Data.Cells.Item[1, 2].Value := 'Units';
  Data.Cells.Item[2, 1].Value := 'Apr';
  Data.Cells.Item[2, 2].Value := 1530;
  Data.Cells.Item[3, 1].Value := 'May';
  Data.Cells.Item[3, 2].Value := 1721;

  Series[0].Name := 'Units';
  Series[0].Categories := 'Data!$A$2:$A$3';
  Series[0].Values := 'Data!$B$2:$B$3';
  Trend := Book.Sheets.AddChartSheet('Trend', xlsChartTypeLine,
    'Units sold', 'Month', 'Units', Series);
  // Trend es un subflujo de gráfico: nunca llame a métodos de celda sobre él

  Data.Shapes.AddTextBox('Source: ERP nightly export', 6, 1, 8, 4);
  Data.Shapes.AddPicture('approved-stamp.bmp');
  Book.SaveAs('trend.xls');
end;

Dos detalles de ciclo de vida importan aquí, y tiran en direcciones opuestas. TXLSWorkbook se mantiene a través de la interfaz IXLSWorkbook y tiene recuento de referencias, así que llamar a Free sobre él usted mismo dispara una liberación doble. TXLSXWorkbook de las secciones anteriores es un objeto sencillo y debe liberarse en un try..finally. El mismo revisor de código que señala un Free ausente en el lado XLSX tiene que señalar uno presente en el lado XLS, lo cual es un auténtico riesgo de tropiezo cuando se trabaja con ambos formatos en la misma unidad. Los propios helpers de forma son uniformes: AddRectangle, AddOval, y AddLine, con DeleteInRange para borrar una región de dibujos, todos anclan mediante pares de fila y columna, así que una plantilla que inserta filas encima de ellos los desplaza junto con la cuadrícula

Una propiedad más se gana su lugar en los archivos heredados. TXLSPicture.TransparentColor enmascara un color de fondo elegido fuera de un mapa de bits, que es cómo se coloca un sello no rectangular (un sello "Approved", una marca de agua) sobre la cuadrícula en un formato cuyo renderizado BIFF nunca aprendió el canal alfa de PNG. Fije el color contra el que se creó el sello y el rectángulo circundante desaparece

Los colores de tema no sobreviven a un viaje de ida y vuelta por BIFF8

Los rellenos de dibujo de OOXML pueden apuntar a una ranura de color de tema, que es por lo que recolorear un .xlsx entero cambiando su tema es barato. Los registros de dibujo de BIFF8 no tienen tal ranura. Cuando HotXLS aplica un color de tema a un dibujo XLS resuelve el color a un valor RGB literal y almacena eso; el índice de tema del que vino desaparece en el instante en que se escribe el archivo, y volver a abrirlo no puede recuperarlo. Esto atrapa en particular a las herramientas de informes de marca blanca, del tipo que rebautiza el mismo documento generado para muchos clientes. Mantenga el mapeo de tema a RGB en su propia configuración y vuelva a aplicarlo cada vez que genere, en lugar de esperar leerlo de vuelta de un .xls guardado

Una decisión relacionada aparece en el lado del rendimiento. Se le puede decir a la fachada XLS que se salte por completo el análisis de la capa de dibujo cuando todo lo que quiere de un archivo heredado grande son sus datos de celda, fijando _DisableGraphics a true, y eso recorta tiempo real de las lecturas masivas. La trampa es permanente: un libro de trabajo abierto de esa forma no tiene ningún stream de OfficeArt en memoria, así que guardarlo escribe los dibujos fuera de existencia. Reserve el indicador para jobs de analítica de solo lectura. El panorama de rendimiento más amplio está en nuestras notas sobre rendimiento con libros de trabajo grandes en HotXLS

Mantener los anclajes estables mientras la cuadrícula cambia

Los informes rara vez se quedan en el tamaño con el que se generaron, y aquí es donde el modelo de ancla del principio da sus frutos. Las operaciones estructurales de la fachada XLSX (InsertRows, DeleteRows, y los equivalentes de columna) mueven las capas dependientes junto con las celdas. Las regiones combinadas, los hipervínculos, los comentarios, los paneles inmovilizados, los rangos de filtro, los formatos condicionales, las validaciones, las tablas, los nombres definidos, y, para este tema, los anclajes de imagen y de gráfico, todos viajan juntos. Un logotipo anclado en la fila 1 permanece arriba cuando se insertan diez filas debajo. Un marco de gráfico anclado bajo el bloque de datos se desliza hacia abajo a medida que el bloque crece. Lo único que no se reescribe es cualquier cadena de rango que capturó como literal antes de que ocurriera la inserción, ya que es simplemente texto que la biblioteca no tiene motivo para revisar. Eso fija el orden seguro para el relleno de una plantilla: escriba y remodele los datos primero, y cree los gráficos y coloque las imágenes como el último paso, con cada cadena de rango derivada de los recuentos de fila que tiene después de las inserciones, no antes

Dos herramientas más pequeñas completan el kit de colocación. TXLSTextBox.SetArea en el lado XLS vuelve a anclar un cuadro de texto o autoforma existente sobre un nuevo rectángulo de celdas, lo que gana a eliminarlo y recrearlo cuando un bloque de pie se desplaza. Y la sobrecarga de mapa de bits de AddPicture toma un TBitmap vivo con un indicador de transparencia opcional, así que cualquier cosa que su propio código VCL pueda dibujar (un indicador, una tira de sparkline, un tipo de gráfico que la lista nativa no ofrece) puede estamparse directamente en la hoja sin escribir antes un archivo temporal

Los gráficos y las imágenes son casi siempre la capa de acabado sobre un informe ya estructurado, razón por la cual el trabajo previo decide si aterrizan limpiamente. Rellenar los datos a los que hará referencia un gráfico se cubre en la generación de informes basada en plantilla, y mantener la cuadrícula estable bajo sus anclajes es el tema de celdas combinadas y control de diseño. La documentación completa de clases y métodos vive en la página de producto de HotXLS Delphi Component