1 // Copyright 2016 - 2019 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 files. Support reads and writes XLSX file generated by
7 // Microsoft Excelâ„¢ 2007 and later. Support save file without losing original
8 // charts of XLSX. This library needs Go version 1.10 or later.
17 type adjustDirection bool
20 columns adjustDirection = false
21 rows adjustDirection = true
24 // adjustHelper provides a function to adjust rows and columns dimensions,
25 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
28 // sheet: Worksheet name that we're editing
29 // column: Index number of the column we're inserting/deleting before
30 // row: Index number of the row we're inserting/deleting before
31 // offset: Number of rows/column to insert/delete negative values indicate deletion
33 // TODO: adjustPageBreaks, adjustComments, adjustDataValidations, adjustProtectedCells
35 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
36 xlsx, err := f.workSheetReader(sheet)
41 f.adjustRowDimensions(xlsx, num, offset)
43 f.adjustColDimensions(xlsx, num, offset)
45 f.adjustHyperlinks(xlsx, sheet, dir, num, offset)
46 if err = f.adjustMergeCells(xlsx, dir, num, offset); err != nil {
49 if err = f.adjustAutoFilter(xlsx, dir, num, offset); err != nil {
52 if err = f.adjustCalcChain(dir, num, offset); err != nil {
58 if xlsx.MergeCells != nil && len(xlsx.MergeCells.Cells) == 0 {
65 // adjustColDimensions provides a function to update column dimensions when
66 // inserting or deleting rows or columns.
67 func (f *File) adjustColDimensions(xlsx *xlsxWorksheet, col, offset int) {
68 for rowIdx := range xlsx.SheetData.Row {
69 for colIdx, v := range xlsx.SheetData.Row[rowIdx].C {
70 cellCol, cellRow, _ := CellNameToCoordinates(v.R)
72 if newCol := cellCol + offset; newCol > 0 {
73 xlsx.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
80 // adjustRowDimensions provides a function to update row dimensions when
81 // inserting or deleting rows or columns.
82 func (f *File) adjustRowDimensions(xlsx *xlsxWorksheet, row, offset int) {
83 for i, r := range xlsx.SheetData.Row {
84 if newRow := r.R + offset; r.R >= row && newRow > 0 {
85 f.ajustSingleRowDimensions(&xlsx.SheetData.Row[i], newRow)
90 // ajustSingleRowDimensions provides a function to ajust single row dimensions.
91 func (f *File) ajustSingleRowDimensions(r *xlsxRow, num int) {
93 for i, col := range r.C {
94 colName, _, _ := SplitCellName(col.R)
95 r.C[i].R, _ = JoinCellName(colName, num)
99 // adjustHyperlinks provides a function to update hyperlinks when inserting or
100 // deleting rows or columns.
101 func (f *File) adjustHyperlinks(xlsx *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
103 if xlsx.Hyperlinks == nil || len(xlsx.Hyperlinks.Hyperlink) == 0 {
107 // order is important
109 for rowIdx, linkData := range xlsx.Hyperlinks.Hyperlink {
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(xlsx.Hyperlinks.Hyperlink) > 1 {
115 xlsx.Hyperlinks.Hyperlink = append(xlsx.Hyperlinks.Hyperlink[:rowIdx],
116 xlsx.Hyperlinks.Hyperlink[rowIdx+1:]...)
118 xlsx.Hyperlinks = nil
124 if xlsx.Hyperlinks == nil {
128 for i := range xlsx.Hyperlinks.Hyperlink {
129 link := &xlsx.Hyperlinks.Hyperlink[i] // get reference
130 colNum, rowNum, _ := CellNameToCoordinates(link.Ref)
134 link.Ref, _ = CoordinatesToCellName(colNum, rowNum+offset)
138 link.Ref, _ = CoordinatesToCellName(colNum+offset, rowNum)
144 // adjustAutoFilter provides a function to update the auto filter when
145 // inserting or deleting rows or columns.
146 func (f *File) adjustAutoFilter(xlsx *xlsxWorksheet, dir adjustDirection, num, offset int) error {
147 if xlsx.AutoFilter == nil {
151 coordinates, err := f.areaRefToCoordinates(xlsx.AutoFilter.Ref)
155 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
157 if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
158 xlsx.AutoFilter = nil
159 for rowIdx := range xlsx.SheetData.Row {
160 rowData := &xlsx.SheetData.Row[rowIdx]
161 if rowData.R > y1 && rowData.R <= y2 {
162 rowData.Hidden = false
168 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
169 x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
171 if xlsx.AutoFilter.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
177 // adjustAutoFilterHelper provides a function for adjusting auto filter to
178 // compare and calculate cell axis by the given adjust direction, operation
180 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
182 if coordinates[1] >= num {
183 coordinates[1] += offset
185 if coordinates[3] >= num {
186 coordinates[3] += offset
189 if coordinates[2] >= num {
190 coordinates[2] += offset
196 // areaRefToCoordinates provides a function to convert area reference to a
197 // pair of coordinates.
198 func (f *File) areaRefToCoordinates(ref string) ([]int, error) {
199 coordinates := make([]int, 4)
200 rng := strings.Split(ref, ":")
204 coordinates[0], coordinates[1], err = CellNameToCoordinates(firstCell)
206 return coordinates, err
208 coordinates[2], coordinates[3], err = CellNameToCoordinates(lastCell)
209 return coordinates, err
212 // coordinatesToAreaRef provides a function to convert a pair of coordinates
213 // to area reference.
214 func (f *File) coordinatesToAreaRef(coordinates []int) (string, error) {
215 if len(coordinates) != 4 {
216 return "", errors.New("coordinates length must be 4")
218 firstCell, err := CoordinatesToCellName(coordinates[0], coordinates[1])
222 lastCell, err := CoordinatesToCellName(coordinates[2], coordinates[3])
226 return firstCell + ":" + lastCell, err
229 // adjustMergeCells provides a function to update merged cells when inserting
230 // or deleting rows or columns.
231 func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, offset int) error {
232 if xlsx.MergeCells == nil {
236 for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
237 areaData := xlsx.MergeCells.Cells[i]
238 coordinates, err := f.areaRefToCoordinates(areaData.Ref)
242 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
244 if y1 == num && y2 == num && offset < 0 {
245 f.deleteMergeCell(xlsx, i)
248 y1 = f.adjustMergeCellsHelper(y1, num, offset)
249 y2 = f.adjustMergeCellsHelper(y2, num, offset)
251 if x1 == num && x2 == num && offset < 0 {
252 f.deleteMergeCell(xlsx, i)
255 x1 = f.adjustMergeCellsHelper(x1, num, offset)
256 x2 = f.adjustMergeCellsHelper(x2, num, offset)
258 if x1 == x2 && y1 == y2 {
259 f.deleteMergeCell(xlsx, i)
262 if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
269 // adjustMergeCellsHelper provides a function for adjusting merge cells to
270 // compare and calculate cell axis by the given pivot, operation axis and
272 func (f *File) adjustMergeCellsHelper(pivot, num, offset int) int {
283 // deleteMergeCell provides a function to delete merged cell by given index.
284 func (f *File) deleteMergeCell(sheet *xlsxWorksheet, idx int) {
285 if len(sheet.MergeCells.Cells) > idx {
286 sheet.MergeCells.Cells = append(sheet.MergeCells.Cells[:idx], sheet.MergeCells.Cells[idx+1:]...)
287 sheet.MergeCells.Count = len(sheet.MergeCells.Cells)
291 // adjustCalcChain provides a function to update the calculation chain when
292 // inserting or deleting rows or columns.
293 func (f *File) adjustCalcChain(dir adjustDirection, num, offset int) error {
294 if f.CalcChain == nil {
297 for index, c := range f.CalcChain.C {
298 colNum, rowNum, err := CellNameToCoordinates(c.R)
302 if dir == rows && num <= rowNum {
303 if newRow := rowNum + offset; newRow > 0 {
304 f.CalcChain.C[index].R, _ = CoordinatesToCellName(colNum, newRow)
307 if dir == columns && num <= colNum {
308 if newCol := colNum + offset; newCol > 0 {
309 f.CalcChain.C[index].R, _ = CoordinatesToCellName(newCol, rowNum)