java分批次导出_Java poi Excel导出文件，Java poi 分批次导出大批量数据

Java poi Excel导出文件，Java poi 分批次导出大批量数据

================================

©Copyright 蕃薯耀 2018年6月26日

http://fanshuyao.iteye.com/

一、问题描述：

1、当使用WorkbookFactory.create 创建Excel时，其实创建的文档为Excel2003版的，最大容纳65536行数据，超出这个行数就是发生溢出。

WorkbookFactory.create(...);

2、当数据量时，直接从数据库查询出来，内存不够也会溢出。

二、解决方案：

1、解决Excel2003行数溢出，使用XSSFWorkbook对象创建2007版的Excel，能容纳104万行数据。

具体如下：

Excel2003版最大行数是65536，Excel2007最大行数是1048576

Excel2003版最大列数是256，Excel2007最大列数是16384

XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(new FileInputStream(excelTemplatePath));//使用模板文件

2、直接从数据库查询大批量数据导致内存溢出，可以使用分批次的方式查询数据，避免内存一下子暴掉。如要导出数据库100万条数据，每次取1万条，这样分100次取，每次取的数量自己可以定义，保证内存不溢出。

具体例子和代码：

/**

* 导出excel文件

* @param request

* @param response

* @throws Exception

*/

public void exportPlaceNameQueryExcelFile(HttpServletRequest request,HttpServletResponse response){

Map map = new HashMap();;

FileOutputStream fileOutputStream = null;

try {

Row row = parseRequestParametersToRow(request);

String projectPath = getRealPath(request);

String excelTemplatePath = projectPath + File.separator

+ "template" + File.separator + "name" + File.separator

+ "exportPlaceNameQueryExcelFile.xlsx";

File excelTemplateFile = new File(excelTemplatePath);//模板文件

File destFile = this.createDestExcelFile(request, excelTemplateFile);//目标文件

fileOutputStream = new FileOutputStream(destFile);

map.put("absoluteFilePath", destFile.getAbsolutePath());

map.put("fileName", destFile.getName());

int count = getPlaceNameQueryDelegate().getPnPlaceNameCount(row);//获取Excel导出的总数据量，根据数量判断是否进行分批次导出

int pageSize = 1000 * 5;//每次查询的数据量

int startRow = 2;//模板前2行是模板标题，excel行数是从0开始的，除去2行模板标题，此处设置成2

XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(new FileInputStream(excelTemplatePath));//使用模板文件

//Excel2003版最大行数是65536，Excel2007最大行数是1048576

//Excel2003版最大列数是256，Excel2007最大列数是16384

SXSSFWorkbook workbook = new SXSSFWorkbook(xSSFWorkbook, 100);//使用SXSSFWorkbook避免Excel数据超出65536行时报错

//Workbook b = WorkbookFactory.create(destFile);//超出65536行时报错

Sheet sheet = workbook.getSheetAt(0);

List rows = null;

if(count > pageSize){//当数量大时，采用分批次获取数据

int page = (count % pageSize == 0) ? (count / pageSize) : (count / pageSize + 1);

for(int i=1; i<=page; i++){

row.addColumn("pageNumber", i);

row.addColumn("pageSize", pageSize);

rows = getPlaceNameQueryDelegate().getAllPlaceNames(row);

startRow = this.setSheet(sheet, startRow, rows);//此处要注意回写startRow供下次循环使用

}

}else{

rows = getPlaceNameQueryDelegate().getAllPlaceNames(row);

startRow = this.setSheet(sheet, startRow, rows);

}

workbook.write(fileOutputStream);

} catch (Exception e) {

e.printStackTrace();

}finally{

try {

if(fileOutputStream != null){

fileOutputStream.close();

}

writeJson(response, JsonUtils.toJson(map));

} catch (Exception e) {

e.printStackTrace();

}

}

}

