305 changes: 265 additions & 40 deletions lib.go
View file

Large diffs are not rendered by default.

119 changes: 109 additions & 10 deletions lib_test.go
View file
Original file line number Diff line number Diff line change
@@ -1,13 +1,19 @@
package excelize

import (
"archive/zip"
"bytes"
"encoding/xml"
"fmt"
"io"
"os"
"strconv"
"strings"
"sync"
"testing"

"github.com/stretchr/testify/assert"
"github.com/stretchr/testify/require"
)

var validColumns = []struct {
Expand Down Expand Up @@ -178,7 +184,7 @@ func TestCellNameToCoordinates_Error(t *testing.T) {
}
}
_, _, err := CellNameToCoordinates("A1048577")
assert.EqualError(t, err, "row number exceeds maximum limit")
assert.EqualError(t, err, ErrMaxRows.Error())
}

func TestCoordinatesToCellName_OK(t *testing.T) {
Expand Down Expand Up @@ -211,11 +217,66 @@ func TestCoordinatesToCellName_Error(t *testing.T) {
}
}

func TestCoordinatesToAreaRef(t *testing.T) {
f := NewFile()
_, err := f.coordinatesToAreaRef([]int{})
assert.EqualError(t, err, ErrCoordinates.Error())
_, err = f.coordinatesToAreaRef([]int{1, -1, 1, 1})
assert.EqualError(t, err, "invalid cell coordinates [1, -1]")
_, err = f.coordinatesToAreaRef([]int{1, 1, 1, -1})
assert.EqualError(t, err, "invalid cell coordinates [1, -1]")
ref, err := f.coordinatesToAreaRef([]int{1, 1, 1, 1})
assert.NoError(t, err)
assert.EqualValues(t, ref, "A1:A1")
}

func TestSortCoordinates(t *testing.T) {
assert.EqualError(t, sortCoordinates(make([]int, 3)), ErrCoordinates.Error())
}

func TestInStrSlice(t *testing.T) {
assert.EqualValues(t, -1, inStrSlice([]string{}, ""))
}

func TestBoolValMarshal(t *testing.T) {
bold := true
node := &xlsxFont{B: &attrValBool{Val: &bold}}
data, err := xml.Marshal(node)
assert.NoError(t, err)
assert.Equal(t, `<xlsxFont><b val="1"></b></xlsxFont>`, string(data))

node = &xlsxFont{}
err = xml.Unmarshal(data, node)
assert.NoError(t, err)
assert.NotEqual(t, nil, node)
assert.NotEqual(t, nil, node.B)
assert.NotEqual(t, nil, node.B.Val)
assert.Equal(t, true, *node.B.Val)
}

func TestBoolValUnmarshalXML(t *testing.T) {
node := xlsxFont{}
assert.NoError(t, xml.Unmarshal([]byte("<xlsxFont><b val=\"\"></b></xlsxFont>"), &node))
assert.Equal(t, true, *node.B.Val)
for content, err := range map[string]string{
"<xlsxFont><b val=\"0\"><i></i></b></xlsxFont>": "unexpected child of attrValBool",
"<xlsxFont><b val=\"x\"></b></xlsxFont>": "strconv.ParseBool: parsing \"x\": invalid syntax",
} {
assert.EqualError(t, xml.Unmarshal([]byte(content), &node), err)
}
attr := attrValBool{}
assert.EqualError(t, attr.UnmarshalXML(xml.NewDecoder(strings.NewReader("")), xml.StartElement{}), io.EOF.Error())
}

func TestBytesReplace(t *testing.T) {
s := []byte{0x01}
assert.EqualValues(t, s, bytesReplace(s, []byte{}, []byte{}, 0))
}

func TestGetRootElement(t *testing.T) {
assert.Equal(t, 0, len(getRootElement(xml.NewDecoder(strings.NewReader("")))))
}

func TestSetIgnorableNameSpace(t *testing.T) {
f := NewFile()
f.xmlAttr["xml_path"] = []xml.Attr{{}}
Expand All @@ -238,21 +299,21 @@ func TestGenXMLNamespace(t *testing.T) {
func TestBstrUnmarshal(t *testing.T) {
bstrs := map[string]string{
"*": "*",
"*_x0000_": "*",
"*_x0008_": "*",
"_x0008_*": "*",
"*_x0008_*": "**",
"*_x0000_": "*\x00",
"*_x0008_": "*\b",
"_x0008_*": "\b*",
"*_x0008_*": "*\b*",
"*_x4F60__x597D_": "*你好",
"*_xG000_": "*_xG000_",
"*_xG05F_x0001_*": "*_xG05F*",
"*_x005F__x0008_*": "*_x005F_*",
"*_x005F__x0008_*": "*_\b*",
"*_x005F_x0001_*": "*_x0001_*",
"*_x005f_x005F__x0008_*": "*_x005F_*",
"*_x005f_x005F__x0008_*": "*_x005F_\b*",
"*_x005F_x005F_xG05F_x0006_*": "*_x005F_xG05F*",
"*_x005F_x005F_x005F_x0006_*": "*_x005F_x0006_*",
"_x005F__x0008_******": "_x005F_******",
"******_x005F__x0008_": "******_x005F_",
"******_x005F__x0008_******": "******_x005F_******",
"_x005F__x0008_******": "_\b******",
"******_x005F__x0008_": "******_\b",
"******_x005F__x0008_******": "******_\b******",
}
for bstr, expected := range bstrs {
assert.Equal(t, expected, bstrUnmarshal(bstr))
Expand All @@ -271,3 +332,41 @@ func TestBstrMarshal(t *testing.T) {
assert.Equal(t, expected, bstrMarshal(bstr))
}
}

func TestReadBytes(t *testing.T) {
f := &File{tempFiles: sync.Map{}}
sheet := "xl/worksheets/sheet1.xml"
f.tempFiles.Store(sheet, "/d/")
assert.Equal(t, []byte{}, f.readBytes(sheet))
}

func TestUnzipToTemp(t *testing.T) {
os.Setenv("TMPDIR", "test")
defer os.Unsetenv("TMPDIR")
assert.NoError(t, os.Chmod(os.TempDir(), 0444))
f := NewFile()
data := []byte("PK\x03\x040000000PK\x01\x0200000" +
"0000000000000000000\x00" +
"\x00\x00\x00\x00\x00000000000000PK\x01" +
"\x020000000000000000000" +
"00000\v\x00\x00\x00\x00\x00000000000" +
"00000000000000PK\x01\x0200" +
"00000000000000000000" +
"00\v\x00\x00\x00\x00\x00000000000000" +
"00000000000PK\x01\x020000<" +
"0\x00\x0000000000000000\v\x00\v" +
"\x00\x00\x00\x00\x0000000000\x00\x00\x00\x00000" +
"00000000PK\x01\x0200000000" +
"0000000000000000\v\x00\x00\x00" +
"\x00\x0000PK\x05\x06000000\x05\x000000" +
"\v\x00\x00\x00\x00\x00")
z, err := zip.NewReader(bytes.NewReader(data), int64(len(data)))
assert.NoError(t, err)

_, err = f.unzipToTemp(z.File[0])
require.Error(t, err)
assert.NoError(t, os.Chmod(os.TempDir(), 0755))

_, err = f.unzipToTemp(z.File[0])
assert.EqualError(t, err, "EOF")
}
229 changes: 156 additions & 73 deletions merge.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -11,18 +11,28 @@

package excelize

import (
"fmt"
"strings"
)
import "strings"

// Rect gets merged cell rectangle coordinates sequence.
func (mc *xlsxMergeCell) Rect() ([]int, error) {
var err error
if mc.rect == nil {
mc.rect, err = areaRefToCoordinates(mc.Ref)
}
return mc.rect, err
}

// 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:
// sheet name. Merging cells only keeps the upper-left cell value, and
// discards the other values. 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.
// those merged cells that already exist will be removed. The cell coordinates
// tuple after merging in the following range will be: A1(x3,y1) D1(x2,y1)
// A8(x3,y4) D8(x2,y4)
//
// B1(x1,y1) D1(x2,y1)
// +------------------------+
Expand All @@ -37,65 +47,25 @@ import (
// +------------------------+
//
func (f *File) MergeCell(sheet, hcell, vcell string) error {
rect1, err := f.areaRefToCoordinates(hcell + ":" + vcell)
rect, err := areaRefToCoordinates(hcell + ":" + vcell)
if err != nil {
return err
}
// Correct the coordinate area, such correct C1:B3 to B1:C3.
_ = sortCoordinates(rect1)
_ = sortCoordinates(rect)

hcell, _ = CoordinatesToCellName(rect1[0], rect1[1])
vcell, _ = CoordinatesToCellName(rect1[2], rect1[3])
hcell, _ = CoordinatesToCellName(rect[0], rect[1])
vcell, _ = CoordinatesToCellName(rect[2], rect[3])

ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
ref := hcell + ":" + vcell
if ws.MergeCells != nil {
for i := 0; i < len(ws.MergeCells.Cells); i++ {
cellData := ws.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) {
ws.MergeCells.Cells = append(ws.MergeCells.Cells[:i], ws.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
}
}
ws.MergeCells.Cells = append(ws.MergeCells.Cells, &xlsxMergeCell{Ref: ref})
ws.MergeCells.Cells = append(ws.MergeCells.Cells, &xlsxMergeCell{Ref: ref, rect: rect})
} else {
ws.MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: ref}}}
ws.MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: ref, rect: rect}}}
}
ws.MergeCells.Count = len(ws.MergeCells.Cells)
return err
Expand All @@ -112,7 +82,7 @@ func (f *File) UnmergeCell(sheet string, hcell, vcell string) error {
if err != nil {
return err
}
rect1, err := f.areaRefToCoordinates(hcell + ":" + vcell)
rect1, err := areaRefToCoordinates(hcell + ":" + vcell)
if err != nil {
return err
}
Expand All @@ -124,26 +94,19 @@ func (f *File) UnmergeCell(sheet string, hcell, vcell string) error {
if ws.MergeCells == nil {
return nil
}

if err = f.mergeOverlapCells(ws); err != nil {
return err
}
i := 0
for _, cellData := range ws.MergeCells.Cells {
if cellData == nil {
for _, mergeCell := range ws.MergeCells.Cells {
if mergeCell == 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
}

rect2, _ := areaRefToCoordinates(mergeCell.Ref)
if isOverlap(rect1, rect2) {
continue
}
ws.MergeCells.Cells[i] = cellData
ws.MergeCells.Cells[i] = mergeCell
i++
}
ws.MergeCells.Cells = ws.MergeCells.Cells[:i]
Expand All @@ -163,19 +126,139 @@ func (f *File) GetMergeCells(sheet string) ([]MergeCell, error) {
return mergeCells, err
}
if ws.MergeCells != nil {
if err = f.mergeOverlapCells(ws); err != nil {
return mergeCells, err
}
mergeCells = make([]MergeCell, 0, len(ws.MergeCells.Cells))

for i := range ws.MergeCells.Cells {
ref := ws.MergeCells.Cells[i].Ref
axis := strings.Split(ref, ":")[0]
val, _ := f.GetCellValue(sheet, axis)
mergeCells = append(mergeCells, []string{ref, val})
}
}

return mergeCells, err
}

// overlapRange calculate overlap range of merged cells, and returns max
// column and rows of the range.
func overlapRange(ws *xlsxWorksheet) (row, col int, err error) {
var rect []int
for _, mergeCell := range ws.MergeCells.Cells {
if mergeCell == nil {
continue
}
if rect, err = mergeCell.Rect(); err != nil {
return
}
x1, y1, x2, y2 := rect[0], rect[1], rect[2], rect[3]
if x1 > col {
col = x1
}
if x2 > col {
col = x2
}
if y1 > row {
row = y1
}
if y2 > row {
row = y2
}
}
return
}

// flatMergedCells convert merged cells range reference to cell-matrix.
func flatMergedCells(ws *xlsxWorksheet, matrix [][]*xlsxMergeCell) error {
for i, cell := range ws.MergeCells.Cells {
rect, err := cell.Rect()
if err != nil {
return err
}
x1, y1, x2, y2 := rect[0]-1, rect[1]-1, rect[2]-1, rect[3]-1
var overlapCells []*xlsxMergeCell
for x := x1; x <= x2; x++ {
for y := y1; y <= y2; y++ {
if matrix[x][y] != nil {
overlapCells = append(overlapCells, matrix[x][y])
}
matrix[x][y] = cell
}
}
if len(overlapCells) != 0 {
newCell := cell
for _, overlapCell := range overlapCells {
newCell = mergeCell(cell, overlapCell)
}
newRect, _ := newCell.Rect()
x1, y1, x2, y2 := newRect[0]-1, newRect[1]-1, newRect[2]-1, newRect[3]-1
for x := x1; x <= x2; x++ {
for y := y1; y <= y2; y++ {
matrix[x][y] = newCell
}
}
ws.MergeCells.Cells[i] = newCell
}
}
return nil
}

// mergeOverlapCells merge overlap cells.
func (f *File) mergeOverlapCells(ws *xlsxWorksheet) error {
rows, cols, err := overlapRange(ws)
if err != nil {
return err
}
if rows == 0 || cols == 0 {
return nil
}
matrix := make([][]*xlsxMergeCell, cols)
for i := range matrix {
matrix[i] = make([]*xlsxMergeCell, rows)
}
_ = flatMergedCells(ws, matrix)
mergeCells := ws.MergeCells.Cells[:0]
for _, cell := range ws.MergeCells.Cells {
rect, _ := cell.Rect()
x1, y1, x2, y2 := rect[0]-1, rect[1]-1, rect[2]-1, rect[3]-1
if matrix[x1][y1] == cell {
mergeCells = append(mergeCells, cell)
for x := x1; x <= x2; x++ {
for y := y1; y <= y2; y++ {
matrix[x][y] = nil
}
}
}
}
ws.MergeCells.Count, ws.MergeCells.Cells = len(mergeCells), mergeCells
return nil
}

// mergeCell merge two cells.
func mergeCell(cell1, cell2 *xlsxMergeCell) *xlsxMergeCell {
rect1, _ := cell1.Rect()
rect2, _ := cell2.Rect()

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])
return &xlsxMergeCell{rect: rect1, Ref: hcell + ":" + vcell}
}

