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 // adjustHelperFunc defines functions to adjust helper.
33 var adjustHelperFunc = [7]func(*File, *xlsxWorksheet, string, adjustDirection, int, int, int) error{
34 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
35 return f.adjustDefinedNames(ws, sheet, dir, num, offset, sheetID)
37 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
38 return f.adjustDrawings(ws, sheet, dir, num, offset, sheetID)
40 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
41 return f.adjustMergeCells(ws, sheet, dir, num, offset, sheetID)
43 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
44 return f.adjustAutoFilter(ws, sheet, dir, num, offset, sheetID)
46 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
47 return f.adjustCalcChain(ws, sheet, dir, num, offset, sheetID)
49 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
50 return f.adjustTable(ws, sheet, dir, num, offset, sheetID)
52 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
53 return f.adjustVolatileDeps(ws, sheet, dir, num, offset, sheetID)
57 // adjustHelper provides a function to adjust rows and columns dimensions,
58 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
61 // sheet: Worksheet name that we're editing
62 // column: Index number of the column we're inserting/deleting before
63 // row: Index number of the row we're inserting/deleting before
64 // offset: Number of rows/column to insert/delete negative values indicate deletion
66 // TODO: adjustComments, adjustDataValidations, adjustPageBreaks, adjustProtectedCells
67 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
68 ws, err := f.workSheetReader(sheet)
72 sheetID := f.getSheetID(sheet)
74 err = f.adjustRowDimensions(sheet, ws, num, offset)
76 err = f.adjustColDimensions(sheet, ws, num, offset)
81 f.adjustHyperlinks(ws, sheet, dir, num, offset)
84 for _, fn := range adjustHelperFunc {
85 if err := fn(f, ws, sheet, dir, num, offset, sheetID); err != nil {
89 if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
95 // adjustCols provides a function to update column style when inserting or
97 func (f *File) adjustCols(ws *xlsxWorksheet, col, offset int) error {
101 for i := 0; i < len(ws.Cols.Col); i++ {
103 if ws.Cols.Col[i].Min >= col {
104 if ws.Cols.Col[i].Min += offset; ws.Cols.Col[i].Min > MaxColumns {
105 ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
110 if ws.Cols.Col[i].Max >= col || ws.Cols.Col[i].Max+1 == col {
111 if ws.Cols.Col[i].Max += offset; ws.Cols.Col[i].Max > MaxColumns {
112 ws.Cols.Col[i].Max = MaxColumns
117 if ws.Cols.Col[i].Min == col && ws.Cols.Col[i].Max == col {
118 ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
122 if ws.Cols.Col[i].Min > col {
123 ws.Cols.Col[i].Min += offset
125 if ws.Cols.Col[i].Max >= col {
126 ws.Cols.Col[i].Max += offset
129 if len(ws.Cols.Col) == 0 {
135 // adjustColDimensions provides a function to update column dimensions when
136 // inserting or deleting rows or columns.
137 func (f *File) adjustColDimensions(sheet string, ws *xlsxWorksheet, col, offset int) error {
138 for rowIdx := range ws.SheetData.Row {
139 for _, v := range ws.SheetData.Row[rowIdx].C {
140 if cellCol, _, _ := CellNameToCoordinates(v.R); col <= cellCol {
141 if newCol := cellCol + offset; newCol > 0 && newCol > MaxColumns {
142 return ErrColumnNumber
147 for _, sheetN := range f.GetSheetList() {
148 worksheet, err := f.workSheetReader(sheetN)
150 if err.Error() == newNotWorksheetError(sheetN).Error() {
155 for rowIdx := range worksheet.SheetData.Row {
156 for colIdx, v := range worksheet.SheetData.Row[rowIdx].C {
157 if cellCol, cellRow, _ := CellNameToCoordinates(v.R); sheetN == sheet && col <= cellCol {
158 if newCol := cellCol + offset; newCol > 0 {
159 worksheet.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
162 if err := f.adjustFormula(sheet, sheetN, worksheet.SheetData.Row[rowIdx].C[colIdx].F, columns, col, offset, false); err != nil {
168 return f.adjustCols(ws, col, offset)
171 // adjustRowDimensions provides a function to update row dimensions when
172 // inserting or deleting rows or columns.
173 func (f *File) adjustRowDimensions(sheet string, ws *xlsxWorksheet, row, offset int) error {
174 for _, sheetN := range f.GetSheetList() {
178 worksheet, err := f.workSheetReader(sheetN)
180 if err.Error() == newNotWorksheetError(sheetN).Error() {
185 numOfRows := len(worksheet.SheetData.Row)
186 for i := 0; i < numOfRows; i++ {
187 r := &worksheet.SheetData.Row[i]
188 if err = f.adjustSingleRowFormulas(sheet, sheetN, r, row, offset, false); err != nil {
193 totalRows := len(ws.SheetData.Row)
197 lastRow := &ws.SheetData.Row[totalRows-1]
198 if newRow := lastRow.R + offset; lastRow.R >= row && newRow > 0 && newRow > TotalRows {
201 numOfRows := len(ws.SheetData.Row)
202 for i := 0; i < numOfRows; i++ {
203 r := &ws.SheetData.Row[i]
204 if newRow := r.R + offset; r.R >= row && newRow > 0 {
205 r.adjustSingleRowDimensions(offset)
207 if err := f.adjustSingleRowFormulas(sheet, sheet, r, row, offset, false); err != nil {
214 // adjustSingleRowDimensions provides a function to adjust single row dimensions.
215 func (r *xlsxRow) adjustSingleRowDimensions(offset int) {
217 for i, col := range r.C {
218 colName, _, _ := SplitCellName(col.R)
219 r.C[i].R, _ = JoinCellName(colName, r.R)
223 // adjustSingleRowFormulas provides a function to adjust single row formulas.
224 func (f *File) adjustSingleRowFormulas(sheet, sheetN string, r *xlsxRow, num, offset int, si bool) error {
225 for _, col := range r.C {
226 if err := f.adjustFormula(sheet, sheetN, col.F, rows, num, offset, si); err != nil {
233 // adjustCellRef provides a function to adjust cell reference.
234 func (f *File) adjustCellRef(ref string, dir adjustDirection, num, offset int) (string, error) {
235 if !strings.Contains(ref, ":") {
238 coordinates, err := rangeRefToCoordinates(ref)
243 if coordinates[0] >= num {
244 coordinates[0] += offset
246 if coordinates[2] >= num {
247 coordinates[2] += offset
250 if coordinates[1] >= num {
251 coordinates[1] += offset
253 if coordinates[3] >= num {
254 coordinates[3] += offset
257 return f.coordinatesToRangeRef(coordinates)
260 // adjustFormula provides a function to adjust formula reference and shared
261 // formula reference.
262 func (f *File) adjustFormula(sheet, sheetN string, formula *xlsxF, dir adjustDirection, num, offset int, si bool) error {
267 if formula.Ref != "" && sheet == sheetN {
268 if formula.Ref, err = f.adjustCellRef(formula.Ref, dir, num, offset); err != nil {
271 if si && formula.Si != nil {
272 formula.Si = intPtr(*formula.Si + 1)
275 if formula.Content != "" {
276 if formula.Content, err = f.adjustFormulaRef(sheet, sheetN, formula.Content, false, dir, num, offset); err != nil {
283 // isFunctionStop provides a function to check if token is a function stop.
284 func isFunctionStop(token efp.Token) bool {
285 return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStop
288 // isFunctionStart provides a function to check if token is a function start.
289 func isFunctionStart(token efp.Token) bool {
290 return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStart
293 // escapeSheetName enclose sheet name in single quotation marks if the giving
294 // worksheet name includes spaces or non-alphabetical characters.
295 func escapeSheetName(name string) string {
296 if strings.IndexFunc(name, func(r rune) bool {
297 return !unicode.IsLetter(r) && !unicode.IsNumber(r)
299 return "'" + strings.ReplaceAll(name, "'", "''") + "'"
304 // adjustFormulaColumnName adjust column name in the formula reference.
305 func adjustFormulaColumnName(name, operand string, abs, keepRelative bool, dir adjustDirection, num, offset int) (string, string, bool, error) {
306 if name == "" || (!abs && keepRelative) {
307 return "", operand + name, abs, nil
309 col, err := ColumnNameToNumber(name)
311 return "", operand, false, err
313 if dir == columns && col >= num {
315 colName, err := ColumnNumberToName(col)
316 return "", operand + colName, false, err
318 return "", operand + name, false, nil
321 // adjustFormulaRowNumber adjust row number in the formula reference.
322 func adjustFormulaRowNumber(name, operand string, abs, keepRelative bool, dir adjustDirection, num, offset int) (string, string, bool, error) {
323 if name == "" || (!abs && keepRelative) {
324 return "", operand + name, abs, nil
326 row, _ := strconv.Atoi(name)
327 if dir == rows && row >= num {
329 if row <= 0 || row > TotalRows {
330 return "", operand + name, false, ErrMaxRows
332 return "", operand + strconv.Itoa(row), false, nil
334 return "", operand + name, false, nil
337 // adjustFormulaOperandRef adjust cell reference in the operand tokens for the formula.
338 func adjustFormulaOperandRef(row, col, operand string, abs, keepRelative bool, dir adjustDirection, num int, offset int) (string, string, string, bool, error) {
340 col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
342 return row, col, operand, abs, err
344 row, operand, abs, err = adjustFormulaRowNumber(row, operand, abs, keepRelative, dir, num, offset)
345 return row, col, operand, abs, err
348 // adjustFormulaOperand adjust range operand tokens for the formula.
349 func (f *File) adjustFormulaOperand(sheet, sheetN string, keepRelative bool, token efp.Token, dir adjustDirection, num int, offset int) (string, error) {
353 sheetName, col, row, operand string
355 tokens = strings.Split(token.TValue, "!")
357 if len(tokens) == 2 { // have a worksheet
358 sheetName, cell = tokens[0], tokens[1]
359 operand = escapeSheetName(sheetName) + "!"
361 if sheet != sheetN && sheet != sheetName {
362 return operand + cell, err
364 for _, r := range cell {
366 if col, operand, _, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset); err != nil {
373 if ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z') {
377 if '0' <= r && r <= '9' {
379 col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
385 if row, col, operand, abs, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset); err != nil {
390 _, _, operand, _, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset)
394 // adjustFormulaRef returns adjusted formula by giving adjusting direction and
395 // the base number of column or row, and offset.
396 func (f *File) adjustFormulaRef(sheet, sheetN, formula string, keepRelative bool, dir adjustDirection, num, offset int) (string, error) {
399 definedNames []string
400 ps = efp.ExcelParser()
402 for _, definedName := range f.GetDefinedName() {
403 if definedName.Scope == "Workbook" || definedName.Scope == sheet {
404 definedNames = append(definedNames, definedName.Name)
407 for _, token := range ps.Parse(formula) {
408 if token.TType == efp.TokenTypeUnknown {
412 if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeRange {
413 if inStrSlice(definedNames, token.TValue, true) != -1 {
417 if strings.ContainsAny(token.TValue, "[]") {
421 operand, err := f.adjustFormulaOperand(sheet, sheetN, keepRelative, token, dir, num, offset)
428 if isFunctionStart(token) {
429 val += token.TValue + string(efp.ParenOpen)
432 if isFunctionStop(token) {
433 val += token.TValue + string(efp.ParenClose)
436 if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
437 val += string(efp.QuoteDouble) + strings.ReplaceAll(token.TValue, "\"", "\"\"") + string(efp.QuoteDouble)
445 // adjustHyperlinks provides a function to update hyperlinks when inserting or
446 // deleting rows or columns.
447 func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
449 if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
453 // order is important
455 for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
456 linkData := ws.Hyperlinks.Hyperlink[i]
457 colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
459 if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
460 f.deleteSheetRelationships(sheet, linkData.RID)
461 if len(ws.Hyperlinks.Hyperlink) > 1 {
462 ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
463 ws.Hyperlinks.Hyperlink[i+1:]...)
470 if ws.Hyperlinks == nil {
473 for i := range ws.Hyperlinks.Hyperlink {
474 link := &ws.Hyperlinks.Hyperlink[i] // get reference
475 link.Ref, _ = f.adjustFormulaRef(sheet, sheet, link.Ref, false, dir, num, offset)
479 // adjustTable provides a function to update the table when inserting or
480 // deleting rows or columns.
481 func (f *File) adjustTable(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
482 if ws.TableParts == nil || len(ws.TableParts.TableParts) == 0 {
485 for idx := 0; idx < len(ws.TableParts.TableParts); idx++ {
486 tbl := ws.TableParts.TableParts[idx]
487 target := f.getSheetRelationshipsTargetByID(sheet, tbl.RID)
488 tableXML := strings.ReplaceAll(target, "..", "xl")
489 content, ok := f.Pkg.Load(tableXML)
494 if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
495 Decode(&t); err != nil && err != io.EOF {
498 coordinates, err := rangeRefToCoordinates(t.Ref)
502 // Remove the table when deleting the header row of the table
503 if dir == rows && num == coordinates[0] && offset == -1 {
504 ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
505 ws.TableParts.Count = len(ws.TableParts.TableParts)
509 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
510 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
511 if y2-y1 < 1 || x2-x1 < 0 {
512 ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
513 ws.TableParts.Count = len(ws.TableParts.TableParts)
517 t.Ref, _ = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
518 if t.AutoFilter != nil {
519 t.AutoFilter.Ref = t.Ref
521 _ = f.setTableColumns(sheet, true, x1, y1, x2, &t)
522 // Currently doesn't support query table
523 t.TableType, t.TotalsRowCount, t.ConnectionID = "", 0, 0
524 table, _ := xml.Marshal(t)
525 f.saveFileList(tableXML, table)
530 // adjustAutoFilter provides a function to update the auto filter when
531 // inserting or deleting rows or columns.
532 func (f *File) adjustAutoFilter(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
533 if ws.AutoFilter == nil {
537 coordinates, err := rangeRefToCoordinates(ws.AutoFilter.Ref)
541 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
543 if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
545 for rowIdx := range ws.SheetData.Row {
546 rowData := &ws.SheetData.Row[rowIdx]
547 if rowData.R > y1 && rowData.R <= y2 {
548 rowData.Hidden = false
554 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
555 x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
557 ws.AutoFilter.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
561 // adjustAutoFilterHelper provides a function for adjusting auto filter to
562 // compare and calculate cell reference by the giving adjusting direction,
563 // operation reference and offset.
564 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
566 if coordinates[1] >= num {
567 coordinates[1] += offset
569 if coordinates[3] >= num {
570 coordinates[3] += offset
574 if coordinates[0] >= num {
575 coordinates[0] += offset
577 if coordinates[2] >= num {
578 coordinates[2] += offset
583 // adjustMergeCells provides a function to update merged cells when inserting
584 // or deleting rows or columns.
585 func (f *File) adjustMergeCells(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
586 if ws.MergeCells == nil {
590 for i := 0; i < len(ws.MergeCells.Cells); i++ {
591 mergedCells := ws.MergeCells.Cells[i]
592 mergedCellsRef := mergedCells.Ref
593 if !strings.Contains(mergedCellsRef, ":") {
594 mergedCellsRef += ":" + mergedCellsRef
596 coordinates, err := rangeRefToCoordinates(mergedCellsRef)
600 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
602 if y1 == num && y2 == num && offset < 0 {
603 f.deleteMergeCell(ws, i)
607 y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
609 if x1 == num && x2 == num && offset < 0 {
610 f.deleteMergeCell(ws, i)
614 x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
616 if x1 == x2 && y1 == y2 {
617 f.deleteMergeCell(ws, i)
621 mergedCells.rect = []int{x1, y1, x2, y2}
622 if mergedCells.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2}); err != nil {
629 // adjustMergeCellsHelper provides a function for adjusting merge cells to
630 // compare and calculate cell reference by the given pivot, operation reference and
632 func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
641 } else if num <= p2 {
646 if num < p1 || (num == p1 && num == p2) {
649 } else if num <= p2 {
655 // deleteMergeCell provides a function to delete merged cell by given index.
656 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
660 if len(ws.MergeCells.Cells) > idx {
661 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
662 ws.MergeCells.Count = len(ws.MergeCells.Cells)
666 // adjustCellName returns updated cell name by giving column/row number and
667 // offset on inserting or deleting rows or columns.
668 func adjustCellName(cell string, dir adjustDirection, c, r, offset int) (string, error) {
670 if rn := r + offset; rn > 0 {
671 return CoordinatesToCellName(c, rn)
674 return CoordinatesToCellName(c+offset, r)
677 // adjustCalcChain provides a function to update the calculation chain when
678 // inserting or deleting rows or columns.
679 func (f *File) adjustCalcChain(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
680 if f.CalcChain == nil {
683 // If sheet ID is omitted, it is assumed to be the same as the i value of
684 // the previous cell.
686 for i := 0; i < len(f.CalcChain.C); i++ {
687 c := f.CalcChain.C[i]
695 colNum, rowNum, err := CellNameToCoordinates(c.R)
699 if dir == rows && num <= rowNum {
700 if num == rowNum && offset == -1 {
701 _ = f.deleteCalcChain(c.I, c.R)
705 f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
707 if dir == columns && num <= colNum {
708 if num == colNum && offset == -1 {
709 _ = f.deleteCalcChain(c.I, c.R)
713 f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
719 // adjustVolatileDepsTopic updates the volatile dependencies topic when
720 // inserting or deleting rows or columns.
721 func (vt *xlsxVolTypes) adjustVolatileDepsTopic(cell string, dir adjustDirection, indexes []int) (int, error) {
722 num, offset, i1, i2, i3, i4 := indexes[0], indexes[1], indexes[2], indexes[3], indexes[4], indexes[5]
723 colNum, rowNum, err := CellNameToCoordinates(cell)
727 if dir == rows && num <= rowNum {
728 if num == rowNum && offset == -1 {
729 vt.deleteVolTopicRef(i1, i2, i3, i4)
733 vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
735 if dir == columns && num <= colNum {
736 if num == colNum && offset == -1 {
737 vt.deleteVolTopicRef(i1, i2, i3, i4)
741 if name, _ := adjustCellName(cell, dir, colNum, rowNum, offset); name != "" {
742 vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
748 // adjustVolatileDeps updates the volatile dependencies when inserting or
749 // deleting rows or columns.
750 func (f *File) adjustVolatileDeps(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
751 volTypes, err := f.volatileDepsReader()
752 if err != nil || volTypes == nil {
755 for i1 := 0; i1 < len(volTypes.VolType); i1++ {
756 for i2 := 0; i2 < len(volTypes.VolType[i1].Main); i2++ {
757 for i3 := 0; i3 < len(volTypes.VolType[i1].Main[i2].Tp); i3++ {
758 for i4 := 0; i4 < len(volTypes.VolType[i1].Main[i2].Tp[i3].Tr); i4++ {
759 ref := volTypes.VolType[i1].Main[i2].Tp[i3].Tr[i4]
760 if ref.S != sheetID {
763 if i4, err = volTypes.adjustVolatileDepsTopic(ref.R, dir, []int{num, offset, i1, i2, i3, i4}); err != nil {
773 // adjustDrawings updates the starting anchor of the two cell anchor pictures
774 // and charts object when inserting or deleting rows or columns.
775 func (from *xlsxFrom) adjustDrawings(dir adjustDirection, num, offset int, editAs string) (bool, error) {
777 if dir == columns && from.Col+1 >= num && from.Col+offset >= 0 {
778 if from.Col+offset >= MaxColumns {
779 return false, ErrColumnNumber
782 ok = editAs == "oneCell"
784 if dir == rows && from.Row+1 >= num && from.Row+offset >= 0 {
785 if from.Row+offset >= TotalRows {
786 return false, ErrMaxRows
789 ok = editAs == "oneCell"
794 // adjustDrawings updates the ending anchor of the two cell anchor pictures
795 // and charts object when inserting or deleting rows or columns.
796 func (to *xlsxTo) adjustDrawings(dir adjustDirection, num, offset int, editAs string, ok bool) error {
797 if dir == columns && to.Col+1 >= num && to.Col+offset >= 0 && ok {
798 if to.Col+offset >= MaxColumns {
799 return ErrColumnNumber
803 if dir == rows && to.Row+1 >= num && to.Row+offset >= 0 && ok {
804 if to.Row+offset >= TotalRows {
812 // adjustDrawings updates the two cell anchor pictures and charts object when
813 // inserting or deleting rows or columns.
814 func (a *xdrCellAnchor) adjustDrawings(dir adjustDirection, num, offset int) error {
816 if a.From == nil || a.To == nil || editAs == "absolute" {
819 ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
823 return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
826 // adjustDrawings updates the existing two cell anchor pictures and charts
827 // object when inserting or deleting rows or columns.
828 func (a *xlsxCellAnchorPos) adjustDrawings(dir adjustDirection, num, offset int, editAs string) error {
829 if a.From == nil || a.To == nil || editAs == "absolute" {
832 ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
836 return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
839 // adjustDrawings updates the pictures and charts object when inserting or
840 // deleting rows or columns.
841 func (f *File) adjustDrawings(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
842 if ws.Drawing == nil {
845 target := f.getSheetRelationshipsTargetByID(sheet, ws.Drawing.RID)
846 drawingXML := strings.TrimPrefix(strings.ReplaceAll(target, "..", "xl"), "/")
851 if wsDr, _, err = f.drawingParser(drawingXML); err != nil {
854 anchorCb := func(a *xdrCellAnchor) error {
855 if a.GraphicFrame == "" {
856 return a.adjustDrawings(dir, num, offset)
858 deCellAnchor := decodeCellAnchor{}
859 deCellAnchorPos := decodeCellAnchorPos{}
860 _ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchor>" + a.GraphicFrame + "</decodeCellAnchor>")).Decode(&deCellAnchor)
861 _ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchorPos>" + a.GraphicFrame + "</decodeCellAnchorPos>")).Decode(&deCellAnchorPos)
862 xlsxCellAnchorPos := xlsxCellAnchorPos(deCellAnchorPos)
863 for i := 0; i < len(xlsxCellAnchorPos.AlternateContent); i++ {
864 xlsxCellAnchorPos.AlternateContent[i].XMLNSMC = SourceRelationshipCompatibility.Value
866 if deCellAnchor.From != nil {
867 xlsxCellAnchorPos.From = &xlsxFrom{
868 Col: deCellAnchor.From.Col, ColOff: deCellAnchor.From.ColOff,
869 Row: deCellAnchor.From.Row, RowOff: deCellAnchor.From.RowOff,
872 if deCellAnchor.To != nil {
873 xlsxCellAnchorPos.To = &xlsxTo{
874 Col: deCellAnchor.To.Col, ColOff: deCellAnchor.To.ColOff,
875 Row: deCellAnchor.To.Row, RowOff: deCellAnchor.To.RowOff,
878 if err = xlsxCellAnchorPos.adjustDrawings(dir, num, offset, a.EditAs); err != nil {
881 cellAnchor, _ := xml.Marshal(xlsxCellAnchorPos)
882 a.GraphicFrame = strings.TrimSuffix(strings.TrimPrefix(string(cellAnchor), "<xlsxCellAnchorPos>"), "</xlsxCellAnchorPos>")
885 for _, anchor := range wsDr.TwoCellAnchor {
886 if err = anchorCb(anchor); err != nil {
893 // adjustDefinedNames updates the cell reference of the defined names when
894 // inserting or deleting rows or columns.
895 func (f *File) adjustDefinedNames(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
896 wb, err := f.workbookReader()
900 if wb.DefinedNames != nil {
901 for i := 0; i < len(wb.DefinedNames.DefinedName); i++ {
902 data := wb.DefinedNames.DefinedName[i].Data
903 if data, err = f.adjustFormulaRef(sheet, "", data, true, dir, num, offset); err == nil {
904 wb.DefinedNames.DefinedName[i].Data = data