1package oracle23import (4 "fmt"5 "strings"6 "testing"78 "github.com/xuri/excelize/v2"910 "src.rest/cat/012/internal/sheet"11)1213// patterns are number formats: Sheets' Format > Number presets and14// common custom ones.15var patterns = []string{16 "#,##0.00", "#,##0", "0.00%", "0%", "0.00E+00", `"$"#,##0.00`, `"$"#,##0`,17 "#,##0.00;(#,##0.00)", "0.0#", "000", "#,##0,", "0.00 \"kg\"",18 "m/d/yyyy", "yyyy-mm-dd", "mmm d, yyyy", "mmmm d, yyyy", "dddd", "d mmm yyyy",19 "h:mm:ss am/pm", "h:mm AM/PM", "hh:mm:ss", "m/d/yyyy h:mm:ss", "[h]:mm:ss", "h:mm",20}2122var values = []float64{0, 1, -1, 0.5, 1234.5678, -1234.5678, 0.125, 1e6, 46291, 46291.6041666667, 1.0423611111}2324// skippedFormats are pattern and value pairs where 012 deliberately25// differs, or excelize departs from Excel.26var skippedFormats = map[string]string{27 "#,##0, -1": "012 drops the sign of a number that rounds to zero; excelize shows -0",28 "0.0# 46291.6041666667": "excelize keeps a trailing zero under #",29 "[h]:mm:ss 1e+06": "excelize overflows",30}3132// skipReason explains whole classes of skipped comparisons.33func skipReason(pat string, v float64) string {34 switch {35 case isDateOrTime(pat) && v < 0:36 return "dates before 1899-12-30 are negative serials in Sheets; Excel has none"37 case isDate(pat) && v < 61:38 return "Excel counts a fictitious 1900-02-29, so its dates before March 1900 are a day off; Sheets and 012 don't"39 }40 return skippedFormats[fmt.Sprintf("%s %v", pat, v)]41}4243func TestFormatsAgainstExcelize(t *testing.T) {44 x := excelize.NewFile()45 matched, skipped := 0, 046 for _, pat := range patterns {47 p := pat48 style, err := x.NewStyle(&excelize.Style{CustomNumFmt: &p})49 if err != nil {50 t.Fatal(err)51 }52 for _, v := range values {53 if why := skipReason(pat, v); why != "" {54 t.Logf("skip %q of %v: %s", pat, v, why)55 skipped++56 continue57 }58 x.SetCellValue("Sheet1", "A1", v)59 x.SetCellStyle("Sheet1", "A1", "A1", style)60 theirs, err := x.GetCellValue("Sheet1", "A1")61 if err != nil {62 t.Fatal(err)63 }64 ours := sheet.FormatPattern(v, pat)65 // 012 shows AM and PM in capitals, as Sheets' Time format does;66 // excelize copies the case of the pattern's am/pm.67 if ours != theirs && !(strings.Contains(pat, "am/pm") && strings.EqualFold(ours, theirs)) {68 t.Errorf("%q of %v: 012 %q, excelize %q", pat, v, ours, theirs)69 continue70 }71 matched++72 }73 }74 t.Logf("%d formatted values match excelize, %d skipped", matched, skipped)75}7677func isDateOrTime(pat string) bool { return strings.ContainsAny(pat, "ydhsm") }7879func isDate(pat string) bool { return strings.ContainsAny(pat, "yd") }