1package fileio23import (4 "bytes"5 "context"6 "os"7 "path/filepath"8 "strings"9 "testing"1011 "github.com/xuri/excelize/v2"1213 "src.rest/cat/012/internal/sheet"14 "src.rest/cat/012/internal/stress"15)1617// TestXLSXDifferential imports a corpus of workbooks with excelize (the18// reader 012 used before its own) and with 012's streaming reader, and19// checks the results are the same: every sheet's cells as saved (input,20// format and style), column widths, named ranges, the sheet shown, the21// rows counted and the notes. The corpus is workbooks written here, the22// stress datasets' XLSX files when fetched (scripts/stress-data.sh), and23// every .xlsx file in the directories listed in XLSX_CORPUS24// (colon-separated), such as excelize's own test files.25//26// Known differences, where excelize's reading is wrong and 012's isn't,27// are left out of the comparison (see compareXLSX).28func TestXLSXDifferential(t *testing.T) {29 for _, path := range xlsxCorpus(t) {30 t.Run(filepath.Base(path), func(t *testing.T) {31 want, errWant := importXLSXExcelize(context.Background(), path, Options{})32 got, errGot := importXLSX(context.Background(), path, Options{})33 if (errWant == nil) != (errGot == nil) {34 t.Fatalf("excelize: %v; 012: %v", errWant, errGot)35 }36 if errWant != nil {37 t.Logf("both fail: excelize: %v; 012: %v", errWant, errGot)38 return39 }40 compareXLSX(t, path, want, got)41 })42 }43}4445func xlsxCorpus(t *testing.T) []string {46 dir := t.TempDir()47 files := []string{writeParts(t, dir, "kitchen.xlsx", kitchenParts())}48 files = append(files, writeExcelizeBook(t, dir), writeExportedBook(t, dir))49 stressData := os.Getenv("STRESS_DIR")50 if stressData == "" {51 stressData = filepath.Join("..", "..", ".deps", "stress")52 }53 files = append(files, globXLSX(stressData)...)54 for _, d := range filepath.SplitList(os.Getenv("XLSX_CORPUS")) {55 files = append(files, globXLSX(d)...)56 }57 return files58}5960func globXLSX(dir string) []string {61 if dir == "" {62 return nil63 }64 m, _ := filepath.Glob(filepath.Join(dir, "*.xlsx"))65 return m66}6768// writeExcelizeBook writes a workbook with excelize: sheets referring to69// each other, styles, widths and names.70func writeExcelizeBook(t *testing.T, dir string) string {71 x := excelize.NewFile()72 defer x.Close()73 x.SetSheetName("Sheet1", "Q1")74 x.NewSheet("Q2 plan")75 x.SetSheetRow("Q1", "A1", &[]any{"Region", 1234.5, "=text", 45000, true, "12%", "TRUE", -0.5})76 x.SetCellFormula("Q1", "A2", "'Q2 plan'!A1*2")77 x.SetCellFormula("Q1", "B2", "SUM(B1,1)")78 x.SetCellFormula("Q1", "C2", "CUBEVALUE(1)")79 x.SetCellValue("Q1", "E4", "after a gap")80 x.SetCellValue("Q2 plan", "A1", 42)81 x.SetCellValue("Q2 plan", "B300", "far down")82 bold, _ := x.NewStyle(&excelize.Style{NumFmt: 4, Font: &excelize.Font{Bold: true, Underline: "double"}})83 date, _ := x.NewStyle(&excelize.Style{NumFmt: 15, Alignment: &excelize.Alignment{Horizontal: "right"}})84 code := `[$$-409]#,##0.00`85 money, _ := x.NewStyle(&excelize.Style{CustomNumFmt: &code, Font: &excelize.Font{Italic: true, Strike: true}})86 x.SetCellStyle("Q1", "B1", "B1", bold)87 x.SetCellStyle("Q1", "D1", "D1", date)88 x.SetCellStyle("Q1", "A2", "C3", money)89 x.SetColWidth("Q1", "A", "B", 14)90 x.SetColWidth("Q2 plan", "C", "C", 30)91 x.SetDefinedName(&excelize.DefinedName{Name: "Rent", RefersTo: "Q1!$B$1"})92 x.SetDefinedName(&excelize.DefinedName{Name: "Mine", RefersTo: "Q1!$B$1", Scope: "Q2 plan"})93 // Frozen panes, and filters: values, conditions and one 012 can't apply.94 x.SetPanes("Q1", &excelize.Panes{Freeze: true, XSplit: 1, YSplit: 1, TopLeftCell: "B2", ActivePane: "bottomRight"})95 x.SetPanes("Q2 plan", &excelize.Panes{Freeze: true, YSplit: 2, TopLeftCell: "A3", ActivePane: "bottomLeft"})96 x.AutoFilter("Q1", "A1:H4", []excelize.AutoFilterOptions{{Column: "E", Expression: "x == *gap"}, {Column: "B", Expression: "x > 1 and x < 5000"}})97 for i, fruit := range []string{"Fruit", "Apple", "Pear", "banana", "Plum", "Apple"} {98 x.SetSheetRow("Q2 plan", "A"+itoa(10+i), &[]any{fruit, i})99 }100 x.AutoFilter("Q2 plan", "A10:B15", []excelize.AutoFilterOptions{{Column: "A", Expression: "x == apple or x == Plum"}, {Column: "B", Expression: "x >= 2"}})101 // Notes, one in rich-text runs, and a protected sheet.102 x.AddComment("Q1", excelize.Comment{Cell: "B1", Author: "Ann", Text: "Checked\nwith the bank"})103 x.AddComment("Q2 plan", excelize.Comment{Cell: "D9", Paragraph: []excelize.RichTextRun{{Text: "Due: ", Font: &excelize.Font{Bold: true}}, {Text: "Friday"}}})104 x.ProtectSheet("Q2 plan", &excelize.SheetProtectionOptions{})105 x.SetActiveSheet(1)106 path := filepath.Join(dir, "excelize.xlsx")107 if err := x.SaveAs(path); err != nil {108 t.Fatal(err)109 }110 return path111}112113// writeExportedBook writes a table and a sheet of formulas with 012's114// exporter.115func writeExportedBook(t *testing.T, dir string) string {116 s := stress.Table(60, 10)117 book := s.Book()118 f, _ := book.AddSheet("Formulas", 1)119 for row := range 40 {120 f.Set(sheet.Addr{Col: 0, Row: row}, "=Sheet1!C"+itoa(row+2)+"*2")121 f.Set(sheet.Addr{Col: 1, Row: row}, "=SUM($A$1:A"+itoa(row+1)+")")122 }123 f.SetColWidth(0, 20)124 f.SetFrozen(1, 2)125 f.CreateFilter(sheet.NewRect(sheet.Addr{}, sheet.Addr{Col: 1, Row: 39}))126 f.FilterColumn(1, sheet.Criteria{Cond: sheet.Condition{Op: sheet.CondGreater, Arg: "500"}})127 s.SetFrozen(1, 0)128 s.CreateFilter(sheet.NewRect(sheet.Addr{}, sheet.Addr{Col: 9, Row: 60}))129 s.FilterColumn(1, sheet.Criteria{Hidden: []string{"beta", "gamma"}})130 s.FilterColumn(3, sheet.Criteria{Cond: sheet.Condition{Op: sheet.CondContains, Arg: "1"}})131 s.SetNote(sheet.Addr{Col: 1, Row: 2}, "A note\non two lines & <markup>")132 f.SetNote(sheet.Addr{Col: 5, Row: 50}, "On a blank cell")133 path := filepath.Join(dir, "exported.xlsx")134 if _, err := Export(context.Background(), path, XLSX, SnapBook(f), ExportOptions{}); err != nil {135 t.Fatal(err)136 }137 return path138}139140// compareXLSX compares two imports of path.141func compareXLSX(t *testing.T, path string, want, got *Result) {142 t.Helper()143 if want.Rows != got.Rows {144 t.Errorf("rows %d, excelize %d", got.Rows, want.Rows)145 }146 if w, g := strings.Join(want.Notes, "; "), strings.Join(got.Notes, "; "); w != g {147 t.Errorf("notes %q, excelize %q", g, w)148 }149 if want.Sheet.Name() != got.Sheet.Name() {150 t.Errorf("showing %s, excelize %s", got.Sheet.Name(), want.Sheet.Name())151 }152 skip := mergedCells(t, path)153 w, g := savedLines(t, want.Sheet, skip), savedLines(t, got.Sheet, skip)154 if w == g {155 return156 }157 wl, gl := strings.Split(w, "\n"), strings.Split(g, "\n")158 shown := 0159 for i := 0; i < max(len(wl), len(gl)) && shown < 20; i++ {160 var a, b string161 if i < len(wl) {162 a = wl[i]163 }164 if i < len(gl) {165 b = gl[i]166 }167 if a != b && !sameSharedFormula(a, b) {168 t.Errorf("line %d:\n 012: %s\n excelize: %s", i+1, b, a)169 shown++170 }171 }172}173174// sameSharedFormula reports whether two saved lines are the same formula175// but for spaces. excelize expands a shared formula by parsing it and176// writing it back, which drops the spaces between its tokens; 012 moves177// the references in the text as written.178func sameSharedFormula(a, b string) bool {179 formula := strings.Contains(a, `": "=`) || strings.Contains(a, `"input":"=`)180 return formula && strings.ReplaceAll(a, " ", "") == strings.ReplaceAll(b, " ", "")181}182183// savedLines is the workbook as 012 saves it, without the cells in skip184// (by sheet), which excelize reads wrongly.185func savedLines(t *testing.T, s *sheet.Sheet, skip map[string][]sheet.Rect) string {186 t.Helper()187 book := s.Book()188 for i := range book.Len() {189 ws := book.Sheet(i)190 for _, r := range skip[ws.Name()] {191 for _, a := range ws.Addrs() {192 if r.Contains(a) && a != r.From {193 ws.EraseRange(sheet.NewRect(a, a))194 }195 }196 }197 }198 var buf bytes.Buffer199 if err := s.Write(&buf); err != nil {200 t.Fatal(err)201 }202 return buf.String()203}204205// mergedCells are the merged ranges of each sheet. excelize reads any206// cell of a merged range as its first cell, so a formula there spreads207// to the others (when a cell after them in the row has a value); 012208// reads each cell as written.209func mergedCells(t *testing.T, path string) map[string][]sheet.Rect {210 x, err := excelize.OpenFile(path)211 if err != nil {212 t.Fatal(err)213 }214 defer x.Close()215 out := map[string][]sheet.Rect{}216 for _, ws := range x.GetSheetList() {217 merged, _ := x.GetMergeCells(ws)218 for _, m := range merged {219 if r, ok := sheet.ParseRange(m.GetStartAxis() + ":" + m.GetEndAxis()); ok {220 out[ws] = append(out[ws], r)221 }222 }223 }224 return out225}226227func itoa(n int) string { return numInput(float64(n)) }