LlosLab Software Development BlogEngineering notes for PDF, Delphi, and document software developers.

Technical Article

Excel Cell Comments and Hyperlinks in Delphi with HotXLS

Spreadsheet Programming

A reconciliation workbook leaves the finance system every night with two kinds of metadata stitched into it: cell comments explaining why a figure was adjusted, and hyperlinks pointing reviewers at the source record in the intranet. After a routine sheet rename — "Summary" became "Overview" — every internal jump in the workbook silently died. No error on save, no error on open; the links just stopped going anywhere. That incident is a useful lens for this topic, because comments and hyperlinks are the two workbook features that look cosmetic but actually carry workflow state. HotXLS gives Delphi and C++Builder code direct write access to both, in XLS and XLSX, without Excel automation — which also means your code, not Excel, is responsible for keeping the targets valid.

Cell comments as machine-written review records

In the XLSX class model, a comment is a worksheet-level object with a row, a column, an author, and a text body. The author field is not decoration: when a generated workbook circulates through a review chain, "who said this" is the first thing an auditor filters on, so generated notes should carry a service identity rather than defaulting to nothing.

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Note: TXLSXComment;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('reconciliation.xlsx');
    Sheet := Book.Sheets[0];

    // Authored note on the adjusted figure
    Sheet.AddComment(14, 4, 'Manual adjustment: late FX rate, see ticket FIN-2214',
      'recon-service');

    // Update an existing note instead of stacking a second one
    Note := Sheet.Comments.FindAt(14, 4);
    if Note <> nil then
      Note.Text := Note.Text + ' [verified 2026-06-11]';

    Book.SaveAs('reconciliation-reviewed.xlsx');
  finally
    Book.Free;
  end;
end;

The FindAt pattern matters more than it looks. Calling AddComment twice on the same cell is the classic bug in retry-prone batch jobs; always probe with FindAt first and update the existing object. The Comments collection also exposes DeleteAt and DeleteInRange, and the range variant is the right tool for a sanitization pass — stripping internal QA annotations from a region before the workbook is sent outside the organization is one call, not a loop.

External URLs and in-workbook jumps are different APIs

OOXML stores the two link kinds differently: an external URL becomes a relationship entry in the sheet's .rels part, while an internal jump is a plain location string such as Summary!A1 stored on the link itself. HotXLS mirrors that split with two methods instead of overloading one:

Sheet.Cells[2, 1].Value := 'Source record';
Sheet.AddHyperlink(2, 1, 'https://intranet.example.com/records/2214',
  'Open record 2214', 'ERP source entry');

Sheet.Cells[3, 1].Value := 'Totals';
Sheet.AddHyperlinkToCell(3, 1, 'Overview!B12', 'Jump to totals');

On the resulting TXLSXHyperlink object, Url and Location are mutually exclusive and the IsInternal flag tells you which one is populated — useful when you inventory links in an opened workbook and need to apply different policies to "leaves the file" versus "stays in the file". Because internal links never touch the relationship parts, they are also cheaper to rewrite in bulk.

The failure mode from the introduction lives entirely on the internal side. A location string is not a parsed reference; HotXLS writes exactly what you pass, and nothing re-points it when a sheet is renamed afterwards. Two defenses work in practice. Either rename sheets before generating any links, and treat sheet names as frozen identifiers from that point on, or target a workbook-level defined name instead of a raw Sheet!Cell address — names survive renames because Excel updates their definitions. The second approach pairs well with the techniques in defined names and cross-sheet formulas in HotXLS.

The XLS side: same concepts, older plumbing

The BIFF8 facade attaches comments to ranges rather than to a worksheet collection. IXLSRange.AddComment returns a TXLSComment, the Comment property reads an existing one back, and ClearComments removes them. One sharp edge: the comment object does not publicly expose its own row and column, so code that walks "all comments with their positions" must navigate from ranges to comments, not the other way around. Plan your audit loop around the cells you annotated, or keep your own position log while writing.

