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.
25 type adjustDirection bool
28 columns adjustDirection = false
29 rows adjustDirection = true
32 // adjustHelper provides a function to adjust rows and columns dimensions,
33 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
36 // sheet: Worksheet name that we're editing
37 // column: Index number of the column we're inserting/deleting before
38 // row: Index number of the row we're inserting/deleting before
39 // offset: Number of rows/column to insert/delete negative values indicate deletion
41 // TODO: adjustPageBreaks, adjustComments, adjustDataValidations, adjustProtectedCells
42 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
43 ws, err := f.workSheetReader(sheet)
47 sheetID := f.getSheetID(sheet)
49 err = f.adjustRowDimensions(sheet, ws, num, offset)
51 err = f.adjustColDimensions(sheet, ws, num, offset)
56 f.adjustHyperlinks(ws, sheet, dir, num, offset)
59 f.adjustTable(ws, sheet, dir, num, offset)
60 if err = f.adjustMergeCells(ws, dir, num, offset); err != nil {
63 if err = f.adjustAutoFilter(ws, dir, num, offset); err != nil {
66 if err = f.adjustCalcChain(dir, num, offset, sheetID); err != nil {
69 if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
76 // adjustCols provides a function to update column style when inserting or
78 func (f *File) adjustCols(ws *xlsxWorksheet, col, offset int) error {
82 for i := 0; i < len(ws.Cols.Col); i++ {
84 if ws.Cols.Col[i].Max+1 == col {
85 ws.Cols.Col[i].Max += offset
88 if ws.Cols.Col[i].Min >= col {
89 ws.Cols.Col[i].Min += offset
90 ws.Cols.Col[i].Max += offset
93 if ws.Cols.Col[i].Min < col && ws.Cols.Col[i].Max >= col {
94 ws.Cols.Col[i].Max += offset
98 if ws.Cols.Col[i].Min == col && ws.Cols.Col[i].Max == col {
99 if len(ws.Cols.Col) > 1 {
100 ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
107 if ws.Cols.Col[i].Min > col {
108 ws.Cols.Col[i].Min += offset
109 ws.Cols.Col[i].Max += offset
112 if ws.Cols.Col[i].Min <= col && ws.Cols.Col[i].Max >= col {
113 ws.Cols.Col[i].Max += offset
120 // adjustColDimensions provides a function to update column dimensions when
121 // inserting or deleting rows or columns.
122 func (f *File) adjustColDimensions(sheet string, ws *xlsxWorksheet, col, offset int) error {
123 for rowIdx := range ws.SheetData.Row {
124 for _, v := range ws.SheetData.Row[rowIdx].C {
125 if cellCol, _, _ := CellNameToCoordinates(v.R); col <= cellCol {
126 if newCol := cellCol + offset; newCol > 0 && newCol > MaxColumns {
127 return ErrColumnNumber
132 for _, sheetN := range f.GetSheetList() {
133 worksheet, err := f.workSheetReader(sheetN)
135 if err.Error() == newNotWorksheetError(sheetN).Error() {
140 for rowIdx := range worksheet.SheetData.Row {
141 for colIdx, v := range worksheet.SheetData.Row[rowIdx].C {
142 if cellCol, cellRow, _ := CellNameToCoordinates(v.R); sheetN == sheet && col <= cellCol {
143 if newCol := cellCol + offset; newCol > 0 {
144 worksheet.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
147 if err := f.adjustFormula(sheet, sheetN, worksheet.SheetData.Row[rowIdx].C[colIdx].F, columns, col, offset, false); err != nil {
153 return f.adjustCols(ws, col, offset)
156 // adjustRowDimensions provides a function to update row dimensions when
157 // inserting or deleting rows or columns.
158 func (f *File) adjustRowDimensions(sheet string, ws *xlsxWorksheet, row, offset int) error {
159 for _, sheetN := range f.GetSheetList() {
163 worksheet, err := f.workSheetReader(sheetN)
165 if err.Error() == newNotWorksheetError(sheetN).Error() {
170 numOfRows := len(worksheet.SheetData.Row)
171 for i := 0; i < numOfRows; i++ {
172 r := &worksheet.SheetData.Row[i]
173 if err = f.adjustSingleRowFormulas(sheet, sheetN, r, row, offset, false); err != nil {
178 totalRows := len(ws.SheetData.Row)
182 lastRow := &ws.SheetData.Row[totalRows-1]
183 if newRow := lastRow.R + offset; lastRow.R >= row && newRow > 0 && newRow > TotalRows {
186 numOfRows := len(ws.SheetData.Row)
187 for i := 0; i < numOfRows; i++ {
188 r := &ws.SheetData.Row[i]
189 if newRow := r.R + offset; r.R >= row && newRow > 0 {
190 r.adjustSingleRowDimensions(offset)
192 if err := f.adjustSingleRowFormulas(sheet, sheet, r, row, offset, false); err != nil {
199 // adjustSingleRowDimensions provides a function to adjust single row dimensions.
200 func (r *xlsxRow) adjustSingleRowDimensions(offset int) {
202 for i, col := range r.C {
203 colName, _, _ := SplitCellName(col.R)
204 r.C[i].R, _ = JoinCellName(colName, r.R)
208 // adjustSingleRowFormulas provides a function to adjust single row formulas.
209 func (f *File) adjustSingleRowFormulas(sheet, sheetN string, r *xlsxRow, num, offset int, si bool) error {
210 for _, col := range r.C {
211 if err := f.adjustFormula(sheet, sheetN, col.F, rows, num, offset, si); err != nil {
218 // adjustCellRef provides a function to adjust cell reference.
219 func (f *File) adjustCellRef(ref string, dir adjustDirection, num, offset int) (string, error) {
220 if !strings.Contains(ref, ":") {
223 coordinates, err := rangeRefToCoordinates(ref)
228 if coordinates[0] >= num {
229 coordinates[0] += offset
231 if coordinates[2] >= num {
232 coordinates[2] += offset
235 if coordinates[1] >= num {
236 coordinates[1] += offset
238 if coordinates[3] >= num {
239 coordinates[3] += offset
242 return f.coordinatesToRangeRef(coordinates)
245 // adjustFormula provides a function to adjust formula reference and shared
246 // formula reference.
247 func (f *File) adjustFormula(sheet, sheetN string, formula *xlsxF, dir adjustDirection, num, offset int, si bool) error {
252 if formula.Ref != "" && sheet == sheetN {
253 if formula.Ref, err = f.adjustCellRef(formula.Ref, dir, num, offset); err != nil {
256 if si && formula.Si != nil {
257 formula.Si = intPtr(*formula.Si + 1)
260 if formula.Content != "" {
261 if formula.Content, err = f.adjustFormulaRef(sheet, sheetN, formula.Content, dir, num, offset); err != nil {
268 // isFunctionStop provides a function to check if token is a function stop.
269 func isFunctionStop(token efp.Token) bool {
270 return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStop
273 // isFunctionStart provides a function to check if token is a function start.
274 func isFunctionStart(token efp.Token) bool {
275 return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStart
278 // escapeSheetName enclose sheet name in single quotation marks if the giving
279 // worksheet name includes spaces or non-alphabetical characters.
280 func escapeSheetName(name string) string {
281 if strings.IndexFunc(name, func(r rune) bool {
282 return !unicode.IsLetter(r) && !unicode.IsNumber(r)
284 return string(efp.QuoteSingle) + name + string(efp.QuoteSingle)
289 // adjustFormulaColumnName adjust column name in the formula reference.
290 func adjustFormulaColumnName(name string, dir adjustDirection, num, offset int) (string, error) {
291 col, err := ColumnNameToNumber(name)
295 if dir == columns && col >= num {
297 return ColumnNumberToName(col)
302 // adjustFormulaRowNumber adjust row number in the formula reference.
303 func adjustFormulaRowNumber(name string, dir adjustDirection, num, offset int) (string, error) {
304 row, _ := strconv.Atoi(name)
305 if dir == rows && row >= num {
308 return name, ErrMaxRows
310 return strconv.Itoa(row), nil
315 // adjustFormulaOperandRef adjust cell reference in the operand tokens for the formula.
316 func adjustFormulaOperandRef(row, col, operand string, dir adjustDirection, num int, offset int) (string, string, string, error) {
318 name, err := adjustFormulaColumnName(col, dir, num, offset)
320 return row, col, operand, err
326 name, err := adjustFormulaRowNumber(row, dir, num, offset)
328 return row, col, operand, err
333 return row, col, operand, nil
336 // adjustFormulaOperand adjust range operand tokens for the formula.
337 func (f *File) adjustFormulaOperand(sheet, sheetN string, token efp.Token, dir adjustDirection, num int, offset int) (string, error) {
340 sheetName, col, row, operand string
342 tokens = strings.Split(token.TValue, "!")
344 if len(tokens) == 2 { // have a worksheet
345 sheetName, cell = tokens[0], tokens[1]
346 operand = escapeSheetName(sheetName) + "!"
348 if sheet != sheetN && sheet != sheetName {
349 return operand + cell, err
351 for _, r := range cell {
352 if ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z') {
356 if '0' <= r && r <= '9' {
359 name, err := adjustFormulaColumnName(col, dir, num, offset)
368 if row, col, operand, err = adjustFormulaOperandRef(row, col, operand, dir, num, offset); err != nil {
373 _, _, operand, err = adjustFormulaOperandRef(row, col, operand, dir, num, offset)
377 // adjustFormulaRef returns adjusted formula by giving adjusting direction and
378 // the base number of column or row, and offset.
379 func (f *File) adjustFormulaRef(sheet, sheetN, formula string, dir adjustDirection, num, offset int) (string, error) {
382 definedNames []string
383 ps = efp.ExcelParser()
385 for _, definedName := range f.GetDefinedName() {
386 if definedName.Scope == "Workbook" || definedName.Scope == sheet {
387 definedNames = append(definedNames, definedName.Name)
390 for _, token := range ps.Parse(formula) {
391 if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeRange {
392 if inStrSlice(definedNames, token.TValue, true) != -1 {
396 if strings.ContainsAny(token.TValue, "[]") {
400 operand, err := f.adjustFormulaOperand(sheet, sheetN, token, dir, num, offset)
407 if isFunctionStart(token) {
408 val += token.TValue + string(efp.ParenOpen)
411 if isFunctionStop(token) {
412 val += token.TValue + string(efp.ParenClose)
415 if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
416 val += string(efp.QuoteDouble) + strings.ReplaceAll(token.TValue, "\"", "\"\"") + string(efp.QuoteDouble)
424 // adjustHyperlinks provides a function to update hyperlinks when inserting or
425 // deleting rows or columns.
426 func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
428 if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
432 // order is important
434 for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
435 linkData := ws.Hyperlinks.Hyperlink[i]
436 colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
438 if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
439 f.deleteSheetRelationships(sheet, linkData.RID)
440 if len(ws.Hyperlinks.Hyperlink) > 1 {
441 ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
442 ws.Hyperlinks.Hyperlink[i+1:]...)
449 if ws.Hyperlinks == nil {
452 for i := range ws.Hyperlinks.Hyperlink {
453 link := &ws.Hyperlinks.Hyperlink[i] // get reference
454 link.Ref, _ = f.adjustFormulaRef(sheet, sheet, link.Ref, dir, num, offset)
458 // adjustTable provides a function to update the table when inserting or
459 // deleting rows or columns.
460 func (f *File) adjustTable(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
461 if ws.TableParts == nil || len(ws.TableParts.TableParts) == 0 {
464 for idx := 0; idx < len(ws.TableParts.TableParts); idx++ {
465 tbl := ws.TableParts.TableParts[idx]
466 target := f.getSheetRelationshipsTargetByID(sheet, tbl.RID)
467 tableXML := strings.ReplaceAll(target, "..", "xl")
468 content, ok := f.Pkg.Load(tableXML)
473 if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
474 Decode(&t); err != nil && err != io.EOF {
477 coordinates, err := rangeRefToCoordinates(t.Ref)
481 // Remove the table when deleting the header row of the table
482 if dir == rows && num == coordinates[0] && offset == -1 {
483 ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
484 ws.TableParts.Count = len(ws.TableParts.TableParts)
488 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
489 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
490 if y2-y1 < 1 || x2-x1 < 0 {
491 ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
492 ws.TableParts.Count = len(ws.TableParts.TableParts)
496 t.Ref, _ = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
497 if t.AutoFilter != nil {
498 t.AutoFilter.Ref = t.Ref
500 _ = f.setTableColumns(sheet, true, x1, y1, x2, &t)
502 table, _ := xml.Marshal(t)
503 f.saveFileList(tableXML, table)
507 // adjustAutoFilter provides a function to update the auto filter when
508 // inserting or deleting rows or columns.
509 func (f *File) adjustAutoFilter(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
510 if ws.AutoFilter == nil {
514 coordinates, err := rangeRefToCoordinates(ws.AutoFilter.Ref)
518 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
520 if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
522 for rowIdx := range ws.SheetData.Row {
523 rowData := &ws.SheetData.Row[rowIdx]
524 if rowData.R > y1 && rowData.R <= y2 {
525 rowData.Hidden = false
531 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
532 x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
534 ws.AutoFilter.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
538 // adjustAutoFilterHelper provides a function for adjusting auto filter to
539 // compare and calculate cell reference by the giving adjusting direction,
540 // operation reference and offset.
541 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
543 if coordinates[1] >= num {
544 coordinates[1] += offset
546 if coordinates[3] >= num {
547 coordinates[3] += offset
551 if coordinates[0] >= num {
552 coordinates[0] += offset
554 if coordinates[2] >= num {
555 coordinates[2] += offset
560 // adjustMergeCells provides a function to update merged cells when inserting
561 // or deleting rows or columns.
562 func (f *File) adjustMergeCells(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
563 if ws.MergeCells == nil {
567 for i := 0; i < len(ws.MergeCells.Cells); i++ {
568 mergedCells := ws.MergeCells.Cells[i]
569 mergedCellsRef := mergedCells.Ref
570 if !strings.Contains(mergedCellsRef, ":") {
571 mergedCellsRef += ":" + mergedCellsRef
573 coordinates, err := rangeRefToCoordinates(mergedCellsRef)
577 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
579 if y1 == num && y2 == num && offset < 0 {
580 f.deleteMergeCell(ws, i)
585 y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
587 if x1 == num && x2 == num && offset < 0 {
588 f.deleteMergeCell(ws, i)
593 x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
595 if x1 == x2 && y1 == y2 {
596 f.deleteMergeCell(ws, i)
600 mergedCells.rect = []int{x1, y1, x2, y2}
601 if mergedCells.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2}); err != nil {
608 // adjustMergeCellsHelper provides a function for adjusting merge cells to
609 // compare and calculate cell reference by the given pivot, operation reference and
611 func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
620 } else if num <= p2 {
625 if num < p1 || (num == p1 && num == p2) {
628 } else if num <= p2 {
634 // deleteMergeCell provides a function to delete merged cell by given index.
635 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
639 if len(ws.MergeCells.Cells) > idx {
640 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
641 ws.MergeCells.Count = len(ws.MergeCells.Cells)
645 // adjustCalcChainRef update the cell reference in calculation chain when
646 // inserting or deleting rows or columns.
647 func (f *File) adjustCalcChainRef(i, c, r, offset int, dir adjustDirection) {
649 if rn := r + offset; rn > 0 {
650 f.CalcChain.C[i].R, _ = CoordinatesToCellName(c, rn)
654 if nc := c + offset; nc > 0 {
655 f.CalcChain.C[i].R, _ = CoordinatesToCellName(nc, r)
659 // adjustCalcChain provides a function to update the calculation chain when
660 // inserting or deleting rows or columns.
661 func (f *File) adjustCalcChain(dir adjustDirection, num, offset, sheetID int) error {
662 if f.CalcChain == nil {
665 // If sheet ID is omitted, it is assumed to be the same as the i value of
666 // the previous cell.
668 for index, c := range f.CalcChain.C {
676 colNum, rowNum, err := CellNameToCoordinates(c.R)
680 if dir == rows && num <= rowNum {
681 if num == rowNum && offset == -1 {
682 _ = f.deleteCalcChain(c.I, c.R)
685 f.adjustCalcChainRef(index, colNum, rowNum, offset, dir)
687 if dir == columns && num <= colNum {
688 if num == colNum && offset == -1 {
689 _ = f.deleteCalcChain(c.I, c.R)
692 f.adjustCalcChainRef(index, colNum, rowNum, offset, dir)