导出:Java实现大批量数据导入导出(100W以上)

来源：https://www.cnblogs.com/barrywxx/p/10700283.html

使用POI或JXLS导出大数据量（百万级）Excel报表常常面临两个问题：

1. 服务器内存溢出；

2. 一次从数据库查询出这么大数据，查询缓慢。

当然也可以分页查询出数据，分别生成多个Excel打包下载，但这种生成还是很缓慢。

大数据量导入请参考：Java实现大批量数据导入导出(100W以上)　-（一）导入

那么如何解决呢？

我们可以借助XML格式利用模板替换，分页查询出数据从磁盘写入XML，最终会以Excel多sheet形式生成。亲测2400万行数据，生成Excel文件4.5G，总耗时1.5分钟。

https://www.cnblogs.com/barrywxx/p/10700283.html 

我利用StringTemplate模板解析技术对XML模板进行填充。当然也可以使用FreeMarker, Velocity等Java模板技术实现。

首先引入StringTemplate所需Jar包：

使用技术为 stringTemplate 

pom.xml：

antlr

antlr

2.7.7

org.antlr

stringtemplate

3.2.1

首先准备导出Excel模板，然后打开－》另存为－》选择格式为XML，然后用文本打开XML,提取XML头模板(head.st可通用)，数据体模板(boday.st):

head.st可通用:

<workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www./TR/REC-html40">

1996-12-17T01:32:42Z

2013-08-02T09:21:24Z

11.9999

4530

8505

480

120

False

False

生成大数据量Excel类：

ExcelGenerator:

package test.exportexcel;

import org.antlr.stringtemplate.StringTemplate;

import org.antlr.stringtemplate.StringTemplateGroup;

import test.exportexcel.bean.Row;

import test.exportexcel.bean.Worksheet;

import java.io.*;

import java.util.ArrayList;

import java.util.List;

import java.util.Random;

/**

* 类功能描述：generator big data Excel

*

* @author WangXueXing create at 19-4-13 下午10:23

* @version 1.0.0

*/

public class ExcelGenerator {

public static void main(String[] args) throws FileNotFoundException{

ExcelGenerator template = new ExcelGenerator();

template.output2();

}

/**

* 生成数据量大的时候，该方法会出现内存溢出

* @throws FileNotFoundException

*/

public void output1() throws FileNotFoundException{

StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");

StringTemplate st4 = stGroup.getInstanceOf("test/exportexcel/template/test");

Listworksheets = new ArrayList<>();

File file = new File("/home/barry/data/output.xls");

PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));

for(int i=0;i<30;i++){

Worksheet worksheet = new Worksheet();

worksheet.setSheet("第"+(i+1)+"页");

Listrows = new ArrayList<>();

for(int j=0;j<6000;j++){

Row row = new Row();

row.setName1("zhangzehao");

row.setName2(""+j);

row.setName3(i+" "+j);

rows.add(row);

}

worksheet.setRows(rows);

worksheets.add(worksheet);

}

st4.setAttribute("worksheets", worksheets);

writer.write(st4.toString());

writer.flush();

writer.close();

System.out.println("生成excel完成");

}

/**

* 该方法不管生成多大的数据量，都不会出现内存溢出，只是时间的长短

* 经测试，生成2400万数据，2分钟内，4.5G大的文件，打开大文件就看内存是否足够大了

* 数据量小的时候，推荐用JXLS的模板技术生成excel文件，谁用谁知道，大数据量可以结合该方法使用

* @throws FileNotFoundException

*/

public void output2() throws FileNotFoundException{

long startTimne = System.currentTimeMillis();

StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");

//写入excel文件头部信息

StringTemplate head = stGroup.getInstanceOf("test/exportexcel/template/head");

File file = new File("/home/barry/data/output.xls");

PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));

writer.print(head.toString());

writer.flush();

int sheets = 400;

//excel单表最大行数是65535

int maxRowNum = 60000;

//写入excel文件数据信息

for(int i=0;i<sheets;i++){< p="">

StringTemplate body = stGroup.getInstanceOf("test/exportexcel/template/body");

Worksheet worksheet = new Worksheet();

worksheet.setSheet(" "+(i+1)+" ");

worksheet.setColumnNum(3);

worksheet.setRowNum(maxRowNum);

Listrows = new ArrayList<>();

for(int j=0;j<maxrownum;j++){< p="">

Row row = new Row();

row.setName1(""+new Random().nextInt(100000));

row.setName2(""+j);

row.setName3(i+""+j);

rows.add(row);

}

worksheet.setRows(rows);

body.setAttribute("worksheet", worksheet);

writer.print(body.toString());

writer.flush();

rows.clear();

rows = null;

worksheet = null;

body = null;

Runtime.getRuntime().gc();

System.out.println("正在生成excel文件的 sheet"+(i+1));

}

//写入excel文件尾部

writer.print("");

writer.flush();

writer.close();

System.out.println("生成excel文件完成");

long endTime = System.currentTimeMillis();

System.out.println("用时="+((endTime-startTimne)/1000)+"秒");

}

}

定义JavaBean:

WorkSheet.java:

package test.exportexcel.bean;

import java.util.List;

/**

* 类功能描述：Excel sheet Bean

*

* @author WangXueXing create at 19-4-13 下午10:21

* @version 1.0.0

*/

public class Worksheet {

private String sheet;

private int columnNum;

private int rowNum;

private Listrows;

public String getSheet() {

return sheet;

}

public void setSheet(String sheet) {

this.sheet = sheet;

}

public ListgetRows() {

return rows;

}

public void setRows(Listrows) {

this.rows = rows;

}

public int getColumnNum() {

return columnNum;

}

public void setColumnNum(int columnNum) {

this.columnNum = columnNum;

}

public int getRowNum() {

return rowNum;

}

public void setRowNum(int rowNum) {

this.rowNum = rowNum;

}

}

Row.java:

package test.exportexcel.bean;

/**

* 类功能描述：Excel row bean

*

* @author WangXueXing create at 19-4-13 下午10:22

* @version 1.0.0

*/

public class Row {

private String name1;

private String name2;

private String name3;

public String getName1() {

return name1;

}

public void setName1(String name1) {

this.name1 = name1;

}

public String getName2() {

return name2;

}

public void setName2(String name2) {

this.name2 = name2;

}

public String getName3() {

return name3;

}

public void setName3(String name3) {

this.name3 = name3;

}

}

另附实现源码：

https://files.cnblogs.com/files/barrywxx/exportexcel.zip

☆

往期精彩

☆