private File createDestExcelFile(HttpServletRequest request, File excelTemplateFile){

try {

String destFileName = UUID.randomUUID().toString();

Properties prop = PropertiesUtils.read("placeName.properties");

String destDir = prop.getProperty("destDirForPlaceNameQuery");

File destFileDir = new File(destDir);

if(!destFileDir.exists()){

destFileDir.mkdirs();

}

String absoluteFilePath = excelTemplateFile.getAbsolutePath();

String suffix = absoluteFilePath.substring(absoluteFilePath.lastIndexOf("."));

File destFile = new File(destDir + destFileName + suffix);

FileUtils.copyFile(excelTemplateFile, destFile);

return destFile;

} catch (Exception e) {

e.printStackTrace();

}

return null;

}

private void writeToSheet(Sheet sheet, int startRow, int startCell, String value){

org.apache.poi.ss.usermodel.Row poiRow = sheet.getRow(startRow);

if(poiRow == null){

poiRow = sheet.createRow(startRow);

}

Cell cell = poiRow.getCell(startCell);

if(cell == null){

cell = poiRow.createCell(startCell);

}

cell.setCellValue(value);

}

/**

* 根据Excel对象插入数据后，返回可以插入数据下一行的行数，startRow

* @param workbook

* @param startRow

* @param rows

* @return int

*/

private int setSheet(Sheet sheet, int startRow, List rows){

if(rows != null && rows.size() > 0){

if(startRow < 0){

startRow = sheet.getLastRowNum();

}

for (Row row : rows) {

int startCell = 0;

this.writeToSheet(sheet, startRow, startCell++, row.getString("pn_code"));

this.writeToSheet(sheet, startRow, startCell++, row.getString("standard_name"));//地名(standard_name)

this.writeToSheet(sheet, startRow, startCell++, row.getString("pinyin"));//拼音(pinyin)

this.writeToSheet(sheet, startRow, startCell++, row.getString("tree_path"));//地名类别(tree_path)

String statusText = row.getString("status");

if(statusText.equals("1")){

statusText = "有效";

}else if(statusText.equals("0")){

statusText = "无效";

}else if(statusText.equals("2")){

statusText = "审批中";

}

this.writeToSheet(sheet, startRow, startCell++, statusText);//状态(status)

//额外属性

String type = row.getString("type");

if(!StringUtils.isBlank(type)){

type = type.trim();

if("建筑物".equals(type)){

//紧接着基础信息，startCell不用加

this.setBuildingProperties(sheet, startRow, startCell, row);//建筑物

}else if("道路".equals(type)){

startCell += 10;//除去建筑物的10个属性

this.setRoadProperties(sheet, startRow, startCell, row);

}else if("桥梁".equals(type)){

startCell += 18;//除去建筑物、道路的18个属性

this.setBridgeProperties(sheet, startRow, startCell, row);

}else if("公园".equals(type)){

startCell += 28;//除去建筑物、道路、桥梁的28个属性

this.setPartProperties(sheet, startRow, startCell, row);

}else if("广场".equals(type)){

startCell += 37;//除去建筑物、道路、桥梁、公园的37个属性

this.setSquareProperties(sheet, startRow, startCell, row);

}else if("隧道".equals(type)){

startCell += 46;//除去建筑物、道路、桥梁、公园、广场的46个属性

this.setTunnelProperties(sheet, startRow, startCell, row);

}

}

startRow ++;//最后行号加1，必须有，且在最后

}

}

return startRow;

}

private void setBuildingProperties(Sheet sheet, int startRow, int startCell, Row row){

try {

Row dataRow = getPlaceNameQueryDelegate().getBuilding(row);

if(dataRow != null){

this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("building_func"));

this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("start_date"));

……

……

}

} catch (Exception e) {

e.printStackTrace();

}

}

private void setRoadProperties(Sheet sheet, int startRow, int startCell, Row row){

try {

Row dataRow = getPlaceNameQueryDelegate().getRoad(row);

if(dataRow != null){

this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("starting_point"));

……

}

} catch (Exception e) {

e.printStackTrace();

}

}