第一步:引入依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
第二步:
@GetMapping(value = "/exportAllSalesRecordSellIn")
public void exportAllSalesRecordSellIn(HttpServletResponse response) throws Exception {
Long t1 = System.currentTimeMillis();
System.out.println();
Map<String,Object> paramsMap = new HashMap<>();
paramsMap.put("page","1");paramsMap.put("limit","20000");
ExportParams params = new ExportParams("销售数据统计", "销售数据");
Workbook workbook = null;
PageData<Mid_salesRecordSellIn> allSalesRecordSellIn = midSalesRecordSellInService.getAllSalesRecordSellIn(paramsMap);
int totalPage = (int)Math.ceil((double)allSalesRecordSellIn.getTotal()/20000);
for(int currentPage = 1; currentPage <= totalPage; currentPage++){
paramsMap.put("page",String.valueOf(currentPage));
Long l1 = System.currentTimeMillis();
PageData<Mid_salesRecordSellIn> mr = midSalesRecordSellInService.getAllSalesRecordSellIn(paramsMap);
Long l2 = System.currentTimeMillis();
System.out.println("查询"+currentPage+"耗时:"+(l2-l1)/1000+"秒");
workbook = ExcelExportUtil.exportBigExcel(params, Mid_salesRecordSellIn.class, mr.getList());
}
ExcelExportUtil.closeExportBigExcel();
String fileName = "saleData.xlsx";
//告诉浏览器下载excel
downloadExcel(fileName, workbook, response);
Long t2 = System.currentTimeMillis();
System.out.println("总共耗时:"+(t2 - t1)/1000+"秒");
}
protected void downloadExcel(String filename, Workbook workbook, HttpServletResponse response) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
数据量非常大时,采用分页查询,分为多次写入,以免内存溢出。