13 "github.com/stretchr/testify/assert"
16 func BenchmarkStreamWriter(b *testing.B) {
19 if err := file.Close(); err != nil {
23 row := make([]interface{}, 10)
24 for colID := 0; colID < 10; colID++ {
28 for n := 0; n < b.N; n++ {
29 streamWriter, _ := file.NewStreamWriter("Sheet1")
30 for rowID := 10; rowID <= 110; rowID++ {
31 cell, _ := CoordinatesToCellName(1, rowID)
32 _ = streamWriter.SetRow(cell, row)
39 func TestStreamWriter(t *testing.T) {
41 streamWriter, err := file.NewStreamWriter("Sheet1")
42 assert.NoError(t, err)
44 // Test max characters in a cell
45 row := make([]interface{}, 1)
46 row[0] = strings.Repeat("c", TotalCellChars+2)
47 assert.NoError(t, streamWriter.SetRow("A1", row))
49 // Test leading and ending space(s) character characters in a cell
50 row = make([]interface{}, 1)
51 row[0] = " characters"
52 assert.NoError(t, streamWriter.SetRow("A2", row))
54 row = make([]interface{}, 1)
55 row[0] = []byte("Word")
56 assert.NoError(t, streamWriter.SetRow("A3", row))
58 // Test set cell with style and rich text
59 styleID, err := file.NewStyle(&Style{Font: &Font{Color: "777777"}})
60 assert.NoError(t, err)
61 assert.NoError(t, streamWriter.SetRow("A4", []interface{}{
62 Cell{StyleID: styleID},
63 Cell{Formula: "SUM(A10,B10)", Value: " preserve space "},
65 RowOpts{Height: 45, StyleID: styleID}))
66 assert.NoError(t, streamWriter.SetRow("A5", []interface{}{
67 &Cell{StyleID: styleID, Value: "cell <>&'\""},
68 &Cell{Formula: "SUM(A10,B10)"},
70 {Text: "Rich ", Font: &Font{Color: "2354E8"}},
71 {Text: "Text", Font: &Font{Color: "E83723"}},
74 assert.NoError(t, streamWriter.SetRow("A6", []interface{}{time.Now()}))
75 assert.NoError(t, streamWriter.SetRow("A7", nil, RowOpts{Height: 20, Hidden: true, StyleID: styleID}))
76 assert.Equal(t, ErrMaxRowHeight, streamWriter.SetRow("A8", nil, RowOpts{Height: MaxRowHeight + 1}))
78 for rowID := 10; rowID <= 51200; rowID++ {
79 row := make([]interface{}, 50)
80 for colID := 0; colID < 50; colID++ {
81 row[colID] = rand.Intn(640000)
83 cell, _ := CoordinatesToCellName(1, rowID)
84 assert.NoError(t, streamWriter.SetRow(cell, row))
87 assert.NoError(t, streamWriter.Flush())
88 // Save spreadsheet by the given path
89 assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriter.xlsx")))
91 // Test set cell column overflow
92 assert.ErrorIs(t, streamWriter.SetRow("XFD51201", []interface{}{"A", "B", "C"}), ErrColumnNumber)
93 assert.NoError(t, file.Close())
95 // Test close temporary file error
97 streamWriter, err = file.NewStreamWriter("Sheet1")
98 assert.NoError(t, err)
99 for rowID := 10; rowID <= 25600; rowID++ {
100 row := make([]interface{}, 50)
101 for colID := 0; colID < 50; colID++ {
102 row[colID] = rand.Intn(640000)
104 cell, _ := CoordinatesToCellName(1, rowID)
105 assert.NoError(t, streamWriter.SetRow(cell, row))
107 assert.NoError(t, streamWriter.rawData.Close())
108 assert.Error(t, streamWriter.Flush())
110 streamWriter.rawData.tmp, err = os.CreateTemp(os.TempDir(), "excelize-")
111 assert.NoError(t, err)
112 _, err = streamWriter.rawData.Reader()
113 assert.NoError(t, err)
114 assert.NoError(t, streamWriter.rawData.tmp.Close())
115 assert.NoError(t, os.Remove(streamWriter.rawData.tmp.Name()))
117 // Test create stream writer with unsupported charset
119 file.Sheet.Delete("xl/worksheets/sheet1.xml")
120 file.Pkg.Store("xl/worksheets/sheet1.xml", MacintoshCyrillicCharset)
121 _, err = file.NewStreamWriter("Sheet1")
122 assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
123 assert.NoError(t, file.Close())
127 streamWriter, err = file.NewStreamWriter("Sheet1")
128 assert.NoError(t, err)
129 assert.NoError(t, streamWriter.SetRow("A1", []interface{}{Cell{StyleID: styleID, Value: "Data"}}))
130 assert.NoError(t, streamWriter.Flush())
131 cellValue, err := file.GetCellValue("Sheet1", "A1")
132 assert.NoError(t, err)
133 assert.Equal(t, "Data", cellValue)
135 // Test stream reader for a worksheet with huge amounts of data
136 file, err = OpenFile(filepath.Join("test", "TestStreamWriter.xlsx"))
137 assert.NoError(t, err)
138 rows, err := file.Rows("Sheet1")
139 assert.NoError(t, err)
142 row, err := rows.Columns()
143 assert.NoError(t, err)
146 assert.NoError(t, rows.Close())
147 assert.Equal(t, 2559559, cells)
148 // Save spreadsheet with password.
149 assert.NoError(t, file.SaveAs(filepath.Join("test", "EncryptionTestStreamWriter.xlsx"), Options{Password: "password"}))
150 assert.NoError(t, file.Close())
153 func TestStreamSetColWidth(t *testing.T) {
156 assert.NoError(t, file.Close())
158 streamWriter, err := file.NewStreamWriter("Sheet1")
159 assert.NoError(t, err)
160 assert.NoError(t, streamWriter.SetColWidth(3, 2, 20))
161 assert.Equal(t, ErrColumnNumber, streamWriter.SetColWidth(0, 3, 20))
162 assert.Equal(t, ErrColumnNumber, streamWriter.SetColWidth(MaxColumns+1, 3, 20))
163 assert.Equal(t, ErrColumnWidth, streamWriter.SetColWidth(1, 3, MaxColumnWidth+1))
164 assert.NoError(t, streamWriter.SetRow("A1", []interface{}{"A", "B", "C"}))
165 assert.Equal(t, ErrStreamSetColWidth, streamWriter.SetColWidth(2, 3, 20))
168 func TestStreamSetPanes(t *testing.T) {
169 file, paneOpts := NewFile(), &Panes{
175 ActivePane: "topRight",
176 Selection: []Selection{
177 {SQRef: "K16", ActiveCell: "K16", Pane: "topRight"},
181 assert.NoError(t, file.Close())
183 streamWriter, err := file.NewStreamWriter("Sheet1")
184 assert.NoError(t, err)
185 assert.NoError(t, streamWriter.SetPanes(paneOpts))
186 assert.Equal(t, ErrParameterInvalid, streamWriter.SetPanes(nil))
187 assert.NoError(t, streamWriter.SetRow("A1", []interface{}{"A", "B", "C"}))
188 assert.Equal(t, ErrStreamSetPanes, streamWriter.SetPanes(paneOpts))
191 func TestStreamTable(t *testing.T) {
194 assert.NoError(t, file.Close())
196 streamWriter, err := file.NewStreamWriter("Sheet1")
197 assert.NoError(t, err)
198 // Test add table without table header
199 assert.EqualError(t, streamWriter.AddTable(&Table{Range: "A1:C2"}), "XML syntax error on line 2: unexpected EOF")
200 // Write some rows. We want enough rows to force a temp file (>16MB)
201 assert.NoError(t, streamWriter.SetRow("A1", []interface{}{"A", "B", "C"}))
202 row := []interface{}{1, 2, 3}
203 for r := 2; r < 10000; r++ {
204 assert.NoError(t, streamWriter.SetRow(fmt.Sprintf("A%d", r), row))
208 assert.NoError(t, streamWriter.AddTable(&Table{Range: "A1:C2"}))
209 assert.NoError(t, streamWriter.Flush())
211 // Verify the table has names
213 val, ok := file.Pkg.Load("xl/tables/table1.xml")
215 assert.NoError(t, xml.Unmarshal(val.([]byte), &table))
216 assert.Equal(t, "A", table.TableColumns.TableColumn[0].Name)
217 assert.Equal(t, "B", table.TableColumns.TableColumn[1].Name)
218 assert.Equal(t, "C", table.TableColumns.TableColumn[2].Name)
220 assert.NoError(t, streamWriter.AddTable(&Table{Range: "A1:C1"}))
222 // Test add table with illegal cell reference
223 assert.Equal(t, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")), streamWriter.AddTable(&Table{Range: "A:B1"}))
224 assert.Equal(t, newCellNameToCoordinatesError("B", newInvalidCellNameError("B")), streamWriter.AddTable(&Table{Range: "A1:B"}))
225 // Test add table with invalid table name
226 assert.Equal(t, newInvalidNameError("1Table"), streamWriter.AddTable(&Table{Range: "A:B1", Name: "1Table"}))
227 // Test add table with unsupported charset content types
228 file.ContentTypes = nil
229 file.Pkg.Store(defaultXMLPathContentTypes, MacintoshCyrillicCharset)
230 assert.EqualError(t, streamWriter.AddTable(&Table{Range: "A1:C2"}), "XML syntax error on line 1: invalid UTF-8")
233 func TestStreamMergeCells(t *testing.T) {
236 assert.NoError(t, file.Close())
238 streamWriter, err := file.NewStreamWriter("Sheet1")
239 assert.NoError(t, err)
240 assert.NoError(t, streamWriter.MergeCell("A1", "D1"))
241 // Test merge cells with illegal cell reference
242 assert.Equal(t, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")), streamWriter.MergeCell("A", "D1"))
243 assert.NoError(t, streamWriter.Flush())
244 // Save spreadsheet by the given path
245 assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamMergeCells.xlsx")))
248 func TestStreamInsertPageBreak(t *testing.T) {
251 assert.NoError(t, file.Close())
253 streamWriter, err := file.NewStreamWriter("Sheet1")
254 assert.NoError(t, err)
255 assert.NoError(t, streamWriter.InsertPageBreak("A1"))
256 assert.NoError(t, streamWriter.Flush())
257 // Save spreadsheet by the given path
258 assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamInsertPageBreak.xlsx")))
261 func TestNewStreamWriter(t *testing.T) {
262 // Test error exceptions
265 assert.NoError(t, file.Close())
267 _, err := file.NewStreamWriter("Sheet1")
268 assert.NoError(t, err)
269 _, err = file.NewStreamWriter("SheetN")
270 assert.EqualError(t, err, "sheet SheetN does not exist")
271 // Test new stream write with invalid sheet name
272 _, err = file.NewStreamWriter("Sheet:1")
273 assert.Equal(t, ErrSheetNameInvalid, err)
276 func TestStreamMarshalAttrs(t *testing.T) {
278 attrs, err := r.marshalAttrs()
279 assert.NoError(t, err)
280 assert.Empty(t, attrs)
283 func TestStreamSetRow(t *testing.T) {
284 // Test error exceptions
287 assert.NoError(t, file.Close())
289 streamWriter, err := file.NewStreamWriter("Sheet1")
290 assert.NoError(t, err)
291 assert.Equal(t, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")), streamWriter.SetRow("A", []interface{}{}))
292 // Test set row with non-ascending row number
293 assert.NoError(t, streamWriter.SetRow("A1", []interface{}{}))
294 assert.Equal(t, newStreamSetRowError(1), streamWriter.SetRow("A1", []interface{}{}))
295 // Test set row with unsupported charset workbook
297 file.Pkg.Store(defaultXMLPathWorkbook, MacintoshCyrillicCharset)
298 assert.EqualError(t, streamWriter.SetRow("A2", []interface{}{time.Now()}), "XML syntax error on line 1: invalid UTF-8")
301 func TestStreamSetRowNilValues(t *testing.T) {
304 assert.NoError(t, file.Close())
306 streamWriter, err := file.NewStreamWriter("Sheet1")
307 assert.NoError(t, err)
308 assert.NoError(t, streamWriter.SetRow("A1", []interface{}{nil, nil, Cell{Value: "foo"}}))
310 ws, err := file.workSheetReader("Sheet1")
311 assert.NoError(t, err)
312 assert.NotEqual(t, ws.SheetData.Row[0].C[0].XMLName.Local, "c")
315 func TestStreamSetRowWithStyle(t *testing.T) {
318 assert.NoError(t, file.Close())
321 grayStyleID, err := file.NewStyle(&Style{Font: &Font{Color: "777777"}})
322 assert.NoError(t, err)
323 blueStyleID, err := file.NewStyle(&Style{Font: &Font{Color: "0000FF"}})
324 assert.NoError(t, err)
326 streamWriter, err := file.NewStreamWriter("Sheet1")
327 assert.NoError(t, err)
328 assert.NoError(t, streamWriter.SetRow("A1", []interface{}{
330 Cell{Value: "value2"},
331 &Cell{Value: "value2"},
332 Cell{StyleID: blueStyleID, Value: "value3"},
333 &Cell{StyleID: blueStyleID, Value: "value3"},
334 }, RowOpts{StyleID: grayStyleID}))
335 err = streamWriter.Flush()
336 assert.NoError(t, err)
338 ws, err := file.workSheetReader("Sheet1")
339 assert.NoError(t, err)
340 for colIdx, expected := range []int{grayStyleID, zeroStyleID, zeroStyleID, blueStyleID, blueStyleID} {
341 assert.Equal(t, expected, ws.SheetData.Row[0].C[colIdx].S)
345 func TestStreamSetCellValFunc(t *testing.T) {
348 assert.NoError(t, f.Close())
350 sw, err := f.NewStreamWriter("Sheet1")
351 assert.NoError(t, err)
353 for _, val := range []interface{}{
358 int64(-9223372036854775808),
363 uint64(18446744073709551615),
374 assert.NoError(t, sw.setCellValFunc(c, val))
378 func TestStreamWriterOutlineLevel(t *testing.T) {
380 streamWriter, err := file.NewStreamWriter("Sheet1")
381 assert.NoError(t, err)
383 // Test set outlineLevel in row
384 assert.NoError(t, streamWriter.SetRow("A1", nil, RowOpts{OutlineLevel: 1}))
385 assert.NoError(t, streamWriter.SetRow("A2", nil, RowOpts{OutlineLevel: 7}))
386 assert.ErrorIs(t, ErrOutlineLevel, streamWriter.SetRow("A3", nil, RowOpts{OutlineLevel: 8}))
388 assert.NoError(t, streamWriter.Flush())
389 // Save spreadsheet by the given path
390 assert.NoError(t, file.SaveAs(filepath.Join("test", "TestStreamWriterSetRowOutlineLevel.xlsx")))
392 file, err = OpenFile(filepath.Join("test", "TestStreamWriterSetRowOutlineLevel.xlsx"))
393 assert.NoError(t, err)
394 for rowIdx, expected := range []uint8{1, 7, 0} {
395 level, err := file.GetRowOutlineLevel("Sheet1", rowIdx+1)
396 assert.NoError(t, err)
397 assert.Equal(t, expected, level)
399 assert.NoError(t, file.Close())