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         "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                                 continue
 622                         }
 623
 624                         y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
 625                 } else {
 626                         if x1 == num && x2 == num && offset < 0 {
 627                                 f.deleteMergeCell(ws, i)
 628                                 continue
 629                         }
 630
 631                         x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
 632                 }
 633                 if x1 == x2 && y1 == y2 {
 634                         f.deleteMergeCell(ws, i)
 635                         i--
 636                         continue
 637                 }
 638                 mergedCells.rect = []int{x1, y1, x2, y2}
 639                 if mergedCells.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2}); err != nil {
 640                         return err
 641                 }
 642         }
 643         return nil
 644 }
 645
 646 // adjustMergeCellsHelper provides a function for adjusting merge cells to
 647 // compare and calculate cell reference by the given pivot, operation reference and
 648 // offset.
 649 func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
 650         if p2 < p1 {
 651                 p1, p2 = p2, p1
 652         }
 653
 654         if offset >= 0 {
 655                 if num <= p1 {
 656                         p1 += offset
 657                         p2 += offset
 658                 } else if num <= p2 {
 659                         p2 += offset
 660                 }
 661                 return p1, p2
 662         }
 663         if num < p1 || (num == p1 && num == p2) {
 664                 p1 += offset
 665                 p2 += offset
 666         } else if num <= p2 {
 667                 p2 += offset
 668         }
 669         return p1, p2
 670 }
 671
 672 // deleteMergeCell provides a function to delete merged cell by given index.
 673 func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
 674         if idx < 0 {
 675                 return
 676         }
 677         if len(ws.MergeCells.Cells) > idx {
 678                 ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
 679                 ws.MergeCells.Count = len(ws.MergeCells.Cells)
 680         }
 681 }
 682
 683 // adjustCellName returns updated cell name by giving column/row number and
 684 // offset on inserting or deleting rows or columns.
 685 func adjustCellName(cell string, dir adjustDirection, c, r, offset int) (string, error) {
 686         if dir == rows {
 687                 if rn := r + offset; rn > 0 {
 688                         return CoordinatesToCellName(c, rn)
 689                 }
 690         }
 691         return CoordinatesToCellName(c+offset, r)
 692 }
 693
 694 // adjustCalcChain provides a function to update the calculation chain when
 695 // inserting or deleting rows or columns.
 696 func (f *File) adjustCalcChain(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 697         if f.CalcChain == nil {
 698                 return nil
 699         }
 700         // If sheet ID is omitted, it is assumed to be the same as the i value of
 701         // the previous cell.
 702         var prevSheetID int
 703         for i := 0; i < len(f.CalcChain.C); i++ {
 704                 c := f.CalcChain.C[i]
 705                 if c.I == 0 {
 706                         c.I = prevSheetID
 707                 }
 708                 prevSheetID = c.I
 709                 if c.I != sheetID {
 710                         continue
 711                 }
 712                 colNum, rowNum, err := CellNameToCoordinates(c.R)
 713                 if err != nil {
 714                         return err
 715                 }
 716                 if dir == rows && num <= rowNum {
 717                         if num == rowNum && offset == -1 {
 718                                 _ = f.deleteCalcChain(c.I, c.R)
 719                                 i--
 720                                 continue
 721                         }
 722                         f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
 723                 }
 724                 if dir == columns && num <= colNum {
 725                         if num == colNum && offset == -1 {
 726                                 _ = f.deleteCalcChain(c.I, c.R)
 727                                 i--
 728                                 continue
 729                         }
 730                         f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
 731                 }
 732         }
 733         return nil
 734 }
 735
 736 // adjustVolatileDepsTopic updates the volatile dependencies topic when
 737 // inserting or deleting rows or columns.
 738 func (vt *xlsxVolTypes) adjustVolatileDepsTopic(cell string, dir adjustDirection, indexes []int) (int, error) {
 739         num, offset, i1, i2, i3, i4 := indexes[0], indexes[1], indexes[2], indexes[3], indexes[4], indexes[5]
 740         colNum, rowNum, err := CellNameToCoordinates(cell)
 741         if err != nil {
 742                 return i4, err
 743         }
 744         if dir == rows && num <= rowNum {
 745                 if num == rowNum && offset == -1 {
 746                         vt.deleteVolTopicRef(i1, i2, i3, i4)
 747                         i4--
 748                         return i4, err
 749                 }
 750                 vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
 751         }
 752         if dir == columns && num <= colNum {
 753                 if num == colNum && offset == -1 {
 754                         vt.deleteVolTopicRef(i1, i2, i3, i4)
 755                         i4--
 756                         return i4, err
 757                 }
 758                 if name, _ := adjustCellName(cell, dir, colNum, rowNum, offset); name != "" {
 759                         vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
 760                 }
 761         }
 762         return i4, err
 763 }
 764
 765 // adjustVolatileDeps updates the volatile dependencies when inserting or
 766 // deleting rows or columns.
 767 func (f *File) adjustVolatileDeps(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 768         volTypes, err := f.volatileDepsReader()
 769         if err != nil || volTypes == nil {
 770                 return err
 771         }
 772         for i1 := 0; i1 < len(volTypes.VolType); i1++ {
 773                 for i2 := 0; i2 < len(volTypes.VolType[i1].Main); i2++ {
 774                         for i3 := 0; i3 < len(volTypes.VolType[i1].Main[i2].Tp); i3++ {
 775                                 for i4 := 0; i4 < len(volTypes.VolType[i1].Main[i2].Tp[i3].Tr); i4++ {
 776                                         ref := volTypes.VolType[i1].Main[i2].Tp[i3].Tr[i4]
 777                                         if ref.S != sheetID {
 778                                                 continue
 779                                         }
 780                                         if i4, err = volTypes.adjustVolatileDepsTopic(ref.R, dir, []int{num, offset, i1, i2, i3, i4}); err != nil {
 781                                                 return err
 782                                         }
 783                                 }
 784                         }
 785                 }
 786         }
 787         return nil
 788 }
 789
 790 // adjustConditionalFormats updates the cell reference of the worksheet
 791 // conditional formatting when inserting or deleting rows or columns.
 792 func (f *File) adjustConditionalFormats(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 793         for i := 0; i < len(ws.ConditionalFormatting); i++ {
 794                 cf := ws.ConditionalFormatting[i]
 795                 if cf == nil {
 796                         continue
 797                 }
 798                 ref, del, err := f.adjustCellRef(cf.SQRef, dir, num, offset)
 799                 if err != nil {
 800                         return err
 801                 }
 802                 if del {
 803                         ws.ConditionalFormatting = append(ws.ConditionalFormatting[:i],
 804                                 ws.ConditionalFormatting[i+1:]...)
 805                         i--
 806                         continue
 807                 }
 808                 ws.ConditionalFormatting[i].SQRef = ref
 809         }
 810         return nil
 811 }
 812
 813 // adjustDataValidations updates the range of data validations for the worksheet
 814 // when inserting or deleting rows or columns.
 815 func (f *File) adjustDataValidations(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 816         for _, sheetN := range f.GetSheetList() {
 817                 worksheet, err := f.workSheetReader(sheetN)
 818                 if err != nil {
 819                         if err.Error() == newNotWorksheetError(sheetN).Error() {
 820                                 continue
 821                         }
 822                         return err
 823                 }
 824                 if worksheet.DataValidations == nil {
 825                         return nil
 826                 }
 827                 for i := 0; i < len(worksheet.DataValidations.DataValidation); i++ {
 828                         dv := worksheet.DataValidations.DataValidation[i]
 829                         if dv == nil {
 830                                 continue
 831                         }
 832                         if sheet == sheetN {
 833                                 ref, del, err := f.adjustCellRef(dv.Sqref, dir, num, offset)
 834                                 if err != nil {
 835                                         return err
 836                                 }
 837                                 if del {
 838                                         worksheet.DataValidations.DataValidation = append(worksheet.DataValidations.DataValidation[:i],
 839                                                 worksheet.DataValidations.DataValidation[i+1:]...)
 840                                         i--
 841                                         continue
 842                                 }
 843                                 worksheet.DataValidations.DataValidation[i].Sqref = ref
 844                         }
 845                         if worksheet.DataValidations.DataValidation[i].Formula1 != nil {
 846                                 formula := unescapeDataValidationFormula(worksheet.DataValidations.DataValidation[i].Formula1.Content)
 847                                 if formula, err = f.adjustFormulaRef(sheet, sheetN, formula, false, dir, num, offset); err != nil {
 848                                         return err
 849                                 }
 850                                 worksheet.DataValidations.DataValidation[i].Formula1 = &xlsxInnerXML{Content: formulaEscaper.Replace(formula)}
 851                         }
 852                         if worksheet.DataValidations.DataValidation[i].Formula2 != nil {
 853                                 formula := unescapeDataValidationFormula(worksheet.DataValidations.DataValidation[i].Formula2.Content)
 854                                 if formula, err = f.adjustFormulaRef(sheet, sheetN, formula, false, dir, num, offset); err != nil {
 855                                         return err
 856                                 }
 857                                 worksheet.DataValidations.DataValidation[i].Formula2 = &xlsxInnerXML{Content: formulaEscaper.Replace(formula)}
 858                         }
 859                 }
 860                 if worksheet.DataValidations.Count = len(worksheet.DataValidations.DataValidation); worksheet.DataValidations.Count == 0 {
 861                         worksheet.DataValidations = nil
 862                 }
 863         }
 864         return nil
 865 }
 866
 867 // adjustDrawings updates the starting anchor of the two cell anchor pictures
 868 // and charts object when inserting or deleting rows or columns.
 869 func (from *xlsxFrom) adjustDrawings(dir adjustDirection, num, offset int, editAs string) (bool, error) {
 870         var ok bool
 871         if dir == columns && from.Col+1 >= num && from.Col+offset >= 0 {
 872                 if from.Col+offset >= MaxColumns {
 873                         return false, ErrColumnNumber
 874                 }
 875                 from.Col += offset
 876                 ok = editAs == "oneCell"
 877         }
 878         if dir == rows && from.Row+1 >= num && from.Row+offset >= 0 {
 879                 if from.Row+offset >= TotalRows {
 880                         return false, ErrMaxRows
 881                 }
 882                 from.Row += offset
 883                 ok = editAs == "oneCell"
 884         }
 885         return ok, nil
 886 }
 887
 888 // adjustDrawings updates the ending anchor of the two cell anchor pictures
 889 // and charts object when inserting or deleting rows or columns.
 890 func (to *xlsxTo) adjustDrawings(dir adjustDirection, num, offset int, editAs string, ok bool) error {
 891         if dir == columns && to.Col+1 >= num && to.Col+offset >= 0 && ok {
 892                 if to.Col+offset >= MaxColumns {
 893                         return ErrColumnNumber
 894                 }
 895                 to.Col += offset
 896         }
 897         if dir == rows && to.Row+1 >= num && to.Row+offset >= 0 && ok {
 898                 if to.Row+offset >= TotalRows {
 899                         return ErrMaxRows
 900                 }
 901                 to.Row += offset
 902         }
 903         return nil
 904 }
 905
 906 // adjustDrawings updates the two cell anchor pictures and charts object when
 907 // inserting or deleting rows or columns.
 908 func (a *xdrCellAnchor) adjustDrawings(dir adjustDirection, num, offset int) error {
 909         editAs := a.EditAs
 910         if a.From == nil || a.To == nil || editAs == "absolute" {
 911                 return nil
 912         }
 913         ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
 914         if err != nil {
 915                 return err
 916         }
 917         return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
 918 }
 919
 920 // adjustDrawings updates the existing two cell anchor pictures and charts
 921 // object when inserting or deleting rows or columns.
 922 func (a *xlsxCellAnchorPos) adjustDrawings(dir adjustDirection, num, offset int, editAs string) error {
 923         if a.From == nil || a.To == nil || editAs == "absolute" {
 924                 return nil
 925         }
 926         ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
 927         if err != nil {
 928                 return err
 929         }
 930         return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
 931 }
 932
 933 // adjustDrawings updates the pictures and charts object when inserting or
 934 // deleting rows or columns.
 935 func (f *File) adjustDrawings(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 936         if ws.Drawing == nil {
 937                 return nil
 938         }
 939         target := f.getSheetRelationshipsTargetByID(sheet, ws.Drawing.RID)
 940         drawingXML := strings.TrimPrefix(strings.ReplaceAll(target, "..", "xl"), "/")
 941         var (
 942                 err  error
 943                 wsDr *xlsxWsDr
 944         )
 945         if wsDr, _, err = f.drawingParser(drawingXML); err != nil {
 946                 return err
 947         }
 948         anchorCb := func(a *xdrCellAnchor) error {
 949                 if a.GraphicFrame == "" {
 950                         return a.adjustDrawings(dir, num, offset)
 951                 }
 952                 deCellAnchor := decodeCellAnchor{}
 953                 deCellAnchorPos := decodeCellAnchorPos{}
 954                 _ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchor>" + a.GraphicFrame + "</decodeCellAnchor>")).Decode(&deCellAnchor)
 955                 _ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchorPos>" + a.GraphicFrame + "</decodeCellAnchorPos>")).Decode(&deCellAnchorPos)
 956                 xlsxCellAnchorPos := xlsxCellAnchorPos(deCellAnchorPos)
 957                 for i := 0; i < len(xlsxCellAnchorPos.AlternateContent); i++ {
 958                         xlsxCellAnchorPos.AlternateContent[i].XMLNSMC = SourceRelationshipCompatibility.Value
 959                 }
 960                 if deCellAnchor.From != nil {
 961                         xlsxCellAnchorPos.From = &xlsxFrom{
 962                                 Col: deCellAnchor.From.Col, ColOff: deCellAnchor.From.ColOff,
 963                                 Row: deCellAnchor.From.Row, RowOff: deCellAnchor.From.RowOff,
 964                         }
 965                 }
 966                 if deCellAnchor.To != nil {
 967                         xlsxCellAnchorPos.To = &xlsxTo{
 968                                 Col: deCellAnchor.To.Col, ColOff: deCellAnchor.To.ColOff,
 969                                 Row: deCellAnchor.To.Row, RowOff: deCellAnchor.To.RowOff,
 970                         }
 971                 }
 972                 if err = xlsxCellAnchorPos.adjustDrawings(dir, num, offset, a.EditAs); err != nil {
 973                         return err
 974                 }
 975                 cellAnchor, _ := xml.Marshal(xlsxCellAnchorPos)
 976                 a.GraphicFrame = strings.TrimSuffix(strings.TrimPrefix(string(cellAnchor), "<xlsxCellAnchorPos>"), "</xlsxCellAnchorPos>")
 977                 return err
 978         }
 979         for _, anchor := range wsDr.TwoCellAnchor {
 980                 if err = anchorCb(anchor); err != nil {
 981                         return err
 982                 }
 983         }
 984         return nil
 985 }
 986
 987 // adjustDefinedNames updates the cell reference of the defined names when
 988 // inserting or deleting rows or columns.
 989 func (f *File) adjustDefinedNames(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
 990         wb, err := f.workbookReader()
 991         if err != nil {
 992                 return err
 993         }
 994         if wb.DefinedNames != nil {
 995                 for i := 0; i < len(wb.DefinedNames.DefinedName); i++ {
 996                         data := wb.DefinedNames.DefinedName[i].Data
 997                         if data, err = f.adjustFormulaRef(sheet, "", data, true, dir, num, offset); err == nil {
 998                                 wb.DefinedNames.DefinedName[i].Data = data
 999                         }
1000                 }
1001         }
1002         return nil
1003 }