adjust.go

// Copyright 2016 - 2024 The excelize Authors. All rights reserved. Use of
// this source code is governed by a BSD-style license that can be found in
// the LICENSE file.
//
// Package excelize providing a set of functions that allow you to write to and
// read from XLAM / XLSM / XLSX / XLTM / XLTX files. Supports reading and
// writing spreadsheet documents generated by Microsoft Excel™ 2007 and later.
// Supports complex components by high compatibility, and provided streaming
// API for generating or reading data from a worksheet with huge amounts of
// data. This library needs Go version 1.18 or later.

package excelize

import (
	"bytes"
	"encoding/xml"
	"io"
	"strconv"
	"strings"
	"unicode"

	"github.com/xuri/efp"
)

type adjustDirection bool

const (
	columns adjustDirection = false
	rows    adjustDirection = true
)

// adjustHelperFunc defines functions to adjust helper.
var adjustHelperFunc = [9]func(*File, *xlsxWorksheet, string, adjustDirection, int, int, int) error{
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustConditionalFormats(ws, sheet, dir, num, offset, sheetID)
	},
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustDataValidations(ws, sheet, dir, num, offset, sheetID)
	},
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustDefinedNames(ws, sheet, dir, num, offset, sheetID)
	},
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustDrawings(ws, sheet, dir, num, offset, sheetID)
	},
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustMergeCells(ws, sheet, dir, num, offset, sheetID)
	},
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustAutoFilter(ws, sheet, dir, num, offset, sheetID)
	},
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustCalcChain(ws, sheet, dir, num, offset, sheetID)
	},
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustTable(ws, sheet, dir, num, offset, sheetID)
	},
	func(f *File, ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
		return f.adjustVolatileDeps(ws, sheet, dir, num, offset, sheetID)
	},
}

// adjustHelper provides a function to adjust rows and columns dimensions,
// hyperlinks, merged cells and auto filter when inserting or deleting rows or
// columns.
//
// sheet: Worksheet name that we're editing
// column: Index number of the column we're inserting/deleting before
// row: Index number of the row we're inserting/deleting before
// offset: Number of rows/column to insert/delete negative values indicate deletion
//
// TODO: adjustComments, adjustPageBreaks, adjustProtectedCells
func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
	ws, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	sheetID := f.getSheetID(sheet)
	if dir == rows {
		err = f.adjustRowDimensions(sheet, ws, num, offset)
	} else {
		err = f.adjustColDimensions(sheet, ws, num, offset)
	}
	if err != nil {
		return err
	}
	f.adjustHyperlinks(ws, sheet, dir, num, offset)
	ws.checkSheet()
	_ = ws.checkRow()
	for _, fn := range adjustHelperFunc {
		if err := fn(f, ws, sheet, dir, num, offset, sheetID); err != nil {
			return err
		}
	}
	if ws.MergeCells != nil && len(ws.MergeCells.Cells) == 0 {
		ws.MergeCells = nil
	}
	return nil
}

// adjustCols provides a function to update column style when inserting or
// deleting columns.
func (f *File) adjustCols(ws *xlsxWorksheet, col, offset int) error {
	if ws.Cols == nil {
		return nil
	}
	for i := 0; i < len(ws.Cols.Col); i++ {
		if offset > 0 {
			if ws.Cols.Col[i].Min >= col {
				if ws.Cols.Col[i].Min += offset; ws.Cols.Col[i].Min > MaxColumns {
					ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
					i--
					continue
				}
			}
			if ws.Cols.Col[i].Max >= col || ws.Cols.Col[i].Max+1 == col {
				if ws.Cols.Col[i].Max += offset; ws.Cols.Col[i].Max > MaxColumns {
					ws.Cols.Col[i].Max = MaxColumns
				}
			}
			continue
		}
		if ws.Cols.Col[i].Min == col && ws.Cols.Col[i].Max == col {
			ws.Cols.Col = append(ws.Cols.Col[:i], ws.Cols.Col[i+1:]...)
			i--
			continue
		}
		if ws.Cols.Col[i].Min > col {
			ws.Cols.Col[i].Min += offset
		}
		if ws.Cols.Col[i].Max >= col {
			ws.Cols.Col[i].Max += offset
		}
	}
	if len(ws.Cols.Col) == 0 {
		ws.Cols = nil
	}
	return nil
}

