The support ticket said the chart was missing. A Delphi service generated a monthly .xls report, created a chart sheet for the revenue trend, and then tried to write a data caption onto that same sheet with cell calls. Nothing raised an exception during generation, but Excel silently discarded the inconsistent drawing stream on open. The root cause is a file-format detail that is easy to miss: in BIFF8, a chart sheet is a separate substream, not a worksheet with a chart floating on it, and cell-level operations against it are unsupported. HotXLS, a native Object Pascal library that reads and writes XLS and XLSX without Excel automation, exposes both drawing models — but they are genuinely different models, and most production incidents with charts and images come from applying the rules of one format to the other.
UK teams should align this hotxls charts images drawings delphi workflow with local governance, audit, and data quality requirements before production release
For UK environments, align the hotxls charts images drawings delphi implementation with local quality gates that include governance approval, versioned fixture baselines, and evidence retention for every publication candidate. Keep an explicit review log for accessibility, redaction policy, and data residency checks so your deployment audit is repeatable without changing output binaries
UK technical governance addendum
Two file formats, two drawing models
Before writing any chart code, decide which container you are producing, because the available object types differ:
- XLS (BIFF8): charts live on dedicated chart sheets created through
AddChartSheeton theSheetscollection. Pictures, text boxes, rectangles, ovals, and lines are OfficeArt shapes managed through the worksheet'sShapescollection. There is no API for embedding a chart within 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 withTXLSXWorkbook.AddChartSheet. Images go in withAddImageorAddImageFromFile, and floating labels withAddTextBox.
If the requirement is 'a dashboard sheet with the chart next to the numbers', that is an XLSX deliverable. Trying to satisfy it in .xls forces a separate chart sheet, which changes navigation for the user and changes your code: the sheet returned by the XLS-side AddChartSheet must be treated as chart-only and never written to with Cells.Item. That single rule would have prevented the incident above.
Embedding a chart in an XLSX worksheet
The XLSX path is the more flexible one, so it is worth getting the two coordinate systems straight. The anchor rectangle passed to AddChart is expressed in worksheet rows and columns and defines where the chart frame sits; the series data is expressed as absolute A1 references including the sheet name. The two are independent — moving the chart frame never changes what it plots.
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 line that goes wrong most often is AddSeries. The category and value arguments are literal range strings, so they do not grow when more rows are appended later in the run. Write the data first, track the final row count, and only then build the series reference from that count. For scatter and bubble charts the same call has shifted semantics: the categories range supplies the X values and the values range supplies Y, with bubble sizes set separately through BubbleSizeRange on the returned TXLSXChartSeries.
The available TXLSXChartType values cover column, bar, line, pie, area, doughnut, scatter, bubble, and radar plots. When the deliverable is a full-page chart, Book.AddChartSheet creates a sheet whose IsChartSheet property is true — the XLSX equivalent of the legacy chart sheet, with the same 'no grid content' expectation.
Images are bytes, and sizes are EMUs
The most common image bug in code review is passing a file path to the wrong overload. AddImage(ARow, ACol, AData, AFormat) takes the already-encoded picture bytes — the raw PNG, JPEG, GIF, or BMP data — in its AData parameter. Hand it a path string and you have inserted a forty-byte 'image' that no viewer can decode. When the source is a file on disk, use AddImageFromFile and let the library load and classify it.
Sizing trips people up next. DrawingML measures in English Metric Units: 914400 EMU per inch, which works out to 9525 EMU per pixel at 96 DPI. The TXLSXImage object exposes WidthEMU and HeightEMU, so a logo that should render at 180 by 60 pixels needs 1714500 by 571500 EMU. Do that arithmetic once in a helper constant instead of sprinkling magic numbers, and remember the anchor row and column are 1-based like the remainder of the cell API.
Chart sheets and shapes in legacy XLS files
On the BIFF8 side, the richer AddChartSheet overload accepts the chart type, titles, and an open array of TXLSChartSeriesInfo records, each carrying name, categories, and values as range strings. Shapes are added on the data worksheet itself:
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. TXLSWorkbook is held through the IXLSWorkbook interface and is reference-counted, so freeing it manually causes a double release; this is the opposite of TXLSXWorkbook, which must be freed in a try..finally block. And the shape helpers — AddRectangle, AddOval, AddLine, plus DeleteInRange for clearing a region of drawings — all anchor by row and column pairs, so a template that inserts rows above the shapes will shift them with the grid.
TXLSPicture additionally supports TransparentColor for masking a background colour out of bitmaps, which is the practical route for putting a non-rectangular stamp over grid content in a format that predates PNG alpha support in BIFF rendering.
Theme colours do not survive a BIFF8 round-trip
OOXML drawing fills can reference a theme colour slot, so re-colouring a whole document by swapping the theme is cheap in .xlsx. BIFF8 drawing records have no equivalent slot. When HotXLS applies a theme colour to an XLS drawing, it resolves and stores the final RGB value; after saving and reopening the file, the original theme index is simply not there to read back. If your product re-brands generated documents — a white-label reporting tool, for instance — keep the theme-to-RGB mapping in application configuration and reapply it on generation, instead of expecting to recover it from a saved .xls.
A related trade-off appears at the performance end: the XLS facade can skip parsing the entire drawing layer when you only need cell data from large legacy files, which speeds up bulk reads considerably, but a workbook loaded that way must never be saved, because the skipped OfficeArt stream is gone. The technique belongs in read-only analytics jobs, covered in more depth in our notes on large-workbook performance in HotXLS.
Keeping anchors stable whilst the grid changes
Reports rarely stay the size they were generated at. The XLSX facade's structural operations — InsertRows, DeleteRows and their column counterparts — shift the dependent layers along with the cells: merged regions, hyperlinks, comments, frozen panes, filter ranges, conditional formats, validations, tables, defined names, and, importantly for this topic, image and chart anchors all move together. A logo anchored at row 1 stays at the top when ten rows are inserted below it; a chart frame anchored under the data block slides down when the block grows. What does not get rewritten is anything you stored as a literal string yourself before the insert, so the safe ordering for a template fill is: write and reshape the data first, then create charts and place images as the final pass, deriving every range string from the post-insert row counts.
Two smaller placement tools round out the kit. On the XLS side, TXLSTextBox.SetArea repositions a text box or auto shape onto a new cell rectangle after the fact, which is cheaper than deleting and recreating it when a footer block moves. And the bitmap overload of AddPicture accepts a live TBitmap with an optional transparency flag, so charts rendered by your own VCL drawing code — gauges, sparkline strips, anything the native chart types do not cover — can be stamped into the sheet without a round trip through a temporary file.
FAQ: charts and pictures in HotXLS
Can I embed a chart within a normal .xls worksheet? No. The BIFF8 API creates chart sheets only. If the layout requires a chart floating beside the data, generate .xlsx with TXLSXWorksheet.AddChart, or restructure the deliverable around a chart sheet.
Why does my inserted image show as a broken icon in Excel? Almost always because a file path was passed to AddImage, which expects encoded image bytes. Switch to AddImageFromFile or read the file into the data string first.
How do I make an image exactly the size of the original bitmap? Multiply the pixel dimensions by 9525 and assign the results to WidthEMU and HeightEMU. Excel scales anything else to whatever the anchor implies, which is rarely what the designer intended.
Charts and images typically arrive as the finishing layer on a structured report, so the groundwork matters: template-driven report generation covers filling the data the chart will reference, and merged cells and layout control covers keeping the grid stable underneath your anchors. Full class and method documentation is on the HotXLS Component product page.