adjust.go

   1 // Copyright 2016 - 2022 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.15 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         checkSheet(ws)
  64         _ = checkRow(ws)
  65
  66         if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
  67                 ws.MergeCells = nil
  68         }
  69
  70         return nil
  71 }
  72
  73 // adjustColDimensions provides a function to update column dimensions when
  74 // inserting or deleting rows or columns.
  75 func (f *File) adjustColDimensions(ws *xlsxWorksheet, col, offset int) error {
  76         for rowIdx := range ws.SheetData.Row {
  77                 for colIdx, v := range ws.SheetData.Row[rowIdx].C {
  78                         cellCol, cellRow, _ := CellNameToCoordinates(v.R)
  79                         if col <= cellCol {
  80                                 if newCol := cellCol + offset; newCol > 0 {
  81                                         if newCol > MaxColumns {
  82                                                 return ErrColumnNumber
  83                                         }
  84                                         ws.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
  85                                 }
  86                         }
  87                 }
  88         }
  89         return nil
  90 }
  91
  92 // adjustRowDimensions provides a function to update row dimensions when
  93 // inserting or deleting rows or columns.
  94 func (f *File) adjustRowDimensions(ws *xlsxWorksheet, row, offset int) error {
  95         for i := range ws.SheetData.Row {
  96                 r := &ws.SheetData.Row[i]
  97                 if newRow := r.R + offset; r.R >= row && newRow > 0 {
  98                         if newRow >= TotalRows {
  99                                 return ErrMaxRows
 100                         }
 101                         f.adjustSingleRowDimensions(r, newRow)
 102                 }
 103         }
 104         return nil
 105 }
 106
 107 // adjustSingleRowDimensions provides a function to adjust single row dimensions.
 108 func (f *File) adjustSingleRowDimensions(r *xlsxRow, num int) {
 109         r.R = num
 110         for i, col := range r.C {
 111                 colName, _, _ := SplitCellName(col.R)
 112                 r.C[i].R, _ = JoinCellName(colName, num)
 113         }
 114 }
 115
 116 // adjustHyperlinks provides a function to update hyperlinks when inserting or
 117 // deleting rows or columns.
 118 func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
 119         // short path
 120         if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
 121                 return
 122         }
 123
 124         // order is important
 125         if offset < 0 {
 126                 for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
 127                         linkData := ws.Hyperlinks.Hyperlink[i]
 128                         colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
 129
 130                         if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
 131                                 f.deleteSheetRelationships(sheet, linkData.RID)
 132                                 if len(ws.Hyperlinks.Hyperlink) > 1 {
 133                                         ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
 134                                                 ws.Hyperlinks.Hyperlink[i+1:]...)
 135                                 } else {
 136                                         ws.Hyperlinks = nil
 137                                 }
 138                         }
 139                 }
 140         }
 141         if ws.Hyperlinks == nil {
 142                 return
 143         }
 144         for i := range ws.Hyperlinks.Hyperlink {
 145                 link := &ws.Hyperlinks.Hyperlink[i] // get reference
 146                 colNum, rowNum, _ := CellNameToCoordinates(link.Ref)
 147                 if dir == rows {
 148                         if rowNum >= num {
 149                                 link.Ref, _ = CoordinatesToCellName(colNum, rowNum+offset)
 150                         }
 151                 } else {
 152                         if colNum >= num {
 153                                 link.Ref, _ = CoordinatesToCellName(colNum+offset, rowNum)
 154                         }
 155                 }
 156         }
 157 }
 158
 159 // adjustTable provides a function to update the table when inserting or
 160 // deleting rows or columns.
 161 func (f *File) adjustTable(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
 162         if ws.TableParts == nil || len(ws.TableParts.TableParts) == 0 {
 163                 return
 164         }
 165         for idx := 0; idx < len(ws.TableParts.TableParts); idx++ {
 166                 tbl := ws.TableParts.TableParts[idx]
 167                 target := f.getSheetRelationshipsTargetByID(sheet, tbl.RID)
 168                 tableXML := strings.ReplaceAll(target, "..", "xl")
 169                 content, ok := f.Pkg.Load(tableXML)
 170                 if !ok {
 171                         continue
 172                 }
 173                 t := xlsxTable{}
 174                 if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
 175                         Decode(&t); err != nil && err != io.EOF {
 176                         return
 177                 }
 178                 coordinates, err := areaRefToCoordinates(t.Ref)
 179                 if err != nil {
 180                         return
 181                 }
 182                 // Remove the table when deleting the header row of the table
 183                 if dir == rows && num == coordinates[0] {
 184                         ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
 185                         ws.TableParts.Count = len(ws.TableParts.TableParts)
 186                         idx--
 187                         continue
 188                 }
 189                 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
 190                 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 191                 if y2-y1 < 2 || x2-x1 < 1 {
 192                         ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
 193                         ws.TableParts.Count = len(ws.TableParts.TableParts)
 194                         idx--
 195                         continue
 196                 }
 197                 t.Ref, _ = f.coordinatesToAreaRef([]int{x1, y1, x2, y2})
 198                 if t.AutoFilter != nil {
 199                         t.AutoFilter.Ref = t.Ref
 200                 }
 201                 _, _ = f.setTableHeader(sheet, x1, y1, x2)
 202                 table, _ := xml.Marshal(t)
 203                 f.saveFileList(tableXML, table)
 204         }
 205 }
 206
 207 // adjustAutoFilter provides a function to update the auto filter when
 208 // inserting or deleting rows or columns.
 209 func (f *File) adjustAutoFilter(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
 210         if ws.AutoFilter == nil {
 211                 return nil
 212         }
 213
 214         coordinates, err := areaRefToCoordinates(ws.AutoFilter.Ref)
 215         if err != nil {
 216                 return err
 217         }
 218         x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 219
 220         if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
 221                 ws.AutoFilter = nil
 222                 for rowIdx := range ws.SheetData.Row {
 223                         rowData := &ws.SheetData.Row[rowIdx]
 224                         if rowData.R > y1 && rowData.R <= y2 {
 225                                 rowData.Hidden = false
 226                         }
 227                 }
 228                 return nil
 229         }
 230
 231         coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
 232         x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 233
 234         if ws.AutoFilter.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
 235                 return err
 236         }
 237         return nil
 238 }
 239
 240 // adjustAutoFilterHelper provides a function for adjusting auto filter to
 241 // compare and calculate cell axis by the given adjust direction, operation
 242 // axis and offset.
 243 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
 244         if dir == rows {
 245                 if coordinates[1] >= num {
 246                         coordinates[1] += offset
 247                 }
 248                 if coordinates[3] >= num {
 249                         coordinates[3] += offset
 250                 }
 251                 return coordinates
 252         }
 253         if coordinates[0] >= num {
 254                 coordinates[0] += offset
 255         }
 256         if coordinates[2] >= num {
 257                 coordinates[2] += offset
 258         }
 259         return coordinates
 260 }
 261
 262 // adjustMergeCells provides a function to update merged cells when inserting
 263 // or deleting rows or columns.
 264 func (f *File) adjustMergeCells(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
 265         if ws.MergeCells == nil {
 266                 return nil
 267         }
 268
 269         for i := 0; i < len(ws.MergeCells.Cells); i++ {
 270                 areaData := ws.MergeCells.Cells[i]
 271                 coordinates, err := areaRefToCoordinates(areaData.Ref)
 272                 if err != nil {
 273                         return err
 274                 }
 275                 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 276                 if dir == rows {
 277                         if y1 == num && y2 == num && offset < 0 {
 278                                 f.deleteMergeCell(ws, i)
 279                                 i--
 280                                 continue
 281                         }
 282
 283                         y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
 284                 } else {
 285                         if x1 == num && x2 == num && offset < 0 {
 286                                 f.deleteMergeCell(ws, i)
 287                                 i--
 288                                 continue
 289                         }
 290
 291                         x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
 292                 }
 293                 if x1 == x2 && y1 == y2 {
 294                         f.deleteMergeCell(ws, i)
 295                         i--
 296                         continue
 297                 }
 298                 areaData.rect = []int{x1, y1, x2, y2}
 299                 if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil {
 300                         return err
 301                 }
 302         }
 303         return nil
 304 }
 305
 306 // adjustMergeCellsHelper provides a function for adjusting merge cells to
 307 // compare and calculate cell axis by the given pivot, operation axis and
 308 // offset.
 309 func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
 310         if p2 < p1 {
 311                 p1, p2 = p2, p1
 312         }
 313
 314         if offset >= 0 {
 315                 if num <= p1 {
 316                         p1 += offset
 317                         p2 += offset
 318                 } else if num <= p2 {
 319                         p2 += offset
 320                 }
 321                 return p1, p2
 322         }
 323         if num < p1 || (num == p1 && num == p2) {
 324                 p1 += offset
 325                 p2 += offset
 326         } else if num <= p2 {
 327                 p2 += offset
 328         }
 329         return p1, p2
 330 }
 331
 332 // deleteMergeCell provides a function to delete merged cell by given index.
 333 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
 334         if idx < 0 {
 335                 return
 336         }
 337         if len(ws.MergeCells.Cells) > idx {
 338                 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
 339                 ws.MergeCells.Count = len(ws.MergeCells.Cells)
 340         }
 341 }
 342
 343 // adjustCalcChain provides a function to update the calculation chain when
 344 // inserting or deleting rows or columns.
 345 func (f *File) adjustCalcChain(dir adjustDirection, num, offset, sheetID int) error {
 346         if f.CalcChain == nil {
 347                 return nil
 348         }
 349         for index, c := range f.CalcChain.C {
 350                 if c.I != sheetID {
 351                         continue
 352                 }
 353                 colNum, rowNum, err := CellNameToCoordinates(c.R)
 354                 if err != nil {
 355                         return err
 356                 }
 357                 if dir == rows && num <= rowNum {
 358                         if newRow := rowNum + offset; newRow > 0 {
 359                                 f.CalcChain.C[index].R, _ = CoordinatesToCellName(colNum, newRow)
 360                         }
 361                 }
 362                 if dir == columns && num <= colNum {
 363                         if newCol := colNum + offset; newCol > 0 {
 364                                 f.CalcChain.C[index].R, _ = CoordinatesToCellName(newCol, rowNum)
 365                         }
 366                 }
 367         }
 368         return nil
 369 }