// adjustColDimensions provides a function to update column dimensions when
// inserting or deleting rows or columns.
func (f *File) adjustColDimensions(sheet string, ws *xlsxWorksheet, col, offset int) error {
	for rowIdx := range ws.SheetData.Row {
		for _, v := range ws.SheetData.Row[rowIdx].C {
			if cellCol, _, _ := CellNameToCoordinates(v.R); col <= cellCol {
				if newCol := cellCol + offset; newCol > 0 && newCol > MaxColumns {
					return ErrColumnNumber
				}
			}
		}
	}
	for _, sheetN := range f.GetSheetList() {
		worksheet, err := f.workSheetReader(sheetN)
		if err != nil {
			if err.Error() == newNotWorksheetError(sheetN).Error() {
				continue
			}
			return err
		}
		for rowIdx := range worksheet.SheetData.Row {
			for colIdx, v := range worksheet.SheetData.Row[rowIdx].C {
				if cellCol, cellRow, _ := CellNameToCoordinates(v.R); sheetN == sheet && col <= cellCol {
					if newCol := cellCol + offset; newCol > 0 {
						worksheet.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
					}
				}
				if err := f.adjustFormula(sheet, sheetN, &worksheet.SheetData.Row[rowIdx].C[colIdx], columns, col, offset, false); err != nil {
					return err
				}
			}
		}
	}
	return f.adjustCols(ws, col, offset)
}

// adjustRowDimensions provides a function to update row dimensions when
// inserting or deleting rows or columns.
func (f *File) adjustRowDimensions(sheet string, ws *xlsxWorksheet, row, offset int) error {
	for _, sheetN := range f.GetSheetList() {
		if sheetN == sheet {
			continue
		}
		worksheet, err := f.workSheetReader(sheetN)
		if err != nil {
			if err.Error() == newNotWorksheetError(sheetN).Error() {
				continue
			}
			return err
		}
		numOfRows := len(worksheet.SheetData.Row)
		for i := 0; i < numOfRows; i++ {
			r := &worksheet.SheetData.Row[i]
			if err = f.adjustSingleRowFormulas(sheet, sheetN, r, row, offset, false); err != nil {
				return err
			}
		}
	}
	totalRows := len(ws.SheetData.Row)
	if totalRows == 0 {
		return nil
	}
	lastRow := &ws.SheetData.Row[totalRows-1]
	if newRow := *lastRow.R + offset; *lastRow.R >= row && newRow > 0 && newRow > TotalRows {
		return ErrMaxRows
	}
	numOfRows := len(ws.SheetData.Row)
	for i := 0; i < numOfRows; i++ {
		r := &ws.SheetData.Row[i]
		if newRow := *r.R + offset; *r.R >= row && newRow > 0 {
			r.adjustSingleRowDimensions(offset)
		}
		if err := f.adjustSingleRowFormulas(sheet, sheet, r, row, offset, false); err != nil {
			return err
		}
	}
	return nil
}

// adjustSingleRowDimensions provides a function to adjust single row dimensions.
func (r *xlsxRow) adjustSingleRowDimensions(offset int) {
	r.R = intPtr(*r.R + offset)
	for i, col := range r.C {
		colName, _, _ := SplitCellName(col.R)
		r.C[i].R, _ = JoinCellName(colName, *r.R)
	}
}

// adjustSingleRowFormulas provides a function to adjust single row formulas.
func (f *File) adjustSingleRowFormulas(sheet, sheetN string, r *xlsxRow, num, offset int, si bool) error {
	for i := 0; i < len(r.C); i++ {
		if err := f.adjustFormula(sheet, sheetN, &r.C[i], rows, num, offset, si); err != nil {
			return err
		}
	}
	return nil
}

// adjustCellRef provides a function to adjust cell reference.
func (f *File) adjustCellRef(ref string, dir adjustDirection, num, offset int) (string, bool, error) {
	if !strings.Contains(ref, ":") {
		ref += ":" + ref
	}
	var delete bool
	coordinates, err := rangeRefToCoordinates(ref)
	if err != nil {
		return ref, delete, err
	}
	if dir == columns {
		if offset < 0 && coordinates[0] == coordinates[2] {
			delete = true
		}
		if coordinates[0] >= num {
			coordinates[0] += offset
		}
		if coordinates[2] >= num {
			coordinates[2] += offset
		}
	} else {
		if offset < 0 && coordinates[1] == coordinates[3] {
			delete = true
		}
		if coordinates[1] >= num {
			coordinates[1] += offset
		}
		if coordinates[3] >= num {
			coordinates[3] += offset
		}
	}
	ref, err = f.coordinatesToRangeRef(coordinates)
	return ref, delete, err
}

