1 // Copyright 2016 - 2023 The excelize Authors. All rights reserved. Use of
2 // this source code is governed by a BSD-style license that can be found in
5 // Package excelize providing a set of functions that allow you to write to and
6 // read from XLAM / XLSM / XLSX / XLTM / XLTX files. Supports reading and
7 // writing spreadsheet documents generated by Microsoft Excelâ„¢ 2007 and later.
8 // Supports complex components by high compatibility, and provided streaming
9 // API for generating or reading data from a worksheet with huge amounts of
10 // data. This library needs Go version 1.16 or later.
22 "github.com/mohae/deepcopy"
25 // GetRows return all the rows in a sheet by given worksheet name, returned as
26 // a two-dimensional array, where the value of the cell is converted to the
27 // string type. If the cell format can be applied to the value of the cell,
28 // the applied value will be used, otherwise the original value will be used.
29 // GetRows fetched the rows with value or formula cells, the continually blank
30 // cells in the tail of each row will be skipped, so the length of each row
31 // may be inconsistent.
33 // For example, get and traverse the value of all cells by rows on a worksheet
36 // rows, err := f.GetRows("Sheet1")
41 // for _, row := range rows {
42 // for _, colCell := range row {
43 // fmt.Print(colCell, "\t")
47 func (f *File) GetRows(sheet string, opts ...Options) ([][]string, error) {
48 if _, err := f.workSheetReader(sheet); err != nil {
51 rows, _ := f.Rows(sheet)
52 results, cur, max := make([][]string, 0, 64), 0, 0
55 row, err := rows.Columns(opts...)
59 results = append(results, row)
64 return results[:max], rows.Close()
67 // Rows defines an iterator to a sheet.
71 needClose, rawCellValue bool
78 curRowOpts, seekRowOpts RowOpts
81 // Next will return true if it finds the next row element.
82 func (rows *Rows) Next() bool {
84 if rows.curRow >= rows.seekRow {
85 rows.curRowOpts = rows.seekRowOpts
89 token, _ := rows.decoder.Token()
93 switch xmlElement := token.(type) {
94 case xml.StartElement:
95 if xmlElement.Name.Local == "row" {
97 if rowNum, _ := attrValToInt("r", xmlElement.Attr); rowNum != 0 {
101 rows.curRowOpts = extractRowOpts(xmlElement.Attr)
105 if xmlElement.Name.Local == "sheetData" {
112 // GetRowOpts will return the RowOpts of the current row.
113 func (rows *Rows) GetRowOpts() RowOpts {
114 return rows.curRowOpts
117 // Error will return the error when the error occurs.
118 func (rows *Rows) Error() error {
122 // Close closes the open worksheet XML file in the system temporary
124 func (rows *Rows) Close() error {
125 if rows.tempFile != nil {
126 return rows.tempFile.Close()
131 // Columns return the current row's column values. This fetches the worksheet
132 // data as a stream, returns each cell in a row as is, and will not skip empty
133 // rows in the tail of the worksheet.
134 func (rows *Rows) Columns(opts ...Options) ([]string, error) {
135 if rows.curRow > rows.seekRow {
138 var rowIterator rowXMLIterator
140 rows.rawCellValue = getOptions(opts...).RawCellValue
141 if rows.sst, rowIterator.err = rows.f.sharedStringsReader(); rowIterator.err != nil {
142 return rowIterator.cells, rowIterator.err
145 if rows.token != nil {
147 } else if token, _ = rows.decoder.Token(); token == nil {
150 switch xmlElement := token.(type) {
151 case xml.StartElement:
152 rowIterator.inElement = xmlElement.Name.Local
153 if rowIterator.inElement == "row" {
155 if rowNum, rowIterator.err = attrValToInt("r", xmlElement.Attr); rowNum != 0 {
157 } else if rows.token == nil {
161 rows.seekRowOpts = extractRowOpts(xmlElement.Attr)
162 if rows.curRow > rows.seekRow {
164 return rowIterator.cells, rowIterator.err
167 if rows.rowXMLHandler(&rowIterator, &xmlElement, rows.rawCellValue); rowIterator.err != nil {
169 return rowIterator.cells, rowIterator.err
173 if xmlElement.Name.Local == "sheetData" {
174 return rowIterator.cells, rowIterator.err
178 return rowIterator.cells, rowIterator.err
181 // extractRowOpts extract row element attributes.
182 func extractRowOpts(attrs []xml.Attr) RowOpts {
183 rowOpts := RowOpts{Height: defaultRowHeight}
184 if styleID, err := attrValToInt("s", attrs); err == nil && styleID > 0 && styleID < MaxCellStyles {
185 rowOpts.StyleID = styleID
187 if hidden, err := attrValToBool("hidden", attrs); err == nil {
188 rowOpts.Hidden = hidden
190 if height, err := attrValToFloat("ht", attrs); err == nil {
191 rowOpts.Height = height
196 // appendSpace append blank characters to slice by given length and source slice.
197 func appendSpace(l int, s []string) []string {
198 for i := 1; i < l; i++ {
204 // rowXMLIterator defined runtime use field for the worksheet row SAX parser.
205 type rowXMLIterator struct {
212 // rowXMLHandler parse the row XML element of the worksheet.
213 func (rows *Rows) rowXMLHandler(rowIterator *rowXMLIterator, xmlElement *xml.StartElement, raw bool) {
214 if rowIterator.inElement == "c" {
215 rowIterator.cellCol++
217 _ = rows.decoder.DecodeElement(&colCell, xmlElement)
219 if rowIterator.cellCol, _, rowIterator.err = CellNameToCoordinates(colCell.R); rowIterator.err != nil {
223 blank := rowIterator.cellCol - len(rowIterator.cells)
224 if val, _ := colCell.getValueFrom(rows.f, rows.sst, raw); val != "" || colCell.F != nil {
225 rowIterator.cells = append(appendSpace(blank, rowIterator.cells), val)
230 // Rows returns a rows iterator, used for streaming reading data for a
231 // worksheet with a large data. This function is concurrency safe. For
234 // rows, err := f.Rows("Sheet1")
240 // row, err := rows.Columns()
244 // for _, colCell := range row {
245 // fmt.Print(colCell, "\t")
249 // if err = rows.Close(); err != nil {
252 func (f *File) Rows(sheet string) (*Rows, error) {
253 if err := checkSheetName(sheet); err != nil {
256 name, ok := f.getSheetXMLPath(sheet)
258 return nil, ErrSheetNotExist{sheet}
260 if worksheet, ok := f.Sheet.Load(name); ok && worksheet != nil {
261 ws := worksheet.(*xlsxWorksheet)
265 output, _ := xml.Marshal(ws)
266 f.saveFileList(name, f.replaceNameSpaceBytes(name, output))
269 rows := Rows{f: f, sheet: name}
270 rows.needClose, rows.decoder, rows.tempFile, err = f.xmlDecoder(name)
274 // getFromStringItem build shared string item offset list from system temporary
275 // file at one time, and return value by given to string index.
276 func (f *File) getFromStringItem(index int) string {
277 if f.sharedStringTemp != nil {
278 if len(f.sharedStringItem) <= index {
279 return strconv.Itoa(index)
281 offsetRange := f.sharedStringItem[index]
282 buf := make([]byte, offsetRange[1]-offsetRange[0])
283 if _, err := f.sharedStringTemp.ReadAt(buf, int64(offsetRange[0])); err != nil {
284 return strconv.Itoa(index)
288 needClose, decoder, tempFile, err := f.xmlDecoder(defaultXMLPathSharedStrings)
289 if needClose && err == nil {
291 err = tempFile.Close()
294 f.sharedStringItem = [][]uint{}
295 f.sharedStringTemp, _ = os.CreateTemp(os.TempDir(), "excelize-")
296 f.tempFiles.Store(defaultTempFileSST, f.sharedStringTemp.Name())
302 token, _ := decoder.Token()
306 switch xmlElement := token.(type) {
307 case xml.StartElement:
308 inElement = xmlElement.Name.Local
309 if inElement == "si" {
311 _ = decoder.DecodeElement(&si, &xmlElement)
314 n, _ := f.sharedStringTemp.WriteString(si.String())
316 f.sharedStringItem = append(f.sharedStringItem, []uint{startIdx, offset})
321 return f.getFromStringItem(index)
324 // xmlDecoder creates XML decoder by given path in the zip from memory data
325 // or system temporary file.
326 func (f *File) xmlDecoder(name string) (bool, *xml.Decoder, *os.File, error) {
332 if content = f.readXML(name); len(content) > 0 {
333 return false, f.xmlNewDecoder(bytes.NewReader(content)), tempFile, err
335 tempFile, err = f.readTemp(name)
336 return true, f.xmlNewDecoder(tempFile), tempFile, err
339 // SetRowHeight provides a function to set the height of a single row. For
340 // example, set the height of the first row in Sheet1:
342 // err := f.SetRowHeight("Sheet1", 1, 50)
343 func (f *File) SetRowHeight(sheet string, row int, height float64) error {
345 return newInvalidRowNumberError(row)
347 if height > MaxRowHeight {
348 return ErrMaxRowHeight
350 ws, err := f.workSheetReader(sheet)
355 ws.prepareSheetXML(0, row)
358 ws.SheetData.Row[rowIdx].Ht = float64Ptr(height)
359 ws.SheetData.Row[rowIdx].CustomHeight = true
363 // getRowHeight provides a function to get row height in pixels by given sheet
364 // name and row number.
365 func (f *File) getRowHeight(sheet string, row int) int {
366 ws, _ := f.workSheetReader(sheet)
369 for i := range ws.SheetData.Row {
370 v := &ws.SheetData.Row[i]
371 if v.R != nil && *v.R == row && v.Ht != nil {
372 return int(convertRowHeightToPixels(*v.Ht))
375 if ws.SheetFormatPr != nil && ws.SheetFormatPr.DefaultRowHeight > 0 {
376 return int(convertRowHeightToPixels(ws.SheetFormatPr.DefaultRowHeight))
378 // Optimization for when the row heights haven't changed.
379 return int(defaultRowHeightPixels)
382 // GetRowHeight provides a function to get row height by given worksheet name
383 // and row number. For example, get the height of the first row in Sheet1:
385 // height, err := f.GetRowHeight("Sheet1", 1)
386 func (f *File) GetRowHeight(sheet string, row int) (float64, error) {
388 return defaultRowHeight, newInvalidRowNumberError(row)
390 ht := defaultRowHeight
391 ws, err := f.workSheetReader(sheet)
395 if ws.SheetFormatPr != nil && ws.SheetFormatPr.CustomHeight {
396 ht = ws.SheetFormatPr.DefaultRowHeight
398 if row > len(ws.SheetData.Row) {
399 return ht, nil // it will be better to use 0, but we take care with BC
401 for _, v := range ws.SheetData.Row {
402 if v.R != nil && *v.R == row && v.Ht != nil {
406 // Optimization for when the row heights haven't changed.
410 // sharedStringsReader provides a function to get the pointer to the structure
411 // after deserialization of xl/sharedStrings.xml.
412 func (f *File) sharedStringsReader() (*xlsxSST, error) {
416 relPath := f.getWorkbookRelsPath()
417 if f.SharedStrings == nil {
418 var sharedStrings xlsxSST
419 ss := f.readXML(defaultXMLPathSharedStrings)
420 if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(ss))).
421 Decode(&sharedStrings); err != nil && err != io.EOF {
422 return f.SharedStrings, err
424 if sharedStrings.Count == 0 {
425 sharedStrings.Count = len(sharedStrings.SI)
427 if sharedStrings.UniqueCount == 0 {
428 sharedStrings.UniqueCount = sharedStrings.Count
430 f.SharedStrings = &sharedStrings
431 for i := range sharedStrings.SI {
432 if sharedStrings.SI[i].T != nil {
433 f.sharedStringsMap[sharedStrings.SI[i].T.Val] = i
436 if err = f.addContentTypePart(0, "sharedStrings"); err != nil {
437 return f.SharedStrings, err
439 rels, err := f.relsReader(relPath)
441 return f.SharedStrings, err
443 for _, rel := range rels.Relationships {
444 if rel.Target == "/xl/sharedStrings.xml" {
445 return f.SharedStrings, nil
448 // Update workbook.xml.rels
449 f.addRels(relPath, SourceRelationshipSharedStrings, "/xl/sharedStrings.xml", "")
452 return f.SharedStrings, nil
455 // SetRowVisible provides a function to set visible of a single row by given
456 // worksheet name and Excel row number. For example, hide row 2 in Sheet1:
458 // err := f.SetRowVisible("Sheet1", 2, false)
459 func (f *File) SetRowVisible(sheet string, row int, visible bool) error {
461 return newInvalidRowNumberError(row)
464 ws, err := f.workSheetReader(sheet)
468 ws.prepareSheetXML(0, row)
469 ws.SheetData.Row[row-1].Hidden = !visible
473 // GetRowVisible provides a function to get visible of a single row by given
474 // worksheet name and Excel row number. For example, get visible state of row
477 // visible, err := f.GetRowVisible("Sheet1", 2)
478 func (f *File) GetRowVisible(sheet string, row int) (bool, error) {
480 return false, newInvalidRowNumberError(row)
483 ws, err := f.workSheetReader(sheet)
487 if row > len(ws.SheetData.Row) {
490 return !ws.SheetData.Row[row-1].Hidden, nil
493 // SetRowOutlineLevel provides a function to set outline level number of a
494 // single row by given worksheet name and Excel row number. The value of
495 // parameter 'level' is 1-7. For example, outline row 2 in Sheet1 to level 1:
497 // err := f.SetRowOutlineLevel("Sheet1", 2, 1)
498 func (f *File) SetRowOutlineLevel(sheet string, row int, level uint8) error {
500 return newInvalidRowNumberError(row)
502 if level > 7 || level < 1 {
503 return ErrOutlineLevel
505 ws, err := f.workSheetReader(sheet)
509 ws.prepareSheetXML(0, row)
510 ws.SheetData.Row[row-1].OutlineLevel = level
514 // GetRowOutlineLevel provides a function to get outline level number of a
515 // single row by given worksheet name and Excel row number. For example, get
516 // outline number of row 2 in Sheet1:
518 // level, err := f.GetRowOutlineLevel("Sheet1", 2)
519 func (f *File) GetRowOutlineLevel(sheet string, row int) (uint8, error) {
521 return 0, newInvalidRowNumberError(row)
523 ws, err := f.workSheetReader(sheet)
527 if row > len(ws.SheetData.Row) {
530 return ws.SheetData.Row[row-1].OutlineLevel, nil
533 // RemoveRow provides a function to remove single row by given worksheet name
534 // and Excel row number. For example, remove row 3 in Sheet1:
536 // err := f.RemoveRow("Sheet1", 3)
538 // Use this method with caution, which will affect changes in references such
539 // as formulas, charts, and so on. If there is any referenced value of the
540 // worksheet, it will cause a file error when you open it. The excelize only
541 // partially updates these references currently.
542 func (f *File) RemoveRow(sheet string, row int) error {
544 return newInvalidRowNumberError(row)
547 ws, err := f.workSheetReader(sheet)
551 if row > len(ws.SheetData.Row) {
552 return f.adjustHelper(sheet, rows, row, -1)
555 for rowIdx := 0; rowIdx < len(ws.SheetData.Row); rowIdx++ {
556 v := &ws.SheetData.Row[rowIdx]
557 if v.R != nil && *v.R != row {
558 ws.SheetData.Row[keep] = *v
562 ws.SheetData.Row = ws.SheetData.Row[:keep]
563 return f.adjustHelper(sheet, rows, row, -1)
566 // InsertRows provides a function to insert new rows after the given Excel row
567 // number starting from 1 and number of rows. For example, create two rows
568 // before row 3 in Sheet1:
570 // err := f.InsertRows("Sheet1", 3, 2)
572 // Use this method with caution, which will affect changes in references such
573 // as formulas, charts, and so on. If there is any referenced value of the
574 // worksheet, it will cause a file error when you open it. The excelize only
575 // partially updates these references currently.
576 func (f *File) InsertRows(sheet string, row, n int) error {
578 return newInvalidRowNumberError(row)
580 if row >= TotalRows || n >= TotalRows {
584 return ErrParameterInvalid
586 return f.adjustHelper(sheet, rows, row, n)
589 // DuplicateRow inserts a copy of specified row (by its Excel row number) below
591 // err := f.DuplicateRow("Sheet1", 2)
593 // Use this method with caution, which will affect changes in references such
594 // as formulas, charts, and so on. If there is any referenced value of the
595 // worksheet, it will cause a file error when you open it. The excelize only
596 // partially updates these references currently.
597 func (f *File) DuplicateRow(sheet string, row int) error {
598 return f.DuplicateRowTo(sheet, row, row+1)
601 // DuplicateRowTo inserts a copy of specified row by it Excel number
602 // to specified row position moving down exists rows after target position
604 // err := f.DuplicateRowTo("Sheet1", 2, 7)
606 // Use this method with caution, which will affect changes in references such
607 // as formulas, charts, and so on. If there is any referenced value of the
608 // worksheet, it will cause a file error when you open it. The excelize only
609 // partially updates these references currently.
610 func (f *File) DuplicateRowTo(sheet string, row, row2 int) error {
612 return newInvalidRowNumberError(row)
615 ws, err := f.workSheetReader(sheet)
620 if row2 < 1 || row == row2 {
627 for i, r := range ws.SheetData.Row {
629 rowCopy = deepcopy.Copy(ws.SheetData.Row[i]).(xlsxRow)
635 if err := f.adjustHelper(sheet, rows, row2, 1); err != nil {
644 for i, r := range ws.SheetData.Row {
650 if idx2 == -1 && len(ws.SheetData.Row) >= row2 {
654 rowCopy.C = append(make([]xlsxC, 0, len(rowCopy.C)), rowCopy.C...)
655 rowCopy.adjustSingleRowDimensions(row2 - row)
656 _ = f.adjustSingleRowFormulas(sheet, sheet, &rowCopy, row, row2-row, true)
659 ws.SheetData.Row[idx2] = rowCopy
661 ws.SheetData.Row = append(ws.SheetData.Row, rowCopy)
663 return f.duplicateMergeCells(sheet, ws, row, row2)
666 // duplicateMergeCells merge cells in the destination row if there are single
667 // row merged cells in the copied row.
668 func (f *File) duplicateMergeCells(sheet string, ws *xlsxWorksheet, row, row2 int) error {
669 if ws.MergeCells == nil {
675 for _, rng := range ws.MergeCells.Cells {
676 coordinates, err := rangeRefToCoordinates(rng.Ref)
680 if coordinates[1] < row2 && row2 < coordinates[3] {
684 for i := 0; i < len(ws.MergeCells.Cells); i++ {
685 mergedCells := ws.MergeCells.Cells[i]
686 coordinates, _ := rangeRefToCoordinates(mergedCells.Ref)
687 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
688 if y1 == y2 && y1 == row {
689 from, _ := CoordinatesToCellName(x1, row2)
690 to, _ := CoordinatesToCellName(x2, row2)
691 if err := f.MergeCell(sheet, from, to); err != nil {
699 // checkRow provides a function to check and fill each column element for all
700 // rows and make that is continuous in a worksheet of XML. For example:
703 // <c r="A15" s="2" />
704 // <c r="B15" s="2" />
705 // <c r="F15" s="1" />
706 // <c r="G15" s="1" />
709 // in this case, we should to change it to
712 // <c r="A15" s="2" />
713 // <c r="B15" s="2" />
714 // <c r="C15" s="2" />
715 // <c r="D15" s="2" />
716 // <c r="E15" s="2" />
717 // <c r="F15" s="1" />
718 // <c r="G15" s="1" />
721 // Notice: this method could be very slow for large spreadsheets (more than
722 // 3000 rows one sheet).
723 func (ws *xlsxWorksheet) checkRow() error {
724 for rowIdx := range ws.SheetData.Row {
725 rowData := &ws.SheetData.Row[rowIdx]
727 colCount := len(rowData.C)
731 // check and fill the cell without r attribute in a row element
733 for idx, cell := range rowData.C {
736 lastR, _, err := CellNameToCoordinates(cell.R)
745 rowData.C[idx].R, _ = CoordinatesToCellName(rCount, rowIdx+1)
747 lastCol, _, err := CellNameToCoordinates(rowData.C[colCount-1].R)
752 if colCount < lastCol {
753 sourceList := rowData.C
754 targetList := make([]xlsxC, 0, lastCol)
756 rowData.C = ws.SheetData.Row[rowIdx].C[:0]
758 for colIdx := 0; colIdx < lastCol; colIdx++ {
759 cellName, err := CoordinatesToCellName(colIdx+1, rowIdx+1)
763 targetList = append(targetList, xlsxC{R: cellName})
766 rowData.C = targetList
768 for colIdx := range sourceList {
769 colData := &sourceList[colIdx]
770 colNum, _, err := CellNameToCoordinates(colData.R)
774 ws.SheetData.Row[rowIdx].C[colNum-1] = *colData
781 // hasAttr determine if row non-default attributes.
782 func (r *xlsxRow) hasAttr() bool {
783 return r.Spans != "" || r.S != 0 || r.CustomFormat || r.Ht != nil ||
784 r.Hidden || r.CustomHeight || r.OutlineLevel != 0 || r.Collapsed ||
785 r.ThickTop || r.ThickBot || r.Ph
788 // SetRowStyle provides a function to set the style of rows by given worksheet
789 // name, row range, and style ID. Note that this will overwrite the existing
790 // styles for the rows, it won't append or merge style with existing styles.
792 // For example set style of row 1 on Sheet1:
794 // err := f.SetRowStyle("Sheet1", 1, 1, styleID)
796 // Set style of rows 1 to 10 on Sheet1:
798 // err := f.SetRowStyle("Sheet1", 1, 10, styleID)
799 func (f *File) SetRowStyle(sheet string, start, end, styleID int) error {
801 start, end = end, start
804 return newInvalidRowNumberError(start)
809 s, err := f.stylesReader()
815 if styleID < 0 || s.CellXfs == nil || len(s.CellXfs.Xf) <= styleID {
816 return newInvalidStyleID(styleID)
818 ws, err := f.workSheetReader(sheet)
822 ws.prepareSheetXML(0, end)
823 for row := start - 1; row < end; row++ {
824 ws.SheetData.Row[row].S = styleID
825 ws.SheetData.Row[row].CustomFormat = true
826 for i := range ws.SheetData.Row[row].C {
827 if _, rowNum, err := CellNameToCoordinates(ws.SheetData.Row[row].C[i].R); err == nil && rowNum-1 == row {
828 ws.SheetData.Row[row].C[i].S = styleID
835 // convertRowHeightToPixels provides a function to convert the height of a
836 // cell from user's units to pixels. If the height hasn't been set by the user
837 // we use the default value. If the row is hidden it has a value of zero.
838 func convertRowHeightToPixels(height float64) float64 {
842 return math.Ceil(4.0 / 3.4 * height)