HotXLS Charts and Images: Excel Drawings in Delphi

Anything that floats over a worksheet grid (a chart, a logo, a stamp, a callout box) is a drawing object, and a drawing object is defined by two things: what it is, and where it is anchored. The anchor is the part people get wrong. A chart does not live in a cell; it sits in a rectangle pinned to a span of rows and columns, and the data it plots is a separate set of A1 references that the anchor knows nothing about. Move the frame and the plot stays put. Insert rows under it and the frame slides down with them. Keeping those two coordinate systems straight is most of what makes drawing code behave

HotXLS is a native Object Pascal library that reads and writes XLS and XLSX without Excel automation, and it carries two separate drawing models because the two file formats store drawings differently. The BIFF8 .xls format keeps charts on their own dedicated sheets and floating shapes in an OfficeArt stream attached to the worksheet. The OOXML .xlsx format can embed a chart inside the grid, anchored to a cell rectangle, alongside the same kind of floating pictures and shapes. The object model mirrors that split, and the failures worth writing about all come from applying one format's rules to the other

Which container can hold what

The choice of container has to come before any chart code, because the available object types differ between the two:

XLS (BIFF8): charts live on dedicated chart sheets created through AddChartSheet on the Sheets collection. Pictures, text boxes, rectangles, ovals, and lines are OfficeArt shapes managed through the worksheet's Shapes collection. There is no API for embedding a chart inside a normal worksheet grid
XLSX (OOXML): charts can be embedded directly in a worksheet with TXLSXWorksheet.AddChart, anchored to a cell rectangle, or placed on a dedicated chart sheet with TXLSXWorkbook.AddChartSheet. Images go in with AddImage or AddImageFromFile, and floating labels with AddTextBox

So a requirement phrased as "a dashboard sheet with the chart next to the numbers" is really a requirement for .xlsx. You can approximate it in .xls only by pushing the chart onto its own sheet, which changes how the user navigates the file and changes how your code has to behave. The sheet returned by the XLS-side AddChartSheet is a chart substream, not a grid: writing to it with Cells.Item produces an inconsistent drawing stream that generates without error and that Excel then discards on open. The chart simply vanishes, and nothing in the build log says why. Treat the returned sheet as chart-only and the whole class of "missing chart" reports goes away

Embedding a chart in an XLSX worksheet

The XLSX path is the one with room to maneuver, and it is where the two coordinate systems from the opening become concrete. The anchor rectangle passed to AddChart is expressed in worksheet rows and columns and fixes where the chart frame sits. The series data is expressed as absolute A1 references that include the sheet name. They are independent: you can move the frame to the far side of the sheet and it still plots the same cells

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;

    // Frame anchored to rows 6..22, columns 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;

The argument that bites is the range string handed to AddSeries. It is a literal, captured at the moment of the call, and it has no idea that you might append twenty more rows of data afterward. Build it from a row count you computed after the data was written, never before. Scatter and bubble charts overload the same two arguments with different meanings: the categories range now supplies the X values and the values range supplies Y, and the bubble radius comes from a third reference set through BubbleSizeRange on the returned TXLSXChartSeries. Read the call as "X, Y, size" rather than "categories, values" once you leave the column-and-bar family

TXLSXChartType spans column, bar, line, pie, area, doughnut, scatter, bubble, and radar plots, which covers the everyday reporting repertoire. For a full-page chart with no surrounding grid, Book.AddChartSheet returns a sheet whose IsChartSheet property is true. It is the .xlsx counterpart of the legacy chart sheet and carries the same expectation: do not write cell content to it

Images go in as bytes, and they are sized in EMUs

There are two overloads for inserting a picture, and mixing them up is the image bug that shows up most in code review. AddImage(ARow, ACol, AData, AFormat) wants the already-encoded picture bytes in AData: the raw contents of a PNG, JPEG, GIF, or BMP. Pass it a file path and you have stored a forty-byte string that no viewer can decode, which is exactly the broken-image-icon report you do not want to debug after deployment. When the source is a file on disk, call AddImageFromFile instead and let the library read the bytes and classify the format for you

