| 1 | package main |
| 2 | |
| 3 | import ( |
| 4 | "archive/zip" |
| 5 | "fmt" |
| 6 | "hash/crc32" |
| 7 | "os" |
| 8 | "regexp" |
| 9 | "strconv" |
| 10 | "strings" |
| 11 | ) |
| 12 | |
| 13 | // odsMimetype is the ODF package media type, written as the archive's |
| 14 | // first, uncompressed entry. |
| 15 | const odsMimetype = "application/vnd.oasis.opendocument.spreadsheet" |
| 16 | |
| 17 | // writeODS writes a minimal OpenDocument spreadsheet at path with one |
| 18 | // formula per row in column D, plus the same small fixed lookup table |
| 19 | // calc's own corpusEnv provides in columns A:B, rows 1-3 (see |
| 20 | // lookupTableRows), with C1 left blank. See main.go's package doc |
| 21 | // comment for the overall mechanism. |
| 22 | func writeODS(path string, cases []corpusCase) error { |
| 23 | f, err := os.Create(path) |
| 24 | if err != nil { |
| 25 | return err |
| 26 | } |
| 27 | defer f.Close() |
| 28 | |
| 29 | zw := zip.NewWriter(f) |
| 30 | |
| 31 | // mimetype must be the first entry, stored uncompressed, per the ODF |
| 32 | // package format spec. It is written with CreateRaw, not |
| 33 | // CreateHeader, on purpose: CreateHeader streams the entry and |
| 34 | // therefore sets the "sizes follow in a data descriptor" flag bit |
| 35 | // even for a Store entry, and LibreOffice's ODF package reader |
| 36 | // rejects that outright -- the whole document fails to open with |
| 37 | // "source file could not be loaded", not with a per-cell error. |
| 38 | // Verified against LibreOffice 26.8.0.3: CreateHeader here produces |
| 39 | // an unloadable file, CreateRaw with the sizes and CRC filled in |
| 40 | // produces a loadable one. |
| 41 | mh := &zip.FileHeader{Name: "mimetype", Method: zip.Store} |
| 42 | mh.CRC32 = crc32.ChecksumIEEE([]byte(odsMimetype)) |
| 43 | mh.CompressedSize64 = uint64(len(odsMimetype)) |
| 44 | mh.UncompressedSize64 = uint64(len(odsMimetype)) |
| 45 | mw, err := zw.CreateRaw(mh) |
| 46 | if err != nil { |
| 47 | return err |
| 48 | } |
| 49 | if _, err := mw.Write([]byte(odsMimetype)); err != nil { |
| 50 | return err |
| 51 | } |
| 52 | |
| 53 | cw, err := zw.Create("content.xml") |
| 54 | if err != nil { |
| 55 | return err |
| 56 | } |
| 57 | if _, err := cw.Write([]byte(contentXML(cases))); err != nil { |
| 58 | return err |
| 59 | } |
| 60 | |
| 61 | mfw, err := zw.Create("META-INF/manifest.xml") |
| 62 | if err != nil { |
| 63 | return err |
| 64 | } |
| 65 | if _, err := mfw.Write([]byte(manifestXML)); err != nil { |
| 66 | return err |
| 67 | } |
| 68 | |
| 69 | return zw.Close() |
| 70 | } |
| 71 | |
| 72 | const manifestXML = `<?xml version="1.0" encoding="UTF-8"?> |
| 73 | <manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0" manifest:version="1.2"> |
| 74 | <manifest:file-entry manifest:full-path="/" manifest:version="1.2" manifest:media-type="application/vnd.oasis.opendocument.spreadsheet"/> |
| 75 | <manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/> |
| 76 | </manifest:manifest> |
| 77 | ` |
| 78 | |
| 79 | // lookupTableRows is the fixed A:B data VLOOKUP/INDEX/MATCH cases |
| 80 | // reference as "A1:B3" (both columns) or "A1:A3" (the number column |
| 81 | // alone) -- byte-identical to the table |
| 82 | // internal/sheets/calc/corpus_test.go's corpusEnv and |
| 83 | // internal/sheets/xlsx/oracle_excelize_test.go's newOracleWorkbook both |
| 84 | // set up, so a generated corpus entry's Expr can be evaluated unchanged |
| 85 | // by both of those harnesses. C1 is deliberately left blank so it reads |
| 86 | // as a genuinely empty cell for the COUNT(A1,C1)/COUNTA(A1,C1) cases; |
| 87 | // each row's own formula therefore goes in column D, clear of the |
| 88 | // table. |
| 89 | var lookupTableRows = []struct { |
| 90 | num float64 |
| 91 | text string |
| 92 | }{ |
| 93 | {10, "ten"}, |
| 94 | {20, "twenty"}, |
| 95 | {30, "thirty"}, |
| 96 | } |
| 97 | |
| 98 | // formulaColumnIndex is the zero-based CSV field the formula cell lands |
| 99 | // in: columns A, B and C are the support table (and its deliberately |
| 100 | // blank C1), so column D is index 3. readResults reads this field, not |
| 101 | // field 0. |
| 102 | const formulaColumnIndex = 3 |
| 103 | |
| 104 | func contentXML(cases []corpusCase) string { |
| 105 | var rows strings.Builder |
| 106 | for i, c := range cases { |
| 107 | rows.WriteString(" <table:table-row>") |
| 108 | if i < len(lookupTableRows) { |
| 109 | rows.WriteString(numberCell(lookupTableRows[i].num)) |
| 110 | rows.WriteString(stringCell(lookupTableRows[i].text)) |
| 111 | rows.WriteString(`<table:table-cell/>`) |
| 112 | } else { |
| 113 | rows.WriteString(`<table:table-cell table:number-columns-repeated="3"/>`) |
| 114 | } |
| 115 | // office:value is deliberately poisoned with a value no case can |
| 116 | // produce, so a run in which LibreOffice failed to recalculate |
| 117 | // would come back as 999999 everywhere rather than as plausible |
| 118 | // answers. See main's poisonedCacheValue check. |
| 119 | rows.WriteString(fmt.Sprintf( |
| 120 | `<table:table-cell table:formula="of:=%s" office:value-type="float" office:value="%s"><text:p>%s</text:p></table:table-cell>`, |
| 121 | escapeXML(toODFFormula(c.Expr)), poisonedCacheValue, poisonedCacheValue)) |
| 122 | rows.WriteString("</table:table-row>\n") |
| 123 | } |
| 124 | // xmlns:of is load-bearing, not decoration: the "of:" in each cell's |
| 125 | // table:formula="of:=..." is an XML namespace prefix, and without |
| 126 | // this declaration LibreOffice does not recognise the formula |
| 127 | // grammar and every single cell comes back as Err:510 rather than a |
| 128 | // value. Verified against LibreOffice 26.8.0.3 by running the same |
| 129 | // document with and without it. |
| 130 | return `<?xml version="1.0" encoding="UTF-8"?> |
| 131 | <office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" |
| 132 | xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" |
| 133 | xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" |
| 134 | xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2" |
| 135 | office:version="1.2"> |
| 136 | <office:body> |
| 137 | <office:spreadsheet> |
| 138 | <!-- Excel's criteria semantics, stated per DOCUMENT. |
| 139 | Without this element LibreOffice falls back to ODF's legacy |
| 140 | default — regular expressions ON, wildcards OFF — so a |
| 141 | criterion of "t*" is the REGEX t*, which matches nothing in |
| 142 | "ten". Measured 2026-09-03: every wildcard case in the list |
| 143 | came back 0, and the first reading of that was that |
| 144 | LibreOffice's profile disagreed with Excel and could not be |
| 145 | the oracle for them. It was not a profile and it does not |
| 146 | disagree; the generator was writing a document that asked |
| 147 | the wrong question. Corrected 2026-09-04 after review, which |
| 148 | rebuilt the same file with this one element and got Excel's |
| 149 | answers from the same binary. --> |
| 150 | <table:calculation-settings table:use-wildcards="true" table:use-regular-expressions="false"/> |
| 151 | <table:table table:name="Corpus"> |
| 152 | ` + rows.String() + ` </table:table> |
| 153 | </office:spreadsheet> |
| 154 | </office:body> |
| 155 | </office:document-content> |
| 156 | ` |
| 157 | } |
| 158 | |
| 159 | // numberCell/stringCell write a literal (not formula) value cell -- the |
| 160 | // lookup table's own data, which must be reflected in the file's actual |
| 161 | // value/type attributes, not just visible text, or LibreOffice would not |
| 162 | // see it as a number/string for VLOOKUP/INDEX/MATCH to compare against. |
| 163 | func numberCell(n float64) string { |
| 164 | s := strconv.FormatFloat(n, 'g', -1, 64) |
| 165 | return fmt.Sprintf(`<table:table-cell office:value-type="float" office:value="%s"><text:p>%s</text:p></table:table-cell>`, s, s) |
| 166 | } |
| 167 | |
| 168 | func stringCell(s string) string { |
| 169 | return fmt.Sprintf(`<table:table-cell office:value-type="string"><text:p>%s</text:p></table:table-cell>`, s) |
| 170 | } |
| 171 | |
| 172 | // escapeXML escapes a formula for use inside an XML attribute value. |
| 173 | // Every case with a string argument contains double quotes, which would |
| 174 | // otherwise close the table:formula attribute early. |
| 175 | func escapeXML(s string) string { |
| 176 | s = strings.ReplaceAll(s, "&", "&") |
| 177 | s = strings.ReplaceAll(s, "<", "<") |
| 178 | s = strings.ReplaceAll(s, ">", ">") |
| 179 | return strings.ReplaceAll(s, `"`, """) |
| 180 | } |
| 181 | |
| 182 | // odfFunctionNames maps a function's Excel/English spelling to the name |
| 183 | // LibreOffice actually answers to inside an ODF of: formula. ODF's own |
| 184 | // function set is a standard; functions Microsoft added after it are |
| 185 | // vendor-namespaced, and LibreOffice writes and reads them under the |
| 186 | // COM.MICROSOFT. prefix. Calling one by its bare Excel name in an of: |
| 187 | // formula is not an error LibreOffice reports as such -- it is simply an |
| 188 | // unknown name, and the cell comes back #NAME?. |
| 189 | // |
| 190 | // This was found the hard way on this tool's first real run: IFS and |
| 191 | // CONCAT both came back #NAME?, which classify would have happily |
| 192 | // recorded as the corpus's expected answer, turning "LibreOffice has |
| 193 | // never heard of this ODF spelling" into "a real spreadsheet says these |
| 194 | // functions are errors". checkResults (soffice.go) now refuses a #NAME? |
| 195 | // result outright for exactly that reason. |
| 196 | // |
| 197 | // Add an entry here whenever a case uses a post-ODF Microsoft function. |
| 198 | // The symptom is always the same: a #NAME? that fails the run. |
| 199 | var odfFunctionNames = map[string]string{ |
| 200 | "IFS": "COM.MICROSOFT.IFS", |
| 201 | "CONCAT": "COM.MICROSOFT.CONCAT", |
| 202 | } |
| 203 | |
| 204 | // funcCallPattern matches an identifier immediately followed by "(", |
| 205 | // i.e. a function call's name. Like the comma substitution below it does |
| 206 | // not skip quoted spans; no case has a bare word followed by "(" inside |
| 207 | // a string literal. |
| 208 | var funcCallPattern = regexp.MustCompile(`[A-Za-z_][A-Za-z0-9_.]*\(`) |
| 209 | |
| 210 | // toODFFormula translates a comma-argument-separated Excel-style formula |
| 211 | // (this tool's own case list, and this project's own formula syntax -- |
| 212 | // see internal/sheets/calc/parse.go) into ODF's own formula syntax, which |
| 213 | // uses semicolons between arguments. The translation is required, not |
| 214 | // cosmetic: verified against LibreOffice 26.8.0.3, an of:= formula with |
| 215 | // comma separators evaluates to #NAME?, not to a value. |
| 216 | // |
| 217 | // Two things this deliberately does NOT do, both checked by measurement |
| 218 | // rather than assumed: |
| 219 | // - It does not rewrite A1-style references into ODF's bracketed |
| 220 | // [.A1:.B3] form. LibreOffice accepts the plain form and resolves it |
| 221 | // identically (VLOOKUP(20;A1:B3;2;FALSE()) and |
| 222 | // VLOOKUP(20;[.A1:.B3];2;FALSE()) both return "twenty"). |
| 223 | // - It does not rewrite bare TRUE/FALSE into TRUE()/FALSE(). |
| 224 | // LibreOffice accepts the bare form (IF(TRUE;"yes";"no") returns |
| 225 | // "yes"). |
| 226 | // |
| 227 | // It DOES rewrite function names that ODF vendor-namespaces -- see |
| 228 | // odfFunctionNames. |
| 229 | // |
| 230 | // It is a blunt, comma-to-semicolon substitution -- wrong for any |
| 231 | // formula whose comma appears inside a quoted string argument (a text |
| 232 | // literal that itself contains a comma). None of this tool's own cases |
| 233 | // do; a human adding one that does needs to rewrite this function to |
| 234 | // skip quoted spans, or avoid the comma. |
| 235 | func toODFFormula(expr string) string { |
| 236 | s := strings.ReplaceAll(expr, ",", ";") |
| 237 | return funcCallPattern.ReplaceAllStringFunc(s, func(call string) string { |
| 238 | name := strings.ToUpper(call[:len(call)-1]) |
| 239 | if odf, ok := odfFunctionNames[name]; ok { |
| 240 | return odf + "(" |
| 241 | } |
| 242 | return call |
| 243 | }) |
| 244 | } |
| 245 | |
amadan