百(千)万级excel导出(流式)
背景介绍:我负责的有个条形码的模块,目前110w数据,未来还会继续添加,需要做一个Excel导出的功能。
与此相关环境: spring boot,mysql,myBatis
maven依赖: [ maven库地址 ]
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16-beta2</version>
</dependency>
主要思路: 页面发出导出请求,在controller里面做处理。Controller里面主要做:
- 1.拿到请求参数
- 2.分页查询数据库
- 3.将每次分页查到的数据,装入SXSSFWorkbook
效果:我这个表也是十几个字段,无大字段。实测导出100w条用时61s,excel文件大小68M;导出1000w条534s,excel文件大小464M。
代码不是很规范,各位大佬高抬贵手 ———— o( ̄▽ ̄)d
下面是controller源码
@RequestMapping(value = "/exportSelectResult", method = RequestMethod.GET)
@ResponseBody
@SuppressWarnings("resource")
public void exportSelectResult(HttpServletResponse response, @RequestParam String itemNo,
@RequestParam String itemName, @RequestParam String productArea) {
long startTime = System.currentTimeMillis();
List<BarCodeForm> list = null;
BarCodeForm bcf = new BarCodeForm();
bcf.setItemNo(itemNo);
bcf.setItemName(itemName);
bcf.setProductArea(productArea);
int count = barCodeService.getSelectTotal(bcf).intValue();
// 定义每5w条记录放在一个sheet
int everySheet = 50000;
int cycle = (int) Math.ceil(count / everySheet);
String[] tableHeader = { "条形码编号", "商品名称", "备注", "容量/体积", "单位", "产地", "创建时间", "创建人", "修改时间", "修改人" };
// 构造一个空的工作簿并指定行访问窗口。
SXSSFWorkbook wb = new SXSSFWorkbook(1);
Sheet sh = null;
Row row = null;
Cell cell = null;
for (int i = 0; i <= cycle; i++) {
list = barCodeService.getAllBarCode(bcf, i+1, everySheet);
if (list == null || list.size() == 0) {
break;
}
sh = wb.createSheet(String.valueOf(i+1));
BarCodePoiUtil.setExcelContent(list, tableHeader, row, sh, cell);
}
BufferedOutputStream out = null;
String filename = "BarCodeDatas.xlsx";
try {
// 设置文件名
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
out = new BufferedOutputStream(response.getOutputStream());
wb.write(out);
out.flush();
} catch (IOException e) {
e.printStackTrace();
new RuntimeException("导出:条形码查询后的列表失败");
} finally {
try {
if (out != null) {
out.close();
}
// 处理在磁盘上备份此工作簿的临时文件
wb.dispose();
} catch (IOException e) {
e.printStackTrace();
}
}
long endTime = System.currentTimeMillis();
log.info("【 " + this.getClass().getName() + " 】【 " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + " 】 导出:条形码" + count + "条,用时:" + (endTime - startTime) / 1000 + " s");
}
(在Controller中被调用)工具方法:将查到的数据,装入sheet
public class BarCodePoiUtil {
public static void setExcelContent(List<BarCodeForm> list, String[] tableHeader, Row row, Sheet sh, Cell cell) {
// 创建表头
row = sh.createRow(0);
for (int b = 0; b < tableHeader.length; b++) {
cell = row.createCell(b);
cell.setCellValue(tableHeader[b]);
}
// 创建表内容
for (int a = 0; a < list.size(); a++) {
row = sh.createRow(a + 1);
for (int b = 0; b < tableHeader.length; b++) {
cell = row.createCell(b);
switch (b) {
case 0:// 条形码编号
cell.setCellValue(list.get(a).getItemNo() != null ? list.get(a).getItemNo() : "");
break;
case 1:// 商品名称
cell.setCellValue(list.get(a).getItemName() != null ? list.get(a).getItemName() : "");
break;
case 2:// 备注
cell.setCellValue(list.get(a).getPym() != null ? list.get(a).getPym() : "");
break;
case 3:// 容量/体积
cell.setCellValue(list.get(a).getItemSize() != null ? list.get(a).getItemSize() : "");
break;
case 4:// 单位
cell.setCellValue(list.get(a).getUnitNo() != null ? list.get(a).getUnitNo() : "");
break;
case 5:// 产地
cell.setCellValue(list.get(a).getProductArea() != null ? list.get(a).getProductArea() : "");
break;
case 6:// 创建时间
cell.setCellValue(list.get(a).getCreateTime() != null ? list.get(a).getCreateTime().toString() : "");
break;
case 7:// 创建人
cell.setCellValue(list.get(a).getCreateByName() != null ? list.get(a).getCreateByName().toString() : "");
break;
case 8:// 修改时间
cell.setCellValue(list.get(a).getUpdateTime() != null ? list.get(a).getUpdateTime().toString() : "");
break;
case 9:// 修改人
cell.setCellValue(list.get(a).getUpdateByName() != null ? list.get(a).getUpdateByName().toString() : "");
break;
default:
cell.setCellValue("");
}
}
}
}
}