SPB Git

Repository Public

XLSXWriteTests.swift 13.9 KB 227 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetIO/Tests/SheetIOTests/XLSXWriteTests.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/// A workbook using every feature the XLSX writer supports, with values Excel can represent.93func makeFeatureWorkbook() -> Workbook {94    let wb = Workbook()95    let s = wb.addSheet(named: "Data")96    let o = wb.addSheet(named: "Other Sheet")97    let h = wb.addSheet(named: "Hidden")98    h.meta.isHidden = true99    wb.properties.title = "Feature workbook"; wb.properties.author = "ORIX <Tests> & co"; wb.properties.subject = "Sujet"100    wb.properties.keywords = "a, b"; wb.properties.comments = "Multi\nline"101    wb.properties.created = Date(timeIntervalSince1970: 1_700_000_000); wb.properties.modified = Date(timeIntervalSince1970: 1_700_003_600)102103    var bold = CellStyle(); bold.bold = true; bold.fill = RGBA(hex: 0xFFE699); bold.hAlign = .center; bold.vAlign = .center104    bold.borders = Borders(left: BorderEdge(style: .thin), right: BorderEdge(style: .medium, color: RGBA(hex: 0xFF0000)), top: BorderEdge(style: .double), bottom: BorderEdge(style: .dashed, color: RGBA(hex: 0x0000FF)))105    let boldID = wb.styles.intern(bold)106    var money = CellStyle(); money.numberFormat = "#,##0.00"; money.textColor = RGBA(hex: 0x006100); money.italic = true107    let moneyID = wb.styles.intern(money)108    var date = CellStyle(); date.numberFormat = "yyyy-mm-dd"109    let dateID = wb.styles.intern(date)110    var custom = CellStyle(); custom.numberFormat = "0.000\" kg\""; custom.fontName = "Arial"; custom.fontSize = 14; custom.underline = .double111    custom.strikethrough = true; custom.wrap = true; custom.rotation = 45; custom.indent = 2; custom.locked = false; custom.formulaHidden = true112    let customID = wb.styles.intern(custom)113    var pct = CellStyle(); pct.numberFormat = "0.00%"; pct.rotation = -30; pct.shrinkToFit = true; pct.hAlign = .right; pct.vAlign = .top114    let pctID = wb.styles.intern(pct)115116    for (i, hname) in ["Item", "Qty", "Price", "Total", "When"].enumerated() { s.cells.set(row: 0, col: i, Cell(value: .text(hname), style: boldID)) }117    let items = ["Apple", "Pêche & crème", "  spaced  ", "Line1\nLine2", "<tag> \"q\" 'a'", "日本語 😀"]118    for (i, name) in items.enumerated() {119        let r = i + 1120        s.cells.set(row: r, col: 0, Cell(value: .text(name)))121        s.cells.set(row: r, col: 1, Cell(value: .number(Double(i + 1))))122        s.cells.set(row: r, col: 2, Cell(value: .number(1.25 * Double(i + 1) + 0.1), style: moneyID))123        setFormula(s, "D\(r + 1)", "B\(r + 1)*C\(r + 1)", cached: .number(Double(i + 1) * (1.25 * Double(i + 1) + 0.1)), style: moneyID)124        s.cells.set(row: r, col: 4, Cell(value: .number(45000 + Double(i)), style: dateID))125    }126    setFormula(s, "D8", "SUM(D2:D7)", cached: .number(123.5), style: moneyID)127    setFormula(s, "A9", "IF(B2>0,\"pos\",\"neg\")", cached: .text("pos"))128    setFormula(s, "B9", "B2>1", cached: .bool(false))129    setFormula(s, "C9", "1/0", cached: .error(.div0))130    setFormula(s, "D9", "'Other Sheet'!A1+MyRange", cached: .number(7))131    setFormula(s, "E9", "XLOOKUP(2,B2:B7,A2:A7)&TEXTJOIN(\",\",TRUE,A2:A3)", cached: .text("x"))132    setFormula(s, "G2", "SEQUENCE(3,2)", cached: .number(1), spill: "G2:H4")133    for (r, c, v) in [(2, 6, 3.0), (3, 6, 5.0), (1, 7, 2.0), (2, 7, 4.0), (3, 7, 6.0)] { s.cells.set(row: r, col: c, Cell(value: .number(v), isSpill: true)) }134    setFormula(s, "J2", "SUM(G2#)", cached: .number(21))135    setFormula(s, "J3", "LET(x,B2,y,x*2,x+y)", cached: .number(3))136    setFormula(s, "J4", "LAMBDA(a,b,a+b)(1,2)", cached: .number(3))137    setCell(s, "A11", .bool(true)); setCell(s, "B11", .error(.na)); setCell(s, "C11", .number(1e-12), style: customID)138    setCell(s, "D11", .number(0.4567), style: pctID); setCell(s, "E11", .number(-1234567.891)); setCell(s, "F11", .text("_x0041_ literal"))139    s.cells.set(row: 12, col: 0, Cell(style: boldID))         // style-only cell140141    s.meta.colWidths = [0: 120, 1: 60, 2: 60, 9: 31.5]142    s.meta.rowHeights = [0: 30, 4: 45.5]143    s.meta.hiddenCols = [5]; s.meta.hiddenRows = [14]144    s.meta.rowOutline = [16: 1, 17: 2]; s.meta.colOutline = [11: 1]145    s.meta.colStyles = [12: moneyID]; s.meta.rowStyles = [19: boldID]146    s.meta.merges = [RangeRef(a1: "A14:C14")!, RangeRef(a1: "L2:M5")!]147    s.meta.frozenRows = 1; s.meta.frozenCols = 2148    s.meta.zoom = 1.25; s.meta.showGridlines = false; s.meta.tabColor = RGBA(hex: 0x00B050)149    s.meta.hyperlinks[CellRef(a1: "A2")!] = Hyperlink(target: "https://example.com/path?x=1&y=2", tooltip: "Go", display: nil)150    s.meta.hyperlinks[CellRef(a1: "A3")!] = Hyperlink(target: "#'Other Sheet'!B2")151    s.meta.comments[CellRef(a1: "B2")!] = CellComment(author: "Simon", text: "A note\nwith <two> lines")152    s.meta.comments[CellRef(a1: "A1")!] = CellComment(author: "Other", text: "First cell")153    var list = DataValidation(ranges: [RangeRef(a1: "N2:N10")!], kind: .list, formula1: "\"Yes,No,Maybe\"")154    list.errorTitle = "Invalid"; list.errorMessage = "Pick one"; list.promptTitle = "Choice"; list.promptMessage = "Pick a value"; list.errorStyle = "warning"155    var whole = DataValidation(ranges: [RangeRef(a1: "O2:O10")!, RangeRef(a1: "Q2")!], kind: .whole, op: "between", formula1: "1", formula2: "10")156    whole.allowBlank = false157    let custV = DataValidation(ranges: [RangeRef(a1: "P2")!], kind: .custom, formula1: "ISNUMBER(P2)")158    var listRef = DataValidation(ranges: [RangeRef(a1: "R2")!], kind: .list, formula1: "'Other Sheet'!$A$1:$A$3")159    listRef.showDropdown = false160    s.meta.validations = [list, whole, custV, listRef]161    let red = DiffStyle(bold: true, textColor: RGBA(hex: 0x9C0006), fill: RGBA(hex: 0xFFC7CE))162    s.meta.conditionalFormats = [163        ConditionalFormat(ranges: [RangeRef(a1: "B2:B7")!], kind: .cellIs(op: "greaterThan", formulas: ["3"]), style: red, priority: 1),164        ConditionalFormat(ranges: [RangeRef(a1: "C2:C7")!], kind: .cellIs(op: "between", formulas: ["2", "5"]), style: red, priority: 2),165        ConditionalFormat(ranges: [RangeRef(a1: "A2:E7")!], kind: .formula("MOD(ROW(),2)=0"), style: DiffStyle(fill: RGBA(hex: 0xDDEBF7)), priority: 3),166        ConditionalFormat(ranges: [RangeRef(a1: "D2:D7")!], kind: .colorScale(points: [ScalePoint(kind: "min", color: RGBA(hex: 0xF8696B)), ScalePoint(kind: "percentile", value: "50", color: RGBA(hex: 0xFFEB84)), ScalePoint(kind: "max", color: RGBA(hex: 0x63BE7B))]), priority: 4),167        ConditionalFormat(ranges: [RangeRef(a1: "B2:B7")!], kind: .dataBar(min: ScalePoint(kind: "min"), max: ScalePoint(kind: "max"), color: RGBA(hex: 0x638EC6)), priority: 5),168        ConditionalFormat(ranges: [RangeRef(a1: "C2:C7")!], kind: .iconSet(name: "3TrafficLights1", thresholds: [ScalePoint(kind: "percent", value: "0"), ScalePoint(kind: "percent", value: "33"), ScalePoint(kind: "percent", value: "67")], reversed: true), priority: 6),169        ConditionalFormat(ranges: [RangeRef(a1: "A2:A7")!], kind: .duplicates(unique: false), style: red, priority: 7),170        ConditionalFormat(ranges: [RangeRef(a1: "D2:D7")!], kind: .top(rank: 2, percent: false, bottom: true), style: red, priority: 8),171        ConditionalFormat(ranges: [RangeRef(a1: "D2:D7")!], kind: .aboveAverage(above: false, equal: true), style: red, priority: 9),172        ConditionalFormat(ranges: [RangeRef(a1: "A2:A7")!], kind: .containsText("App"), style: red, priority: 10),173        ConditionalFormat(ranges: [RangeRef(a1: "A2:A12")!], kind: .blanks(true), style: red, priority: 11),174        ConditionalFormat(ranges: [RangeRef(a1: "C9:C12")!, RangeRef(a1: "E9")!], kind: .errors(true), style: red, priority: 12),175    ]176    s.meta.conditionalFormats[2].stopIfTrue = true177    var p = PrintSettings()178    p.landscape = true; p.paperName = "A4"; p.paperWidthPt = 595.28; p.paperHeightPt = 841.89179    p.marginTop = 72; p.marginBottom = 36; p.marginLeft = 54; p.marginRight = 54; p.headerMargin = 18; p.footerMargin = 18180    p.header = "&CFeature &P"; p.footer = "&RPage &P of &N"181    p.fitToWidth = 1; p.fitToHeight = 0182    p.printArea = RangeRef(a1: "A1:J20")!; p.repeatRows = 0...0; p.repeatCols = 0...1183    p.rowBreaks = [10]; p.colBreaks = [5]; p.showGridlines = true; p.centerHorizontally = true184    s.meta.print = p185186    // second sheet: table, autofilter, sheet-scoped name, protection187    for (i, hname) in ["Region", "Amount", "Tax"].enumerated() { o.cells.set(row: 0, col: i, Cell(value: .text(hname))) }188    for r in 1...4 {189        o.cells.set(row: r, col: 0, Cell(value: .text(["N", "S", "E", "W"][r - 1])))190        o.cells.set(row: r, col: 1, Cell(value: .number(Double(r * 100))))191        setFormula(o, "C\(r + 1)", "Table1[[#This Row],[Amount]]*0.15", cached: .number(Double(r * 15)))192    }193    setCell(o, "A6", .text("Total")); setFormula(o, "B6", "SUBTOTAL(109,Table1[Amount])", cached: .number(1000))194    var t = TableDef(id: 1, name: "Table1", range: RangeRef(a1: "A1:C6")!, columns: [TableColumn(name: "Region"), TableColumn(name: "Amount", totalsFunction: "sum"), TableColumn(name: "Tax", calculatedFormula: "Table1[[#This Row],[Amount]]*0.15")])195    t.hasTotals = true; t.styleName = "TableStyleLight9"; t.showRowStripes = true196    o.meta.tables = [t]197    for r in 0..<5 { o.cells.set(row: r + 9, col: 4, Cell(value: r == 0 ? .text("F") : .number(Double(r)))) }198    o.meta.autoFilter = RangeRef(a1: "E10:E14")!199    o.meta.isProtected = true200    wb.setDefinedName(DefinedName(name: "MyRange", formula: "Data!$B$2:$B$7", comment: "Quantities"))201    wb.setDefinedName(DefinedName(name: "Rate", formula: "0.15"))202    var local = DefinedName(name: "LocalName", formula: "'Other Sheet'!$B$2", scopeSheetID: o.id)203    local.hidden = false204    wb.setDefinedName(local)205    setCell(h, "A1", .number(42))206    wb.activeSheet = 1207    return wb208}209210@Suite struct XLSXWriteTests {211    @Test func featureWorkbookRoundTripsThroughOurReader() throws {212        let wb = makeFeatureWorkbook()213        let dir = Scratch.dir("xlsx")214        defer { try? FileManager.default.removeItem(at: dir) }215        let url = dir.appendingPathComponent("features.xlsx")216        let issues = try WorkbookWriter.save(wb, to: url, format: .xlsx)217        #expect(issues.filter { $0.severity == .loss }.isEmpty, "\(issues)")218        #expect(FileFormat.detect(url: url) == .xlsx)219        let loaded = try WorkbookReader.load(url: url)220        var o = WorkbookCompareOptions()221        o.sheetIDs = false222        let diff = WorkbookDiff.differences(wb, loaded.workbook, options: o)223        #expect(diff.isEmpty, "\(diff.joined(separator: "\n"))")224        #expect(loaded.issues.filter { $0.severity != .info && $0.feature != "Functions" }.isEmpty, "\(loaded.issues)")225    }226}227
Files main
Menu

Theme