// adjustFormula provides a function to adjust formula reference and shared
// formula reference.
func (f *File) adjustFormula(sheet, sheetN string, cell *xlsxC, dir adjustDirection, num, offset int, si bool) error {
	var err error
	if cell.f != "" {
		if cell.f, err = f.adjustFormulaRef(sheet, sheetN, cell.f, false, dir, num, offset); err != nil {
			return err
		}
	}
	if cell.F == nil {
		return nil
	}
	if cell.F.Ref != "" && sheet == sheetN {
		if cell.F.Ref, _, err = f.adjustCellRef(cell.F.Ref, dir, num, offset); err != nil {
			return err
		}
		if si && cell.F.Si != nil {
			cell.F.Si = intPtr(*cell.F.Si + 1)
		}
	}
	if cell.F.Content != "" {
		if cell.F.Content, err = f.adjustFormulaRef(sheet, sheetN, cell.F.Content, false, dir, num, offset); err != nil {
			return err
		}
	}
	return nil
}

// escapeSheetName enclose sheet name in single quotation marks if the giving
// worksheet name includes spaces or non-alphabetical characters.
func escapeSheetName(name string) string {
	if strings.IndexFunc(name, func(r rune) bool {
		return !unicode.IsLetter(r) && !unicode.IsNumber(r)
	}) != -1 {
		return "'" + strings.ReplaceAll(name, "'", "''") + "'"
	}
	return name
}

// adjustFormulaColumnName adjust column name in the formula reference.
func adjustFormulaColumnName(name, operand string, abs, keepRelative bool, dir adjustDirection, num, offset int) (string, string, bool, error) {
	if name == "" || (!abs && keepRelative) {
		return "", operand + name, abs, nil
	}
	col, err := ColumnNameToNumber(name)
	if err != nil {
		return "", operand, false, err
	}
	if dir == columns && col >= num {
		col += offset
		colName, err := ColumnNumberToName(col)
		return "", operand + colName, false, err
	}
	return "", operand + name, false, nil
}

// adjustFormulaRowNumber adjust row number in the formula reference.
func adjustFormulaRowNumber(name, operand string, abs, keepRelative bool, dir adjustDirection, num, offset int) (string, string, bool, error) {
	if name == "" || (!abs && keepRelative) {
		return "", operand + name, abs, nil
	}
	row, _ := strconv.Atoi(name)
	if dir == rows && row >= num {
		row += offset
		if row <= 0 || row > TotalRows {
			return "", operand + name, false, ErrMaxRows
		}
		return "", operand + strconv.Itoa(row), false, nil
	}
	return "", operand + name, false, nil
}

// adjustFormulaOperandRef adjust cell reference in the operand tokens for the formula.
func adjustFormulaOperandRef(row, col, operand string, abs, keepRelative bool, dir adjustDirection, num int, offset int) (string, string, string, bool, error) {
	var err error
	col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
	if err != nil {
		return row, col, operand, abs, err
	}
	row, operand, abs, err = adjustFormulaRowNumber(row, operand, abs, keepRelative, dir, num, offset)
	return row, col, operand, abs, err
}

// adjustFormulaOperand adjust range operand tokens for the formula.
func (f *File) adjustFormulaOperand(sheet, sheetN string, keepRelative bool, token efp.Token, dir adjustDirection, num int, offset int) (string, error) {
	var (
		err                          error
		abs                          bool
		sheetName, col, row, operand string
		cell                         = token.TValue
		tokens                       = strings.Split(token.TValue, "!")
	)
	if len(tokens) == 2 { // have a worksheet
		sheetName, cell = tokens[0], tokens[1]
		operand = escapeSheetName(sheetName) + "!"
	}
	if sheetName == "" {
		sheetName = sheetN
	}
	if sheet != sheetName {
		return operand + cell, err
	}
	for _, r := range cell {
		if r == '$' {
			if col, operand, _, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset); err != nil {
				return operand, err
			}
			abs = true
			operand += string(r)
			continue
		}
		if ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z') {
			col += string(r)
			continue
		}
		if '0' <= r && r <= '9' {
			row += string(r)
			col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
			if err != nil {
				return operand, err
			}
			continue
		}
		if row, col, operand, abs, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset); err != nil {
			return operand, err
		}
		operand += string(r)
	}
	_, _, operand, _, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset)
	return operand, err
}

