1package fileio23import (4 "bufio"5 "fmt"6 "math"7 "slices"8 "strconv"9 "strings"10 "unicode/utf8"1112 "src.rest/cat/012/internal/formula"13 "src.rest/cat/012/internal/locale"14 "src.rest/cat/012/internal/sheet"15)1617// excelMaxText is the most characters an Excel cell holds; longer text18// is cut, or Excel reports the file as damaged.19const excelMaxText = 327672021// xlsxWriter writes worksheets, sharing cell formats among them.22type xlsxWriter struct {23 styles *xlsxStyleTable24 multi bool // examples name their sheet25 buf []byte // a row's cells2627 // Formulas are written as their values when Excel has no equivalent,28 // or when they name a sheet the file doesn't have, which Excel would29 // refuse.30 values valueCount31 missing valueCount32 missingSheet string // the sheet missing's example names33 known map[string]bool // keys of the sheets written34 renamed map[string]string // the names of sheets written under another, by key35 dynamic bool // a dynamic array formula was written: see xlsxarray.go36 spills map[sheet.Addr]sheet.Rect // the sheet being written's Snapshot.Spills37 rules ruleNotes // rules left out, see xlsxrules.go38 // Tables: see xlsxtables.go. tables are the keys of those written,39 // headers the header cells of the sheet being written, tableParts40 // how many parts are written and tableTypes their content types.41 tables map[string]bool42 headers map[sheet.Addr]bool43 tableParts int44 tableTypes []string45}4647// valueCount counts formulas written as values, keeping the first one's48// address as an example.49type valueCount struct {50 n int51 example string52}5354func (v *valueCount) add(multi bool, ws string, a sheet.Addr) {55 v.n++56 if v.example == "" {57 v.example = a.String()58 if multi {59 v.example = sheet.QuoteSheet(ws) + "!" + a.String()60 }61 }62}6364// unknownSheet returns a sheet c's formula names that isn't written.65func (w *xlsxWriter) unknownSheet(c SnapCell) (string, bool) {66 for _, name := range c.Sheets {67 if !w.known[formula.SheetKey(name)] {68 return name, true69 }70 }71 return "", false72}7374// sheet writes a snapshot as a worksheet and returns the rows written.75// Errors stick in bw.76func (w *xlsxWriter) sheet(bw *bufio.Writer, ws string, snap *Snapshot, active bool) int {77 r := snap.Range78 w.spills, w.headers = snap.Spills, headerCells(snap)79 bw.WriteString(xmlHead + `<worksheet xmlns="` + sheetMain + `" xmlns:r="` + officeRel + `">`)80 if len(snap.HiddenRows) > 0 {81 bw.WriteString(`<sheetPr filterMode="1"/>`) // a filter is hiding rows82 }83 fmt.Fprintf(bw, `<dimension ref="%s%d:%s%d"/>`, excelColName(r.From.Col+1), r.From.Row+1, excelColName(r.To.Col+1), r.To.Row+1)84 writeSheetView(bw, snap, active)85 bw.WriteString(`<sheetFormatPr defaultRowHeight="15"/>`)86 w.writeCols(bw, snap)87 bw.WriteString(`<sheetData>`)88 styled := lineRows(snap) // rows with a style or height of their own, written even without cells89 for row := r.From.Row; row <= r.To.Row; row++ {90 styled = w.styledRows(bw, snap, styled, row)91 rowStyled := len(styled) > 0 && styled[0] == row92 if rowStyled {93 styled = styled[1:]94 }95 b := w.buf[:0]96 for col := r.From.Col; col <= r.To.Col; col++ {97 a := sheet.Addr{Col: col, Row: row}98 if c, ok := snap.Cells[a]; ok {99 b = w.cell(b, ws, a, c)100 }101 }102 if len(b) > 0 || rowStyled || snap.HiddenRows[row] {103 w.rowStart(bw, snap, row)104 bw.Write(b)105 bw.WriteString(`</row>`)106 }107 w.buf = b108 }109 w.styledRows(bw, snap, styled, sheet.MaxRows)110 bw.WriteString(`</sheetData>`)111 if tableFilter(snap) < 0 {112 writeAutoFilter(bw, snap)113 }114 writeMerges(bw, snap)115 w.writeRules(bw, ws, snap)116 if len(snap.Notes) > 0 {117 bw.WriteString(`<legacyDrawing r:id="` + vmlRelID + `"/>`)118 }119 writeTableParts(bw, snap)120 bw.WriteString(`</worksheet>`)121 return r.To.Row - r.From.Row + 1122}123124// cell appends snapshot cell c at a as a <c> element. A formula's value125// is written as its cached result; a formula with no Excel equivalent,126// or naming a sheet that isn't written, is written as its value alone,127// and counted.128func (w *xlsxWriter) cell(b []byte, ws string, a sheet.Addr, c SnapCell) []byte {129 fx, arrays := w.formula(ws, a, c)130 b = append(b, `<c r="`...)131 b = append(b, excelColName(a.Col+1)...)132 b = strconv.AppendInt(b, int64(a.Row+1), 10)133 b = append(b, '"')134 // A formula's inferred format is written too, so its result shows135 // the same in Excel.136 if !c.Format.IsZero() || !c.Style.IsZero() {137 b = append(b, ` s="`...)138 b = strconv.AppendInt(b, int64(w.styles.id(c.Format, c.Style)), 10)139 b = append(b, '"')140 }141 typ, v, inline := cellValue(c, fx != "")142 if w.headers[a] && fx == "" && v != "" {143 typ, v, inline = "inlineStr", c.Text(), true // a table's header is text144 }145 if typ != "" {146 b = append(b, ` t="`...)147 b = append(b, typ...)148 b = append(b, '"')149 }150 if fx != "" && arrays {151 b = append(b, ` cm="1"`...)152 w.dynamic = true153 }154 if fx == "" && v == "" {155 return append(b, "/>"...)156 }157 b = append(b, '>')158 switch {159 case fx != "" && arrays:160 b = append(b, `<f t="array" ref="`...)161 b = append(b, arrayRef(a, w.spills[a])...)162 b = append(b, `">`...)163 b = appendEscaped(b, fx, false)164 b = append(b, "</f>"...)165 case fx != "":166 b = append(b, "<f>"...)167 b = appendEscaped(b, fx, false)168 b = append(b, "</f>"...)169 }170 switch {171 case inline && fx == "":172 b = append(b, `<is><t xml:space="preserve">`...)173 b = appendEscaped(b, v, true)174 b = append(b, "</t></is>"...)175 case v != "":176 b = append(b, "<v>"...)177 b = appendEscaped(b, v, typ == "str")178 b = append(b, "</v>"...)179 }180 return append(b, "</c>"...)181}182183// formula is c's formula in Excel's syntax, or "" when it has none or184// is written as its value, which it counts, and whether it is a dynamic185// array formula: one that spills or calls array functions.186func (w *xlsxWriter) formula(ws string, a sheet.Addr, c SnapCell) (string, bool) {187 if !c.Formula {188 return "", false189 }190 if name, ok := w.unknownSheet(c); ok {191 if w.missing.n == 0 {192 w.missingSheet = name193 }194 w.missing.add(w.multi, ws, a)195 return "", false196 }197 fx, arrays, ok := excelFormula(c.Input, w.renamed)198 if w.unknownTable(c) {199 fx, ok = "", false200 }201 if !ok {202 w.values.add(w.multi, ws, a)203 }204 _, spills := w.spills[a]205 return fx, arrays || spills206}207208// cellValue is a cell's value as Excel stores it: the type attribute,209// the text, and whether text goes inline (<is>) rather than in <v>,210// which only a formula's cached text does (t="str"). Errors are written211// as text, as Excel recalculates on opening.212func cellValue(c SnapCell, hasFormula bool) (typ, v string, inline bool) {213 switch c.Value.Kind {214 case sheet.Number:215 n := toExcelSerial(c.Value.Num, c.Format)216 if math.IsNaN(n) || math.IsInf(n, 0) {217 return "e", "#NUM!", false218 }219 return "", numInput(n), false220 case sheet.Bool:221 if c.Value.Num != 0 {222 return "b", "1", false223 }224 return "b", "0", false225 case sheet.Text, sheet.Error:226 s := c.Value.Str227 if utf8.RuneCountInString(s) > excelMaxText {228 s = string([]rune(s)[:excelMaxText])229 }230 if hasFormula {231 return "str", s, false232 }233 return "inlineStr", s, true234 }235 return "", "", false236}237238// escapeXML escapes s for XML text or, with attr, an attribute value.239func escapeXML(s string, attr bool) string {240 return string(appendXML(nil, s, attr))241}242243func appendXML(b []byte, s string, attr bool) []byte {244 for _, r := range s {245 switch {246 case r == '&':247 b = append(b, "&"...)248 case r == '<':249 b = append(b, "<"...)250 case r == '>':251 b = append(b, ">"...)252 case r == '"' && attr:253 b = append(b, """...)254 case r == '\r' || attr && (r == '\n' || r == '\t'):255 b = append(b, "&#x"...)256 b = strconv.AppendInt(b, int64(r), 16)257 b = append(b, ';')258 case !xmlChar(r):259 b = utf8.AppendRune(b, utf8.RuneError)260 default:261 b = utf8.AppendRune(b, r)262 }263 }264 return b265}266267// appendEscaped appends s as XML text; with ooxml, as SpreadsheetML cell268// text, where characters XML can't hold are written _xHHHH_ and a _x269// that would read as such an escape is itself escaped (_x005F_).270func appendEscaped(b []byte, s string, ooxml bool) []byte {271 if !ooxml {272 return appendXML(b, s, false)273 }274 for i := 0; i < len(s); {275 r, size := utf8.DecodeRuneInString(s[i:])276 switch {277 case r == '_' && isOOXMLEscape(s[i:]):278 b = append(b, "_x005F"...)279 case !xmlChar(r) && r != utf8.RuneError && r <= 0xFFFF:280 b = fmt.Appendf(b, "_x%04X_", r)281 i += size282 continue283 }284 b = appendXML(b, s[i:i+size], false)285 i += size286 }287 return b288}289290func isOOXMLEscape(s string) bool {291 _, ok := ooxmlEscape([]byte(s[:min(len(s), 7)]))292 return ok293}294295// xmlChar reports whether XML 1.0 allows r in a document.296func xmlChar(r rune) bool {297 return r == '\t' || r == '\n' || r == '\r' ||298 r >= 0x20 && r <= 0xD7FF || r >= 0xE000 && r <= 0xFFFD || r >= 0x10000 && r <= 0x10FFFF299}300301// xlsxStyleTable collects the cell formats a workbook uses: each302// distinct number format and text style is a cellXfs entry, with its303// number format code and font.304type xlsxStyleTable struct {305 ids map[xlsxStyle]int306 xfs []xlsxStyle // index 0 is the default307 codes []string // custom number formats, from id 164308 fonts []sheet.Style309 borders []sheet.Borders // see xlsxlayout.go; index 0 is none310 dxfs []sheet.RuleStyle // conditional formats' styles, see xlsxrules.go311 loc *locale.Locale // the workbook's, whose Currency and Date codes it writes312}313314// dxf is the index of the differential style for a rule's style.315func (t *xlsxStyleTable) dxf(st sheet.RuleStyle) int {316 if i := slices.Index(t.dxfs, st); i >= 0 {317 return i318 }319 t.dxfs = append(t.dxfs, st)320 return len(t.dxfs) - 1321}322323// dxfsXML writes the differential styles: a font with the text styles324// and color, and a solid fill. Excel reads a dxf's fill from the325// pattern's background color and some readers from its foreground, so326// both are the fill.327func (t *xlsxStyleTable) dxfsXML() string {328 if len(t.dxfs) == 0 {329 return ""330 }331 var b strings.Builder332 fmt.Fprintf(&b, `<dxfs count="%d">`, len(t.dxfs))333 for _, st := range t.dxfs {334 b.WriteString(`<dxf><font>`)335 for _, on := range []struct {336 set bool337 tag string338 }{{st.Bold, `<b/>`}, {st.Italic, `<i/>`}, {st.Strikethrough, `<strike/>`}, {st.Underline, `<u/>`}} {339 if on.set {340 b.WriteString(on.tag)341 }342 }343 if st.Text != sheet.ColorNone {344 fmt.Fprintf(&b, `<color rgb="%s"/>`, ruleRGB[st.Text][0])345 }346 b.WriteString(`</font>`)347 if st.Fill != sheet.ColorNone {348 c := ruleRGB[st.Fill][1]349 fmt.Fprintf(&b, `<fill><patternFill patternType="solid"><fgColor rgb="%s"/><bgColor rgb="%s"/></patternFill></fill>`, c, c)350 }351 b.WriteString(`</dxf>`)352 }353 return b.String() + `</dxfs>`354}355356func newXLSXStyleTable(loc *locale.Locale) *xlsxStyleTable {357 return &xlsxStyleTable{loc: loc, ids: map[xlsxStyle]int{{}: 0}, xfs: []xlsxStyle{{}}, fonts: []sheet.Style{{}}, borders: []sheet.Borders{0}}358}359360// id is the index of the cell format for f and st.361func (t *xlsxStyleTable) id(f sheet.Format, st sheet.Style) int {362 key := xlsxStyle{f, st}363 if id, ok := t.ids[key]; ok {364 return id365 }366 id := len(t.xfs)367 t.ids[key] = id368 t.xfs = append(t.xfs, key)369 return id370}371372// xml is the styles part.373func (t *xlsxStyleTable) xml() string {374 var xfs strings.Builder375 for _, x := range t.xfs {376 numFmt := 0377 if code := excelCodeIn(x.format, t.loc); code != "" {378 i := slices.Index(t.codes, code)379 if i < 0 {380 i = len(t.codes)381 t.codes = append(t.codes, code)382 }383 numFmt = 164 + i384 }385 font := fontOf(x.style)386 i := slices.Index(t.fonts, font)387 if i < 0 {388 i = len(t.fonts)389 t.fonts = append(t.fonts, font)390 }391 border := t.borderID(x.style.Borders)392 fmt.Fprintf(&xfs, `<xf numFmtId="%d" fontId="%d" fillId="0" borderId="%d" xfId="0"`, numFmt, i, border)393 if numFmt != 0 {394 xfs.WriteString(` applyNumberFormat="1"`)395 }396 if i != 0 {397 xfs.WriteString(` applyFont="1"`)398 }399 if border != 0 {400 xfs.WriteString(` applyBorder="1"`)401 }402 align := alignmentXML(x.style)403 if align == "" {404 xfs.WriteString(`/>`)405 continue406 }407 xfs.WriteString(` applyAlignment="1">` + align + `</xf>`)408 }409 var b strings.Builder410 b.WriteString(xmlHead + `<styleSheet xmlns="` + sheetMain + `">`)411 if len(t.codes) > 0 {412 fmt.Fprintf(&b, `<numFmts count="%d">`, len(t.codes))413 for i, code := range t.codes {414 fmt.Fprintf(&b, `<numFmt numFmtId="%d" formatCode="%s"/>`, 164+i, escapeXML(code, true))415 }416 b.WriteString(`</numFmts>`)417 }418 fmt.Fprintf(&b, `<fonts count="%d">`, len(t.fonts))419 for _, f := range t.fonts {420 b.WriteString(`<font>`)421 for _, on := range []struct {422 set bool423 tag string424 }{{f.Bold, `<b/>`}, {f.Italic, `<i/>`}, {f.Strikethrough, `<strike/>`}, {f.Underline, `<u/>`}} {425 if on.set {426 b.WriteString(on.tag)427 }428 }429 b.WriteString(`<sz val="11"/><name val="Calibri"/><family val="2"/></font>`)430 }431 b.WriteString(`</fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills>` +432 t.bordersXML() + `<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>`)433 fmt.Fprintf(&b, `<cellXfs count="%d">%s</cellXfs>`, len(t.xfs), xfs.String())434 b.WriteString(`<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>` + t.dxfsXML() + `</styleSheet>`)435 return b.String()436}437438// fontOf is the part of a style a font holds.439func fontOf(st sheet.Style) sheet.Style {440 return sheet.Style{Bold: st.Bold, Italic: st.Italic, Underline: st.Underline, Strikethrough: st.Strikethrough}441}