HotXLS Delphi Excel Component keeps OpenDocument data pilot tables through an ODS open-and-save cycle by capturing the <table:data-pilot-tables> subtree of content.xml verbatim at open time and replaying it on save, since v2.382.0. Since v2.382.1 the fragment also carries every XML namespace binding its ancestors declared, so the saved pivot definition stays well formed for any consumer, not only for HotXLS
The bug that forced both changes came out of a strict corpus run. The sample official-pivot.ods, written by a LibreOffice 6.1 development build, holds one pivot named DataPilot1 that reads Sheet1.A2:E30 and lands its result in Sheet1.G6:J18. Open it with HotXLS, save it unchanged, count <table:data-pilot-table> elements in the output: one in, zero out, on Win32 and Win64 alike. Nothing in the test touched the pivot. The first round of probes had only compared cell constants and passed; the structural assertion is what exposed the loss, which is a reminder that "values match" is a weak definition of round-trip fidelity
Why does an ODS pivot table vanish after a library save?
An ODS pivot table vanishes because HotXLS has no in-memory model for OpenDocument data pilot tables, and the ODS writer builds content.xml entirely from the model. The writer assembles automatic styles, one <table:table> per worksheet, <table:content-validations>, <table:named-expressions>, and <table:database-ranges>, each generated from objects the workbook actually holds. A pivot definition — ODF 1.3 Part 3 §9.6, a <table:data-pilot-tables> container with one <table:data-pilot-table> per pivot, carrying its table:source-cell-range, its table:data-pilot-field children, its table:target-range-address and table:buttons — has no object to live in, so the regenerated part simply omits it
The contrast with XLSX is deliberate. HotXLS parses SpreadsheetML pivot caches and pivot tables into a real model that you can build, extend with calculated fields, and refresh from Delphi, so those survive a save because they are rewritten, not copied. ODS pivots are a much rarer request, and modelling the ODF data pilot vocabulary for the sake of round-trip alone would be a lot of code that nobody edits. The pragmatic answer is the same one HotXLS already applies to unknown extLst blocks in XLSX: keep what you do not model, byte-for-byte if you can, event-for-event if you cannot
What did the first Pos-based capture get wrong?
The v2.382.0 capture sliced the pivot definition out of content.xml as a plain string, and the slice was missing the namespace declarations that made it meaningful. The implementation was as short as it sounds — decode the part to a WideString, find the opening tag with Pos, find the closing tag after it, copy the span into FRawOdsDataPilotTablesXml on the workbook:
// HotXLS v2.382.0 -- superseded one release later
function OdsCaptureDataPilotTablesXml(Stream: TStream): WideString;
const
OpenTag: WideString = '<table:data-pilot-tables';
CloseTag: WideString = '</table:data-pilot-tables>';
var
Text: WideString;
StartPos, ClosePos: Integer;
begin
Result := '';
Text := LoadPartAsWideString(Stream); // whole content.xml in memory
StartPos := Pos(OpenTag, Text);
if StartPos = 0 then Exit;
ClosePos := Pos(CloseTag, Copy(Text, StartPos, MaxInt));
if ClosePos = 0 then Exit;
Result := Copy(Text, StartPos, ClosePos + Length(CloseTag) - 1);
end;The count assertion went green, and the fix shipped. What caught it was a second, stricter check added the same day: every XML part of the saved package is fed to an independent namespace-aware parser outside HotXLS, and that parser rejected the new content.xml with an unbound-prefix error. The pivot from LibreOffice carries producer extension attributes — loext:ignore-selected-page="true" on a page field, calcext:repeat-item-labels="false" on every level — and the sliced string contained those attributes but not the xmlns:loext and xmlns:calcext declarations that bound them. Those declarations sat on the source file's <office:document-content> root, thirty-five of them, two thousand characters away from the pivot
W3C Namespaces in XML 1.0 §6.1 defines the rule that makes this a hard failure rather than a cosmetic one: a namespace declaration is in scope from the start tag of the element it appears on to that element's end tag, and every prefixed name inside that scope resolves against it. Cut a subtree out of the document and you cut it out of the scope. HotXLS writes its own <office:document-content> root with eleven declarations — office, table, text, style, number, fo, draw, svg, xlink, calcext, tableooo — so calcext: happened to resolve, table: happened to resolve, and loext: did not. A namespace-aware parser treats an unbound prefix as a well-formedness violation, which means the whole part is unreadable, not merely one attribute
How does HotXLS carry ancestor xmlns bindings onto the fragment?
HotXLS v2.382.1 replaced the string slice with a pass over content.xml through its own streaming TXMLReader, maintaining a stack of namespace bindings tagged with the depth at which each was declared, and copying the bindings still in effect onto the fragment's root element the moment the target is reached. The reader runs with PreserveWhitespaceText enabled so text nodes come back exactly as written, and the rebuilt tags use TXMLReader.RawName and TXMLReader.Attribute[I].RawName — the prefix spelling from the file — rather than the canonical names the reader normally hands to the part parsers. Here is the core of the loop:
// Namespaces: TStringList of 'xmlns:p=uri' with the declaring depth in Objects[]
while Reader.Read do
begin
if CaptureDepth >= 0 then
XlsxAppendRawXmlReaderNode(Result, Reader); // element, text, CDATA, comment
if Reader.NodeType = xmlntElement then
begin
for I := 0 to Reader.AttributeCount - 1 do
begin
AttrName := Reader.Attribute[I].RawName;
if (AttrName = 'xmlns') or (Pos(WideString('xmlns:'), AttrName) = 1) then
Namespaces.AddObject(String(AttrName) + '=' + String(Reader.Attribute[I].Value),
TObject(NativeInt(Depth)));
end;
if (CaptureDepth < 0) and (Reader.Name = 'table:data-pilot-tables') then
begin
Opening := XlsxRawXmlReaderOpenTag(Reader); // strip the trailing '>' or '/>' first
...
// Carry the effective ancestor bindings onto the fragment root.
for I := Namespaces.Count - 1 downto 0 do
begin
AttrName := WideString(Namespaces.Names[I]);
if Seen.IndexOf(String(AttrName)) >= 0 then Continue; // innermost binding wins
Seen.Add(String(AttrName));
if not Reader.HasAttribute(AttrName) then // already declared here? skip
Opening := Opening + ' ' + AttrName + '="' +
XlsxEscapeAttr(WideString(Namespaces.ValueFromIndex[I])) + '"';
end;
...
CaptureDepth := Depth;
end;
if not Reader.IsEmptyElement then Inc(Depth);
end
else if Reader.NodeType = xmlntEndElement then
begin
Dec(Depth);
if Depth = CaptureDepth then Exit; // subtree closed
end;
if (Reader.NodeType = xmlntEndElement) or
((Reader.NodeType = xmlntElement) and Reader.IsEmptyElement) then
while (Namespaces.Count > 0) and
(NativeInt(Namespaces.Objects[Namespaces.Count - 1]) >= Depth) do
Namespaces.Delete(Namespaces.Count - 1); // leave the scope
end;
if CaptureDepth >= 0 then
raise Exception.Create('OpenDocument pivot definition ended inside an element');Three details in that loop carry the correctness. Walking the stack from the innermost binding outward and remembering each prefix in Seen implements shadowing: if a nearer ancestor rebinds xmlns:table, the nearer value wins, exactly as §6.1 says it must. Skipping prefixes the element already declares itself avoids emitting the same attribute twice, which would be a different well-formedness error. And the pop rule fires on end tags and on empty elements, because <x/> never produces an EndElement event — the same self-closing trap the XLSX extLst capture had to learn. Matching the target by Reader.Name rather than RawName is a quieter win: the reader canonicalises the ODF table namespace URI to the table prefix, so a producer that spells it t:data-pilot-tables still matches, while the emitted fragment keeps whatever prefix the producer used
The loop also refuses to guess. If the part ends while the capture is still open — a truncated or malformed content.xml — OdsCaptureDataPilotTablesXml raises rather than returning a half fragment, because a half fragment would be written back on save and turn a damaged input into a damaged output with the library's name on it
Where does the fragment land in the saved content.xml?
HotXLS writes the captured fragment into <office:spreadsheet> immediately after the <table:named-expressions> it generates and ahead of <table:database-ranges>. The ODF 1.3 Part 3 content model of <office:spreadsheet> prescribes a fixed sequence for those trailing children, so a verbatim block cannot simply be appended wherever the writer happens to be; it has to be dropped into a specific slot. From the caller's side there is no API and nothing to configure; the definition rides along with an ordinary open and save:
var
Book: TXLSXWorkbook;
begin
Book := TXLSXWorkbook.Create;
try
if Book.OpenODS('official-pivot.ods') <> 1 then
raise Exception.Create('open failed');
Book.Sheets[0].Cells[2, 5].Value := 1250.0; // edit inside the pivot source range
Book.SaveAsODS('official-pivot-out.ods');
// content.xml in the output still carries DataPilot1 with its
// source range, fields, target range, buttons and loext:/calcext: attributes
finally
Book.Free;
end;
end;The redundancy is intentional and worth knowing about. The fragment root now repeats xmlns:table and xmlns:calcext even though the saved document root declares them too; Namespaces in XML permits redeclaring a prefix in a nested scope, so the duplicates are harmless. For the LibreOffice sample the carried set is all thirty-five root declarations, about two kilobytes on top of the 8,357-character definition, because the capture does not analyse which prefixes the subtree actually uses. A used-prefix scan would trim that, and it may come later; correctness first, compactness second
A rule for cutting subtrees out of XML for verbatim replay
The general lesson is that a subtree is only self-contained once you have made it so, and namespace scope is the first thing that breaks when you forget. The checklist HotXLS now applies to any "keep what we do not model" capture:
- Walk the document with a real reader and track the bindings in scope. String search with
Poscannot see scope at all, and it also mismatches on nested elements with the same name, on a matching string inside a comment orCDATAsection, and on attribute values that happen to contain the tag text - Copy the effective bindings onto the fragment root, innermost first, once per prefix, skipping what the root already declares
- Keep the raw prefix spelling in the emitted tags; match the target by resolved namespace, not by literal prefix
- Preserve whitespace text nodes, and remember that an empty element closes its own scope without an end-tag event
- Validate the saved part with a parser that is not the library under test. The library will happily re-read its own output through the same lenient code path that wrote it
The last point is the one that actually found HXLS-003 the second time. The v2.382.0 acceptance check was a regular expression counting data-pilot-table start tags in the saved content.xml, and a regular expression sees a tag, not a document — it is blind to whether the prefixes on that tag are bound. The strict corpus runner added in v2.382.1 parses every XML and .rels part of the saved package with a namespace-aware parser and then compares the pivot tree — tag, sorted attributes, text, children, recursively — against the original. That comparison is namespace-expanded, so a prefix respelling would still pass and an unbound prefix cannot
Where the verbatim guarantee ends
Verbatim replay preserves a definition; it does not understand one, and the boundaries follow from that. HotXLS exposes no API to read, edit, or refresh an ODS pivot, so FRawOdsDataPilotTablesXml is an internal field and the only observable behaviour is that the definition survives. The fragment is re-serialised from reader events, not copied as bytes: attribute quoting and self-closing forms are normalised, while text and whitespace are kept. The captured XML is emitted only by the ODS content writer, so a workbook opened from .ods and saved as .xlsx loses the pivot, and a workbook opened from .xlsx has nothing to replay into an .ods save — the asymmetries of the ODS import and export paths apply here as everywhere. And because the definition is opaque, it cannot follow your edits: rename Sheet1 or move the source data in HotXLS and the saved pivot still points at Sheet1.A2:E30, leaving the consumer to report a broken range when it next refreshes. One ordering caveat belongs here too: HotXLS emits AutoFilter ranges as <table:database-ranges> after the pivot fragment, and the corpus sample carries no database range, so a workbook with both a filter and a pivot should be run through an ODF schema validator before you rely on the relative order of those two elements
Test with your own producer's files, not just the corpus sample. The namespace carry-over handles any prefix a producer declares on an ancestor, but a document that declares a prefix on the pivot element itself, or that uses a default namespace for the table vocabulary, exercises the skip and shadowing branches that the LibreOffice sample does not. Both are implemented; neither has a sample in the corpus yet, and that distinction is exactly the kind of thing a changelog entry tends to blur
The verbatim data pilot capture in v2.382.0 and the namespace-scope fix in v2.382.1 ship in the current HotXLS Delphi Excel Component, whose product page lists the full ODS, XLSX, and XLS read-write coverage for Delphi and C++Builder