Then comes the sizing. DrawingML does not measure in pixels; it measures in English Metric Units, where 914400 EMU make an inch and, at 96 DPI, 9525 EMU make a pixel. The TXLSXImage object exposes WidthEMU and HeightEMU, so a logo meant to render 180 by 60 pixels needs 1714500 by 571500 EMU. Put that conversion in a named constant and compute against it. Magic numbers like 1714500 scattered through the code are unreadable and quietly wrong the first time someone changes the target DPI. The anchor row and column, incidentally, are 1-based, matching the rest of the cell API rather than the 0-based EMU math

Chart sheets and shapes in legacy XLS files

On the BIFF8 side the richer AddChartSheet overload takes the chart type, the axis titles, and an open array of TXLSChartSeriesInfo records, where each record holds a name and a categories and values range as strings. Floating shapes are a separate matter: they go on the data worksheet itself, through its Shapes collection, not on the chart sheet

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // interface-counted: do not 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 is a chart substream: never call cell methods on it

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

Two lifetime details matter here, and they pull in opposite directions. TXLSWorkbook is held through the IXLSWorkbook interface and is reference-counted, so calling Free on it yourself triggers a double release. TXLSXWorkbook from the previous sections is a plain object and must be freed in a try..finally. The same code reviewer who flags a missing Free on the XLSX side has to flag a present one on the XLS side, which is a genuine trip hazard when you work in both formats in the same unit. The shape helpers themselves are uniform: AddRectangle, AddOval, and AddLine, with DeleteInRange to clear a region of drawings, all anchor by row and column pairs, so a template that inserts rows above them shifts them along with the grid

One more property earns its keep on legacy files. TXLSPicture.TransparentColor masks a chosen background color out of a bitmap, which is how you drop a non-rectangular stamp (an "Approved" seal, a watermark) over the grid in a format whose BIFF rendering never learned PNG alpha. Set the color the stamp was authored against and the surrounding rectangle disappears

Theme colors do not survive a BIFF8 round-trip

OOXML drawing fills can point at a theme color slot, which is why re-coloring an entire .xlsx by swapping its theme is cheap. BIFF8 drawing records have no such slot. When HotXLS applies a theme color to an XLS drawing it resolves the color to a literal RGB value and stores that; the theme index it came from is gone the instant the file is written, and reopening cannot recover it. This catches white-label reporting tools in particular, the kind that re-brand the same generated document for many customers. Keep the theme-to-RGB mapping in your own configuration and reapply it every time you generate, rather than expecting to read it back out of a saved .xls

A related decision shows up on the performance side. The XLS facade can be told to skip parsing the drawing layer entirely when all you want from a large legacy file is its cell data, by setting _DisableGraphics to true, and that shaves real time off bulk reads. The catch is permanent: a workbook opened that way has no OfficeArt stream in memory, so saving it writes the drawings out of existence. Reserve the flag for read-only analytics jobs. The wider performance picture is in our notes on large-workbook performance in HotXLS

Keeping anchors stable while the grid changes

Reports rarely stay the size they were generated at, and this is where the anchor model from the opening pays off. The XLSX facade's structural operations (InsertRows, DeleteRows, and the column equivalents) move the dependent layers along with the cells. Merged regions, hyperlinks, comments, frozen panes, filter ranges, conditional formats, validations, tables, defined names, and, for this topic, image and chart anchors all travel together. A logo anchored at row 1 stays at the top when ten rows go in below it. A chart frame anchored under the data block slides down as the block grows. The one thing that does not get rewritten is any range string you captured as a literal before the insert happened, since it is just text the library has no reason to revisit. That fixes the safe ordering for a template fill: write and reshape the data first, and create charts and place images as the final pass, with every range string derived from the row counts you have after the inserts, not before

Two smaller tools finish the placement kit. TXLSTextBox.SetArea on the XLS side re-anchors an existing text box or auto shape onto a new cell rectangle, which beats deleting and recreating it when a footer block shifts. And the bitmap overload of AddPicture takes a live TBitmap with an optional transparency flag, so anything your own VCL code can draw (a gauge, a sparkline strip, a chart type the native list does not offer) can be stamped straight into the sheet without writing a temporary file first

Charts and images are almost always the finishing layer on an already-structured report, which is why the groundwork decides whether they land cleanly. Filling the data a chart will reference is covered in template-driven report generation, and keeping the grid stable underneath your anchors is the subject of merged cells and layout control. Full class and method documentation lives on the HotXLS Component product page