A Method for exporting large amounts of data to Excel in java

If you need to know how to export a large amount of data to Excel in java, you can see it.

In Java Web development, you often need to export a large amount of data to Excel, and use POI and JXL to directly generate Excel, which can easily cause memory overflow.

1. One way is to write data into csv files.

1) csv files can be opened directly in Excel.

2) Writing csv files is as efficient as writing txt files.

3) for the same data content, the size of the generated csv file is much smaller than that of the generated Excel file.

From the above advantages, we can see that the memory consumption for generating csv files is definitely less than that for generating Excel files.

2. Generate a csv file in a certain format. It is in the complete row and column format when opened in Excel.

For example, in Excel:

The format of the csv file must be:

That is to say, the comma "," interval between columns and columns must be in the English input mode: fengyun1dao, Gu Long.

3. export data to the csv file in Struts2, and pass the generated csv file path to the download Action for download. A simple example.

CsvAction: generate a csv file, and pass the complete path of the generated csv file to the download Action.

The Code is as follows:

Copy code

Package cn. luxh. struts2.action; Import java. io. FileWriter; Import java. io. IOException; Import java. text. SimpleDateFormat; Import java. util. ArrayList; Import java. util. Date; Import java. util. List; Import cn. luxh. struts2.entity. Novel; Import com. opensymphony. xwork2.ActionSupport; /** * Export data to a csv file * @ Author Luxh */ Public class CsvAction extends ActionSupport { Private static final long serialVersionUID =-2862629695443964658L; /** * File name containing the complete path * Pass to the download Action for download */ Private String fileName; /** * Export data */ Public String exportData2CSV (){ List <Novel> novels = getNovels (); FileName = "D:/novels.csv "; WriteData2CSV (novels, fileName ); Return SUCCESS; } /** * Construct some data * In fact, a large amount of data may be retrieved from the database. */ Private List <Novel> getNovels (){ List <Novel> novels = new ArrayList <Novel> (); Novel novel1 = new Novel ("Fengyun 1st knife", "Gu Long", new Date ()); Novel novel2 = new Novel ("", "Jin Yong", new Date ()); Novel novel3 = new Novel ("legend of Lu Xiaofeng", "Gu Long", new Date ()); Novel novel4 = new Novel ("Lu Ding Kee", "Jin Yong", new Date ()); Novels. add (novel1 ); Novels. add (novel2 ); Novels. add (novel3 ); Novels. add (novel4 ); Return novels; } /** * Write data to a csv file in a certain format * @ Param novels data set * @ Param fileName complete path of the csv file */ Public void writeData2CSV (List <Novel> novels, String fileName ){ FileWriter fw = null; Try { Fw = new FileWriter (fileName ); // Output Header // Note that "," is used between columns. To write a line, press enter to wrap the line "rn" String title = "No., novel name, author, publication date rn "; Fw. write (title ); String content = null; SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd "); For (int I = 0; I <novels. size (); I ++ ){ Novel novel = novels. get (I ); // Note that "," is used between columns. To write a line, press enter to wrap the line "rn" Content = (I + 1) + "," + novel. getName () + "," + novel. getAuthor () + "," + sdf. format (novel. getPublishDate () + "rn "; Fw. write (content ); } } Catch (Exception e ){ E. printStackTrace (); Throw new RuntimeException (e ); } Finally { Try { If (fw! = Null ){ Fw. close (); } } Catch (IOException e ){ E. printStackTrace (); } } } }

 

 

Configuration file:

The Code is as follows:

Copy code

<! -- Transfer the csv file path to the public download Action for download --> <Action name = "exportData2CSV" class = "cn. luxh. struts2.action. CsvAction" method = "exportData2CSV"> <Result type = "redirectAction"> <Param name = "actionName"> download </param> <Param name = "nameSpace">/download </param> <! -- Complete path of the attachment, passed to the download Action --> <Param name = "fileName" >$ {fileName} </param> </Result> </Action>

4. Check the size of the csv file and the Excel file for the same data content: