Technical Article

Writing ISO 29500 Strict XLSX Files from Delphi

An archive intake check throws out a spreadsheet with a single complaint: the root element of xl/workbook.xml sits in the namespace http://schemas.openxmlformats.org/spreadsheetml/2006/main. Excel opens the very same file without a murmur. Both verdicts are correct. ISO/IEC 29500 defines Strict SpreadsheetML in Part 1 and keeps the Transitional form, with its legacy compatibility features, in Part 4, and the URI the validator quoted belongs to Transitional. A tender or records policy that names ISO 29500 Strict expects the purl.oclc.org vocabulary in every part of the package, and a file can satisfy Excel whilst failing that test completely

What changes between Strict and Transitional?

The visible change is vocabulary. A Strict workbook part declares http://purl.oclc.org/ooxml/spreadsheetml/main as its root namespace and http://purl.oclc.org/ooxml/officeDocument/relationships for r:id references. The DrawingML, chart, picture and spreadsheet-drawing namespaces move in the same way, and so do the document property parts. Relationship types change with them: the root relationships part points at http://purl.oclc.org/ooxml/officeDocument/relationships/officeDocument, and the extended and custom property types become camelCased as extendedProperties and customProperties, where Transitional spells them with hyphens

The less visible change is scope. Strict leaves out the parts of the Transitional schema that only existed to carry legacy binary behaviour, and it has no vocabulary at all for the extensions Office added later. A search and replace over strings cannot paper over that gap, because some features simply have no Strict spelling. Converting a package therefore means two jobs: rename everything that has a Strict equivalent, and decide what to do with everything that does not

HotXLS StrictOOXML vocabulary switch in the XLSX package: workbook, relationship and extended-properties parts rewritten from openxmlformats transitional namespaces to purl.oclc.org strict namespaces
Every part changes vocabulary at once, from the root namespace through relationship types to the camelCased extendedProperties type; no transitional namespace may survive anywhere in the package

Switching a Delphi export to StrictOOXML

In HotXLS the switch is a single Boolean on TXLSXWorkbook, StrictOOXML, which defaults to False. Authoring code stays exactly as it was. You build sheets, cells and formulas the usual way, set the property, and save. SaveAs returns 1 on success; anything else means the package was not written, and the reason sits in Diagnostics

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
  I: Integer;
begin
  Wb := TXLSXWorkbook.Create;
  try
    Sh := Wb.Sheets.Add('Data');
    Sh.Cells[1, 1].Value := 'Product';
    Sh.Cells[1, 2].Value := 'Amount';
    Sh.Cells[2, 1].Value := 'Widget';
    Sh.Cells[2, 2].Value := 17;
    Sh.Cells[3, 1].Value := 'Gadget';
    Sh.Cells[3, 2].Value := 25;
    Sh.Cells[4, 2].Formula := '=SUM(B2:B3)';

    Wb.StrictOOXML := True;            // ISO/IEC 29500 Strict vocabulary
    if Wb.SaveAs('archive-copy.xlsx') <> 1 then
    begin
      for I := 0 to Wb.Diagnostics.Count - 1 do
        WriteLn(Wb.Diagnostics[I].Message);
      raise Exception.Create('Strict save was refused');
    end;
  finally
    Wb.Free;
  end;
end;

The property is read at the start of every save and drives a per-save switch that the writers consult, so a save that fails part-way through cannot leave strict translation switched on for the next one. On a server where a single workbook object fields several export requests, you can flip StrictOOXML between calls and each save gets exactly the vocabulary it asked for

Two boundaries are worth knowing up front. Strict output applies to the Open XML formats: workbook, template and macro-enabled template. Asking for .xlsb whilst StrictOOXML is True is refused with a diagnostic that tells you to turn the property off, because the binary format has no Strict variant. And opening a file that Excel saved as "Strict Open XML Spreadsheet" does not set the property for you. Load such a file, save it without touching StrictOOXML, and you get a Transitional package back

Why does a strict save refuse to run?