// adjustFormulaRef returns adjusted formula by giving adjusting direction and
// the base number of column or row, and offset.
func (f *File) adjustFormulaRef(sheet, sheetN, formula string, keepRelative bool, dir adjustDirection, num, offset int) (string, error) {
	var (
		val          string
		definedNames []string
		ps           = efp.ExcelParser()
	)
	for _, definedName := range f.GetDefinedName() {
		if definedName.Scope == "Workbook" || definedName.Scope == sheet {
			definedNames = append(definedNames, definedName.Name)
		}
	}
	for _, token := range ps.Parse(formula) {
		if token.TType == efp.TokenTypeUnknown {
			val = formula
			break
		}
		if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeRange {
			if inStrSlice(definedNames, token.TValue, true) != -1 {
				val += token.TValue
				continue
			}
			if strings.ContainsAny(token.TValue, "[]") {
				val += token.TValue
				continue
			}
			operand, err := f.adjustFormulaOperand(sheet, sheetN, keepRelative, token, dir, num, offset)
			if err != nil {
				return val, err
			}
			val += operand
			continue
		}
		if isFunctionStartToken(token) {
			val += token.TValue + string(efp.ParenOpen)
			continue
		}
		if isFunctionStopToken(token) {
			val += token.TValue + string(efp.ParenClose)
			continue
		}
		if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
			val += string(efp.QuoteDouble) + strings.ReplaceAll(token.TValue, "\"", "\"\"") + string(efp.QuoteDouble)
			continue
		}
		val += token.TValue
	}
	return val, nil
}

// arrayFormulaOperandToken defines meta fields for transforming the array
// formula to the normal formula.
type arrayFormulaOperandToken struct {
	operandTokenIndex, topLeftCol, topLeftRow, bottomRightCol, bottomRightRow int
	sheetName, sourceCellRef, targetCellRef                                   string
}

// setCoordinates convert each corner cell reference in the array formula cell
// range to the coordinate number.
func (af *arrayFormulaOperandToken) setCoordinates() error {
	for i, ref := range strings.Split(af.sourceCellRef, ":") {
		cellRef, col, row, err := parseRef(ref)
		if err != nil {
			return err
		}
		var c, r int
		if col {
			if cellRef.Row = TotalRows; i == 1 {
				cellRef.Row = 1
			}
		}
		if row {
			if cellRef.Col = MaxColumns; i == 1 {
				cellRef.Col = 1
			}
		}
		if c, r = cellRef.Col, cellRef.Row; cellRef.Sheet != "" {
			af.sheetName = cellRef.Sheet + "!"
		}
		if af.topLeftCol == 0 || c < af.topLeftCol {
			af.topLeftCol = c
		}
		if af.topLeftRow == 0 || r < af.topLeftRow {
			af.topLeftRow = r
		}
		if c > af.bottomRightCol {
			af.bottomRightCol = c
		}
		if r > af.bottomRightRow {
			af.bottomRightRow = r
		}
	}
	return nil
}

// transformArrayFormula transforms an array formula to the normal formula by
// giving a formula tokens list and formula operand tokens list.
func transformArrayFormula(tokens []efp.Token, afs []arrayFormulaOperandToken) string {
	var val string
	for i, token := range tokens {
		var skip bool
		for _, af := range afs {
			if af.operandTokenIndex == i {
				val += af.sheetName + af.targetCellRef
				skip = true
				break
			}
		}
		if skip {
			continue
		}
		if isFunctionStartToken(token) {
			val += token.TValue + string(efp.ParenOpen)
			continue
		}
		if isFunctionStopToken(token) {
			val += token.TValue + string(efp.ParenClose)
			continue
		}
		if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
			val += string(efp.QuoteDouble) + strings.ReplaceAll(token.TValue, "\"", "\"\"") + string(efp.QuoteDouble)
			continue
		}
		val += token.TValue
	}
	return val
}

// getArrayFormulaTokens returns parsed formula token and operand related token
// list for in array formula.
func getArrayFormulaTokens(sheet, formula string, definedNames []DefinedName) ([]efp.Token, []arrayFormulaOperandToken, error) {
	var (
		ps                        = efp.ExcelParser()
		tokens                    = ps.Parse(formula)
		arrayFormulaOperandTokens []arrayFormulaOperandToken
	)
	for i, token := range tokens {
		if token.TSubType == efp.TokenSubTypeRange && token.TType == efp.TokenTypeOperand {
			tokenVal := token.TValue
			for _, definedName := range definedNames {
				if (definedName.Scope == "Workbook" || definedName.Scope == sheet) && definedName.Name == tokenVal {
					tokenVal = definedName.RefersTo
				}
			}
			if len(strings.Split(tokenVal, ":")) > 1 {
				arrayFormulaOperandToken := arrayFormulaOperandToken{
					operandTokenIndex: i,
					sourceCellRef:     tokenVal,
				}
				if err := arrayFormulaOperandToken.setCoordinates(); err != nil {
					return tokens, arrayFormulaOperandTokens, err
				}
				arrayFormulaOperandTokens = append(arrayFormulaOperandTokens, arrayFormulaOperandToken)
			}
		}
	}
	return tokens, arrayFormulaOperandTokens, nil
}

