The 1.0 corpus gate

What was run over the public corpus before 1.0, with the numbers and an explanation for every difference. The runs are one pass each, driven by tools/gate/gate.sh (stages), the probes under tools/, and the pinned oracles (LibreOffice 26.8.2.2, Jing 20241231 with the ODF 1.3 RelaxNG schema, lxml/libxml2 for XSD, Apache Tika 3.3.3, veraPDF 1.30.3), on the build machine. Corpus files stay on it; only counts are written here. The fixes the gate found are in the last section.

Corpus (all fetched by tools/corpus and tools/setup, none committed): Common Crawl Office samples (2,000 DOCX, 365 XLSX), public bug-tracker attachments (800 DOCX, 702 XLSX, 300 PPTX, 18 XLSB), LibreOffice-converted sets made from govdocs1 (453 XLSX, 237 PPTX), govdocs1 itself (810 DOC, 453 XLS, 680 PPT, 23 RTF), generated documents (25) and the 1M × 20 stress workbook. 4,900 OOXML/XLSB packages in all. There is no public ODF/RTF corpus in the set: ODF files are ours (written from the corpus) and LibreOffice's (converted from it), see the ODF section.

1. Round trip

Untouched = open and save with no edit. The comparison is per part by SHA-256 against the input, read by an independent zip reader (the BCL's), and for the hub sweeps also RoundTrip (raw entries preserved = stored bytes, method and CRC unchanged).

Edits (the hub changes only the part it edits; unknown markup stays because the part is rewritten from the tree):

Differences explained: the 36 DOCX and 11 XLSX that first raised format.unrecognised are truncated downloads (they stop at the 5 MiB crawl cap and have no end-of-central-directory record). The format sniffer still answers Unknown for a zip without a readable directory (a test pins that), but Document.Open, Workbook.Open and Presentation.Open now hand a file that starts with a zip header and has no end-of-directory record to the package reader, which scans the local headers and reports each repair as a located defect: 33 of the 36 DOCX and all 11 XLSX open and save now; the other 3 raise the typed packaging.missing-content-types because no content types entry survived the cut.

2. Write validity

Our output against the pinned schemas (ECMA-376 5th ed. Transitional and OPC, plus the Microsoft extension schemas under schemas/ms, markup compatibility applied first, xml:space on text elements not counted because Office writes it too), compared with the same check on the input: a part is newly invalid if it is invalid in the output and valid (or absent) in the input.

The error count rose in worksheets that were already invalid (49 → 50 and 9 → 50 in Common Crawl files, 1 → 2 in a bug-tracker file; a rerun of the 150-file samples listed 15 such parts in 4 Common Crawl files and 1 in a bug-tracker file, 0 in the LibreOffice-converted set). The validator reports at most 50 errors per part, so a 49 → 50 is a cap effect or one more error. The one non-capped case (1 → 2) is the xr:uid attribute on hyperlink: the original carries the same attribute on the same element and on the root, and both are declared ignorable with mc:Ignorable; the worksheet root is identical in the original and the rewrite, so it is not an attribute our writer adds. Why the validator reports it for the rewrite only was not established (open item).

Legacy → OOXML (the conversions the hub does on open), 100 files per format sampled by name order, schema-validated and opened by LibreOffice:

Loss codes by count of files (items): DOC loss.doc-properties 43 (45), loss.doc-sprms 24 (4,205), loss.doc-drawing-shapes 32 (289), loss.doc-text-boxes 23 (662), loss.doc-header-text-boxes 13 (13), loss.doc-ole-objects 8 (22), loss.doc-form-fields 1 (126). XLS loss.rich-text 35 (784), loss.drawing-shapes 13 (156), loss.charts 11 (73), loss.document-properties 11 (11), loss.vba 11 (11), loss.external-links 4 (7), loss.defined-names 2 (3), and one each of loss.autofilter-criteria, loss.form-controls, loss.macro-names, loss.pictures, loss.pivot-tables. PPT ppt.animations-dropped 76 (9,880), ppt.document-properties-not-carried 91 (118), ppt.shape-adjust-dropped 22 (228), ppt.ole-object-flattened 18 (43), ppt.shape-approximated 15 (65), ppt.table-borders-approximated 13 (37), ppt.fill-approximated 4 (10), ppt.picture-pict 4 (35), ppt.wordart-flattened 3 (3), ppt.table-as-group 2 (5), ppt.path-escape 1 (6), ppt.transition-dropped 1 (13). RTF loss.rtf-text-box 5 (36), loss.rtf-shape 5 (52), loss.rtf-frames 2 (11). Every conversion that lost something carried a report; a conversion with an empty report was lossless (DOC 28, XLS 38, PPT 2, RTF 16). The numbers are after the TotalTime fix below; the first pass found 1 invalid PPT-derived app.xml.

OOXML → other formats we write, sample of 60 per format (Common Crawl, bug tracker), loss reports counted, LibreOffice opening the output:

3. OpenDocument

No ODF corpus is public; the sets are ours (written from the corpus by the exporters) and LibreOffice's (converted from the same files). Jing validates every XML part and the manifest against the ODF 1.3 RelaxNG schema; foreign-namespace attributes and elements (LibreOffice extensions) are dropped before checking because the OASIS schema allows none.

The entry round trip is entry content (the package's zip container is rewritten; entries are copied raw). The first pass failed 3 ODT and 1 ODS file: text:soft-hyphen inside a text:span (6 elements; the schema has no such element, a soft hyphen is the character U+00AD), a table:table-row with no cell (3; the source row had no cells), style:header-first without a style:header before it (1; the schema nests the left and first variants inside the default one), and number styles with more than one number:number or with two number:text after it (a phone-number code such as (0) 0-0; the first digit group is kept, the rest are reported as multiple-digit-groups). All four are fixed in the exporters and the same 59 and 51 files, rewritten, now validate. Earlier, before the first fixes, the run failed 5 ODT, 10 ODS and 8 ODP files.

4. Text extraction against Apache Tika 3.3.3

150 files per format (every k-th by name), our text (Office.ExtractText) against Tika's, as the Dice coefficient of character 5-grams ("sim", white space ignored). "precision" is the share of our word tokens that Tika also has; "recall (no digits)" is the share of Tika's tokens without digits that we have (workbooks leave numbers out of the text by design, docs/text-extraction.md).

Nothing of ours is invented (precision ≥ 0.985 everywhere). The differences are what Tika emits and we do not, by policy:

• numbers, booleans and error values of workbook cells (#REF!, TRUE), and hyperlink targets (mailto:): XLSX, XLS, XLSB;
• the file names of embedded pictures and objects (image1.png, image2.svg, oleObject1.bin): 22 of 100 PPTX and 8 of 134 bug-tracker DOCX are exactly the files where Tika lists only such names and we have no text;
• master and layout text of presentations (footer placeholders such as a copyright line, the title-layout "PowerPoint Presentation") and the slide-number field ‹#›;
• text inside embedded OLE objects (a bug-tracker DOCX carries 105,027 characters of Lorem ipsum in embedded documents) and chart parts of DOCX: Tika recurses, we extract the document's own text. The weakest DOCX, XLSX and PPT rows are each a handful of files of this kind. No extraction raised an untyped error (typed errors: 2 truncated XLSX, 1 PPT).

5. Calc: cached values reproduced

Every formula cell is remembered, the workbook recalculated in full, and the result compared with the cached value (numbers at 15 significant digits, text, booleans and errors exactly). Cells with no cached value, cells whose formula spells #REF! and results in it, and cells whose formula calls a function the registry lacks or another workbook are counted but not testable. The floor is matches / testable. Mismatch classes: precision = equal to 9 digits but not to 15; volatile = NOW/TODAY/RAND* itself; volatile-downstream = in a workbook that calls them; import-gap-ref = a legacy reference into another workbook that the converter leaves as #REF! (documented in docs/xls.md), and what depends on it; bug = the rest, grouped by workbook below.

What the bug column is made of, after the fixes below (the biggest first, from tools/gate/calc_causes.py):

• References into other workbooks in legacy files (LibreOffice and XLS sets, ~79,000 and ~98,000 cells in 4–5 workbooks): the BIFF/LibreOffice conversion cannot express the reference, so the formula becomes #REF! and everything depending on it follows. The cached values are kept until recalculation. This is the documented limit (docs/xls.md, external workbook references); it is a conversion gap, not a calc-engine result.
• SUBTOTAL(9, …) over a range (cached 120,401, ours 264,410 in one Common Crawl workbook): probably rows hidden by an autofilter, which the engine does not know; not traced.
• Numerics: NORMINV/NORMSINV/NORMDIST differ from the cache in the 8th digit in files from the XLS era (577 + 134 cells), consistent with the older approximation Excel used then (not confirmed against an old Excel). precision cells (≤ 1e-9 relative) are cancellation noise in subtraction chains.
• Bug-tracker XLSX, the 20,224 bug cells by workbook (the largest, from tools/gate/calc_causes.py):
  • 4,674 cells: VLOOKUP(x, mitglieder, 2, 0) and MAXIFS(…, #REF!, …) in the workbook that needed 31 GB. LibreOffice cached #NAME? for them, ours is #REF! (the #REF! argument is the answer in Excel). Not comparable.
  • 4,405: YEAR(DATEVALUE("01. Januar 2023")): German month names, which DATEVALUE reads only under a German locale; Excel under en-US gives #VALUE! too. 3,358 and 2,476: formulas through names or INDEX/MATCH over whole columns that LibreOffice cached as #NAME? or empty text; not traced.
  • 1,475 (INDEX(L2:L32, COUNT(L2:L32)), 312.75 against 303.68), 1,156 (an empty-text cache), 777, 499, 280: not traced. 499 of them are IF(XLOOKUP(B2, Data!G:G, Data!H:H) = 0, "", …) with an empty B2: the cache says "" (the lookup found a blank row), ours is #N/A; whether Excel matches an empty lookup value against empty cells is one of the Excel-grid questions at the Office run.
  • The rest are under 150 cells each (locale-dependent TEXT(B7, "ddd."), CELL("filename"), DATE with text years). Fixed since the first pass (their cells now match): SUMIFS and XLOOKUP over whole columns on another sheet (the clipped column was compared with the unclipped reference), ISFORMULA (the workbook never answered it), a table's name used alone as a range (VLOOKUP(2, Dow, 2)), and a mismatched pair of criteria ranges (answered #VALUE! after reading both).
• GETPIVOTDATA (13 cells in one ODS) answered #REF! where the cached pivot has the value.
• Cells whose formula is literally #REF! and whose result is an error are not compared (error-literal-formula: 2,457 / 4,543 / 3,018 / 1,854 / 1,499 cells in the Common Crawl, LibreOffice-converted, XLS, ODS and XLSB sets): another implementation's cached error (#NAME?) for a formula that spells #REF! says nothing about the engine.

Recalculation was run with one thread per shard. One workbook (a 114 KB bug-tracker file, 11,876 formulas: MAXIFS over ranges of different sizes, A2:ZW9960 against C2:C9960, 700 times) needed about 31 GB of resident memory before the fix in section 8; it now recalculates in 1.1 s at a 113 MB peak under a 4 GB heap limit. The LibreOffice-converted XLSX, govdocs1 XLS and XLSB rows reran with identical numbers; the ODS row was not rerun (its inputs were deleted with the other outputs).

6. Rendering: veraPDF 1.30.3

Tagged PDF written by LayoutProbe render (the same code path as Office.Convert), a deterministic sample per kind, each in the profile asked for; veraPDF is run with the matching flavour. When the profile cannot be met the writer raises render.failed with the violated rules instead of writing a file, so a PDF that exists is the one veraPDF sees.

So the profile either refuses a document it cannot make conformant (10 of 24 readable Word documents are refused for PDF/UA because a table has no header row; this is the known table-header item), or produces a PDF veraPDF passes, except for the Private Use Area ActualText case in PDF/A. Cause: a symbol-font bullet or label (U+F0xx) that SymbolMap has no Unicode twin for stays a private-use code point in the run text we hand to the display list (DrawGlyphRun takes the source text and the cluster map for exactly this purpose); the PDF writer then maps the glyph to that private-use code point in /ToUnicode and writes no /ActualText, which PDF/A-2 §6.2.11.7.3 requires. The change is in the PDF backend (write /ActualText for a text run whose text holds private-use characters, or map them to U+FFFD in /ToUnicode), not in the layout; widening SymbolMap would only hide the two files. The first run of this stage refused 22 of 23 DOCX because the probe set no title; that was the probe, fixed (Title = name).

7. Memory

The timings printed by the probes are indicative only.

8. What the gate found and what was fixed

Fixed (each with a test):

• ADDRESS(r, c, , , "Sheet"): an empty fourth argument was read as R1C1, not A1 (LookupFunctions.cs). 41,000 cells in one legacy workbook went from #REF! to Excel's values.
• ROW(A2) in A2 (and COLUMN, ROWS, COLUMNS of a reference to the cell itself) counted as a circular reference and evaluated to 0 (DependencyWalker.cs): these functions read where a reference is, not its value.
• A PPT/DOC whose edit-time field held a stray timestamp wrote TotalTime beyond xsd:int, so docProps/app.xml failed the schema (OleDocumentProperties.cs): reported as lost instead.
• BIFF8 writer: a workbook with a style sheet that lists no fills or borders raised an untyped ArgumentOutOfRangeException from SaveAs (XlsStyleMap.cs; 2 of 51 Common Crawl workbooks).
• ODT writer: style names that start with a digit, hyphen or full stop are not XML names (OdfConversionSupport.cs); ODS writer: scale-to written beside scale-to-X/Y for a fit-to-page sheet (OdsExporter.Features.cs); ODP writer: negative text insets as padding and connectors without svg:viewBox (OdpExporter.Shapes.cs, OdpExporter.Text.cs).
• Probes: the calc sweep reads XLS, XLSB, ODS and classifies mismatches; the text and PDF stages set a title and register all kinds.

Fixed in the second pass (each with a test):

• Memory of one calc input. The 114 KB workbook that needed ~31 GB held 700 MAXIFS formulas whose value range (C2:C9960) and criteria range (A2:ZW9960, seven million cells) differ in size, which Excel answers #VALUE!. The engine read both ranges before comparing them. The *IFS functions (SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS) now return an error argument in a range position first, then compare the references' sizes, and read only after that. Whatever else a formula reads, the blocks it has read are added up and a formula that passes MaxAllocationBytes (1 GiB by default) stops with calc.limit-exceeded located at the cell and shows #VALUE!; the run goes on. The workbook now recalculates in 1.1 s at 113 MB. (MathAggregates.cs, DescriptiveFunctions.cs, WorkbookResolver.cs, CalcEngine.cs.)
• Whole-column ranges. SUMIFS(Valuation!D:D, Valuation!E:E, A5) answered #VALUE! and XLOOKUP(x, Data!G:G, Data!H:H) too: the lookup array was clipped to the used extent and the return array was not.
• ISFORMULA always answered FALSE: the workbook resolver never implemented the question (WorkbookResolver.IsFormula); 32,000 cells.
• A table's name used alone (VLOOKUP(2, Dow, 2)) was #NAME?; it now stands for the table's data rows, as Dow[#Data] does.
• Document.Open, Workbook.Open, Presentation.Open recover a truncated package (section 1).
• ODF writers (section 3): soft hyphen as U+00AD, a default header before a first-page or even-page one, an empty source row gets one empty cell, one number element per number style and merged adjacent text.

Open items (not fixed here):

1. SUBTOTAL over rows hidden by an autofilter (the 120,401 against 264,410 workbook) was not traced; GETPIVOTDATA against a cached pivot (13 cells in one ODS) answers #REF!; external-workbook references in BIFF become #REF! (convert them to [n]Sheet!A1 with the cached values) — each stays an explained mismatch.
2. XLSX single-cell edit: the schema error count rose in a few worksheets that were already invalid (above); the cause was not established.
3. PDF/A-2a: the private-use ActualText rule in the PDF backend (section 6); the change belongs to OnePDF/OneRender's PDF writer.
4. Text: no option yet to include numbers, booleans and error values of workbook cells for a Tika-like dump.
5. XLOOKUP with an empty lookup value against whole columns: the cached value says it matches an empty cell, ours is #N/A; settled by the Excel grid at the Office run.
6. ODS calc was not rerun after the second-pass fixes (its inputs were deleted with the other outputs); 65.30% stands from the first pass.