1 // Copyright 2016 - 2021 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.
18 type adjustDirection bool
21 columns adjustDirection = false
22 rows adjustDirection = true
25 // adjustHelper provides a function to adjust rows and columns dimensions,
26 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
29 // sheet: Worksheet name that we're editing
30 // column: Index number of the column we're inserting/deleting before
31 // row: Index number of the row we're inserting/deleting before
32 // offset: Number of rows/column to insert/delete negative values indicate deletion
34 // TODO: adjustPageBreaks, adjustComments, adjustDataValidations, adjustProtectedCells
36 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
37 ws, err := f.workSheetReader(sheet)
41 sheetID := f.getSheetID(sheet)
43 f.adjustRowDimensions(ws, num, offset)
45 f.adjustColDimensions(ws, num, offset)
47 f.adjustHyperlinks(ws, sheet, dir, num, offset)
48 if err = f.adjustMergeCells(ws, dir, num, offset); err != nil {
51 if err = f.adjustAutoFilter(ws, dir, num, offset); err != nil {
54 if err = f.adjustCalcChain(dir, num, offset, sheetID); err != nil {
60 if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
67 // adjustColDimensions provides a function to update column dimensions when
68 // inserting or deleting rows or columns.
69 func (f *File) adjustColDimensions(ws *xlsxWorksheet, col, offset int) {
70 for rowIdx := range ws.SheetData.Row {
71 for colIdx, v := range ws.SheetData.Row[rowIdx].C {
72 cellCol, cellRow, _ := CellNameToCoordinates(v.R)
74 if newCol := cellCol + offset; newCol > 0 {
75 ws.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
82 // adjustRowDimensions provides a function to update row dimensions when
83 // inserting or deleting rows or columns.
84 func (f *File) adjustRowDimensions(ws *xlsxWorksheet, row, offset int) {
85 for i := range ws.SheetData.Row {
86 r := &ws.SheetData.Row[i]
87 if newRow := r.R + offset; r.R >= row && newRow > 0 {
88 f.ajustSingleRowDimensions(r, newRow)
93 // ajustSingleRowDimensions provides a function to ajust single row dimensions.
94 func (f *File) ajustSingleRowDimensions(r *xlsxRow, num int) {
96 for i, col := range r.C {
97 colName, _, _ := SplitCellName(col.R)
98 r.C[i].R, _ = JoinCellName(colName, num)
102 // adjustHyperlinks provides a function to update hyperlinks when inserting or
103 // deleting rows or columns.
104 func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
106 if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
110 // order is important
112 for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
113 linkData := ws.Hyperlinks.Hyperlink[i]
114 colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
116 if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
117 f.deleteSheetRelationships(sheet, linkData.RID)
118 if len(ws.Hyperlinks.Hyperlink) > 1 {
119 ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
120 ws.Hyperlinks.Hyperlink[i+1:]...)
127 if ws.Hyperlinks == nil {
130 for i := range ws.Hyperlinks.Hyperlink {
131 link := &ws.Hyperlinks.Hyperlink[i] // get reference
132 colNum, rowNum, _ := CellNameToCoordinates(link.Ref)
135 link.Ref, _ = CoordinatesToCellName(colNum, rowNum+offset)
139 link.Ref, _ = CoordinatesToCellName(colNum+offset, rowNum)
145 // adjustAutoFilter provides a function to update the auto filter when
146 // inserting or deleting rows or columns.
147 func (f *File) adjustAutoFilter(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
148 if ws.AutoFilter == nil {
152 coordinates, err := f.areaRefToCoordinates(ws.AutoFilter.Ref)
156 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
158 if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
160 for rowIdx := range ws.SheetData.Row {
161 rowData := &ws.SheetData.Row[rowIdx]
162 if rowData.R > y1 && rowData.R <= y2 {
163 rowData.Hidden = false
169 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
170 x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
172 if ws.AutoFilter.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
178 // adjustAutoFilterHelper provides a function for adjusting auto filter to
179 // compare and calculate cell axis by the given adjust direction, operation
181 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
183 if coordinates[1] >= num {
184 coordinates[1] += offset
186 if coordinates[3] >= num {
187 coordinates[3] += offset
190 if coordinates[2] >= num {
191 coordinates[2] += offset
197 // areaRefToCoordinates provides a function to convert area reference to a
198 // pair of coordinates.
199 func (f *File) areaRefToCoordinates(ref string) ([]int, error) {
200 rng := strings.Split(strings.Replace(ref, "$", "", -1), ":")
202 return nil, ErrParameterInvalid
205 return areaRangeToCoordinates(rng[0], rng[1])
208 // areaRangeToCoordinates provides a function to convert cell range to a
209 // pair of coordinates.
210 func areaRangeToCoordinates(firstCell, lastCell string) ([]int, error) {
211 coordinates := make([]int, 4)
213 coordinates[0], coordinates[1], err = CellNameToCoordinates(firstCell)
215 return coordinates, err
217 coordinates[2], coordinates[3], err = CellNameToCoordinates(lastCell)
218 return coordinates, err
221 // sortCoordinates provides a function to correct the coordinate area, such
222 // correct C1:B3 to B1:C3.
223 func sortCoordinates(coordinates []int) error {
224 if len(coordinates) != 4 {
225 return ErrCoordinates
227 if coordinates[2] < coordinates[0] {
228 coordinates[2], coordinates[0] = coordinates[0], coordinates[2]
230 if coordinates[3] < coordinates[1] {
231 coordinates[3], coordinates[1] = coordinates[1], coordinates[3]
236 // coordinatesToAreaRef provides a function to convert a pair of coordinates
237 // to area reference.
238 func (f *File) coordinatesToAreaRef(coordinates []int) (string, error) {
239 if len(coordinates) != 4 {
240 return "", ErrCoordinates
242 firstCell, err := CoordinatesToCellName(coordinates[0], coordinates[1])
246 lastCell, err := CoordinatesToCellName(coordinates[2], coordinates[3])
250 return firstCell + ":" + lastCell, err
253 // adjustMergeCells provides a function to update merged cells when inserting
254 // or deleting rows or columns.
255 func (f *File) adjustMergeCells(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
256 if ws.MergeCells == nil {
260 for i := 0; i < len(ws.MergeCells.Cells); i++ {
261 areaData := ws.MergeCells.Cells[i]
262 coordinates, err := f.areaRefToCoordinates(areaData.Ref)
266 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
268 if y1 == num && y2 == num && offset < 0 {
269 f.deleteMergeCell(ws, i)
272 y1 = f.adjustMergeCellsHelper(y1, num, offset)
273 y2 = f.adjustMergeCellsHelper(y2, num, offset)
275 if x1 == num && x2 == num && offset < 0 {
276 f.deleteMergeCell(ws, i)
279 x1 = f.adjustMergeCellsHelper(x1, num, offset)
280 x2 = f.adjustMergeCellsHelper(x2, num, offset)
282 if x1 == x2 && y1 == y2 {
283 f.deleteMergeCell(ws, i)
286 if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
293 // adjustMergeCellsHelper provides a function for adjusting merge cells to
294 // compare and calculate cell axis by the given pivot, operation axis and
296 func (f *File) adjustMergeCellsHelper(pivot, num, offset int) int {
307 // deleteMergeCell provides a function to delete merged cell by given index.
308 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
309 if len(ws.MergeCells.Cells) > idx {
310 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
311 ws.MergeCells.Count = len(ws.MergeCells.Cells)
315 // adjustCalcChain provides a function to update the calculation chain when
316 // inserting or deleting rows or columns.
317 func (f *File) adjustCalcChain(dir adjustDirection, num, offset, sheetID int) error {
318 if f.CalcChain == nil {
321 for index, c := range f.CalcChain.C {
325 colNum, rowNum, err := CellNameToCoordinates(c.R)
329 if dir == rows && num <= rowNum {
330 if newRow := rowNum + offset; newRow > 0 {
331 f.CalcChain.C[index].R, _ = CoordinatesToCellName(colNum, newRow)
334 if dir == columns && num <= colNum {
335 if newCol := colNum + offset; newCol > 0 {
336 f.CalcChain.C[index].R, _ = CoordinatesToCellName(newCol, rowNum)