1package fileio23// The XLSX importer 012 used before its own reader, on excelize, kept4// as the reference for TestXLSXDifferential.56import (7 "archive/zip"8 "cmp"9 "context"10 "encoding/xml"11 "fmt"12 "math"13 "os"14 "strconv"15 "strings"1617 "github.com/xuri/excelize/v2"1819 "src.rest/cat/012/internal/sheet"20)2122// importXLSXExcelize reads every sheet of a workbook, with its named ranges, and23// returns the sheet that was active in Excel.24func importXLSXExcelize(ctx context.Context, name string, opt Options) (*Result, error) {25 prog := opt.Progress26 x, err := excelize.OpenFile(name)27 if err != nil {28 return nil, err29 }30 defer x.Close()31 names := x.GetSheetList()32 if len(names) == 0 {33 return nil, fmt.Errorf("the workbook has no sheets")34 }35 // Rows across all sheets, for the progress bar.36 dims := make([]int, len(names))37 total := 038 for i, ws := range names {39 if dim, err := x.GetSheetDimension(ws); err == nil {40 if _, to, ok := strings.Cut(dim, ":"); ok {41 if _, r, err := excelize.CellNameToCoordinates(to); err == nil {42 dims[i] = r43 }44 }45 }46 total += dims[i]47 }4849 b := newBuilder(ctx, opt.MaxCells)50 book := b.s.Book()51 styles := map[int]xlsxStyle{}52 var notes []string53 done := 054 for i, ws := range names {55 next := b.s56 if i == 0 {57 err = book.RenameSheet(b.s, ws)58 } else {59 next, err = book.AddSheet(ws, i)60 }61 if err != nil {62 return nil, fmt.Errorf("sheet %s: %w", ws, err)63 }64 b.nextSheet(next)65 rows, err := excelizeSheet(ctx, x, b, ws, styles, func(row int) {66 prog.setRows(done + row)67 prog.setFrac(int64(done+row), int64(total))68 })69 if err != nil {70 return nil, err71 }72 done += rows73 }74 filters, protected, heights, err := referenceAutoFilters(name)75 if err != nil {76 return nil, err77 }78 loadHeights(book, heights)79 notes = append(notes, excelizeNames(x, book)...)80 notes = append(notes, (&xlsxBook{protected: protected}).protectionNote()...)81 active := book.Sheet(clamp(x.GetActiveSheetIndex(), 0, book.Len()-1))82 book.SetActive(active)83 for i, ws := range names {84 if shown, err := x.GetSheetVisible(ws); err == nil && !shown && book.Sheet(i) != active {85 book.HideSheet(book.Sheet(i)) // hidden in Excel, hidden here86 }87 }88 b.s = active89 prog.setRows(done)90 s, notes := b.finish(notes)91 return &Result{Sheet: s, Rows: done, Notes: append(notes, applyFilters(book, filters)...)}, nil92}9394// loadHeights gives each sheet of book its rows' heights.95func loadHeights(book *sheet.Workbook, heights []map[int]int) {96 for i, hs := range heights {97 for row, h := range hs {98 book.Sheet(i).LoadRowHeight(row, h)99 }100 }101}102103// excelizeView freezes s as sheet ws is frozen and loads its notes.104func excelizeView(x *excelize.File, ws string, s *sheet.Sheet) error {105 if p, err := x.GetPanes(ws); err == nil && p.Freeze {106 s.LoadFrozen(p.YSplit, p.XSplit)107 }108 return excelizeNotes(x, ws, s)109}110111// excelizeNotes loads sheet ws's notes (Excel's legacy comments) into s.112func excelizeNotes(x *excelize.File, ws string, s *sheet.Sheet) error {113 comments, err := x.GetComments(ws)114 if err != nil {115 return err116 }117 for _, c := range comments {118 text := c.Text119 for _, run := range c.Paragraph {120 text += run.Text121 }122 if a, ok := sheet.ParseAddr(c.Cell); ok {123 s.LoadNote(a, text)124 }125 }126 return nil127}128129// refWorksheet is the part of a worksheet referenceAutoFilters reads.130type refWorksheet struct {131 Rows []struct {132 R int `xml:"r,attr"`133 Ht string `xml:"ht,attr"`134 Custom string `xml:"customHeight,attr"`135 } `xml:"sheetData>row"`136 Protection *struct {137 Sheet string `xml:"sheet,attr"`138 } `xml:"sheetProtection"`139 AutoFilter *struct {140 Ref string `xml:"ref,attr"`141 Cols []struct {142 ColID int `xml:"colId,attr"`143 Filters *struct {144 Blank string `xml:"blank,attr"`145 Filter []struct {146 Val string `xml:"val,attr"`147 } `xml:"filter"`148 Dates []struct{} `xml:"dateGroupItem"`149 } `xml:"filters"`150 Custom *struct {151 Filter []struct {152 Op string `xml:"operator,attr"`153 Val string `xml:"val,attr"`154 } `xml:"customFilter"`155 } `xml:"customFilters"`156 Top10 *struct{} `xml:"top10"`157 Dynamic *struct{} `xml:"dynamicFilter"`158 Color *struct{} `xml:"colorFilter"`159 Icon *struct{} `xml:"iconFilter"`160 } `xml:"filterColumn"`161 } `xml:"autoFilter"`162}163164// referenceAutoFilters reads each sheet's autoFilter, the names of the165// protected sheets and the heights of rows set by hand (excelize doesn't166// say which those are), by decoding the whole worksheet with167// encoding/xml, as excelize has no API for reading them: the reference168// for the streaming reader's. Only the parts' names come from 012's169// reader.170func referenceAutoFilters(name string) ([]*xlsxAutoFilter, []string, []map[int]int, error) {171 f, err := os.Open(name)172 if err != nil {173 return nil, nil, nil, err174 }175 defer f.Close()176 st, err := f.Stat()177 if err != nil {178 return nil, nil, nil, err179 }180 bk, err := openXLSX(f, st.Size(), defaultXLSXLimits)181 if err != nil {182 return nil, nil, nil, err183 }184 zr, err := zip.NewReader(f, st.Size())185 if err != nil {186 return nil, nil, nil, err187 }188 out := make([]*xlsxAutoFilter, len(bk.sheets))189 heights := make([]map[int]int, len(bk.sheets))190 var protected []string191 for i, info := range bk.sheets {192 if info.part == "" || info.kind != "worksheet" {193 continue194 }195 rc, err := zr.Open(info.part)196 if err != nil {197 return nil, nil, nil, err198 }199 var ws refWorksheet200 err = xml.NewDecoder(rc).Decode(&ws)201 rc.Close()202 if err != nil {203 return nil, nil, nil, err204 }205 out[i] = ws.autoFilter()206 if p := ws.Protection; p != nil && (p.Sheet == "1" || p.Sheet == "true") {207 protected = append(protected, info.name)208 }209 heights[i] = ws.heights()210 }211 return out, protected, heights, nil212}213214// heights are the rows' heights set by hand, in lines, by row from 0.215func (ws refWorksheet) heights() map[int]int {216 out := map[int]int{}217 for _, r := range ws.Rows {218 se := xml.StartElement{Attr: []xml.Attr{{Name: xml.Name{Local: "ht"}, Value: r.Ht}, {Name: xml.Name{Local: "customHeight"}, Value: r.Custom}}}219 if r.Custom == "" {220 se.Attr = se.Attr[:1]221 }222 if h := rowHeight(se); h > 0 && r.R >= 1 {223 out[r.R-1] = h224 }225 }226 return out227}228229func (ws refWorksheet) autoFilter() *xlsxAutoFilter {230 a := ws.AutoFilter231 if a == nil {232 return nil233 }234 af := &xlsxAutoFilter{ref: a.Ref}235 for _, c := range a.Cols {236 fc := xlsxFilterColumn{col: c.ColID}237 if fs := c.Filters; fs != nil {238 fc.filters, fc.blank, fc.dates = true, fs.Blank == "1" || fs.Blank == "true", len(fs.Dates) > 0239 for _, v := range fs.Filter {240 fc.values = append(fc.values, v.Val)241 }242 }243 if c.Custom != nil {244 for _, cf := range c.Custom.Filter {245 op := cf.Op246 if op == "" {247 op = "equal"248 }249 fc.custom = append(fc.custom, xlsxCustomFilter{op: op, val: cf.Val})250 }251 }252 for _, o := range []struct {253 set bool254 what string255 }{{c.Top10 != nil, "top 10"}, {c.Dynamic != nil, "a dynamic filter"}, {c.Color != nil, "by color"}, {c.Icon != nil, "by icon"}} {256 if o.set {257 fc.other = o.what258 }259 }260 af.cols = append(af.cols, fc)261 }262 return af263}264265// excelizeSheet reads one sheet into b.s, reporting rows read, and266// returns how many rows it read.267func excelizeSheet(ctx context.Context, x *excelize.File, b *builder, ws string, styles map[int]xlsxStyle, report func(int)) (int, error) {268 rows, err := x.Rows(ws)269 if err != nil {270 return 0, err271 }272 defer rows.Close()273 dimCols := excelizeDimCols(x, ws)274 row, width := 0, 0275 for ; rows.Next(); row++ {276 if row%128 == 0 {277 if err := ctx.Err(); err != nil {278 return 0, err279 }280 report(row)281 }282 cols, err := rows.Columns(excelize.Options{RawCellValue: true})283 if err != nil {284 return 0, err285 }286 if id := rows.GetRowOpts().StyleID; id != 0 {287 loadRowStyle(b, row, excelizeStyleOf(x, id, styles))288 }289 width = max(width, len(cols))290 for col, raw := range cols {291 a := sheet.Addr{Col: col, Row: row}292 if !b.fits(a) {293 break294 }295 cell, _ := excelize.CoordinatesToCellName(col+1, row+1)296 excelizeCell(x, b, ws, cell, a, raw, styles)297 }298 excelizeBordered(x, b, ws, row, len(cols), dimCols, styles)299 }300 if err := rows.Error(); err != nil {301 return 0, err302 }303 loadColStyles(b, width, func(c int) (xlsxStyle, bool) {304 colName, _ := excelize.ColumnNumberToName(c + 1)305 if id, err := x.GetColStyle(ws, colName); err == nil && id != 0 {306 st := excelizeStyleOf(x, id, styles)307 return st, st != (xlsxStyle{})308 }309 return xlsxStyle{}, false310 })311 for c := range min(max(width, 256), sheet.MaxCols) {312 colName, _ := excelize.ColumnNumberToName(c + 1)313 w, err := x.GetColWidth(ws, colName)314 if err != nil || math.Abs(w-excelDefaultWidth) < 0.01 || math.Abs(w-9.140625) < 0.01 {315 continue316 }317 b.s.LoadColWidth(c, max(int(math.Round(w))+excelPadding, 1))318 }319 excelizeMerges(x, ws, b.s)320 return row, excelizeView(x, ws, b.s)321}322323// excelizeDimCols is the last column of sheet ws's dimension.324func excelizeDimCols(x *excelize.File, ws string) int {325 dim, err := x.GetSheetDimension(ws)326 if err != nil {327 return 0328 }329 _, to, _ := strings.Cut(dim, ":")330 cols, _, _ := excelize.CellNameToCoordinates(cmp.Or(to, dim))331 return cols332}333334// excelizeMerges merges the cells sheet ws merges.335func excelizeMerges(x *excelize.File, ws string, s *sheet.Sheet) {336 merges, err := x.GetMergeCells(ws, true)337 if err != nil {338 return339 }340 for _, mc := range merges {341 if r, ok := sheet.ParseRange(mc.GetStartAxis() + ":" + mc.GetEndAxis()); ok {342 s.LoadMerge(r)343 }344 }345}346347// excelizeNames defines the workbook's named ranges that are a range on348// one sheet, such as Sales = Q3!$B$2:$B$20, and notes the others (sheet349// scoped names, constants, formulas).350func excelizeNames(x *excelize.File, book *sheet.Workbook) []string {351 skipped := 0352 example := ""353 for _, dn := range x.GetDefinedName() {354 if strings.HasPrefix(dn.Name, "_xlnm.") {355 continue // print areas and the like356 }357 ref := strings.TrimPrefix(dn.RefersTo, "=")358 ws, rest := sheet.SplitSheet(ref)359 r, ok := sheet.ParseRange(rest)360 s := book.Lookup(ws)361 if dn.Scope != "" && dn.Scope != "Workbook" || !ok || s == nil || book.DefineName(dn.Name, s, r) != nil {362 skipped++363 if example == "" {364 example = dn.Name365 }366 }367 }368 if skipped > 0 {369 return []string{fmt.Sprintf("%s left out, e.g. %s", count(skipped, "named range", "named ranges"), example)}370 }371 return nil372}373374// excelizeBorderStyles are Excel's border styles by excelize's index.375var excelizeBorderStyles = []string{"none", "thin", "medium", "dashed", "dotted", "thick", "double", "hair",376 "mediumDashed", "dashDot", "mediumDashDot", "dashDotDot", "mediumDashDotDot", "slantDashDot"}377378func excelizeStyleOf(x *excelize.File, id int, cache map[int]xlsxStyle) xlsxStyle {379 if st, ok := cache[id]; ok {380 return st381 }382 var out xlsxStyle383 if st, err := x.GetStyle(id); err == nil && st != nil {384 code := ""385 if st.CustomNumFmt != nil {386 code = *st.CustomNumFmt387 }388 out.format = formatOf(st.NumFmt, code)389 if f := st.Font; f != nil {390 out.style.Bold, out.style.Italic, out.style.Strikethrough = f.Bold, f.Italic, f.Strike391 out.style.Underline = f.Underline != "" && f.Underline != "none"392 }393 for _, br := range st.Border {394 l := sheet.LineNone395 if br.Style > 0 && br.Style < len(excelizeBorderStyles) {396 l = borderLine(excelizeBorderStyles[br.Style])397 }398 if e, ok := xlsxEdges[br.Type]; ok {399 out.style.Borders = out.style.Borders.With(e, l)400 }401 }402 if al := st.Alignment; al != nil {403 if al.WrapText {404 out.style.Wrap = sheet.WrapOn405 }406 switch al.Horizontal {407 case "left":408 out.style.Align = sheet.AlignLeft409 case "center", "centerContinuous":410 out.style.Align = sheet.AlignCenter411 case "right":412 out.style.Align = sheet.AlignRight413 }414 out.style.VAlign = excelVAligns[al.Vertical]415 }416 }417 cache[id] = out418 return out419}420421// excelizeBordered stores the blank cells of row from column from (from422// 0) up to the last one within dimCols that draws borders, which 012's423// reader keeps though no value follows them.424func excelizeBordered(x *excelize.File, b *builder, ws string, row, from, dimCols int, styles map[int]xlsxStyle) {425 last := -1426 for col := from; col < min(dimCols, sheet.MaxCols); col++ {427 cell, _ := excelize.CoordinatesToCellName(col+1, row+1)428 if id, err := x.GetCellStyle(ws, cell); err == nil && id != 0 && !excelizeStyleOf(x, id, styles).style.Borders.IsZero() {429 last = col430 }431 }432 for col := from; col <= last; col++ {433 cell, _ := excelize.CoordinatesToCellName(col+1, row+1)434 excelizeCell(x, b, ws, cell, sheet.Addr{Col: col, Row: row}, "", styles)435 }436}437438func excelizeCell(x *excelize.File, b *builder, ws, cell string, a sheet.Addr, raw string, styles map[int]xlsxStyle) {439 var st xlsxStyle440 if id, err := x.GetCellStyle(ws, cell); err == nil && id != 0 {441 st = excelizeStyleOf(x, id, styles)442 }443 typ, _ := x.GetCellType(ws, cell)444 keep := func() {445 switch typ {446 case excelize.CellTypeBool:447 b.boolean(a, raw == "1" || strings.EqualFold(raw, "TRUE"), st.style)448 return449 case excelize.CellTypeSharedString, excelize.CellTypeInlineString, excelize.CellTypeFormula, excelize.CellTypeError:450 b.text(a, raw, st.format, st.style)451 return452 }453 if v, err := strconv.ParseFloat(raw, 64); err == nil {454 b.number(a, fromExcelSerial(v, st.format), st.format, st.style)455 return456 }457 b.text(a, raw, st.format, st.style)458 }459 if f, err := x.GetCellFormula(ws, cell); err == nil && f != "" {460 b.formula(a, fromExcelFormula(f), st.format, st.style, keep)461 return462 }463 if raw == "" {464 b.put(a, "", st.format, st.style)465 return466 }467 keep()468}