1package fileio23import (4 "reflect"5 "strings"6 "testing"78 "github.com/xuri/excelize/v2"9)1011// Top and bottom values, averages, duplicates, periods Excel names,12// data bars and icon sets go out as Excel's own rules and come back the13// same; periods Excel has no name for, and date rules before or after a14// period, come back as custom formulas that test the same days.15func TestXLSXNewRulesRoundTrip(t *testing.T) {16 src := months(t)17 formats := []string{18 `{"ranges":"B2:B5","condition":"top","values":["2"],"fill":"green"}`,19 `{"ranges":"B2:B5","condition":"bottom_percent","values":["25"],"fill":"red"}`,20 `{"ranges":"B2:B5","condition":"top_percent","values":["10"],"bold":true}`,21 `{"ranges":"B2:B5","condition":"bottom","values":["1"],"italic":true}`,22 `{"ranges":"B2:B5","condition":"above_average","text":"blue"}`,23 `{"ranges":"B2:B5","condition":"below_average","text":"red"}`,24 `{"ranges":"C2:C5","condition":"duplicate","fill":"yellow"}`,25 `{"ranges":"C2:C5","condition":"unique","underline":true}`,26 `{"ranges":"A2:A5","condition":"date_is","values":["this week"],"fill":"cyan"}`,27 `{"ranges":"A2:A5","condition":"date_is","values":["past week"],"fill":"cyan"}`,28 `{"ranges":"A2:A5","condition":"date_is","values":["next month"],"fill":"cyan"}`,29 `{"ranges":"B2:B5","dataBar":{"color":"blue","min":{"type":"min"},"max":{"type":"max"}}}`,30 `{"ranges":"B2:B5","dataBar":{"color":"green","min":{"type":"num","value":"0"},"max":{"type":"percentile","value":"90"},"barOnly":true}}`,31 `{"ranges":"B2:B5","iconSet":{"icons":"arrows","points":[{"type":"percent","value":"33"},{"type":"percent","value":"67"}]}}`,32 `{"ranges":"B2:B5","iconSet":{"icons":"arrows","points":[{"type":"percent","value":"25"},{"type":"percent","value":"50"},{"type":"percent","value":"75"}],"reverse":true}}`,33 `{"ranges":"B2:B5","iconSet":{"icons":"circles","points":[{"type":"percent","value":"20"},{"type":"percent","value":"40"},{"type":"percent","value":"60"},{"type":"percent","value":"80"}],"iconOnly":true}}`,34 `{"ranges":"B2:B5","iconSet":{"icons":"symbols","points":[{"type":"num","value":"100"},{"type":"num","value":"1000"}]}}`,35 `{"ranges":"B2:B5","iconSet":{"icons":"rating","points":[{"type":"percentile","value":"25"},{"type":"percentile","value":"50"},{"type":"percentile","value":"75"}]}}`,36 }37 mustLoad(t, src, formats, nil)38 formats = formatLines(src)39 name, got := exportImport(t, src)40 if len(got.Notes) > 0 {41 t.Errorf("notes %q", got.Notes)42 }43 if f := formatLines(got.Sheet.Book().Sheet(0)); !reflect.DeepEqual(f, formats) {44 t.Errorf("conditional formats:\n%s\nwant\n%s", strings.Join(f, "\n"), strings.Join(formats, "\n"))45 }46 x, err := excelize.OpenFile(name)47 if err != nil {48 t.Fatal(err)49 }50 defer x.Close()51 if _, err := x.GetConditionalFormats("Sheet1"); err != nil {52 t.Errorf("excelize: %v", err)53 }5455 // Periods Excel has no name for.56 other := months(t)57 mustLoad(t, other, []string{58 `{"ranges":"A2:A5","condition":"date_is","values":["last year"],"fill":"cyan"}`,59 `{"ranges":"A2:A5","condition":"date_before","values":["this month"],"fill":"cyan"}`,60 }, nil)61 _, got = exportImport(t, other)62 want := []string{63 "=AND(INT(A2)>=DATE(YEAR(TODAY())-1,1,1),INT(A2)<=DATE(YEAR(TODAY())-1,12,31))",64 "=INT(A2)<DATE(YEAR(TODAY()),MONTH(TODAY()),1)",65 }66 var args []string67 for _, f := range got.Sheet.CondFormats() {68 args = append(args, f.Op.String()+" "+f.Args[0])69 }70 if !reflect.DeepEqual(args, []string{"formula " + want[0], "formula " + want[1]}) {71 t.Errorf("periods as formulas:\n%s\nwant\n%s", strings.Join(args, "\n"), strings.Join(want, "\n"))72 }73}7475// A dropdown shown as plain text goes out with Excel's arrow hidden; a76// checkbox of its own values goes out as a list of them, which comes77// back as a dropdown, as Excel has no such checkbox.78func TestXLSXDropdownDisplayAndCheckboxValues(t *testing.T) {79 src := months(t)80 mustLoad(t, src, nil, []string{81 `{"ranges":"D2:D5","criteria":"list","items":["a","b"],"display":"plain"}`,82 `{"ranges":"E2:E5","criteria":"list","items":["a","b"],"display":"chip"}`,83 `{"ranges":"F2:F5","criteria":"checkbox","items":["Yes","No"]}`,84 })85 _, got := exportImport(t, src)86 want := []string{87 `{"ranges":"D2:D5","criteria":"list","items":["a","b"],"display":"plain"}`,88 `{"ranges":"E2:E5","criteria":"list","items":["a","b"]}`,89 `{"ranges":"F2:F5","criteria":"list","items":["Yes","No"]}`,90 }91 if v := validationLines(got.Sheet); !reflect.DeepEqual(v, want) {92 t.Errorf("validations:\n%s\nwant\n%s", strings.Join(v, "\n"), strings.Join(want, "\n"))93 }94}