Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetIO/Tests/SheetIOTests/InteropTests.swift10// Module : SheetIOTests11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import SheetIO9192/// Compares a workbook with what openpyxl reads from the file SheetIO wrote for it.93func openpyxlMismatches(_ wb: Workbook, _ dump: [String: Any]) -> [String] {94 var out = [String]()95 func add(_ s: String) { if out.count < 40 { out.append(s) } }96 guard let sheets = dump["sheets"] as? [[String: Any]], sheets.count == wb.sheets.count else { return ["sheet count"] }97 func hex(_ any: Any?) -> String? { any as? String }98 for (si, sheet) in wb.sheets.enumerated() {99 let d = sheets[si]100 if d["name"] as? String != sheet.name { add("sheet name \(sheet.name)") }101 let cells = d["cells"] as? [String: [String: Any]] ?? [:]102 var expectedCount = 0103 sheet.cells.forEachAll { r, c, cell in104 let ref = CellRef(r, c).a1105 let style = wb.styles[cell.style]106 guard cell.hasContent || cell.style != 0 else { return }107 guard let e = cells[ref] else {108 if cell.hasContent { add("\(sheet.name)!\(ref) missing in openpyxl") }109 return110 }111 expectedCount += 1112 let v = e["v"]113 if let f = cell.formula {114 let text = "=" + FormulaCompat.fileText(f.expr)115 if let arr = v as? [String: Any], let t = arr["array"] as? String {116 if t != text { add("\(ref) array formula \(t) ≠ \(text)") }117 if let sp = f.spill, arr["ref"] as? String != sp.a1 { add("\(ref) array ref \(String(describing: arr["ref"]))") }118 } else if v as? String != text { add("\(ref) formula \(String(describing: v)) ≠ \(text)") }119 switch f.value {120 case .number(let n):121 if let c = e["cached"] as? NSNumber { if abs(c.doubleValue - n) > 1e-9 * max(1, abs(n)) { add("\(ref) cached \(c) ≠ \(n)") } }122 else if !(e["cached"] is [String: Any]), !(NumberFormats.isDateTime(style.numberFormat) && e["cached"] as? String == "#VALUE!") {123 add("\(ref) cached number missing: \(String(describing: e["cached"]))")124 }125 case .text(let s): if XMLText.ooxmlUnescape(e["cached"] as? String ?? "") != s { add("\(ref) cached text \(String(describing: e["cached"])) ≠ \(s.debugDescription)") }126 case .bool(let b): if (e["cached"] as? Bool) != b { add("\(ref) cached bool") }127 case .error(let err): if e["cached"] as? String != err.rawValue { add("\(ref) cached error \(String(describing: e["cached"]))") }128 default: break129 }130 } else {131 switch cell.value {132 case .number(let n):133 if let dateDict = v as? [String: Any], let iso = dateDict["date"] as? String {134 if !NumberFormats.isDateTime(style.numberFormat) { add("\(ref) unexpected date \(iso)") }135 } else if let x = v as? NSNumber { if x.doubleValue != n && abs(x.doubleValue - n) > 1e-12 * abs(n) { add("\(ref) number \(x) ≠ \(n)") } }136 else if !(NumberFormats.isDateTime(style.numberFormat) && v as? String == "#VALUE!") { add("\(ref) number missing: \(String(describing: v)) for \(n) style \(style.numberFormat)") } // openpyxl cannot convert out-of-range dates137 case .text(let s): if v as? String != s { add("\(ref) text \(String(describing: v)) ≠ \(s.debugDescription)") }138 case .bool(let b): if (v as? Bool) != b || !(v is Bool || CFGetTypeID(v as CFTypeRef) == CFBooleanGetTypeID()) { add("\(ref) bool") }139 case .error(let err): if v as? String != err.rawValue { add("\(ref) error") }140 default: break141 }142 }143 if cell.style != 0 {144 guard let s = e["style"] as? [String: Any] else { add("\(ref) style missing"); return }145 if s["numFmt"] as? String != style.numberFormat { add("\(ref) numFmt \(String(describing: s["numFmt"])) ≠ \(style.numberFormat)") }146 if (s["bold"] as? Bool) != style.bold || (s["italic"] as? Bool) != style.italic || (s["strike"] as? Bool) != style.strikethrough { add("\(ref) font flags") }147 if s["font"] as? String != (style.fontName ?? wb.defaultFontName) { add("\(ref) font name") }148 if (s["size"] as? NSNumber)?.doubleValue != (style.fontSize ?? wb.defaultFontSize) { add("\(ref) font size") }149 let u: String? = style.underline == .none ? nil : style.underline.rawValue150 if s["underline"] as? String != u { add("\(ref) underline") }151 if hex(s["color"]) != style.textColor?.hexString { add("\(ref) color \(String(describing: s["color"]))") }152 if hex(s["fill"]) != style.fill?.hexString { add("\(ref) fill \(String(describing: s["fill"]))") }153 if s["h"] as? String != (style.hAlign == .general ? nil : style.hAlign.rawValue) { add("\(ref) hAlign") }154 if s["v"] as? String != (style.vAlign == .bottom ? nil : style.vAlign.rawValue) { add("\(ref) vAlign \(String(describing: s["v"]))") }155 if (s["wrap"] as? Bool) != style.wrap || (s["shrink"] as? Bool) != style.shrinkToFit { add("\(ref) wrap/shrink") }156 let rot = style.rotation >= 0 ? style.rotation : 90 - style.rotation157 if (s["rotation"] as? NSNumber)?.intValue != rot { add("\(ref) rotation \(String(describing: s["rotation"]))") }158 if (s["indent"] as? NSNumber)?.intValue != style.indent { add("\(ref) indent") }159 if (s["locked"] as? Bool) != style.locked || (s["hidden"] as? Bool) != style.formulaHidden { add("\(ref) protection") }160 let b = s["border"] as? [String: [Any]] ?? [:]161 for (side, edge) in [("left", style.borders.left), ("right", style.borders.right), ("top", style.borders.top), ("bottom", style.borders.bottom)] {162 if let edge, edge.style != .none {163 if b[side]?.first as? String != edge.style.rawValue || b[side]?.last as? String != edge.color.hexString { add("\(ref) border \(side)") }164 } else if b[side] != nil { add("\(ref) unexpected border \(side)") }165 }166 }167 }168 let m = sheet.meta169 if Set(d["merges"] as? [String] ?? []) != Set(m.merges.map(\.a1)) { add("\(sheet.name) merges \(String(describing: d["merges"]))") }170 let freeze = m.frozenRows > 0 || m.frozenCols > 0 ? CellRef(m.frozenRows, m.frozenCols).a1 : nil171 if d["freeze"] as? String != freeze { add("\(sheet.name) freeze \(String(describing: d["freeze"]))") }172 let widths = d["colWidths"] as? [String: NSNumber] ?? [:]173 for (c, w) in m.colWidths { if abs((widths[A1.columnName(c)]?.doubleValue ?? -1) * 5.25 - w) > 0.01 { add("\(sheet.name) width \(A1.columnName(c))") } }174 let heights = d["rowHeights"] as? [String: NSNumber] ?? [:]175 for (r, h) in m.rowHeights { if abs((heights[String(r + 1)]?.doubleValue ?? -1) - h) > 0.01 { add("\(sheet.name) height \(r + 1)") } }176 if Set(d["colHidden"] as? [String] ?? []) != Set(m.hiddenCols.map { A1.columnName($0) }) { add("\(sheet.name) hidden cols") }177 if Set((d["rowHidden"] as? [NSNumber] ?? []).map(\.intValue)) != Set(m.hiddenRows.union(m.filteredRows).map { $0 + 1 }) { add("\(sheet.name) hidden rows") }178 if (d["state"] as? String == "hidden") != m.isHidden { add("\(sheet.name) state") }179 if d["tabColor"] as? String != m.tabColor?.hexString { add("\(sheet.name) tab colour") }180 if (d["protected"] as? Bool) != m.isProtected { add("\(sheet.name) protection") }181 // validations182 let dvs = d["validations"] as? [[String: Any]] ?? []183 if dvs.count != m.validations.count { add("\(sheet.name) validation count \(dvs.count)") }184 for (dv, v) in zip(dvs, m.validations) {185 if dv["type"] as? String != (v.kind == .any ? nil : v.kind.rawValue) { add("validation type") }186 if Set((dv["sqref"] as? String ?? "").split(separator: " ").map(String.init)) != Set(v.ranges.map(\.a1)) { add("validation sqref \(String(describing: dv["sqref"]))") }187 if dv["f1"] as? String != v.formula1.map(FormulaCompat.fileText(fromCanonical:)) || dv["f2"] as? String != v.formula2 { add("validation formulas \(String(describing: dv["f1"]))") }188 if (dv["allowBlank"] as? Bool) != v.allowBlank { add("validation allowBlank") }189 if v.kind == .list, (dv["hideDropDown"] as? Bool) != !v.showDropdown { add("validation dropdown") }190 if dv["errorTitle"] as? String != v.errorTitle || dv["error"] as? String != v.errorMessage || dv["prompt"] as? String != v.promptMessage { add("validation messages") }191 }192 // conditional formats193 let cfs = d["conditional"] as? [[String: Any]] ?? []194 if cfs.count != m.conditionalFormats.count { add("\(sheet.name) conditional count \(cfs.count) ≠ \(m.conditionalFormats.count)") }195 for cf in m.conditionalFormats {196 guard let dc = cfs.first(where: { ($0["priority"] as? NSNumber)?.intValue == cf.priority }) else { add("cf priority \(cf.priority) missing"); continue }197 if Set((dc["sqref"] as? String ?? "").split(separator: " ").map(String.init)) != Set(cf.ranges.map(\.a1)) { add("cf sqref") }198 let type = dc["type"] as? String199 let formulas = dc["formulas"] as? [String] ?? []200 switch cf.kind {201 case .cellIs(let op, let f): if type != "cellIs" || dc["operator"] as? String != op || formulas != f { add("cf cellIs") }202 case .formula(let f): if type != "expression" || formulas != [f] { add("cf expression") }203 case .colorScale(let pts):204 let cs = dc["colorScale"] as? [String: Any]205 if type != "colorScale" || (cs?["colors"] as? [String]) != pts.map({ ($0.color ?? .white).hexString }) { add("cf colorScale") }206 if (cs?["cfvo"] as? [[Any]])?.map({ $0.first as? String }) != pts.map({ Optional($0.kind) }) { add("cf colorScale cfvo") }207 case .dataBar(_, _, let color): if type != "dataBar" || (dc["dataBar"] as? [String: Any])?["color"] as? String != color.hexString { add("cf dataBar") }208 case .iconSet(let name, _, let rev):209 let i = dc["iconSet"] as? [String: Any]210 if type != "iconSet" || i?["name"] as? String != name || (i?["reverse"] as? Bool) != rev { add("cf iconSet") }211 case .duplicates(let unique): if type != (unique ? "uniqueValues" : "duplicateValues") { add("cf duplicates") }212 case .top(let rank, let percent, let bottom):213 if type != "top10" || (dc["rank"] as? NSNumber)?.intValue != rank || (dc["percent"] as? Bool) != percent || (dc["bottom"] as? Bool) != bottom { add("cf top10") }214 case .aboveAverage(let above, _): if type != "aboveAverage" || ((dc["aboveAverage"] as? Bool) ?? true) != above { add("cf aboveAverage") }215 case .containsText(let t): if type != "containsText" || dc["text"] as? String != t { add("cf containsText") }216 case .blanks(let yes): if type != (yes ? "containsBlanks" : "notContainsBlanks") { add("cf blanks") }217 case .errors(let yes): if type != (yes ? "containsErrors" : "notContainsErrors") { add("cf errors") }218 }219 if (dc["stopIfTrue"] as? Bool) != cf.stopIfTrue { add("cf stopIfTrue") }220 if let fill = cf.style.fill, (dc["dxf"] as? [String: Any])?["fill"] as? String != fill.hexString { add("cf dxf fill") }221 if let color = cf.style.textColor, (dc["dxf"] as? [String: Any])?["color"] as? String != color.hexString { add("cf dxf colour") }222 }223 // tables, comments, hyperlinks, filter224 let tables = d["tables"] as? [[String: Any]] ?? []225 if tables.count != m.tables.count { add("\(sheet.name) table count") }226 for (dt, t) in zip(tables, m.tables) {227 if dt["name"] as? String != t.name || dt["ref"] as? String != t.range.a1 || dt["columns"] as? [String] != t.columns.map(\.name) { add("table \(t.name)") }228 if dt["style"] as? String != t.styleName || ((dt["totals"] as? NSNumber)?.intValue ?? 0 > 0) != t.hasTotals { add("table \(t.name) style/totals") }229 }230 let comments = d["comments"] as? [String: [String: Any]] ?? [:]231 for (ref, c) in m.comments where sheet.cell(ref.row, ref.col) != nil {232 if comments[ref.a1]?["text"] as? String != c.text || comments[ref.a1]?["author"] as? String != c.author { add("comment \(ref.a1)") }233 }234 let links = d["hyperlinks"] as? [String: [String: Any]] ?? [:]235 for (ref, l) in m.hyperlinks where sheet.cell(ref.row, ref.col) != nil {236 let target = (links[ref.a1]?["target"] as? String ?? "") + ((links[ref.a1]?["location"] as? String).map { "#" + $0 } ?? "")237 if target != l.target { add("hyperlink \(ref.a1): \(target)") }238 }239 if d["autoFilter"] as? String != m.autoFilter?.a1 { add("\(sheet.name) autoFilter") }240 }241 let names = dump["names"] as? [String: String] ?? [:]242 for dn in wb.names.values where dn.scopeSheetID == nil {243 if names[dn.name] != FormulaCompat.fileText(fromCanonical: dn.formula) { add("name \(dn.name): \(String(describing: names[dn.name]))") }244 }245 for dn in wb.names.values {246 guard let id = dn.scopeSheetID, let i = wb.sheetIndex(id: id) else { continue }247 if (sheets[i]["localNames"] as? [String: String])?[dn.name] != dn.formula { add("local name \(dn.name)") }248 }249 if (dump["active"] as? NSNumber)?.intValue != wb.activeSheet { add("active sheet") }250 if (dump["date1904"] as? Bool) != wb.date1904 { add("date1904") }251 let props = dump["props"] as? [String: Any] ?? [:]252 if props["title"] as? String != (wb.properties.title.isEmpty ? nil : wb.properties.title) { add("title") }253 if !wb.properties.author.isEmpty, props["creator"] as? String != wb.properties.author { add("creator \(String(describing: props["creator"]))") }254 return out255}256257/// Numbers and arithmetic the core engine can compute, so that results can be cross-checked.258func makeCalcWorkbook() -> Workbook {259 let wb = Workbook()260 let s = wb.addSheet(named: "Calc")261 let t = wb.addSheet(named: "Refs")262 for r in 0..<20 {263 s.cells.set(row: r, col: 0, Cell(value: .number(Double(r) * 1.5 - 7)))264 s.cells.set(row: r, col: 1, Cell(value: .number(Double((r * 37) % 11) + 0.25)))265 s.cells.set(row: r, col: 2, Cell(formula: Formula(expr: try! FormulaParser.parse("A\(r + 1)*B\(r + 1)-$B$1"))))266 s.cells.set(row: r, col: 3, Cell(formula: Formula(expr: try! FormulaParser.parse("IF(A\(r + 1)>0,MAX(A\(r + 1),B\(r + 1)),ABS(A\(r + 1))^2)"))))267 }268 for (i, f) in ["SUM(A1:A20)", "AVERAGE(B1:B20)", "MIN(C1:C20)", "MAX(C1:D20)", "COUNT(A1:D20)", "SUM(A1:A20)/COUNT(B1:B20)", "(A1+B2)*3-A3/4", "2^10%",269 "SUM(Calc!A1:B3)+Refs!A1", "INDEX(B1:B20,5)", "ROWS(A1:A20)+COLUMNS(A1:D1)", "IF(NOT(A1>0),\"neg\",\"pos\")", "\"a\"&\"b\"&ROWS(A1:A20)", "A1>B1", "1/0"].enumerated() {270 s.cells.set(row: i, col: 5, Cell(formula: Formula(expr: try! FormulaParser.parse(f))))271 }272 t.cells.set(row: 0, col: 0, Cell(value: .number(1000)))273 t.cells.set(row: 1, col: 0, Cell(formula: Formula(expr: try! FormulaParser.parse("Calc!F1*2+A1"))))274 return wb275}276277@Suite(.serialized) struct InteropTests {278 @Test(.enabled(if: Tools.hasPython)) func openpyxlReadsTheFeatureWorkbook() throws {279 let wb = makeFeatureWorkbook()280 let dir = Scratch.dir("openpyxl")281 defer { try? FileManager.default.removeItem(at: dir) }282 let url = dir.appendingPathComponent("features.xlsx")283 try WorkbookWriter.save(wb, to: url, format: .xlsx)284 let dump = try Tools.openpyxlDump(url)285 let mism = openpyxlMismatches(wb, dump)286 #expect(mism.isEmpty, "\(mism.joined(separator: "\n"))")287 // spot checks on the raw dump288 let sheets = dump["sheets"] as! [[String: Any]]289 let cells = sheets[0]["cells"] as! [String: [String: Any]]290 #expect(cells["E9"]?["v"] as? String == "=_xlfn.XLOOKUP(2,B2:B7,A2:A7)&_xlfn.TEXTJOIN(\",\",TRUE,A2:A3)")291 #expect((cells["G2"]?["v"] as? [String: Any])?["array"] as? String == "=_xlfn.SEQUENCE(3,2)")292 #expect(cells["J2"]?["v"] as? String == "=SUM(_xlfn.ANCHORARRAY(G2))")293 #expect(cells["J3"]?["v"] as? String == "=_xlfn.LET(_xlpm.x,B2,_xlpm.y,_xlpm.x*2,_xlpm.x+_xlpm.y)")294 #expect(cells["J4"]?["v"] as? String == "=_xlfn.LAMBDA(_xlpm.a,_xlpm.b,_xlpm.a+_xlpm.b)(1,2)")295 let p = sheets[0]["print"] as! [String: Any]296 #expect(p["orientation"] as? String == "landscape" && (p["paperSize"] as? NSNumber)?.intValue == 9)297 #expect((p["fitToWidth"] as? NSNumber)?.intValue == 1 && (p["fitToHeight"] as? NSNumber)?.intValue == 0)298 #expect(p["area"] as? String == "'Data'!$A$1:$J$20" && p["titleRows"] as? String == "$1:$1" && p["titleCols"] as? String == "$A:$B")299 #expect((p["left"] as? NSNumber)?.doubleValue == 0.75 && (p["top"] as? NSNumber)?.doubleValue == 1)300 #expect(p["header"] as? String == "Feature &P" && (p["rowBreaks"] as? [NSNumber])?.map(\.intValue) == [10])301 #expect((sheets[0]["zoom"] as? NSNumber)?.intValue == 125 && sheets[0]["gridLines"] as? Bool == false)302 #expect((sheets[1]["tables"] as? [[String: Any]])?.first?["totalsFunctions"] as? [String?] == [nil, "sum", nil])303 }304305 @Test(.enabled(if: Tools.hasPython)) func openpyxlReadsRandomWorkbooks() throws {306 let dir = Scratch.dir("openpyxl-random")307 defer { try? FileManager.default.removeItem(at: dir) }308 for seed in 1...6 {309 let wb = makeRandomXLSXWorkbook(seed: UInt64(seed))310 let url = dir.appendingPathComponent("r\(seed).xlsx")311 let issues = try WorkbookWriter.save(wb, to: url, format: .xlsx)312 #expect(!issues.contains { $0.severity == .loss }, "\(issues)")313 let mism = openpyxlMismatches(wb, try Tools.openpyxlDump(url))314 #expect(mism.isEmpty, "seed \(seed): \(mism.joined(separator: "\n"))")315 }316 }317318 @Test(.enabled(if: Tools.hasPython)) func openpyxlKeepsPreservedChartsAndImages() throws {319 let dir = Scratch.dir("charts")320 defer { try? FileManager.default.removeItem(at: dir) }321 let first = try WorkbookReader.load(url: Scratch.fixture("charts_openpyxl.xlsx"))322 setCell(first.workbook.sheets[0], "A7", .text("edited"))323 let out = dir.appendingPathComponent("charts.xlsx")324 try WorkbookWriter.save(first.workbook, to: out, format: .xlsx, preserved: first.preserved)325 let zip = try ZipArchive(url: out)326 let original = try ZipArchive(url: Scratch.fixture("charts_openpyxl.xlsx"))327 for part in ["xl/charts/chart1.xml", "xl/drawings/drawing1.xml", "xl/media/image1.png", "xl/drawings/_rels/drawing1.xml.rels"] {328 #expect(try zip.data(for: part) == original.data(for: part), "\(part)")329 }330 #expect(String(decoding: try zip.data(for: "xl/worksheets/sheet1.xml"), as: UTF8.self).contains("<drawing r:id="))331 #expect(!String(decoding: try zip.data(for: "xl/worksheets/sheet2.xml"), as: UTF8.self).contains("<drawing"))332 let dump = try Tools.openpyxlDump(out)333 let sheets = dump["sheets"] as! [[String: Any]]334 #expect((sheets[0]["charts"] as? NSNumber)?.intValue == 1 && (sheets[0]["images"] as? NSNumber)?.intValue == 1)335 #expect((sheets[0]["cells"] as! [String: [String: Any]])["A7"]?["v"] as? String == "edited")336 // without the preserved parts the chart is simply absent, and the file is still valid337 let bare = dir.appendingPathComponent("bare.xlsx")338 try WorkbookWriter.save(first.workbook, to: bare, format: .xlsx)339 #expect(((try Tools.openpyxlDump(bare)["sheets"] as! [[String: Any]])[0]["charts"] as? NSNumber)?.intValue == 0)340 }341342 @Test(.enabled(if: Tools.hasLibreOffice)) func libreOfficeConvertsOurXLSX() throws {343 let dir = Scratch.dir("lo-xlsx")344 defer { try? FileManager.default.removeItem(at: dir) }345 // 1. formulas without cached values: LibreOffice has to compute them itself346 let expected = makeCalcWorkbook()347 CalcEngine(workbook: expected).recalculate()348 let blank = dir.appendingPathComponent("calc.xlsx")349 try WorkbookWriter.save(makeCalcWorkbook(), to: blank, format: .xlsx)350 // 2. the feature workbook and a preserved-chart workbook must convert without error351 let features = dir.appendingPathComponent("features.xlsx")352 let fw = makeFeatureWorkbook()353 try WorkbookWriter.save(fw, to: features, format: .xlsx)354 let charts = try WorkbookReader.load(url: Scratch.fixture("charts_openpyxl.xlsx"))355 let chartsOut = dir.appendingPathComponent("charts.xlsx")356 try WorkbookWriter.save(charts.workbook, to: chartsOut, format: .xlsx, preserved: charts.preserved)357358 let outX = dir.appendingPathComponent("x"), outC = dir.appendingPathComponent("c")359 let converted = try Tools.libreOfficeConvert([blank, features, chartsOut], to: "xlsx", outDir: outX)360 let csvs = try Tools.libreOfficeConvert([blank], to: "csv:Text - txt - csv (StarCalc):44,34,76,1,,1033,false,true,false", outDir: outC)361362 // values computed by LibreOffice equal the ones of our engine363 let lo = try WorkbookReader.load(url: converted[0]).workbook364 var o = WorkbookCompareOptions()365 o.styles = false; o.layout = false; o.features = false; o.properties = false; o.names = false; o.numberTolerance = 1e-9; o.spillState = false366 let diff = WorkbookDiff.differences(expected, lo, options: o)367 #expect(diff.isEmpty, "\(diff.joined(separator: "\n"))")368 // the CSV export agrees too (decimal separator depends on the LibreOffice locale)369 var csvOptions = LoadOptions()370 let csvText = String(decoding: try Data(contentsOf: csvs[0]), as: UTF8.self)371 csvOptions.csv.decimalComma = csvText.contains("0,25")372 let csv = try WorkbookReader.load(url: csvs[0], options: csvOptions).workbook373 for r in 0..<20 {374 for c in 0..<4 {375 let a = expected.sheets[0].value(r, c).numberValue ?? .nan, b = csv.sheets[0].value(r, c).numberValue ?? .nan376 #expect(abs(a - b) <= 1e-9 * max(1, abs(a)), "\(CellRef(r, c).a1): \(a) vs \(b)")377 }378 }379 // feature workbook after a LibreOffice round trip: same literal values, formulas LibreOffice understands, layout380 let lf = try WorkbookReader.load(url: converted[1]).workbook381 #expect(lf.sheets.map(\.name) == fw.sheets.map(\.name))382 for ref in ["A2", "A3", "A4", "A5", "A6", "A7", "B2", "C7", "E3", "A11", "B11", "E11", "F11"] {383 #expect(WorkbookDiff.valuesEqual(cellValue(lf, 0, ref), cellValue(fw, 0, ref), tolerance: 1e-12), "\(ref): \(cellValue(lf, 0, ref)) vs \(cellValue(fw, 0, ref))")384 }385 for ref in ["D2", "D8", "A9", "B9", "C9", "G2", "J3"] { #expect(cellFormula(lf, 0, ref) == cellFormula(fw, 0, ref), "\(ref): \(String(describing: cellFormula(lf, 0, ref)))") }386 #expect(Set(lf.sheets[0].meta.merges) == Set(fw.sheets[0].meta.merges))387 #expect(lf.sheets[0].meta.frozenRows == 1 && lf.sheets[0].meta.frozenCols == 2)388 #expect(lf.sheets[0].meta.validations.count == 4 && lf.sheets[0].meta.conditionalFormats.count == 12)389 #expect(lf.sheets[1].meta.tables.first?.name == "Table1" && lf.sheets[1].meta.autoFilter == fw.sheets[1].meta.autoFilter)390 #expect(lf.sheets[0].meta.comments[CellRef(a1: "B2")!]?.text == "A note\nwith <two> lines")391 #expect(lf.sheets[0].meta.hyperlinks[CellRef(a1: "A2")!]?.target == "https://example.com/path?x=1&y=2")392 #expect(lf.sheets[0].meta.print.printArea == fw.sheets[0].meta.print.printArea && lf.sheets[0].meta.print.repeatRows == 0...0)393 #expect(lf.definedName("MyRange")?.formula == "Data!$B$2:$B$7")394 #expect(lf.sheets[2].meta.isHidden)395 #expect(wbStyle(lf, 0, "A1").bold && wbStyle(lf, 0, "A1").fill == RGBA(hex: 0xFFE699) && wbStyle(lf, 0, "C2").numberFormat == "#,##0.00")396 // the chart survived our writer and LibreOffice's import397 #expect(try ZipArchive(url: converted[2]).names.contains { $0.hasPrefix("xl/charts/") })398 }399}400401func wbStyle(_ wb: Workbook, _ sheet: Int, _ a1: String) -> CellStyle {402 let r = CellRef(a1: a1)!403 return wb.styles[wb.sheets[sheet].styleID(r.row, r.col)]404}405406/// Random content limited to what XLSX can store exactly.407func makeRandomXLSXWorkbook(seed: UInt64, cells: Int = 300) -> Workbook {408 var g = RandomWorkbook(seed: seed)409 let wb = Workbook()410 let texts = ["plain", "é ü ñ", "日本語", "😀 emoji", " padded ", "multi\nline", "tab\there", "quote \" and 'apostrophe'", "<xml> & entities", "_x0041_", "a;b,c", "=not a formula", "", "007"]411 let formulas = ["A1+B2", "SUM(A1:C10)*2", "IF($A$1>0,\"yes\",\"no\")", "Second!A1&\"x\"", "LET(x,5,x*2)", "SEQUENCE(3,2)", "A1#", "{1,2;3,4}", "-A1^2%",412 "VLOOKUP(A1,$B$1:$D$20,3,FALSE)", "SUM(A:A,1:3)", "@A1:A10", "\"é😀\"&A1", "1E-05+1.5E+20", "PMT(0.05/12,360,,1)", "XLOOKUP(1,A:A,B:B)", "FILTER(A1:A5,B1:B5>0)", "STDEV.S(A1:A9)"]413 var styleIDs: [Int32] = [0]414 for _ in 0..<10 {415 var s = g.style()416 s.textColor = s.textColor.map { RGBA(hex: $0.rgba >> 8) }; s.fill = s.fill.map { RGBA(hex: $0.rgba >> 8) }417 if s.textColor == .black { s.textColor = nil }418 if s.fontSize == wb.defaultFontSize { s.fontSize = nil } // an explicit default size reads back as "default"419 func opaque(_ e: BorderEdge?) -> BorderEdge? { e.flatMap { $0.style == .none ? nil : BorderEdge(style: $0.style, color: RGBA(hex: $0.color.rgba >> 8)) } }420 s.borders = Borders(left: opaque(s.borders.left), right: opaque(s.borders.right), top: opaque(s.borders.top), bottom: opaque(s.borders.bottom))421 styleIDs.append(wb.styles.intern(s))422 }423 for name in ["First", "Second"] {424 let sheet = wb.addSheet(named: name)425 for _ in 0..<cells {426 let r = g.int(0...60), c = g.int(0...12)427 var cell = Cell(style: g.pick(styleIDs))428 switch g.int(0...9) {429 case 0...3: cell.value = .number(g.double())430 case 4, 5: cell.value = .text(g.pick(texts))431 case 6: cell.value = g.bool() ? .bool(g.bool()) : .error(g.pick([.div0, .na, .name, .null, .num, .ref, .value]))432 case 7, 8:433 let f = Formula(expr: try! FormulaParser.parse(g.pick(formulas)))434 f.value = g.pick([.number(g.double()), .text(g.pick(texts)), .bool(true), .error(.value)])435 cell.formula = f436 default: break437 }438 if case .text(let t) = cell.value, t.isEmpty { cell.value = .blank }439 sheet.cells.set(row: r, col: c, cell)440 }441 for _ in 0..<4 { sheet.meta.colWidths[g.int(0...12)] = Double(g.int(10...300)); sheet.meta.rowHeights[g.int(0...60)] = Double(g.int(5...200)) }442 sheet.meta.frozenRows = g.int(0...3); sheet.meta.frozenCols = g.int(0...2)443 }444 return wb445}446