Create a large Excel file using Sxssfworkbook

A key issue for the creation of large Excel files is to ensure that there is no memory overflow. In fact, even a small excel (such as a few megabytes), it uses more memory than the actual size of the Excel file. If the cell has a variety of formats (for example, bold, red background, and so on), it takes up more memory.

For large Excel creation and no memory overflow, there is only sxssfworkbook. Its principle is very simple, uses the hard disk space to change the memory (just like the hash map uses the space to change the time).

Sxssfworkbook is a streaming version of Xssfworkbook that only saves the most recent Excel rows in memory for viewing, before Excel rows are written to the hard drive (Windows computer, is written to the Temp folder in the C-packing directory. Rows written to the hard drive are not visible/inaccessible. Only those that are still stored in memory can be accessed.

As for how many rows are saved in memory, the other writes to the hard drive is determined by default_window_size. The code can also be set when the Sxssfworkbook instance is created by passing in an int parameter. It should be noted that int rowaccesswindowsize, if passed in 100, does not mean that 100 rows are saved in memory, but rather that the number of rows visible under the 100 screen size.

    Construct a new workbook with default row window size
    sxssfworkbook test1 = new Sxssfworkbook ();
    Construct an empty workbook and specify the Windows for row access.
    Sxssfworkbook test2 =new sxssfworkbook (int rowaccesswindowsize);

Last iteration, my job was to solve the potential problem of writing Excel overflow from DB read data. The number of data bars varies from tens of thousands of to hundreds of thousands of (each cell in each row is formatted), plus the virtual machine configuration is common, and memory overflows. Using Sxssfworkbook, the largest generation to 800Mb of Excel, are smooth. Of course, the actual work should not use such a large excel ... Ordinary machines may die when they are opened, almost unreadable.

Here are some of the results of running in a virtual machine (all of the time you read data from db):

int rowaccesswindowsize =
2Mb Excel, 5 seconds complete
70Mb excel,2 half complete
800Mb excel,65 minutes complete

There is also a point worth mentioning, at that time in order to generate this 800Mb Excel, I leng the C disk clear out 10 g space, otherwise sxssfworkbook will be full of hard disk space, only dozens of KB, and then the program zombie ...

In addition, in the Windows + Eclipse development environment, you can change the Eclipse.ini to allocate more memory space to the Java program. In the Eclipse root directory, there is a Eclipse.ini file that opens with a text editor, finds the-vmargs line, modifies the following parameters, saves, and restarts eclipse:
-xms default is 1/64 of physical memory
-xmx default is 1/4 of physical memory

Next post notice: Build WebUI Automation Framework ...