1 // Copyright 2016 - 2022 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
6 // and read from XLSX / XLSM / XLTM files. Supports reading and writing
7 // spreadsheet documents generated by Microsoft Excelâ„¢ 2007 and later. Supports
8 // complex components by high compatibility, and provided streaming API for
9 // generating or reading data from a worksheet with huge amounts of data. This
10 // library needs Go version 1.15 or later.
14 type adjustDirection bool
17 columns adjustDirection = false
18 rows adjustDirection = true
21 // adjustHelper provides a function to adjust rows and columns dimensions,
22 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
25 // sheet: Worksheet name that we're editing
26 // column: Index number of the column we're inserting/deleting before
27 // row: Index number of the row we're inserting/deleting before
28 // offset: Number of rows/column to insert/delete negative values indicate deletion
30 // TODO: adjustPageBreaks, adjustComments, adjustDataValidations, adjustProtectedCells
32 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
33 ws, err := f.workSheetReader(sheet)
37 sheetID := f.getSheetID(sheet)
39 f.adjustRowDimensions(ws, num, offset)
41 f.adjustColDimensions(ws, num, offset)
43 f.adjustHyperlinks(ws, sheet, dir, num, offset)
44 if err = f.adjustMergeCells(ws, dir, num, offset); err != nil {
47 if err = f.adjustAutoFilter(ws, dir, num, offset); err != nil {
50 if err = f.adjustCalcChain(dir, num, offset, sheetID); err != nil {
56 if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
63 // adjustColDimensions provides a function to update column dimensions when
64 // inserting or deleting rows or columns.
65 func (f *File) adjustColDimensions(ws *xlsxWorksheet, col, offset int) {
66 for rowIdx := range ws.SheetData.Row {
67 for colIdx, v := range ws.SheetData.Row[rowIdx].C {
68 cellCol, cellRow, _ := CellNameToCoordinates(v.R)
70 if newCol := cellCol + offset; newCol > 0 {
71 ws.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
78 // adjustRowDimensions provides a function to update row dimensions when
79 // inserting or deleting rows or columns.
80 func (f *File) adjustRowDimensions(ws *xlsxWorksheet, row, offset int) {
81 for i := range ws.SheetData.Row {
82 r := &ws.SheetData.Row[i]
83 if newRow := r.R + offset; r.R >= row && newRow > 0 {
84 f.adjustSingleRowDimensions(r, newRow)
89 // adjustSingleRowDimensions provides a function to adjust single row dimensions.
90 func (f *File) adjustSingleRowDimensions(r *xlsxRow, num int) {
92 for i, col := range r.C {
93 colName, _, _ := SplitCellName(col.R)
94 r.C[i].R, _ = JoinCellName(colName, num)
98 // adjustHyperlinks provides a function to update hyperlinks when inserting or
99 // deleting rows or columns.
100 func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
102 if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
106 // order is important
108 for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
109 linkData := ws.Hyperlinks.Hyperlink[i]
110 colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
112 if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
113 f.deleteSheetRelationships(sheet, linkData.RID)
114 if len(ws.Hyperlinks.Hyperlink) > 1 {
115 ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
116 ws.Hyperlinks.Hyperlink[i+1:]...)
123 if ws.Hyperlinks == nil {
126 for i := range ws.Hyperlinks.Hyperlink {
127 link := &ws.Hyperlinks.Hyperlink[i] // get reference
128 colNum, rowNum, _ := CellNameToCoordinates(link.Ref)
131 link.Ref, _ = CoordinatesToCellName(colNum, rowNum+offset)
135 link.Ref, _ = CoordinatesToCellName(colNum+offset, rowNum)
141 // adjustAutoFilter provides a function to update the auto filter when
142 // inserting or deleting rows or columns.
143 func (f *File) adjustAutoFilter(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
144 if ws.AutoFilter == nil {
148 coordinates, err := areaRefToCoordinates(ws.AutoFilter.Ref)
152 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
154 if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
156 for rowIdx := range ws.SheetData.Row {
157 rowData := &ws.SheetData.Row[rowIdx]
158 if rowData.R > y1 && rowData.R <= y2 {
159 rowData.Hidden = false
165 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
166 x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
168 if ws.AutoFilter.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
174 // adjustAutoFilterHelper provides a function for adjusting auto filter to
175 // compare and calculate cell axis by the given adjust direction, operation
177 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
179 if coordinates[1] >= num {
180 coordinates[1] += offset
182 if coordinates[3] >= num {
183 coordinates[3] += offset
186 if coordinates[2] >= num {
187 coordinates[2] += offset
193 // adjustMergeCells provides a function to update merged cells when inserting
194 // or deleting rows or columns.
195 func (f *File) adjustMergeCells(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
196 if ws.MergeCells == nil {
200 for i := 0; i < len(ws.MergeCells.Cells); i++ {
201 areaData := ws.MergeCells.Cells[i]
202 coordinates, err := areaRefToCoordinates(areaData.Ref)
206 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
208 if y1 == num && y2 == num && offset < 0 {
209 f.deleteMergeCell(ws, i)
214 y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
216 if x1 == num && x2 == num && offset < 0 {
217 f.deleteMergeCell(ws, i)
222 x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
224 if x1 == x2 && y1 == y2 {
225 f.deleteMergeCell(ws, i)
229 if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
236 // adjustMergeCellsHelper provides a function for adjusting merge cells to
237 // compare and calculate cell axis by the given pivot, operation axis and
239 func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
248 } else if num <= p2 {
253 if num < p1 || (num == p1 && num == p2) {
256 } else if num <= p2 {
262 // deleteMergeCell provides a function to delete merged cell by given index.
263 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
267 if len(ws.MergeCells.Cells) > idx {
268 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
269 ws.MergeCells.Count = len(ws.MergeCells.Cells)
273 // adjustCalcChain provides a function to update the calculation chain when
274 // inserting or deleting rows or columns.
275 func (f *File) adjustCalcChain(dir adjustDirection, num, offset, sheetID int) error {
276 if f.CalcChain == nil {
279 for index, c := range f.CalcChain.C {
283 colNum, rowNum, err := CellNameToCoordinates(c.R)
287 if dir == rows && num <= rowNum {
288 if newRow := rowNum + offset; newRow > 0 {
289 f.CalcChain.C[index].R, _ = CoordinatesToCellName(colNum, newRow)
292 if dir == columns && num <= colNum {
293 if newCol := colNum + offset; newCol > 0 {
294 f.CalcChain.C[index].R, _ = CoordinatesToCellName(newCol, rowNum)