百万数据POI操作(一)
概述
我们都知道Excel可以分为早期的Excel2003版本(使用POI的HSSF对象操作)和Excel2007版本(使用POI的XSSF操作),两者对百万数据的支持如下:
- Excel 2003:在POI中使用HSSF对象时,excel 2003最多只允许存储65536条数据,一般用来处理较少的数据量。这时对于百万级别数据,Excel肯定容纳不了。
- Excel 2007:当POI升级到XSSF对象时,它可以直接支持excel2007以上版本,因为它采用ooxml格式。这时excel可以支持1048576条数据,单个sheet表就支持近百万条数据。但实际运行时还可能存在问题,原因是执行POI报表所产生的行对象,单元格对象,字体对象,他们都不会销毁,这就导致OOM的风险
JDK性能监控工具
没有性能监控工具一切推论都只能停留在理论阶段,我们可以使用Java的性能监控工具来监视程序的运行情况,包括CUP,垃圾回收,内存的分配和使用情况,这让程序的运行阶段变得更加可控,也可以用来证明我们的推测。这里我们使用JDK提供的性能工具Jvisualvm来监控程序运行。
VisualVM 是Netbeans的profile子项目,已在JDK中自带,能够监控线程,内存情况,查看方法的CPU时间和内存中的对 象,已被GC的对象,反向查看分配的堆栈。
Jvisualvm位于JAVA_HOME/bin目录下,直接双击就可以打开该程序。如果只是监控本地的java进程,是不需要配置参数的,直接打开就能够进行监控。首先我们需要在本地打开一个Java程序,例如我打开员工后台管理系统进程,这时在jvisualvm界面就可以看到与IDEA相关的Java进程了
Jvisualvm的使用
Jvisualvm使用起来比较简单,双击点击当前运行的进程即可进入到程序的监控界面:
- 双击Jvisualvm.exe
- 概述:可以看到进程的启动参数。
- 监视:左上:cpu利用率,gc状态的监控,右上:堆利用率,永久内存区的利用率,左下:类的监控,右下:线程的监控
- 线程:能够显示线程的名称和运行的状态,在调试多线程时必不可少,而且可以点进一个线程查看这个线程的详细运行情况
百万数据POI操作(二)SXSSFWorkBook处理百万数据报表打印
需求
使用Apache POI完成百万数据量的Excel报表导出。
分析
基于XSSFWork导出Excel报表,是通过将所有单元格对象保存到内存中,当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时,随着表格的不断创建,内存中对象越来越多,直至内存溢出。ApachePoi提供了SXSSFWork对象,专门用于处理大数据量Excel报表导出。
在实例化SXSSFWork这个对象时,可以指定在内存中所产生的POI导出相关对象的数量(默认100),一旦内存中的对象的个数达到这个指定值时,就将内存中的这些对象的内容写入到磁盘中(XML的文件格式),就可以将这些对象从内存中销毁,以后只要达到这个值,就会以类似的处理方式处理,直至Excel导出完成。
Api
SXSSFWorkbook
-
处理大数据量excel报表生成的:将已经使用过的内存元素,即使删除(poi4采用)或者保存到本地磁盘(poi3)
-
使用条件:
(1) 不支持模板打印
(2)不支持太多的样式对象
实现
步骤1:模拟百万数据的导出
package cn.zonhar.web.controller.cargo;
import cn.zonhar.entity.cargo.Contract;
import cn.zonhar.entity.cargo.ContractExample;
import cn.zonhar.entity.system.User;
import cn.zonhar.entity.utils.DeleteStatus;
import cn.zonhar.entity.vo.ContractProductVo;
import cn.zonhar.service.cargo.ContractProductService;
import cn.zonhar.service.cargo.ContractService;
import cn.zonhar.web.controller.BaseController;
import cn.zonhar.web.utils.DownloadUtil;
import com.alibaba.dubbo.config.annotation.Reference;
import com.github.pagehelper.PageInfo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.List;
/**
* @author zonhar
* @date 2019/7/27
* 购销合同
*/
@Controller
@RequestMapping("/cargo/contract")
public class ContractController extends BaseController {
@Reference
private ContractService contractService;
@Reference
private ContractProductService contractProductService;
@RequestMapping(value = "/print", name = "去出货表页面")
public String toPrint() {
return "/cargo/print/contract-print";
}
/**
* 处理百万级导出
*
* @param inputDate 出货时间
* @return
*/
@RequestMapping(value = "/printExcel", name = "打印出货表")
public void printExcel(String inputDate) throws IOException {
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("出货表");
//出货表.xls ,设置每列列宽
sheet.setColumnWidth(0, 0 * 256);
sheet.setColumnWidth(1, 26 * 256);
sheet.setColumnWidth(2, 12 * 256);
sheet.setColumnWidth(3, 30 * 256);
sheet.setColumnWidth(4, 12 * 256);
sheet.setColumnWidth(5, 15 * 256);
sheet.setColumnWidth(6, 10 * 256);
sheet.setColumnWidth(7, 10 * 256);
sheet.setColumnWidth(8, 8 * 256);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));
/**
* 创建第一行
*/
Row row = sheet.createRow(0);
row.setHeightInPoints(36);
Cell cell = row.createCell(1);
cell.setCellStyle(this.bigTitle(workbook));
String value = inputDate.replace("-0", "-").replace("-", "年") + "月份出货表";
cell.setCellValue(value);
/**
* 创建第二行
*/
row = sheet.createRow(1);
row.setHeightInPoints(26);
String titles[] = new String[]{"客户", "订单号", "货号", "数量", "工厂",
"工厂交期", "船期", "贸易条款"};
for (int i = 0; i < titles.length; i++) {
cell = row.createCell(i + 1);
cell.setCellValue(titles[i]);
cell.setCellStyle(this.title(workbook));
}
/**
* 导出数据行
*/
String companyId = getLoginCompanyId();
List<ContractProductVo> list =
contractProductService.findByShipTime(companyId, inputDate);
if (list != null && list.size() > 0) {
int index = 2;
for (ContractProductVo cp : list) {
for (int i = 0; i < 6000; i++) {
row = sheet.createRow(index++);
row.setHeightInPoints(24);
cell = row.createCell(1);
cell.setCellValue(cp.getCustomName());
// cell.setCellStyle(this.text((workbook)));
cell = row.createCell(2);
cell.setCellValue(cp.getContractNo());
// cell.setCellStyle(this.text(workbook));
cell = row.createCell(3);
cell.setCellValue(cp.getProductNo());
// cell.setCellStyle(this.text(workbook));
cell = row.createCell(4);
cell.setCellValue(cp.getCnumber());
// cell.setCellStyle(this.text(workbook));
cell = row.createCell(5);
cell.setCellValue(cp.getFactoryName());
// cell.setCellStyle(this.text(workbook));
cell = row.createCell(6);
cell.setCellValue(cp.getDeliveryPeriod());
// cell.setCellStyle(this.text(workbook));
cell = row.createCell(7);
cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(cp.getShipTime()));
// cell.setCellStyle(this.text(workbook));
cell = row.createCell(8);
cell.setCellValue(cp.getTradeTerms());
// cell.setCellStyle(this.text(workbook));
}
}
}
//导出下载
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
new DownloadUtil().download(bos, response, "出货表.xlsx");
}
}
堆内存的消耗:
导出后的大小: