-// 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
)
// adjustHelperFunc defines functions to adjust helper.
-var adjustHelperFunc = [7]func(*File, *xlsxWorksheet, string, adjustDirection, int, int, int) error{
+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)
},
// 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, adjustDataValidations, adjustPageBreaks, 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 {
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
}
}
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, sheet, r, row, offset, false); err != nil {
// 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, 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
}
}
}
// 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, 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 {
sheetName, cell = tokens[0], tokens[1]
operand = escapeSheetName(sheetName) + "!"
}
- if sheet != sheetN && sheet != sheetName {
+ if sheetName == "" {
+ sheetName = sheetN
+ }
+ if sheet != sheetName {
return operand + cell, err
}
for _, r := range cell {
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) {
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
}
}
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
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) {
OSDN Git Service
