1package fileio23import (4 "archive/zip"5 "bufio"6 "context"7 "fmt"8 "io"9 "strconv"10 "strings"1112 "src.rest/cat/012/internal/formula"13 "src.rest/cat/012/internal/sheet"14)1516// XLSX files are read by xlsximport.go (on xlsxpkg.go, xlsxbook.go,17// xlsxstyles.go and xlsxsheet.go) and written here and in xlsxwrite.go,18// all with archive/zip and encoding/xml's escaping.1920// Column widths: Excel measures in characters of its default font, 01221// in terminal columns including one of padding.22const (23 excelDefaultWidth = 8.4324 excelPadding = 125)2627func clamp(v, lo, hi int) int { return max(lo, min(v, hi)) }2829// xlsxStyle is what 012 keeps of an Excel cell style.30type xlsxStyle struct {31 format sheet.Format32 style sheet.Style33}3435// exportXLSX writes a workbook: the snapshot's sheets (or just the36// snapshot), each with values, formulas in Excel's syntax with their37// results cached, number formats, text styles, alignment and column38// widths, and the named ranges. Excel recalculates it on opening.39func exportXLSX(_ context.Context, name string, snap *Snapshot, _ ExportOptions) (*ExportResult, error) {40 sheets := snap.Sheets41 if len(sheets) == 0 {42 sheets = []*Snapshot{snap}43 }44 w := &xlsxWriter{styles: newXLSXStyleTable(snap.Locale), multi: len(sheets) > 1, known: map[string]bool{}, renamed: map[string]string{}, tables: tableKeys(sheets)}45 res := &ExportResult{}46 names, hidden, active := make([]string, len(sheets)), make([]bool, len(sheets)), 047 used := map[string]bool{}48 for i, sn := range sheets {49 names[i] = uniqueSheetName(sheetName(sn.Name), used)50 if sn == snap {51 active = i52 }53 hidden[i] = sn.Hidden54 // A sheet renamed to suit Excel is named so in formulas too.55 key := formula.SheetKey(sn.Name)56 w.known[key] = true57 if names[i] != sn.Name {58 w.renamed[key] = names[i]59 }60 }61 hidden[active] = false62 err := writeFile(name, func(out io.Writer) error {63 zw := zip.NewWriter(out)64 var withNotes []int65 for i, sn := range sheets {66 rows, err := w.writeSheet(zw, i, names[i], sn, i == active)67 if err != nil {68 return err69 }70 res.Rows += rows71 if len(sn.Notes) > 0 {72 withNotes = append(withNotes, i+1)73 }74 if err := w.writeSheetParts(zw, i+1, sn); err != nil {75 return err76 }77 }78 if err := w.writeShared(zw); err != nil {79 return err80 }81 types := notesTypes(withNotes) + strings.Join(w.tableTypes, "")82 if err := writePackage(zw, names, hidden, active, w.definedNames(snap.Names), filterRanges(sheets, names), types, w.dynamic); err != nil {83 return err84 }85 return zw.Close()86 })87 if err != nil {88 return nil, err89 }90 if w.values.n > 0 {91 res.Notes = append(res.Notes, fmt.Sprintf("%s with no Excel equivalent saved as values, e.g. %s",92 count(w.values.n, "formula", "formulas"), w.values.example))93 }94 if w.missing.n > 0 {95 res.Notes = append(res.Notes, fmt.Sprintf("%s naming a sheet that doesn't exist saved as values, e.g. %s (%s)",96 count(w.missing.n, "formula", "formulas"), w.missing.example, sheet.QuoteSheet(w.missingSheet)))97 }98 res.Notes = append(res.Notes, w.rulesNotes()...)99 return res, nil100}101102// writeSheetParts writes the parts worksheet part n relates to, its103// notes and tables, and the relationships to them.104func (w *xlsxWriter) writeSheetParts(zw *zip.Writer, n int, sn *Snapshot) error {105 var rels []string106 if len(sn.Notes) > 0 {107 if err := writeNotes(zw, n, sn.Notes); err != nil {108 return err109 }110 rels = append(rels, notesRels(n)...)111 }112 tables, err := w.writeTables(zw, sn)113 if err != nil {114 return err115 }116 if rels = append(rels, tables...); len(rels) == 0 {117 return nil118 }119 return writePart(zw, "xl/worksheets/_rels/sheet"+strconv.Itoa(n)+".xml.rels",120 xmlHead+`<Relationships xmlns="`+relsNS+`">`+strings.Join(rels, "")+`</Relationships>`)121}122123// writeShared writes the parts the worksheets share: the styles, and the124// cell metadata of dynamic array formulas when any was written.125func (w *xlsxWriter) writeShared(zw *zip.Writer) error {126 if err := writePart(zw, "xl/styles.xml", w.styles.xml()); err != nil {127 return err128 }129 if !w.dynamic {130 return nil131 }132 return writePart(zw, "xl/metadata.xml", metadataXML)133}134135// definedNames are the named ranges as Excel's defined names hold them,136// with the names their sheets are written under: Q3!$B$2:$B$9.137func (w *xlsxWriter) definedNames(names []SnapName) [][2]string {138 out := make([][2]string, len(names))139 for i, n := range names {140 ws := n.Sheet141 if to, ok := w.renamed[formula.SheetKey(ws)]; ok {142 ws = to143 }144 out[i] = [2]string{n.Name, excelRange(ws, n.Range)}145 }146 return out147}148149// uniqueSheetName is name, or name with a number when another sheet150// already took it (Excel's names ignore case).151func uniqueSheetName(name string, used map[string]bool) string {152 out := name153 for n := 2; used[strings.ToLower(out)]; n++ {154 suffix := " (" + strconv.Itoa(n) + ")"155 r := []rune(name)156 out = string(r[:min(len(r), 31-len(suffix))]) + suffix157 }158 used[strings.ToLower(out)] = true159 return out160}161162const (163 xmlHead = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>` + "\n"164 relsNS = `http://schemas.openxmlformats.org/package/2006/relationships`165 officeRel = `http://schemas.openxmlformats.org/officeDocument/2006/relationships`166 sheetMain = `http://schemas.openxmlformats.org/spreadsheetml/2006/main`167 mlType = `application/vnd.openxmlformats-officedocument.spreadsheetml.`168)169170// filterRanges is each sheet's filter range as Excel names it, with its171// sheet (_xlnm._FilterDatabase), or "" for a sheet without a filter.172func filterRanges(sheets []*Snapshot, names []string) []string {173 out := make([]string, len(sheets))174 for i, sn := range sheets {175 if sn.Filter != nil && tableFilter(sn) < 0 { // a table's filter is its own176 out[i] = excelRange(names[i], sn.Filter.Range)177 }178 }179 return out180}181182// writePackage writes the parts around the worksheets and styles: the183// workbook with its sheets, active tab, names (with each filter's range,184// filters[i] for sheet i) and calculation settings, the relationships185// and the content types, with extra ones for the sheets' parts, and the186// cell metadata part when there are dynamic array formulas (metadata).187func writePackage(zw *zip.Writer, names []string, hidden []bool, active int, defined [][2]string, filters []string, extraTypes string, metadata bool) error {188 var types, rels, book strings.Builder189 types.WriteString(xmlHead + `<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">` +190 `<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>` +191 `<Default Extension="xml" ContentType="application/xml"/>` +192 `<Override PartName="/xl/workbook.xml" ContentType="` + mlType + `sheet.main+xml"/>` +193 `<Override PartName="/xl/styles.xml" ContentType="` + mlType + `styles+xml"/>` + extraTypes)194 rels.WriteString(xmlHead + `<Relationships xmlns="` + relsNS + `">`)195 book.WriteString(xmlHead + `<workbook xmlns="` + sheetMain + `" xmlns:r="` + officeRel + `">` +196 `<bookViews><workbookView activeTab="` + strconv.Itoa(active) + `"/></bookViews><sheets>`)197 for i, ws := range names {198 n := strconv.Itoa(i + 1)199 fmt.Fprintf(&types, `<Override PartName="/xl/worksheets/sheet%s.xml" ContentType="%sworksheet+xml"/>`, n, mlType)200 fmt.Fprintf(&rels, `<Relationship Id="rId%s" Type="%s/worksheet" Target="worksheets/sheet%s.xml"/>`, n, officeRel, n)201 state := ""202 if hidden[i] {203 state = ` state="hidden"`204 }205 fmt.Fprintf(&book, `<sheet name="%s" sheetId="%s"%s r:id="rId%s"/>`, escapeXML(ws, true), n, state, n)206 }207 fmt.Fprintf(&rels, `<Relationship Id="rId%d" Type="%s/styles" Target="styles.xml"/>`, len(names)+1, officeRel)208 if metadata {209 types.WriteString(`<Override PartName="/xl/metadata.xml" ContentType="` + mlType + `sheetMetadata+xml"/>`)210 fmt.Fprintf(&rels, `<Relationship Id="rId%d" Type="%s/sheetMetadata" Target="metadata.xml"/>`, len(names)+2, officeRel)211 }212 rels.WriteString(`</Relationships>`)213 types.WriteString(`</Types>`)214 book.WriteString(`</sheets>`)215 var dn strings.Builder216 for i, r := range filters {217 if r != "" {218 fmt.Fprintf(&dn, `<definedName name="_xlnm._FilterDatabase" localSheetId="%d" hidden="1">%s</definedName>`, i, escapeXML(r, false))219 }220 }221 for _, n := range defined {222 fmt.Fprintf(&dn, `<definedName name="%s">%s</definedName>`, escapeXML(n[0], true), escapeXML(n[1], false))223 }224 if dn.Len() > 0 {225 book.WriteString(`<definedNames>` + dn.String() + `</definedNames>`)226 }227 book.WriteString(`<calcPr calcId="191029" fullCalcOnLoad="1"/></workbook>`)228 for _, p := range []struct{ name, body string }{229 {"[Content_Types].xml", types.String()},230 {"_rels/.rels", xmlHead + `<Relationships xmlns="` + relsNS + `"><Relationship Id="rId1" Type="` + officeRel + `/officeDocument" Target="xl/workbook.xml"/></Relationships>`},231 {"xl/workbook.xml", book.String()},232 {"xl/_rels/workbook.xml.rels", rels.String()},233 } {234 if err := writePart(zw, p.name, p.body); err != nil {235 return err236 }237 }238 return nil239}240241func writePart(zw *zip.Writer, name, body string) error {242 w, err := zw.Create(name)243 if err != nil {244 return err245 }246 _, err = io.WriteString(w, body)247 return err248}249250// writeSheet writes snapshot sn as worksheet part i and returns the rows251// written.252func (w *xlsxWriter) writeSheet(zw *zip.Writer, i int, ws string, sn *Snapshot, active bool) (int, error) {253 part, err := zw.Create("xl/worksheets/sheet" + strconv.Itoa(i+1) + ".xml")254 if err != nil {255 return 0, err256 }257 bw := bufio.NewWriterSize(part, 64<<10)258 rows := w.sheet(bw, ws, sn, active)259 return rows, bw.Flush()260}261262// sheetName makes a valid Excel sheet name: at most 31 characters, none263// of : \ / ? * [ ].264func sheetName(s string) string {265 s = strings.Map(func(r rune) rune {266 if strings.ContainsRune(`:\/?*[]`, r) {267 return '_'268 }269 return r270 }, s)271 s = strings.Trim(strings.TrimSpace(s), "'")272 if r := []rune(s); len(r) > 31 {273 s = string(r[:31])274 }275 if s == "" {276 return "Sheet1"277 }278 return s279}