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 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 func sortCoordinates(coordinates []int) error {
217 if len(coordinates) != 4 {
218 return errors.New("coordinates length must be 4")
220 if coordinates[2] < coordinates[0] {
221 coordinates[2], coordinates[0] = coordinates[0], coordinates[2]
223 if coordinates[3] < coordinates[1] {
224 coordinates[3], coordinates[1] = coordinates[1], coordinates[3]
229 // coordinatesToAreaRef provides a function to convert a pair of coordinates
230 // to area reference.
231 func (f *File) coordinatesToAreaRef(coordinates []int) (string, error) {
232 if len(coordinates) != 4 {
233 return "", errors.New("coordinates length must be 4")
235 firstCell, err := CoordinatesToCellName(coordinates[0], coordinates[1])
239 lastCell, err := CoordinatesToCellName(coordinates[2], coordinates[3])
243 return firstCell + ":" + lastCell, err
246 // adjustMergeCells provides a function to update merged cells when inserting
247 // or deleting rows or columns.
248 func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, offset int) error {
249 if xlsx.MergeCells == nil {
253 for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
254 areaData := xlsx.MergeCells.Cells[i]
255 coordinates, err := f.areaRefToCoordinates(areaData.Ref)
259 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
261 if y1 == num && y2 == num && offset < 0 {
262 f.deleteMergeCell(xlsx, i)
265 y1 = f.adjustMergeCellsHelper(y1, num, offset)
266 y2 = f.adjustMergeCellsHelper(y2, num, offset)
268 if x1 == num && x2 == num && offset < 0 {
269 f.deleteMergeCell(xlsx, i)
272 x1 = f.adjustMergeCellsHelper(x1, num, offset)
273 x2 = f.adjustMergeCellsHelper(x2, num, offset)
275 if x1 == x2 && y1 == y2 {
276 f.deleteMergeCell(xlsx, i)
279 if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
286 // adjustMergeCellsHelper provides a function for adjusting merge cells to
287 // compare and calculate cell axis by the given pivot, operation axis and
289 func (f *File) adjustMergeCellsHelper(pivot, num, offset int) int {
300 // deleteMergeCell provides a function to delete merged cell by given index.
301 func (f *File) deleteMergeCell(sheet *xlsxWorksheet, idx int) {
302 if len(sheet.MergeCells.Cells) > idx {
303 sheet.MergeCells.Cells = append(sheet.MergeCells.Cells[:idx], sheet.MergeCells.Cells[idx+1:]...)
304 sheet.MergeCells.Count = len(sheet.MergeCells.Cells)
308 // adjustCalcChain provides a function to update the calculation chain when
309 // inserting or deleting rows or columns.
310 func (f *File) adjustCalcChain(dir adjustDirection, num, offset int) error {
311 if f.CalcChain == nil {
314 for index, c := range f.CalcChain.C {
315 colNum, rowNum, err := CellNameToCoordinates(c.R)
319 if dir == rows && num <= rowNum {
320 if newRow := rowNum + offset; newRow > 0 {
321 f.CalcChain.C[index].R, _ = CoordinatesToCellName(colNum, newRow)
324 if dir == columns && num <= colNum {
325 if newCol := colNum + offset; newCol > 0 {
326 f.CalcChain.C[index].R, _ = CoordinatesToCellName(newCol, rowNum)