1package fileio23import (4 "encoding/xml"5 "fmt"6 "io"7 "slices"8 "strconv"9 "strings"1011 "src.rest/cat/012/internal/sheet"12)1314// Excel's own limits on a worksheet. A reference past them is an error,15// as it is in Excel.16const (17 excelRows = 1 << 2018 excelCols = 1 << 1419)2021// xlsxCell is a cell of a worksheet row, as the reader passes it on.22type xlsxCell struct {23 col int // from 124 style int // the cell format index (s), 0 when not given25 typ string // the type (t): s, str, inlineStr, b, e, n, d or ""26 value string // the value, a shared string looked up27 f cellFormula // its <f>, see xlsxSheetReader.formula28 hasF bool // the cell has a formula element, even an empty one29}3031// kept reports whether excelize's row reader would return the cell: it32// has a value or a formula element. The others are formatted blanks.33func (c *xlsxCell) kept() bool { return c.value != "" || c.hasF }3435// xlsxCol is a <col> element: widths and styles for columns min to max.36type xlsxCol struct {37 min, max int38 width float6439 hasWidth bool40 style int41}4243// xlsxSheetReader streams a worksheet's rows. Opening it reads what44// comes before the cells: column widths and styles, the default width.45type xlsxSheetReader struct {46 bk *xlsxBook47 x *xmlStream48 cols []xlsxCol49 // sheetFormatPr's defaultColWidth and baseColWidth.50 defaultWidth float6451 baseWidth int5253 colStyles []int // by column, see colStyle5455 // The first sheet view's frozen panes (see readPane).56 paneRead bool57 frozenRows, frozenCols int5859 // The rules after the rows, see xlsxrulesread.go, and how many60 // conditional formats Excel 2010's extension holds.61 cfs []xlsxCF62 dvs []xlsxDV63 extRules int6465 protected bool // the sheet has a <sheetProtection sheet="1">, read by readTail66 merges []sheet.Rect // its merged cells, read by readTail6768 inData bool // inside <sheetData>69 row xlsxRowData70 shared map[int]sharedFormula71 buf []byte72 // arrays are the cells array formulas read so far spill into, below73 // or right of them, whose values the formulas compute again.74 arrays []sheet.Rect75}7677// arrayFormula notes the cells the array formula f at a spills into.78func (r *xlsxSheetReader) arrayFormula(a sheet.Addr, f cellFormula) {79 if f.typ != "array" {80 return81 }82 from, to, ok := strings.Cut(strings.ReplaceAll(f.ref, "$", ""), ":")83 if !ok {84 return85 }86 c0, r0, ok0 := parseCellRef(from)87 c1, r1, ok1 := parseCellRef(to)88 if ok0 && ok1 && c0 == a.Col+1 && r0 == a.Row+1 && c1 >= c0 && r1 >= r0 {89 r.arrays = append(r.arrays, sheet.Rect{From: a, To: sheet.Addr{Col: c1 - 1, Row: r1 - 1}})90 }91}9293// spilled reports whether the cell at a is one an array formula read94// before spills into, forgetting the formulas whose cells are all past.95func (r *xlsxSheetReader) spilled(a sheet.Addr) bool {96 r.arrays = slices.DeleteFunc(r.arrays, func(x sheet.Rect) bool { return x.To.Row < a.Row })97 for _, x := range r.arrays {98 if x.Contains(a) {99 return true100 }101 }102 return false103}104105// xlsxRowData is the row just read.106type xlsxRowData struct {107 num int // from 1108 style int // the row's cell format index, for cells not written109 height int // lines, when set by hand to more than one; see rowHeight110 cells []xlsxCell111}112113// sharedFormula is the master cell of a shared formula.114type sharedFormula struct {115 text string116 col, row int117}118119// openSheet opens sheet i. A sheet with no worksheet part (a chart120// sheet) reads as empty.121func (bk *xlsxBook) openSheet(i int) (*xlsxSheetReader, error) {122 info := bk.sheets[i]123 if info.part == "" {124 return nil, fmt.Errorf("sheet %s: its part is missing", info.name)125 }126 x, err := bk.pkg.open(info.part)127 if err != nil {128 return nil, err129 }130 r := &xlsxSheetReader{bk: bk, x: x, shared: map[int]sharedFormula{}}131 if info.kind != "worksheet" {132 return r, nil133 }134 return r, r.readHead()135}136137func (r *xlsxSheetReader) close() error { return r.x.close() }138139// readHead reads up to the start of <sheetData>.140func (r *xlsxSheetReader) readHead() error {141 for {142 t, err := r.x.next()143 if err == io.EOF {144 return nil145 }146 if err != nil {147 return err148 }149 se, ok := t.(xml.StartElement)150 if !ok || r.x.depth != 2 && se.Name.Local != "col" && se.Name.Local != "pane" {151 continue152 }153 switch se.Name.Local {154 case "pane":155 if r.x.depth == 4 {156 r.readPane(se)157 }158 case "sheetData":159 r.inData = true160 return nil161 case "sheetFormatPr":162 r.defaultWidth, _ = strconv.ParseFloat(attrOr(se, "defaultColWidth", "0"), 64)163 r.baseWidth = intAttr(se, "baseColWidth", 0)164 case "col":165 if r.x.depth == 3 && len(r.cols) < excelCols {166 w, err := strconv.ParseFloat(attrOr(se, "width", ""), 64)167 r.cols = append(r.cols, xlsxCol{168 min: intAttr(se, "min", 0), max: intAttr(se, "max", 0),169 width: w, hasWidth: err == nil, style: intAttr(se, "style", 0),170 })171 }172 }173 }174}175176// colStyle is the cell format of column col (from 1): the first <col>177// covering it with a style. Columns up to sheet.MaxCols, the ones asked178// for once per cell, are looked up once.179func (r *xlsxSheetReader) colStyle(col int) int {180 if r.colStyles == nil {181 r.colStyles = make([]int, sheet.MaxCols)182 for _, c := range r.cols {183 for i := max(c.min, 1); i <= min(c.max, sheet.MaxCols); i++ {184 if r.colStyles[i-1] == 0 {185 r.colStyles[i-1] = c.style186 }187 }188 }189 }190 if col >= 1 && col <= len(r.colStyles) {191 return r.colStyles[col-1]192 }193 for _, c := range r.cols {194 if c.min <= col && col <= c.max && c.style != 0 {195 return c.style196 }197 }198 return 0199}200201// lineStyle is the style of column col (from 1) as a whole: that of the202// last <col> covering it, as for its width (and as excelize reads it).203func (r *xlsxSheetReader) lineStyle(col int) int {204 style := 0205 for _, c := range r.cols {206 if c.min <= col && col <= c.max {207 style = c.style208 }209 }210 return style211}212213// colWidth is column col's width in Excel's characters: the last <col>214// covering it with a width, else the sheet's default.215func (r *xlsxSheetReader) colWidth(col int) float64 {216 w := 0.0217 for _, c := range r.cols {218 if c.min <= col && col <= c.max && c.hasWidth {219 w = c.width220 }221 }222 switch {223 case w != 0:224 return w225 case r.defaultWidth > 0:226 return r.defaultWidth227 case r.baseWidth > 0:228 return float64(r.baseWidth)229 }230 return excelDefaultWidth231}232233// next reads the next row into r.row, returning false after the last.234func (r *xlsxSheetReader) next() (bool, error) {235 for r.inData {236 t, err := r.x.next()237 if err != nil {238 return false, eofAsUnexpected(err)239 }240 switch t := t.(type) {241 case xml.StartElement:242 if t.Name.Local == "row" && r.x.depth == 3 {243 return true, r.readRow(t)244 }245 case xml.EndElement:246 if r.x.depth < 2 {247 r.inData = false248 }249 }250 }251 return false, nil252}253254// readRow reads a <row>. A row without a number follows the one before.255func (r *xlsxSheetReader) readRow(se xml.StartElement) error {256 num := intAttr(se, "r", 0)257 if num <= 0 {258 num = r.row.num + 1259 }260 if num > excelRows {261 return fmt.Errorf("row %s is past Excel's last row, %s", thousands(num), thousands(excelRows))262 }263 r.row.num, r.row.style = num, intAttr(se, "s", 0)264 r.row.height = rowHeight(se)265 r.row.cells = r.row.cells[:0]266 col := 0267 for {268 t, err := r.x.next()269 if err != nil {270 return eofAsUnexpected(err)271 }272 switch t := t.(type) {273 case xml.StartElement:274 if t.Name.Local != "c" || r.x.depth != 4 {275 if err := r.x.skip(); err != nil {276 return err277 }278 continue279 }280 if len(r.row.cells) >= excelCols {281 return fmt.Errorf("row %s has more than %s cells", thousands(num), thousands(excelCols))282 }283 if col, err = r.readCell(t, col); err != nil {284 return err285 }286 case xml.EndElement:287 return nil288 }289 }290}291292// readCell reads a <c> following column prev and appends it to the row,293// returning its column.294func (r *xlsxSheetReader) readCell(se xml.StartElement, prev int) (int, error) {295 c := xlsxCell{col: prev + 1, style: intAttr(se, "s", 0)}296 c.typ, _ = attr(se, "t")297 if ref, ok := attr(se, "r"); ok && ref != "" {298 col, _, ok := parseCellRef(ref)299 if !ok {300 return 0, fmt.Errorf("row %s: %q isn't a cell reference", thousands(r.row.num), ref)301 }302 c.col = col303 }304 if c.col > excelCols {305 return 0, fmt.Errorf("row %s: a cell past Excel's last column, XFD", thousands(r.row.num))306 }307 var v, is string308 var hasIS bool309 var f cellFormula310 depth := r.x.depth311 for {312 t, err := r.x.next()313 if err != nil {314 return 0, eofAsUnexpected(err)315 }316 if _, ok := t.(xml.EndElement); ok && r.x.depth < depth {317 break318 }319 se, ok := t.(xml.StartElement)320 if !ok {321 continue322 }323 switch se.Name.Local {324 case "v":325 r.buf, err = r.x.text(r.buf[:0])326 v = string(r.buf)327 case "f":328 c.hasF = true329 f, err = r.readFormula(se)330 case "is":331 r.buf, err = inlineText(r.x, r.buf[:0])332 is, hasIS = string(unescapeOOXML(r.buf)), true333 default:334 err = r.x.skip()335 }336 if err != nil {337 return 0, err338 }339 }340 c.value = r.value(c.typ, v, is, hasIS)341 if c.hasF {342 c.f = f343 if _, ok := r.shared[f.si]; !ok && f.typ == "shared" && f.si >= 0 && f.ref != "" {344 r.shared[f.si] = sharedFormula{text: f.text, col: c.col, row: r.row.num}345 }346 }347 r.row.cells = append(r.row.cells, c)348 return c.col, nil349}350351// value is a cell's value: its shared string, its inline string, or the352// text of its <v>.353func (r *xlsxSheetReader) value(typ, v, is string, hasIS bool) string {354 switch typ {355 case "s":356 if v == "" {357 return ""358 }359 i, err := strconv.Atoi(strings.TrimSpace(v))360 if err != nil {361 return ""362 }363 s, _ := r.bk.sst.get(i)364 return s365 case "inlineStr":366 if hasIS {367 return is368 }369 }370 return v371}372373// cellFormula is an <f> element.374type cellFormula struct {375 text, typ, ref string376 si int // -1 when not given377}378379func (r *xlsxSheetReader) readFormula(se xml.StartElement) (cellFormula, error) {380 f := cellFormula{si: intAttr(se, "si", -1)}381 f.typ, _ = attr(se, "t")382 f.ref, _ = attr(se, "ref")383 var err error384 r.buf, err = r.x.text(r.buf[:0])385 f.text = string(r.buf)386 return f, err387}388389// formula is the formula of cell c of the current row: its own text,390// or for a cell sharing a formula, the first cell's with its relative391// references moved by the distance between the cells ("" when the first392// is missing). Shared formulas are expanded only on request, and only393// up to the limit on their text: a small file of shared formulas could394// otherwise expand to gigabytes.395func (r *xlsxSheetReader) formula(c *xlsxCell) (string, error) {396 f := c.f397 if !c.hasF || f.typ != "shared" || f.si < 0 {398 return f.text, nil399 }400 m, ok := r.shared[f.si]401 if !ok {402 return "", nil403 }404 out := shiftFormula(m.text, c.col-m.col, r.row.num-m.row)405 if r.bk.expanded += int64(len(out)); r.bk.expanded > r.bk.pkg.lim.shared {406 return "", fmt.Errorf("shared formulas expand to more than %s: %w", mb(r.bk.pkg.lim.shared), errXLSXLimit)407 }408 return out, nil409}410411// parseCellRef parses an A1 reference (without $) into a column and row412// from 1, within Excel's limits.413func parseCellRef(ref string) (col, row int, ok bool) {414 i := 0415 for i < len(ref) && i < 4 && isLetter(ref[i]) {416 col = col*26 + int(upper(ref[i])-'A'+1)417 i++418 }419 if i == 0 || i == len(ref) || col > excelCols {420 return 0, 0, false421 }422 for _, c := range []byte(ref[i:]) {423 if !isDigitByte(c) {424 return 0, 0, false425 }426 row = row*10 + int(c-'0')427 if row > excelRows {428 return 0, 0, false429 }430 }431 return col, row, row >= 1432}433434func isLetter(c byte) bool { return c >= 'A' && c <= 'Z' || c >= 'a' && c <= 'z' }435436func upper(c byte) byte {437 if c >= 'a' && c <= 'z' {438 return c - 'a' + 'A'439 }440 return c441}442443// dimension is the last row of sheet i by its <dimension> element, for444// the progress bar; 0 when it doesn't say.445func (bk *xlsxBook) dimension(i int) int {446 if bk.sheets[i].part == "" {447 return 0448 }449 x, err := bk.pkg.open(bk.sheets[i].part)450 if x == nil || err != nil {451 return 0452 }453 defer x.close()454 for {455 t, err := x.next()456 if err != nil {457 return 0458 }459 se, ok := t.(xml.StartElement)460 switch {461 case !ok:462 case se.Name.Local == "sheetData":463 return 0464 case se.Name.Local == "dimension":465 ref, _ := attr(se, "ref")466 if _, to, ok := strings.Cut(ref, ":"); ok {467 _, row, _ := parseCellRef(strings.ReplaceAll(to, "$", ""))468 return row469 }470 return 0471 }472 }473}