Java POI writes millions of data to Excel

. xls files support only 6w+ of data writes

. xlsx files only support write-104w+ data

In Java, the Jxl tool class can only manipulate. xls files and cannot manipulate the. xlsx file

The POI tool class can support. xlsx file operations.

Excel data reading and writing are correspondingly simple, there are a lot of code on the net, I would like to say here is how to write 100w+ data into Excel.

In the POI, Xssfworkbook wb = new Xssfworkbook (); the workbook created can write large amounts of data, but it is very likely that the virtual machine does not have enough memory to error

There are two solutions in this case:

1. Reassign the Java Virtual Machine memory (I did not experiment)

The Sxssfworkbook swb=new Sxssfworkbook (wb,10000) is also available in the 2.POI, and the first parameter of the code is Xssfworkbook WB = new Xssfworkbook (); The second parameter is the number of rows of data that are processed in memory, and when the amount of data exceeds the amount you set, it saves more data to disk.

Here is a snippet for simply writing an Excel file:

public static void Createworkbook (list<entity> datalist,string filename) throws IOException {System.out.pri    Ntln ("number of data = =" +datalist.size ());         Create an Excel workbook Xssfworkbook wb = new Xssfworkbook ();        Sxssfworkbook swb=new Sxssfworkbook (wb,10000);        Swb.createsheet ()//Create First sheet (page), named new sheet sxssfsheet sheet = null;        Sheet = (sxssfsheet) swb.createsheet ("MyData");        Row row//cell squares//row and Cell are counted from 0 for (int i=0;i<datalist.size (); i++) {        Entity En=datalist.get (i);             Create a row on page sheet row row = Sheet.createrow (i);             Create a grid on row row/*org.apache.poi.ss.usermodel.cell Cell = Row.createcell (0); Set the display of the grid cell.setcellvalue (1);        *///system.out.println (En.getpkg_name ());            Row.createcell (0). Setcellvalue ("xx");             Row.createcell (0). Setcellvalue (En.getdate ());  Or do it on one line.           Row.createcell (1). Setcellvalue (En.getxxx ());             Row.createcell (2). Setcellvalue (En.getxxx ());                     Row.createcell (3). Setcellvalue (En.getxxx ());             Row.createcell (4). Setcellvalue (En.getxxx ());                  ......        }         Create a file named Workbook.xls fileoutputstream fileout = new FileOutputStream (filename);         Output the workbook created above to a file Swb.write (fileout);         Turn off the output stream fileout.close ();    System.out.println ("Write complete----"); }

　　

Java POI writes millions of data to Excel