关注要点
- 使用SXSSFWorkbook 专门处理大数据,对于大型excel的创建且不会内存溢出的。它的原理很简单,用硬盘空间换内存(就像hashmap用空间换时间一样)。 SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看,在此之前的excel rows都会被写入到硬盘里(Windows电脑的话,是写入到C盘根目录下的temp文件夹)。
- 使用excel分页技术,假设如果有500w条数据,一下导入一个excel的sheet页中,想想打开excel也需要一段时间吧,慢的话有可能导致程序无法加载,或者直接结束进程的情况。
- 为了防止List装载数据过大造成内存溢出,采取分段获取数据方式,并在每次向row中加载完数据后对List进行手动回收,并将对象释放从而避免内存溢出。(如需实现方式请留言)。
/**
* 百万级数据导出
*/
package XXX.XXX.XXX.XXX.impl;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
/**
* @author 紫气东来
*/
public class XxServiceImpl implements XxService {
private XxDao dao;
private SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
private String fileName = df.format(Calendar.getInstance().getTime());
private static String sheetName = "sheet_";
private static final String SUFFIX = ".xlsx";
//private String filePath = "D:" + File.separator + "XXX" + File.separator + "XXX" + File.separator + "XXX" + File.separator + fileName + SUFFIX;
private String filePath = File.separator + "XXX" + File.separator + "XXX" + File.separator + "XXX" + File.separator + fileName + SUFFIX;
public XxServiceImpl() {
}
public void setDao(XxDao dao) {
this.dao = dao;
}
@SuppressWarnings("unchecked")
@Override
public File exportFile() {
long startTime = System.currentTimeMillis();
System.out.println("导出开始时间:"
+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS")
.format(startTime));
File file = null;
List ls = this.dao.queryResult();//获取数据源
Integer dataCount = ls.size();
if (0 == dataCount) return null;
FileOutputStream out = null;
SXSSFWorkbook workbook = null;
SXSSFSheet sheet = null;
int maxDataSize = 5000;
int sheetCnt = 0;
if (dataCount > maxDataSize) {
if (0 == dataCount / maxDataSize) sheetCnt = dataCount / maxDataSize;
else sheetCnt = dataCount / maxDataSize + 1;
} else sheetCnt = 1;
workbook = new SXSSFWorkbook(100);
workbook.setCompressTempFiles(true);
for (int i = 0; i < sheetCnt; i++) {
sheet = (SXSSFSheet) workbook.createSheet(sheetName + (i + 1));
sheet.setDefaultColumnWidth(25);
SXSSFRow row = null;
setHeader(row, sheet);
Map<String, String> resultMap = new HashMap<String, String>();
int curMaxDataSize = dataCount > (i + 1) * maxDataSize ? (i + 1)
* maxDataSize : dataCount;
int idx = 1;
for (int j = i * maxDataSize; j < curMaxDataSize; j++) {
resultMap = (Map<String, String>) ls.get(j);
row = (SXSSFRow) sheet.createRow(idx ++);
setRow(row, resultMap);
}
resultMap.clear();
resultMap = null;
}
try {
file = createFile(filePath);
out = new FileOutputStream(file);
workbook.write(out);
out.close();
} catch (FileNotFoundException e) {
System.err.println(e.getMessage());
} catch (IOException e) {
System.err.println(e.getMessage());
}
long endTime = System.currentTimeMillis();
System.out.println("导出结束时间:"
+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS")
.format(endTime));
System.out.println("当前程序耗时:" + (endTime - startTime) + "ms" + " | 共:"
+ dataCount + "条数据");
return file;
}
private File createFile(String src) throws IOException {
String path = src.substring(0, src.lastIndexOf(File.separator));
String fileName = src.substring(src.lastIndexOf(File.separator) + 1, src.length());
File f = new File(path);
if (!f.exists()) f.mkdirs();
File file = new File(f, fileName);
if (!file.exists()) file.createNewFile();
return file;
}
public void isToFile(InputStream is, File file) throws IOException {
OutputStream os = null;
try {
os = new FileOutputStream(file);
int len = 0;
byte[] buffer = new byte[8192];
while ((len = is.read(buffer)) != -1)
os.write(buffer, 0, len);
} finally {
os.close();
is.close();
}
}
private void setHeader(SXSSFRow row, SXSSFSheet sheet) {
row = (SXSSFRow) sheet.createRow(0);
setRow(row, null);
}
private void setRow(SXSSFRow row, Map<String, String> resultMap) {
if (resultMap == null) {
getCell(row, 0).setCellValue("header1");
getCell(row, 1).setCellValue("header2");
getCell(row, 2).setCellValue("header3");
getCell(row, 3).setCellValue("header4");
getCell(row, 4).setCellValue("header5");
} else {
getCell(row, 0).setCellValue(resultMap.get("NAME1"));
getCell(row, 1).setCellValue(resultMap.get("NAME2"));
getCell(row, 2).setCellValue(resultMap.get("NAME3"));
getCell(row, 3).setCellValue(resultMap.get("NAME4"));
getCell(row, 4).setCellValue(resultMap.get("NAME5"));
}
}
private Cell getCell(Row row, int index) {
Cell cell = row.getCell(index);
if (cell == null) cell = row.createCell(index);
return cell;
}
}