1package fileio23import (4 "context"5 "fmt"6 "math"7 "os"8 "slices"9 "strconv"10 "strings"11 "time"1213 "src.rest/cat/012/internal/sheet"14)1516// importXLSX reads every sheet of a workbook, with its named17// ranges, and returns the sheet that was active in Excel. Worksheets are18// streamed a row at a time (see xlsxpkg.go).19func importXLSX(ctx context.Context, name string, opt Options) (*Result, error) {20 f, err := os.Open(name)21 if err != nil {22 return nil, err23 }24 defer f.Close()25 st, err := f.Stat()26 if err != nil {27 return nil, err28 }29 bk, err := openXLSX(f, st.Size(), defaultXLSXLimits)30 if err != nil {31 return nil, err32 }33 return bk.importBook(ctx, opt)34}3536func (bk *xlsxBook) importBook(ctx context.Context, opt Options) (*Result, error) {37 prog := opt.Progress38 if len(bk.sheets) == 0 {39 return nil, fmt.Errorf("the workbook has no sheets")40 }41 // Rows across all sheets, for the progress bar.42 total := 043 for i := range bk.sheets {44 total += bk.dimension(i)45 }46 b := newBuilder(ctx, opt.MaxCells)47 book := b.s.Book()48 bk.styles.loc = opt.Locale49 if bk.styles.loc == nil {50 bk.styles.loc = book.Locale()51 }52 done := 053 filters := make([]*xlsxAutoFilter, len(bk.sheets))54 var err error55 for i, info := range bk.sheets {56 next := b.s57 if i == 0 {58 err = book.RenameSheet(b.s, info.name)59 } else {60 next, err = book.AddSheet(info.name, i)61 }62 if err != nil {63 return nil, fmt.Errorf("sheet %s: %w", info.name, err)64 }65 b.nextSheet(next)66 rows, err := bk.importSheet(ctx, b, i, &filters[i], func(row int) {67 prog.setRows(done + row)68 prog.setFrac(int64(done+row), int64(total))69 })70 if err != nil {71 return nil, err72 }73 done += rows74 }75 notes := append(bk.importNames(book), bk.protectionNote()...)76 notes = append(notes, bk.tablesNote()...)77 active := book.Sheet(clamp(bk.active, 0, book.Len()-1))78 book.SetActive(active)79 for i, info := range bk.sheets {80 if info.hidden && book.Sheet(i) != active {81 book.HideSheet(book.Sheet(i)) // hidden in Excel, hidden here82 }83 }84 b.s = active85 prog.setRows(done)86 s, notes := b.finish(notes)87 notes = append(notes, applyFilters(book, filters)...)88 return &Result{Sheet: s, Rows: done, Notes: append(notes, bk.skips.notes()...)}, nil89}9091// importSheet reads sheet i into b.s, with its frozen panes, reporting92// rows read, and returns the number of its last row. Its autoFilter, if93// any, goes in af, to apply once values are computed.94func (bk *xlsxBook) importSheet(ctx context.Context, b *builder, i int, af **xlsxAutoFilter, report func(int)) (int, error) {95 r, err := bk.openSheet(i)96 if err != nil {97 return 0, err98 }99 defer r.close()100 b.s.LoadFrozen(r.frozenRows, r.frozenCols)101 last, width := 0, 0102 for n := 0; ; n++ {103 ok, err := r.next()104 if err != nil {105 return 0, fmt.Errorf("sheet %s: %w", bk.sheets[i].name, err)106 }107 if !ok {108 break109 }110 if n%128 == 0 {111 if err := ctx.Err(); err != nil {112 return 0, err113 }114 report(r.row.num)115 }116 if err := bk.importRow(b, r); err != nil {117 return 0, fmt.Errorf("sheet %s: %w", bk.sheets[i].name, err)118 }119 last = max(last, r.row.num)120 if n := len(r.row.cells); n > 0 {121 width = max(width, r.row.cells[n-1].col)122 }123 }124 if *af, err = r.readTail(); err != nil {125 return 0, fmt.Errorf("sheet %s: %w", bk.sheets[i].name, err)126 }127 bk.loadRules(b.s, r)128 for _, m := range r.merges {129 b.s.LoadMerge(m)130 }131 if r.protected {132 bk.protected = append(bk.protected, bk.sheets[i].name)133 }134 if err := bk.readNotes(b.s, bk.sheets[i].part); err != nil {135 return 0, fmt.Errorf("sheet %s: %w", bk.sheets[i].name, err)136 }137 if err := bk.readTables(b.s, bk.sheets[i].part, af); err != nil {138 return 0, fmt.Errorf("sheet %s: %w", bk.sheets[i].name, err)139 }140 loadColStyles(b, width, func(c int) (xlsxStyle, bool) {141 st := bk.styles.style(r.lineStyle(c + 1))142 return st, st != (xlsxStyle{})143 })144 for c := range min(max(width, 256), sheet.MaxCols) { // the data's columns, and 1-2-3's145 w := r.colWidth(c + 1)146 if math.Abs(w-excelDefaultWidth) < 0.01 || math.Abs(w-9.140625) < 0.01 || !(w > 0 && w < 1000) {147 continue148 }149 b.s.LoadColWidth(c, max(int(math.Round(w))+excelPadding, 1))150 }151 return last, nil152}153154// importRow stores a row's cells. As Excel's cells are read, a cell155// with neither a value nor a formula only counts for its format, and156// only up to the row's last cell with one: formatted blanks after it are157// left out.158func (bk *xlsxBook) importRow(b *builder, r *xlsxSheetReader) error {159 row := &r.row160 if row.style != 0 {161 loadRowStyle(b, row.num-1, bk.styles.style(row.style))162 }163 if row.height > 0 && row.num <= sheet.MaxRows {164 b.s.LoadRowHeight(row.num-1, row.height)165 }166 cells := row.cells167 if !slices.IsSortedFunc(cells, func(a, b xlsxCell) int { return a.col - b.col }) {168 slices.SortStableFunc(cells, func(a, b xlsxCell) int { return a.col - b.col })169 }170 lastCol := bk.lastKept(cells)171 if lastCol == 0 || !b.fits(sheet.Addr{Col: lastCol - 1, Row: row.num - 1}) && row.num > sheet.MaxRows {172 return nil173 }174 j := 0175 for col := 1; col <= min(lastCol, sheet.MaxCols); col++ {176 a := sheet.Addr{Col: col - 1, Row: row.num - 1}177 for j < len(cells) && cells[j].col < col {178 j++179 }180 var c *xlsxCell181 if j < len(cells) && cells[j].col == col {182 c = &cells[j]183 }184 st := bk.styles.style(r.cellStyle(c, col))185 if c == nil || !c.kept() || !c.hasF && r.spilled(a) {186 b.put(a, "", st.format, st.style)187 continue188 }189 f, err := r.formula(c)190 if err != nil {191 return err192 }193 if bk.importCell(b, a, c, f, st) {194 r.arrayFormula(a, c.f)195 }196 }197 return nil198}199200// lastKept is the column of a row's last cell that is kept: one with a201// value or a formula, or drawing borders.202func (bk *xlsxBook) lastKept(cells []xlsxCell) int {203 last := 0204 for i := range cells {205 if cells[i].kept() || !bk.styles.style(cells[i].style).style.Borders.IsZero() {206 last = cells[i].col207 }208 }209 return last210}211212// importCell stores a cell, reporting whether it stored a formula.213// cellStyle is the cell format of the cell c (nil when it isn't written)214// in column col of the current row: its own, else the row's, else the215// column's.216func (r *xlsxSheetReader) cellStyle(c *xlsxCell, col int) int {217 if c != nil && c.style != 0 {218 return c.style219 }220 if r.row.style != 0 {221 return r.row.style222 }223 return r.colStyle(col)224}225226func (bk *xlsxBook) importCell(b *builder, a sheet.Addr, c *xlsxCell, formula string, st xlsxStyle) bool {227 if formula != "" {228 return b.formula(a, fromExcelFormula(formula), st.format, st.style, func() { bk.keep(b, a, c, st) })229 }230 if c.value == "" {231 b.put(a, "", st.format, st.style)232 return false233 }234 bk.keep(b, a, c, st)235 return false236}237238// keep stores a cell's value: its type says whether it is a boolean or239// text; anything else that reads as a number is one.240func (bk *xlsxBook) keep(b *builder, a sheet.Addr, c *xlsxCell, st xlsxStyle) {241 raw := c.value242 switch c.typ {243 case "b":244 b.boolean(a, raw == "1" || strings.EqualFold(raw, "TRUE"), st.style)245 return246 case "s", "inlineStr", "str", "e":247 b.text(a, raw, st.format, st.style)248 return249 case "d":250 if v, f, ok := isoDate(raw); ok {251 if st.format.IsZero() {252 st.format = f253 }254 b.number(a, v, st.format, st.style)255 return256 }257 }258 if v, err := strconv.ParseFloat(raw, 64); err == nil {259 b.number(a, bk.serial(v, st.format), st.format, st.style)260 return261 }262 b.text(a, raw, st.format, st.style)263}264265// serial converts an Excel serial number to 012's: in the 1904 date266// system, day 0 is January 1, 1904.267func (bk *xlsxBook) serial(v float64, f sheet.Format) float64 {268 if bk.date1904 && isDateFormat(f) {269 return v + date1904Offset270 }271 return fromExcelSerial(v, f)272}273274// date1904Offset is the serial of January 1, 1904 in 012's (and the 1900275// system's) count.276const date1904Offset = 1462277278// isoDate reads a t="d" cell's ISO 8601 date, time or both as 012's279// serial number, with the format that shows it.280func isoDate(s string) (float64, sheet.Format, bool) {281 s = strings.TrimSpace(s)282 for _, l := range []struct {283 layout string284 kind sheet.FormatKind285 }{286 {"2006-01-02T15:04:05.999999999Z07:00", sheet.FmtDateTime},287 {"2006-01-02T15:04:05.999999999", sheet.FmtDateTime},288 {"2006-01-02", sheet.FmtDate},289 {"T15:04:05.999999999", sheet.FmtTime},290 {"15:04:05.999999999", sheet.FmtTime},291 } {292 t, err := time.Parse(l.layout, s)293 if err != nil {294 continue295 }296 wall := time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), time.UTC)297 base := time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)298 if l.kind == sheet.FmtTime {299 base = time.Date(0, 1, 1, 0, 0, 0, 0, time.UTC)300 }301 return wall.Sub(base).Hours() / 24, sheet.Format{Kind: l.kind}, true302 }303 return 0, sheet.Format{}, false304}305306// protectionNote says which sheets were protected in the file: 012307// leaves their protection out, since Excel's locks cells while 012's308// only warns, and Data > Protect sheets and ranges adds that.309func (bk *xlsxBook) protectionNote() []string {310 if len(bk.protected) == 0 {311 return nil312 }313 return []string{fmt.Sprintf("Protection of %s left out: Data > Protect sheets and ranges warns before edits",314 strings.Join(bk.protected, ", "))}315}316317// importNames defines the workbook's named ranges that are a range on318// one sheet, such as Sales = Q3!$B$2:$B$20, and notes the others (sheet319// scoped names, constants, formulas).320func (bk *xlsxBook) importNames(book *sheet.Workbook) []string {321 skipped := 0322 example := ""323 for _, dn := range bk.names {324 if strings.HasPrefix(dn.name, "_xlnm.") {325 continue // print areas and the like326 }327 ws, rest := sheet.SplitSheet(strings.TrimPrefix(dn.refersTo, "="))328 r, ok := sheet.ParseRange(rest)329 s := book.Lookup(ws)330 if bk.scoped(dn) || !ok || s == nil || book.DefineName(dn.name, s, r) != nil {331 skipped++332 if example == "" {333 example = dn.name334 }335 }336 }337 if skipped > 0 {338 return []string{fmt.Sprintf("%s left out, e.g. %s", count(skipped, "named range", "named ranges"), example)}339 }340 return nil341}342343// scoped reports whether a name belongs to one sheet. As excelize has344// it, a name scoped to a sheet that doesn't exist, or to one called345// Workbook, is the workbook's.346func (bk *xlsxBook) scoped(dn xlsxName) bool {347 return dn.sheet >= 0 && dn.sheet < len(bk.sheets) && bk.sheets[dn.sheet].name != "Workbook"348}