A strict save refuses to run when the workbook holds content that ISO 29500 Strict cannot express. The families HotXLS checks are VBA projects, form controls with their ctrlProps parts, threaded comments (the modern persons-and-threads model, not classic notes), slicers, dynamic-array metadata, data bars that rely on the 2009 extension block for features such as solid fill, negative-bar colours or a custom axis, and Timelines when no conversion policy has been chosen

HotXLS strict save gate in Delphi: SaveAs with StrictOOXML turns away VBA projects, form controls, threaded comments, slicers and dynamic array metadata, and only completes once no forbidden feature is present
A strict save checks the workbook against what ISO 29500 Strict can express and names any blocking feature rather than writing a package that merely claims conformance

Refusing is the right default here. The alternative is to drop the macro project or the slicers and still stamp the result as Strict, which hands someone a workbook that opens cleanly and behaves differently from the one they exported. Excel would never flag that. A refusal names the feature whilst your code still knows which report it was producing. Most refusals arrive as SaveAs returning -1 with a diagnostic such as StrictOOXML: Strict output cannot embed VBA. Dynamic-array storage is decided whilst the package is being written, so a formula assigned in this session can still stop the save with an exception that names the metadata; wrap a Strict SaveAs in try..except as well as checking its result. If the workbook needs its macros, it belongs on the Transitional .xlsm path described in preserving VBA projects and external links

You do not have to find out by saving. GetConversionReport inspects the workbook without recalculating it and returns a TXLSConversionReport whose items carry a feature name, a fidelity level, a sheet index and a message. Under StrictOOXML the blocking items come back as xlsConversionUnsupported. The Timeline case also shows the one place where you can deliberately trade content for conformance: setting StrictTimelineOutputPolicy to xlsxStrictTimelineDateCriteria converts supported Timeline selections into standard pivot date criteria and leaves out the Timeline UI, its cache parts, relationships and defined names. The report lists that as xlsConversionDropped, and the source workbook in memory stays unchanged

function StrictBlockers(Wb: TXLSXWorkbook; Log: TStrings): Boolean;
var
  Report: TXLSConversionReport;
  I: Integer;
begin
  Wb.StrictOOXML := True;
  // Accept the documented Timeline trade: date criteria stay, Timeline UI goes
  Wb.StrictTimelineOutputPolicy := xlsxStrictTimelineDateCriteria;
  Report := Wb.GetConversionReport(xlsConversionOpenXmlWorkbook);
  try
    Result := False;
    for I := 0 to Report.Count - 1 do
      if Report[I].Fidelity in [xlsConversionUnsupported, xlsConversionDropped] then
      begin
        Log.Add(Format('%s (sheet %d): %s',
          [Report[I].Feature, Report[I].SheetIndex, Report[I].Message]));
        if Report[I].Fidelity = xlsConversionUnsupported then
          Result := True;
      end;
  finally
    Report.Free;
  end;
end;

Run this ahead of the export and log every line it returns. A sheet index of -1 marks a workbook-level item such as the VBA project; worksheet items carry the 1-based sheet number and the sheet name, matching the way Sheets is indexed. The report sees dynamic-array metadata that a loaded workbook already carries; for formulas assigned in code, rely on the scalar rewrite in the next section

The formula that quietly becomes a dynamic array

The refusal that catches people unawares is dynamic-array metadata in a workbook that never used FILTER or SEQUENCE. Excel 365 evaluates an operator applied to a multi-cell range as an array, even inside a reducer, so =SUM(B2:B4*C2:C4) is an array formula as far as Excel is concerned. When you assign that text to Formula, HotXLS stores the cell the way Excel would, as a single-cell dynamic array with a cm reference into xl/metadata.xml; otherwise Excel would apply implicit intersection on load and show #VALUE!. The background is in array operator formulas and implicit intersection. Strict has no vocabulary for that metadata part, so the save stops

// Array storage: the product of two ranges is evaluated as an array
Sh.Cells[5, 2].Formula := '=SUM(B2:B4*C2:C4)';    // blocks a Strict save

