1package oracle23import (4 "context"5 "path/filepath"6 "testing"78 "github.com/xuri/excelize/v2"910 "src.rest/cat/012/internal/fileio"11)1213// tableFormulas read the table Sales (A1:C5: Region, Units, Amount) by14// structured references, each in the cell it is written in. excel is15// what Excel gives where excelize can't compute a structured reference16// (it reads tables but its calculation engine doesn't resolve them); 01217// is checked against it there, and against excelize elsewhere.18var tableFormulas = []struct{ cell, formula, excel string }{19 {"E1", "SUM(Sales[Amount])", "100"},20 {"E2", "SUM(Sales[[Units]:[Amount]])", "113"},21 {"E3", "ROWS(Sales[#All])", "5"},22 {"E4", "ROWS(Sales)", "4"},23 {"E5", "Sales[[#Headers],[Units]]", "Units"},24 {"E6", `COUNTIF(Sales[Region],"North")`, "2"},25 {"D3", "Sales[[#This Row],[Amount]]*2", "40"},26 {"E7", "SUM(C2:C5)", ""},27}2829// TestTablesFromExcel builds a workbook with a table in excelize, as30// Excel would save it, and reads it with 012's XLSX reader: the table31// comes in, and formulas reading it compute what Excel computes.32func TestTablesFromExcel(t *testing.T) {33 x := excelize.NewFile()34 rows := [][]any{{"Region", "Units", "Amount"}, {"North", 2, 10}, {"South", 3, 20}, {"North", 1, 30}, {"East", 7, 40}}35 for i, row := range rows {36 cell, _ := excelize.CoordinatesToCellName(1, i+1)37 if err := x.SetSheetRow("Sheet1", cell, &row); err != nil {38 t.Fatal(err)39 }40 }41 if err := x.AddTable("Sheet1", &excelize.Table{Range: "A1:C5", Name: "Sales"}); err != nil {42 t.Fatal(err)43 }44 for _, tc := range tableFormulas {45 if err := x.SetCellFormula("Sheet1", tc.cell, tc.formula); err != nil {46 t.Fatal(err)47 }48 }49 name := filepath.Join(t.TempDir(), "tables.xlsx")50 if err := x.SaveAs(name); err != nil {51 t.Fatal(err)52 }53 got, err := fileio.Import(context.Background(), name, fileio.Options{})54 if err != nil {55 t.Fatal(err)56 }57 s := got.Sheet58 if tb, ok := s.TableAt(addr("B2")); !ok || tb.Name != "Sales" || tb.Range.String() != "A1:C5" {59 t.Fatalf("table %+v", tb)60 }61 for _, tc := range tableFormulas {62 ours := s.Value(addr(tc.cell))63 if tc.excel != "" {64 t.Logf("skip %s: excelize doesn't compute structured references", tc.formula)65 if !agree(ours, tc.excel) {66 t.Errorf("%s: 012 %s, Excel %s", tc.formula, show(ours), tc.excel)67 }68 continue69 }70 theirs, _ := x.CalcCellValue("Sheet1", tc.cell, excelize.Options{RawCellValue: true})71 if !agree(ours, theirs) {72 t.Errorf("%s: 012 %s, excelize %q", tc.formula, show(ours), theirs)73 }74 }75 x.Close()76}