-// Copyright 2016 - 2023 The excelize Authors. All rights reserved. Use of
+// 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.
//
// 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.16 or later.
+// data. This library needs Go version 1.18 or later.
package excelize
"io"
"strconv"
"strings"
+ "unicode"
"github.com/xuri/efp"
)
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.
// row: Index number of the row we're inserting/deleting before
// offset: Number of rows/column to insert/delete negative values indicate deletion
//
-// TODO: adjustPageBreaks, adjustComments, adjustDataValidations, adjustProtectedCells
+// TODO: adjustComments, adjustPageBreaks, adjustProtectedCells
func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
f.adjustHyperlinks(ws, sheet, dir, num, offset)
ws.checkSheet()
_ = ws.checkRow()
- f.adjustTable(ws, sheet, dir, num, offset)
- if err = f.adjustMergeCells(ws, dir, num, offset); err != nil {
- return err
- }
- if err = f.adjustAutoFilter(ws, dir, num, offset); err != nil {
- return err
- }
- if err = f.adjustCalcChain(dir, num, offset, sheetID); err != nil {
- return err
+ 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
}
}
for i := 0; i < len(ws.Cols.Col); i++ {
if offset > 0 {
- if ws.Cols.Col[i].Max+1 == col {
- ws.Cols.Col[i].Max += offset
- continue
- }
if ws.Cols.Col[i].Min >= col {
- ws.Cols.Col[i].Min += offset
- ws.Cols.Col[i].Max += offset
- continue
- }
- if ws.Cols.Col[i].Min < col && ws.Cols.Col[i].Max >= col {
- ws.Cols.Col[i].Max += offset
- }
- }
- if offset < 0 {
- if ws.Cols.Col[i].Min == col && ws.Cols.Col[i].Max == col {
- if len(ws.Cols.Col) > 1 {
+ 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:]...)
- } else {
- ws.Cols.Col = nil
+ i--
+ continue
}
- i--
- continue
- }
- if ws.Cols.Col[i].Min > col {
- ws.Cols.Col[i].Min += offset
- ws.Cols.Col[i].Max += offset
- continue
}
- if ws.Cols.Col[i].Min <= col && ws.Cols.Col[i].Max >= col {
- ws.Cols.Col[i].Max += offset
+ 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
}
}
}
}
- for rowIdx := range ws.SheetData.Row {
- for colIdx, v := range ws.SheetData.Row[rowIdx].C {
- if cellCol, cellRow, _ := CellNameToCoordinates(v.R); col <= cellCol {
- if newCol := cellCol + offset; newCol > 0 {
- ws.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
- }
+ for _, sheetN := range f.GetSheetList() {
+ worksheet, err := f.workSheetReader(sheetN)
+ if err != nil {
+ if err.Error() == newNotWorksheetError(sheetN).Error() {
+ continue
}
- if err := f.adjustFormula(sheet, ws.SheetData.Row[rowIdx].C[colIdx].F, columns, col, offset, false); err != nil {
- return err
+ 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
+ }
}
}
}
// 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 {
+ 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 {
+ if newRow := *r.R + offset; *r.R >= row && newRow > 0 {
r.adjustSingleRowDimensions(offset)
}
- if err := f.adjustSingleRowFormulas(sheet, r, row, offset, false); err != nil {
+ if err := f.adjustSingleRowFormulas(sheet, sheet, r, row, offset, false); err != nil {
return err
}
}
// adjustSingleRowDimensions provides a function to adjust single row dimensions.
func (r *xlsxRow) adjustSingleRowDimensions(offset int) {
- r.R += offset
+ r.R = intPtr(*r.R + offset)
for i, col := range r.C {
colName, _, _ := SplitCellName(col.R)
- r.C[i].R, _ = JoinCellName(colName, r.R)
+ r.C[i].R, _ = JoinCellName(colName, *r.R)
}
}
// adjustSingleRowFormulas provides a function to adjust single row formulas.
-func (f *File) adjustSingleRowFormulas(sheet string, r *xlsxRow, num, offset int, si bool) error {
- for _, col := range r.C {
- if err := f.adjustFormula(sheet, col.F, rows, num, offset, si); err != nil {
+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
}
}
}
// adjustCellRef provides a function to adjust cell reference.
-func (f *File) adjustCellRef(ref string, dir adjustDirection, num, offset int) (string, error) {
+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, err
+ return ref, delete, err
}
if dir == columns {
+ if offset < 0 && coordinates[0] == coordinates[2] {
+ delete = true
+ }
if coordinates[0] >= num {
coordinates[0] += offset
}
coordinates[2] += offset
}
} else {
+ if offset < 0 && coordinates[1] == coordinates[3] {
+ delete = true
+ }
if coordinates[1] >= num {
coordinates[1] += offset
}
coordinates[3] += offset
}
}
- return f.coordinatesToRangeRef(coordinates)
+ 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 string, formula *xlsxF, dir adjustDirection, num, offset int, si bool) error {
- if formula == nil {
+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
}
- var err error
- if formula.Ref != "" {
- if formula.Ref, err = f.adjustCellRef(formula.Ref, dir, num, offset); err != 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 && formula.Si != nil {
- formula.Si = intPtr(*formula.Si + 1)
+ if si && cell.F.Si != nil {
+ cell.F.Si = intPtr(*cell.F.Si + 1)
}
}
- if formula.Content != "" {
- if formula.Content, err = f.adjustFormulaRef(sheet, formula.Content, dir, num, offset); err != nil {
+ 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
}
-// isFunctionStop provides a function to check if token is a function stop.
-func isFunctionStop(token efp.Token) bool {
- return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStop
-}
-
-// isFunctionStart provides a function to check if token is a function start.
-func isFunctionStart(token efp.Token) bool {
- return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStart
+// 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 string, dir adjustDirection, num, offset int) (string, error) {
- if name == "" {
- return name, nil
+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 name, err
+ return "", operand, false, err
}
if dir == columns && col >= num {
col += offset
- return ColumnNumberToName(col)
+ colName, err := ColumnNumberToName(col)
+ return "", operand + colName, false, err
}
- return name, nil
+ return "", operand + name, false, nil
}
// adjustFormulaRowNumber adjust row number in the formula reference.
-func adjustFormulaRowNumber(name string, dir adjustDirection, num, offset int) (string, error) {
- if name == "" {
- return name, nil
+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 > TotalRows {
- return name, ErrMaxRows
+ if row <= 0 || row > TotalRows {
+ return "", operand + name, false, ErrMaxRows
}
- return strconv.Itoa(row), nil
+ return "", operand + strconv.Itoa(row), false, nil
}
- return name, nil
+ return "", operand + name, false, nil
}
// adjustFormulaOperandRef adjust cell reference in the operand tokens for the formula.
-func adjustFormulaOperandRef(row, col, operand string, dir adjustDirection, num int, offset int) (string, string, string, error) {
- if col != "" {
- name, err := adjustFormulaColumnName(col, dir, num, offset)
- if err != nil {
- return row, col, operand, err
- }
- operand += name
- col = ""
- }
- if row != "" {
- name, err := adjustFormulaRowNumber(row, dir, num, offset)
- if err != nil {
- return row, col, operand, err
- }
- operand += name
- row = ""
+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
}
- return row, col, operand, nil
+ 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(token efp.Token, dir adjustDirection, num int, offset int) (string, error) {
+func (f *File) adjustFormulaOperand(sheet, sheetN string, keepRelative bool, token efp.Token, dir adjustDirection, num int, offset int) (string, error) {
var (
- err error
- sheet, col, row, operand string
- cell = token.TValue
- tokens = strings.Split(token.TValue, "!")
+ err error
+ abs bool
+ sheetName, col, row, operand string
+ cell = token.TValue
+ tokens = strings.Split(token.TValue, "!")
)
if len(tokens) == 2 { // have a worksheet
- sheet, cell = tokens[0], tokens[1]
- operand = string(efp.QuoteSingle) + sheet + string(efp.QuoteSingle) + "!"
+ 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)
- if col != "" {
- name, err := adjustFormulaColumnName(col, dir, num, offset)
- if err != nil {
- return operand, err
- }
- operand += name
- col = ""
+ col, operand, abs, err = adjustFormulaColumnName(col, operand, abs, keepRelative, dir, num, offset)
+ if err != nil {
+ return operand, err
}
continue
}
- if row, col, operand, err = adjustFormulaOperandRef(row, col, operand, dir, num, offset); err != nil {
+ if row, col, operand, abs, err = adjustFormulaOperandRef(row, col, operand, abs, keepRelative, dir, num, offset); err != nil {
return operand, err
}
operand += string(r)
}
- name, err := adjustFormulaColumnName(col, dir, num, offset)
- if err != nil {
- return operand, err
- }
- operand += name
- name, err = adjustFormulaRowNumber(row, dir, num, offset)
- operand += name
+ _, _, 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, formula string, dir adjustDirection, num, offset int) (string, error) {
+func (f *File) adjustFormulaRef(sheet, sheetN, formula string, keepRelative bool, dir adjustDirection, num, offset int) (string, error) {
var (
val string
definedNames []string
}
}
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
val += token.TValue
continue
}
- operand, err := f.adjustFormulaOperand(token, dir, num, offset)
+ operand, err := f.adjustFormulaOperand(sheet, sheetN, keepRelative, token, dir, num, offset)
if err != nil {
return val, err
}
val += operand
continue
}
- if isFunctionStart(token) {
+ if isFunctionStartToken(token) {
val += token.TValue + string(efp.ParenOpen)
continue
}
- if isFunctionStop(token) {
+ if isFunctionStopToken(token) {
val += token.TValue + string(efp.ParenClose)
continue
}
if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
- val += string(efp.QuoteDouble) + token.TValue + string(efp.QuoteDouble)
+ val += string(efp.QuoteDouble) + strings.ReplaceAll(token.TValue, "\"", "\"\"") + string(efp.QuoteDouble)
continue
}
val += token.TValue
return val, nil
}
+// adjustRangeSheetName returns replaced range reference by given source and
+// target sheet name.
+func adjustRangeSheetName(rng, source, target string) string {
+ cellRefs := strings.Split(rng, ",")
+ for i, cellRef := range cellRefs {
+ rangeRefs := strings.Split(cellRef, ":")
+ for j, rangeRef := range rangeRefs {
+ parts := strings.Split(rangeRef, "!")
+ for k, part := range parts {
+ singleQuote := strings.HasPrefix(part, "'") && strings.HasSuffix(part, "'")
+ if singleQuote {
+ part = strings.TrimPrefix(strings.TrimSuffix(part, "'"), "'")
+ }
+ if part == source {
+ if part = target; singleQuote {
+ part = "'" + part + "'"
+ }
+ }
+ parts[k] = part
+ }
+ rangeRefs[j] = strings.Join(parts, "!")
+ }
+ cellRefs[i] = strings.Join(rangeRefs, ":")
+ }
+ return strings.Join(cellRefs, ",")
+}
+
+// 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 == 0 {
+ cellRef.Row = 1
+ }
+ }
+ if row {
+ if cellRef.Col = MaxColumns; i == 0 {
+ 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) {
}
for i := range ws.Hyperlinks.Hyperlink {
link := &ws.Hyperlinks.Hyperlink[i] // get reference
- link.Ref, _ = f.adjustFormulaRef(sheet, link.Ref, dir, num, offset)
+ 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 int) {
+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
+ return nil
}
for idx := 0; idx < len(ws.TableParts.TableParts); idx++ {
tbl := ws.TableParts.TableParts[idx]
t := xlsxTable{}
if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
Decode(&t); err != nil && err != io.EOF {
- return
+ return err
}
coordinates, err := rangeRefToCoordinates(t.Ref)
if err != nil {
- return
+ return err
}
// Remove the table when deleting the header row of the table
if dir == rows && num == coordinates[0] && offset == -1 {
t.AutoFilter.Ref = t.Ref
}
_ = f.setTableColumns(sheet, true, x1, y1, x2, &t)
- t.TotalsRowCount = 0
+ // 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, dir adjustDirection, num, offset int) error {
+func (f *File) adjustAutoFilter(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
if ws.AutoFilter == nil {
return nil
}
ws.AutoFilter = nil
for rowIdx := range ws.SheetData.Row {
rowData := &ws.SheetData.Row[rowIdx]
- if rowData.R > y1 && rowData.R <= y2 {
+ if rowData.R != nil && *rowData.R > y1 && *rowData.R <= y2 {
rowData.Hidden = false
}
}
// adjustMergeCells provides a function to update merged cells when inserting
// or deleting rows or columns.
-func (f *File) adjustMergeCells(ws *xlsxWorksheet, dir adjustDirection, num, offset int) error {
+func (f *File) adjustMergeCells(ws *xlsxWorksheet, sheet string, dir adjustDirection, num, offset, sheetID int) error {
if ws.MergeCells == nil {
return nil
}
}
// adjustMergeCellsHelper provides a function for adjusting merge cells to
-// compare and calculate cell reference by the given pivot, operation reference and
-// offset.
+// 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
}
}
-// adjustCalcChainRef update the cell reference in calculation chain when
-// inserting or deleting rows or columns.
-func (f *File) adjustCalcChainRef(i, c, r, offset int, dir adjustDirection) {
+// 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 {
- f.CalcChain.C[i].R, _ = CoordinatesToCellName(c, rn)
+ return CoordinatesToCellName(c, rn)
}
- return
- }
- if nc := c + offset; nc > 0 {
- f.CalcChain.C[i].R, _ = CoordinatesToCellName(nc, r)
}
+ 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(dir adjustDirection, num, offset, sheetID int) error {
+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 index, c := range f.CalcChain.C {
+ for i := 0; i < len(f.CalcChain.C); i++ {
+ c := f.CalcChain.C[i]
if c.I == 0 {
c.I = prevSheetID
}
if dir == rows && num <= rowNum {
if num == rowNum && offset == -1 {
_ = f.deleteCalcChain(c.I, c.R)
+ i--
continue
}
- f.adjustCalcChainRef(index, colNum, rowNum, offset, dir)
+ 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
}
- f.adjustCalcChainRef(index, colNum, rowNum, offset, dir)
+ 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