adjust.go

   1 // Copyright 2016 - 2024 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         "strconv"
  19         "strings"
  20         "unicode"
  21
  22         "github.com/xuri/efp"
  23 )
  24
  25 type adjustDirection bool
  26
  27 const (
  28         columns adjustDirection = false
  29         rows    adjustDirection = true
  30 )
  31
  32 // adjustHelperFunc defines functions to adjust helper.
  33 var adjustHelperFunc = [9]func(*File, *xlsxWorksheet, string, adjustDirection, int, int, int) error{
  34         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  35                 return f.adjustConditionalFormats(ws, sheet, dir, num, offset, sheetID)
  36         },
  37         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  38                 return f.adjustDataValidations(ws, sheet, dir, num, offset, sheetID)
  39         },
  40         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  41                 return f.adjustDefinedNames(ws, sheet, dir, num, offset, sheetID)
  42         },
  43         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  44                 return f.adjustDrawings(ws, sheet, dir, num, offset, sheetID)
  45         },
  46         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  47                 return f.adjustMergeCells(ws, sheet, dir, num, offset, sheetID)
  48         },
  49         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  50                 return f.adjustAutoFilter(ws, sheet, dir, num, offset, sheetID)
  51         },
  52         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  53                 return f.adjustCalcChain(ws, sheet, dir, num, offset, sheetID)
  54         },
  55         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  56                 return f.adjustTable(ws, sheet, dir, num, offset, sheetID)
  57         },
  58         func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
  59                 return f.adjustVolatileDeps(ws, sheet, dir, num, offset, sheetID)
  60         },
  61 }
  62
  63 // adjustHelper provides a function to adjust rows and columns dimensions,
  64 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
  65 // columns.
  66 //
  67 // sheet: Worksheet name that we're editing
  68 // column: Index number of the column we're inserting/deleting before
  69 // row: Index number of the row we're inserting/deleting before
  70 // offset: Number of rows/column to insert/delete negative values indicate deletion
  71 //
  72 // TODO: adjustComments, adjustPageBreaks, adjustProtectedCells
  73 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
  74         ws, err := f.workSheetReader(sheet)
  75         if err != nil {
  76                 return err
  77         }
  78         sheetID := f.getSheetID(sheet)
  79         if dir == rows {
  80                 err = f.adjustRowDimensions(sheet, ws, num, offset)
  81         } else {
  82                 err = f.adjustColDimensions(sheet, ws, num, offset)
  83         }
  84         if err != nil {
  85                 return err
  86         }
  87         f.adjustHyperlinks(ws, sheet, dir, num, offset)
  88         ws.checkSheet()
  89         _ = ws.checkRow()
  90         for _, fn := range adjustHelperFunc {
  91                 if err := fn(f, ws, sheet, dir, num, offset, sheetID); err != nil {
  92                         return err
  93                 }
  94         }
  95         if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
  96                 ws.MergeCells = nil
  97         }
  98         return nil
  99 }
 100
 101 // adjustCols provides a function to update column style when inserting or
 102 // deleting columns.
 103 func (f *File) adjustCols(ws *xlsxWorksheet, col, offset int) error {
 104         if ws.Cols == nil {
 105                 return nil
 106         }
 107         for i := 0; i < len(ws.Cols.Col); i++ {
 108                 if offset > 0 {
 109                         if ws.Cols.Col[i].Min >= col {
 110                                 if ws.Cols.Col[i].Min += offset; ws.Cols.Col[i].Min > MaxColumns {
 111                                         ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
 112                                         i--
 113                                         continue
 114                                 }
 115                         }
 116                         if ws.Cols.Col[i].Max >= col || ws.Cols.Col[i].Max+1 == col {
 117                                 if ws.Cols.Col[i].Max += offset; ws.Cols.Col[i].Max > MaxColumns {
 118                                         ws.Cols.Col[i].Max = MaxColumns
 119                                 }
 120                         }
 121                         continue
 122                 }
 123                 if ws.Cols.Col[i].Min == col && ws.Cols.Col[i].Max == col {
 124                         ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
 125                         i--
 126                         continue
 127                 }
 128                 if ws.Cols.Col[i].Min > col {
 129                         ws.Cols.Col[i].Min += offset
 130                 }
 131                 if ws.Cols.Col[i].Max >= col {
 132                         ws.Cols.Col[i].Max += offset
 133                 }
 134         }
 135         if len(ws.Cols.Col) == 0 {
 136                 ws.Cols = nil
 137         }
 138         return nil
 139 }
 140
 141 // adjustColDimensions provides a function to update column dimensions when
 142 // inserting or deleting rows or columns.
 143 func (f *File) adjustColDimensions(sheet string, ws *xlsxWorksheet, col, offset int) error {
 144         for rowIdx := range ws.SheetData.Row {
 145                 for _, v := range ws.SheetData.Row[rowIdx].C {
 146                         if cellCol, _, _ := CellNameToCoordinates(v.R); col <= cellCol {
 147                                 if newCol := cellCol + offset; newCol > 0 && newCol > MaxColumns {
 148                                         return ErrColumnNumber
 149                                 }
 150                         }
 151                 }
 152         }
 153         for _, sheetN := range f.GetSheetList() {
 154                 worksheet, err := f.workSheetReader(sheetN)
 155                 if err != nil {
 156                         if err.Error() == newNotWorksheetError(sheetN).Error() {
 157                                 continue
 158                         }
 159                         return err
 160                 }
 161                 for rowIdx := range worksheet.SheetData.Row {
 162                         for colIdx, v := range worksheet.SheetData.Row[rowIdx].C {
 163                                 if cellCol, cellRow, _ := CellNameToCoordinates(v.R); sheetN == sheet && col <= cellCol {
 164                                         if newCol := cellCol + offset; newCol > 0 {
 165                                                 worksheet.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
 166                                         }
 167                                 }
 168                                 if err := f.adjustFormula(sheet, sheetN, worksheet.SheetData.Row[rowIdx].C[colIdx].F, columns, col, offset, false); err != nil {
 169                                         return err
 170                                 }
 171                         }
 172                 }
 173         }
 174         return f.adjustCols(ws, col, offset)
 175 }
 176
 177 // adjustRowDimensions provides a function to update row dimensions when
 178 // inserting or deleting rows or columns.
 179 func (f *File) adjustRowDimensions(sheet string, ws *xlsxWorksheet, row, offset int) error {
 180         for _, sheetN := range f.GetSheetList() {
 181                 if sheetN == sheet {
 182                         continue
 183                 }
 184                 worksheet, err := f.workSheetReader(sheetN)
 185                 if err != nil {
 186                         if err.Error() == newNotWorksheetError(sheetN).Error() {
 187                                 continue
 188                         }
 189                         return err
 190                 }
 191                 numOfRows := len(worksheet.SheetData.Row)
 192                 for i := 0; i < numOfRows; i++ {
 193                         r := &worksheet.SheetData.Row[i]
 194                         if err = f.adjustSingleRowFormulas(sheet, sheetN, r, row, offset, false); err != nil {
 195                                 return err
 196                         }
 197                 }
 198         }
 199         totalRows := len(ws.SheetData.Row)
 200         if totalRows == 0 {
 201                 return nil
 202         }
 203         lastRow := &ws.SheetData.Row[totalRows-1]
 204         if newRow := *lastRow.R + offset; *lastRow.R >= row && newRow > 0 && newRow > TotalRows {
 205                 return ErrMaxRows
 206         }
 207         numOfRows := len(ws.SheetData.Row)
 208         for i := 0; i < numOfRows; i++ {
 209                 r := &ws.SheetData.Row[i]
 210                 if newRow := *r.R + offset; *r.R >= row && newRow > 0 {
 211                         r.adjustSingleRowDimensions(offset)
 212                 }
 213                 if err := f.adjustSingleRowFormulas(sheet, sheet, r, row, offset, false); err != nil {
 214                         return err
 215                 }
 216         }
 217         return nil
 218 }
 219
 220 // adjustSingleRowDimensions provides a function to adjust single row dimensions.
 221 func (r *xlsxRow) adjustSingleRowDimensions(offset int) {
 222         r.R = intPtr(*r.R + offset)
 223         for i, col := range r.C {
 224                 colName, _, _ := SplitCellName(col.R)
 225                 r.C[i].R, _ = JoinCellName(colName, *r.R)
 226         }
 227 }
 228
 229 // adjustSingleRowFormulas provides a function to adjust single row formulas.
 230 func (f *File) adjustSingleRowFormulas(sheet, sheetN string, r *xlsxRow, num, offset int, si bool) error {
 231         for _, col := range r.C {
 232                 if err := f.adjustFormula(sheet, sheetN, col.F, rows, num, offset, si); err != nil {
 233                         return err
 234                 }
 235         }
 236         return nil
 237 }
 238
 239 // adjustCellRef provides a function to adjust cell reference.
 240 func (f *File) adjustCellRef(ref string, dir adjustDirection, num, offset int) (string, bool, error) {
 241         if !strings.Contains(ref, ":") {
 242                 ref += ":" + ref
 243         }
 244         var delete bool
 245         coordinates, err := rangeRefToCoordinates(ref)
 246         if err != nil {
 247                 return ref, delete, err
 248         }
 249         if dir == columns {
 250                 if offset < 0 && coordinates[0] == coordinates[2] {
 251                         delete = true
 252                 }
 253                 if coordinates[0] >= num {
 254                         coordinates[0] += offset
 255                 }
 256                 if coordinates[2] >= num {
 257                         coordinates[2] += offset
 258                 }
 259         } else {
 260                 if offset < 0 && coordinates[1] == coordinates[3] {
 261                         delete = true
 262                 }
 263                 if coordinates[1] >= num {
 264                         coordinates[1] += offset
 265                 }
 266                 if coordinates[3] >= num {
 267                         coordinates[3] += offset
 268                 }
 269         }
 270         ref, err = f.coordinatesToRangeRef(coordinates)
 271         return ref, delete, err
 272 }
 273
 274 // adjustFormula provides a function to adjust formula reference and shared
 275 // formula reference.
 276 func (f *File) adjustFormula(sheet, sheetN string, formula *xlsxF, dir adjustDirection, num, offset int, si bool) error {
 277         if formula == nil {
 278                 return nil
 279         }
 280         var err error
 281         if formula.Ref != "" && sheet == sheetN {
 282                 if formula.Ref, _, err = f.adjustCellRef(formula.Ref, dir, num, offset); err != nil {
 283                         return err
 284                 }
 285                 if si && formula.Si != nil {
 286                         formula.Si = intPtr(*formula.Si + 1)
 287                 }
 288         }
 289         if formula.Content != "" {
 290                 if formula.Content, err = f.adjustFormulaRef(sheet, sheetN, formula.Content, false, dir, num, offset); err != nil {
 291                         return err
 292                 }
 293         }
 294         return nil
 295 }
 296
 297 // isFunctionStop provides a function to check if token is a function stop.
 298 func isFunctionStop(token efp.Token) bool {
 299         return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStop
 300 }
 301
 302 // isFunctionStart provides a function to check if token is a function start.
 303 func isFunctionStart(token efp.Token) bool {
 304         return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStart
 305 }
 306
 307 // escapeSheetName enclose sheet name in single quotation marks if the giving
 308 // worksheet name includes spaces or non-alphabetical characters.
 309 func escapeSheetName(name string) string {
 310         if strings.IndexFunc(name, func(r rune) bool {
 311                 return !unicode.IsLetter(r) && !unicode.IsNumber(r)
 312         }) != -1 {
 313                 return "'" + strings.ReplaceAll(name, "'", "''") + "'"
 314         }
 315         return name
 316 }
 317
 318 // adjustFormulaColumnName adjust column name in the formula reference.
 319 func adjustFormulaColumnName(name, operand string, abs, keepRelative bool, dir adjustDirection, num, offset int) (string, string, bool, error) {
 320         if name == "" || (!abs && keepRelative) {
 321                 return "", operand + name, abs, nil
 322         }
 323         col, err := ColumnNameToNumber(name)
 324         if err != nil {
 325                 return "", operand, false, err
 326         }
 327         if dir == columns && col >= num {
 328                 col += offset
 329                 colName, err := ColumnNumberToName(col)
 330                 return "", operand + colName, false, err
 331         }
 332         return "", operand + name, false, nil
 333 }
 334
 335 // adjustFormulaRowNumber adjust row number in the formula reference.
 336 func adjustFormulaRowNumber(name, operand string, abs, keepRelative bool, dir adjustDirection, num, offset int) (string, string, bool, error) {
 337         if name == "" || (!abs && keepRelative) {
 338                 return "", operand + name, abs, nil
 339         }
 340         row, _ := strconv.Atoi(name)
 341         if dir == rows && row >= num {
 342                 row += offset
 343                 if row <= 0 || row > TotalRows {
 344                         return "", operand + name, false, ErrMaxRows
 345                 }
 346                 return "", operand + strconv.Itoa(row), false, nil
 347         }
 348         return "", operand + name, false, nil
 349 }
 350
 351 // adjustFormulaOperandRef adjust cell reference in the operand tokens for the formula.
 352 func adjustFormulaOperandRef(row, col, operand string, abs, keepRelative bool, dir adjustDirection, num int, offset int) (string, string, string, bool, error) {
 353         var err error
 354         col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
 355         if err != nil {
 356                 return row, col, operand, abs, err
 357         }
 358         row, operand, abs, err = adjustFormulaRowNumber(row, operand, abs, keepRelative, dir, num, offset)
 359         return row, col, operand, abs, err
 360 }
 361
 362 // adjustFormulaOperand adjust range operand tokens for the formula.
 363 func (f *File) adjustFormulaOperand(sheet, sheetN string, keepRelative bool, token efp.Token, dir adjustDirection, num int, offset int) (string, error) {
 364         var (
 365                 err                          error
 366                 abs                          bool
 367                 sheetName, col, row, operand string
 368                 cell                         = token.TValue
 369                 tokens                       = strings.Split(token.TValue, "!")
 370         )
 371         if len(tokens) == 2 { // have a worksheet
 372                 sheetName, cell = tokens[0], tokens[1]
 373                 operand = escapeSheetName(sheetName) + "!"
 374         }
 375         if sheetName == "" {
 376                 sheetName = sheetN
 377         }
 378         if sheet != sheetName {
 379                 return operand + cell, err
 380         }
 381         for _, r := range cell {
 382                 if r == '$' {
 383                         if col, operand, _, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset); err != nil {
 384                                 return operand, err
 385                         }
 386                         abs = true
 387                         operand += string(r)
 388                         continue
 389                 }
 390                 if ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z') {
 391                         col += string(r)
 392                         continue
 393                 }
 394                 if '0' <= r && r <= '9' {
 395                         row += string(r)
 396                         col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
 397                         if err != nil {
 398                                 return operand, err
 399                         }
 400                         continue
 401                 }
 402                 if row, col, operand, abs, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset); err != nil {
 403                         return operand, err
 404                 }
 405                 operand += string(r)
 406         }
 407         _, _, operand, _, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset)
 408         return operand, err
 409 }
 410
 411 // adjustFormulaRef returns adjusted formula by giving adjusting direction and
 412 // the base number of column or row, and offset.
 413 func (f *File) adjustFormulaRef(sheet, sheetN, formula string, keepRelative bool, dir adjustDirection, num, offset int) (string, error) {
 414         var (
 415                 val          string
 416                 definedNames []string
 417                 ps           = efp.ExcelParser()
 418         )
 419         for _, definedName := range f.GetDefinedName() {
 420                 if definedName.Scope == "Workbook" || definedName.Scope == sheet {
 421                         definedNames = append(definedNames, definedName.Name)
 422                 }
 423         }
 424         for _, token := range ps.Parse(formula) {
 425                 if token.TType == efp.TokenTypeUnknown {
 426                         val = formula
 427                         break
 428                 }
 429                 if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeRange {
 430                         if inStrSlice(definedNames, token.TValue, true) != -1 {
 431                                 val += token.TValue
 432                                 continue
 433                         }
 434                         if strings.ContainsAny(token.TValue, "[]") {
 435                                 val += token.TValue
 436                                 continue
 437                         }
 438                         operand, err := f.adjustFormulaOperand(sheet, sheetN, keepRelative, token, dir, num, offset)
 439                         if err != nil {
 440                                 return val, err
 441                         }
 442                         val += operand
 443                         continue
 444                 }
 445                 if isFunctionStart(token) {
 446                         val += token.TValue + string(efp.ParenOpen)
 447                         continue
 448                 }
 449                 if isFunctionStop(token) {
 450                         val += token.TValue + string(efp.ParenClose)
 451                         continue
 452                 }
 453                 if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
 454                         val += string(efp.QuoteDouble) + strings.ReplaceAll(token.TValue, "\"", "\"\"") + string(efp.QuoteDouble)
 455                         continue
 456                 }
 457                 val += token.TValue
 458         }
 459         return val, nil
 460 }
 461
 462 // adjustHyperlinks provides a function to update hyperlinks when inserting or
 463 // deleting rows or columns.
 464 func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
 465         // short path
 466         if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
 467                 return
 468         }
 469
 470         // order is important
 471         if offset < 0 {
 472                 for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
 473                         linkData := ws.Hyperlinks.Hyperlink[i]
 474                         colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
 475
 476                         if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
 477                                 f.deleteSheetRelationships(sheet, linkData.RID)
 478                                 if len(ws.Hyperlinks.Hyperlink) > 1 {
 479                                         ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
 480                                                 ws.Hyperlinks.Hyperlink[i+1:]...)
 481                                 } else {
 482                                         ws.Hyperlinks = nil
 483                                 }
 484                         }
 485                 }
 486         }
 487         if ws.Hyperlinks == nil {
 488                 return
 489         }
 490         for i := range ws.Hyperlinks.Hyperlink {
 491                 link := &ws.Hyperlinks.Hyperlink[i] // get reference
 492                 link.Ref, _ = f.adjustFormulaRef(sheet, sheet, link.Ref, false, dir, num, offset)
 493         }
 494 }
 495
 496 // adjustTable provides a function to update the table when inserting or
 497 // deleting rows or columns.
 498 func (f *File) adjustTable(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 499         if ws.TableParts == nil || len(ws.TableParts.TableParts) == 0 {
 500                 return nil
 501         }
 502         for idx := 0; idx < len(ws.TableParts.TableParts); idx++ {
 503                 tbl := ws.TableParts.TableParts[idx]
 504                 target := f.getSheetRelationshipsTargetByID(sheet, tbl.RID)
 505                 tableXML := strings.ReplaceAll(target, "..", "xl")
 506                 content, ok := f.Pkg.Load(tableXML)
 507                 if !ok {
 508                         continue
 509                 }
 510                 t := xlsxTable{}
 511                 if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
 512                         Decode(&t); err != nil && err != io.EOF {
 513                         return err
 514                 }
 515                 coordinates, err := rangeRefToCoordinates(t.Ref)
 516                 if err != nil {
 517                         return err
 518                 }
 519                 // Remove the table when deleting the header row of the table
 520                 if dir == rows && num == coordinates[0] && offset == -1 {
 521                         ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
 522                         ws.TableParts.Count = len(ws.TableParts.TableParts)
 523                         idx--
 524                         continue
 525                 }
 526                 coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
 527                 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 528                 if y2-y1 < 1 || x2-x1 < 0 {
 529                         ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
 530                         ws.TableParts.Count = len(ws.TableParts.TableParts)
 531                         idx--
 532                         continue
 533                 }
 534                 t.Ref, _ = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
 535                 if t.AutoFilter != nil {
 536                         t.AutoFilter.Ref = t.Ref
 537                 }
 538                 _ = f.setTableColumns(sheet, true, x1, y1, x2, &t)
 539                 // Currently doesn't support query table
 540                 t.TableType, t.TotalsRowCount, t.ConnectionID = "", 0, 0
 541                 table, _ := xml.Marshal(t)
 542                 f.saveFileList(tableXML, table)
 543         }
 544         return nil
 545 }
 546
 547 // adjustAutoFilter provides a function to update the auto filter when
 548 // inserting or deleting rows or columns.
 549 func (f *File) adjustAutoFilter(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 550         if ws.AutoFilter == nil {
 551                 return nil
 552         }
 553
 554         coordinates, err := rangeRefToCoordinates(ws.AutoFilter.Ref)
 555         if err != nil {
 556                 return err
 557         }
 558         x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 559
 560         if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
 561                 ws.AutoFilter = nil
 562                 for rowIdx := range ws.SheetData.Row {
 563                         rowData := &ws.SheetData.Row[rowIdx]
 564                         if rowData.R != nil && *rowData.R > y1 && *rowData.R <= y2 {
 565                                 rowData.Hidden = false
 566                         }
 567                 }
 568                 return err
 569         }
 570
 571         coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
 572         x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 573
 574         ws.AutoFilter.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
 575         return err
 576 }
 577
 578 // adjustAutoFilterHelper provides a function for adjusting auto filter to
 579 // compare and calculate cell reference by the giving adjusting direction,
 580 // operation reference and offset.
 581 func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
 582         if dir == rows {
 583                 if coordinates[1] >= num {
 584                         coordinates[1] += offset
 585                 }
 586                 if coordinates[3] >= num {
 587                         coordinates[3] += offset
 588                 }
 589                 return coordinates
 590         }
 591         if coordinates[0] >= num {
 592                 coordinates[0] += offset
 593         }
 594         if coordinates[2] >= num {
 595                 coordinates[2] += offset
 596         }
 597         return coordinates
 598 }
 599
 600 // adjustMergeCells provides a function to update merged cells when inserting
 601 // or deleting rows or columns.
 602 func (f *File) adjustMergeCells(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 603         if ws.MergeCells == nil {
 604                 return nil
 605         }
 606
 607         for i := 0; i < len(ws.MergeCells.Cells); i++ {
 608                 mergedCells := ws.MergeCells.Cells[i]
 609                 mergedCellsRef := mergedCells.Ref
 610                 if !strings.Contains(mergedCellsRef, ":") {
 611                         mergedCellsRef += ":" + mergedCellsRef
 612                 }
 613                 coordinates, err := rangeRefToCoordinates(mergedCellsRef)
 614                 if err != nil {
 615                         return err
 616                 }
 617                 x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
 618                 if dir == rows {
 619                         if y1 == num && y2 == num && offset < 0 {
 620                                 f.deleteMergeCell(ws, i)
 621                                 i--
 622                                 continue
 623                         }
 624
 625                         y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
 626                 } else {
 627                         if x1 == num && x2 == num && offset < 0 {
 628                                 f.deleteMergeCell(ws, i)
 629                                 i--
 630                                 continue
 631                         }
 632
 633                         x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
 634                 }
 635                 if x1 == x2 && y1 == y2 {
 636                         f.deleteMergeCell(ws, i)
 637                         i--
 638                         continue
 639                 }
 640                 mergedCells.rect = []int{x1, y1, x2, y2}
 641                 if mergedCells.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2}); err != nil {
 642                         return err
 643                 }
 644         }
 645         return nil
 646 }
 647
 648 // adjustMergeCellsHelper provides a function for adjusting merge cells to
 649 // compare and calculate cell reference by the given pivot, operation reference
 650 // and offset.
 651 func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
 652         if p2 < p1 {
 653                 p1, p2 = p2, p1
 654         }
 655
 656         if offset >= 0 {
 657                 if num <= p1 {
 658                         p1 += offset
 659                         p2 += offset
 660                 } else if num <= p2 {
 661                         p2 += offset
 662                 }
 663                 return p1, p2
 664         }
 665         if num < p1 || (num == p1 && num == p2) {
 666                 p1 += offset
 667                 p2 += offset
 668         } else if num <= p2 {
 669                 p2 += offset
 670         }
 671         return p1, p2
 672 }
 673
 674 // deleteMergeCell provides a function to delete merged cell by given index.
 675 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
 676         if idx < 0 {
 677                 return
 678         }
 679         if len(ws.MergeCells.Cells) > idx {
 680                 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
 681                 ws.MergeCells.Count = len(ws.MergeCells.Cells)
 682         }
 683 }
 684
 685 // adjustCellName returns updated cell name by giving column/row number and
 686 // offset on inserting or deleting rows or columns.
 687 func adjustCellName(cell string, dir adjustDirection, c, r, offset int) (string, error) {
 688         if dir == rows {
 689                 if rn := r + offset; rn > 0 {
 690                         return CoordinatesToCellName(c, rn)
 691                 }
 692         }
 693         return CoordinatesToCellName(c+offset, r)
 694 }
 695
 696 // adjustCalcChain provides a function to update the calculation chain when
 697 // inserting or deleting rows or columns.
 698 func (f *File) adjustCalcChain(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 699         if f.CalcChain == nil {
 700                 return nil
 701         }
 702         // If sheet ID is omitted, it is assumed to be the same as the i value of
 703         // the previous cell.
 704         var prevSheetID int
 705         for i := 0; i < len(f.CalcChain.C); i++ {
 706                 c := f.CalcChain.C[i]
 707                 if c.I == 0 {
 708                         c.I = prevSheetID
 709                 }
 710                 prevSheetID = c.I
 711                 if c.I != sheetID {
 712                         continue
 713                 }
 714                 colNum, rowNum, err := CellNameToCoordinates(c.R)
 715                 if err != nil {
 716                         return err
 717                 }
 718                 if dir == rows && num <= rowNum {
 719                         if num == rowNum && offset == -1 {
 720                                 _ = f.deleteCalcChain(c.I, c.R)
 721                                 i--
 722                                 continue
 723                         }
 724                         f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
 725                 }
 726                 if dir == columns && num <= colNum {
 727                         if num == colNum && offset == -1 {
 728                                 _ = f.deleteCalcChain(c.I, c.R)
 729                                 i--
 730                                 continue
 731                         }
 732                         f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
 733                 }
 734         }
 735         return nil
 736 }
 737
 738 // adjustVolatileDepsTopic updates the volatile dependencies topic when
 739 // inserting or deleting rows or columns.
 740 func (vt *xlsxVolTypes) adjustVolatileDepsTopic(cell string, dir adjustDirection, indexes []int) (int, error) {
 741         num, offset, i1, i2, i3, i4 := indexes[0], indexes[1], indexes[2], indexes[3], indexes[4], indexes[5]
 742         colNum, rowNum, err := CellNameToCoordinates(cell)
 743         if err != nil {
 744                 return i4, err
 745         }
 746         if dir == rows && num <= rowNum {
 747                 if num == rowNum && offset == -1 {
 748                         vt.deleteVolTopicRef(i1, i2, i3, i4)
 749                         i4--
 750                         return i4, err
 751                 }
 752                 vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
 753         }
 754         if dir == columns && num <= colNum {
 755                 if num == colNum && offset == -1 {
 756                         vt.deleteVolTopicRef(i1, i2, i3, i4)
 757                         i4--
 758                         return i4, err
 759                 }
 760                 if name, _ := adjustCellName(cell, dir, colNum, rowNum, offset); name != "" {
 761                         vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
 762                 }
 763         }
 764         return i4, err
 765 }
 766
 767 // adjustVolatileDeps updates the volatile dependencies when inserting or
 768 // deleting rows or columns.
 769 func (f *File) adjustVolatileDeps(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 770         volTypes, err := f.volatileDepsReader()
 771         if err != nil || volTypes == nil {
 772                 return err
 773         }
 774         for i1 := 0; i1 < len(volTypes.VolType); i1++ {
 775                 for i2 := 0; i2 < len(volTypes.VolType[i1].Main); i2++ {
 776                         for i3 := 0; i3 < len(volTypes.VolType[i1].Main[i2].Tp); i3++ {
 777                                 for i4 := 0; i4 < len(volTypes.VolType[i1].Main[i2].Tp[i3].Tr); i4++ {
 778                                         ref := volTypes.VolType[i1].Main[i2].Tp[i3].Tr[i4]
 779                                         if ref.S != sheetID {
 780                                                 continue
 781                                         }
 782                                         if i4, err = volTypes.adjustVolatileDepsTopic(ref.R, dir, []int{num, offset, i1, i2, i3, i4}); err != nil {
 783                                                 return err
 784                                         }
 785                                 }
 786                         }
 787                 }
 788         }
 789         return nil
 790 }
 791
 792 // adjustConditionalFormats updates the cell reference of the worksheet
 793 // conditional formatting when inserting or deleting rows or columns.
 794 func (f *File) adjustConditionalFormats(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 795         for i := 0; i < len(ws.ConditionalFormatting); i++ {
 796                 cf := ws.ConditionalFormatting[i]
 797                 if cf == nil {
 798                         continue
 799                 }
 800                 ref, del, err := f.adjustCellRef(cf.SQRef, dir, num, offset)
 801                 if err != nil {
 802                         return err
 803                 }
 804                 if del {
 805                         ws.ConditionalFormatting = append(ws.ConditionalFormatting[:i],
 806                                 ws.ConditionalFormatting[i+1:]...)
 807                         i--
 808                         continue
 809                 }
 810                 ws.ConditionalFormatting[i].SQRef = ref
 811         }
 812         return nil
 813 }
 814
 815 // adjustDataValidations updates the range of data validations for the worksheet
 816 // when inserting or deleting rows or columns.
 817 func (f *File) adjustDataValidations(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 818         for _, sheetN := range f.GetSheetList() {
 819                 worksheet, err := f.workSheetReader(sheetN)
 820                 if err != nil {
 821                         if err.Error() == newNotWorksheetError(sheetN).Error() {
 822                                 continue
 823                         }
 824                         return err
 825                 }
 826                 if worksheet.DataValidations == nil {
 827                         return nil
 828                 }
 829                 for i := 0; i < len(worksheet.DataValidations.DataValidation); i++ {
 830                         dv := worksheet.DataValidations.DataValidation[i]
 831                         if dv == nil {
 832                                 continue
 833                         }
 834                         if sheet == sheetN {
 835                                 ref, del, err := f.adjustCellRef(dv.Sqref, dir, num, offset)
 836                                 if err != nil {
 837                                         return err
 838                                 }
 839                                 if del {
 840                                         worksheet.DataValidations.DataValidation = append(worksheet.DataValidations.DataValidation[:i],
 841                                                 worksheet.DataValidations.DataValidation[i+1:]...)
 842                                         i--
 843                                         continue
 844                                 }
 845                                 worksheet.DataValidations.DataValidation[i].Sqref = ref
 846                         }
 847                         if worksheet.DataValidations.DataValidation[i].Formula1 != nil {
 848                                 formula := unescapeDataValidationFormula(worksheet.DataValidations.DataValidation[i].Formula1.Content)
 849                                 if formula, err = f.adjustFormulaRef(sheet, sheetN, formula, false, dir, num, offset); err != nil {
 850                                         return err
 851                                 }
 852                                 worksheet.DataValidations.DataValidation[i].Formula1 = &xlsxInnerXML{Content: formulaEscaper.Replace(formula)}
 853                         }
 854                         if worksheet.DataValidations.DataValidation[i].Formula2 != nil {
 855                                 formula := unescapeDataValidationFormula(worksheet.DataValidations.DataValidation[i].Formula2.Content)
 856                                 if formula, err = f.adjustFormulaRef(sheet, sheetN, formula, false, dir, num, offset); err != nil {
 857                                         return err
 858                                 }
 859                                 worksheet.DataValidations.DataValidation[i].Formula2 = &xlsxInnerXML{Content: formulaEscaper.Replace(formula)}
 860                         }
 861                 }
 862                 if worksheet.DataValidations.Count = len(worksheet.DataValidations.DataValidation); worksheet.DataValidations.Count == 0 {
 863                         worksheet.DataValidations = nil
 864                 }
 865         }
 866         return nil
 867 }
 868
 869 // adjustDrawings updates the starting anchor of the two cell anchor pictures
 870 // and charts object when inserting or deleting rows or columns.
 871 func (from *xlsxFrom) adjustDrawings(dir adjustDirection, num, offset int, editAs string) (bool, error) {
 872         var ok bool
 873         if dir == columns && from.Col+1 >= num && from.Col+offset >= 0 {
 874                 if from.Col+offset >= MaxColumns {
 875                         return false, ErrColumnNumber
 876                 }
 877                 from.Col += offset
 878                 ok = editAs == "oneCell"
 879         }
 880         if dir == rows && from.Row+1 >= num && from.Row+offset >= 0 {
 881                 if from.Row+offset >= TotalRows {
 882                         return false, ErrMaxRows
 883                 }
 884                 from.Row += offset
 885                 ok = editAs == "oneCell"
 886         }
 887         return ok, nil
 888 }
 889
 890 // adjustDrawings updates the ending anchor of the two cell anchor pictures
 891 // and charts object when inserting or deleting rows or columns.
 892 func (to *xlsxTo) adjustDrawings(dir adjustDirection, num, offset int, editAs string, ok bool) error {
 893         if dir == columns && to.Col+1 >= num && to.Col+offset >= 0 && ok {
 894                 if to.Col+offset >= MaxColumns {
 895                         return ErrColumnNumber
 896                 }
 897                 to.Col += offset
 898         }
 899         if dir == rows && to.Row+1 >= num && to.Row+offset >= 0 && ok {
 900                 if to.Row+offset >= TotalRows {
 901                         return ErrMaxRows
 902                 }
 903                 to.Row += offset
 904         }
 905         return nil
 906 }
 907
 908 // adjustDrawings updates the two cell anchor pictures and charts object when
 909 // inserting or deleting rows or columns.
 910 func (a *xdrCellAnchor) adjustDrawings(dir adjustDirection, num, offset int) error {
 911         editAs := a.EditAs
 912         if a.From == nil || a.To == nil || editAs == "absolute" {
 913                 return nil
 914         }
 915         ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
 916         if err != nil {
 917                 return err
 918         }
 919         return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
 920 }
 921
 922 // adjustDrawings updates the existing two cell anchor pictures and charts
 923 // object when inserting or deleting rows or columns.
 924 func (a *xlsxCellAnchorPos) adjustDrawings(dir adjustDirection, num, offset int, editAs string) error {
 925         if a.From == nil || a.To == nil || editAs == "absolute" {
 926                 return nil
 927         }
 928         ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
 929         if err != nil {
 930                 return err
 931         }
 932         return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
 933 }
 934
 935 // adjustDrawings updates the pictures and charts object when inserting or
 936 // deleting rows or columns.
 937 func (f *File) adjustDrawings(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 938         if ws.Drawing == nil {
 939                 return nil
 940         }
 941         target := f.getSheetRelationshipsTargetByID(sheet, ws.Drawing.RID)
 942         drawingXML := strings.TrimPrefix(strings.ReplaceAll(target, "..", "xl"), "/")
 943         var (
 944                 err  error
 945                 wsDr *xlsxWsDr
 946         )
 947         if wsDr, _, err = f.drawingParser(drawingXML); err != nil {
 948                 return err
 949         }
 950         anchorCb := func(a *xdrCellAnchor) error {
 951                 if a.GraphicFrame == "" {
 952                         return a.adjustDrawings(dir, num, offset)
 953                 }
 954                 deCellAnchor := decodeCellAnchor{}
 955                 deCellAnchorPos := decodeCellAnchorPos{}
 956                 _ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchor>" + a.GraphicFrame + "</decodeCellAnchor>")).Decode(&deCellAnchor)
 957                 _ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchorPos>" + a.GraphicFrame + "</decodeCellAnchorPos>")).Decode(&deCellAnchorPos)
 958                 xlsxCellAnchorPos := xlsxCellAnchorPos(deCellAnchorPos)
 959                 for i := 0; i < len(xlsxCellAnchorPos.AlternateContent); i++ {
 960                         xlsxCellAnchorPos.AlternateContent[i].XMLNSMC = SourceRelationshipCompatibility.Value
 961                 }
 962                 if deCellAnchor.From != nil {
 963                         xlsxCellAnchorPos.From = &xlsxFrom{
 964                                 Col: deCellAnchor.From.Col, ColOff: deCellAnchor.From.ColOff,
 965                                 Row: deCellAnchor.From.Row, RowOff: deCellAnchor.From.RowOff,
 966                         }
 967                 }
 968                 if deCellAnchor.To != nil {
 969                         xlsxCellAnchorPos.To = &xlsxTo{
 970                                 Col: deCellAnchor.To.Col, ColOff: deCellAnchor.To.ColOff,
 971                                 Row: deCellAnchor.To.Row, RowOff: deCellAnchor.To.RowOff,
 972                         }
 973                 }
 974                 if err = xlsxCellAnchorPos.adjustDrawings(dir, num, offset, a.EditAs); err != nil {
 975                         return err
 976                 }
 977                 cellAnchor, _ := xml.Marshal(xlsxCellAnchorPos)
 978                 a.GraphicFrame = strings.TrimSuffix(strings.TrimPrefix(string(cellAnchor), "<xlsxCellAnchorPos>"), "</xlsxCellAnchorPos>")
 979                 return err
 980         }
 981         for _, anchor := range wsDr.TwoCellAnchor {
 982                 if err = anchorCb(anchor); err != nil {
 983                         return err
 984                 }
 985         }
 986         return nil
 987 }
 988
 989 // adjustDefinedNames updates the cell reference of the defined names when
 990 // inserting or deleting rows or columns.
 991 func (f *File) adjustDefinedNames(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 992         wb, err := f.workbookReader()
 993         if err != nil {
 994                 return err
 995         }
 996         if wb.DefinedNames != nil {
 997                 for i := 0; i < len(wb.DefinedNames.DefinedName); i++ {
 998                         data := wb.DefinedNames.DefinedName[i].Data
 999                         if data, err = f.adjustFormulaRef(sheet, "", data, true, dir, num, offset); err == nil {
1000                                 wb.DefinedNames.DefinedName[i].Data = data
1001                         }
1002                 }
1003         }
1004         return nil
1005 }