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 = [8]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.adjustConditionalFormats(ws, sheet, dir, num, offset, sheetID)
37 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
38 return f.adjustDefinedNames(ws, sheet, dir, num, offset, sheetID)
40 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
41 return f.adjustDrawings(ws, sheet, dir, num, offset, sheetID)
43 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
44 return f.adjustMergeCells(ws, sheet, dir, num, offset, sheetID)
46 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
47 return f.adjustAutoFilter(ws, sheet, dir, num, offset, sheetID)
49 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
50 return f.adjustCalcChain(ws, sheet, dir, num, offset, sheetID)
52 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
53 return f.adjustTable(ws, sheet, dir, num, offset, sheetID)
55 func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
56 return f.adjustVolatileDeps(ws, sheet, dir, num, offset, sheetID)
60 // adjustHelper provides a function to adjust rows and columns dimensions,
61 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
64 // sheet: Worksheet name that we're editing
65 // column: Index number of the column we're inserting/deleting before
66 // row: Index number of the row we're inserting/deleting before
67 // offset: Number of rows/column to insert/delete negative values indicate deletion
69 // TODO: adjustComments, adjustDataValidations, adjustPageBreaks, adjustProtectedCells
70 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
71 ws, err := f.workSheetReader(sheet)
75 sheetID := f.getSheetID(sheet)
77 err = f.adjustRowDimensions(sheet, ws, num, offset)
79 err = f.adjustColDimensions(sheet, ws, num, offset)
84 f.adjustHyperlinks(ws, sheet, dir, num, offset)
87 for _, fn := range adjustHelperFunc {
88 if err := fn(f, ws, sheet, dir, num, offset, sheetID); err != nil {
92 if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
98 // adjustCols provides a function to update column style when inserting or
100 func (f *File) adjustCols(ws *xlsxWorksheet, col, offset int) error {
104 for i := 0; i < len(ws.Cols.Col); i++ {
106 if ws.Cols.Col[i].Min >= col {
107 if ws.Cols.Col[i].Min += offset; ws.Cols.Col[i].Min > MaxColumns {
108 ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
113 if ws.Cols.Col[i].Max >= col || ws.Cols.Col[i].Max+1 == col {
114 if ws.Cols.Col[i].Max += offset; ws.Cols.Col[i].Max > MaxColumns {
115 ws.Cols.Col[i].Max = MaxColumns
120 if ws.Cols.Col[i].Min == col && ws.Cols.Col[i].Max == col {
121 ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
125 if ws.Cols.Col[i].Min > col {
126 ws.Cols.Col[i].Min += offset
128 if ws.Cols.Col[i].Max >= col {
129 ws.Cols.Col[i].Max += offset
132 if len(ws.Cols.Col) == 0 {
138 // adjustColDimensions provides a function to update column dimensions when
139 // inserting or deleting rows or columns.
140 func (f *File) adjustColDimensions(sheet string, ws *xlsxWorksheet, col, offset int) error {
141 for rowIdx := range ws.SheetData.Row {
142 for _, v := range ws.SheetData.Row[rowIdx].C {
143 if cellCol, _, _ := CellNameToCoordinates(v.R); col <= cellCol {
144 if newCol := cellCol + offset; newCol > 0 && newCol > MaxColumns {
145 return ErrColumnNumber
150 for _, sheetN := range f.GetSheetList() {
151 worksheet, err := f.workSheetReader(sheetN)
153 if err.Error() == newNotWorksheetError(sheetN).Error() {
158 for rowIdx := range worksheet.SheetData.Row {
159 for colIdx, v := range worksheet.SheetData.Row[rowIdx].C {
160 if cellCol, cellRow, _ := CellNameToCoordinates(v.R); sheetN == sheet && col <= cellCol {
161 if newCol := cellCol + offset; newCol > 0 {
162 worksheet.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
165 if err := f.adjustFormula(sheet, sheetN, worksheet.SheetData.Row[rowIdx].C[colIdx].F, columns, col, offset, false); err != nil {
171 return f.adjustCols(ws, col, offset)
174 // adjustRowDimensions provides a function to update row dimensions when
175 // inserting or deleting rows or columns.
176 func (f *File) adjustRowDimensions(sheet string, ws *xlsxWorksheet, row, offset int) error {
177 for _, sheetN := range f.GetSheetList() {
181 worksheet, err := f.workSheetReader(sheetN)
183 if err.Error() == newNotWorksheetError(sheetN).Error() {
188 numOfRows := len(worksheet.SheetData.Row)
189 for i := 0; i < numOfRows; i++ {
190 r := &worksheet.SheetData.Row[i]
191 if err = f.adjustSingleRowFormulas(sheet, sheetN, r, row, offset, false); err != nil {
196 totalRows := len(ws.SheetData.Row)
200 lastRow := &ws.SheetData.Row[totalRows-1]
201 if newRow := lastRow.R + offset; lastRow.R >= row && newRow > 0 && newRow > TotalRows {
204 numOfRows := len(ws.SheetData.Row)
205 for i := 0; i < numOfRows; i++ {
206 r := &ws.SheetData.Row[i]
207 if newRow := r.R + offset; r.R >= row && newRow > 0 {
208 r.adjustSingleRowDimensions(offset)
210 if err := f.adjustSingleRowFormulas(sheet, sheet, r, row, offset, false); err != nil {
217 // adjustSingleRowDimensions provides a function to adjust single row dimensions.
218 func (r *xlsxRow) adjustSingleRowDimensions(offset int) {
220 for i, col := range r.C {
221 colName, _, _ := SplitCellName(col.R)
222 r.C[i].R, _ = JoinCellName(colName, r.R)
226 // adjustSingleRowFormulas provides a function to adjust single row formulas.
227 func (f *File) adjustSingleRowFormulas(sheet, sheetN string, r *xlsxRow, num, offset int, si bool) error {
228 for _, col := range r.C {
229 if err := f.adjustFormula(sheet, sheetN, col.F, rows, num, offset, si); err != nil {
236 // adjustCellRef provides a function to adjust cell reference.
237 func (f *File) adjustCellRef(ref string, dir adjustDirection, num, offset int) (string, bool, error) {
238 if !strings.Contains(ref, ":") {
242 coordinates, err := rangeRefToCoordinates(ref)
244 return ref, delete, err
247 if offset < 0 && coordinates[0] == coordinates[2] {
250 if coordinates[0] >= num {
251 coordinates[0] += offset
253 if coordinates[2] >= num {
254 coordinates[2] += offset
257 if offset < 0 && coordinates[1] == coordinates[3] {
260 if coordinates[1] >= num {
261 coordinates[1] += offset
263 if coordinates[3] >= num {
264 coordinates[3] += offset
267 ref, err = f.coordinatesToRangeRef(coordinates)
268 return ref, delete, err
271 // adjustFormula provides a function to adjust formula reference and shared
272 // formula reference.
273 func (f *File) adjustFormula(sheet, sheetN string, formula *xlsxF, dir adjustDirection, num, offset int, si bool) error {
278 if formula.Ref != "" && sheet == sheetN {
279 if formula.Ref, _, err = f.adjustCellRef(formula.Ref, dir, num, offset); err != nil {
282 if si && formula.Si != nil {
283 formula.Si = intPtr(*formula.Si + 1)
286 if formula.Content != "" {
287 if formula.Content, err = f.adjustFormulaRef(sheet, sheetN, formula.Content, false, dir, num, offset); err != nil {
294 // isFunctionStop provides a function to check if token is a function stop.
295 func isFunctionStop(token efp.Token) bool {
296 return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStop
299 // isFunctionStart provides a function to check if token is a function start.
300 func isFunctionStart(token efp.Token) bool {
301 return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStart
304 // escapeSheetName enclose sheet name in single quotation marks if the giving
305 // worksheet name includes spaces or non-alphabetical characters.
306 func escapeSheetName(name string) string {
307 if strings.IndexFunc(name, func(r rune) bool {
308 return !unicode.IsLetter(r) && !unicode.IsNumber(r)
310 return "'" + strings.ReplaceAll(name, "'", "''") + "'"
315 // adjustFormulaColumnName adjust column name in the formula reference.
316 func adjustFormulaColumnName(name, operand string, abs, keepRelative bool, dir adjustDirection, num, offset int) (string, string, bool, error) {
317 if name == "" || (!abs && keepRelative) {
318 return "", operand + name, abs, nil
320 col, err := ColumnNameToNumber(name)
322 return "", operand, false, err
324 if dir == columns && col >= num {
326 colName, err := ColumnNumberToName(col)
327 return "", operand + colName, false, err
329 return "", operand + name, false, nil
332 // adjustFormulaRowNumber adjust row number in the formula reference.
333 func adjustFormulaRowNumber(name, operand string, abs, keepRelative bool, dir adjustDirection, num, offset int) (string, string, bool, error) {
334 if name == "" || (!abs && keepRelative) {
335 return "", operand + name, abs, nil
337 row, _ := strconv.Atoi(name)
338 if dir == rows && row >= num {
340 if row <= 0 || row > TotalRows {
341 return "", operand + name, false, ErrMaxRows
343 return "", operand + strconv.Itoa(row), false, nil
345 return "", operand + name, false, nil
348 // adjustFormulaOperandRef adjust cell reference in the operand tokens for the formula.
349 func adjustFormulaOperandRef(row, col, operand string, abs, keepRelative bool, dir adjustDirection, num int, offset int) (string, string, string, bool, error) {
351 col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
353 return row, col, operand, abs, err
355 row, operand, abs, err = adjustFormulaRowNumber(row, operand, abs, keepRelative, dir, num, offset)
356 return row, col, operand, abs, err
359 // adjustFormulaOperand adjust range operand tokens for the formula.
360 func (f *File) adjustFormulaOperand(sheet, sheetN string, keepRelative bool, token efp.Token, dir adjustDirection, num int, offset int) (string, error) {
364 sheetName, col, row, operand string
366 tokens = strings.Split(token.TValue, "!")
368 if len(tokens) == 2 { // have a worksheet
369 sheetName, cell = tokens[0], tokens[1]
370 operand = escapeSheetName(sheetName) + "!"
372 if sheet != sheetN && sheet != sheetName {
373 return operand + cell, err
375 for _, r := range cell {
377 if col, operand, _, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset); err != nil {
384 if ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z') {
388 if '0' <= r && r <= '9' {
390 col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
396 if row, col, operand, abs, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset); err != nil {
401 _, _, operand, _, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset)
405 // adjustFormulaRef returns adjusted formula by giving adjusting direction and
406 // the base number of column or row, and offset.
407 func (f *File) adjustFormulaRef(sheet, sheetN, formula string, keepRelative bool, dir adjustDirection, num, offset int) (string, error) {
410 definedNames []string
411 ps = efp.ExcelParser()
413 for _, definedName := range f.GetDefinedName() {
414 if definedName.Scope == "Workbook" || definedName.Scope == sheet {
415 definedNames = append(definedNames, definedName.Name)
418 for _, token := range ps.Parse(formula) {
419 if token.TType == efp.TokenTypeUnknown {
423 if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeRange {
424 if inStrSlice(definedNames, token.TValue, true) != -1 {
428 if strings.ContainsAny(token.TValue, "[]") {
432 operand, err := f.adjustFormulaOperand(sheet, sheetN, keepRelative, token, dir, num, offset)
439 if isFunctionStart(token) {
440 val += token.TValue + string(efp.ParenOpen)
443 if isFunctionStop(token) {
444 val += token.TValue + string(efp.ParenClose)
447 if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
448 val += string(efp.QuoteDouble) + strings.ReplaceAll(token.TValue, "\"", "\"\"") + string(efp.QuoteDouble)
456 // adjustHyperlinks provides a function to update hyperlinks when inserting or
457 // deleting rows or columns.
458 func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
460 if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
464 // order is important
466 for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
467 linkData := ws.Hyperlinks.Hyperlink[i]
468 colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
470 if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
471 f.deleteSheetRelationships(sheet, linkData.RID)
472 if len(ws.Hyperlinks.Hyperlink) > 1 {
473 ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
474 ws.Hyperlinks.Hyperlink[i+1:]...)
481 if ws.Hyperlinks == nil {
484 for i := range ws.Hyperlinks.Hyperlink {
485 link := &ws.Hyperlinks.Hyperlink[i] // get reference
486 link.Ref, _ = f.adjustFormulaRef(sheet, sheet, link.Ref, false, dir, num, offset)
490 // adjustTable provides a function to update the table when inserting or
491 // deleting rows or columns.
492 func (f *File) adjustTable(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
493 if ws.TableParts == nil || len(ws.TableParts.TableParts) == 0 {
496 for idx := 0; idx < len(ws.TableParts.TableParts); idx++ {
497 tbl := ws.TableParts.TableParts[idx]
498 target := f.getSheetRelationshipsTargetByID(sheet, tbl.RID)
499 tableXML := strings.ReplaceAll(target, "..", "xl")
500 content, ok := f.Pkg.Load(tableXML)
505 if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
506 Decode(&t); err != nil && err != io.EOF {
509 coordinates, err := rangeRefToCoordinates(t.Ref)
513 // Remove the table when deleting the header row of the table
514 if dir == rows && num == coordinates[0] && offset == -1 {
515 ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
516 ws.TableParts.Count = len(ws.TableParts.TableParts)
520 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
521 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
522 if y2-y1 < 1 || x2-x1 < 0 {
523 ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
524 ws.TableParts.Count = len(ws.TableParts.TableParts)
528 t.Ref, _ = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
529 if t.AutoFilter != nil {
530 t.AutoFilter.Ref = t.Ref
532 _ = f.setTableColumns(sheet, true, x1, y1, x2, &t)
533 // Currently doesn't support query table
534 t.TableType, t.TotalsRowCount, t.ConnectionID = "", 0, 0
535 table, _ := xml.Marshal(t)
536 f.saveFileList(tableXML, table)
541 // adjustAutoFilter provides a function to update the auto filter when
542 // inserting or deleting rows or columns.
543 func (f *File) adjustAutoFilter(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
544 if ws.AutoFilter == nil {
548 coordinates, err := rangeRefToCoordinates(ws.AutoFilter.Ref)
552 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
554 if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
556 for rowIdx := range ws.SheetData.Row {
557 rowData := &ws.SheetData.Row[rowIdx]
558 if rowData.R > y1 && rowData.R <= y2 {
559 rowData.Hidden = false
565 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
566 x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
568 ws.AutoFilter.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
572 // adjustAutoFilterHelper provides a function for adjusting auto filter to
573 // compare and calculate cell reference by the giving adjusting direction,
574 // operation reference and offset.
575 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
577 if coordinates[1] >= num {
578 coordinates[1] += offset
580 if coordinates[3] >= num {
581 coordinates[3] += offset
585 if coordinates[0] >= num {
586 coordinates[0] += offset
588 if coordinates[2] >= num {
589 coordinates[2] += offset
594 // adjustMergeCells provides a function to update merged cells when inserting
595 // or deleting rows or columns.
596 func (f *File) adjustMergeCells(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
597 if ws.MergeCells == nil {
601 for i := 0; i < len(ws.MergeCells.Cells); i++ {
602 mergedCells := ws.MergeCells.Cells[i]
603 mergedCellsRef := mergedCells.Ref
604 if !strings.Contains(mergedCellsRef, ":") {
605 mergedCellsRef += ":" + mergedCellsRef
607 coordinates, err := rangeRefToCoordinates(mergedCellsRef)
611 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
613 if y1 == num && y2 == num && offset < 0 {
614 f.deleteMergeCell(ws, i)
618 y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
620 if x1 == num && x2 == num && offset < 0 {
621 f.deleteMergeCell(ws, i)
625 x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
627 if x1 == x2 && y1 == y2 {
628 f.deleteMergeCell(ws, i)
632 mergedCells.rect = []int{x1, y1, x2, y2}
633 if mergedCells.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2}); err != nil {
640 // adjustMergeCellsHelper provides a function for adjusting merge cells to
641 // compare and calculate cell reference by the given pivot, operation reference and
643 func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
652 } else if num <= p2 {
657 if num < p1 || (num == p1 && num == p2) {
660 } else if num <= p2 {
666 // deleteMergeCell provides a function to delete merged cell by given index.
667 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
671 if len(ws.MergeCells.Cells) > idx {
672 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
673 ws.MergeCells.Count = len(ws.MergeCells.Cells)
677 // adjustCellName returns updated cell name by giving column/row number and
678 // offset on inserting or deleting rows or columns.
679 func adjustCellName(cell string, dir adjustDirection, c, r, offset int) (string, error) {
681 if rn := r + offset; rn > 0 {
682 return CoordinatesToCellName(c, rn)
685 return CoordinatesToCellName(c+offset, r)
688 // adjustCalcChain provides a function to update the calculation chain when
689 // inserting or deleting rows or columns.
690 func (f *File) adjustCalcChain(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
691 if f.CalcChain == nil {
694 // If sheet ID is omitted, it is assumed to be the same as the i value of
695 // the previous cell.
697 for i := 0; i < len(f.CalcChain.C); i++ {
698 c := f.CalcChain.C[i]
706 colNum, rowNum, err := CellNameToCoordinates(c.R)
710 if dir == rows && num <= rowNum {
711 if num == rowNum && offset == -1 {
712 _ = f.deleteCalcChain(c.I, c.R)
716 f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
718 if dir == columns && num <= colNum {
719 if num == colNum && offset == -1 {
720 _ = f.deleteCalcChain(c.I, c.R)
724 f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
730 // adjustVolatileDepsTopic updates the volatile dependencies topic when
731 // inserting or deleting rows or columns.
732 func (vt *xlsxVolTypes) adjustVolatileDepsTopic(cell string, dir adjustDirection, indexes []int) (int, error) {
733 num, offset, i1, i2, i3, i4 := indexes[0], indexes[1], indexes[2], indexes[3], indexes[4], indexes[5]
734 colNum, rowNum, err := CellNameToCoordinates(cell)
738 if dir == rows && num <= rowNum {
739 if num == rowNum && offset == -1 {
740 vt.deleteVolTopicRef(i1, i2, i3, i4)
744 vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
746 if dir == columns && num <= colNum {
747 if num == colNum && offset == -1 {
748 vt.deleteVolTopicRef(i1, i2, i3, i4)
752 if name, _ := adjustCellName(cell, dir, colNum, rowNum, offset); name != "" {
753 vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
759 // adjustVolatileDeps updates the volatile dependencies when inserting or
760 // deleting rows or columns.
761 func (f *File) adjustVolatileDeps(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
762 volTypes, err := f.volatileDepsReader()
763 if err != nil || volTypes == nil {
766 for i1 := 0; i1 < len(volTypes.VolType); i1++ {
767 for i2 := 0; i2 < len(volTypes.VolType[i1].Main); i2++ {
768 for i3 := 0; i3 < len(volTypes.VolType[i1].Main[i2].Tp); i3++ {
769 for i4 := 0; i4 < len(volTypes.VolType[i1].Main[i2].Tp[i3].Tr); i4++ {
770 ref := volTypes.VolType[i1].Main[i2].Tp[i3].Tr[i4]
771 if ref.S != sheetID {
774 if i4, err = volTypes.adjustVolatileDepsTopic(ref.R, dir, []int{num, offset, i1, i2, i3, i4}); err != nil {
784 // adjustConditionalFormats updates the cell reference of the worksheet
785 // conditional formatting when inserting or deleting rows or columns.
786 func (f *File) adjustConditionalFormats(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
787 for i := 0; i < len(ws.ConditionalFormatting); i++ {
788 cf := ws.ConditionalFormatting[i]
792 ref, del, err := f.adjustCellRef(cf.SQRef, dir, num, offset)
797 ws.ConditionalFormatting = append(ws.ConditionalFormatting[:i],
798 ws.ConditionalFormatting[i+1:]...)
802 ws.ConditionalFormatting[i].SQRef = ref
807 // adjustDrawings updates the starting anchor of the two cell anchor pictures
808 // and charts object when inserting or deleting rows or columns.
809 func (from *xlsxFrom) adjustDrawings(dir adjustDirection, num, offset int, editAs string) (bool, error) {
811 if dir == columns && from.Col+1 >= num && from.Col+offset >= 0 {
812 if from.Col+offset >= MaxColumns {
813 return false, ErrColumnNumber
816 ok = editAs == "oneCell"
818 if dir == rows && from.Row+1 >= num && from.Row+offset >= 0 {
819 if from.Row+offset >= TotalRows {
820 return false, ErrMaxRows
823 ok = editAs == "oneCell"
828 // adjustDrawings updates the ending anchor of the two cell anchor pictures
829 // and charts object when inserting or deleting rows or columns.
830 func (to *xlsxTo) adjustDrawings(dir adjustDirection, num, offset int, editAs string, ok bool) error {
831 if dir == columns && to.Col+1 >= num && to.Col+offset >= 0 && ok {
832 if to.Col+offset >= MaxColumns {
833 return ErrColumnNumber
837 if dir == rows && to.Row+1 >= num && to.Row+offset >= 0 && ok {
838 if to.Row+offset >= TotalRows {
846 // adjustDrawings updates the two cell anchor pictures and charts object when
847 // inserting or deleting rows or columns.
848 func (a *xdrCellAnchor) adjustDrawings(dir adjustDirection, num, offset int) error {
850 if a.From == nil || a.To == nil || editAs == "absolute" {
853 ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
857 return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
860 // adjustDrawings updates the existing two cell anchor pictures and charts
861 // object when inserting or deleting rows or columns.
862 func (a *xlsxCellAnchorPos) adjustDrawings(dir adjustDirection, num, offset int, editAs string) error {
863 if a.From == nil || a.To == nil || editAs == "absolute" {
866 ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
870 return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
873 // adjustDrawings updates the pictures and charts object when inserting or
874 // deleting rows or columns.
875 func (f *File) adjustDrawings(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
876 if ws.Drawing == nil {
879 target := f.getSheetRelationshipsTargetByID(sheet, ws.Drawing.RID)
880 drawingXML := strings.TrimPrefix(strings.ReplaceAll(target, "..", "xl"), "/")
885 if wsDr, _, err = f.drawingParser(drawingXML); err != nil {
888 anchorCb := func(a *xdrCellAnchor) error {
889 if a.GraphicFrame == "" {
890 return a.adjustDrawings(dir, num, offset)
892 deCellAnchor := decodeCellAnchor{}
893 deCellAnchorPos := decodeCellAnchorPos{}
894 _ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchor>" + a.GraphicFrame + "</decodeCellAnchor>")).Decode(&deCellAnchor)
895 _ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchorPos>" + a.GraphicFrame + "</decodeCellAnchorPos>")).Decode(&deCellAnchorPos)
896 xlsxCellAnchorPos := xlsxCellAnchorPos(deCellAnchorPos)
897 for i := 0; i < len(xlsxCellAnchorPos.AlternateContent); i++ {
898 xlsxCellAnchorPos.AlternateContent[i].XMLNSMC = SourceRelationshipCompatibility.Value
900 if deCellAnchor.From != nil {
901 xlsxCellAnchorPos.From = &xlsxFrom{
902 Col: deCellAnchor.From.Col, ColOff: deCellAnchor.From.ColOff,
903 Row: deCellAnchor.From.Row, RowOff: deCellAnchor.From.RowOff,
906 if deCellAnchor.To != nil {
907 xlsxCellAnchorPos.To = &xlsxTo{
908 Col: deCellAnchor.To.Col, ColOff: deCellAnchor.To.ColOff,
909 Row: deCellAnchor.To.Row, RowOff: deCellAnchor.To.RowOff,
912 if err = xlsxCellAnchorPos.adjustDrawings(dir, num, offset, a.EditAs); err != nil {
915 cellAnchor, _ := xml.Marshal(xlsxCellAnchorPos)
916 a.GraphicFrame = strings.TrimSuffix(strings.TrimPrefix(string(cellAnchor), "<xlsxCellAnchorPos>"), "</xlsxCellAnchorPos>")
919 for _, anchor := range wsDr.TwoCellAnchor {
920 if err = anchorCb(anchor); err != nil {
927 // adjustDefinedNames updates the cell reference of the defined names when
928 // inserting or deleting rows or columns.
929 func (f *File) adjustDefinedNames(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
930 wb, err := f.workbookReader()
934 if wb.DefinedNames != nil {
935 for i := 0; i < len(wb.DefinedNames.DefinedName); i++ {
936 data := wb.DefinedNames.DefinedName[i].Data
937 if data, err = f.adjustFormulaRef(sheet, "", data, true, dir, num, offset); err == nil {
938 wb.DefinedNames.DefinedName[i].Data = data