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
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
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
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