amadan

oficina / sheets Public

Clone 
git clone https://amadan.net/oficina/sheets

Plain git — no account needed to clone.

Download

Download this file

1package format
2
3import (
4 "math"
5 "strconv"
6 "strings"
7 "time"
8)
9
10// excelEpoch mirrors calc/fn_date.go's constant of the same name and
11// value: day zero in spreadsheet date-serial numbering (Dec 30, 1899).
12// format cannot reach calc's unexported var (its only allowed import
13// from calc is Value), so the value is restated here; it must stay in
14// lockstep with calc's, or a date serial stops rendering as the
15// calendar date that produced it. See calc/fn_date.go for why this
16// epoch (one day off "real" Dec 31, 1899) reproduces Excel's numbering
17// with ordinary calendar arithmetic, no fake-leap-day case needed.
18var excelEpoch = time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC)
19
20type dateTokKind int
21
22const (
23 dtLiteral dateTokKind = iota
24 dtYear4
25 dtYear2
26 dtMonthFull
27 dtMonthAbbr
28 dtMonth2 // ambiguous with minutes until resolveMinutes runs
29 dtMonth1 // ambiguous with minutes until resolveMinutes runs
30 dtMinute2
31 dtMinute1
32 dtDayFull
33 dtDayAbbr
34 dtDay2
35 dtDay1
36 dtHour2
37 dtHour1
38 dtSec2
39 dtSec1
40 dtAmPm
41)
42
43type dateTok struct {
44 kind dateTokKind
45 lit string // literal text for dtLiteral; original-case "AM/PM" text for dtAmPm
46}
47
48// tokenizeDate turns a date-mode section into an ordered token list. ok
49// is false for a construct this package does not support: a fractional-
50// second or numeric placeholder ('0', '#', '?') inside a date section,
51// three-or-more 'y's, or hh/ss beyond two digits, or any bare letter
52// that isn't part of a recognized token or "AM/PM"/"am/pm".
53func tokenizeDate(section string) ([]dateTok, bool) {
54 var toks []dateTok
55 i := 0
56 for i < len(section) {
57 c := section[i]
58 switch {
59 case c == '\\' && i+1 < len(section):
60 toks = append(toks, dateTok{kind: dtLiteral, lit: string(section[i+1])})
61 i += 2
62 case c == '"':
63 j := strings.IndexByte(section[i+1:], '"')
64 if j < 0 {
65 toks = append(toks, dateTok{kind: dtLiteral, lit: section[i+1:]})
66 i = len(section)
67 } else {
68 toks = append(toks, dateTok{kind: dtLiteral, lit: section[i+1 : i+1+j]})
69 i = i + 1 + j + 1
70 }
71 case matchAmPm(section[i:]):
72 toks = append(toks, dateTok{kind: dtAmPm, lit: section[i : i+5]})
73 i += 5
74 case isDateLetter(c):
75 run := 1
76 for i+run < len(section) && section[i+run] == c {
77 run++
78 }
79 tok, ok := dateTokenFor(c, run)
80 if !ok {
81 return nil, false
82 }
83 toks = append(toks, tok)
84 i += run
85 case c == '0' || c == '#' || c == '?' || c == '@' || c == '_' || c == '*':
86 return nil, false
87 case isASCIILetter(c):
88 return nil, false
89 default:
90 toks = append(toks, dateTok{kind: dtLiteral, lit: string(c)})
91 i++
92 }
93 }
94 return toks, true
95}
96
97func matchAmPm(s string) bool {
98 return len(s) >= 5 && (s[:5] == "AM/PM" || s[:5] == "am/pm")
99}
100
101func isDateLetter(c byte) bool { return strings.IndexByte("yYmMdDhHsS", c) >= 0 }
102
103func dateTokenFor(c byte, run int) (dateTok, bool) {
104 switch c {
105 case 'y', 'Y':
106 switch {
107 case run >= 4:
108 return dateTok{kind: dtYear4}, true
109 case run <= 2:
110 return dateTok{kind: dtYear2}, true
111 }
112 case 'm', 'M':
113 switch {
114 case run == 1:
115 return dateTok{kind: dtMonth1}, true
116 case run == 2:
117 return dateTok{kind: dtMonth2}, true
118 case run == 3:
119 return dateTok{kind: dtMonthAbbr}, true
120 default:
121 return dateTok{kind: dtMonthFull}, true
122 }
123 case 'd', 'D':
124 switch {
125 case run == 1:
126 return dateTok{kind: dtDay1}, true
127 case run == 2:
128 return dateTok{kind: dtDay2}, true
129 case run == 3:
130 return dateTok{kind: dtDayAbbr}, true
131 default:
132 return dateTok{kind: dtDayFull}, true
133 }
134 case 'h', 'H':
135 switch run {
136 case 1:
137 return dateTok{kind: dtHour1}, true
138 case 2:
139 return dateTok{kind: dtHour2}, true
140 }
141 case 's', 'S':
142 switch run {
143 case 1:
144 return dateTok{kind: dtSec1}, true
145 case 2:
146 return dateTok{kind: dtSec2}, true
147 }
148 }
149 return dateTok{}, false
150}
151
152// resolveMinutes rewrites an ambiguous dtMonth1/dtMonth2 to a minute
153// token when it sits next to an hour token (before it) or a seconds
154// token (after it), skipping over intervening literal tokens (e.g. the
155// ':' in "hh:mm") — Excel's own rule, applied slightly more broadly:
156// any intervening literal counts, not only ':'.
157func resolveMinutes(toks []dateTok) []dateTok {
158 isHour := func(k dateTokKind) bool { return k == dtHour1 || k == dtHour2 }
159 isSec := func(k dateTokKind) bool { return k == dtSec1 || k == dtSec2 }
160 for idx, t := range toks {
161 if t.kind != dtMonth1 && t.kind != dtMonth2 {
162 continue
163 }
164 afterHour := false
165 for p := idx - 1; p >= 0; p-- {
166 if toks[p].kind == dtLiteral {
167 continue
168 }
169 afterHour = isHour(toks[p].kind)
170 break
171 }
172 beforeSec := false
173 if !afterHour {
174 for n := idx + 1; n < len(toks); n++ {
175 if toks[n].kind == dtLiteral {
176 continue
177 }
178 beforeSec = isSec(toks[n].kind)
179 break
180 }
181 }
182 if afterHour || beforeSec {
183 if t.kind == dtMonth1 {
184 toks[idx].kind = dtMinute1
185 } else {
186 toks[idx].kind = dtMinute2
187 }
188 }
189 }
190 return toks
191}
192
193// serialToTime reverses calc's dateSerial: whole days since excelEpoch
194// plus a fractional day become a calendar instant. Rounding to the
195// nearest whole second (ties away from zero, via math.Round) absorbs
196// float64 jitter in the fractional-day component.
197func serialToTime(serial float64) time.Time {
198 totalSeconds := int64(math.Round(serial * 86400))
199 return excelEpoch.Add(time.Duration(totalSeconds) * time.Second)
200}
201
202// renderDate renders val (a date serial) through a date-mode section.
203func renderDate(section string, val float64) (string, bool) {
204 toks, ok := tokenizeDate(section)
205 if !ok {
206 return "", false
207 }
208 toks = resolveMinutes(toks)
209
210 hasAmPm := false
211 for _, t := range toks {
212 if t.kind == dtAmPm {
213 hasAmPm = true
214 break
215 }
216 }
217
218 t := serialToTime(val)
219 y, mo, d := t.Year(), int(t.Month()), t.Day()
220 h, mi, s := t.Hour(), t.Minute(), t.Second()
221 hour12 := h % 12
222 if hour12 == 0 {
223 hour12 = 12
224 }
225 pm := h >= 12
226
227 var sb strings.Builder
228 for _, tk := range toks {
229 switch tk.kind {
230 case dtLiteral:
231 sb.WriteString(tk.lit)
232 case dtYear4:
233 sb.WriteString(pad(y, 4))
234 case dtYear2:
235 sb.WriteString(pad(y%100, 2))
236 case dtMonthFull:
237 sb.WriteString(time.Month(mo).String())
238 case dtMonthAbbr:
239 sb.WriteString(time.Month(mo).String()[:3])
240 case dtMonth2:
241 sb.WriteString(pad(mo, 2))
242 case dtMonth1:
243 sb.WriteString(strconv.Itoa(mo))
244 case dtDayFull:
245 sb.WriteString(t.Weekday().String())
246 case dtDayAbbr:
247 sb.WriteString(t.Weekday().String()[:3])
248 case dtDay2:
249 sb.WriteString(pad(d, 2))
250 case dtDay1:
251 sb.WriteString(strconv.Itoa(d))
252 case dtHour2:
253 if hasAmPm {
254 sb.WriteString(pad(hour12, 2))
255 } else {
256 sb.WriteString(pad(h, 2))
257 }
258 case dtHour1:
259 if hasAmPm {
260 sb.WriteString(strconv.Itoa(hour12))
261 } else {
262 sb.WriteString(strconv.Itoa(h))
263 }
264 case dtMinute2:
265 sb.WriteString(pad(mi, 2))
266 case dtMinute1:
267 sb.WriteString(strconv.Itoa(mi))
268 case dtSec2:
269 sb.WriteString(pad(s, 2))
270 case dtSec1:
271 sb.WriteString(strconv.Itoa(s))
272 case dtAmPm:
273 sb.WriteString(ampmText(tk.lit, pm))
274 }
275 }
276 return sb.String(), true
277}
278
279func pad(n, width int) string {
280 s := strconv.Itoa(n)
281 for len(s) < width {
282 s = "0" + s
283 }
284 return s
285}
286
287func ampmText(original string, pm bool) string {
288 upper := original[0] >= 'A' && original[0] <= 'Z'
289 switch {
290 case pm && upper:
291 return "PM"
292 case pm:
293 return "pm"
294 case upper:
295 return "AM"
296 default:
297 return "am"
298 }
299}
300