142 Star 801 Fork 149

GVPxuri / excelize

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
克隆/下载
adjust.go 36.09 KB
一键复制 编辑 Web IDE 原始数据 按行查看 历史
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150
// 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 += 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(cellRef string, dir adjustDirection, num, offset int) (string, error) {
var SQRef []string
applyOffset := func(coordinates []int, idx1, idx2, maxVal int) []int {
if coordinates[idx1] >= num {
coordinates[idx1] += offset
}
if coordinates[idx2] >= num {
if coordinates[idx2] += offset; coordinates[idx2] > maxVal {
coordinates[idx2] = maxVal
}
}
return coordinates
}
for _, ref := range strings.Split(cellRef, " ") {
if !strings.Contains(ref, ":") {
ref += ":" + ref
}
coordinates, err := rangeRefToCoordinates(ref)
if err != nil {
return "", err
}
if dir == columns {
if offset < 0 && coordinates[0] == coordinates[2] {
continue
}
coordinates = applyOffset(coordinates, 0, 2, MaxColumns)
} else {
if offset < 0 && coordinates[1] == coordinates[3] {
continue
}
coordinates = applyOffset(coordinates, 1, 3, TotalRows)
}
if ref, err = coordinatesToRangeRef(coordinates); err != nil {
return "", err
}
SQRef = append(SQRef, ref)
}
return strings.Join(SQRef, " "), nil
}
// 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 {
if col += offset; col < 1 {
col = 1
}
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 {
if row += offset; row < 1 {
row = 1
}
if 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 paren := transformParenthesesToken(token); paren != "" {
val += transformParenthesesToken(token)
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
}
// transformParenthesesToken returns formula part with parentheses by given
// token.
func transformParenthesesToken(token efp.Token) string {
if isFunctionStartToken(token) || isBeginParenthesesToken(token) {
return token.TValue + string(efp.ParenOpen)
}
if isFunctionStopToken(token) || isEndParenthesesToken(token) {
return token.TValue + string(efp.ParenClose)
}
return ""
}
// 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 paren := transformParenthesesToken(token); paren != "" {
val += transformParenthesesToken(token)
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, _ = 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 > 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 = 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 = 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, err := f.adjustCellRef(cf.SQRef, dir, num, offset)
if err != nil {
return err
}
if ref == "" {
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, err := f.adjustCellRef(dv.Sqref, dir, num, offset)
if err != nil {
return err
}
if ref == "" {
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 := formulaUnescaper.Replace(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 := formulaUnescaper.Replace(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
}
Go
1
https://gitee.com/xurime/excelize.git
git@gitee.com:xurime/excelize.git
xurime
excelize
excelize
master

搜索帮助

14c37bed 8189591 565d56ea 8189591