// adjustHyperlinks provides a function to update hyperlinks when inserting or
// deleting rows or columns.
func (f *File) adjustHyperlinks(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
	// short path
	if ws.Hyperlinks == nil || len(ws.Hyperlinks.Hyperlink) == 0 {
		return
	}

	// order is important
	if offset < 0 {
		for i := len(ws.Hyperlinks.Hyperlink) - 1; i >= 0; i-- {
			linkData := ws.Hyperlinks.Hyperlink[i]
			colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)

			if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
				f.deleteSheetRelationships(sheet, linkData.RID)
				if len(ws.Hyperlinks.Hyperlink) > 1 {
					ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink[:i],
						ws.Hyperlinks.Hyperlink[i+1:]...)
				} else {
					ws.Hyperlinks = nil
				}
			}
		}
	}
	if ws.Hyperlinks == nil {
		return
	}
	for i := range ws.Hyperlinks.Hyperlink {
		link := &ws.Hyperlinks.Hyperlink[i] // get reference
		link.Ref, _ = f.adjustFormulaRef(sheet, sheet, link.Ref, false, dir, num, offset)
	}
}

// adjustTable provides a function to update the table when inserting or
// deleting rows or columns.
func (f *File) adjustTable(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	if ws.TableParts == nil || len(ws.TableParts.TableParts) == 0 {
		return nil
	}
	for idx := 0; idx < len(ws.TableParts.TableParts); idx++ {
		tbl := ws.TableParts.TableParts[idx]
		target := f.getSheetRelationshipsTargetByID(sheet, tbl.RID)
		tableXML := strings.ReplaceAll(target, "..", "xl")
		content, ok := f.Pkg.Load(tableXML)
		if !ok {
			continue
		}
		t := xlsxTable{}
		if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
			Decode(&t); err != nil && err != io.EOF {
			return err
		}
		coordinates, err := rangeRefToCoordinates(t.Ref)
		if err != nil {
			return err
		}
		// Remove the table when deleting the header row of the table
		if dir == rows && num == coordinates[0] && offset == -1 {
			ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
			ws.TableParts.Count = len(ws.TableParts.TableParts)
			idx--
			continue
		}
		coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
		x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
		if y2-y1 < 1 || x2-x1 < 0 {
			ws.TableParts.TableParts = append(ws.TableParts.TableParts[:idx], ws.TableParts.TableParts[idx+1:]...)
			ws.TableParts.Count = len(ws.TableParts.TableParts)
			idx--
			continue
		}
		t.Ref, _ = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
		if t.AutoFilter != nil {
			t.AutoFilter.Ref = t.Ref
		}
		_ = f.setTableColumns(sheet, true, x1, y1, x2, &t)
		// Currently doesn't support query table
		t.TableType, t.TotalsRowCount, t.ConnectionID = "", 0, 0
		table, _ := xml.Marshal(t)
		f.saveFileList(tableXML, table)
	}
	return nil
}

// adjustAutoFilter provides a function to update the auto filter when
// inserting or deleting rows or columns.
func (f *File) adjustAutoFilter(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	if ws.AutoFilter == nil {
		return nil
	}

	coordinates, err := rangeRefToCoordinates(ws.AutoFilter.Ref)
	if err != nil {
		return err
	}
	x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]

	if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) {
		ws.AutoFilter = nil
		for rowIdx := range ws.SheetData.Row {
			rowData := &ws.SheetData.Row[rowIdx]
			if rowData.R != nil && *rowData.R > y1 && *rowData.R <= y2 {
				rowData.Hidden = false
			}
		}
		return err
	}

	coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset)
	x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3]

	ws.AutoFilter.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2})
	return err
}

// adjustAutoFilterHelper provides a function for adjusting auto filter to
// compare and calculate cell reference by the giving adjusting direction,
// operation reference and offset.
func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int {
	if dir == rows {
		if coordinates[1] >= num {
			coordinates[1] += offset
		}
		if coordinates[3] >= num {
			coordinates[3] += offset
		}
		return coordinates
	}
	if coordinates[0] >= num {
		coordinates[0] += offset
	}
	if coordinates[2] >= num {
		coordinates[2] += offset
	}
	return coordinates
}

// adjustMergeCells provides a function to update merged cells when inserting
// or deleting rows or columns.
func (f *File) adjustMergeCells(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	if ws.MergeCells == nil {
		return nil
	}

	for i := 0; i < len(ws.MergeCells.Cells); i++ {
		mergedCells := ws.MergeCells.Cells[i]
		mergedCellsRef := mergedCells.Ref
		if !strings.Contains(mergedCellsRef, ":") {
			mergedCellsRef += ":" + mergedCellsRef
		}
		coordinates, err := rangeRefToCoordinates(mergedCellsRef)
		if err != nil {
			return err
		}
		x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
		if dir == rows {
			if y1 == num && y2 == num && offset < 0 {
				f.deleteMergeCell(ws, i)
				i--
				continue
			}

			y1, y2 = f.adjustMergeCellsHelper(y1, y2, num, offset)
		} else {
			if x1 == num && x2 == num && offset < 0 {
				f.deleteMergeCell(ws, i)
				i--
				continue
			}

			x1, x2 = f.adjustMergeCellsHelper(x1, x2, num, offset)
		}
		if x1 == x2 && y1 == y2 {
			f.deleteMergeCell(ws, i)
			i--
			continue
		}
		mergedCells.rect = []int{x1, y1, x2, y2}
		if mergedCells.Ref, err = f.coordinatesToRangeRef([]int{x1, y1, x2, y2}); err != nil {
			return err
		}
	}
	return nil
}

