Java large data processing-large numbers of datasets to Excel

In Java Web Development, it is often necessary to export a large amount of data to http://www.aliyun.com/zixun/aggregation/16544.html ">excel, using POI, JXL directly generate Excel, It is easy to cause memory overflow.

1, there is a way, is to write data in CSV format file.

1 CSV file can be opened directly with Excel.

2 The efficiency of writing CSV file is as high as the efficiency of writing TXT file.

3 The same data content, the generated CSV file size is much smaller than the generated Excel file.

From the above, you can see that the memory generated by the CSV file is absolutely less than the build Excel file.

2, in a certain format to generate CSV file, in Excel when opened is the complete row and column format.

For example: Formatting in Excel:

In this case, the format in the CSV file must be:

That is, between columns and columns, you need to use the English IME state of the comma "," interval: The first knife, cologne.

3, in Struts2 to export data to Excel, a simple example.

Csvaction, generates a CSV file and passes the full path of the generated CSV file to the download action.

Java code <!--[if!vml]-->

<!--[endif]-->

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 CSV file

* @author Luxh

*/

public class Csvaction extends Actionsupport {

Private static final long serialversionuid = -2862629695443964658l;

/**

* File name containing full path

* Pass to download action for download

*/

Private String FileName;

/**

* Export Data

*/

Public String Exportdata2csv () {

List novels = Getnovels ();

FileName = "D:/novels.csv";

Writedata2csv (Novels,filename);

return SUCCESS;

}

/**

* Construct some data

* It's actually possible to isolate a large amount of data from the database

*/

Private List Getnovels () {

List novels = new ArrayList ();

Novel Novel1 = new Novel ("The First Sword", "Cologne", New Date ());

Novel Novel2 = new Novel ("Book Sword and Hatred Record", "Jin Yong", New Date ());

Novel Novel3 = new Novel ("Xiaofeng Legend", "Cologne", New Date ());

Novel Novel4 = new novel ("The Deer Ding Kee", "Jin Yong", New Date ());

Novels.add (NOVEL1);

Novels.add (NOVEL2);

Novels.add (NOVEL3);

Novels.add (NOVEL4);

return novels;

}

/**

* Write the data in a certain format to the CSV file

* @param novels data collection

* @param fileName CSV file full path

*/

public void Writedata2csv (List novels,string fileName) {

FileWriter FW = NULL;

try {

FW = new FileWriter (fileName);

Output header

Note between columns with the "," interval, write a line need to return line "\ r \ n"

String title = "Serial number, novel name, author, publication date \ r \ n";

Fw.write (title);

String content = null;

SimpleDateFormat SDF = new SimpleDateFormat ("yyyy");

for (int i=0;i

Novel novel = Novels.get (i);

Note between columns with the "," interval, write a line need to return line "\ r \ n"

Content = (i+1) + "," +novel.getname () + "," +novel.getauthor () + "," +sdf.format (Novel.getpublishdate ()) + "\ r \ n";

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:

XML code <!--[if!vml]-->

<!--[endif]-->

Download

/download

${filename}

4, look at the same data content, CSV file and Excel file size comparison: