summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--README.md2
-rw-r--r--README_zh.md2
-rw-r--r--adjust.go3
-rw-r--r--calcchain.go2
-rw-r--r--cell.go90
-rw-r--r--cell_test.go30
-rw-r--r--cellmerged.go153
-rw-r--r--cellmerged_test.go109
-rw-r--r--chart.go29
-rw-r--r--chart_test.go2
-rw-r--r--col_test.go7
-rw-r--r--excelize.go22
-rw-r--r--excelize_test.go72
-rw-r--r--file_test.go27
-rw-r--r--lib.go11
-rw-r--r--picture.go14
-rw-r--r--pivotTable.go41
-rw-r--r--pivotTable_test.go6
-rw-r--r--rows.go129
-rw-r--r--rows_test.go73
-rw-r--r--sheet.go169
-rw-r--r--sheet_test.go19
-rw-r--r--sheetpr.go166
-rw-r--r--sheetpr_test.go161
-rw-r--r--stream.go218
-rw-r--r--stream_test.go66
-rw-r--r--templates.go4
-rw-r--r--xmlChart.go7
-rw-r--r--xmlDrawing.go8
-rw-r--r--xmlPivotCache.go4
-rw-r--r--xmlPivotTable.go4
-rw-r--r--xmlTable.go1
-rw-r--r--xmlWorkbook.go2
-rw-r--r--xmlWorksheet.go220
34 files changed, 1523 insertions, 350 deletions
diff --git a/README.md b/README.md
index 998a4c1..03e33ae 100644
--- a/README.md
+++ b/README.md
@@ -182,6 +182,4 @@ This program is under the terms of the BSD 3-Clause License. See [https://openso
The Excel logo is a trademark of [Microsoft Corporation](https://aka.ms/trademarks-usage). This artwork is an adaptation.
-Some struct of XML originally by [tealeg/xlsx](https://github.com/tealeg/xlsx). Licensed under the [BSD 3-Clause License](https://github.com/tealeg/xlsx/blob/master/LICENSE).
-
gopher.{ai,svg,png} was created by [Takuya Ueda](https://twitter.com/tenntenn). Licensed under the [Creative Commons 3.0 Attributions license](http://creativecommons.org/licenses/by/3.0/).
diff --git a/README_zh.md b/README_zh.md
index d4cac66..57cd645 100644
--- a/README_zh.md
+++ b/README_zh.md
@@ -182,6 +182,4 @@ func main() {
Excel 徽标是 [Microsoft Corporation](https://aka.ms/trademarks-usage) 的商标，项目的图片是一种改编。
-本类库中部分 XML 结构体的定义参考了开源项目：[tealeg/xlsx](https://github.com/tealeg/xlsx)，遵循 [BSD 3-Clause License](https://github.com/tealeg/xlsx/blob/master/LICENSE) 开源许可协议。
-
gopher.{ai,svg,png} 由 [Takuya Ueda](https://twitter.com/tenntenn) 创作，遵循 [Creative Commons 3.0 Attributions license](http://creativecommons.org/licenses/by/3.0/) 创作共用授权条款。
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
}
diff --git a/calcchain.go b/calcchain.go
index 413f470..a3d3820 100644
--- a/calcchain.go
+++ b/calcchain.go
@@ -66,7 +66,7 @@ type xlsxCalcChainCollection []xlsxCalcChainC
// Filter provides a function to filter calculation chain.
func (c xlsxCalcChainCollection) Filter(fn func(v xlsxCalcChainC) bool) []xlsxCalcChainC {
- results := make([]xlsxCalcChainC, 0)
+ var results []xlsxCalcChainC
for _, v := range c {
if fn(v) {
results = append(results, v)
diff --git a/cell.go b/cell.go
index f9868de..ad4bcdb 100644
--- a/cell.go
+++ b/cell.go
@@ -228,8 +228,8 @@ func (f *File) SetCellStr(sheet, axis, value string) error {
if len(value) > 32767 {
value = value[0:32767]
}
- // Leading space(s) character detection.
- if len(value) > 0 && value[0] == 32 {
+ // Leading and ending space(s) character detection.
+ if len(value) > 0 && (value[0] == 32 || value[len(value)-1] == 32) {
cellData.XMLSpace = xml.Attr{
Name: xml.Name{Space: NameSpaceXML, Local: "space"},
Value: "preserve",
@@ -412,63 +412,6 @@ func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) error {
return nil
}
-// MergeCell provides a function to merge cells by given coordinate area and
-// sheet name. For example create a merged cell of D3:E9 on Sheet1:
-//
-// err := f.MergeCell("Sheet1", "D3", "E9")
-//
-// If you create a merged cell that overlaps with another existing merged cell,
-// those merged cells that already exist will be removed.
-func (f *File) MergeCell(sheet, hcell, vcell string) error {
- coordinates, err := f.areaRefToCoordinates(hcell + ":" + vcell)
- if err != nil {
- return err
- }
- x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
-
- if x1 == x2 && y1 == y2 {
- return err
- }
-
- // Correct the coordinate area, such correct C1:B3 to B1:C3.
- if x2 < x1 {
- x1, x2 = x2, x1
- }
-
- if y2 < y1 {
- y1, y2 = y2, y1
- }
-
- hcell, _ = CoordinatesToCellName(x1, y1)
- vcell, _ = CoordinatesToCellName(x2, y2)
-
- xlsx, err := f.workSheetReader(sheet)
- if err != nil {
- return err
- }
- if xlsx.MergeCells != nil {
- ref := hcell + ":" + vcell
- // Delete the merged cells of the overlapping area.
- for _, cellData := range xlsx.MergeCells.Cells {
- cc := strings.Split(cellData.Ref, ":")
- if len(cc) != 2 {
- return fmt.Errorf("invalid area %q", cellData.Ref)
- }
- c1, _ := checkCellInArea(hcell, cellData.Ref)
- c2, _ := checkCellInArea(vcell, cellData.Ref)
- c3, _ := checkCellInArea(cc[0], ref)
- c4, _ := checkCellInArea(cc[1], ref)
- if !(!c1 && !c2 && !c3 && !c4) {
- return nil
- }
- }
- xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells, &xlsxMergeCell{Ref: ref})
- } else {
- xlsx.MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: hcell + ":" + vcell}}}
- }
- return err
-}
-
// SetSheetRow writes an array to row by given worksheet name, starting
// coordinate and a pointer to array type 'slice'. For example, writes an
// array to row 6 start with the cell B6 on Sheet1:
@@ -601,7 +544,7 @@ func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) (string, error
axis = strings.ToUpper(axis)
if xlsx.MergeCells != nil {
for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
- ok, err := checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref)
+ ok, err := f.checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref)
if err != nil {
return axis, err
}
@@ -615,7 +558,7 @@ func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) (string, error
// checkCellInArea provides a function to determine if a given coordinate is
// within an area.
-func checkCellInArea(cell, area string) (bool, error) {
+func (f *File) checkCellInArea(cell, area string) (bool, error) {
col, row, err := CellNameToCoordinates(cell)
if err != nil {
return false, err
@@ -625,11 +568,30 @@ func checkCellInArea(cell, area string) (bool, error) {
if len(rng) != 2 {
return false, err
}
+ coordinates, err := f.areaRefToCoordinates(area)
+ if err != nil {
+ return false, err
+ }
+
+ return cellInRef([]int{col, row}, coordinates), err
+}
- firstCol, firstRow, _ := CellNameToCoordinates(rng[0])
- lastCol, lastRow, _ := CellNameToCoordinates(rng[1])
+// cellInRef provides a function to determine if a given range is within an
+// range.
+func cellInRef(cell, ref []int) bool {
+ return cell[0] >= ref[0] && cell[0] <= ref[2] && cell[1] >= ref[1] && cell[1] <= ref[3]
+}
- return col >= firstCol && col <= lastCol && row >= firstRow && row <= lastRow, err
+// isOverlap find if the given two rectangles overlap or not.
+func isOverlap(rect1, rect2 []int) bool {
+ return cellInRef([]int{rect1[0], rect1[1]}, rect2) ||
+ cellInRef([]int{rect1[2], rect1[1]}, rect2) ||
+ cellInRef([]int{rect1[0], rect1[3]}, rect2) ||
+ cellInRef([]int{rect1[2], rect1[3]}, rect2) ||
+ cellInRef([]int{rect2[0], rect2[1]}, rect1) ||
+ cellInRef([]int{rect2[2], rect2[1]}, rect1) ||
+ cellInRef([]int{rect2[0], rect2[3]}, rect1) ||
+ cellInRef([]int{rect2[2], rect2[3]}, rect1)
}
// getSharedForumula find a cell contains the same formula as another cell,
diff --git a/cell_test.go b/cell_test.go
index 653aaab..b030622 100644
--- a/cell_test.go
+++ b/cell_test.go
@@ -4,11 +4,13 @@ import (
"fmt"
"path/filepath"
"testing"
+ "time"
"github.com/stretchr/testify/assert"
)
func TestCheckCellInArea(t *testing.T) {
+ f := NewFile()
expectedTrueCellInAreaList := [][2]string{
{"c2", "A1:AAZ32"},
{"B9", "A1:B9"},
@@ -18,7 +20,7 @@ func TestCheckCellInArea(t *testing.T) {
for _, expectedTrueCellInArea := range expectedTrueCellInAreaList {
cell := expectedTrueCellInArea[0]
area := expectedTrueCellInArea[1]
- ok, err := checkCellInArea(cell, area)
+ ok, err := f.checkCellInArea(cell, area)
assert.NoError(t, err)
assert.Truef(t, ok,
"Expected cell %v to be in area %v, got false\n", cell, area)
@@ -33,13 +35,17 @@ func TestCheckCellInArea(t *testing.T) {
for _, expectedFalseCellInArea := range expectedFalseCellInAreaList {
cell := expectedFalseCellInArea[0]
area := expectedFalseCellInArea[1]
- ok, err := checkCellInArea(cell, area)
+ ok, err := f.checkCellInArea(cell, area)
assert.NoError(t, err)
assert.Falsef(t, ok,
"Expected cell %v not to be inside of area %v, but got true\n", cell, area)
}
- ok, err := checkCellInArea("AA0", "Z0:AB1")
+ ok, err := f.checkCellInArea("A1", "A:B")
+ assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+ assert.False(t, ok)
+
+ ok, err = f.checkCellInArea("AA0", "Z0:AB1")
assert.EqualError(t, err, `cannot convert cell "AA0" to coordinates: invalid cell name "AA0"`)
assert.False(t, ok)
}
@@ -73,6 +79,24 @@ func TestSetCellFloat(t *testing.T) {
assert.NoError(t, err)
assert.Equal(t, "123.42", val, "A1 should be 123.42")
})
+ f := NewFile()
+ assert.EqualError(t, f.SetCellFloat(sheet, "A", 123.42, -1, 64), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+}
+
+func TestSetCellValue(t *testing.T) {
+ f := NewFile()
+ assert.EqualError(t, f.SetCellValue("Sheet1", "A", time.Now().UTC()), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+ assert.EqualError(t, f.SetCellValue("Sheet1", "A", time.Duration(1e13)), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+}
+
+func TestSetCellBool(t *testing.T) {
+ f := NewFile()
+ assert.EqualError(t, f.SetCellBool("Sheet1", "A", true), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+}
+
+func TestGetCellFormula(t *testing.T) {
+ f := NewFile()
+ f.GetCellFormula("Sheet", "A1")
}
func ExampleFile_SetCellFloat() {
diff --git a/cellmerged.go b/cellmerged.go
index c1df9b3..968a28a 100644
--- a/cellmerged.go
+++ b/cellmerged.go
@@ -9,7 +9,158 @@
package excelize
-import "strings"
+import (
+ "fmt"
+ "strings"
+)
+
+// MergeCell provides a function to merge cells by given coordinate area and
+// sheet name. For example create a merged cell of D3:E9 on Sheet1:
+//
+// err := f.MergeCell("Sheet1", "D3", "E9")
+//
+// If you create a merged cell that overlaps with another existing merged cell,
+// those merged cells that already exist will be removed.
+//
+// B1(x1,y1) D1(x2,y1)
+// +--------------------------------+
+// | |
+// | |
+// A4(x3,y3) | C4(x4,y3) |
+// +-----------------------------+ |
+// | | | |
+// | | | |
+// | |B5(x1,y2) | D5(x2,y2)|
+// | +--------------------------------+
+// | |
+// | |
+// |A8(x3,y4) C8(x4,y4)|
+// +-----------------------------+
+//
+func (f *File) MergeCell(sheet, hcell, vcell string) error {
+ rect1, err := f.areaRefToCoordinates(hcell + ":" + vcell)
+ if err != nil {
+ return err
+ }
+ // Correct the coordinate area, such correct C1:B3 to B1:C3.
+ if rect1[2] < rect1[0] {
+ rect1[0], rect1[2] = rect1[2], rect1[0]
+ }
+
+ if rect1[3] < rect1[1] {
+ rect1[1], rect1[3] = rect1[3], rect1[1]
+ }
+
+ hcell, _ = CoordinatesToCellName(rect1[0], rect1[1])
+ vcell, _ = CoordinatesToCellName(rect1[2], rect1[3])
+
+ xlsx, err := f.workSheetReader(sheet)
+ if err != nil {
+ return err
+ }
+ ref := hcell + ":" + vcell
+ if xlsx.MergeCells != nil {
+ for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
+ cellData := xlsx.MergeCells.Cells[i]
+ if cellData == nil {
+ continue
+ }
+ cc := strings.Split(cellData.Ref, ":")
+ if len(cc) != 2 {
+ return fmt.Errorf("invalid area %q", cellData.Ref)
+ }
+
+ rect2, err := f.areaRefToCoordinates(cellData.Ref)
+ if err != nil {
+ return err
+ }
+
+ // Delete the merged cells of the overlapping area.
+ if isOverlap(rect1, rect2) {
+ xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells[:i], xlsx.MergeCells.Cells[i+1:]...)
+ i--
+
+ if rect1[0] > rect2[0] {
+ rect1[0], rect2[0] = rect2[0], rect1[0]
+ }
+
+ if rect1[2] < rect2[2] {
+ rect1[2], rect2[2] = rect2[2], rect1[2]
+ }
+
+ if rect1[1] > rect2[1] {
+ rect1[1], rect2[1] = rect2[1], rect1[1]
+ }
+
+ if rect1[3] < rect2[3] {
+ rect1[3], rect2[3] = rect2[3], rect1[3]
+ }
+ hcell, _ = CoordinatesToCellName(rect1[0], rect1[1])
+ vcell, _ = CoordinatesToCellName(rect1[2], rect1[3])
+ ref = hcell + ":" + vcell
+ }
+ }
+ xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells, &xlsxMergeCell{Ref: ref})
+ } else {
+ xlsx.MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: ref}}}
+ }
+ return err
+}
+
+// UnmergeCell provides a function to unmerge a given coordinate area.
+// For example unmerge area D3:E9 on Sheet1:
+//
+// err := f.UnmergeCell("Sheet1", "D3", "E9")
+//
+// Attention: overlapped areas will also be unmerged.
+func (f *File) UnmergeCell(sheet string, hcell, vcell string) error {
+ xlsx, err := f.workSheetReader(sheet)
+ if err != nil {
+ return err
+ }
+ rect1, err := f.areaRefToCoordinates(hcell + ":" + vcell)
+ if err != nil {
+ return err
+ }
+
+ if rect1[2] < rect1[0] {
+ rect1[0], rect1[2] = rect1[2], rect1[0]
+ }
+ if rect1[3] < rect1[1] {
+ rect1[1], rect1[3] = rect1[3], rect1[1]
+ }
+ hcell, _ = CoordinatesToCellName(rect1[0], rect1[1])
+ vcell, _ = CoordinatesToCellName(rect1[2], rect1[3])
+
+ // return nil since no MergeCells in the sheet
+ if xlsx.MergeCells == nil {
+ return nil
+ }
+
+ i := 0
+ for _, cellData := range xlsx.MergeCells.Cells {
+ if cellData == nil {
+ continue
+ }
+ cc := strings.Split(cellData.Ref, ":")
+ if len(cc) != 2 {
+ return fmt.Errorf("invalid area %q", cellData.Ref)
+ }
+
+ rect2, err := f.areaRefToCoordinates(cellData.Ref)
+ if err != nil {
+ return err
+ }
+
+ if isOverlap(rect1, rect2) {
+ continue
+ }
+ xlsx.MergeCells.Cells[i] = cellData
+ i++
+ }
+ xlsx.MergeCells.Cells = xlsx.MergeCells.Cells[:i]
+ return nil
+}
// GetMergeCells provides a function to get all merged cells from a worksheet
// currently.
diff --git a/cellmerged_test.go b/cellmerged_test.go
index d53acc2..1da0eb3 100644
--- a/cellmerged_test.go
+++ b/cellmerged_test.go
@@ -7,6 +7,74 @@ import (
"github.com/stretchr/testify/assert"
)
+func TestMergeCell(t *testing.T) {
+ f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+ assert.EqualError(t, f.MergeCell("Sheet1", "A", "B"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+ assert.NoError(t, f.MergeCell("Sheet1", "D9", "D9"))
+ assert.NoError(t, f.MergeCell("Sheet1", "D9", "E9"))
+ assert.NoError(t, f.MergeCell("Sheet1", "H14", "G13"))
+ assert.NoError(t, f.MergeCell("Sheet1", "C9", "D8"))
+ assert.NoError(t, f.MergeCell("Sheet1", "F11", "G13"))
+ assert.NoError(t, f.MergeCell("Sheet1", "H7", "B15"))
+ assert.NoError(t, f.MergeCell("Sheet1", "D11", "F13"))
+ assert.NoError(t, f.MergeCell("Sheet1", "G10", "K12"))
+ f.SetCellValue("Sheet1", "G11", "set value in merged cell")
+ f.SetCellInt("Sheet1", "H11", 100)
+ f.SetCellValue("Sheet1", "I11", float64(0.5))
+ f.SetCellHyperLink("Sheet1", "J11", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
+ f.SetCellFormula("Sheet1", "G12", "SUM(Sheet1!B19,Sheet1!C19)")
+ f.GetCellValue("Sheet1", "H11")
+ f.GetCellValue("Sheet2", "A6") // Merged cell ref is single coordinate.
+ f.GetCellFormula("Sheet1", "G12")
+
+ f.NewSheet("Sheet3")
+ assert.NoError(t, f.MergeCell("Sheet3", "D11", "F13"))
+ assert.NoError(t, f.MergeCell("Sheet3", "G10", "K12"))
+
+ assert.NoError(t, f.MergeCell("Sheet3", "B1", "D5")) // B1:D5
+ assert.NoError(t, f.MergeCell("Sheet3", "E1", "F5")) // E1:F5
+
+ assert.NoError(t, f.MergeCell("Sheet3", "H2", "I5"))
+ assert.NoError(t, f.MergeCell("Sheet3", "I4", "J6")) // H2:J6
+
+ assert.NoError(t, f.MergeCell("Sheet3", "M2", "N5"))
+ assert.NoError(t, f.MergeCell("Sheet3", "L4", "M6")) // L2:N6
+
+ assert.NoError(t, f.MergeCell("Sheet3", "P4", "Q7"))
+ assert.NoError(t, f.MergeCell("Sheet3", "O2", "P5")) // O2:Q7
+
+ assert.NoError(t, f.MergeCell("Sheet3", "A9", "B12"))
+ assert.NoError(t, f.MergeCell("Sheet3", "B7", "C9")) // A7:C12
+
+ assert.NoError(t, f.MergeCell("Sheet3", "E9", "F10"))
+ assert.NoError(t, f.MergeCell("Sheet3", "D8", "G12"))
+
+ assert.NoError(t, f.MergeCell("Sheet3", "I8", "I12"))
+ assert.NoError(t, f.MergeCell("Sheet3", "I10", "K10"))
+
+ assert.NoError(t, f.MergeCell("Sheet3", "M8", "Q13"))
+ assert.NoError(t, f.MergeCell("Sheet3", "N10", "O11"))
+
+ // Test get merged cells on not exists worksheet.
+ assert.EqualError(t, f.MergeCell("SheetN", "N10", "O11"), "sheet SheetN is not exist")
+
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestMergeCell.xlsx")))
+
+ f = NewFile()
+ assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{nil, nil}}
+ assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
+
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}
+ assert.EqualError(t, f.MergeCell("Sheet1", "A2", "B3"), `invalid area "A1"`)
+
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A:A"}}}
+ assert.EqualError(t, f.MergeCell("Sheet1", "A2", "B3"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+}
+
func TestGetMergeCells(t *testing.T) {
wants := []struct {
value string
@@ -52,3 +120,44 @@ func TestGetMergeCells(t *testing.T) {
_, err = f.GetMergeCells("SheetN")
assert.EqualError(t, err, "sheet SheetN is not exist")
}
+
+func TestUnmergeCell(t *testing.T) {
+ f, err := OpenFile(filepath.Join("test", "MergeCell.xlsx"))
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+ sheet1 := f.GetSheetName(1)
+
+ xlsx, err := f.workSheetReader(sheet1)
+ assert.NoError(t, err)
+
+ mergeCellNum := len(xlsx.MergeCells.Cells)
+
+ assert.EqualError(t, f.UnmergeCell("Sheet1", "A", "A"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+
+ // unmerge the mergecell that contains A1
+ assert.NoError(t, f.UnmergeCell(sheet1, "A1", "A1"))
+ if len(xlsx.MergeCells.Cells) != mergeCellNum-1 {
+ t.FailNow()
+ }
+
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestUnmergeCell.xlsx")))
+
+ f = NewFile()
+ assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
+ // Test unmerged area on not exists worksheet.
+ assert.EqualError(t, f.UnmergeCell("SheetN", "A1", "A1"), "sheet SheetN is not exist")
+
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = nil
+ assert.NoError(t, f.UnmergeCell("Sheet1", "H7", "B15"))
+
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{nil, nil}}
+ assert.NoError(t, f.UnmergeCell("Sheet1", "H15", "B7"))
+
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}
+ assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), `invalid area "A1"`)
+
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A:A"}}}
+ assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+
+}
diff --git a/chart.go b/chart.go
index aaa7cd6..bf8155a 100644
--- a/chart.go
+++ b/chart.go
@@ -587,6 +587,7 @@ func parseFormatChartSet(formatSet string) (*formatChart, error) {
// name
// categories
// values
+// line
//
// name: Set the name for the series. The name is displayed in the chart legend and in the formula bar. The name property is optional and if it isn't supplied it will default to Series 1..n. The name can also be a formula such as Sheet1!$A$1
//
@@ -594,6 +595,8 @@ func parseFormatChartSet(formatSet string) (*formatChart, error) {
//
// values: This is the most important property of a series and is the only mandatory option for every chart object. This option links the chart with the worksheet data that it displays.
//
+// line: This sets the line format of the line chart. The line property is optional and if it isn't supplied it will default style. The options that can be set is width. The range of width is 0.25pt - 999pt. If the value of width is outside the range, the default width of the line is 2pt.
+//
// Set properties of the chart legend. The options that can be set are:
//
// position
@@ -652,10 +655,16 @@ func parseFormatChartSet(formatSet string) (*formatChart, error) {
//
// Set the primary horizontal and vertical axis options by x_axis and y_axis. The properties that can be set are:
//
+// major_grid_lines
+// minor_grid_lines
// reverse_order
// maximum
// minimum
//
+// major_grid_lines: Specifies major gridlines.
+//
+// minor_grid_lines: Specifies minor gridlines.
+//
// reverse_order: Specifies that the categories or values on reverse order (orientation of the chart). The reverse_order property is optional. The default value is false.
//
// maximum: Specifies that the fixed maximum, 0 is auto. The maximum property is optional. The default value is auto.
@@ -1390,7 +1399,7 @@ func (f *File) drawChartSeriesSpPr(i int, formatSet *formatChart) *cSpPr {
}
spPrLine := &cSpPr{
Ln: &aLn{
- W: 25400,
+ W: f.ptToEMUs(formatSet.Series[i].Line.Width),
Cap: "rnd", // rnd, sq, flat
},
}
@@ -1441,7 +1450,7 @@ func (f *File) drawChartSeriesCat(v formatChartSeries, formatSet *formatChart) *
},
}
chartSeriesCat := map[string]*cCat{Scatter: nil, Bubble: nil, Bubble3D: nil}
- if _, ok := chartSeriesCat[formatSet.Type]; ok {
+ if _, ok := chartSeriesCat[formatSet.Type]; ok || v.Categories == "" {
return nil
}
return cat
@@ -1601,6 +1610,9 @@ func (f *File) drawPlotAreaCatAx(formatSet *formatChart) []*cAxs {
if formatSet.XAxis.MajorGridlines {
axs[0].MajorGridlines = &cChartLines{SpPr: f.drawPlotAreaSpPr()}
}
+ if formatSet.XAxis.MinorGridlines {
+ axs[0].MinorGridlines = &cChartLines{SpPr: f.drawPlotAreaSpPr()}
+ }
return axs
}
@@ -1641,6 +1653,9 @@ func (f *File) drawPlotAreaValAx(formatSet *formatChart) []*cAxs {
if formatSet.YAxis.MajorGridlines {
axs[0].MajorGridlines = &cChartLines{SpPr: f.drawPlotAreaSpPr()}
}
+ if formatSet.YAxis.MinorGridlines {
+ axs[0].MinorGridlines = &cChartLines{SpPr: f.drawPlotAreaSpPr()}
+ }
if pos, ok := valTickLblPos[formatSet.Type]; ok {
axs[0].TickLblPos.Val = pos
}
@@ -1831,3 +1846,13 @@ func (f *File) addDrawingChart(sheet, drawingXML, cell string, width, height, rI
f.Drawings[drawingXML] = content
return err
}
+
+// ptToEMUs provides a function to convert pt to EMUs, 1 pt = 12700 EMUs. The
+// range of pt is 0.25pt - 999pt. If the value of pt is outside the range, the
+// default EMUs will be returned.
+func (f *File) ptToEMUs(pt float64) int {
+ if 0.25 > pt || pt > 999 {
+ return 25400
+ }
+ return int(12700 * pt)
+}
diff --git a/chart_test.go b/chart_test.go
index 932e873..bc5c30a 100644
--- a/chart_test.go
+++ b/chart_test.go
@@ -144,7 +144,7 @@ func TestAddChart(t *testing.T) {
assert.NoError(t, f.AddChart("Sheet2", "P1", `{"type":"radar","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"},{"name":"Sheet1!$A$33","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$33:$D$33"},{"name":"Sheet1!$A$34","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$34:$D$34"},{"name":"Sheet1!$A$35","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$35:$D$35"},{"name":"Sheet1!$A$36","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$36:$D$36"},{"name":"Sheet1!$A$37","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$37:$D$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"top_right","show_legend_key":false},"title":{"name":"Radar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"span"}`))
assert.NoError(t, f.AddChart("Sheet2", "X1", `{"type":"scatter","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"},{"name":"Sheet1!$A$33","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$33:$D$33"},{"name":"Sheet1!$A$34","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$34:$D$34"},{"name":"Sheet1!$A$35","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$35:$D$35"},{"name":"Sheet1!$A$36","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$36:$D$36"},{"name":"Sheet1!$A$37","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$37:$D$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Scatter Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`))
assert.NoError(t, f.AddChart("Sheet2", "P16", `{"type":"doughnut","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$A$30:$D$37","values":"Sheet1!$B$30:$B$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"right","show_legend_key":false},"title":{"name":"Doughnut Chart"},"plotarea":{"show_bubble_size":false,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"zero"}`))
- assert.NoError(t, f.AddChart("Sheet2", "X16", `{"type":"line","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"},{"name":"Sheet1!$A$33","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$33:$D$33"},{"name":"Sheet1!$A$34","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$34:$D$34"},{"name":"Sheet1!$A$35","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$35:$D$35"},{"name":"Sheet1!$A$36","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$36:$D$36"},{"name":"Sheet1!$A$37","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$37:$D$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"top","show_legend_key":false},"title":{"name":"Line Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`))
+ assert.NoError(t, f.AddChart("Sheet2", "X16", `{"type":"line","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"},{"name":"Sheet1!$A$33","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$33:$D$33"},{"name":"Sheet1!$A$34","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$34:$D$34"},{"name":"Sheet1!$A$35","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$35:$D$35"},{"name":"Sheet1!$A$36","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$36:$D$36"},{"name":"Sheet1!$A$37","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$37:$D$37","line":{"width":0.25}}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"top","show_legend_key":false},"title":{"name":"Line Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","x_axis":{"major_grid_lines":true,"minor_grid_lines":true},"y_axis":{"major_grid_lines":true,"minor_grid_lines":true}}`))
assert.NoError(t, f.AddChart("Sheet2", "P32", `{"type":"pie3D","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$A$30:$D$37","values":"Sheet1!$B$30:$B$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"3D Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"zero"}`))
assert.NoError(t, f.AddChart("Sheet2", "X32", `{"type":"pie","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$A$30:$D$37","values":"Sheet1!$B$30:$B$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"gap"}`))
assert.NoError(t, f.AddChart("Sheet2", "P48", `{"type":"bar","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"},{"name":"Sheet1!$A$33","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$33:$D$33"},{"name":"Sheet1!$A$34","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$34:$D$34"},{"name":"Sheet1!$A$35","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$35:$D$35"},{"name":"Sheet1!$A$36","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$36:$D$36"},{"name":"Sheet1!$A$37","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$37:$D$37"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"2D Clustered Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`))
diff --git a/col_test.go b/col_test.go
index a696caa..edbdae7 100644
--- a/col_test.go
+++ b/col_test.go
@@ -53,10 +53,15 @@ func TestOutlineLevel(t *testing.T) {
assert.NoError(t, f.SetRowOutlineLevel("Sheet1", 2, 7))
assert.EqualError(t, f.SetColOutlineLevel("Sheet1", "D", 8), "invalid outline level")
assert.EqualError(t, f.SetRowOutlineLevel("Sheet1", 2, 8), "invalid outline level")
+ // Test set row outline level on not exists worksheet.
+ assert.EqualError(t, f.SetRowOutlineLevel("SheetN", 1, 4), "sheet SheetN is not exist")
+ // Test get row outline level on not exists worksheet.
+ _, err := f.GetRowOutlineLevel("SheetN", 1)
+ assert.EqualError(t, err, "sheet SheetN is not exist")
// Test set and get column outline level with illegal cell coordinates.
assert.EqualError(t, f.SetColOutlineLevel("Sheet1", "*", 1), `invalid column name "*"`)
- _, err := f.GetColOutlineLevel("Sheet1", "*")
+ _, err = f.GetColOutlineLevel("Sheet1", "*")
assert.EqualError(t, err, `invalid column name "*"`)
// Test set column outline level on not exists worksheet.
diff --git a/excelize.go b/excelize.go
index fe227b9..8e386a8 100644
--- a/excelize.go
+++ b/excelize.go
@@ -188,20 +188,12 @@ func checkSheet(xlsx *xlsxWorksheet) {
row = lastRow
}
}
- sheetData := xlsxSheetData{}
- existsRows := map[int]int{}
- for k := range xlsx.SheetData.Row {
- existsRows[xlsx.SheetData.Row[k].R] = k
+ sheetData := xlsxSheetData{Row: make([]xlsxRow, row)}
+ for _, r := range xlsx.SheetData.Row {
+ sheetData.Row[r.R-1] = r
}
- for i := 0; i < row; i++ {
- _, ok := existsRows[i+1]
- if ok {
- sheetData.Row = append(sheetData.Row, xlsx.SheetData.Row[existsRows[i+1]])
- } else {
- sheetData.Row = append(sheetData.Row, xlsxRow{
- R: i + 1,
- })
- }
+ for i := 1; i <= row; i++ {
+ sheetData.Row[i-1].R = i
}
xlsx.SheetData = sheetData
}
@@ -233,7 +225,7 @@ func (f *File) addRels(relPath, relType, target, targetMode string) int {
// Office Excel 2007.
func replaceWorkSheetsRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte {
var oldXmlns = []byte(`<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`)
- var newXmlns = []byte(`<worksheet xr:uid="{00000000-0001-0000-0000-000000000000}" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6" xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" mc:Ignorable="x14ac xr xr2 xr3 xr6 xr10 x15" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`)
+ var newXmlns = []byte(`<worksheet` + templateNamespaceIDMap)
workbookMarshal = bytes.Replace(workbookMarshal, oldXmlns, newXmlns, -1)
return workbookMarshal
}
@@ -243,7 +235,7 @@ func replaceWorkSheetsRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte
// Excel 2007.
func replaceStyleRelationshipsNameSpaceBytes(contentMarshal []byte) []byte {
var oldXmlns = []byte(`<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`)
- var newXmlns = []byte(`<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac x16r2 xr xr9" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr9="http://schemas.microsoft.com/office/spreadsheetml/2016/revision9">`)
+ var newXmlns = []byte(`<styleSheet` + templateNamespaceIDMap)
contentMarshal = bytes.Replace(contentMarshal, oldXmlns, newXmlns, -1)
return contentMarshal
}
diff --git a/excelize_test.go b/excelize_test.go
index 7b6b674..95d63fd 100644
--- a/excelize_test.go
+++ b/excelize_test.go
@@ -290,6 +290,12 @@ func TestSetCellHyperLink(t *testing.T) {
assert.NoError(t, file.SetCellHyperLink("Sheet1", cell, "https://github.com/360EntSecGroup-Skylar/excelize", "External"))
}
assert.EqualError(t, file.SetCellHyperLink("Sheet1", "A65531", "https://github.com/360EntSecGroup-Skylar/excelize", "External"), "over maximum limit hyperlinks in a worksheet")
+
+ f = NewFile()
+ f.workSheetReader("Sheet1")
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A:A"}}}
+ err = f.SetCellHyperLink("Sheet1", "A1", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
+ assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`)
}
func TestGetCellHyperLink(t *testing.T) {
@@ -310,6 +316,23 @@ func TestGetCellHyperLink(t *testing.T) {
link, target, err = f.GetCellHyperLink("Sheet3", "H3")
assert.EqualError(t, err, "sheet Sheet3 is not exist")
t.Log(link, target)
+
+ f = NewFile()
+ f.workSheetReader("Sheet1")
+ f.Sheet["xl/worksheets/sheet1.xml"].Hyperlinks = &xlsxHyperlinks{
+ Hyperlink: []xlsxHyperlink{{Ref: "A1"}},
+ }
+ link, target, err = f.GetCellHyperLink("Sheet1", "A1")
+ assert.NoError(t, err)
+ assert.Equal(t, link, true)
+ assert.Equal(t, target, "")
+
+ f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A:A"}}}
+ link, target, err = f.GetCellHyperLink("Sheet1", "A1")
+ assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+ assert.Equal(t, link, false)
+ assert.Equal(t, target, "")
+
}
func TestSetCellFormula(t *testing.T) {
@@ -372,32 +395,6 @@ func TestSetSheetBackgroundErrors(t *testing.T) {
assert.EqualError(t, err, "unsupported image extension")
}
-func TestMergeCell(t *testing.T) {
- f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
- if !assert.NoError(t, err) {
- t.FailNow()
- }
-
- f.MergeCell("Sheet1", "D9", "D9")
- f.MergeCell("Sheet1", "D9", "E9")
- f.MergeCell("Sheet1", "H14", "G13")
- f.MergeCell("Sheet1", "C9", "D8")
- f.MergeCell("Sheet1", "F11", "G13")
- f.MergeCell("Sheet1", "H7", "B15")
- f.MergeCell("Sheet1", "D11", "F13")
- f.MergeCell("Sheet1", "G10", "K12")
- f.SetCellValue("Sheet1", "G11", "set value in merged cell")
- f.SetCellInt("Sheet1", "H11", 100)
- f.SetCellValue("Sheet1", "I11", float64(0.5))
- f.SetCellHyperLink("Sheet1", "J11", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
- f.SetCellFormula("Sheet1", "G12", "SUM(Sheet1!B19,Sheet1!C19)")
- f.GetCellValue("Sheet1", "H11")
- f.GetCellValue("Sheet2", "A6") // Merged cell ref is single coordinate.
- f.GetCellFormula("Sheet1", "G12")
-
- assert.NoError(t, f.SaveAs(filepath.Join("test", "TestMergeCell.xlsx")))
-}
-
// TestWriteArrayFormula tests the extended options of SetCellFormula by writing an array function
// to a workbook. In the resulting file, the lines 2 and 3 as well as 4 and 5 should have matching
// contents.
@@ -913,13 +910,18 @@ func TestAddShape(t *testing.T) {
t.FailNow()
}
- f.AddShape("Sheet1", "A30", `{"type":"rect","paragraph":[{"text":"Rectangle","font":{"color":"CD5C5C"}},{"text":"Shape","font":{"bold":true,"color":"2980B9"}}]}`)
- f.AddShape("Sheet1", "B30", `{"type":"rect","paragraph":[{"text":"Rectangle"},{}]}`)
- f.AddShape("Sheet1", "C30", `{"type":"rect","paragraph":[]}`)
- f.AddShape("Sheet3", "H1", `{"type":"ellipseRibbon", "color":{"line":"#4286f4","fill":"#8eb9ff"}, "paragraph":[{"font":{"bold":true,"italic":true,"family":"Times New Roman","size":36,"color":"#777777","underline":"single"}}], "height": 90}`)
- f.AddShape("Sheet3", "H1", "")
+ assert.NoError(t, f.AddShape("Sheet1", "A30", `{"type":"rect","paragraph":[{"text":"Rectangle","font":{"color":"CD5C5C"}},{"text":"Shape","font":{"bold":true,"color":"2980B9"}}]}`))
+ assert.NoError(t, f.AddShape("Sheet1", "B30", `{"type":"rect","paragraph":[{"text":"Rectangle"},{}]}`))
+ assert.NoError(t, f.AddShape("Sheet1", "C30", `{"type":"rect","paragraph":[]}`))
+ assert.EqualError(t, f.AddShape("Sheet3", "H1", `{"type":"ellipseRibbon", "color":{"line":"#4286f4","fill":"#8eb9ff"}, "paragraph":[{"font":{"bold":true,"italic":true,"family":"Times New Roman","size":36,"color":"#777777","underline":"single"}}], "height": 90}`), "sheet Sheet3 is not exist")
+ assert.EqualError(t, f.AddShape("Sheet3", "H1", ""), "unexpected end of JSON input")
+ assert.EqualError(t, f.AddShape("Sheet1", "A", `{"type":"rect","paragraph":[{"text":"Rectangle","font":{"color":"CD5C5C"}},{"text":"Shape","font":{"bold":true,"color":"2980B9"}}]}`), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddShape1.xlsx")))
- assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddShape.xlsx")))
+ // Test add first shape for given sheet.
+ f = NewFile()
+ assert.NoError(t, f.AddShape("Sheet1", "A1", `{"type":"ellipseRibbon", "color":{"line":"#4286f4","fill":"#8eb9ff"}, "paragraph":[{"font":{"bold":true,"italic":true,"family":"Times New Roman","size":36,"color":"#777777","underline":"single"}}], "height": 90}`))
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddShape2.xlsx")))
}
func TestAddComments(t *testing.T) {
@@ -1278,3 +1280,9 @@ func fillCells(f *File, sheet string, colCount, rowCount int) {
}
}
}
+
+func BenchmarkOpenFile(b *testing.B) {
+ for i := 0; i < b.N; i++ {
+ OpenFile(filepath.Join("test", "Book1.xlsx"))
+ }
+}
diff --git a/file_test.go b/file_test.go
new file mode 100644
index 0000000..6c30f4a
--- /dev/null
+++ b/file_test.go
@@ -0,0 +1,27 @@
+package excelize
+
+import (
+ "testing"
+)
+
+func BenchmarkWrite(b *testing.B) {
+ const s = "This is test data"
+ for i := 0; i < b.N; i++ {
+ f := NewFile()
+ for row := 1; row <= 10000; row++ {
+ for col := 1; col <= 20; col++ {
+ val, err := CoordinatesToCellName(col, row)
+ if err != nil {
+ panic(err)
+ }
+ f.SetCellDefault("Sheet1", val, s)
+ }
+ }
+ // Save xlsx file by the given path.
+ err := f.SaveAs("./test.xlsx")
+ if err != nil {
+ panic(err)
+ }
+ }
+
+}
diff --git a/lib.go b/lib.go
index 4dea16a..edac98a 100644
--- a/lib.go
+++ b/lib.go
@@ -22,14 +22,12 @@ import (
// ReadZipReader can be used to read an XLSX in memory without touching the
// filesystem.
func ReadZipReader(r *zip.Reader) (map[string][]byte, int, error) {
- fileList := make(map[string][]byte)
+ fileList := make(map[string][]byte, len(r.File))
worksheets := 0
for _, v := range r.File {
fileList[v.Name] = readFile(v)
- if len(v.Name) > 18 {
- if v.Name[0:19] == "xl/worksheets/sheet" {
- worksheets++
- }
+ if strings.HasPrefix(v.Name, "xl/worksheets/sheet") {
+ worksheets++
}
}
return fileList, worksheets, nil
@@ -58,7 +56,8 @@ func readFile(file *zip.File) []byte {
if err != nil {
log.Fatal(err)
}
- buff := bytes.NewBuffer(nil)
+ dat := make([]byte, 0, file.FileInfo().Size())
+ buff := bytes.NewBuffer(dat)
_, _ = io.Copy(buff, rc)
rc.Close()
return buff.Bytes()
diff --git a/picture.go b/picture.go
index 09c1955..2420350 100644
--- a/picture.go
+++ b/picture.go
@@ -479,7 +479,7 @@ func (f *File) getPicture(row, col int, drawingXML, drawingRelationships string)
ok bool
anchor *xdrCellAnchor
deWsDr *decodeWsDr
- xxRelationship *xlsxRelationship
+ drawRel *xlsxRelationship
deTwoCellAnchor *decodeTwoCellAnchor
)
@@ -487,10 +487,10 @@ func (f *File) getPicture(row, col int, drawingXML, drawingRelationships string)
for _, anchor = range wsDr.TwoCellAnchor {
if anchor.From != nil && anchor.Pic != nil {
if anchor.From.Col == col && anchor.From.Row == row {
- xxRelationship = f.getDrawingRelationships(drawingRelationships,
+ drawRel = f.getDrawingRelationships(drawingRelationships,
anchor.Pic.BlipFill.Blip.Embed)
- if _, ok = supportImageTypes[filepath.Ext(xxRelationship.Target)]; ok {
- ret, buf = filepath.Base(xxRelationship.Target), []byte(f.XLSX[strings.Replace(xxRelationship.Target, "..", "xl", -1)])
+ if _, ok = supportImageTypes[filepath.Ext(drawRel.Target)]; ok {
+ ret, buf = filepath.Base(drawRel.Target), []byte(f.XLSX[strings.Replace(drawRel.Target, "..", "xl", -1)])
return
}
}
@@ -512,9 +512,9 @@ func (f *File) getPicture(row, col int, drawingXML, drawingRelationships string)
}
if err = nil; deTwoCellAnchor.From != nil && deTwoCellAnchor.Pic != nil {
if deTwoCellAnchor.From.Col == col && deTwoCellAnchor.From.Row == row {
- xxRelationship = f.getDrawingRelationships(drawingRelationships, deTwoCellAnchor.Pic.BlipFill.Blip.Embed)
- if _, ok = supportImageTypes[filepath.Ext(xxRelationship.Target)]; ok {
- ret, buf = filepath.Base(xxRelationship.Target), []byte(f.XLSX[strings.Replace(xxRelationship.Target, "..", "xl", -1)])
+ drawRel = f.getDrawingRelationships(drawingRelationships, deTwoCellAnchor.Pic.BlipFill.Blip.Embed)
+ if _, ok = supportImageTypes[filepath.Ext(drawRel.Target)]; ok {
+ ret, buf = filepath.Base(drawRel.Target), []byte(f.XLSX[strings.Replace(drawRel.Target, "..", "xl", -1)])
return
}
}
diff --git a/pivotTable.go b/pivotTable.go
index 881d774..6045e41 100644
--- a/pivotTable.go
+++ b/pivotTable.go
@@ -253,7 +253,10 @@ func (f *File) addPivotTable(cacheID, pivotTableID int, pivotTableXML string, op
},
},
},
- ColFields: &xlsxColFields{},
+ ColItems: &xlsxColItems{
+ Count: 1,
+ I: []*xlsxI{{}},
+ },
DataFields: &xlsxDataFields{},
PivotTableStyleInfo: &xlsxPivotTableStyleInfo{
Name: "PivotStyleLight16",
@@ -286,19 +289,10 @@ func (f *File) addPivotTable(cacheID, pivotTableID int, pivotTableXML string, op
// count row fields
pt.RowFields.Count = len(pt.RowFields.Field)
- // col fields
- colFieldsIndex, err := f.getPivotFieldsIndex(opt.Columns, opt)
+ err = f.addPivotColFields(&pt, opt)
if err != nil {
return err
}
- for _, filedIdx := range colFieldsIndex {
- pt.ColFields.Field = append(pt.ColFields.Field, &xlsxField{
- X: filedIdx,
- })
- }
-
- // count col fields
- pt.ColFields.Count = len(pt.ColFields.Field)
// data fields
dataFieldsIndex, err := f.getPivotFieldsIndex(opt.Data, opt)
@@ -330,6 +324,31 @@ func inStrSlice(a []string, x string) int {
return -1
}
+// addPivotColFields create pivot column fields by given pivot table
+// definition and option.
+func (f *File) addPivotColFields(pt *xlsxPivotTableDefinition, opt *PivotTableOption) error {
+ if len(opt.Columns) == 0 {
+ return nil
+ }
+
+ pt.ColFields = &xlsxColFields{}
+
+ // col fields
+ colFieldsIndex, err := f.getPivotFieldsIndex(opt.Columns, opt)
+ if err != nil {
+ return err
+ }
+ for _, filedIdx := range colFieldsIndex {
+ pt.ColFields.Field = append(pt.ColFields.Field, &xlsxField{
+ X: filedIdx,
+ })
+ }
+
+ // count col fields
+ pt.ColFields.Count = len(pt.ColFields.Field)
+ return err
+}
+
// addPivotFields create pivot fields based on the column order of the first
// row in the data region by given pivot table definition and option.
func (f *File) addPivotFields(pt *xlsxPivotTableDefinition, opt *PivotTableOption) error {
diff --git a/pivotTable_test.go b/pivotTable_test.go
index 27e5914..9bf08e8 100644
--- a/pivotTable_test.go
+++ b/pivotTable_test.go
@@ -54,6 +54,12 @@ func TestAddPivotTable(t *testing.T) {
Columns: []string{"Region", "Year"},
Data: []string{"Sales"},
}))
+ assert.NoError(t, f.AddPivotTable(&PivotTableOption{
+ DataRange: "Sheet1!$A$1:$E$31",
+ PivotTableRange: "Sheet1!$AE$2:$AG$33",
+ Rows: []string{"Month", "Year"},
+ Data: []string{"Sales"},
+ }))
f.NewSheet("Sheet2")
assert.NoError(t, f.AddPivotTable(&PivotTableOption{
DataRange: "Sheet1!$A$1:$E$31",
diff --git a/rows.go b/rows.go
index e12e349..fc7b55a 100644
--- a/rows.go
+++ b/rows.go
@@ -11,6 +11,7 @@ package excelize
import (
"bytes"
+ "encoding/xml"
"errors"
"fmt"
"io"
@@ -51,15 +52,19 @@ func (f *File) GetRows(sheet string) ([][]string, error) {
// Rows defines an iterator to a sheet
type Rows struct {
- err error
- f *File
- rows []xlsxRow
- curRow int
+ err error
+ f *File
+ rows []xlsxRow
+ sheet string
+ curRow int
+ totalRow int
+ decoder *xml.Decoder
}
// Next will return true if find the next row element.
func (rows *Rows) Next() bool {
- return rows.curRow < len(rows.rows)
+ rows.curRow++
+ return rows.curRow <= rows.totalRow
}
// Error will return the error when the find next row element
@@ -69,20 +74,57 @@ func (rows *Rows) Error() error {
// Columns return the current row's column values
func (rows *Rows) Columns() ([]string, error) {
- curRow := rows.rows[rows.curRow]
- rows.curRow++
-
- columns := make([]string, len(curRow.C))
+ var (
+ err error
+ inElement string
+ row, cellCol int
+ columns []string
+ )
d := rows.f.sharedStringsReader()
- for _, colCell := range curRow.C {
- col, _, err := CellNameToCoordinates(colCell.R)
- if err != nil {
- return columns, err
+ for {
+ token, _ := rows.decoder.Token()
+ if token == nil {
+ break
+ }
+ switch startElement := token.(type) {
+ case xml.StartElement:
+ inElement = startElement.Name.Local
+ if inElement == "row" {
+ for _, attr := range startElement.Attr {
+ if attr.Name.Local == "r" {
+ row, err = strconv.Atoi(attr.Value)
+ if err != nil {
+ return columns, err
+ }
+ if row > rows.curRow {
+ return columns, err
+ }
+ }
+ }
+ }
+ if inElement == "c" {
+ colCell := xlsxC{}
+ _ = rows.decoder.DecodeElement(&colCell, &startElement)
+ cellCol, _, err = CellNameToCoordinates(colCell.R)
+ if err != nil {
+ return columns, err
+ }
+ blank := cellCol - len(columns)
+ for i := 1; i < blank; i++ {
+ columns = append(columns, "")
+ }
+ val, _ := colCell.getValueFrom(rows.f, d)
+ columns = append(columns, val)
+ }
+ case xml.EndElement:
+ inElement = startElement.Name.Local
+ if inElement == "row" {
+ return columns, err
+ }
+ default:
}
- val, _ := colCell.getValueFrom(rows.f, d)
- columns[col-1] = val
}
- return columns, nil
+ return columns, err
}
// ErrSheetNotExist defines an error of sheet is not exist
@@ -91,7 +133,7 @@ type ErrSheetNotExist struct {
}
func (err ErrSheetNotExist) Error() string {
- return fmt.Sprintf("Sheet %s is not exist", string(err.SheetName))
+ return fmt.Sprintf("sheet %s is not exist", string(err.SheetName))
}
// Rows return a rows iterator. For example:
@@ -106,22 +148,48 @@ func (err ErrSheetNotExist) Error() string {
// }
//
func (f *File) Rows(sheet string) (*Rows, error) {
- xlsx, err := f.workSheetReader(sheet)
- if err != nil {
- return nil, err
- }
name, ok := f.sheetMap[trimSheetName(sheet)]
if !ok {
return nil, ErrSheetNotExist{sheet}
}
- if xlsx != nil {
- data := f.readXML(name)
- f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpaceBytes(namespaceStrictToTransitional(data)))
+ if f.Sheet[name] != nil {
+ // flush data
+ output, _ := xml.Marshal(f.Sheet[name])
+ f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpaceBytes(output))
+ }
+ var (
+ err error
+ inElement string
+ row int
+ rows Rows
+ )
+ decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
+ for {
+ token, _ := decoder.Token()
+ if token == nil {
+ break
+ }
+ switch startElement := token.(type) {
+ case xml.StartElement:
+ inElement = startElement.Name.Local
+ if inElement == "row" {
+ for _, attr := range startElement.Attr {
+ if attr.Name.Local == "r" {
+ row, err = strconv.Atoi(attr.Value)
+ if err != nil {
+ return &rows, err
+ }
+ }
+ }
+ rows.totalRow = row
+ }
+ default:
+ }
}
- return &Rows{
- f: f,
- rows: xlsx.SheetData.Row,
- }, nil
+ rows.f = f
+ rows.sheet = name
+ rows.decoder = f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
+ return &rows, nil
}
// SetRowHeight provides a function to set the height of a single row. For
@@ -219,7 +287,10 @@ func (xlsx *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error) {
case "str":
return f.formattedValue(xlsx.S, xlsx.V), nil
case "inlineStr":
- return f.formattedValue(xlsx.S, xlsx.IS.String()), nil
+ if xlsx.IS != nil {
+ return f.formattedValue(xlsx.S, xlsx.IS.String()), nil
+ }
+ return f.formattedValue(xlsx.S, xlsx.V), nil
default:
return f.formattedValue(xlsx.S, xlsx.V), nil
}
diff --git a/rows_test.go b/rows_test.go
index d52c635..6b50c75 100644
--- a/rows_test.go
+++ b/rows_test.go
@@ -6,6 +6,7 @@ import (
"testing"
"github.com/stretchr/testify/assert"
+ "github.com/stretchr/testify/require"
)
func TestRows(t *testing.T) {
@@ -21,7 +22,7 @@ func TestRows(t *testing.T) {
t.FailNow()
}
- collectedRows := make([][]string, 0)
+ var collectedRows [][]string
for rows.Next() {
columns, err := rows.Columns()
assert.NoError(t, err)
@@ -41,6 +42,39 @@ func TestRows(t *testing.T) {
}
}
+func TestRowsIterator(t *testing.T) {
+ const (
+ sheet2 = "Sheet2"
+ expectedNumRow = 11
+ )
+ f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
+ require.NoError(t, err)
+
+ rows, err := f.Rows(sheet2)
+ require.NoError(t, err)
+ var rowCount int
+ for rows.Next() {
+ rowCount++
+ require.True(t, rowCount <= expectedNumRow, "rowCount is greater than expected")
+ }
+ assert.Equal(t, expectedNumRow, rowCount)
+
+ // Valued cell sparse distribution test
+ f = NewFile()
+ cells := []string{"C1", "E1", "A3", "B3", "C3", "D3", "E3"}
+ for _, cell := range cells {
+ f.SetCellValue("Sheet1", cell, 1)
+ }
+ rows, err = f.Rows("Sheet1")
+ require.NoError(t, err)
+ rowCount = 0
+ for rows.Next() {
+ rowCount++
+ require.True(t, rowCount <= 3, "rowCount is greater than expected")
+ }
+ assert.Equal(t, 3, rowCount)
+}
+
func TestRowsError(t *testing.T) {
xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
if !assert.NoError(t, err) {
@@ -93,22 +127,25 @@ func TestRowHeight(t *testing.T) {
}
func TestRowVisibility(t *testing.T) {
- xlsx, err := prepareTestBook1()
+ f, err := prepareTestBook1()
if !assert.NoError(t, err) {
t.FailNow()
}
- xlsx.NewSheet("Sheet3")
- assert.NoError(t, xlsx.SetRowVisible("Sheet3", 2, false))
- assert.NoError(t, xlsx.SetRowVisible("Sheet3", 2, true))
- xlsx.GetRowVisible("Sheet3", 2)
- xlsx.GetRowVisible("Sheet3", 25)
- assert.EqualError(t, xlsx.SetRowVisible("Sheet3", 0, true), "invalid row number 0")
+ f.NewSheet("Sheet3")
+ assert.NoError(t, f.SetRowVisible("Sheet3", 2, false))
+ assert.NoError(t, f.SetRowVisible("Sheet3", 2, true))
+ f.GetRowVisible("Sheet3", 2)
+ f.GetRowVisible("Sheet3", 25)
+ assert.EqualError(t, f.SetRowVisible("Sheet3", 0, true), "invalid row number 0")
+ assert.EqualError(t, f.SetRowVisible("SheetN", 2, false), "sheet SheetN is not exist")
- visible, err := xlsx.GetRowVisible("Sheet3", 0)
+ visible, err := f.GetRowVisible("Sheet3", 0)
assert.Equal(t, false, visible)
assert.EqualError(t, err, "invalid row number 0")
+ _, err = f.GetRowVisible("SheetN", 1)
+ assert.EqualError(t, err, "sheet SheetN is not exist")
- assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestRowVisibility.xlsx")))
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestRowVisibility.xlsx")))
}
func TestRemoveRow(t *testing.T) {
@@ -669,9 +706,23 @@ func TestDuplicateRowInvalidRownum(t *testing.T) {
}
}
+func TestGetValueFrom(t *testing.T) {
+ c := &xlsxC{T: "inlineStr"}
+ f := NewFile()
+ d := &xlsxSST{}
+ val, err := c.getValueFrom(f, d)
+ assert.NoError(t, err)
+ assert.Equal(t, "", val)
+}
+
+func TestErrSheetNotExistError(t *testing.T) {
+ err := ErrSheetNotExist{SheetName: "Sheet1"}
+ assert.EqualValues(t, err.Error(), "sheet Sheet1 is not exist")
+}
+
func BenchmarkRows(b *testing.B) {
+ f, _ := OpenFile(filepath.Join("test", "Book1.xlsx"))
for i := 0; i < b.N; i++ {
- f, _ := OpenFile(filepath.Join("test", "Book1.xlsx"))
rows, _ := f.Rows("Sheet2")
for rows.Next() {
row, _ := rows.Columns()
diff --git a/sheet.go b/sheet.go
index 42fd6b3..3b22a0e 100644
--- a/sheet.go
+++ b/sheet.go
@@ -129,12 +129,19 @@ func (f *File) workSheetWriter() {
}
}
-// trimCell provides a function to trim blank cells which created by completeCol.
+// trimCell provides a function to trim blank cells which created by fillColumns.
func trimCell(column []xlsxC) []xlsxC {
+ rowFull := true
+ for i := range column {
+ rowFull = column[i].hasValue() && rowFull
+ }
+ if rowFull {
+ return column
+ }
col := make([]xlsxC, len(column))
i := 0
for _, c := range column {
- if c.S != 0 || c.V != "" || c.F != nil || c.T != "" {
+ if c.hasValue() {
col[i] = c
i++
}
@@ -154,11 +161,12 @@ func (f *File) setContentTypes(index int) {
// setSheet provides a function to update sheet property by given index.
func (f *File) setSheet(index int, name string) {
- var xlsx xlsxWorksheet
- xlsx.Dimension.Ref = "A1"
- xlsx.SheetViews.SheetView = append(xlsx.SheetViews.SheetView, xlsxSheetView{
- WorkbookViewID: 0,
- })
+ xlsx := xlsxWorksheet{
+ Dimension: &xlsxDimension{Ref: "A1"},
+ SheetViews: xlsxSheetViews{
+ SheetView: []xlsxSheetView{{WorkbookViewID: 0}},
+ },
+ }
path := "xl/worksheets/sheet" + strconv.Itoa(index) + ".xml"
f.sheetMap[trimSheetName(name)] = path
f.Sheet[path] = &xlsx
@@ -212,7 +220,7 @@ func replaceRelationshipsBytes(content []byte) []byte {
// a horrible hack to fix that after the XML marshalling is completed.
func replaceRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte {
oldXmlns := []byte(`<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`)
- newXmlns := []byte(`<workbook xr:uid="{00000000-0001-0000-0000-000000000000}" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6" xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" mc:Ignorable="x14ac xr xr2 xr3 xr6 xr10 x15" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`)
+ newXmlns := []byte(`<workbook` + templateNamespaceIDMap)
return bytes.Replace(workbookMarshal, oldXmlns, newXmlns, -1)
}
@@ -227,6 +235,9 @@ func (f *File) SetActiveSheet(index int) {
wb := f.workbookReader()
for activeTab, sheet := range wb.Sheets.Sheet {
if sheet.SheetID == index {
+ if wb.BookViews == nil {
+ wb.BookViews = &xlsxBookViews{}
+ }
if len(wb.BookViews.WorkBookView) > 0 {
wb.BookViews.WorkBookView[0].ActiveTab = activeTab
} else {
@@ -258,16 +269,13 @@ func (f *File) SetActiveSheet(index int) {
func (f *File) GetActiveSheetIndex() int {
wb := f.workbookReader()
if wb != nil {
- view := wb.BookViews.WorkBookView
- sheets := wb.Sheets.Sheet
- var activeTab int
- if len(view) > 0 {
- activeTab = view[0].ActiveTab
- if len(sheets) > activeTab && sheets[activeTab].SheetID != 0 {
- return sheets[activeTab].SheetID
+ if wb.BookViews != nil && len(wb.BookViews.WorkBookView) > 0 {
+ activeTab := wb.BookViews.WorkBookView[0].ActiveTab
+ if len(wb.Sheets.Sheet) > activeTab && wb.Sheets.Sheet[activeTab].SheetID != 0 {
+ return wb.Sheets.Sheet[activeTab].SheetID
}
}
- if len(wb.Sheets.Sheet) == 1 {
+ if len(wb.Sheets.Sheet) >= 1 {
return wb.Sheets.Sheet[0].SheetID
}
}
@@ -418,6 +426,13 @@ func (f *File) DeleteSheet(name string) {
f.SheetCount--
}
}
+ if wb.BookViews != nil {
+ for idx, bookView := range wb.BookViews.WorkBookView {
+ if bookView.ActiveTab >= f.SheetCount {
+ wb.BookViews.WorkBookView[idx].ActiveTab--
+ }
+ }
+ }
f.SetActiveSheet(len(f.GetSheetMap()))
}
@@ -691,30 +706,23 @@ func (f *File) GetSheetVisible(name string) bool {
//
// result, err := f.SearchSheet("Sheet1", "[0-9]", true)
//
-func (f *File) SearchSheet(sheet, value string, reg ...bool) (result []string, err error) {
+func (f *File) SearchSheet(sheet, value string, reg ...bool) ([]string, error) {
var (
- xlsx *xlsxWorksheet
- regSearch, r, ok bool
- name string
- output []byte
+ regSearch bool
+ result []string
)
-
- for _, r = range reg {
+ for _, r := range reg {
regSearch = r
}
- if xlsx, err = f.workSheetReader(sheet); err != nil {
- return
- }
- if name, ok = f.sheetMap[trimSheetName(sheet)]; !ok {
- return
+ name, ok := f.sheetMap[trimSheetName(sheet)]
+ if !ok {
+ return result, ErrSheetNotExist{sheet}
}
- if xlsx != nil {
- if output, err = xml.Marshal(f.Sheet[name]); err != nil {
- return
- }
+ if f.Sheet[name] != nil {
+ // flush data
+ output, _ := xml.Marshal(f.Sheet[name])
f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpaceBytes(output))
}
-
return f.searchSheet(name, value, regSearch)
}
@@ -722,17 +730,16 @@ func (f *File) SearchSheet(sheet, value string, reg ...bool) (result []string, e
// cell value, and regular expression.
func (f *File) searchSheet(name, value string, regSearch bool) (result []string, err error) {
var (
- d *xlsxSST
- decoder *xml.Decoder
- inElement string
- r xlsxRow
- token xml.Token
+ cellName, inElement string
+ cellCol, row int
+ d *xlsxSST
)
d = f.sharedStringsReader()
- decoder = f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
+ decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
for {
- if token, err = decoder.Token(); err != nil || token == nil {
+ token, err := decoder.Token()
+ if err != nil || token == nil {
if err == io.EOF {
err = nil
}
@@ -742,31 +749,38 @@ func (f *File) searchSheet(name, value string, regSearch bool) (result []string,
case xml.StartElement:
inElement = startElement.Name.Local
if inElement == "row" {
- r = xlsxRow{}
- _ = decoder.DecodeElement(&r, &startElement)
- for _, colCell := range r.C {
- val, _ := colCell.getValueFrom(f, d)
- if regSearch {
- regex := regexp.MustCompile(value)
- if !regex.MatchString(val) {
- continue
- }
- } else {
- if val != value {
- continue
+ for _, attr := range startElement.Attr {
+ if attr.Name.Local == "r" {
+ row, err = strconv.Atoi(attr.Value)
+ if err != nil {
+ return result, err
}
}
-
- cellCol, _, err := CellNameToCoordinates(colCell.R)
- if err != nil {
- return result, err
+ }
+ }
+ if inElement == "c" {
+ colCell := xlsxC{}
+ _ = decoder.DecodeElement(&colCell, &startElement)
+ val, _ := colCell.getValueFrom(f, d)
+ if regSearch {
+ regex := regexp.MustCompile(value)
+ if !regex.MatchString(val) {
+ continue
}
- cellName, err := CoordinatesToCellName(cellCol, r.R)
- if err != nil {
- return result, err
+ } else {
+ if val != value {
+ continue
}
- result = append(result, cellName)
}
+ cellCol, _, err = CellNameToCoordinates(colCell.R)
+ if err != nil {
+ return result, err
+ }
+ cellName, err = CoordinatesToCellName(cellCol, row)
+ if err != nil {
+ return result, err
+ }
+ result = append(result, cellName)
}
default:
}
@@ -1288,7 +1302,7 @@ func (f *File) SetDefinedName(definedName *DefinedName) error {
scope = f.GetSheetName(*dn.LocalSheetID + 1)
}
if scope == definedName.Scope && dn.Name == definedName.Name {
- return errors.New("the same name already exists on scope")
+ return errors.New("the same name already exists on the scope")
}
}
wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName, d)
@@ -1300,6 +1314,32 @@ func (f *File) SetDefinedName(definedName *DefinedName) error {
return nil
}
+// DeleteDefinedName provides a function to delete the defined names of the
+// workbook or worksheet. If not specified scope, the default scope is
+// workbook. For example:
+//
+// f.DeleteDefinedName(&excelize.DefinedName{
+// Name: "Amount",
+// Scope: "Sheet2",
+// })
+//
+func (f *File) DeleteDefinedName(definedName *DefinedName) error {
+ wb := f.workbookReader()
+ if wb.DefinedNames != nil {
+ for idx, dn := range wb.DefinedNames.DefinedName {
+ var scope string
+ if dn.LocalSheetID != nil {
+ scope = f.GetSheetName(*dn.LocalSheetID + 1)
+ }
+ if scope == definedName.Scope && dn.Name == definedName.Name {
+ wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName[:idx], wb.DefinedNames.DefinedName[idx+1:]...)
+ return nil
+ }
+ }
+ }
+ return errors.New("no defined name on the scope")
+}
+
// GetDefinedName provides a function to get the defined names of the workbook
// or worksheet.
func (f *File) GetDefinedName() []DefinedName {
@@ -1401,12 +1441,17 @@ func (f *File) relsReader(path string) *xlsxRelationships {
// fillSheetData ensures there are enough rows, and columns in the chosen
// row to accept data. Missing rows are backfilled and given their row number
+// Uses the last populated row as a hint for the size of the next row to add
func prepareSheetXML(xlsx *xlsxWorksheet, col int, row int) {
rowCount := len(xlsx.SheetData.Row)
+ sizeHint := 0
+ if rowCount > 0 {
+ sizeHint = len(xlsx.SheetData.Row[rowCount-1].C)
+ }
if rowCount < row {
// append missing rows
for rowIdx := rowCount; rowIdx < row; rowIdx++ {
- xlsx.SheetData.Row = append(xlsx.SheetData.Row, xlsxRow{R: rowIdx + 1})
+ xlsx.SheetData.Row = append(xlsx.SheetData.Row, xlsxRow{R: rowIdx + 1, C: make([]xlsxC, 0, sizeHint)})
}
}
rowData := &xlsx.SheetData.Row[row-1]
diff --git a/sheet_test.go b/sheet_test.go
index 5179793..b9e4abf 100644
--- a/sheet_test.go
+++ b/sheet_test.go
@@ -66,6 +66,15 @@ func ExampleFile_GetPageLayout() {
// - fit to width: 1
}
+func TestNewSheet(t *testing.T) {
+ f := excelize.NewFile()
+ sheetID := f.NewSheet("Sheet2")
+ f.SetActiveSheet(sheetID)
+ // delete original sheet
+ f.DeleteSheet(f.GetSheetName(f.GetSheetIndex("Sheet1")))
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestNewSheet.xlsx")))
+}
+
func TestPageLayoutOption(t *testing.T) {
const sheet = "Sheet1"
@@ -201,8 +210,16 @@ func TestDefinedName(t *testing.T) {
Name: "Amount",
RefersTo: "Sheet1!$A$2:$D$5",
Comment: "defined name comment",
- }), "the same name already exists on scope")
+ }), "the same name already exists on the scope")
+ assert.EqualError(t, f.DeleteDefinedName(&excelize.DefinedName{
+ Name: "No Exist Defined Name",
+ }), "no defined name on the scope")
assert.Exactly(t, "Sheet1!$A$2:$D$5", f.GetDefinedName()[1].RefersTo)
+ assert.NoError(t, f.DeleteDefinedName(&excelize.DefinedName{
+ Name: "Amount",
+ }))
+ assert.Exactly(t, "Sheet1!$A$2:$D$5", f.GetDefinedName()[0].RefersTo)
+ assert.Exactly(t, 1, len(f.GetDefinedName()))
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestDefinedName.xlsx")))
}
diff --git a/sheetpr.go b/sheetpr.go
index a273ac1..086bd3a 100644
--- a/sheetpr.go
+++ b/sheetpr.go
@@ -191,3 +191,169 @@ func (f *File) GetSheetPrOptions(name string, opts ...SheetPrOptionPtr) error {
}
return err
}
+
+type (
+ // PageMarginBottom specifies the bottom margin for the page.
+ PageMarginBottom float64
+ // PageMarginFooter specifies the footer margin for the page.
+ PageMarginFooter float64
+ // PageMarginHeader specifies the header margin for the page.
+ PageMarginHeader float64
+ // PageMarginLeft specifies the left margin for the page.
+ PageMarginLeft float64
+ // PageMarginRight specifies the right margin for the page.
+ PageMarginRight float64
+ // PageMarginTop specifies the top margin for the page.
+ PageMarginTop float64
+)
+
+// setPageMargins provides a method to set the bottom margin for the worksheet.
+func (p PageMarginBottom) setPageMargins(pm *xlsxPageMargins) {
+ pm.Bottom = float64(p)
+}
+
+// setPageMargins provides a method to get the bottom margin for the worksheet.
+func (p *PageMarginBottom) getPageMargins(pm *xlsxPageMargins) {
+ // Excel default: 0.75
+ if pm == nil || pm.Bottom == 0 {
+ *p = 0.75
+ return
+ }
+ *p = PageMarginBottom(pm.Bottom)
+}
+
+// setPageMargins provides a method to set the footer margin for the worksheet.
+func (p PageMarginFooter) setPageMargins(pm *xlsxPageMargins) {
+ pm.Footer = float64(p)
+}
+
+// setPageMargins provides a method to get the footer margin for the worksheet.
+func (p *PageMarginFooter) getPageMargins(pm *xlsxPageMargins) {
+ // Excel default: 0.3
+ if pm == nil || pm.Footer == 0 {
+ *p = 0.3
+ return
+ }
+ *p = PageMarginFooter(pm.Footer)
+}
+
+// setPageMargins provides a method to set the header margin for the worksheet.
+func (p PageMarginHeader) setPageMargins(pm *xlsxPageMargins) {
+ pm.Header = float64(p)
+}
+
+// setPageMargins provides a method to get the header margin for the worksheet.
+func (p *PageMarginHeader) getPageMargins(pm *xlsxPageMargins) {
+ // Excel default: 0.3
+ if pm == nil || pm.Header == 0 {
+ *p = 0.3
+ return
+ }
+ *p = PageMarginHeader(pm.Header)
+}
+
+// setPageMargins provides a method to set the left margin for the worksheet.
+func (p PageMarginLeft) setPageMargins(pm *xlsxPageMargins) {
+ pm.Left = float64(p)
+}
+
+// setPageMargins provides a method to get the left margin for the worksheet.
+func (p *PageMarginLeft) getPageMargins(pm *xlsxPageMargins) {
+ // Excel default: 0.7
+ if pm == nil || pm.Left == 0 {
+ *p = 0.7
+ return
+ }
+ *p = PageMarginLeft(pm.Left)
+}
+
+// setPageMargins provides a method to set the right margin for the worksheet.
+func (p PageMarginRight) setPageMargins(pm *xlsxPageMargins) {
+ pm.Right = float64(p)
+}
+
+// setPageMargins provides a method to get the right margin for the worksheet.
+func (p *PageMarginRight) getPageMargins(pm *xlsxPageMargins) {
+ // Excel default: 0.7
+ if pm == nil || pm.Right == 0 {
+ *p = 0.7
+ return
+ }
+ *p = PageMarginRight(pm.Right)
+}
+
+// setPageMargins provides a method to set the top margin for the worksheet.
+func (p PageMarginTop) setPageMargins(pm *xlsxPageMargins) {
+ pm.Top = float64(p)
+}
+
+// setPageMargins provides a method to get the top margin for the worksheet.
+func (p *PageMarginTop) getPageMargins(pm *xlsxPageMargins) {
+ // Excel default: 0.75
+ if pm == nil || pm.Top == 0 {
+ *p = 0.75
+ return
+ }
+ *p = PageMarginTop(pm.Top)
+}
+
+// PageMarginsOptions is an option of a page margin of a worksheet. See
+// SetPageMargins().
+type PageMarginsOptions interface {
+ setPageMargins(layout *xlsxPageMargins)
+}
+
+// PageMarginsOptionsPtr is a writable PageMarginsOptions. See
+// GetPageMargins().
+type PageMarginsOptionsPtr interface {
+ PageMarginsOptions
+ getPageMargins(layout *xlsxPageMargins)
+}
+
+// SetPageMargins provides a function to set worksheet page margins.
+//
+// Available options:
+// PageMarginBotom(float64)
+// PageMarginFooter(float64)
+// PageMarginHeader(float64)
+// PageMarginLeft(float64)
+// PageMarginRight(float64)
+// PageMarginTop(float64)
+func (f *File) SetPageMargins(sheet string, opts ...PageMarginsOptions) error {
+ s, err := f.workSheetReader(sheet)
+ if err != nil {
+ return err
+ }
+ pm := s.PageMargins
+ if pm == nil {
+ pm = new(xlsxPageMargins)
+ s.PageMargins = pm
+ }
+
+ for _, opt := range opts {
+ opt.setPageMargins(pm)
+ }
+ return err
+}
+
+// GetPageMargins provides a function to get worksheet page margins.
+//
+// Available options:
+// PageMarginBotom(float64)
+// PageMarginFooter(float64)
+// PageMarginHeader(float64)
+// PageMarginLeft(float64)
+// PageMarginRight(float64)
+// PageMarginTop(float64)
+func (f *File) GetPageMargins(sheet string, opts ...PageMarginsOptionsPtr) error {
+ s, err := f.workSheetReader(sheet)
+ if err != nil {
+ return err
+ }
+ pm := s.PageMargins
+
+ for _, opt := range opts {
+ opt.getPageMargins(pm)
+ }
+ return err
+}
diff --git a/sheetpr_test.go b/sheetpr_test.go
index 97a314c..d1ae2f1 100644
--- a/sheetpr_test.go
+++ b/sheetpr_test.go
@@ -146,3 +146,164 @@ func TestSheetPrOptions(t *testing.T) {
})
}
}
+
+func TestSetSheetrOptions(t *testing.T) {
+ f := excelize.NewFile()
+ // Test SetSheetrOptions on not exists worksheet.
+ assert.EqualError(t, f.SetSheetPrOptions("SheetN"), "sheet SheetN is not exist")
+}
+
+func TestGetSheetPrOptions(t *testing.T) {
+ f := excelize.NewFile()
+ // Test GetSheetPrOptions on not exists worksheet.
+ assert.EqualError(t, f.GetSheetPrOptions("SheetN"), "sheet SheetN is not exist")
+}
+
+var _ = []excelize.PageMarginsOptions{
+ excelize.PageMarginBottom(1.0),
+ excelize.PageMarginFooter(1.0),
+ excelize.PageMarginHeader(1.0),
+ excelize.PageMarginLeft(1.0),
+ excelize.PageMarginRight(1.0),
+ excelize.PageMarginTop(1.0),
+}
+
+var _ = []excelize.PageMarginsOptionsPtr{
+ (*excelize.PageMarginBottom)(nil),
+ (*excelize.PageMarginFooter)(nil),
+ (*excelize.PageMarginHeader)(nil),
+ (*excelize.PageMarginLeft)(nil),
+ (*excelize.PageMarginRight)(nil),
+ (*excelize.PageMarginTop)(nil),
+}
+
+func ExampleFile_SetPageMargins() {
+ f := excelize.NewFile()
+ const sheet = "Sheet1"
+
+ if err := f.SetPageMargins(sheet,
+ excelize.PageMarginBottom(1.0),
+ excelize.PageMarginFooter(1.0),
+ excelize.PageMarginHeader(1.0),
+ excelize.PageMarginLeft(1.0),
+ excelize.PageMarginRight(1.0),
+ excelize.PageMarginTop(1.0),
+ ); err != nil {
+ panic(err)
+ }
+ // Output:
+}
+
+func ExampleFile_GetPageMargins() {
+ f := excelize.NewFile()
+ const sheet = "Sheet1"
+
+ var (
+ marginBottom excelize.PageMarginBottom
+ marginFooter excelize.PageMarginFooter
+ marginHeader excelize.PageMarginHeader
+ marginLeft excelize.PageMarginLeft
+ marginRight excelize.PageMarginRight
+ marginTop excelize.PageMarginTop
+ )
+
+ if err := f.GetPageMargins(sheet,
+ &marginBottom,
+ &marginFooter,
+ &marginHeader,
+ &marginLeft,
+ &marginRight,
+ &marginTop,
+ ); err != nil {
+ panic(err)
+ }
+ fmt.Println("Defaults:")
+ fmt.Println("- marginBottom:", marginBottom)
+ fmt.Println("- marginFooter:", marginFooter)
+ fmt.Println("- marginHeader:", marginHeader)
+ fmt.Println("- marginLeft:", marginLeft)
+ fmt.Println("- marginRight:", marginRight)
+ fmt.Println("- marginTop:", marginTop)
+ // Output:
+ // Defaults:
+ // - marginBottom: 0.75
+ // - marginFooter: 0.3
+ // - marginHeader: 0.3
+ // - marginLeft: 0.7
+ // - marginRight: 0.7
+ // - marginTop: 0.75
+}
+
+func TestPageMarginsOption(t *testing.T) {
+ const sheet = "Sheet1"
+
+ testData := []struct {
+ container excelize.PageMarginsOptionsPtr
+ nonDefault excelize.PageMarginsOptions
+ }{
+ {new(excelize.PageMarginTop), excelize.PageMarginTop(1.0)},
+ {new(excelize.PageMarginBottom), excelize.PageMarginBottom(1.0)},
+ {new(excelize.PageMarginLeft), excelize.PageMarginLeft(1.0)},
+ {new(excelize.PageMarginRight), excelize.PageMarginRight(1.0)},
+ {new(excelize.PageMarginHeader), excelize.PageMarginHeader(1.0)},
+ {new(excelize.PageMarginFooter), excelize.PageMarginFooter(1.0)},
+ }
+
+ for i, test := range testData {
+ t.Run(fmt.Sprintf("TestData%d", i), func(t *testing.T) {
+
+ opt := test.nonDefault
+ t.Logf("option %T", opt)
+
+ def := deepcopy.Copy(test.container).(excelize.PageMarginsOptionsPtr)
+ val1 := deepcopy.Copy(def).(excelize.PageMarginsOptionsPtr)
+ val2 := deepcopy.Copy(def).(excelize.PageMarginsOptionsPtr)
+
+ f := excelize.NewFile()
+ // Get the default value
+ assert.NoError(t, f.GetPageMargins(sheet, def), opt)
+ // Get again and check
+ assert.NoError(t, f.GetPageMargins(sheet, val1), opt)
+ if !assert.Equal(t, val1, def, opt) {
+ t.FailNow()
+ }
+ // Set the same value
+ assert.NoError(t, f.SetPageMargins(sheet, val1), opt)
+ // Get again and check
+ assert.NoError(t, f.GetPageMargins(sheet, val1), opt)
+ if !assert.Equal(t, val1, def, "%T: value should not have changed", opt) {
+ t.FailNow()
+ }
+ // Set a different value
+ assert.NoError(t, f.SetPageMargins(sheet, test.nonDefault), opt)
+ assert.NoError(t, f.GetPageMargins(sheet, val1), opt)
+ // Get again and compare
+ assert.NoError(t, f.GetPageMargins(sheet, val2), opt)
+ if !assert.Equal(t, val1, val2, "%T: value should not have changed", opt) {
+ t.FailNow()
+ }
+ // Value should not be the same as the default
+ if !assert.NotEqual(t, def, val1, "%T: value should have changed from default", opt) {
+ t.FailNow()
+ }
+ // Restore the default value
+ assert.NoError(t, f.SetPageMargins(sheet, def), opt)
+ assert.NoError(t, f.GetPageMargins(sheet, val1), opt)
+ if !assert.Equal(t, def, val1) {
+ t.FailNow()
+ }
+ })
+ }
+}
+
+func TestSetPageMargins(t *testing.T) {
+ f := excelize.NewFile()
+ // Test set page margins on not exists worksheet.
+ assert.EqualError(t, f.SetPageMargins("SheetN"), "sheet SheetN is not exist")
+}
+
+func TestGetPageMargins(t *testing.T) {
+ f := excelize.NewFile()
+ // Test get page margins on not exists worksheet.
+ assert.EqualError(t, f.GetPageMargins("SheetN"), "sheet SheetN is not exist")
+}
diff --git a/stream.go b/stream.go
new file mode 100644
index 0000000..5e74e8e
--- /dev/null
+++ b/stream.go
@@ -0,0 +1,218 @@
+// Copyright 2016 - 2019 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.10 or later.
+
+package excelize
+
+import (
+ "bytes"
+ "encoding/xml"
+ "errors"
+ "fmt"
+ "io/ioutil"
+ "os"
+ "reflect"
+)
+
+// StreamWriter defined the type of stream writer.
+type StreamWriter struct {
+ tmpFile *os.File
+ File *File
+ Sheet string
+ SheetID int
+ SheetData bytes.Buffer
+}
+
+// NewStreamWriter return stream writer struct by given worksheet name for
+// generate new worksheet with large amounts of data. Note that after set
+// rows, you must call the 'Flush' method to end the streaming writing
+// process and ensure that the order of line numbers is ascending. For
+// example, set data for worksheet of size 102400 rows x 50 columns with
+// numbers:
+//
+// file := excelize.NewFile()
+// streamWriter, err := file.NewStreamWriter("Sheet1")
+// if err != nil {
+// panic(err)
+// }
+// for rowID := 1; rowID <= 102400; rowID++ {
+// row := make([]interface{}, 50)
+// for colID := 0; colID < 50; colID++ {
+// row[colID] = rand.Intn(640000)
+// }
+// cell, _ := excelize.CoordinatesToCellName(1, rowID)
+// if err := streamWriter.SetRow(cell, &row); err != nil {
+// panic(err)
+// }
+// }
+// if err := streamWriter.Flush(); err != nil {
+// panic(err)
+// }
+// if err := file.SaveAs("Book1.xlsx"); err != nil {
+// panic(err)
+// }
+//
+func (f *File) NewStreamWriter(sheet string) (*StreamWriter, error) {
+ sheetID := f.GetSheetIndex(sheet)
+ if sheetID == 0 {
+ return nil, fmt.Errorf("sheet %s is not exist", sheet)
+ }
+ rsw := &StreamWriter{
+ File: f,
+ Sheet: sheet,
+ SheetID: sheetID,
+ }
+ rsw.SheetData.WriteString("<sheetData>")
+ return rsw, nil
+}
+
+// SetRow writes an array to streaming row by given worksheet name, starting
+// coordinate and a pointer to array type 'slice'. Note that, cell settings
+// with styles are not supported currently and after set rows, you must call the
+// 'Flush' method to end the streaming writing process. The following
+// shows the supported data types:
+//
+// int
+// string
+//
+func (sw *StreamWriter) SetRow(axis string, slice interface{}) error {
+ col, row, err := CellNameToCoordinates(axis)
+ if err != nil {
+ return err
+ }
+ // Make sure 'slice' is a Ptr to Slice
+ v := reflect.ValueOf(slice)
+ if v.Kind() != reflect.Ptr || v.Elem().Kind() != reflect.Slice {
+ return errors.New("pointer to slice expected")
+ }
+ v = v.Elem()
+ sw.SheetData.WriteString(fmt.Sprintf(`<row r="%d">`, row))
+ for i := 0; i < v.Len(); i++ {
+ axis, err := CoordinatesToCellName(col+i, row)
+ if err != nil {
+ return err
+ }
+ switch val := v.Index(i).Interface().(type) {
+ case int:
+ sw.SheetData.WriteString(fmt.Sprintf(`<c r="%s"><v>%d</v></c>`, axis, val))
+ case string:
+ sw.SheetData.WriteString(sw.setCellStr(axis, val))
+ default:
+ sw.SheetData.WriteString(sw.setCellStr(axis, fmt.Sprint(val)))
+ }
+ }
+ sw.SheetData.WriteString(`</row>`)
+ // Try to use local storage
+ chunk := 1 << 24
+ if sw.SheetData.Len() >= chunk {
+ if sw.tmpFile == nil {
+ err := sw.createTmp()
+ if err != nil {
+ // can not use local storage
+ return nil
+ }
+ }
+ // use local storage
+ _, err := sw.tmpFile.Write(sw.SheetData.Bytes())
+ if err != nil {
+ return nil
+ }
+ sw.SheetData.Reset()
+ }
+ return err
+}
+
+// Flush ending the streaming writing process.
+func (sw *StreamWriter) Flush() error {
+ sw.SheetData.WriteString(`</sheetData>`)
+
+ ws, err := sw.File.workSheetReader(sw.Sheet)
+ if err != nil {
+ return err
+ }
+ sheetXML := fmt.Sprintf("xl/worksheets/sheet%d.xml", sw.SheetID)
+ delete(sw.File.Sheet, sheetXML)
+ delete(sw.File.checked, sheetXML)
+ var sheetDataByte []byte
+ if sw.tmpFile != nil {
+ // close the local storage file
+ if err = sw.tmpFile.Close(); err != nil {
+ return err
+ }
+
+ file, err := os.Open(sw.tmpFile.Name())
+ if err != nil {
+ return err
+ }
+
+ sheetDataByte, err = ioutil.ReadAll(file)
+ if err != nil {
+ return err
+ }
+
+ if err := file.Close(); err != nil {
+ return err
+ }
+
+ err = os.Remove(sw.tmpFile.Name())
+ if err != nil {
+ return err
+ }
+ }
+
+ sheetDataByte = append(sheetDataByte, sw.SheetData.Bytes()...)
+ replaceMap := map[string][]byte{
+ "XMLName": []byte{},
+ "SheetData": sheetDataByte,
+ }
+ sw.SheetData.Reset()
+ sw.File.XLSX[fmt.Sprintf("xl/worksheets/sheet%d.xml", sw.SheetID)] =
+ StreamMarshalSheet(ws, replaceMap)
+ return err
+}
+
+// createTmp creates a temporary file in the operating system default
+// temporary directory.
+func (sw *StreamWriter) createTmp() (err error) {
+ sw.tmpFile, err = ioutil.TempFile(os.TempDir(), "excelize-")
+ return err
+}
+
+// StreamMarshalSheet provides method to serialization worksheets by field as
+// streaming.
+func StreamMarshalSheet(ws *xlsxWorksheet, replaceMap map[string][]byte) []byte {
+ s := reflect.ValueOf(ws).Elem()
+ typeOfT := s.Type()
+ var marshalResult []byte
+ marshalResult = append(marshalResult, []byte(XMLHeader+`<worksheet`+templateNamespaceIDMap)...)
+ for i := 0; i < s.NumField(); i++ {
+ content, ok := replaceMap[typeOfT.Field(i).Name]
+ if ok {
+ marshalResult = append(marshalResult, content...)
+ continue
+ }
+ out, _ := xml.Marshal(s.Field(i).Interface())
+ marshalResult = append(marshalResult, out...)
+ }
+ marshalResult = append(marshalResult, []byte(`</worksheet>`)...)
+ return marshalResult
+}
+
+// setCellStr provides a function to set string type value of a cell as
+// streaming. Total number of characters that a cell can contain 32767
+// characters.
+func (sw *StreamWriter) setCellStr(axis, value string) string {
+ if len(value) > 32767 {
+ value = value[0:32767]
+ }
+ // Leading and ending space(s) character detection.
+ if len(value) > 0 && (value[0] == 32 || value[len(value)-1] == 32) {
+ return fmt.Sprintf(`<c xml:space="preserve" r="%s" t="str"><v>%s</v></c>`, axis, value)
+ }
+ return fmt.Sprintf(`<c r="%s" t="str"><v>%s</v></c>`, axis, value)
+}
diff --git a/stream_test.go b/stream_test.go
new file mode 100644
index 0000000..97c55a7
--- /dev/null
+++ b/stream_test.go
@@ -0,0 +1,66 @@
+package excelize
+
+import (
+ "math/rand"
+ "path/filepath"
+ "strings"
+ "testing"
+
+ "github.com/stretchr/testify/assert"
+)
+
+func TestStreamWriter(t *testing.T) {
+ file := NewFile()
+ streamWriter, err := file.NewStreamWriter("Sheet1")
+ assert.NoError(t, err)
+
+ // Test max characters in a cell.
+ row := make([]interface{}, 1)
+ row[0] = strings.Repeat("c", 32769)
+ assert.NoError(t, streamWriter.SetRow("A1", &row))
+
+ // Test leading and ending space(s) character characters in a cell.
+ row = make([]interface{}, 1)
+ row[0] = " characters"
+ assert.NoError(t, streamWriter.SetRow("A2", &row))
+
+ row = make([]interface{}, 1)
+ row[0] = []byte("Word")
+ assert.NoError(t, streamWriter.SetRow("A3", &row))
+
+ for rowID := 10; rowID <= 51200; rowID++ {
+ row := make([]interface{}, 50)
+ for colID := 0; colID < 50; colID++ {
+ row[colID] = rand.Intn(640000)
+ }
+ cell, _ := CoordinatesToCellName(1, rowID)
+ assert.NoError(t, streamWriter.SetRow(cell, &row))
+ }
+
+ err = streamWriter.Flush()
+ assert.NoError(t, err)
+ // Save xlsx file by the given path.
+ assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriter.xlsx")))
+
+ // Test error exceptions
+ streamWriter, err = file.NewStreamWriter("SheetN")
+ assert.EqualError(t, err, "sheet SheetN is not exist")
+}
+
+func TestFlush(t *testing.T) {
+ // Test error exceptions
+ file := NewFile()
+ streamWriter, err := file.NewStreamWriter("Sheet1")
+ assert.NoError(t, err)
+ streamWriter.Sheet = "SheetN"
+ assert.EqualError(t, streamWriter.Flush(), "sheet SheetN is not exist")
+}
+
+func TestSetRow(t *testing.T) {
+ // Test error exceptions
+ file := NewFile()
+ streamWriter, err := file.NewStreamWriter("Sheet1")
+ assert.NoError(t, err)
+ assert.EqualError(t, streamWriter.SetRow("A", &[]interface{}{}), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
+ assert.EqualError(t, streamWriter.SetRow("A1", []interface{}{}), `pointer to slice expected`)
+}
diff --git a/templates.go b/templates.go
index 0d3a0c5..5b79b0c 100644
--- a/templates.go
+++ b/templates.go
@@ -29,7 +29,7 @@ const templateWorkbook = `<workbook xmlns="http://schemas.openxmlformats.org/spr
const templateStyles = `<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac x16r2" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main"><fonts count="1" x14ac:knownFonts="1"><font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/></font></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills><borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs><cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles><dxfs count="0"/><tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16"/></styleSheet>`
-const templateSheet = `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="A1"/><sheetViews><sheetView tabSelected="1" workbookViewId="0"/></sheetViews><sheetFormatPr defaultRowHeight="15"/><sheetData/><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/></worksheet>`
+const templateSheet = `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="A1"/><sheetViews><sheetView tabSelected="1" workbookViewId="0"/></sheetViews><sheetFormatPr defaultRowHeight="15"/><sheetData/></worksheet>`
const templateWorkbookRels = `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/></Relationships>`
@@ -38,3 +38,5 @@ const templateDocpropsCore = `<cp:coreProperties xmlns:cp="http://schemas.openxm
const templateRels = `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>`
const templateTheme = `<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office Theme"><a:themeElements><a:clrScheme name="Office"><a:dk1><a:sysClr val="windowText" lastClr="000000"/></a:dk1><a:lt1><a:sysClr val="window" lastClr="FFFFFF"/></a:lt1><a:dk2><a:srgbClr val="44546A"/></a:dk2><a:lt2><a:srgbClr val="E7E6E6"/></a:lt2><a:accent1><a:srgbClr val="5B9BD5"/></a:accent1><a:accent2><a:srgbClr val="ED7D31"/></a:accent2><a:accent3><a:srgbClr val="A5A5A5"/></a:accent3><a:accent4><a:srgbClr val="FFC000"/></a:accent4><a:accent5><a:srgbClr val="4472C4"/></a:accent5><a:accent6><a:srgbClr val="70AD47"/></a:accent6><a:hlink><a:srgbClr val="0563C1"/></a:hlink><a:folHlink><a:srgbClr val="954F72"/></a:folHlink></a:clrScheme><a:fontScheme name="Office"><a:majorFont><a:latin typeface="Calibri Light" panose="020F0302020204030204"/><a:ea typeface=""/><a:cs typeface=""/><a:font script="Jpan" typeface="游ゴシック Light"/><a:font script="Hang" typeface="맑은 고딕"/><a:font script="Hans" typeface="等线 Light"/><a:font script="Hant" typeface="新細明體"/><a:font script="Arab" typeface="Times New Roman"/><a:font script="Hebr" typeface="Times New Roman"/><a:font script="Thai" typeface="Tahoma"/><a:font script="Ethi" typeface="Nyala"/><a:font script="Beng" typeface="Vrinda"/><a:font script="Gujr" typeface="Shruti"/><a:font script="Khmr" typeface="MoolBoran"/><a:font script="Knda" typeface="Tunga"/><a:font script="Guru" typeface="Raavi"/><a:font script="Cans" typeface="Euphemia"/><a:font script="Cher" typeface="Plantagenet Cherokee"/><a:font script="Yiii" typeface="Microsoft Yi Baiti"/><a:font script="Tibt" typeface="Microsoft Himalaya"/><a:font script="Thaa" typeface="MV Boli"/><a:font script="Deva" typeface="Mangal"/><a:font script="Telu" typeface="Gautami"/><a:font script="Taml" typeface="Latha"/><a:font script="Syrc" typeface="Estrangelo Edessa"/><a:font script="Orya" typeface="Kalinga"/><a:font script="Mlym" typeface="Kartika"/><a:font script="Laoo" typeface="DokChampa"/><a:font script="Sinh" typeface="Iskoola Pota"/><a:font script="Mong" typeface="Mongolian Baiti"/><a:font script="Viet" typeface="Times New Roman"/><a:font script="Uigh" typeface="Microsoft Uighur"/><a:font script="Geor" typeface="Sylfaen"/></a:majorFont><a:minorFont><a:latin typeface="Calibri" panose="020F0502020204030204"/><a:ea typeface=""/><a:cs typeface=""/><a:font script="Jpan" typeface="游ゴシック"/><a:font script="Hang" typeface="맑은 고딕"/><a:font script="Hans" typeface="等线"/><a:font script="Hant" typeface="新細明體"/><a:font script="Arab" typeface="Arial"/><a:font script="Hebr" typeface="Arial"/><a:font script="Thai" typeface="Tahoma"/><a:font script="Ethi" typeface="Nyala"/><a:font script="Beng" typeface="Vrinda"/><a:font script="Gujr" typeface="Shruti"/><a:font script="Khmr" typeface="DaunPenh"/><a:font script="Knda" typeface="Tunga"/><a:font script="Guru" typeface="Raavi"/><a:font script="Cans" typeface="Euphemia"/><a:font script="Cher" typeface="Plantagenet Cherokee"/><a:font script="Yiii" typeface="Microsoft Yi Baiti"/><a:font script="Tibt" typeface="Microsoft Himalaya"/><a:font script="Thaa" typeface="MV Boli"/><a:font script="Deva" typeface="Mangal"/><a:font script="Telu" typeface="Gautami"/><a:font script="Taml" typeface="Latha"/><a:font script="Syrc" typeface="Estrangelo Edessa"/><a:font script="Orya" typeface="Kalinga"/><a:font script="Mlym" typeface="Kartika"/><a:font script="Laoo" typeface="DokChampa"/><a:font script="Sinh" typeface="Iskoola Pota"/><a:font script="Mong" typeface="Mongolian Baiti"/><a:font script="Viet" typeface="Arial"/><a:font script="Uigh" typeface="Microsoft Uighur"/><a:font script="Geor" typeface="Sylfaen"/></a:minorFont></a:fontScheme><a:fmtScheme name="Office"><a:fillStyleLst><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:lumMod val="110000"/><a:satMod val="105000"/><a:tint val="67000"/></a:schemeClr></a:gs><a:gs pos="50000"><a:schemeClr val="phClr"><a:lumMod val="105000"/><a:satMod val="103000"/><a:tint val="73000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:lumMod val="105000"/><a:satMod val="109000"/><a:tint val="81000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="5400000" scaled="0"/></a:gradFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:satMod val="103000"/><a:lumMod val="102000"/><a:tint val="94000"/></a:schemeClr></a:gs><a:gs pos="50000"><a:schemeClr val="phClr"><a:satMod val="110000"/><a:lumMod val="100000"/><a:shade val="100000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:lumMod val="99000"/><a:satMod val="120000"/><a:shade val="78000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="5400000" scaled="0"/></a:gradFill></a:fillStyleLst><a:lnStyleLst><a:ln w="6350" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/><a:miter lim="800000"/></a:ln><a:ln w="12700" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/><a:miter lim="800000"/></a:ln><a:ln w="19050" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/><a:miter lim="800000"/></a:ln></a:lnStyleLst><a:effectStyleLst><a:effectStyle><a:effectLst/></a:effectStyle><a:effectStyle><a:effectLst/></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad="57150" dist="19050" dir="5400000" algn="ctr" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="63000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle></a:effectStyleLst><a:bgFillStyleLst><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:solidFill><a:schemeClr val="phClr"><a:tint val="95000"/><a:satMod val="170000"/></a:schemeClr></a:solidFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="93000"/><a:satMod val="150000"/><a:shade val="98000"/><a:lumMod val="102000"/></a:schemeClr></a:gs><a:gs pos="50000"><a:schemeClr val="phClr"><a:tint val="98000"/><a:satMod val="130000"/><a:shade val="90000"/><a:lumMod val="103000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="63000"/><a:satMod val="120000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="5400000" scaled="0"/></a:gradFill></a:bgFillStyleLst></a:fmtScheme></a:themeElements><a:objectDefaults/><a:extraClrSchemeLst/></a:theme>`
+
+const templateNamespaceIDMap = ` xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:ap="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:op="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:cdr="http://schemas.openxmlformats.org/drawingml/2006/chartDrawing" xmlns:comp="http://schemas.openxmlformats.org/drawingml/2006/compatibility" xmlns:dgm="http://schemas.openxmlformats.org/drawingml/2006/diagram" xmlns:lc="http://schemas.openxmlformats.org/drawingml/2006/lockedCanvas" xmlns:pic="http://schemas.openxmlformats.org/drawingml/2006/picture" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:ds="http://schemas.openxmlformats.org/officeDocument/2006/customXml" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:sl="http://schemas.openxmlformats.org/schemaLibrary/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:xne="http://schemas.microsoft.com/office/excel/2006/main" xmlns:mso="http://schemas.microsoft.com/office/2006/01/customui" xmlns:ax="http://schemas.microsoft.com/office/2006/activeX" xmlns:cppr="http://schemas.microsoft.com/office/2006/coverPageProps" xmlns:cdip="http://schemas.microsoft.com/office/2006/customDocumentInformationPanel" xmlns:ct="http://schemas.microsoft.com/office/2006/metadata/contentType" xmlns:ntns="http://schemas.microsoft.com/office/2006/metadata/customXsn" xmlns:lp="http://schemas.microsoft.com/office/2006/metadata/longProperties" xmlns:ma="http://schemas.microsoft.com/office/2006/metadata/properties/metaAttributes" xmlns:msink="http://schemas.microsoft.com/ink/2010/main" xmlns:c14="http://schemas.microsoft.com/office/drawing/2007/8/2/chart" xmlns:cdr14="http://schemas.microsoft.com/office/drawing/2010/chartDrawing" xmlns:a14="http://schemas.microsoft.com/office/drawing/2010/main" xmlns:pic14="http://schemas.microsoft.com/office/drawing/2010/picture" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:xdr14="http://schemas.microsoft.com/office/excel/2010/spreadsheetDrawing" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:dsp="http://schemas.microsoft.com/office/drawing/2008/diagram" xmlns:mso14="http://schemas.microsoft.com/office/2009/07/customui" xmlns:dgm14="http://schemas.microsoft.com/office/drawing/2010/diagram" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" xmlns:x12ac="http://schemas.microsoft.com/office/spreadsheetml/2011/1/ac" xmlns:x15ac="http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xmlns:xr4="http://schemas.microsoft.com/office/spreadsheetml/2016/revision4" xmlns:xr5="http://schemas.microsoft.com/office/spreadsheetml/2016/revision5" xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6" xmlns:xr7="http://schemas.microsoft.com/office/spreadsheetml/2016/revision7" xmlns:xr8="http://schemas.microsoft.com/office/spreadsheetml/2016/revision8" xmlns:xr9="http://schemas.microsoft.com/office/spreadsheetml/2016/revision9" xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10" xmlns:xr11="http://schemas.microsoft.com/office/spreadsheetml/2016/revision11" xmlns:xr12="http://schemas.microsoft.com/office/spreadsheetml/2016/revision12" xmlns:xr13="http://schemas.microsoft.com/office/spreadsheetml/2016/revision13" xmlns:xr14="http://schemas.microsoft.com/office/spreadsheetml/2016/revision14" xmlns:xr15="http://schemas.microsoft.com/office/spreadsheetml/2016/revision15" xmlns:x16="http://schemas.microsoft.com/office/spreadsheetml/2014/11/main" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main" mc:Ignorable="c14 cdr14 a14 pic14 x14 xdr14 x14ac dsp mso14 dgm14 x15 x12ac x15ac xr xr2 xr3 xr4 xr5 xr6 xr7 xr8 xr9 xr10 xr11 xr12 xr13 xr14 xr15 x15 x16 x16r2 mo mx mv o v" xmlns:mo="http://schemas.microsoft.com/office/mac/office/2008/main" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:v="urn:schemas-microsoft-com:vml" xr:uid="{00000000-0001-0000-0000-000000000000}">`
diff --git a/xmlChart.go b/xmlChart.go
index 69e119a..a02da2a 100644
--- a/xmlChart.go
+++ b/xmlChart.go
@@ -347,6 +347,7 @@ type cAxs struct {
Delete *attrValBool `xml:"delete"`
AxPos *attrValString `xml:"axPos"`
MajorGridlines *cChartLines `xml:"majorGridlines"`
+ MinorGridlines *cChartLines `xml:"minorGridlines"`
NumFmt *cNumFmt `xml:"numFmt"`
MajorTickMark *attrValString `xml:"majorTickMark"`
MinorTickMark *attrValString `xml:"minorTickMark"`
@@ -514,6 +515,7 @@ type cPageMargins struct {
type formatChartAxis struct {
Crossing string `json:"crossing"`
MajorGridlines bool `json:"major_grid_lines"`
+ MinorGridlines bool `json:"minor_grid_lines"`
MajorTickMark string `json:"major_tick_mark"`
MinorTickMark string `json:"minor_tick_mark"`
MinorUnitType string `json:"minor_unit_type"`
@@ -606,8 +608,9 @@ type formatChartSeries struct {
Categories string `json:"categories"`
Values string `json:"values"`
Line struct {
- None bool `json:"none"`
- Color string `json:"color"`
+ None bool `json:"none"`
+ Color string `json:"color"`
+ Width float64 `json:"width"`
} `json:"line"`
Marker struct {
Type string `json:"type"`
diff --git a/xmlDrawing.go b/xmlDrawing.go
index 4338c5e..1c24f08 100644
--- a/xmlDrawing.go
+++ b/xmlDrawing.go
@@ -47,10 +47,10 @@ const (
NameSpaceDublinCore = "http://purl.org/dc/elements/1.1/"
NameSpaceDublinCoreTerms = "http://purl.org/dc/terms/"
NameSpaceDublinCoreMetadataIntiative = "http://purl.org/dc/dcmitype/"
- // The extLst child element ([ISO/IEC29500-1:2016] section 18.2.10) of the
- // worksheet element ([ISO/IEC29500-1:2016] section 18.3.1.99) is extended by
- // the addition of new child ext elements ([ISO/IEC29500-1:2016] section
- // 18.2.7)
+ // ExtURIConditionalFormattings is the extLst child element
+ // ([ISO/IEC29500-1:2016] section 18.2.10) of the worksheet element
+ // ([ISO/IEC29500-1:2016] section 18.3.1.99) is extended by the addition of
+ // new child ext elements ([ISO/IEC29500-1:2016] section 18.2.7)
ExtURIConditionalFormattings = "{78C0D931-6437-407D-A8EE-F0AAD7539E65}"
ExtURIDataValidations = "{CCE6A557-97BC-4B89-ADB6-D9C93CAAB3DF}"
ExtURISparklineGroups = "{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"
diff --git a/xmlPivotCache.go b/xmlPivotCache.go
index a4b0711..45b48de 100644
--- a/xmlPivotCache.go
+++ b/xmlPivotCache.go
@@ -48,7 +48,7 @@ type xlsxPivotCacheDefinition struct {
// PivotTable.
type xlsxCacheSource struct {
Type string `xml:"type,attr"`
- ConnectionId int `xml:"connectionId,attr,omitempty"`
+ ConnectionID int `xml:"connectionId,attr,omitempty"`
WorksheetSource *xlsxWorksheetSource `xml:"worksheetSource"`
Consolidation *xlsxConsolidation `xml:"consolidation"`
ExtLst *xlsxExtLst `xml:"extLst"`
@@ -89,7 +89,7 @@ type xlsxCacheField struct {
PropertyName string `xml:"propertyName,attr,omitempty"`
ServerField bool `xml:"serverField,attr,omitempty"`
UniqueList bool `xml:"uniqueList,attr,omitempty"`
- NumFmtId int `xml:"numFmtId,attr"`
+ NumFmtID int `xml:"numFmtId,attr"`
Formula string `xml:"formula,attr,omitempty"`
SQLType int `xml:"sqlType,attr,omitempty"`
Hierarchy int `xml:"hierarchy,attr,omitempty"`
diff --git a/xmlPivotTable.go b/xmlPivotTable.go
index 3738ed8..0549c5e 100644
--- a/xmlPivotTable.go
+++ b/xmlPivotTable.go
@@ -125,7 +125,7 @@ type xlsxPivotField struct {
UniqueMemberProperty string `xml:"uniqueMemberProperty,attr,omitempty"`
Compact bool `xml:"compact,attr"`
AllDrilled bool `xml:"allDrilled,attr,omitempty"`
- NumFmtId string `xml:"numFmtId,attr,omitempty"`
+ NumFmtID string `xml:"numFmtId,attr,omitempty"`
Outline bool `xml:"outline,attr"`
SubtotalTop bool `xml:"subtotalTop,attr,omitempty"`
DragToRow bool `xml:"dragToRow,attr,omitempty"`
@@ -273,7 +273,7 @@ type xlsxDataField struct {
ShowDataAs string `xml:"showDataAs,attr,omitempty"`
BaseField int `xml:"baseField,attr,omitempty"`
BaseItem int64 `xml:"baseItem,attr,omitempty"`
- NumFmtId string `xml:"numFmtId,attr,omitempty"`
+ NumFmtID string `xml:"numFmtId,attr,omitempty"`
ExtLst *xlsxExtLst `xml:"extLst"`
}
diff --git a/xmlTable.go b/xmlTable.go
index ca4ce03..017bda1 100644
--- a/xmlTable.go
+++ b/xmlTable.go
@@ -44,6 +44,7 @@ type xlsxTable struct {
// applied column by column to a table of data in the worksheet. This collection
// expresses AutoFilter settings.
type xlsxAutoFilter struct {
+ XMLName xml.Name `xml:"autoFilter"`
Ref string `xml:"ref,attr"`
FilterColumn *xlsxFilterColumn `xml:"filterColumn"`
}
diff --git a/xmlWorkbook.go b/xmlWorkbook.go
index 765563b..e9ded6c 100644
--- a/xmlWorkbook.go
+++ b/xmlWorkbook.go
@@ -33,7 +33,7 @@ type xlsxWorkbook struct {
FileVersion *xlsxFileVersion `xml:"fileVersion"`
WorkbookPr *xlsxWorkbookPr `xml:"workbookPr"`
WorkbookProtection *xlsxWorkbookProtection `xml:"workbookProtection"`
- BookViews xlsxBookViews `xml:"bookViews"`
+ BookViews *xlsxBookViews `xml:"bookViews"`
Sheets xlsxSheets `xml:"sheets"`
ExternalReferences *xlsxExternalReferences `xml:"externalReferences"`
DefinedNames *xlsxDefinedNames `xml:"definedNames"`
diff --git a/xmlWorksheet.go b/xmlWorksheet.go
index c78d3ef..b785eac 100644
--- a/xmlWorksheet.go
+++ b/xmlWorksheet.go
@@ -17,18 +17,23 @@ import "encoding/xml"
type xlsxWorksheet struct {
XMLName xml.Name `xml:"http://schemas.openxmlformats.org/spreadsheetml/2006/main worksheet"`
SheetPr *xlsxSheetPr `xml:"sheetPr"`
- Dimension xlsxDimension `xml:"dimension"`
- SheetViews xlsxSheetViews `xml:"sheetViews,omitempty"`
+ Dimension *xlsxDimension `xml:"dimension"`
+ SheetViews xlsxSheetViews `xml:"sheetViews"`
SheetFormatPr *xlsxSheetFormatPr `xml:"sheetFormatPr"`
- Cols *xlsxCols `xml:"cols,omitempty"`
+ Cols *xlsxCols `xml:"cols"`
SheetData xlsxSheetData `xml:"sheetData"`
+ SheetCalcPr *xlsxInnerXML `xml:"sheetCalcPr"`
SheetProtection *xlsxSheetProtection `xml:"sheetProtection"`
+ ProtectedRanges *xlsxInnerXML `xml:"protectedRanges"`
+ Scenarios *xlsxInnerXML `xml:"scenarios"`
AutoFilter *xlsxAutoFilter `xml:"autoFilter"`
+ SortState *xlsxInnerXML `xml:"sortState"`
+ DataConsolidate *xlsxInnerXML `xml:"dataConsolidate"`
CustomSheetViews *xlsxCustomSheetViews `xml:"customSheetViews"`
MergeCells *xlsxMergeCells `xml:"mergeCells"`
PhoneticPr *xlsxPhoneticPr `xml:"phoneticPr"`
ConditionalFormatting []*xlsxConditionalFormatting `xml:"conditionalFormatting"`
- DataValidations *xlsxDataValidations `xml:"dataValidations,omitempty"`
+ DataValidations *xlsxDataValidations `xml:"dataValidations"`
Hyperlinks *xlsxHyperlinks `xml:"hyperlinks"`
PrintOptions *xlsxPrintOptions `xml:"printOptions"`
PageMargins *xlsxPageMargins `xml:"pageMargins"`
@@ -36,16 +41,26 @@ type xlsxWorksheet struct {
HeaderFooter *xlsxHeaderFooter `xml:"headerFooter"`
RowBreaks *xlsxBreaks `xml:"rowBreaks"`
ColBreaks *xlsxBreaks `xml:"colBreaks"`
+ CustomProperties *xlsxInnerXML `xml:"customProperties"`
+ CellWatches *xlsxInnerXML `xml:"cellWatches"`
+ IgnoredErrors *xlsxInnerXML `xml:"ignoredErrors"`
+ SmartTags *xlsxInnerXML `xml:"smartTags"`
Drawing *xlsxDrawing `xml:"drawing"`
LegacyDrawing *xlsxLegacyDrawing `xml:"legacyDrawing"`
+ LegacyDrawingHF *xlsxInnerXML `xml:"legacyDrawingHF"`
+ DrawingHF *xlsxDrawingHF `xml:"drawingHF"`
Picture *xlsxPicture `xml:"picture"`
+ OleObjects *xlsxInnerXML `xml:"oleObjects"`
+ Controls *xlsxInnerXML `xml:"controls"`
+ WebPublishItems *xlsxInnerXML `xml:"webPublishItems"`
TableParts *xlsxTableParts `xml:"tableParts"`
ExtLst *xlsxExtLst `xml:"extLst"`
}
// xlsxDrawing change r:id to rid in the namespace.
type xlsxDrawing struct {
- RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`
+ XMLName xml.Name `xml:"drawing"`
+ RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`
}
// xlsxHeaderFooter directly maps the headerFooter element in the namespace
@@ -56,6 +71,7 @@ type xlsxDrawing struct {
// footers on the first page can differ from those on odd- and even-numbered
// pages. In the latter case, the first page is not considered an odd page.
type xlsxHeaderFooter struct {
+ XMLName xml.Name `xml:"headerFooter"`
AlignWithMargins bool `xml:"alignWithMargins,attr,omitempty"`
DifferentFirst bool `xml:"differentFirst,attr,omitempty"`
DifferentOddEven bool `xml:"differentOddEven,attr,omitempty"`
@@ -77,32 +93,33 @@ type xlsxHeaderFooter struct {
// each of the left section, center section and right section of a header and
// a footer.
type xlsxDrawingHF struct {
- Content string `xml:",chardata"`
+ Content string `xml:",innerxml"`
}
// xlsxPageSetUp directly maps the pageSetup element in the namespace
// http://schemas.openxmlformats.org/spreadsheetml/2006/main - Page setup
// settings for the worksheet.
type xlsxPageSetUp struct {
- BlackAndWhite bool `xml:"blackAndWhite,attr,omitempty"`
- CellComments string `xml:"cellComments,attr,omitempty"`
- Copies int `xml:"copies,attr,omitempty"`
- Draft bool `xml:"draft,attr,omitempty"`
- Errors string `xml:"errors,attr,omitempty"`
- FirstPageNumber int `xml:"firstPageNumber,attr,omitempty"`
- FitToHeight int `xml:"fitToHeight,attr,omitempty"`
- FitToWidth int `xml:"fitToWidth,attr,omitempty"`
- HorizontalDPI float32 `xml:"horizontalDpi,attr,omitempty"`
- RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`
- Orientation string `xml:"orientation,attr,omitempty"`
- PageOrder string `xml:"pageOrder,attr,omitempty"`
- PaperHeight string `xml:"paperHeight,attr,omitempty"`
- PaperSize int `xml:"paperSize,attr,omitempty"`
- PaperWidth string `xml:"paperWidth,attr,omitempty"`
- Scale int `xml:"scale,attr,omitempty"`
- UseFirstPageNumber bool `xml:"useFirstPageNumber,attr,omitempty"`
- UsePrinterDefaults bool `xml:"usePrinterDefaults,attr,omitempty"`
- VerticalDPI float32 `xml:"verticalDpi,attr,omitempty"`
+ XMLName xml.Name `xml:"pageSetup"`
+ BlackAndWhite bool `xml:"blackAndWhite,attr,omitempty"`
+ CellComments string `xml:"cellComments,attr,omitempty"`
+ Copies int `xml:"copies,attr,omitempty"`
+ Draft bool `xml:"draft,attr,omitempty"`
+ Errors string `xml:"errors,attr,omitempty"`
+ FirstPageNumber int `xml:"firstPageNumber,attr,omitempty"`
+ FitToHeight int `xml:"fitToHeight,attr,omitempty"`
+ FitToWidth int `xml:"fitToWidth,attr,omitempty"`
+ HorizontalDPI int `xml:"horizontalDpi,attr,omitempty"`
+ RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`
+ Orientation string `xml:"orientation,attr,omitempty"`
+ PageOrder string `xml:"pageOrder,attr,omitempty"`
+ PaperHeight string `xml:"paperHeight,attr,omitempty"`
+ PaperSize int `xml:"paperSize,attr,omitempty"`
+ PaperWidth string `xml:"paperWidth,attr,omitempty"`
+ Scale int `xml:"scale,attr,omitempty"`
+ UseFirstPageNumber bool `xml:"useFirstPageNumber,attr,omitempty"`
+ UsePrinterDefaults bool `xml:"usePrinterDefaults,attr,omitempty"`
+ VerticalDPI int `xml:"verticalDpi,attr,omitempty"`
}
// xlsxPrintOptions directly maps the printOptions element in the namespace
@@ -110,44 +127,48 @@ type xlsxPageSetUp struct {
// the sheet. Printer-specific settings are stored separately in the Printer
// Settings part.
type xlsxPrintOptions struct {
- GridLines bool `xml:"gridLines,attr,omitempty"`
- GridLinesSet bool `xml:"gridLinesSet,attr,omitempty"`
- Headings bool `xml:"headings,attr,omitempty"`
- HorizontalCentered bool `xml:"horizontalCentered,attr,omitempty"`
- VerticalCentered bool `xml:"verticalCentered,attr,omitempty"`
+ XMLName xml.Name `xml:"printOptions"`
+ GridLines bool `xml:"gridLines,attr,omitempty"`
+ GridLinesSet bool `xml:"gridLinesSet,attr,omitempty"`
+ Headings bool `xml:"headings,attr,omitempty"`
+ HorizontalCentered bool `xml:"horizontalCentered,attr,omitempty"`
+ VerticalCentered bool `xml:"verticalCentered,attr,omitempty"`
}
// xlsxPageMargins directly maps the pageMargins element in the namespace
// http://schemas.openxmlformats.org/spreadsheetml/2006/main - Page margins for
// a sheet or a custom sheet view.
type xlsxPageMargins struct {
- Bottom float64 `xml:"bottom,attr"`
- Footer float64 `xml:"footer,attr"`
- Header float64 `xml:"header,attr"`
- Left float64 `xml:"left,attr"`
- Right float64 `xml:"right,attr"`
- Top float64 `xml:"top,attr"`
+ XMLName xml.Name `xml:"pageMargins"`
+ Bottom float64 `xml:"bottom,attr"`
+ Footer float64 `xml:"footer,attr"`
+ Header float64 `xml:"header,attr"`
+ Left float64 `xml:"left,attr"`
+ Right float64 `xml:"right,attr"`
+ Top float64 `xml:"top,attr"`
}
// xlsxSheetFormatPr directly maps the sheetFormatPr element in the namespace
// http://schemas.openxmlformats.org/spreadsheetml/2006/main. This element
// specifies the sheet formatting properties.
type xlsxSheetFormatPr struct {
- BaseColWidth uint8 `xml:"baseColWidth,attr,omitempty"`
- DefaultColWidth float64 `xml:"defaultColWidth,attr,omitempty"`
- DefaultRowHeight float64 `xml:"defaultRowHeight,attr"`
- CustomHeight bool `xml:"customHeight,attr,omitempty"`
- ZeroHeight bool `xml:"zeroHeight,attr,omitempty"`
- ThickTop bool `xml:"thickTop,attr,omitempty"`
- ThickBottom bool `xml:"thickBottom,attr,omitempty"`
- OutlineLevelRow uint8 `xml:"outlineLevelRow,attr,omitempty"`
- OutlineLevelCol uint8 `xml:"outlineLevelCol,attr,omitempty"`
+ XMLName xml.Name `xml:"sheetFormatPr"`
+ BaseColWidth uint8 `xml:"baseColWidth,attr,omitempty"`
+ DefaultColWidth float64 `xml:"defaultColWidth,attr,omitempty"`
+ DefaultRowHeight float64 `xml:"defaultRowHeight,attr"`
+ CustomHeight bool `xml:"customHeight,attr,omitempty"`
+ ZeroHeight bool `xml:"zeroHeight,attr,omitempty"`
+ ThickTop bool `xml:"thickTop,attr,omitempty"`
+ ThickBottom bool `xml:"thickBottom,attr,omitempty"`
+ OutlineLevelRow uint8 `xml:"outlineLevelRow,attr,omitempty"`
+ OutlineLevelCol uint8 `xml:"outlineLevelCol,attr,omitempty"`
}
// xlsxSheetViews directly maps the sheetViews element in the namespace
// http://schemas.openxmlformats.org/spreadsheetml/2006/main - Worksheet views
// collection.
type xlsxSheetViews struct {
+ XMLName xml.Name `xml:"sheetViews"`
SheetView []xlsxSheetView `xml:"sheetView"`
}
@@ -249,7 +270,8 @@ type xlsxTabColor struct {
// http://schemas.openxmlformats.org/spreadsheetml/2006/main - currently I have
// not checked it for completeness - it does as much as I need.
type xlsxCols struct {
- Col []xlsxCol `xml:"col"`
+ XMLName xml.Name `xml:"cols"`
+ Col []xlsxCol `xml:"col"`
}
// xlsxCol directly maps the col (Column Width & Formatting). Defines column
@@ -275,7 +297,8 @@ type xlsxCol struct {
// When an entire column is formatted, only the first cell in that column is
// considered used.
type xlsxDimension struct {
- Ref string `xml:"ref,attr"`
+ XMLName xml.Name `xml:"dimension"`
+ Ref string `xml:"ref,attr"`
}
// xlsxSheetData directly maps the sheetData element in the namespace
@@ -308,6 +331,7 @@ type xlsxRow struct {
// xlsxCustomSheetViews directly maps the customSheetViews element. This is a
// collection of custom sheet views.
type xlsxCustomSheetViews struct {
+ XMLName xml.Name `xml:"customSheetViews"`
CustomSheetView []*xlsxCustomSheetView `xml:"customSheetView"`
}
@@ -370,13 +394,15 @@ type xlsxMergeCell struct {
// xlsxMergeCells directly maps the mergeCells element. This collection
// expresses all the merged cells in the sheet.
type xlsxMergeCells struct {
- Count int `xml:"count,attr,omitempty"`
- Cells []*xlsxMergeCell `xml:"mergeCell,omitempty"`
+ XMLName xml.Name `xml:"mergeCells"`
+ Count int `xml:"count,attr,omitempty"`
+ Cells []*xlsxMergeCell `xml:"mergeCell,omitempty"`
}
// xlsxDataValidations expresses all data validation information for cells in a
// sheet which have data validation features applied.
type xlsxDataValidations struct {
+ XMLName xml.Name `xml:"dataValidations"`
Count int `xml:"count,attr,omitempty"`
DisablePrompts bool `xml:"disablePrompts,attr,omitempty"`
XWindow int `xml:"xWindow,attr,omitempty"`
@@ -420,14 +446,19 @@ type DataValidation struct {
// str (String) | Cell containing a formula string.
//
type xlsxC struct {
- R string `xml:"r,attr"` // Cell ID, e.g. A1
- S int `xml:"s,attr,omitempty"` // Style reference.
- // Str string `xml:"str,attr,omitempty"` // Style reference.
- T string `xml:"t,attr,omitempty"` // Type.
- F *xlsxF `xml:"f,omitempty"` // Formula
- V string `xml:"v,omitempty"` // Value
- IS *xlsxSI `xml:"is"`
+ XMLName xml.Name `xml:"c"`
XMLSpace xml.Attr `xml:"space,attr,omitempty"`
+ R string `xml:"r,attr"` // Cell ID, e.g. A1
+ S int `xml:"s,attr,omitempty"` // Style reference.
+ // Str string `xml:"str,attr,omitempty"` // Style reference.
+ T string `xml:"t,attr,omitempty"` // Type.
+ F *xlsxF `xml:"f,omitempty"` // Formula
+ V string `xml:"v,omitempty"` // Value
+ IS *xlsxSI `xml:"is"`
+}
+
+func (c *xlsxC) hasValue() bool {
+ return c.S != 0 || c.V != "" || c.F != nil || c.T != ""
}
// xlsxF directly maps the f element in the namespace
@@ -443,27 +474,28 @@ type xlsxF struct {
// xlsxSheetProtection collection expresses the sheet protection options to
// enforce when the sheet is protected.
type xlsxSheetProtection struct {
- AlgorithmName string `xml:"algorithmName,attr,omitempty"`
- Password string `xml:"password,attr,omitempty"`
- HashValue string `xml:"hashValue,attr,omitempty"`
- SaltValue string `xml:"saltValue,attr,omitempty"`
- SpinCount int `xml:"spinCount,attr,omitempty"`
- Sheet bool `xml:"sheet,attr"`
- Objects bool `xml:"objects,attr"`
- Scenarios bool `xml:"scenarios,attr"`
- FormatCells bool `xml:"formatCells,attr"`
- FormatColumns bool `xml:"formatColumns,attr"`
- FormatRows bool `xml:"formatRows,attr"`
- InsertColumns bool `xml:"insertColumns,attr"`
- InsertRows bool `xml:"insertRows,attr"`
- InsertHyperlinks bool `xml:"insertHyperlinks,attr"`
- DeleteColumns bool `xml:"deleteColumns,attr"`
- DeleteRows bool `xml:"deleteRows,attr"`
- SelectLockedCells bool `xml:"selectLockedCells,attr"`
- Sort bool `xml:"sort,attr"`
- AutoFilter bool `xml:"autoFilter,attr"`
- PivotTables bool `xml:"pivotTables,attr"`
- SelectUnlockedCells bool `xml:"selectUnlockedCells,attr"`
+ XMLName xml.Name `xml:"sheetProtection"`
+ AlgorithmName string `xml:"algorithmName,attr,omitempty"`
+ Password string `xml:"password,attr,omitempty"`
+ HashValue string `xml:"hashValue,attr,omitempty"`
+ SaltValue string `xml:"saltValue,attr,omitempty"`
+ SpinCount int `xml:"spinCount,attr,omitempty"`
+ Sheet bool `xml:"sheet,attr"`
+ Objects bool `xml:"objects,attr"`
+ Scenarios bool `xml:"scenarios,attr"`
+ FormatCells bool `xml:"formatCells,attr"`
+ FormatColumns bool `xml:"formatColumns,attr"`
+ FormatRows bool `xml:"formatRows,attr"`
+ InsertColumns bool `xml:"insertColumns,attr"`
+ InsertRows bool `xml:"insertRows,attr"`
+ InsertHyperlinks bool `xml:"insertHyperlinks,attr"`
+ DeleteColumns bool `xml:"deleteColumns,attr"`
+ DeleteRows bool `xml:"deleteRows,attr"`
+ SelectLockedCells bool `xml:"selectLockedCells,attr"`
+ Sort bool `xml:"sort,attr"`
+ AutoFilter bool `xml:"autoFilter,attr"`
+ PivotTables bool `xml:"pivotTables,attr"`
+ SelectUnlockedCells bool `xml:"selectUnlockedCells,attr"`
}
// xlsxPhoneticPr (Phonetic Properties) represents a collection of phonetic
@@ -474,9 +506,10 @@ type xlsxSheetProtection struct {
// every phonetic hint is expressed as a phonetic run (rPh), and these
// properties specify how to display that phonetic run.
type xlsxPhoneticPr struct {
- Alignment string `xml:"alignment,attr,omitempty"`
- FontID *int `xml:"fontId,attr"`
- Type string `xml:"type,attr,omitempty"`
+ XMLName xml.Name `xml:"phoneticPr"`
+ Alignment string `xml:"alignment,attr,omitempty"`
+ FontID *int `xml:"fontId,attr"`
+ Type string `xml:"type,attr,omitempty"`
}
// A Conditional Format is a format, such as cell shading or font color, that a
@@ -484,8 +517,9 @@ type xlsxPhoneticPr struct {
// condition is true. This collection expresses conditional formatting rules
// applied to a particular cell or range.
type xlsxConditionalFormatting struct {
- SQRef string `xml:"sqref,attr,omitempty"`
- CfRule []*xlsxCfRule `xml:"cfRule"`
+ XMLName xml.Name `xml:"conditionalFormatting"`
+ SQRef string `xml:"sqref,attr,omitempty"`
+ CfRule []*xlsxCfRule `xml:"cfRule"`
}
// xlsxCfRule (Conditional Formatting Rule) represents a description of a
@@ -550,6 +584,7 @@ type xlsxCfvo struct {
// be stored in a package as a relationship. Hyperlinks shall be identified by
// containing a target which specifies the destination of the given hyperlink.
type xlsxHyperlinks struct {
+ XMLName xml.Name `xml:"hyperlinks"`
Hyperlink []xlsxHyperlink `xml:"hyperlink"`
}
@@ -594,6 +629,7 @@ type xlsxHyperlink struct {
// </worksheet>
//
type xlsxTableParts struct {
+ XMLName xml.Name `xml:"tableParts"`
Count int `xml:"count,attr,omitempty"`
TableParts []*xlsxTablePart `xml:"tablePart"`
}
@@ -611,7 +647,8 @@ type xlsxTablePart struct {
// <picture r:id="rId1"/>
//
type xlsxPicture struct {
- RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`
+ XMLName xml.Name `xml:"picture"`
+ RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`
}
// xlsxLegacyDrawing directly maps the legacyDrawing element in the namespace
@@ -624,7 +661,12 @@ type xlsxPicture struct {
// can also be used to explain assumptions made in a formula or to call out
// something special about the cell.
type xlsxLegacyDrawing struct {
- RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`
+ XMLName xml.Name `xml:"legacyDrawing"`
+ RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`
+}
+
+type xlsxInnerXML struct {
+ Content string `xml:",innerxml"`
}
// xlsxWorksheetExt directly maps the ext element in the worksheet.
@@ -801,3 +843,13 @@ type FormatHeaderFooter struct {
FirstFooter string
FirstHeader string
}
+
+// FormatPageMargins directly maps the settings of page margins
+type FormatPageMargins struct {
+ Bottom string
+ Footer string
+ Header string
+ Left string
+ Right string
+ Top string
+}
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 09:57:01 +0000