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