// Scalar storage: plain range arguments, same total
Sh.Cells[5, 2].Formula := '=SUMPRODUCT(B2:B4,C2:C4)';

For Strict exports, write the scalar form. SUMPRODUCT with plain range arguments gives the same total, needs no dynamic-array support in whatever reads the file later, and keeps the cell out of the metadata part altogether. The same rewrite works for conditional counts: SUMPRODUCT((A2:A4="x")*1) still multiplies arrays, so prefer COUNTIF(A2:A4,"x") there. If a report genuinely needs spilled results, it needs Transitional output

Preserved parts get rebuilt instead of copied

The worksheet XML is the easy part of strict output. The hard part is everything a fast writer would rather copy verbatim. When HotXLS opens an existing package, it keeps the original theme, connections, external links, charts and pivot parts, and on a Transitional save it copies their compressed bytes straight through when the model has not changed. That is the right call for fidelity and the wrong one for Strict, because those bytes still carry Transitional namespaces

With StrictOOXML on, the byte-copy path is bypassed for all of those parts. Connections, external links and charts are rebuilt from the model; the theme and the captured pivot table, cache definition and cache record XML are replayed through the namespace translator. That translator parses each start tag and rewrites only two things: namespace declarations, and the Type attribute of Relationship elements in .rels parts. Cell text, other attribute values, comments and CDATA sections pass through byte for byte, so a shared string that happens to contain http://schemas.openxmlformats.org/... is never touched. Namespace URIs outside schemas.openxmlformats.org, such as the Microsoft extension namespaces inside extLst blocks, are left alone; if your validator rejects extension content as well as Transitional namespaces, remove those features from the source workbook before exporting

HotXLS strict output path for preserved XLSX parts: theme, connections, external links, charts and pivot caches move from verbatim byte-copy to a rebuild or a translating replay that rewrites only namespace declarations and relationship types
Copied compressed bytes would smuggle transitional namespaces into the strict package, so the preserved paths skip the fast path; translation rewrites only namespace declarations and relationship types, and the streamed worksheet skeleton is translated before being split at sheetData

Streamed worksheets follow the same rule with one twist. The writer builds the worksheet skeleton first, translates it, and only then splits it at <sheetData> to stream the rows in between. Row blocks contain no vocabulary URIs, so large sheets pay nothing extra for Strict output. Shared strings and pivot cache records work in the same way: only the header element is translated. There is more on the preservation side in lossless round-trips of themes, extension lists and calcChain

Reading Strict files and checking what you wrote

Files that Excel saved as Strict open like any other workbook. HotXLS maps Strict relationship types onto their Transitional counterparts wherever a relationship part is parsed, at the package root, worksheets, drawings, external links and pivot tables, and the reader resolves element names by namespace URI rather than by prefix. Third-party generators bind prefixes however they like, so the same machinery serves ordinary files too; it is described in OPC relationship resolution in XLSX packages. On a Transitional save of such a file, any preserved XML part whose root still uses a Strict namespace is converted back on the way out, so the result does not end up half one vocabulary and half the other

Verify Strict output against the package, never against Excel, which opens both forms happily. Unzip the result and check three things: xl/workbook.xml declares http://purl.oclc.org/ooxml/spreadsheetml/main, no part contains the string schemas.openxmlformats.org/spreadsheetml, and the Type values in _rels/.rels and xl/_rels/workbook.xml.rels start with http://purl.oclc.org/ooxml/. A quick findstr /s /m "schemas.openxmlformats.org/spreadsheetml" *.xml run over the extracted folder should list nothing

Then open the file again through TXLSXWorkbook.Open and compare values, formulas, number formats and hyperlinks with the source workbook. That read-back also exercises the reader-side mapping. If the workbook carries charts or pivot tables, include them in the comparison, since both switch from byte-copy to a rebuilt or replayed path under StrictOOXML

Strict output, Strict-aware reading and lossless round-tripping of Transitional packages share one OOXML engine in HotXLS for Delphi and C++Builder; the HotXLS Delphi spreadsheet component page sets out the formats and features it covers