adjust.go

   1 // Copyright 2016 - 2023 The excelize Authors. All rights reserved. Use of
   2 // this source code is governed by a BSD-style license that can be found in
   3 // the LICENSE file.
   4 //
   5 // Package excelize providing a set of functions that allow you to write to and
   6 // read from XLAM / XLSM / XLSX / XLTM / XLTX files. Supports reading and
   7 // writing spreadsheet documents generated by Microsoft Excel™ 2007 and later.
   8 // Supports complex components by high compatibility, and provided streaming
   9 // API for generating or reading data from a worksheet with huge amounts of
  10 // data. This library needs Go version 1.16 or later.
  11
  12 package excelize
  13
  14 import (
  15         "bytes"
  16         "encoding/xml"
  17         "io"
  18         "strings"
  19 )
  20
  21 type adjustDirection bool
  22
  23 const (
  24         columns adjustDirection = false
  25         rows    adjustDirection = true
  26 )
  27
  28 // adjustHelper provides a function to adjust rows and columns dimensions,
  29 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
  30 // columns.
  31 //
  32 // sheet: Worksheet name that we're editing
  33 // column: Index number of the column we're inserting/deleting before
  34 // row: Index number of the row we're inserting/deleting before
  35 // offset: Number of rows/column to insert/delete negative values indicate deletion
  36 //
  37 // TODO: adjustPageBreaks, adjustComments, adjustDataValidations, adjustProtectedCells
  38 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
  39         ws, err := f.workSheetReader(sheet)
  40         if err != nil {
  41                 return err
  42         }
  43         sheetID := f.getSheetID(sheet)
  44         if dir == rows {
  45                 err = f.adjustRowDimensions(ws, num, offset)
  46         } else {
  47                 err = f.adjustColDimensions(ws, num, offset)
  48         }
  49         if err != nil {
  50                 return err
  51         }
  52         f.adjustHyperlinks(ws, sheet, dir, num, offset)
  53         f.adjustTable(ws, sheet, dir, num, offset)
  54         if err = f.adjustMergeCells(ws, dir, num, offset); err != nil {
  55                 return err
  56         }
  57         if err = f.adjustAutoFilter(ws, dir, num, offset); err != nil {
  58                 return err
  59         }
  60         if err = f.adjustCalcChain(dir, num, offset, sheetID); err != nil {
  61                 return err
  62         }
  63         ws.checkSheet()
  64         _ = ws.checkRow()
  65
  66         if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
  67                 ws.MergeCells = nil
  68         }
  69
  70         return nil
  71 }
  72
  73 // adjustCols provides a function to update column style when inserting or
  74 // deleting columns.
  75 func (f *File) adjustCols(ws *xlsxWorksheet, col, offset int) error {
  76         if ws.Cols == nil {
  77                 return nil
  78         }
  79         for i := 0; i < len(ws.Cols.Col); i++ {
  80                 if offset > 0 {
  81                         if ws.Cols.Col[i].Max+1 == col {
  82                                 ws.Cols.Col[i].Max += offset
  83                                 continue
  84                         }
  85                         if ws.Cols.Col[i].Min >= col {
  86                                 ws.Cols.Col[i].Min += offset
  87                                 ws.Cols.Col[i].Max += offset
  88                                 continue
  89                         }
  90                         if ws.Cols.Col[i].Min < col && ws.Cols.Col[i].Max >= col {
  91                                 ws.Cols.Col[i].Max += offset
  92                         }
  93                 }
  94                 if offset < 0 {
  95                         if ws.Cols.Col[i].Min == col && ws.Cols.Col[i].Max == col {
  96                                 if len(ws.Cols.Col) > 1 {
  97                                         ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
  98                                 } else {
  99                                         ws.Cols.Col = nil
 100                                 }
 101                                 i--
 102                                 continue
 103                         }
 104                         if ws.Cols.Col[i].Min > col {
 105                                 ws.Cols.Col[i].Min += offset
 106                                 ws.Cols.Col[i].Max += offset
 107                                 continue
 108                         }
 109                         if ws.Cols.Col[i].Min <= col && ws.Cols.Col[i].Max >= col {
 110                                 ws.Cols.Col[i].Max += offset
 111                         }
 112                 }
 113         }
 114         return nil
 115 }
 116
 117 // adjustColDimensions provides a function to update column dimensions when
 118 // inserting or deleting rows or columns.
 119 func (f *File) adjustColDimensions(ws *xlsxWorksheet, col, offset int) error {
 120         for rowIdx := range ws.SheetData.Row {
 121                 for _, v := range ws.SheetData.Row[rowIdx].C {
 122                         if cellCol, _, _ := CellNameToCoordinates(v.R); col <= cellCol {
 123                                 if newCol := cellCol + offset; newCol > 0 && newCol > MaxColumns {
 124                                         return ErrColumnNumber
 125                                 }
 126                         }
 127                 }
 128         }
 129         for rowIdx := range ws.SheetData.Row {
 130                 for colIdx, v := range ws.SheetData.Row[rowIdx].C {
 131                         if cellCol, cellRow, _ := CellNameToCoordinates(v.R); col <= cellCol {
 132                                 if newCol := cellCol + offset; newCol > 0 {
 133                                         ws.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
 134                                 }
 135                         }
 136                 }
 137         }
 138         return f.adjustCols(ws, col, offset)
 139 }
 140
 141 // adjustRowDimensions provides a function to update row dimensions when
 142 // inserting or deleting rows or columns.
 143 func (f *File) adjustRowDimensions(ws *xlsxWorksheet, row, offset int) error {
 144         totalRows := len(ws.SheetData.Row)
 145         if totalRows == 0 {
 146                 return nil
 147         }
 148         lastRow := &ws.SheetData.Row[totalRows-1]
 149         if newRow := lastRow.R + offset; lastRow.R >= row && newRow > 0 && newRow >= TotalRows {
 150                 return ErrMaxRows
 151         }
 152         for i := 0; i < len(ws.SheetData.Row); i++ {
 153                 r := &ws.SheetData.Row[i]
 154                 if newRow := r.R + offset; r.R >= row && newRow > 0 {
 155                         f.adjustSingleRowDimensions(r, newRow)
 156                 }
 157         }
 158         return nil
 159 }
 160
 161 // adjustSingleRowDimensions provides a function to adjust single row dimensions.
 162 func (f *File) adjustSingleRowDimensions(r *xlsxRow, num int) {
 163         r.R = num
 164         for i, col := range r.C {
 165                 colName, _, _ := SplitCellName(col.R)
 166                 r.C[i].R, _ = JoinCellName(colName, num)
 167         }
 168 }
 169
 170 // adjustHyperlinks provides a function to update hyperlinks when inserting or
 171 // deleting rows or columns.
 172 func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
 173         // short path
 174         if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
 175                 return
 176         }
 177
 178         // order is important
 179         if offset < 0 {
 180                 for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
 181                         linkData := ws.Hyperlinks.Hyperlink[i]
 182                         colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
 183
 184                         if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
 185                                 f.deleteSheetRelationships(sheet, linkData.RID)
 186                                 if len(ws.Hyperlinks.Hyperlink) > 1 {
 187                                         ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
 188                                                 ws.Hyperlinks.Hyperlink[i+1:]...)
 189                                 } else {
 190                                         ws.Hyperlinks = nil
 191                                 }
 192                         }
 193                 }
 194         }
 195         if ws.Hyperlinks == nil {
 196                 return
 197         }
 198         for i := range ws.Hyperlinks.Hyperlink {
 199                 link := &ws.Hyperlinks.Hyperlink[i] // get reference
 200                 colNum, rowNum, _ := CellNameToCoordinates(link.Ref)
 201                 if dir == rows {
 202                         if rowNum >= num {
 203                                 link.Ref, _ = CoordinatesToCellName(colNum, rowNum+offset)
 204                         }
 205                 } else {
 206                         if colNum >= num {
 207                                 link.Ref, _ = CoordinatesToCellName(colNum+offset, rowNum)
 208                         }
 209                 }
 210         }
 211 }
 212
 213 // adjustTable provides a function to update the table when inserting or
 214 // deleting rows or columns.
 215 func (f *File) adjustTable(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
 216         if ws.TableParts == nil || len(ws.TableParts.TableParts) == 0 {
 217                 return
 218         }
 219         for idx := 0; idx < len(ws.TableParts.TableParts); idx++ {
 220                 tbl := ws.TableParts.TableParts[idx]
 221                 target := f.getSheetRelationshipsTargetByID(sheet, tbl.RID)
 222                 tableXML := strings.ReplaceAll(target, "..", "xl")
 223                 content, ok := f.Pkg.Load(tableXML)
 224                 if !ok {
 225                         continue
 226                 }
 227                 t := xlsxTable{}
 228                 if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
 229                         Decode(&t); err != nil && err != io.EOF {
 230                         return
 231                 }
 232                 coordinates, err := rangeRefToCoordinates(t.Ref)
 233                 if err != nil {
 234                         return
 235                 }
 236                 // Remove the table when deleting the header row of the table
 237                 if dir == rows && num == coordinates[0] {
 238                         ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
 239                         ws.TableParts.Count = len(ws.TableParts.TableParts)
 240                         idx--
 241                         continue
 242                 }
 243                 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
 244                 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 245                 if y2-y1 < 2 || x2-x1 < 1 {
 246                         ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
 247                         ws.TableParts.Count = len(ws.TableParts.TableParts)
 248                         idx--
 249                         continue
 250                 }
 251                 t.Ref, _ = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
 252                 if t.AutoFilter != nil {
 253                         t.AutoFilter.Ref = t.Ref
 254                 }
 255                 _, _ = f.setTableHeader(sheet, true, x1, y1, x2)
 256                 table, _ := xml.Marshal(t)
 257                 f.saveFileList(tableXML, table)
 258         }
 259 }
 260
 261 // adjustAutoFilter provides a function to update the auto filter when
 262 // inserting or deleting rows or columns.
 263 func (f *File) adjustAutoFilter(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
 264         if ws.AutoFilter == nil {
 265                 return nil
 266         }
 267
 268         coordinates, err := rangeRefToCoordinates(ws.AutoFilter.Ref)
 269         if err != nil {
 270                 return err
 271         }
 272         x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 273
 274         if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
 275                 ws.AutoFilter = nil
 276                 for rowIdx := range ws.SheetData.Row {
 277                         rowData := &ws.SheetData.Row[rowIdx]
 278                         if rowData.R > y1 && rowData.R <= y2 {
 279                                 rowData.Hidden = false
 280                         }
 281                 }
 282                 return err
 283         }
 284
 285         coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
 286         x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 287
 288         ws.AutoFilter.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
 289         return err
 290 }
 291
 292 // adjustAutoFilterHelper provides a function for adjusting auto filter to
 293 // compare and calculate cell reference by the given adjust direction, operation
 294 // reference and offset.
 295 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
 296         if dir == rows {
 297                 if coordinates[1] >= num {
 298                         coordinates[1] += offset
 299                 }
 300                 if coordinates[3] >= num {
 301                         coordinates[3] += offset
 302                 }
 303                 return coordinates
 304         }
 305         if coordinates[0] >= num {
 306                 coordinates[0] += offset
 307         }
 308         if coordinates[2] >= num {
 309                 coordinates[2] += offset
 310         }
 311         return coordinates
 312 }
 313
 314 // adjustMergeCells provides a function to update merged cells when inserting
 315 // or deleting rows or columns.
 316 func (f *File) adjustMergeCells(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
 317         if ws.MergeCells == nil {
 318                 return nil
 319         }
 320
 321         for i := 0; i < len(ws.MergeCells.Cells); i++ {
 322                 mergedCells := ws.MergeCells.Cells[i]
 323                 mergedCellsRef := mergedCells.Ref
 324                 if !strings.Contains(mergedCellsRef, ":") {
 325                         mergedCellsRef += ":" + mergedCellsRef
 326                 }
 327                 coordinates, err := rangeRefToCoordinates(mergedCellsRef)
 328                 if err != nil {
 329                         return err
 330                 }
 331                 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 332                 if dir == rows {
 333                         if y1 == num && y2 == num && offset < 0 {
 334                                 f.deleteMergeCell(ws, i)
 335                                 i--
 336                                 continue
 337                         }
 338
 339                         y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
 340                 } else {
 341                         if x1 == num && x2 == num && offset < 0 {
 342                                 f.deleteMergeCell(ws, i)
 343                                 i--
 344                                 continue
 345                         }
 346
 347                         x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
 348                 }
 349                 if x1 == x2 && y1 == y2 {
 350                         f.deleteMergeCell(ws, i)
 351                         i--
 352                         continue
 353                 }
 354                 mergedCells.rect = []int{x1, y1, x2, y2}
 355                 if mergedCells.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2}); err != nil {
 356                         return err
 357                 }
 358         }
 359         return nil
 360 }
 361
 362 // adjustMergeCellsHelper provides a function for adjusting merge cells to
 363 // compare and calculate cell reference by the given pivot, operation reference and
 364 // offset.
 365 func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
 366         if p2 < p1 {
 367                 p1, p2 = p2, p1
 368         }
 369
 370         if offset >= 0 {
 371                 if num <= p1 {
 372                         p1 += offset
 373                         p2 += offset
 374                 } else if num <= p2 {
 375                         p2 += offset
 376                 }
 377                 return p1, p2
 378         }
 379         if num < p1 || (num == p1 && num == p2) {
 380                 p1 += offset
 381                 p2 += offset
 382         } else if num <= p2 {
 383                 p2 += offset
 384         }
 385         return p1, p2
 386 }
 387
 388 // deleteMergeCell provides a function to delete merged cell by given index.
 389 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
 390         if idx < 0 {
 391                 return
 392         }
 393         if len(ws.MergeCells.Cells) > idx {
 394                 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
 395                 ws.MergeCells.Count = len(ws.MergeCells.Cells)
 396         }
 397 }
 398
 399 // adjustCalcChain provides a function to update the calculation chain when
 400 // inserting or deleting rows or columns.
 401 func (f *File) adjustCalcChain(dir adjustDirection, num, offset, sheetID int) error {
 402         if f.CalcChain == nil {
 403                 return nil
 404         }
 405         for index, c := range f.CalcChain.C {
 406                 if c.I != sheetID {
 407                         continue
 408                 }
 409                 colNum, rowNum, err := CellNameToCoordinates(c.R)
 410                 if err != nil {
 411                         return err
 412                 }
 413                 if dir == rows && num <= rowNum {
 414                         if newRow := rowNum + offset; newRow > 0 {
 415                                 f.CalcChain.C[index].R, _ = CoordinatesToCellName(colNum, newRow)
 416                         }
 417                 }
 418                 if dir == columns && num <= colNum {
 419                         if newCol := colNum + offset; newCol > 0 {
 420                                 f.CalcChain.C[index].R, _ = CoordinatesToCellName(newCol, rowNum)
 421                         }
 422                 }
 423         }
 424         return nil
 425 }