1package fileio23import (4 "context"5 "path/filepath"6 "strings"7 "testing"89 "github.com/xuri/excelize/v2"1011 "src.rest/cat/012/internal/sheet"12)1314func TestXLSXRoundTrip(t *testing.T) {15 src := build(t, map[string]string{16 "A1": "Bills", "B1": "Due", "C1": "Amount", "D1": "Share",17 "A2": "Rent", "B2": "10/1/2026", "C2": "$1,450.00", "D2": "=C2/C$4",18 "A3": "Food", "B3": "2026-09-28", "C3": "612.4", "D3": "=@ROUND(C3/C$4..C$4, 2)",19 "A4": "Total", "C4": "=SUM(C2:C3)", "D4": "=IFNA(XLOOKUP(\"Rent\",A2:A3,C2:C3),0)",20 "A5": "'123", "B5": "14:30", "C5": "TRUE", "D5": "1.5E+30",21 "A6": "old", "B6": "2/1/1900", "C6": "=C2>C3 #AND# C3>0", "E6": "=A1&\" x\"",22 })23 r := func(s string) sheet.Rect { return sheet.NewRect(addr(t, s), addr(t, s)) }24 src.SetFormat(r("D2"), sheet.Preset(sheet.FmtPercent))25 src.SetFormat(r("C3"), sheet.Format{Kind: sheet.FmtAccounting, Decimals: 1})26 src.SetFormat(r("B5"), sheet.Format{Kind: sheet.FmtTime, Pattern: "h:mm AM/PM"})27 src.SetFormat(r("A6"), sheet.Format{Kind: sheet.FmtCustom, Pattern: "0.000"})28 src.SetStyle(r("A1"), func(st *sheet.Style) { st.Bold, st.Italic = true, true })29 src.SetStyle(r("A2"), func(st *sheet.Style) { st.Underline, st.Strikethrough = true, true })30 src.SetStyle(r("B1"), func(st *sheet.Style) { st.Align = sheet.AlignRight })31 src.SetColWidth(0, 14)32 src.SetColWidth(3, 7)3334 name := filepath.Join(t.TempDir(), "bills.xlsx")35 res, err := Export(context.Background(), name, XLSX, Snap(src, sheet.Rect{}, "Bills"), ExportOptions{})36 if err != nil {37 t.Fatal(err)38 }39 if len(res.Notes) != 1 || !strings.Contains(res.Notes[0], "1 formula with no Excel equivalent saved as values, e.g. C6") {40 t.Errorf("notes %q", res.Notes)41 }4243 // Excel sees Excel formulas, with _xlfn. for newer functions.44 x, err := excelize.OpenFile(name)45 if err != nil {46 t.Fatal(err)47 }48 for cell, want := range map[string]string{49 "D3": "ROUND(C3/C$4:C$4, 2)", "D4": `_xlfn.IFNA(_xlfn.XLOOKUP("Rent",A2:A3,C2:C3),0)`, "C6": "",50 } {51 if got, _ := x.GetCellFormula("Bills", cell); got != want {52 t.Errorf("Excel formula in %s = %q, want %q", cell, got, want)53 }54 }55 if v, _ := x.GetCellValue("Bills", "C4"); v != "$2,062.40" {56 t.Errorf("cached C4 = %q", v)57 }58 x.Close()5960 got, err := Import(context.Background(), name, Options{})61 if err != nil {62 t.Fatal(err)63 }64 s := got.Sheet65 sameShown(t, src, s)66 for _, a := range src.Addrs() {67 if want, g := src.Cell(a).Style, s.Cell(a).Style; g != want {68 t.Errorf("%s style %+v, want %+v", a, g, want)69 }70 }71 for cell, want := range map[string]string{72 "D2": "=C2/C$4", "D3": "=ROUND(C3/C$4:C$4, 2)", "D4": `=IFNA(XLOOKUP("Rent",A2:A3,C2:C3),0)`,73 "A5": "'123", "C5": "TRUE", "C6": "TRUE", "E6": `=A1&" x"`,74 } {75 if g := input(s, addr(t, cell)); g != want {76 t.Errorf("%s input = %q, want %q", cell, g, want)77 }78 }79 for cell, want := range map[string]sheet.Format{80 "B2": {Kind: sheet.FmtDate}, "C3": {Kind: sheet.FmtAccounting, Decimals: 1},81 "B5": {Kind: sheet.FmtTime, Pattern: "h:mm AM/PM"}, "A6": {Kind: sheet.FmtCustom, Pattern: "0.000"},82 } {83 if g := s.Cell(addr(t, cell)).Format; g != want {84 t.Errorf("%s format %+v, want %+v", cell, g, want)85 }86 }87 if w := s.ColWidth(0); w != 14 {88 t.Errorf("A width = %d", w)89 }90 if w := s.ColWidth(3); w != 7 {91 t.Errorf("D width = %d", w)92 }93 if w := s.ColWidth(1); w != sheet.DefaultWidth {94 t.Errorf("B width = %d", w)95 }96}9798// TestXLSXImport reads a workbook written by excelize the way Excel99// writes one: several sheets referring to each other, shared strings,100// built-in formats, named ranges and formulas 012 can't read.101func TestXLSXImport(t *testing.T) {102 x := excelize.NewFile()103 x.SetSheetName("Sheet1", "Q1")104 x.NewSheet("Q2")105 x.NewSheet("Q3")106 x.SetCellValue("Q1", "A1", "Region")107 x.SetCellValue("Q1", "B1", 1234.5)108 x.SetCellValue("Q1", "C1", "=not a formula")109 x.SetCellValue("Q1", "D1", 45000)110 x.SetCellValue("Q1", "E1", true)111 x.SetCellFormula("Q1", "A2", "Q2!A1*2")112 x.SetCellValue("Q1", "A2", 84) // the cached value113 x.SetCellFormula("Q1", "A2", "Q2!A1*2")114 x.SetCellFormula("Q1", "B2", "SUM(B1,1)")115 x.SetCellFormula("Q1", "C2", "SUM('Q3 plan'!A1:A2)+Total")116 x.SetCellValue("Q1", "D2", 7)117 x.SetCellFormula("Q1", "D2", "CUBEVALUE(1)")118 x.SetCellValue("Q2", "A1", 42)119 x.SetSheetName("Q3", "Q3 plan")120 x.SetCellValue("Q3 plan", "A1", 1)121 x.SetCellValue("Q3 plan", "A2", 2)122 x.SetDefinedName(&excelize.DefinedName{Name: "Total", RefersTo: "Q2!$A$1"})123 x.SetDefinedName(&excelize.DefinedName{Name: "Local", RefersTo: "Q2!$A$1", Scope: "Q2"})124 x.SetActiveSheet(1)125 id, _ := x.NewStyle(&excelize.Style{NumFmt: 4, Font: &excelize.Font{Bold: true}})126 x.SetCellStyle("Q1", "B1", "B1", id)127 date, _ := x.NewStyle(&excelize.Style{NumFmt: 15})128 x.SetCellStyle("Q1", "D1", "D1", date)129 pct, _ := x.NewStyle(&excelize.Style{NumFmt: 10, Alignment: &excelize.Alignment{Horizontal: "center"}})130 x.SetCellStyle("Q1", "B2", "B2", pct)131 name := filepath.Join(t.TempDir(), "q.xlsx")132 if err := x.SaveAs(name); err != nil {133 t.Fatal(err)134 }135136 prog := NewProgress()137 res, err := Import(context.Background(), name, Options{Progress: prog})138 if err != nil {139 t.Fatal(err)140 }141 book := res.Sheet.Book()142 if book.Len() != 3 || res.Sheet.Name() != "Q2" || book.Sheet(2).Name() != "Q3 plan" {143 t.Fatalf("imported %d sheets, showing %s", book.Len(), res.Sheet.Name())144 }145 s := book.Sheet(0)146 for cell, want := range map[string]string{147 "A1": "Region", "B1": "1,234.50", "C1": "=not a formula", "D1": "15-Mar-23", "E1": "TRUE",148 "A2": "84", "B2": "123550.00%", "C2": "45", "D2": "7",149 } {150 if g := shown(s, addr(t, cell)); g != want {151 t.Errorf("%s shows %q, want %q", cell, g, want)152 }153 }154 if g := input(s, addr(t, "B2")); g != "=SUM(B1,1)" {155 t.Errorf("B2 input %q", g)156 }157 if st := s.Cell(addr(t, "B1")).Style; !st.Bold {158 t.Errorf("B1 not bold")159 }160 if st := s.Cell(addr(t, "B2")).Style; st.Align != sheet.AlignCenter {161 t.Errorf("B2 align %v", st.Align)162 }163 notes := strings.Join(res.Notes, "; ")164 for _, want := range []string{165 "1 formula kept as values, e.g. D2 =CUBEVALUE(1)",166 "1 named range left out, e.g. Local",167 } {168 if !strings.Contains(notes, want) {169 t.Errorf("notes %q, want %q", notes, want)170 }171 }172 if rows, frac := prog.Get(); rows != 5 {173 t.Errorf("progress %d rows, %v", rows, frac)174 }175}176177func TestFormatCodes(t *testing.T) {178 for _, tc := range []struct {179 id int180 code string181 want sheet.Format182 }{183 {0, "", sheet.Format{}},184 {0, "General", sheet.Format{}},185 {3, "", sheet.Format{Kind: sheet.FmtNumber}},186 {4, "", sheet.Format{Kind: sheet.FmtNumber, Decimals: 2}},187 {7, "", sheet.Format{Kind: sheet.FmtCurrency, Decimals: 2}},188 {9, "", sheet.Format{Kind: sheet.FmtPercent}},189 {11, "", sheet.Format{Kind: sheet.FmtScientific, Decimals: 2}},190 {14, "", sheet.Format{Kind: sheet.FmtDate}},191 {22, "", sheet.Format{Kind: sheet.FmtDateTime, Pattern: "m/d/yyyy h:mm"}},192 {44, "", sheet.Format{Kind: sheet.FmtAccounting, Decimals: 2}},193 {46, "", sheet.Format{Kind: sheet.FmtDuration}},194 {49, "", sheet.Format{Kind: sheet.FmtText}},195 {164, `\$#,##0.00`, sheet.Format{Kind: sheet.FmtCurrency, Decimals: 2}},196 {164, `[$$-409]#,##0`, sheet.Format{Kind: sheet.FmtCurrency}},197 {164, "yyyy-mm-dd", sheet.Format{Kind: sheet.FmtDate, Pattern: "yyyy-mm-dd"}},198 {164, "hh:mm", sheet.Format{Kind: sheet.FmtTime, Pattern: "hh:mm"}},199 {164, "0.0", sheet.Format{Kind: sheet.FmtCustom, Pattern: "0.0"}},200 {164, "[Red]0.00", sheet.Format{Kind: sheet.FmtCustom, Pattern: "[Red]0.00"}},201 } {202 if got := formatOf(tc.id, tc.code); got != tc.want {203 t.Errorf("formatOf(%d, %q) = %+v, want %+v", tc.id, tc.code, got, tc.want)204 }205 }206 // Every format 012 writes reads back as itself.207 kinds := []sheet.FormatKind{sheet.FmtSize}208 for k := sheet.FmtText; k <= sheet.FmtDuration; k++ {209 kinds = append(kinds, k)210 }211 for _, k := range kinds {212 for d := range 4 {213 f := sheet.Format{Kind: k}214 if sheet.Preset(k).Decimals > 0 {215 f.Decimals = d216 }217 if got := formatOf(164, excelCode(f)); got != f {218 t.Errorf("%+v -> %q -> %+v", f, excelCode(f), got)219 }220 }221 }222}223224func TestExcelFormulas(t *testing.T) {225 for _, tc := range []struct {226 in, want string227 ok bool228 }{229 {"=SUM(A1:B2)", "SUM(A1:B2)", true},230 {"=@SUM(A1..B2)", "SUM(A1:B2)", true},231 {`=IF(A1="@x..y",1,2)`, `IF(A1="@x..y",1,2)`, true},232 {"=@PI*2", "PI()*2", true},233 {"=$A$1*1.5E3", "$A$1*1.5E3", true},234 {"=xlookup(1,A:A,B:B)", "_xlfn.XLOOKUP(1,A:A,B:B)", true},235 {`=JEV.TEST(A2,"Is it?")`, "", false},236 {"=A1>1 #AND# B1<2", "", false},237 {"=#REF!+1", "#REF!+1", true},238 {"='Q3 #1'!A1..B2", "'Q3 #1'!A1:B2", true},239 {"=SUM('Bob''s'!A1)", "SUM('Bob''s'!A1)", true},240 {"='q3/q4'!A1+plan!B2:B3+Plan+'Plan'", "'Q3_Q4'!A1+'Plan (2)'!B2:B3+Plan+'Plan'", true},241 {`=PLAN(1)&"plan!"&Other!A1`, `PLAN(1)&"plan!"&Other!A1`, true},242 } {243 got, ok := toExcelFormula(tc.in, map[string]string{"Q3/Q4": "Q3_Q4", "PLAN": "Plan (2)"})244 if ok != tc.ok || ok && got != tc.want {245 t.Errorf("toExcelFormula(%q) = %q, %v, want %q, %v", tc.in, got, ok, tc.want, tc.ok)246 }247 }248 for in, want := range map[string]string{249 "SUM(A1:B2)": "=SUM(A1:B2)",250 "_xlfn.IFNA(_xlfn.XLOOKUP(1,A:A,B:B),0)": "=IFNA(XLOOKUP(1,A:A,B:B),0)",251 `"_xlfn.kept"&A1`: `="_xlfn.kept"&A1`,252 "'_xlfn.odd'!A1": "='_xlfn.odd'!A1",253 } {254 if got := fromExcelFormula(in); got != want {255 t.Errorf("fromExcelFormula(%q) = %q, want %q", in, got, want)256 }257 }258}259260// A pivot table goes out as the values it shows: Excel gets no pivot261// cache, and its results read back as plain cells.262func TestXLSXPivotAsValues(t *testing.T) {263 src := build(t, map[string]string{"A1": "Item", "B1": "Qty", "A2": "Pen", "B2": "3", "A3": "Ink", "B3": "4", "A4": "Pen", "B4": "5"})264 r := sheet.NewRect(addr(t, "A1"), addr(t, "B4"))265 p := sheet.NewPivot(src, r)266 p.Rows = []sheet.PivotGroup{{Col: 0}}267 p.Values = []sheet.PivotValue{{Col: 1, Summarize: sheet.SumBy}}268 pv, err := src.Book().CreatePivot(src, r, "", p)269 if err != nil {270 t.Fatal(err)271 }272 name := filepath.Join(t.TempDir(), "pivot.xlsx")273 if _, err := Export(context.Background(), name, XLSX, SnapBook(pv), ExportOptions{}); err != nil {274 t.Fatal(err)275 }276 x, err := excelize.OpenFile(name)277 if err != nil {278 t.Fatal(err)279 }280 defer x.Close()281 for cell, want := range map[string]string{"A1": "Item", "B1": "SUM of Qty", "A3": "Pen", "B3": "8", "B4": "12"} {282 if got, _ := x.GetCellValue("Pivot Table 1", cell); got != want {283 t.Errorf("%s = %q, want %q", cell, got, want)284 }285 if f, _ := x.GetCellFormula("Pivot Table 1", cell); f != "" {286 t.Errorf("%s has formula %q", cell, f)287 }288 }289}290291// Every sheet goes out to Excel and comes back, with references between292// sheets, the sheet shown and named ranges.293func TestXLSXWorkbookRoundTrip(t *testing.T) {294 src := build(t, map[string]string{"A1": "Rent", "B1": "1450", "B2": "='Q3 plan'!A1*2"})295 book := src.Book()296 book.RenameSheet(src, "Budget")297 plan, _ := book.AddSheet("Q3 plan", 1)298 plan.Set(addr(t, "A1"), "=Budget!B1+Rent")299 plan.Set(addr(t, "A2"), `=JEV.TEST(A1, "Big?")`)300 book.DefineName("Rent", src, sheet.NewRect(addr(t, "B1"), addr(t, "B1")))301302 name := filepath.Join(t.TempDir(), "book.xlsx")303 res, err := Export(context.Background(), name, XLSX, SnapBook(plan), ExportOptions{})304 if err != nil {305 t.Fatal(err)306 }307 if len(res.Notes) != 1 || !strings.Contains(res.Notes[0], "e.g. 'Q3 plan'!A2") {308 t.Errorf("notes %q", res.Notes)309 }310 x, err := excelize.OpenFile(name)311 if err != nil {312 t.Fatal(err)313 }314 if got, _ := x.GetCellFormula("Budget", "B2"); got != "'Q3 plan'!A1*2" {315 t.Errorf("Excel formula %q", got)316 }317 x.Close()318319 got, err := Import(context.Background(), name, Options{})320 if err != nil {321 t.Fatal(err)322 }323 gb := got.Sheet.Book()324 if gb.Len() != 2 || got.Sheet.Name() != "Q3 plan" {325 t.Fatalf("read back %d sheets, showing %s", gb.Len(), got.Sheet.Name())326 }327 if v := shown(gb.Sheet(0), addr(t, "B2")); v != "5800" {328 t.Errorf("Budget!B2 shows %s", v)329 }330 if v := input(got.Sheet, addr(t, "A1")); v != "=Budget!B1+Rent" {331 t.Errorf("Q3 plan!A1 input %s", v)332 }333 if n, ok := gb.LookupName("Rent"); !ok || n.Ref() != "Budget!B1" {334 t.Errorf("name Rent: %v %s", ok, n.Ref())335 }336}337338// Text that XML can't hold, or that reads as one of SpreadsheetML's339// escapes, goes out escaped and comes back the same, through excelize340// and 012; text past Excel's limit is cut.341func TestXLSXWriteText(t *testing.T) {342 src := sheet.New()343 long := strings.Repeat("x", excelMaxText+10)344 want := map[string]string{345 "A1": "bell\x07 and nul\x00", "A2": "_x0041_ stays", "A3": `<&> "quoted" 'too'`,346 "A4": "cr\rend", "A5": " noncharacter", "A6": long, "A7": "emoji \U0001F642",347 }348 for cell, text := range want {349 if err := src.Load(addr(t, cell), "'"+text, sheet.Format{}, sheet.Style{}); err != nil {350 t.Fatal(err)351 }352 }353 src.RecalcAll()354 for cell := range want {355 want[cell] = src.Value(addr(t, cell)).Str // as 012 keeps it356 }357 want["A6"] = long[:excelMaxText]358 name := filepath.Join(t.TempDir(), "text.xlsx")359 if _, err := Export(context.Background(), name, XLSX, SnapBook(src), ExportOptions{}); err != nil {360 t.Fatal(err)361 }362 x, err := excelize.OpenFile(name)363 if err != nil {364 t.Fatal(err)365 }366 defer x.Close()367 for cell, text := range want {368 if got, _ := x.GetCellValue("Sheet1", cell); got != text {369 t.Errorf("excelize reads %s as %q, want %q", cell, trim(got), trim(text))370 }371 }372 res, err := Import(context.Background(), name, Options{})373 if err != nil {374 t.Fatal(err)375 }376 back := res.Sheet.Book().Sheet(0)377 for cell, text := range want {378 if got := input(back, addr(t, cell)); got != flatten(text) {379 t.Errorf("012 reads %s as %q, want %q", cell, trim(got), trim(text))380 }381 }382}383384func TestUniqueSheetName(t *testing.T) {385 used := map[string]bool{}386 long := strings.Repeat("x", 31)387 for _, tc := range [][2]string{{"Plan", "Plan"}, {"plan", "plan (2)"}, {"Plan", "Plan (3)"}, {long, long}, {long, long[:27] + " (2)"}} {388 if got := uniqueSheetName(tc[0], used); got != tc[1] {389 t.Errorf("uniqueSheetName(%q) = %q, want %q", tc[0], got, tc[1])390 }391 }392}393394func trim(s string) string {395 if len(s) > 40 {396 return s[:40] + "..."397 }398 return s399}400401// A formula naming a sheet the workbook doesn't have goes out as its402// value (#REF!), with a note, since Excel would refuse the reference.403func TestXLSXMissingSheetAsValue(t *testing.T) {404 src := build(t, map[string]string{"A1": "2", "B1": "=Gone!A1+1", "B2": "=A1*2", "B3": "=SUM('Old plan'!A1:A3, gone!B1)"})405 name := filepath.Join(t.TempDir(), "missing.xlsx")406 res, err := Export(context.Background(), name, XLSX, SnapBook(src), ExportOptions{})407 if err != nil {408 t.Fatal(err)409 }410 want := "2 formulas naming a sheet that doesn't exist saved as values, e.g. B1 (Gone)"411 if len(res.Notes) != 1 || res.Notes[0] != want {412 t.Errorf("notes %q, want %q", res.Notes, want)413 }414 x, err := excelize.OpenFile(name)415 if err != nil {416 t.Fatal(err)417 }418 defer x.Close()419 ws := x.GetSheetName(0)420 for cell, formula := range map[string]string{"B1": "", "B2": "A1*2", "B3": ""} {421 if got, _ := x.GetCellFormula(ws, cell); got != formula {422 t.Errorf("%s formula %q, want %q", cell, got, formula)423 }424 }425 if got, _ := x.GetCellValue(ws, "B1"); got != "#REF!" {426 t.Errorf("B1 value %q", got)427 }428}429430// Hidden sheets go out to Excel hidden, and come back hidden.431func TestXLSXHiddenSheets(t *testing.T) {432 src := build(t, map[string]string{"A1": "=Data!A1*2"})433 book := src.Book()434 data, _ := book.AddSheet("Data", 1)435 data.Set(addr(t, "A1"), "21")436 if err := book.HideSheet(data); err != nil {437 t.Fatal(err)438 }439 name := filepath.Join(t.TempDir(), "hidden.xlsx")440 if _, err := Export(context.Background(), name, XLSX, SnapBook(src), ExportOptions{}); err != nil {441 t.Fatal(err)442 }443 x, err := excelize.OpenFile(name)444 if err != nil {445 t.Fatal(err)446 }447 if shown, _ := x.GetSheetVisible("Data"); shown {448 t.Error("Data is visible in Excel")449 }450 if shown, _ := x.GetSheetVisible(x.GetSheetName(0)); !shown {451 t.Error("the first sheet is hidden in Excel")452 }453 x.Close()454 got, err := Import(context.Background(), name, Options{})455 if err != nil {456 t.Fatal(err)457 }458 gb := got.Sheet.Book()459 if !gb.Lookup("Data").Hidden() || got.Sheet.Hidden() || shown(got.Sheet, addr(t, "A1")) != "42" {460 t.Errorf("read back: Data hidden %v, shown %s hidden %v", gb.Lookup("Data").Hidden(), got.Sheet.Name(), got.Sheet.Hidden())461 }462}