1 // Copyright 2016 - 2020 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 rng := strings.Split(ref, ":")
200 return areaRangeToCoordinates(rng[0], rng[1])
203 // areaRangeToCoordinates provides a function to convert cell range to a
204 // pair of coordinates.
205 func areaRangeToCoordinates(firstCell, lastCell string) ([]int, error) {
206 coordinates := make([]int, 4)
208 coordinates[0], coordinates[1], err = CellNameToCoordinates(firstCell)
210 return coordinates, err
212 coordinates[2], coordinates[3], err = CellNameToCoordinates(lastCell)
213 return coordinates, err
216 // sortCoordinates provides a function to correct the coordinate area, such
217 // correct C1:B3 to B1:C3.
218 func sortCoordinates(coordinates []int) error {
219 if len(coordinates) != 4 {
220 return errors.New("coordinates length must be 4")
222 if coordinates[2] < coordinates[0] {
223 coordinates[2], coordinates[0] = coordinates[0], coordinates[2]
225 if coordinates[3] < coordinates[1] {
226 coordinates[3], coordinates[1] = coordinates[1], coordinates[3]
231 // coordinatesToAreaRef provides a function to convert a pair of coordinates
232 // to area reference.
233 func (f *File) coordinatesToAreaRef(coordinates []int) (string, error) {
234 if len(coordinates) != 4 {
235 return "", errors.New("coordinates length must be 4")
237 firstCell, err := CoordinatesToCellName(coordinates[0], coordinates[1])
241 lastCell, err := CoordinatesToCellName(coordinates[2], coordinates[3])
245 return firstCell + ":" + lastCell, err
248 // adjustMergeCells provides a function to update merged cells when inserting
249 // or deleting rows or columns.
250 func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, offset int) error {
251 if xlsx.MergeCells == nil {
255 for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
256 areaData := xlsx.MergeCells.Cells[i]
257 coordinates, err := f.areaRefToCoordinates(areaData.Ref)
261 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
263 if y1 == num && y2 == num && offset < 0 {
264 f.deleteMergeCell(xlsx, i)
267 y1 = f.adjustMergeCellsHelper(y1, num, offset)
268 y2 = f.adjustMergeCellsHelper(y2, num, offset)
270 if x1 == num && x2 == num && offset < 0 {
271 f.deleteMergeCell(xlsx, i)
274 x1 = f.adjustMergeCellsHelper(x1, num, offset)
275 x2 = f.adjustMergeCellsHelper(x2, num, offset)
277 if x1 == x2 && y1 == y2 {
278 f.deleteMergeCell(xlsx, i)
281 if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
288 // adjustMergeCellsHelper provides a function for adjusting merge cells to
289 // compare and calculate cell axis by the given pivot, operation axis and
291 func (f *File) adjustMergeCellsHelper(pivot, num, offset int) int {
302 // deleteMergeCell provides a function to delete merged cell by given index.
303 func (f *File) deleteMergeCell(sheet *xlsxWorksheet, idx int) {
304 if len(sheet.MergeCells.Cells) > idx {
305 sheet.MergeCells.Cells = append(sheet.MergeCells.Cells[:idx], sheet.MergeCells.Cells[idx+1:]...)
306 sheet.MergeCells.Count = len(sheet.MergeCells.Cells)
310 // adjustCalcChain provides a function to update the calculation chain when
311 // inserting or deleting rows or columns.
312 func (f *File) adjustCalcChain(dir adjustDirection, num, offset int) error {
313 if f.CalcChain == nil {
316 for index, c := range f.CalcChain.C {
317 colNum, rowNum, err := CellNameToCoordinates(c.R)
321 if dir == rows && num <= rowNum {
322 if newRow := rowNum + offset; newRow > 0 {
323 f.CalcChain.C[index].R, _ = CoordinatesToCellName(colNum, newRow)
326 if dir == columns && num <= colNum {
327 if newCol := colNum + offset; newCol > 0 {
328 f.CalcChain.C[index].R, _ = CoordinatesToCellName(newCol, rowNum)