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)1314// Tables go out as Excel's tables, with their filters and style, and15// formulas reading them in Excel's syntax; excelize and 012 read them16// back.17func TestXLSXTables(t *testing.T) {18 src := build(t, map[string]string{19 "A1": "Region", "B1": "2024", "C1": "Amount",20 "A2": "North", "B2": "2", "C2": "10",21 "A3": "South", "B3": "3", "C3": "20",22 "E1": "=SUM(Sales[Amount])", "D2": "=Sales[@Amount]*2",23 })24 if err := src.CreateTable("Sales", rect(t, "A1:C3")); err != nil {25 t.Fatal(err)26 }27 src.Book().SetTableStyle("Sales", false, true)28 src.CreateFilter(rect(t, "A1:C3"))29 src.FilterColumn(0, sheet.Criteria{Hidden: []string{"South"}})30 name := filepath.Join(t.TempDir(), "tables.xlsx")31 if _, err := Export(context.Background(), name, XLSX, SnapBook(src), ExportOptions{}); err != nil {32 t.Fatal(err)33 }34 x, err := excelize.OpenFile(name)35 if err != nil {36 t.Fatal(err)37 }38 ts, err := x.GetTables("Sheet1")39 if err != nil || len(ts) != 1 || ts[0].Name != "Sales" || ts[0].Range != "A1:C3" || ts[0].StyleName != "TableStyleMedium2" {40 t.Errorf("excelize tables %+v, %v", ts, err)41 }42 if f, _ := x.GetCellFormula("Sheet1", "D2"); f != "Sales[[#This Row],[Amount]]*2" {43 t.Errorf("formula %q", f)44 }45 if v, _ := x.GetCellValue("Sheet1", "B1"); v != "2024" {46 t.Errorf("header %q", v)47 }48 x.Close()49 got, err := Import(context.Background(), name, Options{})50 if err != nil {51 t.Fatal(err)52 }53 s := got.Sheet54 tb, ok := s.TableAt(addr(t, "B2"))55 if !ok || tb.Name != "Sales" || tb.Range != rect(t, "A1:C3") || tb.Banded || !tb.Header || strings.Join(tb.Cols, ",") != "Region,2024,Amount" {56 t.Fatalf("table read back %+v", tb)57 }58 if r, ok := s.FilterRange(); !ok || r != rect(t, "A1:C3") || !s.RowHidden(2) {59 t.Errorf("filter %v %v, row 3 hidden %v", r, ok, s.RowHidden(2))60 }61 for a, want := range map[string]string{"E1": "30", "D2": "20"} {62 if v := s.Value(addr(t, a)).String(); v != want {63 t.Errorf("%s = %s, want %s", a, v, want)64 }65 }66}6768// A formula reading a table the file doesn't hold, a notebook output's,69// is written as its value, as Excel would refuse it.70func TestXLSXUnknownTable(t *testing.T) {71 src := build(t, map[string]string{"A1": "=SUM(app[ms])+1"})72 name := filepath.Join(t.TempDir(), "outputs.xlsx")73 res, err := Export(context.Background(), name, XLSX, SnapBook(src), ExportOptions{})74 if err != nil {75 t.Fatal(err)76 }77 if !strings.Contains(strings.Join(res.Notes, "; "), "saved as values") {78 t.Errorf("notes %q", res.Notes)79 }80 x, _ := excelize.OpenFile(name)81 defer x.Close()82 if f, _ := x.GetCellFormula("Sheet1", "A1"); f != "" {83 t.Errorf("formula kept: %q", f)84 }85}8687// Tables Excel makes, a totals row and all, come in as tables.88func TestXLSXTablesFromExcelize(t *testing.T) {89 x := excelize.NewFile()90 for a, v := range map[string]any{"A1": "Item", "B1": "Cost", "A2": "Rent", "B2": 1450, "A3": "Food", "B3": 300, "A4": "Total", "B4": 1750} {91 x.SetCellValue("Sheet1", a, v)92 }93 x.SetCellFormula("Sheet1", "D1", "SUM(Costs[Cost])")94 stripes := true95 if err := x.AddTable("Sheet1", &excelize.Table{Range: "A1:B3", Name: "Costs", StyleName: "TableStyleLight9", ShowRowStripes: &stripes}); err != nil {96 t.Fatal(err)97 }98 name := filepath.Join(t.TempDir(), "excelize.xlsx")99 if err := x.SaveAs(name); err != nil {100 t.Fatal(err)101 }102 x.Close()103 got, err := Import(context.Background(), name, Options{})104 if err != nil {105 t.Fatal(err)106 }107 s := got.Sheet108 tb, ok := s.TableAt(addr(t, "A2"))109 if !ok || tb.Name != "Costs" || !tb.Banded || !tb.Header || strings.Join(tb.Cols, ",") != "Item,Cost" {110 t.Fatalf("table %+v", tb)111 }112 if v := s.Value(addr(t, "D1")).String(); v != "1750" {113 t.Errorf("D1 = %s", v)114 }115}116117// A table's totals row stays cells below it.118func TestXLSXTableTotalsRow(t *testing.T) {119 b, err := readParts(t, tableParts(), defaultXLSXLimits)120 if err != nil {121 t.Fatal(err)122 }123 s := b.Sheet124 tb, ok := s.TableAt(addr(t, "A1"))125 if !ok || tb.Range != rect(t, "A1:B2") || !tb.Banded || !tb.Header {126 t.Fatalf("table %+v", tb)127 }128 if v := s.Value(addr(t, "C2")).String(); v != "10" {129 t.Errorf("C2 = %s", v)130 }131}132133func rect(t *testing.T, s string) sheet.Rect {134 t.Helper()135 r, ok := sheet.ParseRange(s)136 if !ok {137 t.Fatalf("bad range %s", s)138 }139 return r140}