| 1 | package xlsx |
| 2 | |
| 3 | import ( |
| 4 | "math" |
| 5 | "sort" |
| 6 | |
| 7 | "github.com/xuri/excelize/v2" |
| 8 | |
| 9 | "amadan.net/oficina/sheets/internal/sheets/store" |
| 10 | ) |
| 11 | |
| 12 | // Column widths and row heights, on import. |
| 13 | // |
| 14 | // Without them an imported file arrives with every column the same |
| 15 | // default width, which for the sheets people actually import — a label |
| 16 | // column beside four number columns — is not a rendering of the |
| 17 | // document. It is the same class of defect as not painting the fills: |
| 18 | // the information is in the file, we simply were not reading it. |
| 19 | |
| 20 | // Excel's units are not pixels, and the conversion is the fiddly part. |
| 21 | // |
| 22 | // A column width is in "characters" of the workbook's default font, and |
| 23 | // Excel's own documented formula for the pixel width of a column of |
| 24 | // width w at 96 DPI with the default 11pt Calibri is |
| 25 | // round(((256*w + round(128/MDW))/256) * MDW), where MDW is the maximum |
| 26 | // digit width in pixels — 7 for that font. That rounds to 7w + 5 for |
| 27 | // every width anybody actually has, and the five pixels are the cell's |
| 28 | // padding. |
| 29 | // |
| 30 | // It is an approximation of a measurement made in a font we are not |
| 31 | // rendering in, so it is not exact and cannot be: the browser is laying |
| 32 | // out a different typeface. What it buys is proportion — a column the |
| 33 | // author made twice as wide arrives twice as wide — and proportion is |
| 34 | // what makes an imported sheet recognisable. |
| 35 | const ( |
| 36 | excelMaxDigitWidth = 7.0 |
| 37 | excelCellPadding = 5.0 |
| 38 | ) |
| 39 | |
| 40 | // A row height is in POINTS, which is a real typographic unit and |
| 41 | // converts to CSS pixels at 96/72. |
| 42 | const pointsToPixels = 96.0 / 72.0 |
| 43 | |
| 44 | // importSizes copies a worksheet's column widths and row heights. |
| 45 | // |
| 46 | // Best effort, and deliberately not fatal: a workbook whose widths |
| 47 | // cannot be read is still a workbook whose cells can, and refusing the |
| 48 | // import over its layout would trade the whole document for its |
| 49 | // margins. Callers ignore the error for that reason and this function |
| 50 | // returns none. |
| 51 | // |
| 52 | // Only columns and rows the file gives an EXPLICIT size are touched. |
| 53 | // excelize answers the sheet's default for everything else, and writing |
| 54 | // that back would turn "the author said nothing" into "the author asked |
| 55 | // for exactly the default" — a different document, and one whose every |
| 56 | // column is pinned against a later change to our own default. What |
| 57 | // counts as the default is asked of the file rather than assumed; see |
| 58 | // defaultColWidth for the version of this that was wrong. |
| 59 | func importSizes(f *excelize.File, sheet string, h *store.Handle, rows, cols int) { |
| 60 | defW, okW := defaultColWidth(f, sheet, cols) |
| 61 | for c := 0; c < cols; c++ { |
| 62 | name, err := excelize.ColumnNumberToName(c + 1) |
| 63 | if err != nil { |
| 64 | continue |
| 65 | } |
| 66 | w, err := f.GetColWidth(sheet, name) |
| 67 | if err != nil || !okW || w == defW { |
| 68 | continue |
| 69 | } |
| 70 | px := int(math.Round(w*excelMaxDigitWidth + excelCellPadding)) |
| 71 | colID, err := h.EnsureCol(c) |
| 72 | if err != nil { |
| 73 | continue |
| 74 | } |
| 75 | // SetColWidth bounds the value itself and errors on one outside |
| 76 | // them; a column the file made absurdly wide is skipped rather |
| 77 | // than clamped, because clamping would silently claim the |
| 78 | // author asked for our maximum. |
| 79 | _ = h.SetColWidth(colID, px) |
| 80 | } |
| 81 | |
| 82 | defH, okH := defaultRowHeight(f, sheet, rows) |
| 83 | for r := 0; r < rows; r++ { |
| 84 | ht, err := f.GetRowHeight(sheet, r+1) |
| 85 | if err != nil || !okH || ht == defH { |
| 86 | continue |
| 87 | } |
| 88 | rowID, err := h.EnsureRow(r) |
| 89 | if err != nil { |
| 90 | continue |
| 91 | } |
| 92 | _ = h.SetRowHeight(rowID, int(math.Round(ht*pointsToPixels))) |
| 93 | } |
| 94 | } |
| 95 | |
| 96 | // probeDistance is how far past the used range to look for a sheet's |
| 97 | // default size. |
| 98 | // |
| 99 | // Far enough that a file written by Excel has no explicit width out |
| 100 | // there, near enough to stay inside the format's limits. |
| 101 | // |
| 102 | // **It is not far enough for every producer, and cannot be.** |
| 103 | // LibreOffice writes a single `<col min="1" max="1025">` covering the |
| 104 | // whole sheet, so the probe lands inside it and reads that file's |
| 105 | // uniform width as the default — with the result that a uniformly |
| 106 | // widened LibreOffice file imports with no widths at all. That is the |
| 107 | // safe direction to be wrong in (nothing invented, the app's own |
| 108 | // default used) and the alternative is reading excelize's internals to |
| 109 | // distinguish a span from a default, which is a coupling to a library |
| 110 | // version rather than to a format. Named rather than papered over. |
| 111 | const probeDistance = 64 |
| 112 | |
| 113 | // defaultColWidth asks the file what an UNTOUCHED column is. |
| 114 | // |
| 115 | // **Probing a column past the data, not column A.** GetColWidth answers |
| 116 | // for any column: an explicit <col> entry if one covers it, then the |
| 117 | // sheet's declared default, then the format's. So a column outside the |
| 118 | // used range is answered by exactly the fall-through this needs, using |
| 119 | // the library's own code path rather than a constant restated here. |
| 120 | // |
| 121 | // The obvious version — take column A's width as the default — is |
| 122 | // wrong in precisely the case that matters, and wrong silently. A file |
| 123 | // whose author widened column A reports that width as "the default", |
| 124 | // so column A is then treated as unset and skipped while every ordinary |
| 125 | // column looks customised and is written. The result is a document with |
| 126 | // its layout inverted, and it passed review here until a test asked for |
| 127 | // a specific number. |
| 128 | func defaultColWidth(f *excelize.File, sheet string, cols int) (float64, bool) { |
| 129 | name, err := excelize.ColumnNumberToName(cols + probeDistance + 1) |
| 130 | if err != nil { |
| 131 | return 0, false |
| 132 | } |
| 133 | w, err := f.GetColWidth(sheet, name) |
| 134 | if err != nil { |
| 135 | return 0, false |
| 136 | } |
| 137 | return w, true |
| 138 | } |
| 139 | |
| 140 | // defaultRowHeight is defaultColWidth's twin — same probe, same reason. |
| 141 | func defaultRowHeight(f *excelize.File, sheet string, rows int) (float64, bool) { |
| 142 | ht, err := f.GetRowHeight(sheet, rows+probeDistance+1) |
| 143 | if err != nil { |
| 144 | return 0, false |
| 145 | } |
| 146 | return ht, true |
| 147 | } |
| 148 | |
| 149 | // exportSizes writes a tab's column widths and row heights back out. |
| 150 | // |
| 151 | // The inverse of importSizes, and it exists for the same reason |
| 152 | // Format.FillHex is retained: a round trip that silently drops the |
| 153 | // author's layout is not lossless, and the file that comes out would be |
| 154 | // a different document from the one that went in — one where every |
| 155 | // column had reverted to the default width. |
| 156 | // |
| 157 | // Only sized rows and columns are written. A tab where nobody has |
| 158 | // dragged anything writes nothing, which leaves the workbook's own |
| 159 | // defaults alone rather than pinning every column against ours. |
| 160 | func exportSizes(f *excelize.File, sheet string, h *store.Handle) error { |
| 161 | cols, rows, err := h.Sizes() |
| 162 | if err != nil { |
| 163 | return err |
| 164 | } |
| 165 | // Sorted, so two exports of an unchanged tab produce identical |
| 166 | // bytes. Go randomises map iteration, which would otherwise reorder |
| 167 | // the <cols> block on every run and make a corpus diff unreadable. |
| 168 | for _, pos := range sortedKeys(cols) { |
| 169 | px := cols[pos] |
| 170 | name, err := excelize.ColumnNumberToName(pos + 1) |
| 171 | if err != nil { |
| 172 | continue |
| 173 | } |
| 174 | // The inverse of importSizes' conversion. Rounded to two places |
| 175 | // because that is the precision the format stores, and a width |
| 176 | // carrying fifteen significant figures is noise in a diff. |
| 177 | w := (float64(px) - excelCellPadding) / excelMaxDigitWidth |
| 178 | if w <= 0 { |
| 179 | continue |
| 180 | } |
| 181 | if err := f.SetColWidth(sheet, name, name, math.Round(w*100)/100); err != nil { |
| 182 | return err |
| 183 | } |
| 184 | } |
| 185 | for _, pos := range sortedKeys(rows) { |
| 186 | px := rows[pos] |
| 187 | if err := f.SetRowHeight(sheet, pos+1, math.Round(float64(px)/pointsToPixels*100)/100); err != nil { |
| 188 | return err |
| 189 | } |
| 190 | } |
| 191 | return nil |
| 192 | } |
| 193 | |
| 194 | func sortedKeys(m map[int]int) []int { |
| 195 | out := make([]int, 0, len(m)) |
| 196 | for k := range m { |
| 197 | out = append(out, k) |
| 198 | } |
| 199 | sort.Ints(out) |
| 200 | return out |
| 201 | } |
| 202 | |
amadan