Repository Public License
1// ORIX Convert — Packages/EngineData/Tests/EngineDataTests/SpreadsheetTests.swift2// Description: XLSX / ODS readers against hand-assembled packages that mimic what Excel and LibreOffice write3// (shared strings, date styles, cached formula results, merged cells, 1904 dates, hidden sheets),4// the writers' edge cases, and hostile packages (bombs, truncation, bad checksums).5// Author: Simon-Pierre Boucher — contact@spboucher.ai6import Foundation7import Testing8import ConvertCore9@testable import EngineData1011enum HandMade {12 static let ns = "http://schemas.openxmlformats.org"1314 /// A workbook written by hand, part by part, the way Excel lays it out.15 static func xlsx(at url: URL, date1904: Bool = false, extras: Bool = true) throws {16 let z = try ZipWriter(url: url)17 try z.add("[Content_Types].xml", "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?><Types xmlns=\"\(ns)/package/2006/content-types\"><Default Extension=\"rels\" ContentType=\"application/vnd.openxmlformats-package.relationships+xml\"/><Default Extension=\"xml\" ContentType=\"application/xml\"/><Override PartName=\"/xl/workbook.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\"/></Types>")18 try z.add("_rels/.rels", "<?xml version=\"1.0\"?><Relationships xmlns=\"\(ns)/package/2006/relationships\"><Relationship Id=\"rId1\" Type=\"\(ns)/officeDocument/2006/relationships/officeDocument\" Target=\"xl/workbook.xml\"/></Relationships>")19 try z.add("xl/workbook.xml", "<?xml version=\"1.0\"?><workbook xmlns=\"\(ns)/spreadsheetml/2006/main\" xmlns:r=\"\(ns)/officeDocument/2006/relationships\"><workbookPr\(date1904 ? " date1904=\"1\"" : "")/><sheets><sheet name=\"Data\" sheetId=\"1\" r:id=\"rId1\"/><sheet name=\"Secret & Hidden\" sheetId=\"2\" state=\"hidden\" r:id=\"rId2\"/><sheet name=\"Third\" sheetId=\"3\" r:id=\"rId3\"/></sheets><definedNames><definedName name=\"Total\">Data!$C$2</definedName></definedNames></workbook>")20 try z.add("xl/_rels/workbook.xml.rels", "<?xml version=\"1.0\"?><Relationships xmlns=\"\(ns)/package/2006/relationships\"><Relationship Id=\"rId1\" Type=\"\(ns)/officeDocument/2006/relationships/worksheet\" Target=\"worksheets/sheet1.xml\"/><Relationship Id=\"rId2\" Type=\"\(ns)/officeDocument/2006/relationships/worksheet\" Target=\"worksheets/sheet2.xml\"/><Relationship Id=\"rId3\" Type=\"\(ns)/officeDocument/2006/relationships/worksheet\" Target=\"/xl/worksheets/sheet3.xml\"/><Relationship Id=\"rId4\" Type=\"\(ns)/officeDocument/2006/relationships/styles\" Target=\"styles.xml\"/><Relationship Id=\"rId5\" Type=\"\(ns)/officeDocument/2006/relationships/sharedStrings\" Target=\"sharedStrings.xml\"/></Relationships>")21 try z.add("xl/styles.xml", "<?xml version=\"1.0\"?><styleSheet xmlns=\"\(ns)/spreadsheetml/2006/main\"><numFmts count=\"2\"><numFmt numFmtId=\"164\" formatCode=\"dd/mm/yyyy\\ hh:mm:ss\"/><numFmt numFmtId=\"165\" formatCode=\"#,##0.00\\ [$€-40C];[Red]-#,##0.00\\ "d"\"/></numFmts><cellStyleXfs count=\"1\"><xf numFmtId=\"14\"/></cellStyleXfs><cellXfs count=\"6\"><xf numFmtId=\"0\"/><xf numFmtId=\"14\"/><xf numFmtId=\"164\"/><xf numFmtId=\"2\"/><xf numFmtId=\"20\"/><xf numFmtId=\"165\"/></cellXfs></styleSheet>")22 try z.add("xl/sharedStrings.xml", "<?xml version=\"1.0\"?><sst xmlns=\"\(ns)/spreadsheetml/2006/main\" count=\"9\" uniqueCount=\"9\"><si><t>name</t></si><si><t>when</t></si><si><t>amount</t></si><si><t>ok</t></si><si><t>stamp</t></si><si><t>calc</t></si><si><t>note</t></si><si><r><rPr><b/></rPr><t>Rich </t></r><r><t xml:space=\"preserve\">text & more</t></r><rPh sb=\"0\" eb=\"1\"><t>PHONETIC</t></rPh></si><si><t>line1_x000D__x000A_line2 _x005F_x0041_</t></si></sst>")23 let serial = date1904 ? 43889 : 4535124 try z.add("xl/worksheets/sheet1.xml", """25 <?xml version="1.0"?><worksheet xmlns="\(ns)/spreadsheetml/2006/main"><dimension ref="A1:H4"/><cols><col min="3" max="4" hidden="1"/></cols><sheetData>26 <row r="1"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v></c><c r="C1" t="s"><v>2</v></c><c r="D1" t="s"><v>3</v></c><c r="E1" t="s"><v>4</v></c><c r="F1" t="s"><v>5</v></c><c r="G1" t="s"><v>6</v></c><c r="H1" t="inlineStr"><is><t>clock</t></is></c></row>27 <row r="2"><c r="A2" t="s"><v>7</v></c><c r="B2" s="1"><v>\(serial)</v></c><c r="C2" s="5"><v>12.5</v></c><c r="D2" t="b"><v>1</v></c><c r="E2" s="2"><v>\(serial).5</v></c><c r="F2"><f>C2*2</f><v>25</v></c><c r="G2" t="inlineStr"><is><t>inline <text></t></is></c><c r="H2" s="4"><v>0.75</v></c></row>28 <row r="3" hidden="1"><c r="A3" t="s"><v>8</v></c><c r="B3" s="1"><v>\(serial + 1)</v></c><c r="C3" s="3"><v>-3</v></c><c r="D3" t="b"><v>0</v></c><c r="F3" t="e"><f>1/0</f><v>#DIV/0!</v></c><c r="G3" t="str"><f>A2&"!"</f><v>Rich text & more!</v></c></row>29 <row r="4"><c r="A4" t="inlineStr"><is><t>merged</t></is></c><c r="C4"><v>1E3</v></c><c r="F4"><f t="shared" si="0"/><v>7</v></c></row>30 </sheetData><mergeCells count="1"><mergeCell ref="A4:B4"/></mergeCells><conditionalFormatting sqref="C2:C4"><cfRule type="cellIs" priority="1" operator="greaterThan"><formula>10</formula></cfRule></conditionalFormatting><dataValidations count="1"><dataValidation type="list" sqref="D2"><formula1>"a,b"</formula1></dataValidation></dataValidations><hyperlinks><hyperlink ref="A2" location="Third!A1"/></hyperlinks></worksheet>31 """)32 try z.add("xl/worksheets/sheet2.xml", "<?xml version=\"1.0\"?><worksheet xmlns=\"\(ns)/spreadsheetml/2006/main\"><sheetData><row r=\"1\"><c r=\"A1\" t=\"inlineStr\"><is><t>k</t></is></c></row><row r=\"2\"><c r=\"A2\"><v>1</v></c></row></sheetData></worksheet>")33 // Prefixed elements, no cell references, a gap in row numbers.34 try z.add("xl/worksheets/sheet3.xml", "<?xml version=\"1.0\"?><x:worksheet xmlns:x=\"\(ns)/spreadsheetml/2006/main\"><x:sheetData><x:row><x:c t=\"inlineStr\"><x:is><x:t>a</x:t></x:is></x:c><x:c t=\"inlineStr\"><x:is><x:t>b</x:t></x:is></x:c></x:row><x:row r=\"4\"><x:c><x:v>1</x:v></x:c><x:c><x:v>2.5</x:v></x:c></x:row></x:sheetData></x:worksheet>")35 if extras {36 try z.add("xl/charts/chart1.xml", "<c/>")37 try z.add("xl/media/image1.png", "not really a png")38 try z.add("xl/comments1.xml", "<comments/>")39 try z.add("xl/pivotTables/pivotTable1.xml", "<p/>")40 try z.add("xl/vbaProject.bin", "macro bytes")41 try z.add("../../outside.txt", "a traversal-shaped name is only ever a dictionary key")42 }43 try z.finish()44 }4546 static func ods(at url: URL, body: String) throws {47 let z = try ZipWriter(url: url)48 try z.addStored("mimetype", Array("application/vnd.oasis.opendocument.spreadsheet".utf8))49 let o = "urn:oasis:names:tc:opendocument:xmlns"50 try z.add("content.xml", "<?xml version=\"1.0\" encoding=\"UTF-8\"?><office:document-content xmlns:office=\"\(o):office:1.0\" xmlns:table=\"\(o):table:1.0\" xmlns:text=\"\(o):text:1.0\" xmlns:style=\"\(o):style:1.0\" xmlns:draw=\"\(o):drawing:1.0\"><office:automatic-styles><style:style style:name=\"ta2\" style:family=\"table\"><style:table-properties table:display=\"false\"/></style:style></office:automatic-styles><office:body><office:spreadsheet>\(body)</office:spreadsheet></office:body></office:document-content>")51 try z.add("META-INF/manifest.xml", "<?xml version=\"1.0\"?><manifest:manifest xmlns:manifest=\"\(o):manifest:1.0\"/>")52 try z.finish()53 }54}5556@Suite("Spreadsheets")57struct SpreadsheetTests {58 @Test(arguments: [false, true])59 func excelStyleWorkbookIsReadCorrectly(date1904: Bool) async throws {60 let scratch = Scratch("xlsx")61 let url = scratch.file("book.xlsx")62 try HandMade.xlsx(at: url, date1904: date1904)63 let p = try await DataPreview.preview(url: url, format: .xlsx)64 #expect(p.tableNames == ["Data", "Secret & Hidden", "Third"])65 #expect(p.hiddenTableNames == ["Secret & Hidden"])66 #expect(p.table == "Data")67 #expect(p.columns.map(\.name) == ["name", "when", "amount", "ok", "stamp", "calc", "note", "clock"])68 #expect(p.columns.map(\.kind) == [.string, .date, .double, .boolean, .timestamp, .integer, .string, .time])69 #expect(p.rows.count == 3)70 #expect(p.rows[0] == ["Rich text & more", "2024-02-29", "12.5", "true", "2024-02-29T12:00:00", "25", "inline <text>", "18:00:00"])71 #expect(p.rows[1] == ["line1\r\nline2 _x0041_", "2024-03-01", "-3.0", "false", nil, nil, "Rich text & more!", nil])72 #expect(p.rows[2] == ["merged", nil, "1000.0", nil, nil, "7", nil, nil])73 #expect(Set(p.notes.map(\.code)).isSuperset(of: ["formulas-dropped", "error-cells", "merged-cells", "hidden-rows-columns", "sheet-features-dropped", "workbook-content-dropped", "sheets-dropped"]))74 let third = try await DataPreview.preview(url: url, format: .xlsx, table: "#3")75 #expect(third.columns.map(\.name) == ["a", "b"])76 // Rows 2 and 3 of the sheet are absent from the file: they are empty rows of the table.77 #expect(third.rows == [[nil, nil], [nil, nil], ["1.0", "2.5"]] || third.rows == [[nil, nil], [nil, nil], ["1", "2.5"]])78 let hidden = try await DataPreview.preview(url: url, format: .xlsx, table: "secret & hidden")79 #expect(hidden.rows == [["1"]])80 #expect(hidden.notes.contains { $0.code == "hidden-sheet" })81 }8283 @Test func preflightAndInspectionReportWhatAWorkbookHolds() async throws {84 let scratch = Scratch("xlsx")85 let url = scratch.file("book.xlsx")86 try HandMade.xlsx(at: url)87 let engine = DataEngine()88 let request = ConversionRequest(source: url, sourceFormat: .xlsx, destinationFormat: .csv)89 let warnings = engine.preflight(request, inspection: nil)90 func message(_ code: String) -> String { warnings.first { $0.code == code }?.message ?? "" }91 #expect(message("sheets-dropped").contains("“Secret & Hidden”") && message("sheets-dropped").contains("“Third”"))92 #expect(message("formulas-dropped").contains("4 formula cells"))93 #expect(message("merged-cells").contains("1 merged range"))94 #expect(message("error-cells").contains("1 cell"))95 #expect(message("workbook-content-dropped").contains("1 chart") && message("workbook-content-dropped").contains("VBA macros") && message("workbook-content-dropped").contains("1 pivot table"))96 #expect(message("sheet-features-dropped").contains("1 data-validation rule") && message("sheet-features-dropped").contains("1 conditional-formatting rule") && message("sheet-features-dropped").contains("1 hyperlink"))97 #expect(warnings.first { $0.code == "formulas-dropped" }?.severity == .dataLoss)98 let asText = engine.preflight(ConversionRequest(source: url, sourceFormat: .xlsx, destinationFormat: .csv, options: .init(["formulas.as.text": .bool(true)])), inspection: nil)99 #expect(asText.contains { $0.code == "formulas-as-text" } && !asText.contains { $0.code == "formulas-dropped" })100 // To a workbook target every sheet is kept by default: no sheet is reported as dropped.101 #expect(!engine.preflight(ConversionRequest(source: url, sourceFormat: .xlsx, destinationFormat: .ods), inspection: nil).contains { $0.code == "sheets-dropped" })102103 let info = try await engine.inspect(url, format: .xlsx)104 #expect(info.sheetNames == ["Data", "Secret & Hidden", "Third"])105 #expect(info.rowCount == 4 && info.columnCount == 8)106 func field(_ g: String, _ k: String) -> String { info.fields.first { $0.group == g && $0.key == k }?.value ?? "" }107 #expect(field("Sheets", "Data") == "4 rows × 8 columns, 4 formulas, 1 merged ranges, 1 hidden rows, 2 hidden columns, 1 data validations, 1 conditional formats")108 #expect(field("Sheets", "Secret & Hidden").contains("hidden"))109 #expect(field("Workbook", "Charts") == "1" && field("Workbook", "Macros").contains("never executed") && field("Workbook", "Date system") == "1900")110 }111112 @Test func formulasAsTextAndSheetSelection() async throws {113 let scratch = Scratch("xlsx")114 let url = scratch.file("book.xlsx")115 try HandMade.xlsx(at: url)116 let c = try await runConversion(url, .xlsx, to: .csv, in: scratch, options: ["formulas.as.text": .bool(true)])117 let text = try String(contentsOf: c.file, encoding: .utf8)118 #expect(text.contains("=C2*2") && text.contains("=1/0") && text.contains("\"=A2&\"\"!\"\"\""))119 #expect(c.warnings.all.first { $0.code == "formulas-as-text" }?.message.contains("1 of them belong to a shared formula") == true)120 let all = try await runConversion(url, .xlsx, to: .csv, in: scratch, options: ["sheets.all": .bool(true)], name: "book")121 #expect(all.output.files.map(\.lastPathComponent) == ["book-Data.csv", "book-Secret & Hidden.csv", "book-Third.csv"])122 #expect(all.output.facts["tables"] == "3" && all.output.facts["rows.1"] == "3" && all.output.facts["rows.2"] == "1" && all.output.facts["rows.3"] == "3")123 #expect(!all.warnings.codes.contains("sheets-dropped"))124 for check in try await DataEngine().validate(all.output, request: all.request, source: nil) { #expect(check.passed, "\(check.name): \(check.detail)") }125 let db = try await runConversion(url, .xlsx, to: .sqlite, in: scratch)126 let info = try await DataEngine().inspect(db.file, format: .sqlite)127 #expect(info.sheetNames == ["Data", "Secret & Hidden", "Third"])128 await #expect(throws: ConversionError.self) { _ = try await runConversion(url, .xlsx, to: .csv, in: scratch, options: ["sheet": .string("Nope")]) }129 await #expect(throws: ConversionError.self) { _ = try await runConversion(url, .xlsx, to: .csv, in: scratch, options: ["sheet": .string("#9")]) }130 let noHeader = try await DataPreview.preview(url: url, format: .xlsx, table: "Third", options: .init(["header": .string("no")]))131 #expect(noHeader.columns.map(\.name) == ["column1", "column2"] && noHeader.rows.count == 4 && noHeader.columns[0].kind == .string)132 let skipped = try await DataPreview.preview(url: url, format: .xlsx, options: .init(["header.row": .int(2), "header": .string("no")]))133 #expect(skipped.rows.count == 3 && skipped.rows[0][0] == "Rich text & more")134 }135136 @Test func numberFormatClassification() {137 #expect(XLSXReader.classify(formatCode: "yyyy-mm-dd") == .date)138 #expect(XLSXReader.classify(formatCode: "d-mmm-yy") == .date)139 #expect(XLSXReader.classify(formatCode: "mmm yyyy") == .date)140 #expect(XLSXReader.classify(formatCode: "h:mm AM/PM") == .time)141 #expect(XLSXReader.classify(formatCode: "[h]:mm:ss") == .time)142 #expect(XLSXReader.classify(formatCode: "mm:ss.0") == .time)143 #expect(XLSXReader.classify(formatCode: "m/d/yy h:mm") == .dateTime)144 #expect(XLSXReader.classify(formatCode: "0.00E+00") == .general)145 #expect(XLSXReader.classify(formatCode: "#,##0.00 \"days\"") == .general)146 #expect(XLSXReader.classify(formatCode: "[$-409]General") == .general)147 #expect(XLSXReader.classify(formatCode: "0.0\\d") == .general)148 #expect(XLSXReader.classify(formatCode: "[Red]#,##0;[Blue]-#,##0") == .general)149 #expect(XLSXReader.classify(formatCode: "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy") == .date)150 #expect(XLSXReader.builtinKind(14) == .date && XLSXReader.builtinKind(22) == .dateTime && XLSXReader.builtinKind(21) == .time && XLSXReader.builtinKind(4) == .general)151 }152153 @Test func writerEscapesAndReportsWhatSpreadsheetsCannotHold() async throws {154 let scratch = Scratch("xlsx")155 let cols: [TableColumn] = [.init(name: "s", kind: .string), .init(name: "n", kind: .double), .init(name: "i", kind: .integer), .init(name: "d", kind: .date), .init(name: "t", kind: .timestamp), .init(name: "z", kind: .timestampTZ), .init(name: "c", kind: .time), .init(name: "b", kind: .binary), .init(name: "x", kind: .decimal)]156 let rows: [[Cell]] = [157 [.string("ctl \u{1}\u{B} tab\t nl\n cr\r _x0041_ <&>\"' \u{FFFE}"), .double(.nan), .int(9_007_199_254_740_993), .date(Canonical.day(1899, 12, 31)), .timestamp(Canonical.ts(2024, 1, 2, 3, 4, 5, 123_456)), .timestampTZ(Canonical.ts(2024, 1, 2, 3, 4, 5)), .time(11_045_250_000), .bytes(Data([1, 2, 3])), .decimal("1.500")],158 [.string("=SUM(A1)"), .double(-.infinity), .int(-5), .date(Canonical.day(1900, 3, 1)), .timestamp(Canonical.ts(1850, 1, 1, 0, 0, 0)), .null, .time(1), .null, .decimal("0.1234567890123456789")],159 [.string(String(repeating: "é", count: 40_000)), .double(2.5), .int(9_007_199_254_740_992), .date(Canonical.day(9999, 12, 31)), .null, .null, .null, .null, .decimal("123456789012345678901234567890")],160 ]161 for format in [FormatID.xlsx, .ods] {162 let url = scratch.file("w.\(format.rawValue)")163 var tally: LossTally164 if format == .xlsx {165 let w = try XLSXWriter(url: url, options: .init(formulaGuard: true))166 try w.beginSheet(name: "A/B*C?[x]:" + String(repeating: "n", count: 40), columns: cols, header: true)167 for r in rows { try w.writeRow(r) }168 try w.beginSheet(name: "a/b*c?[x]:" + String(repeating: "N", count: 40), columns: [cols[0]], header: false)169 try w.finish()170 tally = w.tally171 #expect(w.renamedSheets.count == 2)172 } else {173 let w = try ODSWriter(url: url, options: .init(formulaGuard: true))174 try w.beginSheet(name: "Sheet", columns: cols, header: true)175 for r in rows { try w.writeRow(r) }176 try w.finish()177 tally = w.tally178 }179 let p = try await DataPreview.preview(url: url, format: format)180 #expect(p.rows.count == 3)181 #expect(p.rows[0][0] == (format == .xlsx ? "ctl \u{1}\u{B} tab\t nl\n cr\r _x0041_ <&>\"' \u{FFFE}" : "ctl \u{FFFD}\u{FFFD} tab\t nl\n cr\r _x0041_ <&>\"' \u{FFFE}"))182 #expect(p.rows[1][0] == "'=SUM(A1)")183 #expect(p.rows[2][0]?.count == 40_000)184 #expect(p.rows[0][1] == nil && p.rows[1][1] == nil && p.rows[2][1] == "2.5")185 #expect(p.rows.map { $0[2] } == ["9007199254740993", "-5", "9007199254740992"])186 #expect(p.rows[1][3] == "1900-03-01" && p.rows[2][3] == "9999-12-31")187 #expect(p.rows[0][3] == "1899-12-31")188 #expect(p.rows[0][4] == (format == .xlsx ? "2024-01-02T03:04:05.123" : "2024-01-02T03:04:05.123456"))189 #expect(p.rows[1][4] == "1850-01-01T00:00:00")190 #expect(p.rows[0][5] == "2024-01-02T03:04:05")191 #expect(p.rows[0][6] == "03:04:05.25")192 #expect(p.rows[0][7] == "AQID")193 #expect(Validator.equivalent(p.rows[0][8], "1.5") && p.rows[1][8] == "0.1234567890123456789" && p.rows[2][8] == "123456789012345678901234567890")194 #expect(tally.counts["non-finite-dropped"] == 2 && tally.counts["integer-as-text"] == 2 && tally.counts["decimal-as-text"] == 1)195 #expect(tally.counts["csv-injection-guarded"] == 1 && tally.counts["binary-as-text"] == 1)196 if format == .xlsx {197 #expect(tally.counts["date-as-text"] == 2 && tally.counts["sub-millisecond-dropped"] == 2 && tally.counts["cell-too-long"] == 1)198 #expect(p.tableNames.count == 2 && p.tableNames.allSatisfy { $0.count <= 31 && !$0.contains("/") } && Set(p.tableNames.map { $0.lowercased() }).count == 2)199 } else {200 #expect(tally.counts["control-characters-replaced"] == 2)201 }202 }203 }204205 @Test func sheetOverflowStopsOrSplits() async throws {206 let scratch = Scratch("xlsx")207 let cols = [TableColumn(name: "n", kind: .integer)]208 let strict = try XLSXWriter(url: scratch.file("strict.xlsx"), options: .init(maxRows: 4))209 try strict.beginSheet(name: "S", columns: cols, header: true)210 for i in 0..<3 { try strict.writeRow([.int(Int64(i))]) }211 #expect(throws: ConversionError.self) { try strict.writeRow([.int(3)]) }212 for format in [FormatID.xlsx, .ods] {213 let url = scratch.file("split.\(format.rawValue)")214 if format == .xlsx {215 let w = try XLSXWriter(url: url, options: .init(splitOverflow: true, maxRows: 4))216 try w.beginSheet(name: "S", columns: cols, header: true)217 for i in 0..<8 { try w.writeRow([.int(Int64(i))]) }218 #expect(try w.finish() == ["S", "S (2)", "S (3)"])219 #expect(w.splitSheets == 2)220 let reader = try XLSXReader(url: url)221 var values: [Cell] = []222 for i in reader.sheets.indices { _ = try reader.read(sheet: i) { row in values.append(contentsOf: row); return true } }223 #expect(values == [.string("n"), .int(0), .int(1), .int(2), .string("n"), .int(3), .int(4), .int(5), .string("n"), .int(6), .int(7)])224 }225 }226 // Through the engine: a table larger than a sheet stops with a clear error unless splitting is chosen.227 let big = scratch.file("big.csv")228 try ("n\n" + (0..<1_048_580).map(String.init).joined(separator: "\n") + "\n").write(to: big, atomically: true, encoding: .utf8)229 do { _ = try await runConversion(big, .csv, to: .xlsx, in: scratch); Issue.record("no error") } catch let e as ConversionError {230 guard case .limitExceeded(let m) = e else { Issue.record("unexpected \(e)"); return }231 #expect(m.contains("1048576"))232 }233 let split = try await runConversion(big, .csv, to: .xlsx, in: scratch, options: ["sheet.overflow": .string("split")])234 #expect(split.warnings.codes.contains("sheet-split") && split.output.facts["rows"] == "1048580")235 for check in try await DataEngine().validate(split.output, request: split.request, source: nil) where check.name == "row-count" || check.name == "reopen" { #expect(check.passed, "\(check.name): \(check.detail)") }236 }237238 @Test func odsTypedCellsRepeatsAndHiddenTables() async throws {239 let scratch = Scratch("ods")240 let url = scratch.file("t.ods")241 try HandMade.ods(at: url, body: """242 <table:table table:name="Main"><table:table-column table:number-columns-repeated="16384"/>243 <table:table-row><table:table-cell office:value-type="string"><text:p>k</text:p></table:table-cell><table:table-cell office:value-type="string"><text:p>v</text:p></table:table-cell><table:table-cell office:value-type="string"><text:p>d</text:p></table:table-cell><table:table-cell office:value-type="string"><text:p>t</text:p></table:table-cell><table:table-cell table:number-columns-repeated="16380"/></table:table-row>244 <table:table-row><table:table-cell office:value-type="string"><text:p>a<text:s text:c="3"/>b<text:tab/>c<text:line-break/>d</text:p><text:p>second <text:span>para</text:span></text:p><office:annotation><text:p>a comment</text:p></office:annotation></table:table-cell><table:table-cell office:value-type="float" office:value="1.5"><text:p>1,50</text:p></table:table-cell><table:table-cell office:value-type="date" office:date-value="2024-02-29"><text:p>29/02/24</text:p></table:table-cell><table:table-cell office:value-type="time" office:time-value="PT13H45M10.5S"><text:p>13:45</text:p></table:table-cell></table:table-row>245 <table:table-row table:number-rows-repeated="2"><table:table-cell office:value-type="percentage" office:value="0.25" table:formula="of:=[.B2]/6"><text:p>25%</text:p></table:table-cell><table:table-cell office:value-type="float" office:value="7" table:number-columns-repeated="2"><text:p>7</text:p></table:table-cell><table:table-cell office:value-type="boolean" office:boolean-value="true"><text:p>TRUE</text:p></table:table-cell></table:table-row>246 <table:table-row><table:table-cell table:number-columns-spanned="2" office:value-type="currency" office:value="9.99"><text:p>9,99 €</text:p></table:table-cell><table:covered-table-cell/><table:table-cell office:value-type="date" office:date-value="2024-02-29T13:45:10.123456"><text:p>x</text:p></table:table-cell></table:table-row>247 <table:table-row table:number-rows-repeated="1048570"><table:table-cell table:number-columns-repeated="16384"/></table:table-row>248 </table:table><table:table table:name="Hidden" table:style-name="ta2"><table:table-row><table:table-cell office:value-type="float" office:value="1"><text:p>1</text:p></table:table-cell></table:table-row></table:table>249 """)250 let reader = try ODSReader(url: url)251 #expect(reader.sheets.map(\.name) == ["Main", "Hidden"] && reader.sheets.map(\.visibility) == [.visible, .hidden])252 var rows: [[Cell]] = []253 let scan = try reader.read(sheet: 0) { rows.append($0); return true }254 #expect(rows.count == 5 && scan.rows == 5 && scan.formulaCells == 2 && scan.mergedRanges == 1)255 #expect(rows[1] == [.string("a b\tc\nd\nsecond para"), .double(1.5), .date(Canonical.day(2024, 2, 29)), .time(49_510_500_000)])256 #expect(rows[2] == [.double(0.25), .int(7), .int(7), .bool(true)] && rows[3] == rows[2])257 #expect(rows[4] == [.double(9.99), .null, .timestamp(Canonical.ts(2024, 2, 29, 13, 45, 10, 123_456))])258 #expect(reader.features.commentParts == 1)259 var asText: [[Cell]] = []260 _ = try reader.read(sheet: 0, formulasAsText: true) { asText.append($0); return true }261 #expect(asText[2][0] == .string("=[.B2]/6"))262 let c = try await runConversion(url, .ods, to: .csv, in: scratch)263 #expect(c.warnings.codes.isSuperset(of: ["sheets-dropped", "formulas-dropped", "merged-cells", "mixed-types"]))264 }265266 @Test func repeatBombsAreRefused() throws {267 let scratch = Scratch("ods")268 let cell = "<table:table-cell office:value-type=\"float\" office:value=\"1\" table:number-columns-repeated=\"16384\"><text:p>1</text:p></table:table-cell>"269 let rowsBomb = scratch.file("rows.ods")270 try HandMade.ods(at: rowsBomb, body: "<table:table table:name=\"B\"><table:table-row table:number-rows-repeated=\"1048576\">\(cell)</table:table-row><table:table-row table:number-rows-repeated=\"1048576\">\(cell)</table:table-row></table:table>")271 var delivered = 0272 #expect(throws: ConversionError.self) { _ = try ODSReader(url: rowsBomb).read(sheet: 0) { _ in delivered += 1; return true } }273 #expect(delivered == 0, "the bomb must be refused before any row is expanded")274 let cellsBomb = scratch.file("cells.ods")275 try HandMade.ods(at: cellsBomb, body: "<table:table table:name=\"B\"><table:table-row><table:table-cell office:value-type=\"float\" office:value=\"1\" table:number-columns-repeated=\"1000000000\"><text:p>1</text:p></table:table-cell></table:table-row></table:table>")276 #expect(throws: ConversionError.self) { _ = try ODSReader(url: cellsBomb).read(sheet: 0) { _ in true } }277 let cellBudget = scratch.file("budget.ods")278 try HandMade.ods(at: cellBudget, body: "<table:table table:name=\"B\"><table:table-row table:number-rows-repeated=\"40000\">\(cell)</table:table-row></table:table>")279 #expect(throws: ConversionError.self) { _ = try ODSReader(url: cellBudget).read(sheet: 0) { _ in true } }280 // Trailing repeated empty rows and cells (what LibreOffice really writes) cost nothing.281 let harmless = scratch.file("ok.ods")282 try HandMade.ods(at: harmless, body: "<table:table table:name=\"B\"><table:table-row><table:table-cell office:value-type=\"float\" office:value=\"1\"><text:p>1</text:p></table:table-cell><table:table-cell table:number-columns-repeated=\"16383\"/></table:table-row><table:table-row table:number-rows-repeated=\"1048575\"><table:table-cell table:number-columns-repeated=\"16384\"/></table:table-row></table:table>")283 var n = 0284 _ = try ODSReader(url: harmless).read(sheet: 0) { row in n += 1; #expect(row == [.int(1)]); return true }285 #expect(n == 1)286 }287288 @Test func zipReaderIsDefensive() throws {289 let scratch = Scratch("zip")290 let url = scratch.file("a.zip")291 let w = try ZipWriter(url: url)292 try w.addStored("mimetype", Array("x/y".utf8))293 try w.begin("big.xml")294 let chunk = [UInt8](repeating: 0x41, count: 1 << 20)295 for _ in 0..<64 { try w.write(chunk) }296 try w.end()297 try w.add("é/ünïcode name.xml", "<a/>")298 try w.finish()299 let size = (try FileManager.default.attributesOfItem(atPath: url.path)[.size] as? Int) ?? 0300 #expect(size < 1 << 20, "64 MB of one byte must compress far below 1 MB")301 let zip = try ZipArchive(url: url)302 #expect(zip.entries.map(\.name) == ["mimetype", "big.xml", "é/ünïcode name.xml"])303 #expect(try zip.read(#require(zip.entry(named: "mimetype"))) == Data("x/y".utf8))304 // Decompression is bounded by the bytes actually produced, whatever the directory declares.305 #expect(throws: ConversionError.limitExceeded("The part big.xml expands beyond 8 MB; the package looks like a decompression bomb.")) {306 let s = try zip.open(#require(zip.entry(named: "big.xml")), limit: 8 << 20)307 while try s.next() != nil {}308 }309 var total = 0310 let s = try zip.open(#require(zip.entry(named: "big.xml")))311 while let c = try s.next() { total += c.count }312 #expect(total == 64 << 20)313 // The archive is also readable by the system's unzip (an independent implementation), when present.314 if FileManager.default.isExecutableFile(atPath: "/usr/bin/unzip") {315 let p = Process()316 p.executableURL = URL(fileURLWithPath: "/usr/bin/unzip")317 p.arguments = ["-tq", url.path]318 p.standardOutput = FileHandle.nullDevice; p.standardError = FileHandle.nullDevice319 try p.run(); p.waitUntilExit()320 #expect(p.terminationStatus == 0)321 }322 var bytes = try Data(contentsOf: url)323 // Truncated: no end-of-directory record.324 let cut = scratch.file("cut.zip")325 try bytes.prefix(bytes.count / 2).write(to: cut)326 #expect(throws: ConversionError.self) { _ = try ZipArchive(url: cut) }327 // A flipped byte in compressed data fails the checksum (or the inflate).328 bytes[bytes.startIndex + 200] ^= 0xFF329 let flipped = scratch.file("flip.zip")330 try bytes.write(to: flipped)331 #expect(throws: ConversionError.self) {332 let z = try ZipArchive(url: flipped)333 let st = try z.open(#require(z.entry(named: "big.xml")))334 while try st.next() != nil {}335 }336 let tiny = scratch.file("tiny.zip")337 try Data([0x50, 0x4B]).write(to: tiny)338 #expect(throws: ConversionError.self) { _ = try ZipArchive(url: tiny) }339 }340341 @Test func xmlTokenizerRefusesDoctypesAndUnknownEntities() throws {342 func tokens(_ s: String) throws -> [String] {343 let p = XMLPull(data: Data(s.utf8))344 var out: [String] = []345 while true {346 switch try p.next() {347 case .eof: return out348 case .start: out.append("<" + p.qualifiedName + (try p.attributes().map { " \($0.0)=\($0.1)" }.joined()))349 case .end: out.append("/")350 case .text: if !p.textIsWhitespace { out.append(try p.text()) }351 }352 }353 }354 #expect(try tokens("<?xml version='1.0'?><!-- c --><a x='1 & 2' y=\"AB\"><b/>t<><![CDATA[<raw>&]]></a>") == ["<a x=1 & 2 y=AB", "<b", "/", "t<>", "<raw>&", "/"])355 #expect(throws: ConversionError.self) { _ = try tokens("<!DOCTYPE a [<!ENTITY x \"y\">]><a>&x;</a>") }356 #expect(throws: ConversionError.self) { _ = try tokens("<a>&undefined;</a>") }357 #expect(throws: ConversionError.self) { _ = try tokens("<a b=c></a>") }358 #expect(throws: ConversionError.self) { _ = try tokens("<a><b></a") }359 // Tokens split across refills are reassembled.360 var parts = Array("<root><item id=\"12345\">some text & more</item><![CDATA[x]]><!-- note --></root>".utf8)[...]361 let p = XMLPull { guard let b = parts.first else { return nil }; parts = parts.dropFirst(); return [b] }362 var seen: [String] = []363 loop: while true {364 switch try p.next() {365 case .eof: break loop366 case .start: seen.append(p.localName + ((try p.attr("id")).map { "#\($0)" } ?? ""))367 case .text: seen.append(try p.text())368 case .end: seen.append("/")369 }370 }371 #expect(seen == ["root", "item#12345", "some text & more", "/", "x", "/"])372 }373374 @Test(.enabled(if: libreOfficeAvailable, "the bundled LibreOffice is not installed"))375 func libreOfficeOpensOurOdsAndXlsxWithTheSameValues() async throws {376 let scratch = Scratch("lo")377 for format in [FormatID.ods, .xlsx] {378 let ours = try await Canonical.fixture(format, in: scratch)379 let ws = try Workspace(parent: scratch.url)380 // LibreOffice (an independent implementation) loads our file and re-saves it; we read its output.381 let resaved = try LibreOffice.convert(ours, to: format == .ods ? "xlsx" : "xls", workspace: ws, control: nil, inputExtension: format.rawValue)382 let back = format == .ods ? resaved : try LibreOffice.convert(resaved, to: "xlsx", workspace: ws, control: nil)383 let p = try await DataPreview.preview(url: back, format: .xlsx)384 #expect(p.columns.map(\.name) == Canonical.names)385 #expect(p.tableNames == ["Data"])386 for (r, (got, want)) in zip(p.rows, Canonical.texts).enumerated() {387 for c in want.indices {388 let value = c < got.count ? got[c] : nil389 // Depending on the filter, LibreOffice keeps an empty text cell or stores an empty cell.390 if want[c] == "" { #expect(value == nil || value == ""); continue }391 #expect(Validator.equivalent(value, want[c]), "\(format) row \(r + 1) \(Canonical.names[c]): \(String(describing: value)) vs \(String(describing: want[c]))")392 }393 }394 }395 }396397 @Test(.enabled(if: libreOfficeAvailable, "the bundled LibreOffice is not installed"))398 func xlsLimitsAreEnforcedBeforeLibreOfficeRuns() async throws {399 let scratch = Scratch("xls")400 let big = scratch.file("big.csv")401 try ("n\n" + (0..<65_536).map(String.init).joined(separator: "\n") + "\n").write(to: big, atomically: true, encoding: .utf8)402 await #expect(throws: ConversionError.self) { _ = try await runConversion(big, .csv, to: .xls, in: scratch) }403 let wide = scratch.file("wide.csv")404 try ((0..<300).map { "c\($0)" }.joined(separator: ",") + "\n" + (0..<300).map(String.init).joined(separator: ",") + "\n").write(to: wide, atomically: true, encoding: .utf8)405 await #expect(throws: ConversionError.self) { _ = try await runConversion(wide, .csv, to: .xls, in: scratch) }406 #expect(DataEngine().preflight(ConversionRequest(source: wide, sourceFormat: .csv, destinationFormat: .xls), inspection: nil).contains { $0.code == "xls-column-limit" })407 }408409 @Test func xlsPathsFollowTheBundledTool() {410 let declared = DataEngine().paths.contains { $0.source == .xls || $0.destination == .xls }411 #expect(declared == libreOfficeAvailable)412 }413}414