// MergeCell define a merged cell data.
// It consists of the following structure.
// example: []string{"D4:E10", "cell value"}
Expand All @@ -186,15 +269,15 @@ func (m *MergeCell) GetCellValue() string {
return (*m)[1]
}

// GetStartAxis returns the merge start axis.
// example: "C2"
// GetStartAxis returns the top left cell coordinates of merged range, for
// example: "C2".
func (m *MergeCell) GetStartAxis() string {
axis := strings.Split((*m)[0], ":")
return axis[0]
}

// GetEndAxis returns the merge end axis.
// example: "D4"
// GetEndAxis returns the bottom right cell coordinates of merged range, for
// example: "D4".
func (m *MergeCell) GetEndAxis() string {
axis := strings.Split((*m)[0], ":")
return axis[1]
Expand Down
48 changes: 32 additions & 16 deletions merge_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -12,7 +12,7 @@ func TestMergeCell(t *testing.T) {
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.EqualError(t, f.MergeCell("Sheet1", "A", "B"), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
assert.NoError(t, f.MergeCell("Sheet1", "D9", "D9"))
assert.NoError(t, f.MergeCell("Sheet1", "D9", "E9"))
assert.NoError(t, f.MergeCell("Sheet1", "H14", "G13"))
Expand All @@ -27,7 +27,7 @@ func TestMergeCell(t *testing.T) {
assert.NoError(t, f.SetCellHyperLink("Sheet1", "J11", "https://github.com/xuri/excelize", "External"))
assert.NoError(t, f.SetCellFormula("Sheet1", "G12", "SUM(Sheet1!B19,Sheet1!C19)"))
value, err := f.GetCellValue("Sheet1", "H11")
assert.Equal(t, "0.5", value)
assert.Equal(t, "100", value)
assert.NoError(t, err)
value, err = f.GetCellValue("Sheet2", "A6") // Merged cell ref is single coordinate.
assert.Equal(t, "", value)
Expand Down Expand Up @@ -68,23 +68,33 @@ func TestMergeCell(t *testing.T) {
assert.EqualError(t, f.MergeCell("SheetN", "N10", "O11"), "sheet SheetN is not exist")

assert.NoError(t, f.SaveAs(filepath.Join("test", "TestMergeCell.xlsx")))
assert.NoError(t, f.Close())

f = NewFile()
assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
ws, ok := f.Sheet.Load("xl/worksheets/sheet1.xml")
assert.True(t, ok)
ws.(*xlsxWorksheet).MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{nil, nil}}
assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
}

ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
assert.True(t, ok)
ws.(*xlsxWorksheet).MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}
assert.EqualError(t, f.MergeCell("Sheet1", "A2", "B3"), `invalid area "A1"`)
func TestMergeCellOverlap(t *testing.T) {
f := NewFile()
assert.NoError(t, f.MergeCell("Sheet1", "A1", "C2"))
assert.NoError(t, f.MergeCell("Sheet1", "B2", "D3"))
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestMergeCellOverlap.xlsx")))

ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
assert.True(t, ok)
ws.(*xlsxWorksheet).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"`)
f, err := OpenFile(filepath.Join("test", "TestMergeCellOverlap.xlsx"))
if !assert.NoError(t, err) {
t.FailNow()
}
mc, err := f.GetMergeCells("Sheet1")
assert.NoError(t, err)
assert.Equal(t, 1, len(mc))
assert.Equal(t, "A1", mc[0].GetStartAxis())
assert.Equal(t, "D3", mc[0].GetEndAxis())
assert.Equal(t, "", mc[0].GetCellValue())
assert.NoError(t, f.Close())
}

func TestGetMergeCells(t *testing.T) {
Expand Down Expand Up @@ -131,6 +141,7 @@ func TestGetMergeCells(t *testing.T) {
// Test get merged cells on not exists worksheet.
_, err = f.GetMergeCells("SheetN")
assert.EqualError(t, err, "sheet SheetN is not exist")
assert.NoError(t, f.Close())
}

func TestUnmergeCell(t *testing.T) {
Expand All @@ -140,20 +151,21 @@ func TestUnmergeCell(t *testing.T) {
}
sheet1 := f.GetSheetName(0)

xlsx, err := f.workSheetReader(sheet1)
sheet, err := f.workSheetReader(sheet1)
assert.NoError(t, err)

mergeCellNum := len(xlsx.MergeCells.Cells)
mergeCellNum := len(sheet.MergeCells.Cells)

assert.EqualError(t, f.UnmergeCell("Sheet1", "A", "A"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, f.UnmergeCell("Sheet1", "A", "A"), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())

// unmerge the mergecell that contains A1
assert.NoError(t, f.UnmergeCell(sheet1, "A1", "A1"))
if len(xlsx.MergeCells.Cells) != mergeCellNum-1 {
if len(sheet.MergeCells.Cells) != mergeCellNum-1 {
t.FailNow()
}

assert.NoError(t, f.SaveAs(filepath.Join("test", "TestUnmergeCell.xlsx")))
assert.NoError(t, f.Close())

f = NewFile()
assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
Expand All @@ -173,11 +185,15 @@ func TestUnmergeCell(t *testing.T) {
ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
assert.True(t, ok)
ws.(*xlsxWorksheet).MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}
assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), `invalid area "A1"`)
assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), ErrParameterInvalid.Error())

ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
assert.True(t, ok)
ws.(*xlsxWorksheet).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"`)
assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
}

func TestFlatMergedCells(t *testing.T) {
ws := &xlsxWorksheet{MergeCells: &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}}
assert.EqualError(t, flatMergedCells(ws, [][]*xlsxMergeCell{}), ErrParameterInvalid.Error())
}
82 changes: 57 additions & 25 deletions picture.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -76,14 +76,42 @@ func parseFormatPictureSet(formatSet string) (*formatPicture, error) {
// }
// }
//
// LinkType defines two types of hyperlink "External" for web site or
// "Location" for moving to one of cell in this workbook. When the
// "hyperlink_type" is "Location", coordinates need to start with "#".
// The optional parameter "autofit" specifies if make image size auto fits the
// cell, the default value of that is 'false'.
//
// The optional parameter "hyperlink" specifies the hyperlink of the image.
//
// The optional parameter "hyperlink_type" defines two types of
// hyperlink "External" for website or "Location" for moving to one of the
// cells in this workbook. When the "hyperlink_type" is "Location",
// coordinates need to start with "#".
//
// The optional parameter "positioning" defines two types of the position of a
// image in an Excel spreadsheet, "oneCell" (Move but don't size with
// cells) or "absolute" (Don't move or size with cells). If you don't set this
// parameter, the default positioning is move and size with cells.
//
// The optional parameter "print_obj" indicates whether the image is printed
// when the worksheet is printed, the default value of that is 'true'.
//
// The optional parameter "lock_aspect_ratio" indicates whether lock aspect
// ratio for the image, the default value of that is 'false'.
//
// The optional parameter "locked" indicates whether lock the image. Locking
// an object has no effect unless the sheet is protected.
//
// The optional parameter "x_offset" specifies the horizontal offset of the
// image with the cell, the default value of that is 0.
//
// The optional parameter "x_scale" specifies the horizontal scale of images,
// the default value of that is 1.0 which presents 100%.
//
// The optional parameter "y_offset" specifies the vertical offset of the
// image with the cell, the default value of that is 0.
//
// The optional parameter "y_scale" specifies the vertical scale of images,
// the default value of that is 1.0 which presents 100%.
//
// Positioning defines two types of the position of a picture in an Excel
// spreadsheet, "oneCell" (Move but don't size with cells) or "absolute"
// (Don't move or size with cells). If you don't set this parameter, default
// positioning is move and size with cells.
func (f *File) AddPicture(sheet, cell, picture, format string) error {
var err error
// Check picture exists first.
Expand All @@ -94,7 +122,7 @@ func (f *File) AddPicture(sheet, cell, picture, format string) error {
if !ok {
return ErrImgExt
}
file, _ := ioutil.ReadFile(picture)
file, _ := ioutil.ReadFile(filepath.Clean(picture))
_, name := filepath.Split(picture)
return f.AddPictureFromBytes(sheet, cell, format, name, ext, file)
}
Expand Down Expand Up @@ -148,6 +176,7 @@ func (f *File) AddPictureFromBytes(sheet, cell, format, name, extension string,
if err != nil {
return err
}
ws.Lock()
// Add first picture for given sheet, create xl/drawings/ and xl/drawings/_rels/ folder.
drawingID := f.countDrawings() + 1
drawingXML := "xl/drawings/drawing" + strconv.Itoa(drawingID) + ".xml"
Expand All @@ -162,6 +191,7 @@ func (f *File) AddPictureFromBytes(sheet, cell, format, name, extension string,
}
drawingHyperlinkRID = f.addRels(drawingRels, SourceRelationshipHyperLink, formatSet.Hyperlink, hyperlinkType)
}
ws.Unlock()
err = f.addDrawingPicture(sheet, drawingXML, cell, name, img.Width, img.Height, drawingRID, drawingHyperlinkRID, formatSet)
if err != nil {
return err
Expand Down Expand Up @@ -197,50 +227,46 @@ func (f *File) deleteSheetRelationships(sheet, rID string) {
// addSheetLegacyDrawing provides a function to add legacy drawing element to
// xl/worksheets/sheet%d.xml by given worksheet name and relationship index.
func (f *File) addSheetLegacyDrawing(sheet string, rID int) {
xlsx, _ := f.workSheetReader(sheet)
xlsx.LegacyDrawing = &xlsxLegacyDrawing{
ws, _ := f.workSheetReader(sheet)
ws.LegacyDrawing = &xlsxLegacyDrawing{
RID: "rId" + strconv.Itoa(rID),
}
}

// addSheetDrawing provides a function to add drawing element to
// xl/worksheets/sheet%d.xml by given worksheet name and relationship index.
func (f *File) addSheetDrawing(sheet string, rID int) {
xlsx, _ := f.workSheetReader(sheet)
xlsx.Drawing = &xlsxDrawing{
ws, _ := f.workSheetReader(sheet)
ws.Drawing = &xlsxDrawing{
RID: "rId" + strconv.Itoa(rID),
}
}

// addSheetPicture provides a function to add picture element to
// xl/worksheets/sheet%d.xml by given worksheet name and relationship index.
func (f *File) addSheetPicture(sheet string, rID int) {
xlsx, _ := f.workSheetReader(sheet)
xlsx.Picture = &xlsxPicture{
ws, _ := f.workSheetReader(sheet)
ws.Picture = &xlsxPicture{
RID: "rId" + strconv.Itoa(rID),
}
}

// countDrawings provides a function to get drawing files count storage in the
// folder xl/drawings.
func (f *File) countDrawings() int {
c1, c2 := 0, 0
func (f *File) countDrawings() (count int) {
f.Pkg.Range(func(k, v interface{}) bool {
if strings.Contains(k.(string), "xl/drawings/drawing") {
c1++
count++
}
return true
})
f.Drawings.Range(func(rel, value interface{}) bool {
if strings.Contains(rel.(string), "xl/drawings/drawing") {
c2++
count++
}
return true
})
if c1 < c2 {
return c2
}
return c1
return
}

// addDrawingPicture provides a function to add picture by given sheet,
Expand Down Expand Up @@ -455,14 +481,20 @@ func (f *File) getSheetRelationshipsTargetByID(sheet, rID string) string {
}

// GetPicture provides a function to get picture base name and raw content
// embed in XLSX by given worksheet and cell name. This function returns the
// file name in XLSX and file contents as []byte data types. For example:
// embed in spreadsheet by given worksheet and cell name. This function
// returns the file name in spreadsheet and file contents as []byte data
// types. For example:
//
// f, err := excelize.OpenFile("Book1.xlsx")
// if err != nil {
// fmt.Println(err)
// return
// }
// defer func() {
// if err := f.Close(); err != nil {
// fmt.Println(err)
// }
// }()
// file, raw, err := f.GetPicture("Sheet1", "A2")
// if err != nil {
// fmt.Println(err)
Expand Down Expand Up @@ -622,7 +654,7 @@ func (f *File) drawingsWriter() {
}

// drawingResize calculate the height and width after resizing.
func (f *File) drawingResize(sheet string, cell string, width, height float64, formatSet *formatPicture) (w, h, c, r int, err error) {
func (f *File) drawingResize(sheet, cell string, width, height float64, formatSet *formatPicture) (w, h, c, r int, err error) {
var mergeCells []MergeCell
mergeCells, err = f.GetMergeCells(sheet)
if err != nil {
Expand Down
27 changes: 15 additions & 12 deletions picture_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -60,36 +60,38 @@ func TestAddPicture(t *testing.T) {
// Test add picture to worksheet from bytes.
assert.NoError(t, f.AddPictureFromBytes("Sheet1", "Q1", "", "Excel Logo", ".png", file))
// Test add picture to worksheet from bytes with illegal cell coordinates.
assert.EqualError(t, f.AddPictureFromBytes("Sheet1", "A", "", "Excel Logo", ".png", file), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, f.AddPictureFromBytes("Sheet1", "A", "", "Excel Logo", ".png", file), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())

assert.NoError(t, f.AddPicture("Sheet1", "Q8", filepath.Join("test", "images", "excel.gif"), ""))
assert.NoError(t, f.AddPicture("Sheet1", "Q15", filepath.Join("test", "images", "excel.jpg"), ""))
assert.NoError(t, f.AddPicture("Sheet1", "Q22", filepath.Join("test", "images", "excel.tif"), ""))

// Test write file to given path.
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddPicture.xlsx")))
assert.NoError(t, f.Close())
}

func TestAddPictureErrors(t *testing.T) {
xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
assert.NoError(t, err)

// Test add picture to worksheet with invalid file path.
err = xlsx.AddPicture("Sheet1", "G21", filepath.Join("test", "not_exists_dir", "not_exists.icon"), "")
err = f.AddPicture("Sheet1", "G21", filepath.Join("test", "not_exists_dir", "not_exists.icon"), "")
if assert.Error(t, err) {
assert.True(t, os.IsNotExist(err), "Expected os.IsNotExist(err) == true")
}

// Test add picture to worksheet with unsupported file type.
err = xlsx.AddPicture("Sheet1", "G21", filepath.Join("test", "Book1.xlsx"), "")
err = f.AddPicture("Sheet1", "G21", filepath.Join("test", "Book1.xlsx"), "")
assert.EqualError(t, err, ErrImgExt.Error())

err = xlsx.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", "jpg", make([]byte, 1))
err = f.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", "jpg", make([]byte, 1))
assert.EqualError(t, err, ErrImgExt.Error())

// Test add picture to worksheet with invalid file data.
err = xlsx.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", ".jpg", make([]byte, 1))
err = f.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", ".jpg", make([]byte, 1))
assert.EqualError(t, err, "image: unknown format")
assert.NoError(t, f.Close())
}

func TestGetPicture(t *testing.T) {
Expand All @@ -108,7 +110,7 @@ func TestGetPicture(t *testing.T) {

// Try to get picture from a worksheet with illegal cell coordinates.
_, _, err = f.GetPicture("Sheet1", "A")
assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, err, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())

// Try to get picture from a worksheet that doesn't contain any images.
file, raw, err = f.GetPicture("Sheet3", "I9")
Expand Down Expand Up @@ -137,7 +139,6 @@ func TestGetPicture(t *testing.T) {
assert.NoError(t, err)
if !assert.NotEmpty(t, filepath.Join("test", file)) || !assert.NotEmpty(t, raw) ||
!assert.NoError(t, ioutil.WriteFile(filepath.Join("test", file), raw, 0644)) {

t.FailNow()
}

Expand All @@ -146,6 +147,7 @@ func TestGetPicture(t *testing.T) {
assert.NoError(t, err)
assert.Empty(t, file)
assert.Empty(t, raw)
assert.NoError(t, f.Close())

// Test get picture from none drawing worksheet.
f = NewFile()
Expand All @@ -163,7 +165,7 @@ func TestGetPicture(t *testing.T) {
func TestAddDrawingPicture(t *testing.T) {
// testing addDrawingPicture with illegal cell coordinates.
f := NewFile()
assert.EqualError(t, f.addDrawingPicture("sheet1", "", "A", "", 0, 0, 0, 0, nil), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, f.addDrawingPicture("sheet1", "", "A", "", 0, 0, 0, 0, nil), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
}

func TestAddPictureFromBytes(t *testing.T) {
Expand Down Expand Up @@ -193,7 +195,8 @@ func TestDeletePicture(t *testing.T) {
// Test delete picture on not exists worksheet.
assert.EqualError(t, f.DeletePicture("SheetN", "A1"), "sheet SheetN is not exist")
// Test delete picture with invalid coordinates.
assert.EqualError(t, f.DeletePicture("Sheet1", ""), `cannot convert cell "" to coordinates: invalid cell name ""`)
assert.EqualError(t, f.DeletePicture("Sheet1", ""), newCellNameToCoordinatesError("", newInvalidCellNameError("")).Error())
assert.NoError(t, f.Close())
// Test delete picture on no chart worksheet.
assert.NoError(t, NewFile().DeletePicture("Sheet1", "A1"))
}
Expand All @@ -205,9 +208,9 @@ func TestDrawingResize(t *testing.T) {
assert.EqualError(t, err, "sheet SheetN is not exist")
// Test calculate drawing resize with invalid coordinates.
_, _, _, _, err = f.drawingResize("Sheet1", "", 1, 1, nil)
assert.EqualError(t, err, `cannot convert cell "" to coordinates: invalid cell name ""`)
assert.EqualError(t, err, newCellNameToCoordinatesError("", newInvalidCellNameError("")).Error())
ws, ok := f.Sheet.Load("xl/worksheets/sheet1.xml")
assert.True(t, ok)
ws.(*xlsxWorksheet).MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A:A"}}}
assert.EqualError(t, f.AddPicture("Sheet1", "A1", filepath.Join("test", "images", "excel.jpg"), `{"autofit": true}`), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, f.AddPicture("Sheet1", "A1", filepath.Join("test", "images", "excel.jpg"), `{"autofit": true}`), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
}
64 changes: 34 additions & 30 deletions pivotTable.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -19,6 +19,13 @@ import (
)

// PivotTableOption directly maps the format settings of the pivot table.
//
// PivotTableStyleName: The built-in pivot table style names
//
// PivotStyleLight1 - PivotStyleLight28
// PivotStyleMedium1 - PivotStyleMedium28
// PivotStyleDark1 - PivotStyleDark28
//
type PivotTableOption struct {
pivotTableSheetName string
DataRange string
Expand Down Expand Up @@ -63,8 +70,10 @@ type PivotTableOption struct {
// Name specifies the name of the data field. Maximum 255 characters
// are allowed in data field name, excess characters will be truncated.
type PivotTableField struct {
Compact bool
Data string
Name string
Outline bool
Subtotal string
DefaultSubtotal bool
}
Expand Down Expand Up @@ -198,7 +207,7 @@ func (f *File) adjustRange(rangeStr string) (string, []int, error) {
return "", []int{}, ErrParameterInvalid
}
trimRng := strings.Replace(rng[1], "$", "", -1)
coordinates, err := f.areaRefToCoordinates(trimRng)
coordinates, err := areaRefToCoordinates(trimRng)
if err != nil {
return rng[0], []int{}, err
}
Expand Down Expand Up @@ -277,13 +286,13 @@ func (f *File) addPivotCache(pivotCacheID int, pivotCacheXML string, opt *PivotT
pc.CacheSource.WorksheetSource = &xlsxWorksheetSource{Name: opt.DataRange}
}
for _, name := range order {
defaultRowsSubtotal, rowOk := f.getPivotTableFieldNameDefaultSubtotal(name, opt.Rows)
defaultColumnsSubtotal, colOk := f.getPivotTableFieldNameDefaultSubtotal(name, opt.Columns)
rowOptions, rowOk := f.getPivotTableFieldOptions(name, opt.Rows)
columnOptions, colOk := f.getPivotTableFieldOptions(name, opt.Columns)
sharedItems := xlsxSharedItems{
Count: 0,
}
s := xlsxString{}
if (rowOk && !defaultRowsSubtotal) || (colOk && !defaultColumnsSubtotal) {
if (rowOk && !rowOptions.DefaultSubtotal) || (colOk && !columnOptions.DefaultSubtotal) {
s = xlsxString{
V: "",
}
Expand Down Expand Up @@ -459,17 +468,6 @@ func (f *File) addPivotDataFields(pt *xlsxPivotTableDefinition, opt *PivotTableO
return err
}

// inStrSlice provides a method to check if an element is present in an array,
// and return the index of its location, otherwise return -1.
func inStrSlice(a []string, x string) int {
for idx, n := range a {
if x == n {
return idx
}
}
return -1
}

// inPivotTableField provides a method to check if an element is present in
// pivot table fields list, and return the index of its location, otherwise
// return -1.
Expand Down Expand Up @@ -533,22 +531,24 @@ func (f *File) addPivotFields(pt *xlsxPivotTableDefinition, opt *PivotTableOptio
x := 0
for _, name := range order {
if inPivotTableField(opt.Rows, name) != -1 {
defaultSubtotal, ok := f.getPivotTableFieldNameDefaultSubtotal(name, opt.Rows)
rowOptions, ok := f.getPivotTableFieldOptions(name, opt.Rows)
var items []*xlsxItem
if !ok || !defaultSubtotal {
if !ok || !rowOptions.DefaultSubtotal {
items = append(items, &xlsxItem{X: &x})
} else {
items = append(items, &xlsxItem{T: "default"})
}

pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{
Axis: "axisRow",
Name: f.getPivotTableFieldName(name, opt.Rows),
Name: f.getPivotTableFieldName(name, opt.Rows),
Axis: "axisRow",
Compact: &rowOptions.Compact,
Outline: &rowOptions.Outline,
DefaultSubtotal: &rowOptions.DefaultSubtotal,
Items: &xlsxItems{
Count: len(items),
Item: items,
},
DefaultSubtotal: &defaultSubtotal,
})
continue
}
Expand All @@ -566,21 +566,23 @@ func (f *File) addPivotFields(pt *xlsxPivotTableDefinition, opt *PivotTableOptio
continue
}
if inPivotTableField(opt.Columns, name) != -1 {
defaultSubtotal, ok := f.getPivotTableFieldNameDefaultSubtotal(name, opt.Columns)
columnOptions, ok := f.getPivotTableFieldOptions(name, opt.Columns)
var items []*xlsxItem
if !ok || !defaultSubtotal {
if !ok || !columnOptions.DefaultSubtotal {
items = append(items, &xlsxItem{X: &x})
} else {
items = append(items, &xlsxItem{T: "default"})
}
pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{
Axis: "axisCol",
Name: f.getPivotTableFieldName(name, opt.Columns),
Name: f.getPivotTableFieldName(name, opt.Columns),
Axis: "axisCol",
Compact: &columnOptions.Compact,
Outline: &columnOptions.Outline,
DefaultSubtotal: &columnOptions.DefaultSubtotal,
Items: &xlsxItems{
Count: len(items),
Item: items,
},
DefaultSubtotal: &defaultSubtotal,
})
continue
}
Expand Down Expand Up @@ -660,8 +662,8 @@ func (f *File) getPivotTableFieldsSubtotal(fields []PivotTableField) []string {
func (f *File) getPivotTableFieldsName(fields []PivotTableField) []string {
field := make([]string, len(fields))
for idx, fld := range fields {
if len(fld.Name) > 255 {
field[idx] = fld.Name[0:255]
if len(fld.Name) > MaxFieldLength {
field[idx] = fld.Name[:MaxFieldLength]
continue
}
field[idx] = fld.Name
Expand All @@ -680,13 +682,15 @@ func (f *File) getPivotTableFieldName(name string, fields []PivotTableField) str
return ""
}

func (f *File) getPivotTableFieldNameDefaultSubtotal(name string, fields []PivotTableField) (bool, bool) {
// getPivotTableFieldOptions return options for specific field by given field name.
func (f *File) getPivotTableFieldOptions(name string, fields []PivotTableField) (options PivotTableField, ok bool) {
for _, field := range fields {
if field.Data == name {
return field.DefaultSubtotal, true
options, ok = field, true
return
}
}
return false, false
return
}

// addWorkbookPivotCache add the association ID of the pivot cache in workbook.xml.
Expand Down
8 changes: 2 additions & 6 deletions pivotTable_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -158,7 +158,7 @@ func TestAddPivotTable(t *testing.T) {
}))

// Test empty pivot table options
assert.EqualError(t, f.AddPivotTable(nil), "parameter is required")
assert.EqualError(t, f.AddPivotTable(nil), ErrParameterRequired.Error())
// Test invalid data range
assert.EqualError(t, f.AddPivotTable(&PivotTableOption{
DataRange: "Sheet1!$A$1:$A$1",
Expand Down Expand Up @@ -227,7 +227,7 @@ func TestAddPivotTable(t *testing.T) {

// Test adjust range with invalid range
_, _, err := f.adjustRange("")
assert.EqualError(t, err, "parameter is required")
assert.EqualError(t, err, ErrParameterRequired.Error())
// Test adjust range with incorrect range
_, _, err = f.adjustRange("sheet1!")
assert.EqualError(t, err, "parameter is invalid")
Expand Down Expand Up @@ -301,10 +301,6 @@ func TestGetPivotFieldsOrder(t *testing.T) {
assert.EqualError(t, err, "sheet SheetN is not exist")
}

func TestInStrSlice(t *testing.T) {
assert.EqualValues(t, -1, inStrSlice([]string{}, ""))
}

func TestGetPivotTableFieldName(t *testing.T) {
f := NewFile()
f.getPivotTableFieldName("-", []PivotTableField{})
Expand Down
217 changes: 172 additions & 45 deletions rows.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -18,13 +18,21 @@ import (
"io"
"log"
"math"
"math/big"
"os"
"strconv"
"sync"

"github.com/mohae/deepcopy"
)

// GetRows return all the rows in a sheet by given worksheet name (case
// sensitive). For example:
// GetRows return all the rows in a sheet by given worksheet name
// (case sensitive), returned as a two-dimensional array, where the value of
// the cell is converted to the string type. If the cell format can be
// applied to the value of the cell, the applied value will be used,
// otherwise the original value will be used. GetRows fetched the rows with
// value or formula cells, the tail continuously empty cell will be skipped.
// For example:
//
// rows, err := f.GetRows("Sheet1")
// if err != nil {
Expand All @@ -38,15 +46,15 @@ import (
// fmt.Println()
// }
//
func (f *File) GetRows(sheet string) ([][]string, error) {
func (f *File) GetRows(sheet string, opts ...Options) ([][]string, error) {
rows, err := f.Rows(sheet)
if err != nil {
return nil, err
}
results, cur, max := make([][]string, 0, 64), 0, 0
for rows.Next() {
cur++
row, err := rows.Columns()
row, err := rows.Columns(opts...)
if err != nil {
break
}
Expand All @@ -55,35 +63,57 @@ func (f *File) GetRows(sheet string) ([][]string, error) {
max = cur
}
}
return results[:max], nil
return results[:max], rows.Close()
}

// Rows defines an iterator to a sheet.
type Rows struct {
err error
curRow, totalRow, stashRow int
sheet string
f *File
decoder *xml.Decoder
err error
curRow, totalRows, stashRow int
rawCellValue bool
sheet string
f *File
tempFile *os.File
decoder *xml.Decoder
}

// CurrentRow returns the row number that represents the current row.
func (rows *Rows) CurrentRow() int {
return rows.curRow
}

// TotalRows returns the total rows count in the worksheet.
func (rows *Rows) TotalRows() int {
return rows.totalRows
}

// Next will return true if find the next row element.
func (rows *Rows) Next() bool {
rows.curRow++
return rows.curRow <= rows.totalRow
return rows.curRow <= rows.totalRows
}

// Error will return the error when the error occurs.
func (rows *Rows) Error() error {
return rows.err
}

// Close closes the open worksheet XML file in the system temporary
// directory.
func (rows *Rows) Close() error {
if rows.tempFile != nil {
return rows.tempFile.Close()
}
return nil
}

// Columns return the current row's column values.
func (rows *Rows) Columns() ([]string, error) {
func (rows *Rows) Columns(opts ...Options) ([]string, error) {
var rowIterator rowXMLIterator
if rows.stashRow >= rows.curRow {
return rowIterator.columns, rowIterator.err
}
rows.rawCellValue = parseOptions(opts...).RawCellValue
rowIterator.rows = rows
rowIterator.d = rows.f.sharedStringsReader()
for {
Expand All @@ -104,13 +134,13 @@ func (rows *Rows) Columns() ([]string, error) {
return rowIterator.columns, rowIterator.err
}
}
rowXMLHandler(&rowIterator, &xmlElement)
rowXMLHandler(&rowIterator, &xmlElement, rows.rawCellValue)
if rowIterator.err != nil {
return rowIterator.columns, rowIterator.err
}
case xml.EndElement:
rowIterator.inElement = xmlElement.Name.Local
if rowIterator.row == 0 {
if rowIterator.row == 0 && rowIterator.rows.curRow > 1 {
rowIterator.row = rowIterator.rows.curRow
}
if rowIterator.inElement == "row" && rowIterator.row+1 < rowIterator.rows.curRow {
Expand Down Expand Up @@ -152,7 +182,7 @@ type rowXMLIterator struct {
}

// rowXMLHandler parse the row XML element of the worksheet.
func rowXMLHandler(rowIterator *rowXMLIterator, xmlElement *xml.StartElement) {
func rowXMLHandler(rowIterator *rowXMLIterator, xmlElement *xml.StartElement, raw bool) {
rowIterator.err = nil
if rowIterator.inElement == "c" {
rowIterator.cellCol++
Expand All @@ -164,7 +194,7 @@ func rowXMLHandler(rowIterator *rowXMLIterator, xmlElement *xml.StartElement) {
}
}
blank := rowIterator.cellCol - len(rowIterator.columns)
val, _ := colCell.getValueFrom(rowIterator.rows.f, rowIterator.d)
val, _ := colCell.getValueFrom(rowIterator.rows.f, rowIterator.d, raw)
if val != "" || colCell.F != nil {
rowIterator.columns = append(appendSpace(blank, rowIterator.columns), val)
}
Expand All @@ -189,6 +219,9 @@ func rowXMLHandler(rowIterator *rowXMLIterator, xmlElement *xml.StartElement) {
// }
// fmt.Println()
// }
// if err = rows.Close(); err != nil {
// fmt.Println(err)
// }
//
func (f *File) Rows(sheet string) (*Rows, error) {
name, ok := f.sheetMap[trimSheetName(sheet)]
Expand All @@ -208,8 +241,13 @@ func (f *File) Rows(sheet string) (*Rows, error) {
inElement string
row int
rows Rows
needClose bool
decoder *xml.Decoder
tempFile *os.File
)
decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
if needClose, decoder, tempFile, err = f.xmlDecoder(name); needClose && err == nil {
defer tempFile.Close()
}
for {
token, _ := decoder.Token()
if token == nil {
Expand All @@ -228,21 +266,72 @@ func (f *File) Rows(sheet string) (*Rows, error) {
}
}
}
rows.totalRow = row
rows.totalRows = row
}
case xml.EndElement:
if xmlElement.Name.Local == "sheetData" {
rows.f = f
rows.sheet = name
rows.decoder = f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
return &rows, nil
_, rows.decoder, rows.tempFile, err = f.xmlDecoder(name)
return &rows, err
}
default:
}
}
return &rows, nil
}

// getFromStringItemMap build shared string item map from system temporary
// file at one time, and return value by given to string index.
func (f *File) getFromStringItemMap(index int) string {
if f.sharedStringItemMap != nil {
if value, ok := f.sharedStringItemMap.Load(index); ok {
return value.(string)
}
return strconv.Itoa(index)
}
f.sharedStringItemMap = &sync.Map{}
needClose, decoder, tempFile, err := f.xmlDecoder(dafaultXMLPathSharedStrings)
if needClose && err == nil {
defer tempFile.Close()
}
var (
inElement string
i int
)
for {
token, _ := decoder.Token()
if token == nil {
break
}
switch xmlElement := token.(type) {
case xml.StartElement:
inElement = xmlElement.Name.Local
if inElement == "si" {
si := xlsxSI{}
_ = decoder.DecodeElement(&si, &xmlElement)
f.sharedStringItemMap.Store(i, si.String())
i++
}
}
}
return f.getFromStringItemMap(index)
}

// xmlDecoder creates XML decoder by given path in the zip from memory data
// or system temporary file.
func (f *File) xmlDecoder(name string) (bool, *xml.Decoder, *os.File, error) {
var (
content []byte
err error
tempFile *os.File
)
if content = f.readXML(name); len(content) > 0 {
return false, f.xmlNewDecoder(bytes.NewReader(content)), tempFile, err
}
tempFile, err = f.readTemp(name)
return true, f.xmlNewDecoder(tempFile), tempFile, err
}

// SetRowHeight provides a function to set the height of a single row. For
// example, set the height of the first row in Sheet1:
//
Expand Down Expand Up @@ -322,7 +411,7 @@ func (f *File) sharedStringsReader() *xlsxSST {
relPath := f.getWorkbookRelsPath()
if f.SharedStrings == nil {
var sharedStrings xlsxSST
ss := f.readXML("xl/sharedStrings.xml")
ss := f.readXML(dafaultXMLPathSharedStrings)
if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(ss))).
Decode(&sharedStrings); err != nil && err != io.EOF {
log.Printf("xml decode error: %s", err)
Expand Down Expand Up @@ -356,46 +445,47 @@ func (f *File) sharedStringsReader() *xlsxSST {
// getValueFrom return a value from a column/row cell, this function is
// inteded to be used with for range on rows an argument with the spreadsheet
// opened file.
func (c *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error) {
func (c *xlsxC) getValueFrom(f *File, d *xlsxSST, raw bool) (string, error) {
f.Lock()
defer f.Unlock()
switch c.T {
case "s":
if c.V != "" {
xlsxSI := 0
xlsxSI, _ = strconv.Atoi(c.V)
if _, ok := f.tempFiles.Load(dafaultXMLPathSharedStrings); ok {
return f.formattedValue(c.S, f.getFromStringItemMap(xlsxSI), raw), nil
}
if len(d.SI) > xlsxSI {
return f.formattedValue(c.S, d.SI[xlsxSI].String()), nil
return f.formattedValue(c.S, d.SI[xlsxSI].String(), raw), nil
}
}
return f.formattedValue(c.S, c.V), nil
return f.formattedValue(c.S, c.V, raw), nil
case "str":
return f.formattedValue(c.S, c.V), nil
return f.formattedValue(c.S, c.V, raw), nil
case "inlineStr":
if c.IS != nil {
return f.formattedValue(c.S, c.IS.String()), nil
return f.formattedValue(c.S, c.IS.String(), raw), nil
}
return f.formattedValue(c.S, c.V), nil
return f.formattedValue(c.S, c.V, raw), nil
default:
isNum, precision := isNumeric(c.V)
if isNum && precision > 10 {
val, _ := roundPrecision(c.V)
if val != c.V {
return f.formattedValue(c.S, val), nil
}
}
return f.formattedValue(c.S, c.V), nil
return f.formattedValue(c.S, c.V, raw), nil
}
}

// roundPrecision round precision for numeric.
func roundPrecision(value string) (result string, err error) {
var num float64
if num, err = strconv.ParseFloat(value, 64); err != nil {
return
// roundPrecision provides a function to format floating-point number text
// with precision, if the given text couldn't be parsed to float, this will
// return the original string.
func roundPrecision(text string, prec int) string {
decimal := big.Float{}
if _, ok := decimal.SetString(text); ok {
flt, _ := decimal.Float64()
if prec == -1 {
return decimal.Text('G', 15)
}
return strconv.FormatFloat(flt, 'f', -1, 64)
}
result = fmt.Sprintf("%g", math.Round(num*numericPrecision)/numericPrecision)
return
return text
}

// SetRowVisible provides a function to set visible of a single row by given
Expand Down Expand Up @@ -614,7 +704,7 @@ func (f *File) duplicateMergeCells(sheet string, ws *xlsxWorksheet, row, row2 in
row++
}
for _, rng := range ws.MergeCells.Cells {
coordinates, err := f.areaRefToCoordinates(rng.Ref)
coordinates, err := areaRefToCoordinates(rng.Ref)
if err != nil {
return err
}
Expand All @@ -624,7 +714,7 @@ func (f *File) duplicateMergeCells(sheet string, ws *xlsxWorksheet, row, row2 in
}
for i := 0; i < len(ws.MergeCells.Cells); i++ {
areaData := ws.MergeCells.Cells[i]
coordinates, _ := f.areaRefToCoordinates(areaData.Ref)
coordinates, _ := areaRefToCoordinates(areaData.Ref)
x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
if y1 == y2 && y1 == row {
from, _ := CoordinatesToCellName(x1, row2)
Expand Down Expand Up @@ -719,6 +809,43 @@ func checkRow(ws *xlsxWorksheet) error {
return nil
}

// SetRowStyle provides a function to set the style of rows by given worksheet
// name, row range, and style ID. Note that this will overwrite the existing
// styles for the rows, it won't append or merge style with existing styles.
//
// For example set style of row 1 on Sheet1:
//
// err = f.SetRowStyle("Sheet1", 1, 1, styleID)
//
// Set style of rows 1 to 10 on Sheet1:
//
// err = f.SetRowStyle("Sheet1", 1, 10, styleID)
//
func (f *File) SetRowStyle(sheet string, start, end, styleID int) error {
if end < start {
start, end = end, start
}
if start < 1 {
return newInvalidRowNumberError(start)
}
if end > TotalRows {
return ErrMaxRows
}
if styleID < 0 {
return newInvalidStyleID(styleID)
}
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
prepareSheetXML(ws, 0, end)
for row := start - 1; row < end; row++ {
ws.SheetData.Row[row].S = styleID
ws.SheetData.Row[row].CustomFormat = true
}
return nil
}

// convertRowHeightToPixels provides a function to convert the height of a
// cell from user's units to pixels. If the height hasn't been set by the user
// we use the default value. If the row is hidden it has a value of zero.
Expand Down
93 changes: 65 additions & 28 deletions rows_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -32,6 +32,7 @@ func TestRows(t *testing.T) {
if !assert.NoError(t, rows.Error()) {
t.FailNow()
}
assert.NoError(t, rows.Close())

returnedRows, err := f.GetRows(sheet2)
assert.NoError(t, err)
Expand All @@ -41,6 +42,7 @@ func TestRows(t *testing.T) {
if !assert.Equal(t, collectedRows, returnedRows) {
t.FailNow()
}
assert.NoError(t, f.Close())

f = NewFile()
f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(`<worksheet><sheetData><row r="1"><c r="A1" t="s"><v>1</v></c></row><row r="A"><c r="2" t="str"><v>B</v></c></row></sheetData></worksheet>`))
Expand All @@ -52,39 +54,54 @@ func TestRows(t *testing.T) {
f.Pkg.Store("xl/worksheets/sheet1.xml", nil)
_, err = f.Rows("Sheet1")
assert.NoError(t, err)

// Test reload the file to memory from system temporary directory.
f, err = OpenFile(filepath.Join("test", "Book1.xlsx"), Options{UnzipXMLSizeLimit: 128})
assert.NoError(t, err)
value, err := f.GetCellValue("Sheet1", "A19")
assert.NoError(t, err)
assert.Equal(t, "Total:", value)
// Test load shared string table to memory
err = f.SetCellValue("Sheet1", "A19", "A19")
assert.NoError(t, err)
value, err = f.GetCellValue("Sheet1", "A19")
assert.NoError(t, err)
assert.Equal(t, "A19", value)
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetRow.xlsx")))
assert.NoError(t, f.Close())
}

func TestRowsIterator(t *testing.T) {
const (
sheet2 = "Sheet2"
expectedNumRow = 11
)
sheetName, rowCount, expectedNumRow := "Sheet2", 0, 11
f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
require.NoError(t, err)

rows, err := f.Rows(sheet2)
rows, err := f.Rows(sheetName)
require.NoError(t, err)
var rowCount int

for rows.Next() {
rowCount++
assert.Equal(t, rowCount, rows.CurrentRow())
assert.Equal(t, expectedNumRow, rows.TotalRows())
require.True(t, rowCount <= expectedNumRow, "rowCount is greater than expected")
}
assert.Equal(t, expectedNumRow, rowCount)
assert.NoError(t, rows.Close())
assert.NoError(t, f.Close())

// Valued cell sparse distribution test
f = NewFile()
f, sheetName, rowCount, expectedNumRow = NewFile(), "Sheet1", 0, 3
cells := []string{"C1", "E1", "A3", "B3", "C3", "D3", "E3"}
for _, cell := range cells {
assert.NoError(t, f.SetCellValue("Sheet1", cell, 1))
assert.NoError(t, f.SetCellValue(sheetName, cell, 1))
}
rows, err = f.Rows("Sheet1")
rows, err = f.Rows(sheetName)
require.NoError(t, err)
rowCount = 0
for rows.Next() {
rowCount++
require.True(t, rowCount <= 3, "rowCount is greater than expected")
require.True(t, rowCount <= expectedNumRow, "rowCount is greater than expected")
}
assert.Equal(t, 3, rowCount)
assert.Equal(t, expectedNumRow, rowCount)
}

func TestRowsError(t *testing.T) {
Expand All @@ -94,16 +111,17 @@ func TestRowsError(t *testing.T) {
}
_, err = f.Rows("SheetN")
assert.EqualError(t, err, "sheet SheetN is not exist")
assert.NoError(t, f.Close())
}

func TestRowHeight(t *testing.T) {
f := NewFile()
sheet1 := f.GetSheetName(0)

assert.EqualError(t, f.SetRowHeight(sheet1, 0, defaultRowHeightPixels+1.0), "invalid row number 0")
assert.EqualError(t, f.SetRowHeight(sheet1, 0, defaultRowHeightPixels+1.0), newInvalidRowNumberError(0).Error())

_, err := f.GetRowHeight("Sheet1", 0)
assert.EqualError(t, err, "invalid row number 0")
assert.EqualError(t, err, newInvalidRowNumberError(0).Error())

assert.NoError(t, f.SetRowHeight(sheet1, 1, 111.0))
height, err := f.GetRowHeight(sheet1, 1)
Expand Down Expand Up @@ -179,7 +197,7 @@ func TestColumns(t *testing.T) {
rows.curRow = 3
rows.decoder = f.xmlNewDecoder(bytes.NewReader([]byte(`<worksheet><sheetData><row r="1"><c r="A" t="s"><v>1</v></c></row></sheetData></worksheet>`)))
_, err = rows.Columns()
assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, err, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())

// Test token is nil
rows.decoder = f.xmlNewDecoder(bytes.NewReader(nil))
Expand All @@ -189,7 +207,7 @@ func TestColumns(t *testing.T) {

func TestSharedStringsReader(t *testing.T) {
f := NewFile()
f.Pkg.Store("xl/sharedStrings.xml", MacintoshCyrillicCharset)
f.Pkg.Store(dafaultXMLPathSharedStrings, MacintoshCyrillicCharset)
f.sharedStringsReader()
si := xlsxSI{}
assert.EqualValues(t, "", si.String())
Expand All @@ -209,12 +227,12 @@ func TestRowVisibility(t *testing.T) {
visiable, err = f.GetRowVisible("Sheet3", 25)
assert.Equal(t, false, visiable)
assert.NoError(t, err)
assert.EqualError(t, f.SetRowVisible("Sheet3", 0, true), "invalid row number 0")
assert.EqualError(t, f.SetRowVisible("Sheet3", 0, true), newInvalidRowNumberError(0).Error())
assert.EqualError(t, f.SetRowVisible("SheetN", 2, false), "sheet SheetN is not exist")

visible, err := f.GetRowVisible("Sheet3", 0)
assert.Equal(t, false, visible)
assert.EqualError(t, err, "invalid row number 0")
assert.EqualError(t, err, newInvalidRowNumberError(0).Error())
_, err = f.GetRowVisible("SheetN", 1)
assert.EqualError(t, err, "sheet SheetN is not exist")

Expand All @@ -234,9 +252,9 @@ func TestRemoveRow(t *testing.T) {

assert.NoError(t, f.SetCellHyperLink(sheet1, "A5", "https://github.com/xuri/excelize", "External"))

assert.EqualError(t, f.RemoveRow(sheet1, -1), "invalid row number -1")
assert.EqualError(t, f.RemoveRow(sheet1, -1), newInvalidRowNumberError(-1).Error())

assert.EqualError(t, f.RemoveRow(sheet1, 0), "invalid row number 0")
assert.EqualError(t, f.RemoveRow(sheet1, 0), newInvalidRowNumberError(0).Error())

assert.NoError(t, f.RemoveRow(sheet1, 4))
if !assert.Len(t, r.SheetData.Row, rowCount-1) {
Expand Down Expand Up @@ -295,9 +313,9 @@ func TestInsertRow(t *testing.T) {

assert.NoError(t, f.SetCellHyperLink(sheet1, "A5", "https://github.com/xuri/excelize", "External"))

assert.EqualError(t, f.InsertRow(sheet1, -1), "invalid row number -1")
assert.EqualError(t, f.InsertRow(sheet1, -1), newInvalidRowNumberError(-1).Error())

assert.EqualError(t, f.InsertRow(sheet1, 0), "invalid row number 0")
assert.EqualError(t, f.InsertRow(sheet1, 0), newInvalidRowNumberError(0).Error())

assert.NoError(t, f.InsertRow(sheet1, 1))
if !assert.Len(t, r.SheetData.Row, rowCount+1) {
Expand Down Expand Up @@ -473,7 +491,7 @@ func TestDuplicateRowZeroWithNoRows(t *testing.T) {
t.Run("ZeroWithNoRows", func(t *testing.T) {
f := NewFile()

assert.EqualError(t, f.DuplicateRow(sheet, 0), "invalid row number 0")
assert.EqualError(t, f.DuplicateRow(sheet, 0), newInvalidRowNumberError(0).Error())

if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "ZeroWithNoRows"))) {
t.FailNow()
Expand Down Expand Up @@ -789,7 +807,7 @@ func TestDuplicateRowInvalidRowNum(t *testing.T) {
assert.NoError(t, f.SetCellStr(sheet, col, val))
}

assert.EqualError(t, f.DuplicateRow(sheet, row), fmt.Sprintf("invalid row number %d", row))
assert.EqualError(t, f.DuplicateRow(sheet, row), newInvalidRowNumberError(row).Error())

for col, val := range cells {
v, err := f.GetCellValue(sheet, col)
Expand All @@ -811,7 +829,7 @@ func TestDuplicateRowInvalidRowNum(t *testing.T) {
assert.NoError(t, f.SetCellStr(sheet, col, val))
}

assert.EqualError(t, f.DuplicateRowTo(sheet, row1, row2), fmt.Sprintf("invalid row number %d", row1))
assert.EqualError(t, f.DuplicateRowTo(sheet, row1, row2), newInvalidRowNumberError(row1).Error())

for col, val := range cells {
v, err := f.GetCellValue(sheet, col)
Expand Down Expand Up @@ -845,7 +863,7 @@ func TestGetValueFromInlineStr(t *testing.T) {
c := &xlsxC{T: "inlineStr"}
f := NewFile()
d := &xlsxSST{}
val, err := c.getValueFrom(f, d)
val, err := c.getValueFrom(f, d, false)
assert.NoError(t, err)
assert.Equal(t, "", val)
}
Expand All @@ -865,7 +883,7 @@ func TestGetValueFromNumber(t *testing.T) {
"2.220000ddsf0000000002-r": "2.220000ddsf0000000002-r",
} {
c.V = input
val, err := c.getValueFrom(f, d)
val, err := c.getValueFrom(f, d, false)
assert.NoError(t, err)
assert.Equal(t, expected, val)
}
Expand All @@ -886,7 +904,19 @@ func TestCheckRow(t *testing.T) {
f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(`<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" ><sheetData><row r="2"><c><v>1</v></c><c r="-"><v>2</v></c><c><v>3</v></c><c><v>4</v></c><c r="M2"><v>5</v></c></row></sheetData></worksheet>`))
f.Sheet.Delete("xl/worksheets/sheet1.xml")
delete(f.checked, "xl/worksheets/sheet1.xml")
assert.EqualError(t, f.SetCellValue("Sheet1", "A1", false), `cannot convert cell "-" to coordinates: invalid cell name "-"`)
assert.EqualError(t, f.SetCellValue("Sheet1", "A1", false), newCellNameToCoordinatesError("-", newInvalidCellNameError("-")).Error())
}

func TestSetRowStyle(t *testing.T) {
f := NewFile()
styleID, err := f.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":1}}`)
assert.NoError(t, err)
assert.EqualError(t, f.SetRowStyle("Sheet1", 10, -1, styleID), newInvalidRowNumberError(-1).Error())
assert.EqualError(t, f.SetRowStyle("Sheet1", 1, TotalRows+1, styleID), ErrMaxRows.Error())
assert.EqualError(t, f.SetRowStyle("Sheet1", 1, 1, -1), newInvalidStyleID(-1).Error())
assert.EqualError(t, f.SetRowStyle("SheetN", 1, 1, styleID), "sheet SheetN is not exist")
assert.NoError(t, f.SetRowStyle("Sheet1", 10, 1, styleID))
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetRowStyle.xlsx")))
}

func TestNumberFormats(t *testing.T) {
Expand All @@ -908,6 +938,7 @@ func TestNumberFormats(t *testing.T) {
cells = append(cells, col)
}
assert.Equal(t, []string{"", "200", "450", "200", "510", "315", "127", "89", "348", "53", "37"}, cells[3])
assert.NoError(t, f.Close())
}

func BenchmarkRows(b *testing.B) {
Expand All @@ -922,6 +953,12 @@ func BenchmarkRows(b *testing.B) {
}
}
}
if err := rows.Close(); err != nil {
b.Error(err)
}
}
if err := f.Close(); err != nil {
b.Error(err)
}
}

Expand Down
34 changes: 33 additions & 1 deletion shape.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -32,6 +32,7 @@ func parseFormatShapeSet(formatSet string) (*formatShape, error) {
XScale: 1.0,
YScale: 1.0,
},
Line: formatLine{Width: 1},
}
err := json.Unmarshal([]byte(formatSet), &format)
return &format, err
Expand All @@ -42,7 +43,33 @@ func parseFormatShapeSet(formatSet string) (*formatShape, error) {
// print settings) and properties set. For example, add text box (rect shape)
// in Sheet1:
//
// err := f.AddShape("Sheet1", "G6", `{"type":"rect","color":{"line":"#4286F4","fill":"#8eb9ff"},"paragraph":[{"text":"Rectangle Shape","font":{"bold":true,"italic":true,"family":"Times New Roman","size":36,"color":"#777777","underline":"sng"}}],"width":180,"height": 90}`)
// err := f.AddShape("Sheet1", "G6", `{
// "type": "rect",
// "color":
// {
// "line": "#4286F4",
// "fill": "#8eb9ff"
// },
// "paragraph": [
// {
// "text": "Rectangle Shape",
// "font":
// {
// "bold": true,
// "italic": true,
// "family": "Times New Roman",
// "size": 36,
// "color": "#777777",
// "underline": "sng"
// }
// }],
// "width": 180,
// "height": 90,
// "line":
// {
// "width": 1.2
// }
// }`)
//
// The following shows the type of shape supported by excelize:
//
Expand Down Expand Up @@ -378,6 +405,11 @@ func (f *File) addDrawingShape(sheet, drawingXML, cell string, formatSet *format
},
},
}
if formatSet.Line.Width != 1 {
shape.SpPr.Ln = xlsxLineProperties{
W: f.ptToEMUs(formatSet.Line.Width),
}
}
if len(formatSet.Paragraph) < 1 {
formatSet.Paragraph = []formatShapeParagraph{
{
Expand Down
68 changes: 65 additions & 3 deletions shape_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -16,13 +16,75 @@ func TestAddShape(t *testing.T) {
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", `{
"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.EqualError(t, f.AddShape("Sheet1", "A", `{
"type": "rect",
"paragraph": [
{
"text": "Rectangle",
"font":
{
"color": "CD5C5C"
}
},
{
"text": "Shape",
"font":
{
"bold": true,
"color": "2980B9"
}
}]
}`), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddShape1.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.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,
"line":
{
"width": 1.2
}
}`))
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddShape2.xlsx")))
}
181 changes: 121 additions & 60 deletions sheet.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -24,8 +24,10 @@ import (
"path/filepath"
"reflect"
"regexp"
"sort"
"strconv"
"strings"
"unicode/utf16"
"unicode/utf8"

"github.com/mohae/deepcopy"
Expand Down Expand Up @@ -74,7 +76,7 @@ func (f *File) contentTypesReader() *xlsxTypes {
f.ContentTypes = new(xlsxTypes)
f.ContentTypes.Lock()
defer f.ContentTypes.Unlock()
if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML("[Content_Types].xml")))).
if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(defaultXMLPathContentTypes)))).
Decode(f.ContentTypes); err != nil && err != io.EOF {
log.Printf("xml decode error: %s", err)
}
Expand All @@ -87,7 +89,7 @@ func (f *File) contentTypesReader() *xlsxTypes {
func (f *File) contentTypesWriter() {
if f.ContentTypes != nil {
output, _ := xml.Marshal(f.ContentTypes)
f.saveFileList("[Content_Types].xml", output)
f.saveFileList(defaultXMLPathContentTypes, output)
}
}

Expand Down Expand Up @@ -120,6 +122,19 @@ func (f *File) getWorkbookRelsPath() (path string) {
return
}

// getWorksheetPath construct a target XML as xl/worksheets/sheet%d by split
// path, compatible with different types of relative paths in
// workbook.xml.rels, for example: worksheets/sheet%d.xml
// and /xl/worksheets/sheet%d.xml
func (f *File) getWorksheetPath(relTarget string) (path string) {
path = filepath.ToSlash(strings.TrimPrefix(
strings.Replace(filepath.Clean(fmt.Sprintf("%s/%s", filepath.Dir(f.getWorkbookPath()), relTarget)), "\\", "/", -1), "/"))
if strings.HasPrefix(relTarget, "/") {
path = filepath.ToSlash(strings.TrimPrefix(strings.Replace(filepath.Clean(relTarget), "\\", "/", -1), "/"))
}
return path
}

// workbookReader provides a function to get the pointer to the workbook.xml
// structure after deserialization.
func (f *File) workbookReader() *xlsxWorkbook {
Expand Down Expand Up @@ -149,6 +164,37 @@ func (f *File) workBookWriter() {
}
}

// mergeExpandedCols merge expanded columns.
func (f *File) mergeExpandedCols(ws *xlsxWorksheet) {
sort.Slice(ws.Cols.Col, func(i, j int) bool {
return ws.Cols.Col[i].Min < ws.Cols.Col[j].Min
})
columns := []xlsxCol{}
for i, n := 0, len(ws.Cols.Col); i < n; {
left := i
for i++; i < n && reflect.DeepEqual(
xlsxCol{
BestFit: ws.Cols.Col[i-1].BestFit,
Collapsed: ws.Cols.Col[i-1].Collapsed,
CustomWidth: ws.Cols.Col[i-1].CustomWidth,
Hidden: ws.Cols.Col[i-1].Hidden,
Max: ws.Cols.Col[i-1].Max + 1,
Min: ws.Cols.Col[i-1].Min + 1,
OutlineLevel: ws.Cols.Col[i-1].OutlineLevel,
Phonetic: ws.Cols.Col[i-1].Phonetic,
Style: ws.Cols.Col[i-1].Style,
Width: ws.Cols.Col[i-1].Width,
}, ws.Cols.Col[i]); i++ {
}
column := deepcopy.Copy(ws.Cols.Col[left]).(xlsxCol)
if left < i-1 {
column.Max = ws.Cols.Col[i-1].Min
}
columns = append(columns, column)
}
ws.Cols.Col = columns
}

// workSheetWriter provides a function to save xl/worksheets/sheet%d.xml after
// serialize structure.
func (f *File) workSheetWriter() {
Expand All @@ -158,6 +204,12 @@ func (f *File) workSheetWriter() {
f.Sheet.Range(func(p, ws interface{}) bool {
if ws != nil {
sheet := ws.(*xlsxWorksheet)
if sheet.MergeCells != nil && len(sheet.MergeCells.Cells) > 0 {
_ = f.mergeOverlapCells(sheet)
}
if sheet.Cols != nil && len(sheet.Cols.Col) > 0 {
f.mergeExpandedCols(sheet)
}
for k, v := range sheet.SheetData.Row {
sheet.SheetData.Row[k].C = trimCell(v.C)
}
Expand Down Expand Up @@ -195,7 +247,7 @@ func trimCell(column []xlsxC) []xlsxC {
i++
}
}
return col[0:i]
return col[:i]
}

// setContentTypes provides a function to read and update property of contents
Expand Down Expand Up @@ -252,7 +304,7 @@ func (f *File) relsWriter() {

// setAppXML update docProps/app.xml file of XML.
func (f *File) setAppXML() {
f.saveFileList("docProps/app.xml", []byte(templateDocpropsApp))
f.saveFileList(dafaultXMLPathDocPropsApp, []byte(templateDocpropsApp))
}

// replaceRelationshipsBytes; Some tools that read spreadsheet files have very
Expand Down Expand Up @@ -322,6 +374,7 @@ func (f *File) GetActiveSheetIndex() (index int) {
for idx, sheet := range wb.Sheets.Sheet {
if sheet.SheetID == sheetID {
index = idx
return
}
}
}
Expand Down Expand Up @@ -374,6 +427,7 @@ func (f *File) GetSheetName(index int) (name string) {
for idx, sheet := range f.GetSheetList() {
if idx == index {
name = sheet
return
}
}
return
Expand All @@ -383,27 +437,25 @@ func (f *File) GetSheetName(index int) (name string) {
// given sheet name. If given worksheet name is invalid, will return an
// integer type value -1.
func (f *File) getSheetID(name string) int {
var ID = -1
for sheetID, sheet := range f.GetSheetMap() {
if sheet == trimSheetName(name) {
ID = sheetID
return sheetID
}
}
return ID
return -1
}

// GetSheetIndex provides a function to get a sheet index of the workbook by
// the given sheet name, the sheet names are not case sensitive. If the given
// sheet name is invalid or sheet doesn't exist, it will return an integer
// type value -1.
func (f *File) GetSheetIndex(name string) int {
var idx = -1
for index, sheet := range f.GetSheetList() {
if strings.EqualFold(sheet, trimSheetName(name)) {
idx = index
return index
}
}
return idx
return -1
}

// GetSheetMap provides a function to get worksheets, chart sheets, dialog
Expand All @@ -413,6 +465,11 @@ func (f *File) GetSheetIndex(name string) int {
// if err != nil {
// return
// }
// defer func() {
// if err := f.Close(); err != nil {
// fmt.Println(err)
// }
// }()
// for index, name := range f.GetSheetMap() {
// fmt.Println(index, name)
// }
Expand Down Expand Up @@ -447,18 +504,13 @@ func (f *File) getSheetMap() map[string]string {
for _, v := range f.workbookReader().Sheets.Sheet {
for _, rel := range f.relsReader(f.getWorkbookRelsPath()).Relationships {
if rel.ID == v.ID {
// Construct a target XML as xl/worksheets/sheet%d by split
// path, compatible with different types of relative paths in
// workbook.xml.rels, for example: worksheets/sheet%d.xml
// and /xl/worksheets/sheet%d.xml
path := filepath.ToSlash(strings.TrimPrefix(
strings.Replace(filepath.Clean(fmt.Sprintf("%s/%s", filepath.Dir(f.getWorkbookPath()), rel.Target)), "\\", "/", -1), "/"))
if strings.HasPrefix(rel.Target, "/") {
path = filepath.ToSlash(strings.TrimPrefix(strings.Replace(filepath.Clean(rel.Target), "\\", "/", -1), "/"))
}
path := f.getWorksheetPath(rel.Target)
if _, ok := f.Pkg.Load(path); ok {
maps[v.Name] = path
}
if _, ok := f.tempFiles.Load(path); ok {
maps[v.Name] = path
}
}
}
}
Expand All @@ -477,7 +529,7 @@ func (f *File) SetSheetBackground(sheet, picture string) error {
if !ok {
return ErrImgExt
}
file, _ := ioutil.ReadFile(picture)
file, _ := ioutil.ReadFile(filepath.Clean(picture))
name := f.addMedia(file, ext)
sheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(f.sheetMap[trimSheetName(sheet)], "xl/worksheets/") + ".rels"
rID := f.addRels(sheetRels, SourceRelationshipImage, strings.Replace(name, "xl", "..", 1), "")
Expand All @@ -503,46 +555,55 @@ func (f *File) DeleteSheet(name string) {
wbRels := f.relsReader(f.getWorkbookRelsPath())
activeSheetName := f.GetSheetName(f.GetActiveSheetIndex())
deleteLocalSheetID := f.GetSheetIndex(name)
// Delete and adjust defined names
if wb.DefinedNames != nil {
for idx := 0; idx < len(wb.DefinedNames.DefinedName); idx++ {
dn := wb.DefinedNames.DefinedName[idx]
if dn.LocalSheetID != nil {
localSheetID := *dn.LocalSheetID
if localSheetID == deleteLocalSheetID {
wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName[:idx], wb.DefinedNames.DefinedName[idx+1:]...)
idx--
} else if localSheetID > deleteLocalSheetID {
wb.DefinedNames.DefinedName[idx].LocalSheetID = intPtr(*dn.LocalSheetID - 1)
deleteAndAdjustDefinedNames(wb, deleteLocalSheetID)

for idx, sheet := range wb.Sheets.Sheet {
if !strings.EqualFold(sheet.Name, sheetName) {
continue
}

wb.Sheets.Sheet = append(wb.Sheets.Sheet[:idx], wb.Sheets.Sheet[idx+1:]...)
var sheetXML, rels string
if wbRels != nil {
for _, rel := range wbRels.Relationships {
if rel.ID == sheet.ID {
sheetXML = f.getWorksheetPath(rel.Target)
rels = "xl/worksheets/_rels/" + strings.TrimPrefix(f.sheetMap[sheetName], "xl/worksheets/") + ".rels"
}
}
}
target := f.deleteSheetFromWorkbookRels(sheet.ID)
f.deleteSheetFromContentTypes(target)
f.deleteCalcChain(sheet.SheetID, "")
delete(f.sheetMap, sheet.Name)
f.Pkg.Delete(sheetXML)
f.Pkg.Delete(rels)
f.Relationships.Delete(rels)
f.Sheet.Delete(sheetXML)
delete(f.xmlAttr, sheetXML)
f.SheetCount--
}
for idx, sheet := range wb.Sheets.Sheet {
if strings.EqualFold(sheet.Name, sheetName) {
wb.Sheets.Sheet = append(wb.Sheets.Sheet[:idx], wb.Sheets.Sheet[idx+1:]...)
var sheetXML, rels string
if wbRels != nil {
for _, rel := range wbRels.Relationships {
if rel.ID == sheet.ID {
sheetXML = rel.Target
rels = "xl/worksheets/_rels/" + strings.TrimPrefix(f.sheetMap[sheetName], "xl/worksheets/") + ".rels"
}
}
f.SetActiveSheet(f.GetSheetIndex(activeSheetName))
}

// deleteAndAdjustDefinedNames delete and adjust defined name in the workbook
// by given worksheet ID.
func deleteAndAdjustDefinedNames(wb *xlsxWorkbook, deleteLocalSheetID int) {
if wb == nil || wb.DefinedNames == nil {
return
}
for idx := 0; idx < len(wb.DefinedNames.DefinedName); idx++ {
dn := wb.DefinedNames.DefinedName[idx]
if dn.LocalSheetID != nil {
localSheetID := *dn.LocalSheetID
if localSheetID == deleteLocalSheetID {
wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName[:idx], wb.DefinedNames.DefinedName[idx+1:]...)
idx--
} else if localSheetID > deleteLocalSheetID {
wb.DefinedNames.DefinedName[idx].LocalSheetID = intPtr(*dn.LocalSheetID - 1)
}
target := f.deleteSheetFromWorkbookRels(sheet.ID)
f.deleteSheetFromContentTypes(target)
f.deleteCalcChain(sheet.SheetID, "")
delete(f.sheetMap, sheet.Name)
f.Pkg.Delete(sheetXML)
f.Pkg.Delete(rels)
f.Relationships.Delete(rels)
f.Sheet.Delete(sheetXML)
delete(f.xmlAttr, sheetXML)
f.SheetCount--
}
}
f.SetActiveSheet(f.GetSheetIndex(activeSheetName))
}

// deleteSheetFromWorkbookRels provides a function to remove worksheet
Expand Down Expand Up @@ -652,13 +713,13 @@ func (f *File) SetSheetVisible(name string, visible bool) error {
}
}
for k, v := range content.Sheets.Sheet {
xlsx, err := f.workSheetReader(v.Name)
ws, err := f.workSheetReader(v.Name)
if err != nil {
return err
}
tabSelected := false
if len(xlsx.SheetViews.SheetView) > 0 {
tabSelected = xlsx.SheetViews.SheetView[0].TabSelected
if len(ws.SheetViews.SheetView) > 0 {
tabSelected = ws.SheetViews.SheetView[0].TabSelected
}
if v.Name == name && count > 1 && !tabSelected {
content.Sheets.Sheet[k].State = "hidden"
Expand Down Expand Up @@ -855,7 +916,7 @@ func (f *File) searchSheet(name, value string, regSearch bool) (result []string,
)

d = f.sharedStringsReader()
decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
decoder := f.xmlNewDecoder(bytes.NewReader(f.readBytes(name)))
for {
var token xml.Token
token, err = decoder.Token()
Expand All @@ -877,7 +938,7 @@ func (f *File) searchSheet(name, value string, regSearch bool) (result []string,
if inElement == "c" {
colCell := xlsxC{}
_ = decoder.DecodeElement(&colCell, &xmlElement)
val, _ := colCell.getValueFrom(f, d)
val, _ := colCell.getValueFrom(f, d, false)
if regSearch {
regex := regexp.MustCompile(value)
if !regex.MatchString(val) {
Expand Down Expand Up @@ -1048,8 +1109,8 @@ func (f *File) SetHeaderFooter(sheet string, settings *FormatHeaderFooter) error
// Check 6 string type fields: OddHeader, OddFooter, EvenHeader, EvenFooter,
// FirstFooter, FirstHeader
for i := 4; i < v.NumField()-1; i++ {
if v.Field(i).Len() >= 255 {
return fmt.Errorf("field %s must be less than 255 characters", v.Type().Field(i).Name)
if len(utf16.Encode([]rune(v.Field(i).String()))) > MaxFieldLength {
return newFieldLengthError(v.Type().Field(i).Name)
}
}
ws.HeaderFooter = &xlsxHeaderFooter{
Expand Down
54 changes: 48 additions & 6 deletions sheet_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -189,12 +189,31 @@ func TestSearchSheet(t *testing.T) {
result, err = f.SearchSheet("Sheet1", "[0-9]", true)
assert.NoError(t, err)
assert.EqualValues(t, expected, result)
assert.NoError(t, f.Close())

// Test search worksheet data after set cell value
f = NewFile()
assert.NoError(t, f.SetCellValue("Sheet1", "A1", true))
_, err = f.SearchSheet("Sheet1", "")
assert.NoError(t, err)

f = NewFile()
f.Sheet.Delete("xl/worksheets/sheet1.xml")
f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(`<worksheet><sheetData><row r="A"><c r="2" t="str"><v>A</v></c></row></sheetData></worksheet>`))
f.checked = nil
result, err = f.SearchSheet("Sheet1", "A")
assert.EqualError(t, err, "strconv.Atoi: parsing \"A\": invalid syntax")
assert.Equal(t, []string(nil), result)

f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(`<worksheet><sheetData><row r="2"><c r="A" t="str"><v>A</v></c></row></sheetData></worksheet>`))
result, err = f.SearchSheet("Sheet1", "A")
assert.EqualError(t, err, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
assert.Equal(t, []string(nil), result)

f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(`<worksheet><sheetData><row r="0"><c r="A1" t="str"><v>A</v></c></row></sheetData></worksheet>`))
result, err = f.SearchSheet("Sheet1", "A")
assert.EqualError(t, err, "invalid cell coordinates [1, 0]")
assert.Equal(t, []string(nil), result)
}

func TestSetPageLayout(t *testing.T) {
Expand All @@ -216,10 +235,18 @@ func TestSetHeaderFooter(t *testing.T) {
assert.EqualError(t, f.SetHeaderFooter("SheetN", nil), "sheet SheetN is not exist")
// Test set header and footer with illegal setting.
assert.EqualError(t, f.SetHeaderFooter("Sheet1", &FormatHeaderFooter{
OddHeader: strings.Repeat("c", 256),
}), "field OddHeader must be less than 255 characters")
OddHeader: strings.Repeat("c", MaxFieldLength+1),
}), newFieldLengthError("OddHeader").Error())

assert.NoError(t, f.SetHeaderFooter("Sheet1", nil))
text := strings.Repeat("一", MaxFieldLength)
assert.NoError(t, f.SetHeaderFooter("Sheet1", &FormatHeaderFooter{
OddHeader: text,
OddFooter: text,
EvenHeader: text,
EvenFooter: text,
FirstHeader: text,
}))
assert.NoError(t, f.SetHeaderFooter("Sheet1", &FormatHeaderFooter{
DifferentFirst: true,
DifferentOddEven: true,
Expand Down Expand Up @@ -249,10 +276,10 @@ func TestDefinedName(t *testing.T) {
Name: "Amount",
RefersTo: "Sheet1!$A$2:$D$5",
Comment: "defined name comment",
}), "the same name already exists on the scope")
}), ErrDefinedNameduplicate.Error())
assert.EqualError(t, f.DeleteDefinedName(&DefinedName{
Name: "No Exist Defined Name",
}), "no defined name on the scope")
}), ErrDefinedNameScope.Error())
assert.Exactly(t, "Sheet1!$A$2:$D$5", f.GetDefinedName()[1].RefersTo)
assert.NoError(t, f.DeleteDefinedName(&DefinedName{
Name: "Amount",
Expand Down Expand Up @@ -289,7 +316,7 @@ func TestInsertPageBreak(t *testing.T) {
assert.NoError(t, f.InsertPageBreak("Sheet1", "B2"))
assert.NoError(t, f.InsertPageBreak("Sheet1", "C3"))
assert.NoError(t, f.InsertPageBreak("Sheet1", "C3"))
assert.EqualError(t, f.InsertPageBreak("Sheet1", "A"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, f.InsertPageBreak("Sheet1", "A"), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
assert.EqualError(t, f.InsertPageBreak("SheetN", "C3"), "sheet SheetN is not exist")
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestInsertPageBreak.xlsx")))
}
Expand All @@ -315,7 +342,7 @@ func TestRemovePageBreak(t *testing.T) {
assert.NoError(t, f.InsertPageBreak("Sheet2", "C2"))
assert.NoError(t, f.RemovePageBreak("Sheet2", "B2"))

assert.EqualError(t, f.RemovePageBreak("Sheet1", "A"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, f.RemovePageBreak("Sheet1", "A"), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
assert.EqualError(t, f.RemovePageBreak("SheetN", "C3"), "sheet SheetN is not exist")
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestRemovePageBreak.xlsx")))
}
Expand All @@ -327,6 +354,7 @@ func TestGetSheetName(t *testing.T) {
assert.Equal(t, "Sheet2", f.GetSheetName(1))
assert.Equal(t, "", f.GetSheetName(-1))
assert.Equal(t, "", f.GetSheetName(2))
assert.NoError(t, f.Close())
}

func TestGetSheetMap(t *testing.T) {
Expand All @@ -341,6 +369,7 @@ func TestGetSheetMap(t *testing.T) {
assert.Equal(t, expectedMap[idx], name)
}
assert.Equal(t, len(sheetMap), 2)
assert.NoError(t, f.Close())
}

func TestSetActiveSheet(t *testing.T) {
Expand All @@ -359,6 +388,14 @@ func TestSetActiveSheet(t *testing.T) {
f = NewFile()
f.SetActiveSheet(-1)
assert.Equal(t, f.GetActiveSheetIndex(), 0)

f = NewFile()
f.WorkBook.BookViews = nil
idx := f.NewSheet("Sheet2")
ws, ok = f.Sheet.Load("xl/worksheets/sheet2.xml")
assert.True(t, ok)
ws.(*xlsxWorksheet).SheetViews = &xlsxSheetViews{SheetView: []xlsxSheetView{}}
f.SetActiveSheet(idx)
}

func TestSetSheetName(t *testing.T) {
Expand Down Expand Up @@ -403,6 +440,11 @@ func TestDeleteSheet(t *testing.T) {
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestDeleteSheet2.xlsx")))
}

func TestDeleteAndAdjustDefinedNames(t *testing.T) {
deleteAndAdjustDefinedNames(nil, 0)
deleteAndAdjustDefinedNames(&xlsxWorkbook{}, 0)
}

func BenchmarkNewSheet(b *testing.B) {
b.RunParallel(func(pb *testing.PB) {
for pb.Next() {
Expand Down
10 changes: 5 additions & 5 deletions sheetpr.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -182,14 +182,14 @@ func (o *AutoPageBreaks) getSheetPrOption(pr *xlsxSheetPr) {
// AutoPageBreaks(bool)
// OutlineSummaryBelow(bool)
func (f *File) SetSheetPrOptions(name string, opts ...SheetPrOption) error {
sheet, err := f.workSheetReader(name)
ws, err := f.workSheetReader(name)
if err != nil {
return err
}
pr := sheet.SheetPr
pr := ws.SheetPr
if pr == nil {
pr = new(xlsxSheetPr)
sheet.SheetPr = pr
ws.SheetPr = pr
}

for _, opt := range opts {
Expand All @@ -208,11 +208,11 @@ func (f *File) SetSheetPrOptions(name string, opts ...SheetPrOption) error {
// AutoPageBreaks(bool)
// OutlineSummaryBelow(bool)
func (f *File) GetSheetPrOptions(name string, opts ...SheetPrOptionPtr) error {
sheet, err := f.workSheetReader(name)
ws, err := f.workSheetReader(name)
if err != nil {
return err
}
pr := sheet.SheetPr
pr := ws.SheetPr

for _, opt := range opts {
opt.getSheetPrOption(pr)
Expand Down
18 changes: 9 additions & 9 deletions sparkline.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -13,7 +13,6 @@ package excelize

import (
"encoding/xml"
"errors"
"io"
"strings"
)
Expand Down Expand Up @@ -410,7 +409,7 @@ func (f *File) AddSparkline(sheet string, opt *SparklineOption) (err error) {
sparkTypes = map[string]string{"line": "line", "column": "column", "win_loss": "stacked"}
if opt.Type != "" {
if specifiedSparkTypes, ok = sparkTypes[opt.Type]; !ok {
err = errors.New("parameter 'Type' must be 'line', 'column' or 'win_loss'")
err = ErrSparklineType
return
}
sparkType = specifiedSparkTypes
Expand Down Expand Up @@ -470,17 +469,17 @@ func (f *File) parseFormatAddSparklineSet(sheet string, opt *SparklineOption) (*
return ws, ErrParameterRequired
}
if len(opt.Location) < 1 {
return ws, errors.New("parameter 'Location' is required")
return ws, ErrSparklineLocation
}
if len(opt.Range) < 1 {
return ws, errors.New("parameter 'Range' is required")
return ws, ErrSparklineRange
}
// The ranges and locations must match.\
if len(opt.Location) != len(opt.Range) {
return ws, errors.New(`must have the same number of 'Location' and 'Range' parameters`)
return ws, ErrSparkline
}
if opt.Style < 0 || opt.Style > 35 {
return ws, errors.New("parameter 'Style' must betweent 0-35")
return ws, ErrSparklineStyle
}
if ws.ExtLst == nil {
ws.ExtLst = &xlsxExtLst{}
Expand Down Expand Up @@ -524,10 +523,11 @@ func (f *File) appendSparkline(ws *xlsxWorksheet, group *xlsxX14SparklineGroup,
if sparklineGroupBytes, err = xml.Marshal(group); err != nil {
return
}
groups = &xlsxX14SparklineGroups{
XMLNSXM: NameSpaceSpreadSheetExcel2006Main.Value,
Content: decodeSparklineGroups.Content + string(sparklineGroupBytes),
if groups == nil {
groups = &xlsxX14SparklineGroups{}
}
groups.XMLNSXM = NameSpaceSpreadSheetExcel2006Main.Value
groups.Content = decodeSparklineGroups.Content + string(sparklineGroupBytes)
if sparklineGroupsBytes, err = xml.Marshal(groups); err != nil {
return
}
Expand Down
14 changes: 7 additions & 7 deletions sparkline_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -220,38 +220,38 @@ func TestAddSparkline(t *testing.T) {
Range: []string{"Sheet2!A3:E3"},
}), "sheet SheetN is not exist")

assert.EqualError(t, f.AddSparkline("Sheet1", nil), "parameter is required")
assert.EqualError(t, f.AddSparkline("Sheet1", nil), ErrParameterRequired.Error())

assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
Range: []string{"Sheet2!A3:E3"},
}), `parameter 'Location' is required`)
}), ErrSparklineLocation.Error())

assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
Location: []string{"F3"},
}), `parameter 'Range' is required`)
}), ErrSparklineRange.Error())

assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
Location: []string{"F2", "F3"},
Range: []string{"Sheet2!A3:E3"},
}), `must have the same number of 'Location' and 'Range' parameters`)
}), ErrSparkline.Error())

assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
Location: []string{"F3"},
Range: []string{"Sheet2!A3:E3"},
Type: "unknown_type",
}), `parameter 'Type' must be 'line', 'column' or 'win_loss'`)
}), ErrSparklineType.Error())

assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
Location: []string{"F3"},
Range: []string{"Sheet2!A3:E3"},
Style: -1,
}), `parameter 'Style' must betweent 0-35`)
}), ErrSparklineStyle.Error())

assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
Location: []string{"F3"},
Range: []string{"Sheet2!A3:E3"},
Style: -1,
}), `parameter 'Style' must betweent 0-35`)
}), ErrSparklineStyle.Error())

ws, ok := f.Sheet.Load("xl/worksheets/sheet1.xml")
assert.True(t, ok)
Expand Down
34 changes: 25 additions & 9 deletions stream.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -84,6 +84,12 @@ type StreamWriter struct {
// excelize.Cell{Value: 2},
// excelize.Cell{Formula: "SUM(A1,B1)"}});
//
// Set cell value and rows style for a worksheet with stream writer:
//
// err := streamWriter.SetRow("A1", []interface{}{
// excelize.Cell{Value: 1}},
// excelize.RowOpts{StyleID: styleID, Height: 20, Hidden: false});
//
func (f *File) NewStreamWriter(sheet string) (*StreamWriter, error) {
sheetID := f.getSheetID(sheet)
if sheetID == -1 {
Expand All @@ -106,7 +112,7 @@ func (f *File) NewStreamWriter(sheet string) (*StreamWriter, error) {
}
f.streams[sheetPath] = sw

_, _ = sw.rawData.WriteString(XMLHeader + `<worksheet` + templateNamespaceIDMap)
_, _ = sw.rawData.WriteString(xml.Header + `<worksheet` + templateNamespaceIDMap)
bulkAppendFields(&sw.rawData, sw.worksheet, 2, 5)
return sw, err
}
Expand Down Expand Up @@ -289,10 +295,12 @@ type Cell struct {
Value interface{}
}

// RowOpts define the options for set row.
// RowOpts define the options for the set row, it can be used directly in
// StreamWriter.SetRow to specify the style and properties of the row.
type RowOpts struct {
Height float64
Hidden bool
Height float64
Hidden bool
StyleID int
}

// SetRow writes an array to stream rows by giving a worksheet name, starting
Expand Down Expand Up @@ -333,7 +341,7 @@ func (sw *StreamWriter) SetRow(axis string, values []interface{}, opts ...RowOpt
val = v.Value
setCellFormula(&c, v.Formula)
}
if err = setCellValFunc(&c, val); err != nil {
if err = sw.setCellValFunc(&c, val); err != nil {
_, _ = sw.rawData.WriteString(`</row>`)
return err
}
Expand All @@ -346,8 +354,8 @@ func (sw *StreamWriter) SetRow(axis string, values []interface{}, opts ...RowOpt
// marshalRowAttrs prepare attributes of the row by given options.
func marshalRowAttrs(opts ...RowOpts) (attrs string, err error) {
var opt *RowOpts
for _, o := range opts {
opt = &o
for i := range opts {
opt = &opts[i]
}
if opt == nil {
return
Expand All @@ -356,6 +364,9 @@ func marshalRowAttrs(opts ...RowOpts) (attrs string, err error) {
err = ErrMaxRowHeight
return
}
if opt.StyleID > 0 {
attrs += fmt.Sprintf(` s="%d" customFormat="true"`, opt.StyleID)
}
if opt.Height > 0 {
attrs += fmt.Sprintf(` ht="%v" customHeight="true"`, opt.Height)
}
Expand Down Expand Up @@ -413,7 +424,7 @@ func setCellFormula(c *xlsxC, formula string) {
}

// setCellValFunc provides a function to set value of a cell.
func setCellValFunc(c *xlsxC, val interface{}) (err error) {
func (sw *StreamWriter) setCellValFunc(c *xlsxC, val interface{}) (err error) {
switch val := val.(type) {
case int, int8, int16, int32, int64, uint, uint8, uint16, uint32, uint64:
err = setCellIntFunc(c, val)
Expand All @@ -428,7 +439,12 @@ func setCellValFunc(c *xlsxC, val interface{}) (err error) {
case time.Duration:
c.T, c.V = setCellDuration(val)
case time.Time:
c.T, c.V, _, err = setCellTime(val)
var isNum bool
c.T, c.V, isNum, err = setCellTime(val)
if isNum && c.S == 0 {
style, _ := sw.File.NewStyle(&Style{NumFmt: 22})
c.S = style
}
case bool:
c.T, c.V = setCellBool(val)
case nil:
Expand Down
68 changes: 43 additions & 25 deletions stream_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -55,10 +55,10 @@ func TestStreamWriter(t *testing.T) {
// Test set cell with style.
styleID, err := file.NewStyle(`{"font":{"color":"#777777"}}`)
assert.NoError(t, err)
assert.NoError(t, streamWriter.SetRow("A4", []interface{}{Cell{StyleID: styleID}, Cell{Formula: "SUM(A10,B10)"}}), RowOpts{Height: 45})
assert.NoError(t, streamWriter.SetRow("A4", []interface{}{Cell{StyleID: styleID}, Cell{Formula: "SUM(A10,B10)"}}), RowOpts{Height: 45, StyleID: styleID})
assert.NoError(t, streamWriter.SetRow("A5", []interface{}{&Cell{StyleID: styleID, Value: "cell"}, &Cell{Formula: "SUM(A10,B10)"}}))
assert.NoError(t, streamWriter.SetRow("A6", []interface{}{time.Now()}))
assert.NoError(t, streamWriter.SetRow("A7", nil, RowOpts{Hidden: true}))
assert.NoError(t, streamWriter.SetRow("A7", nil, RowOpts{Height: 20, Hidden: true, StyleID: styleID}))
assert.EqualError(t, streamWriter.SetRow("A7", nil, RowOpts{Height: MaxRowHeight + 1}), ErrMaxRowHeight.Error())

for rowID := 10; rowID <= 51200; rowID++ {
Expand Down Expand Up @@ -115,6 +115,21 @@ func TestStreamWriter(t *testing.T) {
cellValue, err := file.GetCellValue("Sheet1", "A1")
assert.NoError(t, err)
assert.Equal(t, "Data", cellValue)

// Test stream reader for a worksheet with huge amounts of data.
file, err = OpenFile(filepath.Join("test", "TestStreamWriter.xlsx"))
assert.NoError(t, err)
rows, err := file.Rows("Sheet1")
assert.NoError(t, err)
cells := 0
for rows.Next() {
row, err := rows.Columns()
assert.NoError(t, err)
cells += len(row)
}
assert.NoError(t, rows.Close())
assert.Equal(t, 2559558, cells)
assert.NoError(t, file.Close())
}

func TestStreamSetColWidth(t *testing.T) {
Expand Down Expand Up @@ -159,8 +174,8 @@ func TestStreamTable(t *testing.T) {
// Test add table with illegal formatset.
assert.EqualError(t, streamWriter.AddTable("B26", "A21", `{x}`), "invalid character 'x' looking for beginning of object key string")
// Test add table with illegal cell coordinates.
assert.EqualError(t, streamWriter.AddTable("A", "B1", `{}`), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, streamWriter.AddTable("A1", "B", `{}`), `cannot convert cell "B" to coordinates: invalid cell name "B"`)
assert.EqualError(t, streamWriter.AddTable("A", "B1", `{}`), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
assert.EqualError(t, streamWriter.AddTable("A1", "B", `{}`), newCellNameToCoordinatesError("B", newInvalidCellNameError("B")).Error())
}

func TestStreamMergeCells(t *testing.T) {
Expand All @@ -169,7 +184,7 @@ func TestStreamMergeCells(t *testing.T) {
assert.NoError(t, err)
assert.NoError(t, streamWriter.MergeCell("A1", "D1"))
// Test merge cells with illegal cell coordinates.
assert.EqualError(t, streamWriter.MergeCell("A", "D1"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
assert.EqualError(t, streamWriter.MergeCell("A", "D1"), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
assert.NoError(t, streamWriter.Flush())
// Save spreadsheet by the given path.
assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamMergeCells.xlsx")))
Expand All @@ -189,28 +204,31 @@ func TestSetRow(t *testing.T) {
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("A", []interface{}{}), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
}

func TestSetCellValFunc(t *testing.T) {
f := NewFile()
sw, err := f.NewStreamWriter("Sheet1")
assert.NoError(t, err)
c := &xlsxC{}
assert.NoError(t, setCellValFunc(c, 128))
assert.NoError(t, setCellValFunc(c, int8(-128)))
assert.NoError(t, setCellValFunc(c, int16(-32768)))
assert.NoError(t, setCellValFunc(c, int32(-2147483648)))
assert.NoError(t, setCellValFunc(c, int64(-9223372036854775808)))
assert.NoError(t, setCellValFunc(c, uint(128)))
assert.NoError(t, setCellValFunc(c, uint8(255)))
assert.NoError(t, setCellValFunc(c, uint16(65535)))
assert.NoError(t, setCellValFunc(c, uint32(4294967295)))
assert.NoError(t, setCellValFunc(c, uint64(18446744073709551615)))
assert.NoError(t, setCellValFunc(c, float32(100.1588)))
assert.NoError(t, setCellValFunc(c, float64(100.1588)))
assert.NoError(t, setCellValFunc(c, " Hello"))
assert.NoError(t, setCellValFunc(c, []byte(" Hello")))
assert.NoError(t, setCellValFunc(c, time.Now().UTC()))
assert.NoError(t, setCellValFunc(c, time.Duration(1e13)))
assert.NoError(t, setCellValFunc(c, true))
assert.NoError(t, setCellValFunc(c, nil))
assert.NoError(t, setCellValFunc(c, complex64(5+10i)))
assert.NoError(t, sw.setCellValFunc(c, 128))
assert.NoError(t, sw.setCellValFunc(c, int8(-128)))
assert.NoError(t, sw.setCellValFunc(c, int16(-32768)))
assert.NoError(t, sw.setCellValFunc(c, int32(-2147483648)))
assert.NoError(t, sw.setCellValFunc(c, int64(-9223372036854775808)))
assert.NoError(t, sw.setCellValFunc(c, uint(128)))
assert.NoError(t, sw.setCellValFunc(c, uint8(255)))
assert.NoError(t, sw.setCellValFunc(c, uint16(65535)))
assert.NoError(t, sw.setCellValFunc(c, uint32(4294967295)))
assert.NoError(t, sw.setCellValFunc(c, uint64(18446744073709551615)))
assert.NoError(t, sw.setCellValFunc(c, float32(100.1588)))
assert.NoError(t, sw.setCellValFunc(c, float64(100.1588)))
assert.NoError(t, sw.setCellValFunc(c, " Hello"))
assert.NoError(t, sw.setCellValFunc(c, []byte(" Hello")))
assert.NoError(t, sw.setCellValFunc(c, time.Now().UTC()))
assert.NoError(t, sw.setCellValFunc(c, time.Duration(1e13)))
assert.NoError(t, sw.setCellValFunc(c, true))
assert.NoError(t, sw.setCellValFunc(c, nil))
assert.NoError(t, sw.setCellValFunc(c, complex64(5+10i)))
}
Loading