-// 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
worksheet.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
}
}
- if err := f.adjustFormula(sheet, sheetN, worksheet.SheetData.Row[rowIdx].C[colIdx].F, columns, col, offset, false); err != nil {
+ if err := f.adjustFormula(sheet, sheetN, &worksheet.SheetData.Row[rowIdx].C[colIdx], columns, col, offset, false); err != nil {
return err
}
}
// 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 _, col := range r.C {
- if err := f.adjustFormula(sheet, sheetN, col.F, rows, num, offset, si); err != nil {
+ 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
}
}
// adjustFormula provides a function to adjust formula reference and shared
// formula reference.
-func (f *File) adjustFormula(sheet, sheetN 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 != "" && sheet == sheetN {
- 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, sheetN, formula.Content, false, 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 {
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
}
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) {
if dir == rows {
if y1 == num && y2 == num && offset < 0 {
f.deleteMergeCell(ws, i)
+ i--
continue
}
} else {
if x1 == num && x2 == num && offset < 0 {
f.deleteMergeCell(ws, i)
+ i--
continue
}
}
// 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
OSDN Git Service