var
  Book: IXLSWorkbook;
  Sheet: IXLSWorksheet;
  Remark: TXLSComment;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Review';
  Sheet.Cells.Item[5, 2].Value := 4821.50;

  Remark := Sheet.Cells.Item[5, 2].AddComment('Awaiting sign-off from controller');
  Remark.Visible := True;   // pop the note open on first view

  Sheet.AddHyperlink(7, 2, 'https://intranet.example.com/signoff/4821',
    'Sign-off form', 'Opens the controller queue');
  Book.SaveAs('review.xls');
end;

Setting Visible on a comment is the legacy trick for making a note impossible to miss — the yellow box renders permanently instead of waiting for hover. TXLSComment also exposes TextRuns, so a note can mix bold warnings with plain explanation, which the XLSX comment API does not offer in the same way. Hyperlinks on this side come in three progressive overloads (address only, plus display text, plus screen tip) and are enumerable through the worksheet's HyperLinks collection, with Address, SubAddress, DisplayText, and ScreenTip readable per link.

A review index sheet beats scattered notes

Once a workbook carries more than a dozen annotations, hover-to-read stops scaling and reviewers start missing notes on sheets they never visit. The pattern that has held up best in production is a generated index: a dedicated sheet listing every annotated location with its sheet name, cell address, author, and an excerpt of the note text — and, in the last column, an internal hyperlink built with AddHyperlinkToCell that jumps straight to the annotated cell. The reviewer works down the index top to bottom instead of hunting through the grid, and the row count of that index doubles as your comment inventory for the audit pass below.

Building it costs little because your generator already knows every position it annotated. Append each (sheet, row, column, author, summary) tuple to a list while writing comments, then emit the index sheet last so its own row count is final. Two practical refinements: sort the index by severity or by sheet rather than by insertion order, and give the index sheet a return link in its header so users can bounce back after each item. Since internal links are plain location strings with no relationship parts behind them, even a thousand-row index adds almost nothing to file size or save time.

The same index makes the post-review trip cheaper in reverse: when the workbook comes back, your code reads status values typed next to the index rows instead of re-scanning every sheet for changed comments — structured cells are far easier to parse reliably than free-text notes.

A pre-delivery audit pass that actually catches the breakage

None of these APIs validate targets — a link to a deleted sheet, a misspelled intranet host, or a file share that was decommissioned last quarter saves without complaint. ECMA-376 only specifies how links are stored, not that they resolve. So a workbook that carries review metadata deserves a short audit stage before SaveAs:

  • Collect every internal location written during generation and verify the sheet name before the bang still exists in the workbook's sheet collection.
  • Check external URLs against an allowlist of schemes and hosts; file:// and bare UNC paths leak environment details and break off-network.
  • Count comments per sheet and compare against the count your generator intended — a retry that doubled the notes shows up immediately.
  • Strip internal-only annotations with DeleteInRange when the recipient is external.

Teams that already generate their workbooks from a data layer can fold this into the same pipeline stage that validates the data itself; the pattern is the same one described in exporting database query results to Excel reports, just applied to metadata instead of rows.

FAQ: comments and hyperlinks

Does HotXLS check that a hyperlink target exists? No, by design. The library writes the structures; reachability is an application concern. Validate URLs and internal locations in your own code before saving.

How do I link to a sheet whose name contains spaces? Quote the sheet name inside the location string, exactly as Excel does in formulas: 'Quarterly Totals'!A1. The quoting rules are the same ones the formula engine accepts for cross-sheet references.

Can I make a comment visible without the user hovering? In XLS, yes — set Visible := True on the TXLSComment. In XLSX the comment renders on hover; if the note must be unmissable, put it in a cell or a text box instead.

Comments and links are the part of a workbook your users judge by trust rather than by looks, so the engineering bar is "never wrong" rather than "usually fine". The full API surface for both facades is documented on the HotXLS Component product page.