Write files based on the SXSSF (streaming Usermodel API)

In POI3.8, SXSSF only supports excel2007 format as an extension of a stream of XSSF. The purpose of this is to provide the efficiency of writing data by refreshing the Excel memory information to the hard disk when generating a large amount of data in the form of Excel.

The official text reads as follows:

SXSSF (streaming Usermodel API) Note SXSSF is a brand new contribution and some features were added after it was F Irst introduced in POI 3.8-beta3. Users is advised to try the latest build from trunk. Instructions how to build is here.

SXSSF (package:org.apache.poi.xssf.streaming) is a api-compatible streaming extension of XSSF to being used when very l Arge spreadsheets are produced, and heap space is limite d. SXSSF achieves it low memory footprint by limiting Access to the rows is within a sliding window, while XSSF gives access to all rows in the document. Older rows that is no longer in the window become inaccessible, as they is written to the disk.

You can specify the window size @ Workbook construction time via new Sxssfworkbook (int windowsize) or you can SE T it per-sheet via sxssfsheet#setrandomaccesswindowsize (int windowsize)

When a new row was created via CreateRow () and the total number of unflushed records would exceed the specified window Size, then the row with the lowest index value are flushed a nd cannot be accessed via GetRow () anymore.

The default window size is A and defined by Sxssfworkbook.default_window_size.

A windowsize of-1 indicates unlimited access. In the This is records that has not been flushed by a call to Flushrows () is available for random access.

The example below writes a sheet with a window of $ rows. When the row was Count reaches 101, the row with rownum=0 was flushed to disk and removed from memory, when RowNum reaches 102 Then the row with Rownum=1 is flushed, etc.

The test code is as follows:

Java code

2. Package com.easyway.excel.events.stream;
6. Import Java.io.FileOutputStream;
10. Import Org.apache.poi.ss.usermodel.Cell;
12. Import Org.apache.poi.ss.usermodel.Row;
14. Import Org.apache.poi.ss.usermodel.Sheet;
16. Import Org.apache.poi.ss.usermodel.Workbook;
18. Import org.apache.poi.ss.util.CellReference;
20. Import Org.apache.poi.xssf.streaming.SXSSFWorkbook;
22. /**
24. * SXSSF (streaming Usermodel API)
26. * When the file is written in a particularly large stream, flush the in-memory data to the hard disk, reducing the amount of memory used.
28. * Play the role of space for time, to provide efficiency.
30. *
32. * @Title:
34. * @version 1.0
36. */
38. Public class Sxssexcelevent {
40. public static void Main (string[] args) throws Throwable {
42. //Create a stream-based workbook object
44. Workbook WB = new Sxssfworkbook (100); //Keep rows in memory, exceeding rows would be flushed to disk
46. //sxssfworkbook wb = new Sxssfworkbook ();
48. //wb.setcompresstempfiles (TRUE);//Temp files would be gzipped
50. Sheet sh = wb.createsheet ();
52. //Use CreateRow to write information in memory.
54. For (int rownum = 0; rownum < ; rownum++) {
56. Row row = Sh.createrow (rownum);
58. For (int cellnum = 0; cellnum < cellnum++) {
60. Cell cell = Row.createcell (Cellnum);
62. String address = new cellreference (cell). formatasstring ();
64. Cell.setcellvalue (address);
66. }
70. }
74. //Rows with rownum < is flushed and not accessible
76. //When accessing using the GetRow method, flush the in-memory information to the hard disk.
78. For (int rownum = 0; rownum < ; rownum++) {
80. System.out.println (Sh.getrow (rownum));
82. }
86. //ther last rows is still in memory
88. For (int rownum = N, rownum < rownum++) {
90. System.out.println (Sh.getrow (rownum));
92. }
94. //write in file
96. FileOutputStream out = new FileOutputStream ("c://sxssf.xlsx");
98. Wb.write (out);
100. //Close File stream object
102. Out.close ();
104. System.out.println ("Based on stream write execution!");
106. }
108. }

SXSSF Flushes sheet data in temporary files (a temp file per sheet) and the size of these temporary files can grow to A very large value. For example, A-MB CSV data, the size of the temp XML becomes more than a gigabyte. If the size of the temp files is a issue, you can tell SXSSF to use gzip compression:

  Sxssfworkbook wb = new Sxssfworkbook ();  Wb.setcompresstempfiles (TRUE);//Temp files would be gzipped

Write files based on the SXSSF (streaming Usermodel API)