| 1 | // Package xlsx converts between an XLSX workbook and one tab of this |
| 2 | // app's own storage (internal/sheets/store). excelize is confined to |
| 3 | // this package deliberately: its calc engine works against a file model |
| 4 | // rather than our dependency graph (internal/sheets/calc), so letting any |
| 5 | // other package import it would couple our storage to theirs. |
| 6 | package xlsx |
| 7 | |
| 8 | import ( |
| 9 | "errors" |
| 10 | "fmt" |
| 11 | "io" |
| 12 | |
| 13 | "github.com/xuri/excelize/v2" |
| 14 | |
| 15 | "amadan.net/oficina/sheets/internal/sheets/store" |
| 16 | ) |
| 17 | |
| 18 | // importActor is the actor id Import's own writes are attributed with. |
| 19 | // Import's signature (fixed by the interface this package implements) |
| 20 | // carries no caller identity, and the background-job wrapper that would |
| 21 | // thread a real uploading user's id through (spec §11: "Import runs as a |
| 22 | // rastrillo background job") is plan 2, not built in this repo yet. 0 is |
| 23 | // not a real user id anywhere in this schema, so it reads unambiguously |
| 24 | // as "the system, not a person" in the event log. |
| 25 | const importActor uint64 = 0 |
| 26 | |
| 27 | // openLimits bounds what a workbook may decompress to. |
| 28 | // |
| 29 | // XLSX is a ZIP, so the size of the upload bounds nothing: excelize's |
| 30 | // own defaults are UnzipSizeLimit 16GB and an unzipXML limit derived |
| 31 | // from it, and the caller's byte cap on the HTTP body is a cap on |
| 32 | // COMPRESSED bytes. A small file of highly compressible XML therefore |
| 33 | // reaches OpenReader and GetRows — both of which materialise what they |
| 34 | // read — long before Import's own MaxCellsPerTab check gets a chance to |
| 35 | // refuse it, because that check counts cells that have already been |
| 36 | // decompressed into memory. |
| 37 | // |
| 38 | // UnzipSizeLimit is the hard bound and the one doing the work here: |
| 39 | // 256MB unzipped is generous for a workbook that has to fit in |
| 40 | // MaxCellsPerTab (200,000 cells) and small enough that several |
| 41 | // concurrent imports cannot exhaust an instance. |
| 42 | // |
| 43 | // UnzipXMLSizeLimit is NOT a second rejection threshold, whatever its |
| 44 | // name suggests — excelize uses it to decide when a worksheet or shared |
| 45 | // string table is large enough to stream through temporary storage |
| 46 | // instead of memory (its default is StreamChunkSize). Setting it lower |
| 47 | // than the total therefore bounds resident memory rather than refusing |
| 48 | // anything, which is worth having and is not the protection. |
| 49 | // |
| 50 | // This is a bound, not validation: a legitimate workbook past it is |
| 51 | // refused the same as a malicious one, and that is the right trade for |
| 52 | // a limit whose job is to stop the process dying. |
| 53 | var openLimits = excelize.Options{ |
| 54 | UnzipSizeLimit: 256 << 20, |
| 55 | UnzipXMLSizeLimit: 64 << 20, |
| 56 | } |
| 57 | |
| 58 | // Import reads an XLSX workbook's first worksheet into h, cell by cell: |
| 59 | // literal values become literal input, formulas are parsed and |
| 60 | // evaluated by this app's own engine (never excelize's — see the package |
| 61 | // doc), and each cell's fill, number format, bold, italic and alignment |
| 62 | // are carried over via SetFormat. |
| 63 | // |
| 64 | // Errors carry coordinates and codes, never cell content (spec §2.4): |
| 65 | // import runs as a background job whose error text reaches the owner and |
| 66 | // platform logs this app cannot grep, so "cannot parse cell B7" is what |
| 67 | // gets said — never what B7 actually contained. |
| 68 | func Import(r io.Reader, h *store.Handle) error { |
| 69 | f, err := excelize.OpenReader(r, openLimits) |
| 70 | if err != nil { |
| 71 | return fmt.Errorf("xlsx: open workbook: %w", err) |
| 72 | } |
| 73 | defer f.Close() |
| 74 | |
| 75 | sheetList := f.GetSheetList() |
| 76 | if len(sheetList) == 0 { |
| 77 | return errors.New("xlsx: workbook has no worksheets") |
| 78 | } |
| 79 | sheetName := sheetList[0] |
| 80 | |
| 81 | rows, err := f.GetRows(sheetName, excelize.Options{RawCellValue: true}) |
| 82 | if err != nil { |
| 83 | return fmt.Errorf("xlsx: read worksheet: %w", err) |
| 84 | } |
| 85 | |
| 86 | // Fail before writing anything: a partial import left behind by |
| 87 | // hitting the cap mid-way through would be a confusing half state, |
| 88 | // and the bounding box is a cheap, conservative upper bound on the |
| 89 | // cell count to check it against up front. |
| 90 | total := 0 |
| 91 | for _, row := range rows { |
| 92 | total += len(row) |
| 93 | } |
| 94 | if total > store.MaxCellsPerTab { |
| 95 | return store.ErrTabTooLarge |
| 96 | } |
| 97 | |
| 98 | for rowPos, row := range rows { |
| 99 | for colPos, raw := range row { |
| 100 | if err := importCell(f, sheetName, h, rowPos, colPos, raw); err != nil { |
| 101 | return err |
| 102 | } |
| 103 | } |
| 104 | } |
| 105 | |
| 106 | // Layout last, and best effort. A workbook whose widths cannot be |
| 107 | // read is still a workbook whose cells can, and refusing the import |
| 108 | // over its margins would trade the document for its formatting. |
| 109 | widest := 0 |
| 110 | for _, row := range rows { |
| 111 | if len(row) > widest { |
| 112 | widest = len(row) |
| 113 | } |
| 114 | } |
| 115 | importSizes(f, sheetName, h, len(rows), widest) |
| 116 | return nil |
| 117 | } |
| 118 | |
| 119 | // importCell imports one XLSX cell at (rowPos, colPos) (0-based). It |
| 120 | // creates the backing Row/Col only if the cell actually has content or a |
| 121 | // format worth recording — an interior blank cell with neither leaves no |
| 122 | // trace, matching the sparse model (models.go's comment on Cell). |
| 123 | func importCell(f *excelize.File, sheetName string, h *store.Handle, rowPos, colPos int, raw string) error { |
| 124 | cellRef, err := excelize.CoordinatesToCellName(colPos+1, rowPos+1) |
| 125 | if err != nil { |
| 126 | return cellErr("cannot address cell", rowPos, colPos, err) |
| 127 | } |
| 128 | |
| 129 | input, err := cellInputText(f, sheetName, cellRef, rowPos, colPos, raw) |
| 130 | if err != nil { |
| 131 | return err |
| 132 | } |
| 133 | |
| 134 | styleID, err := f.GetCellStyle(sheetName, cellRef) |
| 135 | if err != nil { |
| 136 | return cellErr("cannot read the style at", rowPos, colPos, err) |
| 137 | } |
| 138 | style, err := f.GetStyle(styleID) |
| 139 | if err != nil { |
| 140 | return cellErr("cannot read the style at", rowPos, colPos, err) |
| 141 | } |
| 142 | format, hasFormat := formatFromStyle(style) |
| 143 | |
| 144 | if input == "" && !hasFormat { |
| 145 | return nil // nothing here worth a Row/Col at all |
| 146 | } |
| 147 | |
| 148 | rowID, err := h.EnsureRow(rowPos) |
| 149 | if err != nil { |
| 150 | return cellErr("cannot make the row for cell", rowPos, colPos, err) |
| 151 | } |
| 152 | colID, err := h.EnsureCol(colPos) |
| 153 | if err != nil { |
| 154 | return cellErr("cannot make the column for cell", rowPos, colPos, err) |
| 155 | } |
| 156 | |
| 157 | if input != "" { |
| 158 | if err := h.Set(rowID, colID, input, importActor); err != nil { |
| 159 | return cellErr("cannot parse cell", rowPos, colPos, err) |
| 160 | } |
| 161 | } |
| 162 | if hasFormat { |
| 163 | if err := h.SetFormat(rowID, colID, format); err != nil { |
| 164 | return cellErr("cannot set the format at", rowPos, colPos, err) |
| 165 | } |
| 166 | } |
| 167 | return nil |
| 168 | } |
| 169 | |
| 170 | // cellInputText decides what raw text (this app's own Set contract: |
| 171 | // "" is blank, a leading "=" is a formula, anything else is a literal — |
| 172 | // store/handle_codec.go) a cell becomes. |
| 173 | // |
| 174 | // A genuine XLSX formula is detected through excelize's own formula |
| 175 | // reader, never by sniffing raw's leading character — but a plain TEXT |
| 176 | // cell whose content happens to start with "=" is indistinguishable from |
| 177 | // a formula once it becomes this app's raw text, because Set's contract |
| 178 | // (predating this task — store/handle_codec.go, task 10) has exactly one |
| 179 | // signal for "is this a formula": a leading "=" in the one text field a |
| 180 | // cell has. That is a pre-existing modeling gap this task inherits, not |
| 181 | // one introduced by XLSX import; it is called out in the task report |
| 182 | // rather than worked around here. |
| 183 | func cellInputText(f *excelize.File, sheetName, cellRef string, rowPos, colPos int, raw string) (string, error) { |
| 184 | formula, err := f.GetCellFormula(sheetName, cellRef) |
| 185 | if err != nil { |
| 186 | return "", cellErr("cannot read the formula at", rowPos, colPos, err) |
| 187 | } |
| 188 | if formula != "" { |
| 189 | return "=" + formula, nil |
| 190 | } |
| 191 | if raw == "" { |
| 192 | return "", nil |
| 193 | } |
| 194 | |
| 195 | cellType, err := f.GetCellType(sheetName, cellRef) |
| 196 | if err != nil { |
| 197 | return "", cellErr("cannot read the cell type at", rowPos, colPos, err) |
| 198 | } |
| 199 | if cellType == excelize.CellTypeBool { |
| 200 | if raw == "1" { |
| 201 | return "TRUE", nil |
| 202 | } |
| 203 | return "FALSE", nil |
| 204 | } |
| 205 | return raw, nil |
| 206 | } |
| 207 | |
amadan