1package fileio23import (4 "fmt"5 "strings"6 "sync"78 "src.rest/cat/012/internal/locale"9 "src.rest/cat/012/internal/sheet"10)1112// Number formats in files are Excel format codes. 012's formats map to13// the codes Sheets writes for them, and codes map back to the format14// that writes them, so formats survive a round trip; any other code is15// kept as a custom pattern, which 012 renders as Sheets would.1617// excelCode is the Excel format code for f, or "" for General.18func excelCode(f sheet.Format) string {19 dec := ""20 if f.Decimals > 0 {21 dec = "." + strings.Repeat("0", min(f.Decimals, sheet.MaxDecimals))22 }23 switch f.Kind {24 case sheet.FmtText:25 return "@"26 case sheet.FmtNumber:27 return "#,##0" + dec28 case sheet.FmtPercent:29 return "0" + dec + "%"30 case sheet.FmtScientific:31 return "0" + dec + "E+00"32 case sheet.FmtAccounting:33 return `_("$"* #,##0` + dec + `_);_("$"* \(#,##0` + dec + `\);_("$"* "-"??_);_(@_)`34 case sheet.FmtFinancial:35 return "#,##0" + dec + ";(#,##0" + dec + ")"36 case sheet.FmtCurrency:37 return `"$"#,##0` + dec38 case sheet.FmtDate:39 return orDefault(f.Pattern, "m/d/yyyy")40 case sheet.FmtTime:41 return orDefault(f.Pattern, "h:mm:ss AM/PM")42 case sheet.FmtDateTime:43 return orDefault(f.Pattern, "m/d/yyyy h:mm:ss")44 case sheet.FmtDuration:45 return orDefault(f.Pattern, "[h]:mm:ss")46 case sheet.FmtCustom:47 return f.Pattern48 case sheet.FmtSize:49 return f.Code()50 }51 return ""52}5354// excelCodeIn is the Excel code for f in a workbook in loc: the55// Currency, Date, Time and Date time formats in loc's form, tagged with56// its LCID as Excel writes them ([$€-407], [$-407]d.m.yyyy), so Excel57// shows them as the sheet does and they read back as the same formats.58// Other formats, and every format in en-US, are excelCode's.59func excelCodeIn(f sheet.Format, loc *locale.Locale) string {60 if loc.IsCanonical() || f.Pattern != "" {61 return excelCode(f)62 }63 tag := fmt.Sprintf("-%X]", loc.LCID)64 switch f.Kind {65 case sheet.FmtCurrency:66 num := "#,##0"67 if f.Decimals > 0 {68 num += "." + strings.Repeat("0", min(f.Decimals, sheet.MaxDecimals))69 }70 sym, gap := "[$"+loc.Currency+tag, ""71 if loc.Space {72 gap = `\ `73 }74 if loc.After {75 return num + gap + sym76 }77 return sym + gap + num78 case sheet.FmtDate:79 return "[$" + tag + loc.Date80 case sheet.FmtTime:81 return "[$" + tag + excelAMPM(loc.Time)82 case sheet.FmtDateTime:83 return "[$" + tag + excelAMPM(loc.DateTime())84 }85 return excelCode(f)86}8788// excelAMPM spells a pattern's am/pm as Excel does.89func excelAMPM(pat string) string { return strings.Replace(pat, "am/pm", "AM/PM", 1) }9091// localFormat is a format as excelCodeIn writes it in a locale.92type localFormat struct {93 f sheet.Format94 loc *locale.Locale95}9697var (98 localOnce sync.Once99 localCodes map[string]localFormat // normalized code -> format and locale100)101102// formatIn maps an Excel number format to a 012 format for a workbook103// in loc. A code excelCodeIn writes for loc is its format again; one104// written for another locale keeps its own symbol and order, as a105// custom or date pattern.106func formatIn(id int, code string, loc *locale.Locale) sheet.Format {107 if code != "" && !loc.IsCanonical() {108 localOnce.Do(func() {109 localCodes = map[string]localFormat{}110 for _, l := range locale.All()[1:] {111 for _, f := range localKinds() {112 localCodes[normCode(excelCodeIn(f, l))] = localFormat{f, l}113 }114 }115 })116 if lf, ok := localCodes[normCode(code)]; ok && lf.loc == loc {117 return lf.f118 }119 }120 return formatOf(id, code)121}122123// localKinds are the formats written in the workbook's locale.124func localKinds() []sheet.Format {125 out := []sheet.Format{{Kind: sheet.FmtDate}, {Kind: sheet.FmtTime}, {Kind: sheet.FmtDateTime}}126 for d := range sheet.MaxDecimals + 1 {127 out = append(out, sheet.Format{Kind: sheet.FmtCurrency, Decimals: d})128 }129 return out130}131132func orDefault(s, def string) string {133 if s == "" {134 return def135 }136 return s137}138139// builtinCodes are Excel's built-in number formats by ID, for files that140// refer to them without spelling out the code.141var builtinCodes = map[int]string{142 1: "0", 2: "0.00", 3: "#,##0", 4: "#,##0.00",143 5: `"$"#,##0_);\("$"#,##0\)`, 6: `"$"#,##0_);[Red]\("$"#,##0\)`,144 7: `"$"#,##0.00_);\("$"#,##0.00\)`, 8: `"$"#,##0.00_);[Red]\("$"#,##0.00\)`,145 9: "0%", 10: "0.00%", 11: "0.00E+00", 12: "# ?/?", 13: "# ??/??",146 14: "m/d/yyyy", 15: "d-mmm-yy", 16: "d-mmm", 17: "mmm-yy",147 18: "h:mm AM/PM", 19: "h:mm:ss AM/PM", 20: "h:mm", 21: "h:mm:ss", 22: "m/d/yyyy h:mm",148 37: "#,##0 ;(#,##0)", 38: "#,##0 ;[Red](#,##0)", 39: "#,##0.00;(#,##0.00)", 40: "#,##0.00;[Red](#,##0.00)",149 41: `_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)`, 42: `_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_)`,150 43: `_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)`, 44: `_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)`,151 45: "mm:ss", 46: "[h]:mm:ss", 47: "mm:ss.0", 48: "##0.0E+0", 49: "@",152}153154// builtinFormats are built-in IDs whose meaning is one of 012's formats155// although the code differs from the one 012 writes.156var builtinFormats = map[int]sheet.Format{157 5: {Kind: sheet.FmtCurrency}, 6: {Kind: sheet.FmtCurrency},158 7: {Kind: sheet.FmtCurrency, Decimals: 2}, 8: {Kind: sheet.FmtCurrency, Decimals: 2},159 37: {Kind: sheet.FmtFinancial}, 38: {Kind: sheet.FmtFinancial},160 39: {Kind: sheet.FmtFinancial, Decimals: 2}, 40: {Kind: sheet.FmtFinancial, Decimals: 2},161 42: {Kind: sheet.FmtAccounting},162}163164var (165 knownOnce sync.Once166 knownCodes map[string]sheet.Format // normalized code -> format167)168169// normCode folds the spellings of a code that mean the same thing: case,170// a locale tag, and \$ for "$".171func normCode(code string) string {172 code = strings.ReplaceAll(code, "[$-409]", "")173 code = strings.ReplaceAll(code, "[$$-409]", `"$"`)174 code = strings.ReplaceAll(code, `\$`, `"$"`)175 return strings.ToLower(strings.TrimSpace(code))176}177178// formatOf maps an Excel number format, by built-in ID or code, to a 012179// format.180func formatOf(id int, code string) sheet.Format {181 if f, ok := builtinFormats[id]; ok && code == "" {182 return f183 }184 if code == "" {185 code = builtinCodes[id]186 }187 switch normCode(code) {188 case "", "general":189 return sheet.Format{}190 }191 knownOnce.Do(func() {192 knownCodes = map[string]sheet.Format{}193 for _, k := range []sheet.FormatKind{sheet.FmtAccounting, sheet.FmtFinancial, sheet.FmtScientific,194 sheet.FmtPercent, sheet.FmtCurrency, sheet.FmtNumber, sheet.FmtSize} {195 for d := sheet.MaxDecimals; d >= 0; d-- {196 f := sheet.Format{Kind: k, Decimals: d}197 knownCodes[normCode(excelCode(f))] = f198 }199 }200 for _, k := range []sheet.FormatKind{sheet.FmtText, sheet.FmtDate, sheet.FmtTime, sheet.FmtDateTime, sheet.FmtDuration} {201 knownCodes[normCode(excelCode(sheet.Format{Kind: k}))] = sheet.Format{Kind: k}202 }203 knownCodes["m/d/yyyy h:mm"] = sheet.Format{Kind: sheet.FmtDateTime, Pattern: "m/d/yyyy h:mm"}204 })205 if f, ok := knownCodes[normCode(code)]; ok {206 return f207 }208 if f, ok := builtinFormats[id]; ok {209 return f210 }211 switch kind := dateKind(code); kind {212 case sheet.FmtDate, sheet.FmtTime, sheet.FmtDateTime, sheet.FmtDuration:213 return sheet.Format{Kind: kind, Pattern: code}214 }215 return sheet.Format{Kind: sheet.FmtCustom, Pattern: code}216}217218// dateKind says whether a code shows a date, a time, both, or a219// duration, looking at its letters outside quotes and brackets; it220// returns FmtCustom for anything else.221func dateKind(code string) sheet.FormatKind {222 date, clock, elapsed := false, false, false223 inQuote, inBracket := false, false224 for i := 0; i < len(code); i++ {225 c := code[i]226 switch {227 case c == '"':228 inQuote = !inQuote229 case inQuote:230 case c == '\\':231 i++232 case c == '[':233 inBracket = true234 if i+1 < len(code) && strings.ContainsRune("hHmMsS", rune(code[i+1])) {235 elapsed = true236 }237 case c == ']':238 inBracket = false239 case inBracket:240 case c == 'd' || c == 'D' || c == 'y' || c == 'Y':241 date = true242 case c == 'h' || c == 'H' || c == 's' || c == 'S':243 clock = true244 case c == '0' || c == '#' || c == '?':245 if !clock && !date {246 return sheet.FmtCustom247 }248 }249 }250 switch {251 case elapsed:252 return sheet.FmtDuration253 case date && clock:254 return sheet.FmtDateTime255 case date:256 return sheet.FmtDate257 case clock:258 return sheet.FmtTime259 }260 return sheet.FmtCustom261}262263// isDateFormat reports whether f shows a calendar date, whose serial264// numbers differ between Excel and 012 before March 1900.265func isDateFormat(f sheet.Format) bool {266 switch f.Kind {267 case sheet.FmtDate, sheet.FmtDateTime:268 return true269 case sheet.FmtCustom:270 k := dateKind(f.Pattern)271 return k == sheet.FmtDate || k == sheet.FmtDateTime272 }273 return false274}275276// Excel and 1-2-3 count 1900 as a leap year, so their serials before277// March 1, 1900 are one less than 012's (and Sheets').278const leapBug = 61279280func fromExcelSerial(v float64, f sheet.Format) float64 {281 if isDateFormat(f) && v >= 1 && v < leapBug-1 {282 return v + 1283 }284 return v285}286287func toExcelSerial(v float64, f sheet.Format) float64 {288 if isDateFormat(f) && v >= 1 && v < leapBug {289 return v - 1290 }291 return v292}