// adjustMergeCellsHelper provides a function for adjusting merge cells to
// compare and calculate cell reference by the given pivot, operation reference
// and offset.
func (f *File) adjustMergeCellsHelper(p1, p2, num, offset int) (int, int) {
	if p2 < p1 {
		p1, p2 = p2, p1
	}

	if offset >= 0 {
		if num <= p1 {
			p1 += offset
			p2 += offset
		} else if num <= p2 {
			p2 += offset
		}
		return p1, p2
	}
	if num < p1 || (num == p1 && num == p2) {
		p1 += offset
		p2 += offset
	} else if num <= p2 {
		p2 += offset
	}
	return p1, p2
}

// deleteMergeCell provides a function to delete merged cell by given index.
func (f *File) deleteMergeCell(ws *xlsxWorksheet, idx int) {
	if idx < 0 {
		return
	}
	if len(ws.MergeCells.Cells) > idx {
		ws.MergeCells.Cells = append(ws.MergeCells.Cells[:idx], ws.MergeCells.Cells[idx+1:]...)
		ws.MergeCells.Count = len(ws.MergeCells.Cells)
	}
}

// adjustCellName returns updated cell name by giving column/row number and
// offset on inserting or deleting rows or columns.
func adjustCellName(cell string, dir adjustDirection, c, r, offset int) (string, error) {
	if dir == rows {
		if rn := r + offset; rn > 0 {
			return CoordinatesToCellName(c, rn)
		}
	}
	return CoordinatesToCellName(c+offset, r)
}

// adjustCalcChain provides a function to update the calculation chain when
// inserting or deleting rows or columns.
func (f *File) adjustCalcChain(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	if f.CalcChain == nil {
		return nil
	}
	// If sheet ID is omitted, it is assumed to be the same as the i value of
	// the previous cell.
	var prevSheetID int
	for i := 0; i < len(f.CalcChain.C); i++ {
		c := f.CalcChain.C[i]
		if c.I == 0 {
			c.I = prevSheetID
		}
		prevSheetID = c.I
		if c.I != sheetID {
			continue
		}
		colNum, rowNum, err := CellNameToCoordinates(c.R)
		if err != nil {
			return err
		}
		if dir == rows && num <= rowNum {
			if num == rowNum && offset == -1 {
				_ = f.deleteCalcChain(c.I, c.R)
				i--
				continue
			}
			f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
		}
		if dir == columns && num <= colNum {
			if num == colNum && offset == -1 {
				_ = f.deleteCalcChain(c.I, c.R)
				i--
				continue
			}
			f.CalcChain.C[i].R, _ = adjustCellName(c.R, dir, colNum, rowNum, offset)
		}
	}
	return nil
}

// adjustVolatileDepsTopic updates the volatile dependencies topic when
// inserting or deleting rows or columns.
func (vt *xlsxVolTypes) adjustVolatileDepsTopic(cell string, dir adjustDirection, indexes []int) (int, error) {
	num, offset, i1, i2, i3, i4 := indexes[0], indexes[1], indexes[2], indexes[3], indexes[4], indexes[5]
	colNum, rowNum, err := CellNameToCoordinates(cell)
	if err != nil {
		return i4, err
	}
	if dir == rows && num <= rowNum {
		if num == rowNum && offset == -1 {
			vt.deleteVolTopicRef(i1, i2, i3, i4)
			i4--
			return i4, err
		}
		vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
	}
	if dir == columns && num <= colNum {
		if num == colNum && offset == -1 {
			vt.deleteVolTopicRef(i1, i2, i3, i4)
			i4--
			return i4, err
		}
		if name, _ := adjustCellName(cell, dir, colNum, rowNum, offset); name != "" {
			vt.VolType[i1].Main[i2].Tp[i3].Tr[i4].R, _ = adjustCellName(cell, dir, colNum, rowNum, offset)
		}
	}
	return i4, err
}

