diff options
Diffstat (limited to 'adjust.go')
| -rw-r--r-- | adjust.go | 225 |
1 files changed, 157 insertions, 68 deletions
@@ -1,15 +1,18 @@ -// Copyright 2016 - 2019 The excelize Authors. All rights reserved. Use of +// Copyright 2016 - 2020 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 XLSX files. Support reads and writes XLSX file generated by // Microsoft Excel™ 2007 and later. Support save file without losing original -// charts of XLSX. This library needs Go version 1.8 or later. +// charts of XLSX. This library needs Go version 1.10 or later. package excelize -import "strings" +import ( + "errors" + "strings" +) type adjustDirection bool @@ -27,8 +30,7 @@ const ( // row: Index number of the row we're inserting/deleting before // offset: Number of rows/column to insert/delete negative values indicate deletion // -// TODO: adjustCalcChain, adjustPageBreaks, adjustComments, -// adjustDataValidations, adjustProtectedCells +// TODO: adjustPageBreaks, adjustComments, adjustDataValidations, adjustProtectedCells // func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error { xlsx, err := f.workSheetReader(sheet) @@ -47,9 +49,16 @@ func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) if err = f.adjustAutoFilter(xlsx, dir, num, offset); err != nil { return err } - + if err = f.adjustCalcChain(dir, num, offset); err != nil { + return err + } checkSheet(xlsx) - checkRow(xlsx) + _ = checkRow(xlsx) + + if xlsx.MergeCells != nil && len(xlsx.MergeCells.Cells) == 0 { + xlsx.MergeCells = nil + } + return nil } @@ -71,9 +80,10 @@ func (f *File) adjustColDimensions(xlsx *xlsxWorksheet, col, offset int) { // adjustRowDimensions provides a function to update row dimensions when // inserting or deleting rows or columns. func (f *File) adjustRowDimensions(xlsx *xlsxWorksheet, row, offset int) { - for i, r := range xlsx.SheetData.Row { + for i := range xlsx.SheetData.Row { + r := &xlsx.SheetData.Row[i] if newRow := r.R + offset; r.R >= row && newRow > 0 { - f.ajustSingleRowDimensions(&xlsx.SheetData.Row[i], newRow) + f.ajustSingleRowDimensions(r, newRow) } } } @@ -139,48 +149,103 @@ func (f *File) adjustAutoFilter(xlsx *xlsxWorksheet, dir adjustDirection, num, o return nil } - rng := strings.Split(xlsx.AutoFilter.Ref, ":") - firstCell := rng[0] - lastCell := rng[1] - - firstCol, firstRow, err := CellNameToCoordinates(firstCell) - if err != nil { - return err - } - - lastCol, lastRow, err := CellNameToCoordinates(lastCell) + coordinates, err := f.areaRefToCoordinates(xlsx.AutoFilter.Ref) if err != nil { return err } + x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3] - if (dir == rows && firstRow == num && offset < 0) || (dir == columns && firstCol == num && lastCol == num) { + if (dir == rows && y1 == num && offset < 0) || (dir == columns && x1 == num && x2 == num) { xlsx.AutoFilter = nil for rowIdx := range xlsx.SheetData.Row { rowData := &xlsx.SheetData.Row[rowIdx] - if rowData.R > firstRow && rowData.R <= lastRow { + if rowData.R > y1 && rowData.R <= y2 { rowData.Hidden = false } } return nil } + coordinates = f.adjustAutoFilterHelper(dir, coordinates, num, offset) + x1, y1, x2, y2 = coordinates[0], coordinates[1], coordinates[2], coordinates[3] + + if xlsx.AutoFilter.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil { + return err + } + return nil +} + +// adjustAutoFilterHelper provides a function for adjusting auto filter to +// compare and calculate cell axis by the given adjust direction, operation +// axis and offset. +func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, num, offset int) []int { if dir == rows { - if firstRow >= num { - firstCell, _ = CoordinatesToCellName(firstCol, firstRow+offset) + if coordinates[1] >= num { + coordinates[1] += offset } - if lastRow >= num { - lastCell, _ = CoordinatesToCellName(lastCol, lastRow+offset) + if coordinates[3] >= num { + coordinates[3] += offset } } else { - if lastCol >= num { - lastCell, _ = CoordinatesToCellName(lastCol+offset, lastRow) + if coordinates[2] >= num { + coordinates[2] += offset } } + return coordinates +} + +// areaRefToCoordinates provides a function to convert area reference to a +// pair of coordinates. +func (f *File) areaRefToCoordinates(ref string) ([]int, error) { + rng := strings.Split(ref, ":") + return areaRangeToCoordinates(rng[0], rng[1]) +} - xlsx.AutoFilter.Ref = firstCell + ":" + lastCell +// areaRangeToCoordinates provides a function to convert cell range to a +// pair of coordinates. +func areaRangeToCoordinates(firstCell, lastCell string) ([]int, error) { + coordinates := make([]int, 4) + var err error + coordinates[0], coordinates[1], err = CellNameToCoordinates(firstCell) + if err != nil { + return coordinates, err + } + coordinates[2], coordinates[3], err = CellNameToCoordinates(lastCell) + return coordinates, err +} + +// sortCoordinates provides a function to correct the coordinate area, such +// correct C1:B3 to B1:C3. +func sortCoordinates(coordinates []int) error { + if len(coordinates) != 4 { + return errors.New("coordinates length must be 4") + } + if coordinates[2] < coordinates[0] { + coordinates[2], coordinates[0] = coordinates[0], coordinates[2] + } + if coordinates[3] < coordinates[1] { + coordinates[3], coordinates[1] = coordinates[1], coordinates[3] + } return nil } +// coordinatesToAreaRef provides a function to convert a pair of coordinates +// to area reference. +func (f *File) coordinatesToAreaRef(coordinates []int) (string, error) { + if len(coordinates) != 4 { + return "", errors.New("coordinates length must be 4") + } + firstCell, err := CoordinatesToCellName(coordinates[0], coordinates[1]) + if err != nil { + return "", err + } + lastCell, err := CoordinatesToCellName(coordinates[2], coordinates[3]) + if err != nil { + return "", err + } + return firstCell + ":" + lastCell, err +} + // adjustMergeCells provides a function to update merged cells when inserting // or deleting rows or columns. func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, offset int) error { @@ -188,58 +253,82 @@ func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, o return nil } - for i, areaData := range xlsx.MergeCells.Cells { - rng := strings.Split(areaData.Ref, ":") - firstCell := rng[0] - lastCell := rng[1] - - firstCol, firstRow, err := CellNameToCoordinates(firstCell) - if err != nil { - return err - } - - lastCol, lastRow, err := CellNameToCoordinates(lastCell) + for i := 0; i < len(xlsx.MergeCells.Cells); i++ { + areaData := xlsx.MergeCells.Cells[i] + coordinates, err := f.areaRefToCoordinates(areaData.Ref) if err != nil { return err } - - adjust := func(v int) int { - if v >= num { - v += offset - if v < 1 { - return 1 - } - return v - } - return v - } - + x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3] if dir == rows { - firstRow = adjust(firstRow) - lastRow = adjust(lastRow) + if y1 == num && y2 == num && offset < 0 { + f.deleteMergeCell(xlsx, i) + i-- + } + y1 = f.adjustMergeCellsHelper(y1, num, offset) + y2 = f.adjustMergeCellsHelper(y2, num, offset) } else { - firstCol = adjust(firstCol) - lastCol = adjust(lastCol) - } - - if firstCol == lastCol && firstRow == lastRow { - if len(xlsx.MergeCells.Cells) > 1 { - xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells[:i], xlsx.MergeCells.Cells[i+1:]...) - xlsx.MergeCells.Count = len(xlsx.MergeCells.Cells) - } else { - xlsx.MergeCells = nil + if x1 == num && x2 == num && offset < 0 { + f.deleteMergeCell(xlsx, i) + i-- } + x1 = f.adjustMergeCellsHelper(x1, num, offset) + x2 = f.adjustMergeCellsHelper(x2, num, offset) } - - if firstCell, err = CoordinatesToCellName(firstCol, firstRow); err != nil { + if x1 == x2 && y1 == y2 { + f.deleteMergeCell(xlsx, i) + i-- + } + if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil { return err } + } + return nil +} + +// adjustMergeCellsHelper provides a function for adjusting merge cells to +// compare and calculate cell axis by the given pivot, operation axis and +// offset. +func (f *File) adjustMergeCellsHelper(pivot, num, offset int) int { + if pivot >= num { + pivot += offset + if pivot < 1 { + return 1 + } + return pivot + } + return pivot +} - if lastCell, err = CoordinatesToCellName(lastCol, lastRow); err != nil { +// deleteMergeCell provides a function to delete merged cell by given index. +func (f *File) deleteMergeCell(sheet *xlsxWorksheet, idx int) { + if len(sheet.MergeCells.Cells) > idx { + sheet.MergeCells.Cells = append(sheet.MergeCells.Cells[:idx], sheet.MergeCells.Cells[idx+1:]...) + sheet.MergeCells.Count = len(sheet.MergeCells.Cells) + } +} + +// adjustCalcChain provides a function to update the calculation chain when +// inserting or deleting rows or columns. +func (f *File) adjustCalcChain(dir adjustDirection, num, offset int) error { + if f.CalcChain == nil { + return nil + } + for index, c := range f.CalcChain.C { + colNum, rowNum, err := CellNameToCoordinates(c.R) + if err != nil { return err } - - areaData.Ref = firstCell + ":" + lastCell + if dir == rows && num <= rowNum { + if newRow := rowNum + offset; newRow > 0 { + f.CalcChain.C[index].R, _ = CoordinatesToCellName(colNum, newRow) + } + } + if dir == columns && num <= colNum { + if newCol := colNum + offset; newCol > 0 { + f.CalcChain.C[index].R, _ = CoordinatesToCellName(newCol, rowNum) + } + } } return nil } |