Excel Cell Comments and Hyperlinks in Delphi with HotXLS

Rename a sheet from "Summary" to "Overview" in a generated workbook, and every internal hyperlink that pointed at Summary!A1 stops going anywhere. No exception on save, none on open. The link still renders, still looks clickable, and quietly resolves to nothing. The same kind of breakage shows up after a save-as conversion or an .xls/.xlsx round-trip, when a comment lands a column off or a relative link drops its target. Both features carry review state that real people act on, so when they break the failure is invisible until a reviewer clicks and nothing happens

That is the practical reason comments and hyperlinks deserve more care than their cosmetic appearance suggests. HotXLS gives Delphi and C++Builder code direct write access to both, in XLS and XLSX, with no Excel automation in the loop. The flip side of that control is responsibility: the library writes exactly the targets you hand it and validates none of them, so keeping a review workflow intact is your code's job, not Excel's

Cell comments as machine-written review records

In the XLSX class model a comment is a worksheet-level object: it knows its row, its column, an author, and a text body. The author field earns its place. When a workbook your code generated travels through a review chain, the first question an auditor asks is who wrote a given note, and a note left without an author answers that question with a blank. Stamp generated comments with a service identity so the provenance is never ambiguous

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 probe carries more weight than it looks. A batch job that retries after a transient failure will happily call AddComment a second time on a cell it already annotated, and the cell ends up with two stacked notes that nobody asked for. Probe with FindAt first, and update the object it returns. The Comments collection also exposes DeleteAt and DeleteInRange. That range variant is the one to reach for when you sanitize a workbook before it leaves the building: clearing internal QA annotations from a whole region is a single call rather than a hand-written loop over cells

External URLs and in-workbook jumps are different APIs

OOXML keeps the two link kinds in different places. An external URL becomes a relationship entry in the sheet's .rels part, with the cell pointing at the relationship by id. An internal jump never touches the relationship layer at all; it is a plain location string such as Summary!A1 stored directly on the link. HotXLS keeps that distinction visible in the API rather than overloading a single method, which means you pick the right call by knowing where the target lives:

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 IsInternal tells you which of the two is populated. That flag is what you check when you inventory the links in an opened workbook and need to treat "leaves the file" and "stays in the file" under different rules: an external host might face an allowlist while an internal target only has to name a sheet that exists. Internal links carry no relationship parts behind them, which also makes them cheaper to rewrite in bulk

The breakage from the opening lives entirely on the internal side, and it follows from one fact: a location string is not a parsed reference. HotXLS writes the exact text you hand it, and nothing re-points that text when a sheet is renamed later. Two defenses hold up in practice. The first is discipline about ordering: rename every sheet before you generate a single link, then treat sheet names as frozen identifiers. The second is sturdier and survives renames done after the fact. Point the link at a workbook-level defined name rather than a raw Sheet!Cell address, because Excel rewrites a name's definition when the underlying sheet changes, so the link rides along automatically. That second approach pairs naturally with the techniques in defined names and cross-sheet formulas in HotXLS

The XLS side: same concepts, older plumbing

The BIFF8 facade hangs comments off ranges instead of a worksheet-level collection. You call AddComment on an IXLSRange and get back a TXLSComment; the range's Comment property reads an existing note, and ClearComments wipes them. The sharp edge here is positional. A TXLSComment does not publicly expose its own row and column, so the natural loop, "walk every comment and report where it sits," runs backward against the API. You have to start from the cells. Either drive the audit from the list of addresses you annotated, or keep your own position log as you write, because the comment object will not tell you afterward where it lives

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 to True is the legacy way to make a note impossible to overlook: the yellow box stays open on the sheet instead of waiting for a hover. TXLSComment goes a step further than its XLSX counterpart by exposing TextRuns, so a single note can carry a bold warning next to a plain explanation, formatting the XLSX comment API does not expose the same way. Hyperlinks on this side arrive through three progressive overloads (address only, then with display text, then with a screen tip) and read back through the worksheet's HyperLinks collection, where each link surfaces Address, SubAddress, DisplayText, and ScreenTip

A review index sheet beats scattered notes

Past a dozen or so annotations, hover-to-read quietly stops scaling. Notes pile up on sheets a reviewer never opens, and the ones that matter most are exactly the ones easiest to miss. The structure that has held up best is a generated index sheet: one row per annotated location, listing its sheet name, cell address, author, and a short excerpt of the note. The last column carries an internal hyperlink built with AddHyperlinkToCell that jumps straight to the annotated cell. Now the reviewer reads down a list instead of hunting across a grid, and the row count of that index doubles as your comment inventory for the audit pass below

The index is cheap to build because your generator already knows every position it touched. Append a (sheet, row, column, author, summary) tuple to a list as you write each comment, then emit the index sheet last so its row count is final before you save. Two refinements pay off: order the index by severity or by sheet rather than by insertion sequence, and put a return link in the index header so a reviewer can bounce back to the top after each item. Because internal links are plain location strings with nothing in the relationship layer behind them, even a thousand-row index adds almost nothing to file size or save time

That same sheet pays off again on the return trip. When the reviewed workbook comes back, your code reads status values typed into cells beside the index rows rather than re-scanning every sheet for comments that may have changed. A column of structured status cells parses cleanly; a scatter of free-text notes does not

A pre-delivery audit pass that actually catches the breakage

None of these APIs validate a target. A link to a sheet you deleted, a misspelled intranet host, a file share decommissioned last quarter: all of them save without a murmur. ECMA-376 specifies how a link is stored, not that it resolves to anything. A workbook that carries review metadata therefore earns a short audit stage of your own, run just before SaveAs:

• Collect every internal location written during generation and confirm the sheet name ahead of the bang still exists in the workbook's sheet collection
• Check external URLs against an allowlist of schemes and hosts. Bare file:// and UNC paths leak environment detail and break the moment the file leaves your network
• Count comments per sheet and compare against what your generator intended to write. A retry that doubled the notes surfaces here rather than in the reviewer's inbox
• Strip internal-only annotations with DeleteInRange whenever the recipient sits outside the organization

Teams that build their workbooks from a data layer can fold this stage into the same pipeline step that already validates the data, so the metadata check rides along for free. The mechanics are the ones described in exporting database query results to Excel reports, turned toward links and comments rather than rows

One quoting detail trips people up when they build location strings by hand. A sheet whose name contains a space has to be quoted inside the location, exactly the way the formula bar quotes it: 'Quarterly Totals'!A1, not Quarterly Totals!A1. HotXLS applies the same rules the formula engine uses for cross-sheet references, so if a link works in a worksheet formula its quoting will work here too. Hand it an unquoted name with a space and you get the same silent dead link the opening warned about

Comments and hyperlinks are the parts of a generated workbook that reviewers act on without a second glance, which is exactly why a target that points at nothing does real damage before anyone notices. Build the validation pass once, run it on every workbook before it ships, and the review workflow stays intact across renames and conversions. The full API surface for both the XLS and XLSX facades is documented on the HotXLS Component product page