// adjustVolatileDeps updates the volatile dependencies when inserting or
// deleting rows or columns.
func (f *File) adjustVolatileDeps(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	volTypes, err := f.volatileDepsReader()
	if err != nil || volTypes == nil {
		return err
	}
	for i1 := 0; i1 < len(volTypes.VolType); i1++ {
		for i2 := 0; i2 < len(volTypes.VolType[i1].Main); i2++ {
			for i3 := 0; i3 < len(volTypes.VolType[i1].Main[i2].Tp); i3++ {
				for i4 := 0; i4 < len(volTypes.VolType[i1].Main[i2].Tp[i3].Tr); i4++ {
					ref := volTypes.VolType[i1].Main[i2].Tp[i3].Tr[i4]
					if ref.S != sheetID {
						continue
					}
					if i4, err = volTypes.adjustVolatileDepsTopic(ref.R, dir, []int{num, offset, i1, i2, i3, i4}); err != nil {
						return err
					}
				}
			}
		}
	}
	return nil
}

// adjustConditionalFormats updates the cell reference of the worksheet
// conditional formatting when inserting or deleting rows or columns.
func (f *File) adjustConditionalFormats(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	for i := 0; i < len(ws.ConditionalFormatting); i++ {
		cf := ws.ConditionalFormatting[i]
		if cf == nil {
			continue
		}
		ref, del, err := f.adjustCellRef(cf.SQRef, dir, num, offset)
		if err != nil {
			return err
		}
		if del {
			ws.ConditionalFormatting = append(ws.ConditionalFormatting[:i],
				ws.ConditionalFormatting[i+1:]...)
			i--
			continue
		}
		ws.ConditionalFormatting[i].SQRef = ref
	}
	return nil
}

// adjustDataValidations updates the range of data validations for the worksheet
// when inserting or deleting rows or columns.
func (f *File) adjustDataValidations(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	for _, sheetN := range f.GetSheetList() {
		worksheet, err := f.workSheetReader(sheetN)
		if err != nil {
			if err.Error() == newNotWorksheetError(sheetN).Error() {
				continue
			}
			return err
		}
		if worksheet.DataValidations == nil {
			return nil
		}
		for i := 0; i < len(worksheet.DataValidations.DataValidation); i++ {
			dv := worksheet.DataValidations.DataValidation[i]
			if dv == nil {
				continue
			}
			if sheet == sheetN {
				ref, del, err := f.adjustCellRef(dv.Sqref, dir, num, offset)
				if err != nil {
					return err
				}
				if del {
					worksheet.DataValidations.DataValidation = append(worksheet.DataValidations.DataValidation[:i],
						worksheet.DataValidations.DataValidation[i+1:]...)
					i--
					continue
				}
				worksheet.DataValidations.DataValidation[i].Sqref = ref
			}
			if worksheet.DataValidations.DataValidation[i].Formula1 != nil {
				formula := unescapeDataValidationFormula(worksheet.DataValidations.DataValidation[i].Formula1.Content)
				if formula, err = f.adjustFormulaRef(sheet, sheetN, formula, false, dir, num, offset); err != nil {
					return err
				}
				worksheet.DataValidations.DataValidation[i].Formula1 = &xlsxInnerXML{Content: formulaEscaper.Replace(formula)}
			}
			if worksheet.DataValidations.DataValidation[i].Formula2 != nil {
				formula := unescapeDataValidationFormula(worksheet.DataValidations.DataValidation[i].Formula2.Content)
				if formula, err = f.adjustFormulaRef(sheet, sheetN, formula, false, dir, num, offset); err != nil {
					return err
				}
				worksheet.DataValidations.DataValidation[i].Formula2 = &xlsxInnerXML{Content: formulaEscaper.Replace(formula)}
			}
		}
		if worksheet.DataValidations.Count = len(worksheet.DataValidations.DataValidation); worksheet.DataValidations.Count == 0 {
			worksheet.DataValidations = nil
		}
	}
	return nil
}

// adjustDrawings updates the starting anchor of the two cell anchor pictures
// and charts object when inserting or deleting rows or columns.
func (from *xlsxFrom) adjustDrawings(dir adjustDirection, num, offset int, editAs string) (bool, error) {
	var ok bool
	if dir == columns && from.Col+1 >= num && from.Col+offset >= 0 {
		if from.Col+offset >= MaxColumns {
			return false, ErrColumnNumber
		}
		from.Col += offset
		ok = editAs == "oneCell"
	}
	if dir == rows && from.Row+1 >= num && from.Row+offset >= 0 {
		if from.Row+offset >= TotalRows {
			return false, ErrMaxRows
		}
		from.Row += offset
		ok = editAs == "oneCell"
	}
	return ok, nil
}

