From 08d1a86c3a1bffdf431dba6a3d5a3b369ef740a7 Mon Sep 17 00:00:00 2001 From: xuri Date: Tue, 10 Dec 2019 00:16:17 +0800 Subject: Fix #523, add stream writer for generate new worksheet with huge amounts of data --- stream_test.go | 66 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 66 insertions(+) create mode 100644 stream_test.go (limited to 'stream_test.go') diff --git a/stream_test.go b/stream_test.go new file mode 100644 index 0000000..97c55a7 --- /dev/null +++ b/stream_test.go @@ -0,0 +1,66 @@ +package excelize + +import ( + "math/rand" + "path/filepath" + "strings" + "testing" + + "github.com/stretchr/testify/assert" +) + +func TestStreamWriter(t *testing.T) { + file := NewFile() + streamWriter, err := file.NewStreamWriter("Sheet1") + assert.NoError(t, err) + + // Test max characters in a cell. + row := make([]interface{}, 1) + row[0] = strings.Repeat("c", 32769) + assert.NoError(t, streamWriter.SetRow("A1", &row)) + + // Test leading and ending space(s) character characters in a cell. + row = make([]interface{}, 1) + row[0] = " characters" + assert.NoError(t, streamWriter.SetRow("A2", &row)) + + row = make([]interface{}, 1) + row[0] = []byte("Word") + assert.NoError(t, streamWriter.SetRow("A3", &row)) + + for rowID := 10; rowID <= 51200; rowID++ { + row := make([]interface{}, 50) + for colID := 0; colID < 50; colID++ { + row[colID] = rand.Intn(640000) + } + cell, _ := CoordinatesToCellName(1, rowID) + assert.NoError(t, streamWriter.SetRow(cell, &row)) + } + + err = streamWriter.Flush() + assert.NoError(t, err) + // Save xlsx file by the given path. + assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriter.xlsx"))) + + // Test error exceptions + streamWriter, err = file.NewStreamWriter("SheetN") + assert.EqualError(t, err, "sheet SheetN is not exist") +} + +func TestFlush(t *testing.T) { + // Test error exceptions + file := NewFile() + streamWriter, err := file.NewStreamWriter("Sheet1") + assert.NoError(t, err) + streamWriter.Sheet = "SheetN" + assert.EqualError(t, streamWriter.Flush(), "sheet SheetN is not exist") +} + +func TestSetRow(t *testing.T) { + // Test error exceptions + file := NewFile() + streamWriter, err := file.NewStreamWriter("Sheet1") + assert.NoError(t, err) + assert.EqualError(t, streamWriter.SetRow("A", &[]interface{}{}), `cannot convert cell "A" to coordinates: invalid cell name "A"`) + assert.EqualError(t, streamWriter.SetRow("A1", []interface{}{}), `pointer to slice expected`) +} -- cgit v1.2.1 From ae2865d9237cfd27d7bc4fbef3870b3361597be8 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 22 Dec 2019 00:02:09 +0800 Subject: Improve code coverage unit tests --- stream_test.go | 18 ++++++++++++++++-- 1 file changed, 16 insertions(+), 2 deletions(-) (limited to 'stream_test.go') diff --git a/stream_test.go b/stream_test.go index 97c55a7..8371a4e 100644 --- a/stream_test.go +++ b/stream_test.go @@ -37,8 +37,7 @@ func TestStreamWriter(t *testing.T) { assert.NoError(t, streamWriter.SetRow(cell, &row)) } - err = streamWriter.Flush() - assert.NoError(t, err) + assert.NoError(t, streamWriter.Flush()) // Save xlsx file by the given path. assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriter.xlsx"))) @@ -54,6 +53,21 @@ func TestFlush(t *testing.T) { assert.NoError(t, err) streamWriter.Sheet = "SheetN" assert.EqualError(t, streamWriter.Flush(), "sheet SheetN is not exist") + + // Test close temporary file error + file = NewFile() + streamWriter, err = file.NewStreamWriter("Sheet1") + assert.NoError(t, err) + for rowID := 10; rowID <= 51200; rowID++ { + row := make([]interface{}, 50) + for colID := 0; colID < 50; colID++ { + row[colID] = rand.Intn(640000) + } + cell, _ := CoordinatesToCellName(1, rowID) + assert.NoError(t, streamWriter.SetRow(cell, &row)) + } + assert.NoError(t, streamWriter.tmpFile.Close()) + assert.Error(t, streamWriter.Flush()) } func TestSetRow(t *testing.T) { -- cgit v1.2.1 From 4e4a5b9b3e052d1694442515492792fb1aa74c5a Mon Sep 17 00:00:00 2001 From: xuri Date: Mon, 23 Dec 2019 00:07:40 +0800 Subject: Improve compatibility, fix workbook's rels ID calc error --- stream_test.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'stream_test.go') diff --git a/stream_test.go b/stream_test.go index 8371a4e..4482bd1 100644 --- a/stream_test.go +++ b/stream_test.go @@ -42,7 +42,7 @@ func TestStreamWriter(t *testing.T) { assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriter.xlsx"))) // Test error exceptions - streamWriter, err = file.NewStreamWriter("SheetN") + _, err = file.NewStreamWriter("SheetN") assert.EqualError(t, err, "sheet SheetN is not exist") } -- cgit v1.2.1 From 5c87effc7e6c97fff36a56dea1afac8a2f06fb37 Mon Sep 17 00:00:00 2001 From: Cameron Howey Date: Sat, 28 Dec 2019 20:45:10 -0800 Subject: Stream to Excel table (#530) * Support all datatypes for StreamWriter * Support setting styles with StreamWriter **NOTE:** This is a breaking change. Values are now explicitly passed as a []interface{} for simplicity. We also let styles to be set at the same time. * Create function to write stream into a table * Write rows directly to buffer Avoiding the xml.Encoder makes the streamer faster and use less memory. Using the included benchmark, the results went from: > BenchmarkStreamWriter-4 514 2576155 ns/op 454918 B/op 6592 allocs/op down to: > BenchmarkStreamWriter-4 1614 777480 ns/op 147608 B/op 5570 allocs/op * Use AddTable instead of SetTable This requires reading the cells after they have been written, which requires additional structure for the temp file. As a bonus, we now efficiently allocate only one buffer when reading the file back into memory, using the same approach as ioutil.ReadFile. * Use an exported Cell type to handle inline styles for StreamWriter --- stream_test.go | 82 +++++++++++++++++++++++++++++++++++++++++++--------------- 1 file changed, 61 insertions(+), 21 deletions(-) (limited to 'stream_test.go') diff --git a/stream_test.go b/stream_test.go index 4482bd1..015f64b 100644 --- a/stream_test.go +++ b/stream_test.go @@ -1,6 +1,8 @@ package excelize import ( + "encoding/xml" + "fmt" "math/rand" "path/filepath" "strings" @@ -9,6 +11,25 @@ import ( "github.com/stretchr/testify/assert" ) +func BenchmarkStreamWriter(b *testing.B) { + file := NewFile() + + row := make([]interface{}, 10) + for colID := 0; colID < 10; colID++ { + row[colID] = colID + } + + for n := 0; n < b.N; n++ { + streamWriter, _ := file.NewStreamWriter("Sheet1") + for rowID := 10; rowID <= 110; rowID++ { + cell, _ := CoordinatesToCellName(1, rowID) + streamWriter.SetRow(cell, row) + } + } + + b.ReportAllocs() +} + func TestStreamWriter(t *testing.T) { file := NewFile() streamWriter, err := file.NewStreamWriter("Sheet1") @@ -17,16 +38,16 @@ func TestStreamWriter(t *testing.T) { // Test max characters in a cell. row := make([]interface{}, 1) row[0] = strings.Repeat("c", 32769) - assert.NoError(t, streamWriter.SetRow("A1", &row)) + assert.NoError(t, streamWriter.SetRow("A1", row)) // Test leading and ending space(s) character characters in a cell. row = make([]interface{}, 1) row[0] = " characters" - assert.NoError(t, streamWriter.SetRow("A2", &row)) + assert.NoError(t, streamWriter.SetRow("A2", row)) row = make([]interface{}, 1) row[0] = []byte("Word") - assert.NoError(t, streamWriter.SetRow("A3", &row)) + assert.NoError(t, streamWriter.SetRow("A3", row)) for rowID := 10; rowID <= 51200; rowID++ { row := make([]interface{}, 50) @@ -34,26 +55,13 @@ func TestStreamWriter(t *testing.T) { row[colID] = rand.Intn(640000) } cell, _ := CoordinatesToCellName(1, rowID) - assert.NoError(t, streamWriter.SetRow(cell, &row)) + assert.NoError(t, streamWriter.SetRow(cell, row)) } assert.NoError(t, streamWriter.Flush()) // Save xlsx file by the given path. assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriter.xlsx"))) - // Test error exceptions - _, err = file.NewStreamWriter("SheetN") - assert.EqualError(t, err, "sheet SheetN is not exist") -} - -func TestFlush(t *testing.T) { - // Test error exceptions - file := NewFile() - streamWriter, err := file.NewStreamWriter("Sheet1") - assert.NoError(t, err) - streamWriter.Sheet = "SheetN" - assert.EqualError(t, streamWriter.Flush(), "sheet SheetN is not exist") - // Test close temporary file error file = NewFile() streamWriter, err = file.NewStreamWriter("Sheet1") @@ -64,17 +72,49 @@ func TestFlush(t *testing.T) { row[colID] = rand.Intn(640000) } cell, _ := CoordinatesToCellName(1, rowID) - assert.NoError(t, streamWriter.SetRow(cell, &row)) + assert.NoError(t, streamWriter.SetRow(cell, row)) } - assert.NoError(t, streamWriter.tmpFile.Close()) + assert.NoError(t, streamWriter.rawData.Close()) assert.Error(t, streamWriter.Flush()) } +func TestStreamTable(t *testing.T) { + file := NewFile() + streamWriter, err := file.NewStreamWriter("Sheet1") + assert.NoError(t, err) + + // Write some rows. We want enough rows to force a temp file (>16MB). + assert.NoError(t, streamWriter.SetRow("A1", []interface{}{"A", "B", "C"})) + row := []interface{}{1, 2, 3} + for r := 2; r < 10000; r++ { + assert.NoError(t, streamWriter.SetRow(fmt.Sprintf("A%d", r), row)) + } + + // Write a table. + assert.NoError(t, streamWriter.AddTable("A1", "C2", ``)) + assert.NoError(t, streamWriter.Flush()) + + // Verify the table has names. + var table xlsxTable + assert.NoError(t, xml.Unmarshal(file.XLSX["xl/tables/table1.xml"], &table)) + assert.Equal(t, "A", table.TableColumns.TableColumn[0].Name) + assert.Equal(t, "B", table.TableColumns.TableColumn[1].Name) + assert.Equal(t, "C", table.TableColumns.TableColumn[2].Name) +} + +func TestNewStreamWriter(t *testing.T) { + // Test error exceptions + file := NewFile() + _, err := file.NewStreamWriter("Sheet1") + assert.NoError(t, err) + _, err = file.NewStreamWriter("SheetN") + assert.EqualError(t, err, "sheet SheetN is not exist") +} + func TestSetRow(t *testing.T) { // Test error exceptions file := NewFile() streamWriter, err := file.NewStreamWriter("Sheet1") assert.NoError(t, err) - assert.EqualError(t, streamWriter.SetRow("A", &[]interface{}{}), `cannot convert cell "A" to coordinates: invalid cell name "A"`) - assert.EqualError(t, streamWriter.SetRow("A1", []interface{}{}), `pointer to slice expected`) + assert.EqualError(t, streamWriter.SetRow("A", []interface{}{}), `cannot convert cell "A" to coordinates: invalid cell name "A"`) } -- cgit v1.2.1 From 09485b3f9f0aefc58d51462aed65c2416205c591 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 29 Dec 2019 16:02:31 +0800 Subject: Improve code coverage unit tests --- stream_test.go | 49 ++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 48 insertions(+), 1 deletion(-) (limited to 'stream_test.go') diff --git a/stream_test.go b/stream_test.go index 015f64b..8c5e7ea 100644 --- a/stream_test.go +++ b/stream_test.go @@ -3,10 +3,13 @@ package excelize import ( "encoding/xml" "fmt" + "io/ioutil" "math/rand" + "os" "path/filepath" "strings" "testing" + "time" "github.com/stretchr/testify/assert" ) @@ -49,6 +52,13 @@ func TestStreamWriter(t *testing.T) { row[0] = []byte("Word") assert.NoError(t, streamWriter.SetRow("A3", row)) + // 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}})) + assert.NoError(t, streamWriter.SetRow("A5", []interface{}{&Cell{StyleID: styleID, Value: "cell"}})) + assert.EqualError(t, streamWriter.SetRow("A6", []interface{}{time.Now()}), "only UTC time expected") + for rowID := 10; rowID <= 51200; rowID++ { row := make([]interface{}, 50) for colID := 0; colID < 50; colID++ { @@ -62,7 +72,7 @@ func TestStreamWriter(t *testing.T) { // Save xlsx file by the given path. assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriter.xlsx"))) - // Test close temporary file error + // Test close temporary file error. file = NewFile() streamWriter, err = file.NewStreamWriter("Sheet1") assert.NoError(t, err) @@ -76,6 +86,12 @@ func TestStreamWriter(t *testing.T) { } assert.NoError(t, streamWriter.rawData.Close()) assert.Error(t, streamWriter.Flush()) + + streamWriter.rawData.tmp, err = ioutil.TempFile(os.TempDir(), "excelize-") + assert.NoError(t, err) + _, err = streamWriter.rawData.Reader() + assert.NoError(t, err) + assert.NoError(t, os.Remove(streamWriter.rawData.tmp.Name())) } func TestStreamTable(t *testing.T) { @@ -100,6 +116,14 @@ func TestStreamTable(t *testing.T) { assert.Equal(t, "A", table.TableColumns.TableColumn[0].Name) assert.Equal(t, "B", table.TableColumns.TableColumn[1].Name) assert.Equal(t, "C", table.TableColumns.TableColumn[2].Name) + + assert.NoError(t, streamWriter.AddTable("A1", "C1", ``)) + + // 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"`) } func TestNewStreamWriter(t *testing.T) { @@ -118,3 +142,26 @@ func TestSetRow(t *testing.T) { assert.NoError(t, err) assert.EqualError(t, streamWriter.SetRow("A", []interface{}{}), `cannot convert cell "A" to coordinates: invalid cell name "A"`) } + +func TestSetCellValFunc(t *testing.T) { + 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))) +} -- cgit v1.2.1 From 023dba726510a4a7a97838ac9a8f4292a90aa227 Mon Sep 17 00:00:00 2001 From: xuri Date: Thu, 13 Feb 2020 00:00:42 +0800 Subject: Fix #576, serialize by fields order on stream flush --- stream_test.go | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'stream_test.go') diff --git a/stream_test.go b/stream_test.go index 8c5e7ea..d89dad8 100644 --- a/stream_test.go +++ b/stream_test.go @@ -92,6 +92,13 @@ func TestStreamWriter(t *testing.T) { _, err = streamWriter.rawData.Reader() assert.NoError(t, err) assert.NoError(t, os.Remove(streamWriter.rawData.tmp.Name())) + + // Test unsupport charset + file = NewFile() + delete(file.Sheet, "xl/worksheets/sheet1.xml") + file.XLSX["xl/worksheets/sheet1.xml"] = MacintoshCyrillicCharset + streamWriter, err = file.NewStreamWriter("Sheet1") + assert.EqualError(t, err, "xml decode error: XML syntax error on line 1: invalid UTF-8") } func TestStreamTable(t *testing.T) { -- cgit v1.2.1