1package fileio23import (4 "context"5 "reflect"6 "strings"7 "testing"89 "github.com/xuri/excelize/v2"1011 "src.rest/cat/012/internal/sheet"12)1314// lines are rules as the file writes them, for comparing.15func formatLines(s *sheet.Sheet) []string {16 var out []string17 for _, f := range s.CondFormats() {18 out = append(out, f.JSON())19 }20 return out21}2223func validationLines(s *sheet.Sheet) []string {24 var out []string25 for _, v := range s.Validations() {26 out = append(out, v.JSON())27 }28 return out29}3031func mustLoad(t *testing.T, s *sheet.Sheet, formats, validations []string) {32 t.Helper()33 var fs []sheet.CondFormat34 for _, l := range formats {35 f, err := sheet.ParseCondFormat(l)36 if err != nil {37 t.Fatalf("%s: %v", l, err)38 }39 fs = append(fs, f)40 }41 var vs []sheet.Validation42 for _, l := range validations {43 v, err := sheet.ParseValidation(l)44 if err != nil {45 t.Fatalf("%s: %v", l, err)46 }47 vs = append(vs, v)48 }49 if s.LoadCondFormats(fs)+s.LoadValidations(vs) > 0 {50 t.Fatal("rules left out")51 }52}5354// Every rule 012 has goes out as Excel's and comes back the same.55func TestXLSXRulesRoundTrip(t *testing.T) {56 src := months(t)57 lists, err := src.Book().AddSheet("Lists", 1)58 if err != nil {59 t.Fatal(err)60 }61 lists.Set(addr(t, "A1"), "North")62 formats := []string{63 `{"ranges":"B2:B5","condition":"gt","values":["100"],"fill":"green","bold":true}`,64 `{"ranges":"B2:B5","condition":"between","values":["1","10"],"text":"red","fill":"yellow"}`,65 `{"ranges":"C2:C5","condition":"contains","values":["o"],"italic":true}`,66 `{"ranges":"C2:C5","condition":"not_contains","values":["x"],"text":"blue"}`,67 `{"ranges":"C2:C5","condition":"starts_with","values":["re"],"underline":true}`,68 `{"ranges":"C2:C5","condition":"ends_with","values":["s"],"strikethrough":true}`,69 `{"ranges":"C2:C5","condition":"exactly","values":["Fees"],"fill":"magenta"}`,70 `{"ranges":"A2:A5,C2:C5","condition":"empty","fill":"cyan"}`,71 `{"ranges":"A2:A5","condition":"not_empty","text":"magenta"}`,72 `{"ranges":"A2:A5","condition":"date_is","values":["today"],"fill":"red"}`,73 `{"ranges":"A2:A5","condition":"date_before","values":["2026-09-30"],"fill":"red"}`,74 `{"ranges":"A2:A5","condition":"date_after","values":["yesterday"],"fill":"blue"}`,75 `{"ranges":"B2:B5","condition":"eq","values":["=B$2"],"fill":"blue"}`,76 `{"ranges":"A2:C5","condition":"formula","values":["=$B2>=Lists!$B$1"],"text":"green"}`,77 `{"ranges":"B2:B5","scale":[{"type":"min","color":"red"},{"type":"percentile","value":"50","color":"yellow"},{"type":"max","color":"green"}]}`,78 `{"ranges":"B2:B5","scale":[{"type":"num","value":"0","color":"cyan"},{"type":"percent","value":"90","color":"blue"}]}`,79 }80 validations := []string{81 `{"ranges":"D2:D5","criteria":"list","items":["Yes","No"],"reject":true,"help":"Pick one"}`,82 `{"ranges":"E2:E5","criteria":"range","source":"Lists!A1:A5"}`,83 `{"ranges":"F2:F5","criteria":"range","source":"A2:A5","reject":true}`,84 `{"ranges":"G2:G5","criteria":"checkbox"}`,85 `{"ranges":"H2:H5","criteria":"number","condition":"between","values":["1","10"],"reject":true}`,86 `{"ranges":"I2:I5","criteria":"date","condition":"gt","values":["2026-01-31"]}`,87 `{"ranges":"J2:J5","criteria":"date"}`,88 `{"ranges":"K2:K5","criteria":"length","condition":"le","values":["=$B$2"]}`,89 `{"ranges":"L2:L5","criteria":"formula","values":["=L2<>A2"]}`,90 }91 mustLoad(t, src, formats, validations)92 formats, validations = formatLines(src), validationLines(src)93 name, got := exportImport(t, src)94 if len(got.Notes) > 0 {95 t.Errorf("notes %q", got.Notes)96 }97 back := got.Sheet.Book().Sheet(0)98 if f := formatLines(back); !reflect.DeepEqual(f, formats) {99 t.Errorf("conditional formats:\n%s\nwant\n%s", strings.Join(f, "\n"), strings.Join(formats, "\n"))100 }101 if v := validationLines(back); !reflect.DeepEqual(v, validations) {102 t.Errorf("validations:\n%s\nwant\n%s", strings.Join(v, "\n"), strings.Join(validations, "\n"))103 }104 // excelize reads what Excel would.105 x, err := excelize.OpenFile(name)106 if err != nil {107 t.Fatal(err)108 }109 defer x.Close()110 // excelize keeps one rule per range, the last written there.111 cfs, err := x.GetConditionalFormats("Sheet1")112 if err != nil || len(cfs) != 5 { // by sqref: B2:B5, C2:C5, A2:A5 C2:C5, A2:A5, A2:C5113 t.Errorf("excelize: %d ranges %v", len(cfs), err)114 }115 if r := cfs["B2:B5"]; len(r) != 1 || r[0].Type != "2_color_scale" || r[0].MinType != "num" || r[0].MaxValue != "90" {116 t.Errorf("excelize B2:B5: %+v", r)117 }118 if r := cfs["C2:C5"]; len(r) != 1 || r[0].Type != "cell" || r[0].Criteria != "equal to" || r[0].Value != `"Fees"` {119 t.Errorf("excelize C2:C5: %+v", r)120 }121 dvs, err := x.GetDataValidations("Sheet1")122 if err != nil || len(dvs) != len(validations) {123 t.Fatalf("excelize: %d validations %v", len(dvs), err)124 }125 if dv := dvs[0]; dv.Type != "list" || dv.Formula1 != `"Yes,No"` || dv.ErrorStyle == nil || *dv.ErrorStyle != "stop" || dv.Prompt == nil || *dv.Prompt != "Pick one" {126 t.Errorf("excelize list: %+v", dv)127 }128 if dv := dvs[1]; dv.Formula1 != "Lists!$A$1:$A$5" {129 t.Errorf("excelize range: %+v", dv)130 }131 // excelize reports colors only for files with a theme part.132 style, err := x.GetConditionalStyle(0)133 if err != nil || style.Font == nil || !style.Font.Bold || style.Fill.Pattern != 1 {134 t.Errorf("excelize dxf 0: %+v %v", style, err)135 }136}137138// Rules only Excel has, or ones 012 can't write, are left out with a139// note.140func TestXLSXRulesLimits(t *testing.T) {141 s := months(t)142 mustLoad(t, s, []string{143 `{"ranges":"A2:A5","condition":"formula","values":["=B2>1#AND#B2<9"],"fill":"red"}`,144 }, []string{145 `{"ranges":"D2:D5","criteria":"list","items":["a,b","c"]}`,146 })147 _, got := exportImport(t, s)148 want := []string{149 "1 conditional format with no Excel equivalent left out, e.g. A2",150 "1 data validation rule Excel can't hold left out, e.g. D2 (list items with commas or quotes, or over 255 characters, or a formula with no Excel equivalent)",151 }152 if res, err := Export(context.Background(), t.TempDir()+"/x.xlsx", XLSX, SnapBook(s), ExportOptions{}); err != nil || !reflect.DeepEqual(res.Notes, want) {153 t.Errorf("export notes %q %v", res.Notes, err)154 }155 if len(got.Sheet.CondFormats())+len(got.Sheet.Validations()) != 0 {156 t.Error("rules came back")157 }158}159160// What Excel writes: theme and indexed colors, rules 012 doesn't have,161// validation in the 2010 extension.162func TestXLSXRulesImport(t *testing.T) {163 p := oneSheetParts("", `<row r="1"><c r="A1"><v>5</v></c></row>`)164 p["xl/styles.xml"] = `<styleSheet ` + mainNS + `><cellXfs count="1"><xf numFmtId="0" fontId="0"/></cellXfs><dxfs count="3">` +165 `<dxf><font><color rgb="FF9C0006"/></font><fill><patternFill><bgColor rgb="FFFFC7CE"/></patternFill></fill></dxf>` +166 `<dxf><font><b/><color theme="4"/></font></dxf>` +167 `<dxf><numFmt numFmtId="164" formatCode="0.0"/><border><left style="thin"/></border></dxf></dxfs></styleSheet>`168 p["xl/worksheets/sheet1.xml"] = `<worksheet ` + mainNS + ` xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">` +169 `<sheetData><row r="1"><c r="A1"><v>5</v></c></row></sheetData>` +170 `<conditionalFormatting sqref="A1:A10"><cfRule type="dataBar" priority="4"><dataBar><cfvo type="min"/><cfvo type="max"/><color rgb="FF638EC6"/></dataBar></cfRule>` +171 `<cfRule type="cellIs" dxfId="0" priority="2" operator="lessThan"><formula>10</formula></cfRule>` +172 `<cfRule type="iconSet" priority="5"><iconSet iconSet="3Arrows"><cfvo type="percent" val="0"/><cfvo type="percent" val="33"/><cfvo type="percent" val="67"/></iconSet></cfRule></conditionalFormatting>` +173 `<conditionalFormatting sqref="B1:B10 D1"><cfRule type="expression" dxfId="1" priority="1"><formula>INT(B1)<DATE(2026,1,5)</formula></cfRule>` +174 `<cfRule type="expression" dxfId="2" priority="3"><formula>B1>0</formula></cfRule>` +175 `<cfRule type="colorScale" priority="6"><colorScale><cfvo type="min"/><cfvo type="max"/><color theme="0"/><color rgb="FF57BB8A"/></colorScale></cfRule>` +176 `<cfRule type="timePeriod" dxfId="0" priority="7" timePeriod="lastWeek"><formula>x</formula></cfRule></conditionalFormatting>` +177 `<dataValidations count="4"><dataValidation type="list" allowBlank="1" showErrorMessage="1" sqref="C1:C9"><formula1>"Yes,No"</formula1></dataValidation>` +178 `<dataValidation type="whole" operator="greaterThanOrEqual" errorStyle="warning" showErrorMessage="1" error="Positive please" sqref="E1:E9"><formula1>0</formula1></dataValidation>` +179 `<dataValidation type="time" sqref="F1"><formula1>0.5</formula1></dataValidation>` +180 `<dataValidation type="list" sqref="G1"><formula1>Choices</formula1></dataValidation></dataValidations>` +181 `<extLst><ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}"><x14:dataValidations count="1"><x14:dataValidation type="list" allowBlank="1" showErrorMessage="1">` +182 `<x14:formula1><xm:f>Lists!$A$1:$A$5</xm:f></x14:formula1><xm:sqref>H1:H9</xm:sqref></x14:dataValidation></x14:dataValidations></ext>` +183 `<ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}"><x14:conditionalFormattings><x14:conditionalFormatting><x14:cfRule type="dataBar" id="{1}"/><xm:sqref>A1:A10</xm:sqref></x14:conditionalFormatting>` +184 `<x14:conditionalFormatting><x14:cfRule type="iconSet" id="{2}"><x14:iconSet iconSet="3Stars"/></x14:cfRule><xm:sqref>C1:C5</xm:sqref></x14:conditionalFormatting></x14:conditionalFormattings></ext></extLst></worksheet>`185 path := writeParts(t, t.TempDir(), "excel.xlsx", p)186 got, err := Import(context.Background(), path, Options{})187 if err != nil {188 t.Fatal(err)189 }190 s := got.Sheet191 wantF := []string{192 `{"ranges":"B1:B10,D1","condition":"date_before","values":["2026-01-05"],"text":"blue","bold":true}`,193 `{"ranges":"A1:A10","condition":"lt","values":["10"],"text":"red","fill":"red"}`,194 `{"ranges":"A1:A10","dataBar":{"color":"blue","min":{"type":"min"},"max":{"type":"max"}}}`,195 `{"ranges":"A1:A10","iconSet":{"icons":"arrows","points":[{"type":"percent","value":"33"},{"type":"percent","value":"67"}]}}`,196 `{"ranges":"B1:B10,D1","scale":[{"type":"min","color":"yellow"},{"type":"max","color":"green"}]}`,197 `{"ranges":"B1:B10,D1","condition":"date_is","values":["last week"],"text":"red","fill":"red"}`,198 }199 if f := formatLines(s); !reflect.DeepEqual(f, wantF) {200 t.Errorf("conditional formats:\n%s\nwant\n%s", strings.Join(f, "\n"), strings.Join(wantF, "\n"))201 }202 wantV := []string{203 `{"ranges":"C1:C9","criteria":"list","items":["Yes","No"],"reject":true}`,204 `{"ranges":"E1:E9","criteria":"number","condition":"ge","values":["0"],"help":"Positive please"}`,205 `{"ranges":"H1:H9","criteria":"range","source":"Lists!A1:A5","reject":true}`,206 }207 if v := validationLines(s); !reflect.DeepEqual(v, wantV) {208 t.Errorf("validations:\n%s\nwant\n%s", strings.Join(v, "\n"), strings.Join(wantV, "\n"))209 }210 wantNotes := []string{211 "2 conditional formats left out: in Excel 2010's extension (its own icon sets and the like) (1), without a text color, fill or text style 012 draws (1)",212 "2 data validation rules left out: lists from named ranges or formulas (1), time rules (1)",213 }214 if !reflect.DeepEqual(got.Notes, wantNotes) {215 t.Errorf("notes:\n%q\nwant\n%q", got.Notes, wantNotes)216 }217}