// adjustDrawings updates the ending anchor of the two cell anchor pictures
// and charts object when inserting or deleting rows or columns.
func (to *xlsxTo) adjustDrawings(dir adjustDirection, num, offset int, editAs string, ok bool) error {
	if dir == columns && to.Col+1 >= num && to.Col+offset >= 0 && ok {
		if to.Col+offset >= MaxColumns {
			return ErrColumnNumber
		}
		to.Col += offset
	}
	if dir == rows && to.Row+1 >= num && to.Row+offset >= 0 && ok {
		if to.Row+offset >= TotalRows {
			return ErrMaxRows
		}
		to.Row += offset
	}
	return nil
}

// adjustDrawings updates the two cell anchor pictures and charts object when
// inserting or deleting rows or columns.
func (a *xdrCellAnchor) adjustDrawings(dir adjustDirection, num, offset int) error {
	editAs := a.EditAs
	if a.From == nil || a.To == nil || editAs == "absolute" {
		return nil
	}
	ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
	if err != nil {
		return err
	}
	return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
}

// adjustDrawings updates the existing two cell anchor pictures and charts
// object when inserting or deleting rows or columns.
func (a *xlsxCellAnchorPos) adjustDrawings(dir adjustDirection, num, offset int, editAs string) error {
	if a.From == nil || a.To == nil || editAs == "absolute" {
		return nil
	}
	ok, err := a.From.adjustDrawings(dir, num, offset, editAs)
	if err != nil {
		return err
	}
	return a.To.adjustDrawings(dir, num, offset, editAs, ok || editAs == "")
}

// adjustDrawings updates the pictures and charts object when inserting or
// deleting rows or columns.
func (f *File) adjustDrawings(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	if ws.Drawing == nil {
		return nil
	}
	target := f.getSheetRelationshipsTargetByID(sheet, ws.Drawing.RID)
	drawingXML := strings.TrimPrefix(strings.ReplaceAll(target, "..", "xl"), "/")
	var (
		err  error
		wsDr *xlsxWsDr
	)
	if wsDr, _, err = f.drawingParser(drawingXML); err != nil {
		return err
	}
	anchorCb := func(a *xdrCellAnchor) error {
		if a.GraphicFrame == "" {
			return a.adjustDrawings(dir, num, offset)
		}
		deCellAnchor := decodeCellAnchor{}
		deCellAnchorPos := decodeCellAnchorPos{}
		_ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchor>" + a.GraphicFrame + "</decodeCellAnchor>")).Decode(&deCellAnchor)
		_ = f.xmlNewDecoder(strings.NewReader("<decodeCellAnchorPos>" + a.GraphicFrame + "</decodeCellAnchorPos>")).Decode(&deCellAnchorPos)
		xlsxCellAnchorPos := xlsxCellAnchorPos(deCellAnchorPos)
		for i := 0; i < len(xlsxCellAnchorPos.AlternateContent); i++ {
			xlsxCellAnchorPos.AlternateContent[i].XMLNSMC = SourceRelationshipCompatibility.Value
		}
		if deCellAnchor.From != nil {
			xlsxCellAnchorPos.From = &xlsxFrom{
				Col: deCellAnchor.From.Col, ColOff: deCellAnchor.From.ColOff,
				Row: deCellAnchor.From.Row, RowOff: deCellAnchor.From.RowOff,
			}
		}
		if deCellAnchor.To != nil {
			xlsxCellAnchorPos.To = &xlsxTo{
				Col: deCellAnchor.To.Col, ColOff: deCellAnchor.To.ColOff,
				Row: deCellAnchor.To.Row, RowOff: deCellAnchor.To.RowOff,
			}
		}
		if err = xlsxCellAnchorPos.adjustDrawings(dir, num, offset, a.EditAs); err != nil {
			return err
		}
		cellAnchor, _ := xml.Marshal(xlsxCellAnchorPos)
		a.GraphicFrame = strings.TrimSuffix(strings.TrimPrefix(string(cellAnchor), "<xlsxCellAnchorPos>"), "</xlsxCellAnchorPos>")
		return err
	}
	for _, anchor := range wsDr.TwoCellAnchor {
		if err = anchorCb(anchor); err != nil {
			return err
		}
	}
	return nil
}

// adjustDefinedNames updates the cell reference of the defined names when
// inserting or deleting rows or columns.
func (f *File) adjustDefinedNames(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
	wb, err := f.workbookReader()
	if err != nil {
		return err
	}
	if wb.DefinedNames != nil {
		for i := 0; i < len(wb.DefinedNames.DefinedName); i++ {
			data := wb.DefinedNames.DefinedName[i].Data
			if data, err = f.adjustFormulaRef(sheet, "", data, true, dir, num, offset); err == nil {
				wb.DefinedNames.DefinedName[i].Data = data
			}
		}
	}
	return nil
}