From 421f945f51f254054991127758db0520cf0f5456 Mon Sep 17 00:00:00 2001 From: xuri Date: Sat, 8 Jun 2019 00:00:55 +0800 Subject: Fixed #418, #420, #421, init adjust calculation chain support Update testing case --- adjust.go | 32 +++++++++++++++++++++++++++++--- 1 file changed, 29 insertions(+), 3 deletions(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index 51db57e..d88990d 100644 --- a/adjust.go +++ b/adjust.go @@ -27,8 +27,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,7 +46,9 @@ 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) return nil @@ -243,3 +244,28 @@ func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, o } return nil } + +// 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 + } + 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 +} -- cgit v1.2.1 From 821632cf89422b9955160a3af7f28f05a12f70f8 Mon Sep 17 00:00:00 2001 From: xuri Date: Wed, 12 Jun 2019 08:10:33 +0800 Subject: Fix #424, refactor merged cells adjuster --- adjust.go | 161 ++++++++++++++++++++++++++++++++++++++------------------------ 1 file changed, 100 insertions(+), 61 deletions(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index d88990d..56d812f 100644 --- a/adjust.go +++ b/adjust.go @@ -9,7 +9,10 @@ package excelize -import "strings" +import ( + "errors" + "strings" +) type adjustDirection bool @@ -140,46 +143,85 @@ 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) + coordinates, err := f.areaRefToCoordinates(xlsx.AutoFilter.Ref) if err != nil { return err } + x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3] - lastCol, lastRow, err := CellNameToCoordinates(lastCell) - if err != nil { - return err - } - - 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 +} - xlsx.AutoFilter.Ref = firstCell + ":" + lastCell - return nil +// areaRefToCoordinates provides a function to convert area reference to a +// pair of coordinates. +func (f *File) areaRefToCoordinates(ref string) ([]int, error) { + coordinates := make([]int, 4) + rng := strings.Split(ref, ":") + firstCell := rng[0] + lastCell := rng[1] + var err error + coordinates[0], coordinates[1], err = CellNameToCoordinates(firstCell) + if err != nil { + return coordinates, err + } + coordinates[2], coordinates[3], err = CellNameToCoordinates(lastCell) + if err != nil { + return coordinates, err + } + return coordinates, err +} + +// 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 @@ -190,59 +232,56 @@ func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, o } for i, areaData := range xlsx.MergeCells.Cells { - rng := strings.Split(areaData.Ref, ":") - firstCell := rng[0] - lastCell := rng[1] - - firstCol, firstRow, err := CellNameToCoordinates(firstCell) + coordinates, err := f.areaRefToCoordinates(areaData.Ref) if err != nil { return err } - - lastCol, lastRow, err := CellNameToCoordinates(lastCell) - 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) + } + 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) } + 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) + } + if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil { return err } + } + return nil +} - if lastCell, err = CoordinatesToCellName(lastCol, lastRow); err != nil { - return err +// 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 +} - areaData.Ref = firstCell + ":" + lastCell +// deleteMergeCell provides a function to delete merged cell by given index. +func (f *File) deleteMergeCell(sheet *xlsxWorksheet, idx int) { + if len(sheet.MergeCells.Cells) > 1 { + sheet.MergeCells.Cells = append(sheet.MergeCells.Cells[:idx], sheet.MergeCells.Cells[idx+1:]...) + sheet.MergeCells.Count = len(sheet.MergeCells.Cells) + } else { + sheet.MergeCells = nil } - return nil } // adjustCalcChain provides a function to update the calculation chain when -- cgit v1.2.1 From 855c3605f6fce4916cdde1dadba2dd73d9f4b744 Mon Sep 17 00:00:00 2001 From: xuri Date: Sat, 20 Jul 2019 19:24:57 +0800 Subject: Fix #437, recalculate offset for merged cells adjuster --- adjust.go | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index 56d812f..f26f132 100644 --- a/adjust.go +++ b/adjust.go @@ -231,7 +231,8 @@ func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, o return nil } - for i, areaData := range xlsx.MergeCells.Cells { + 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 @@ -240,18 +241,21 @@ func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, o if dir == rows { 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 { 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 x1 == x2 && y1 == y2 { f.deleteMergeCell(xlsx, i) + i-- } if areaData.Ref, err = f.coordinatesToAreaRef([]int{x1, y1, x2, y2}); err != nil { return err @@ -276,7 +280,7 @@ func (f *File) adjustMergeCellsHelper(pivot, num, offset int) int { // deleteMergeCell provides a function to delete merged cell by given index. func (f *File) deleteMergeCell(sheet *xlsxWorksheet, idx int) { - if len(sheet.MergeCells.Cells) > 1 { + 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) } else { -- cgit v1.2.1 From 1092009541430c711676efb95b876598f59bb53c Mon Sep 17 00:00:00 2001 From: xuri Date: Sat, 3 Aug 2019 23:10:01 +0800 Subject: Fixed doc corruption when deleting all merged cells --- adjust.go | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index f26f132..ccc5ce9 100644 --- a/adjust.go +++ b/adjust.go @@ -54,6 +54,11 @@ func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) } checkSheet(xlsx) checkRow(xlsx) + + if xlsx.MergeCells != nil && len(xlsx.MergeCells.Cells) == 0 { + xlsx.MergeCells = nil + } + return nil } @@ -283,8 +288,6 @@ 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) - } else { - sheet.MergeCells = nil } } -- cgit v1.2.1 From 9c70d0ac868f66badf2663cc7b4b3c46d5411131 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 11 Aug 2019 00:36:14 +0800 Subject: Documentation updated, Go 1.10+ required --- adjust.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index ccc5ce9..186112d 100644 --- a/adjust.go +++ b/adjust.go @@ -5,7 +5,7 @@ // 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 -- cgit v1.2.1 From da0d2ffbb6ebdfb7b1e5cf501a1986421311017b Mon Sep 17 00:00:00 2001 From: xuri Date: Sat, 14 Dec 2019 19:57:37 +0800 Subject: Fix #533, add support overlapped mergecells --- adjust.go | 3 --- 1 file changed, 3 deletions(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index 186112d..bb583f1 100644 --- a/adjust.go +++ b/adjust.go @@ -206,9 +206,6 @@ func (f *File) areaRefToCoordinates(ref string) ([]int, error) { return coordinates, err } coordinates[2], coordinates[3], err = CellNameToCoordinates(lastCell) - if err != nil { - return coordinates, err - } return coordinates, err } -- cgit v1.2.1 From 4e4a5b9b3e052d1694442515492792fb1aa74c5a Mon Sep 17 00:00:00 2001 From: xuri Date: Mon, 23 Dec 2019 00:07:40 +0800 Subject: Improve compatibility, fix workbook's rels ID calc error --- adjust.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index bb583f1..c15d4b4 100644 --- a/adjust.go +++ b/adjust.go @@ -53,7 +53,7 @@ func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) return err } checkSheet(xlsx) - checkRow(xlsx) + _ = checkRow(xlsx) if xlsx.MergeCells != nil && len(xlsx.MergeCells.Cells) == 0 { xlsx.MergeCells = nil -- cgit v1.2.1 From 5c87effc7e6c97fff36a56dea1afac8a2f06fb37 Mon Sep 17 00:00:00 2001 From: Cameron Howey Date: Sat, 28 Dec 2019 20:45:10 -0800 Subject: Stream to Excel table (#530) * Support all datatypes for StreamWriter * Support setting styles with StreamWriter **NOTE:** This is a breaking change. Values are now explicitly passed as a []interface{} for simplicity. We also let styles to be set at the same time. * Create function to write stream into a table * Write rows directly to buffer Avoiding the xml.Encoder makes the streamer faster and use less memory. Using the included benchmark, the results went from: > BenchmarkStreamWriter-4 514 2576155 ns/op 454918 B/op 6592 allocs/op down to: > BenchmarkStreamWriter-4 1614 777480 ns/op 147608 B/op 5570 allocs/op * Use AddTable instead of SetTable This requires reading the cells after they have been written, which requires additional structure for the temp file. As a bonus, we now efficiently allocate only one buffer when reading the file back into memory, using the same approach as ioutil.ReadFile. * Use an exported Cell type to handle inline styles for StreamWriter --- adjust.go | 23 ++++++++++++++++++++--- 1 file changed, 20 insertions(+), 3 deletions(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index c15d4b4..69ded1b 100644 --- a/adjust.go +++ b/adjust.go @@ -196,10 +196,14 @@ func (f *File) adjustAutoFilterHelper(dir adjustDirection, coordinates []int, nu // areaRefToCoordinates provides a function to convert area reference to a // pair of coordinates. func (f *File) areaRefToCoordinates(ref string) ([]int, error) { - coordinates := make([]int, 4) rng := strings.Split(ref, ":") - firstCell := rng[0] - lastCell := rng[1] + return areaRangeToCoordinates(rng[0], rng[1]) +} + +// 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 { @@ -209,6 +213,19 @@ func (f *File) areaRefToCoordinates(ref string) ([]int, error) { return coordinates, err } +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) { -- cgit v1.2.1 From 09485b3f9f0aefc58d51462aed65c2416205c591 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 29 Dec 2019 16:02:31 +0800 Subject: Improve code coverage unit tests --- adjust.go | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index 69ded1b..bedeec0 100644 --- a/adjust.go +++ b/adjust.go @@ -1,4 +1,4 @@ -// 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. // @@ -213,6 +213,8 @@ func areaRangeToCoordinates(firstCell, lastCell string) ([]int, error) { 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") -- cgit v1.2.1 From 0f2a9053246c3ae45e6c7ba911a1fb135664abdf Mon Sep 17 00:00:00 2001 From: xuri Date: Thu, 2 Apr 2020 00:41:14 +0800 Subject: Performance improvements --- adjust.go | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'adjust.go') diff --git a/adjust.go b/adjust.go index bedeec0..5056839 100644 --- a/adjust.go +++ b/adjust.go @@ -80,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) } } } -- cgit v1.2.1