SpringBoot_报表_POI

依赖pom.xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
</dependency>

配置文件bootstrap.yml

配置源码

可以使用工具类ExcelImportUtil、ExcelExportUtil

导入报表

/**
 * 导入Excel，添加用户
 *  文件上传：springboot
 */
@RequestMapping(value="/user/import",method = RequestMethod.POST)
public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception {
    //1.解析Excel
    //1.1.根据Excel文件创建工作簿
    Workbook wb = new XSSFWorkbook(file.getInputStream());
    //1.2.获取Sheet
    Sheet sheet = wb.getSheetAt(0);//参数：索引
    //1.3.获取Sheet中的每一行，和每一个单元格
    //2.获取用户数据列表
    List<User> list = new ArrayList<>();
    System.out.println(sheet.getLastRowNum());
    for (int rowNum = 1; rowNum<= sheet.getLastRowNum() ;rowNum ++) {
        Row row = sheet.getRow(rowNum);//根据索引获取每一个行
        Object [] values = new Object[row.getLastCellNum()];
        for(int cellNum=1;cellNum< row.getLastCellNum(); cellNum ++) {
            Cell cell = row.getCell(cellNum);
            Object value = getCellValue(cell);
            values[cellNum] = value;
        }
        User user = new User(values);
        list.add(user);
    }
    //3.批量保存用户
    userService.saveAll(list,companyId,companyName);
    return new Result(ResultCode.SUCCESS);
}

导出报表

@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable(name = "month") String month) throws Exception {
    //1.构造数据
    List<EmployeeReportResult> list =
    userCompanyPersonalService.findByReport(companyId,month+"%");
    //2.创建工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    //3.构造sheet
    String[] titles = {"编号", "姓名", "手机","最高学历", "国家地区", "护照号", "籍贯",
    "生日", "属相","入职时间","离职类型","离职原因","离职时间"};
    Sheet sheet = workbook.createSheet();
    Row row = sheet.createRow(0);
    AtomicInteger headersAi = new AtomicInteger();
    for (String title : titles) {
	    Cell cell = row.createCell(headersAi.getAndIncrement());
	    cell.setCellValue(title);
    }
    AtomicInteger datasAi = new AtomicInteger(1);
    Cell cell = null;
    for (EmployeeReportResult report : list) {
	    Row dataRow = sheet.createRow(datasAi.getAndIncrement());
	    //编号
	    cell = dataRow.createCell(0);
	    cell.setCellValue(report.getUserId());
	    //姓名
	    cell = dataRow.createCell(1);
	    cell.setCellValue(report.getUsername());
	    //手机
	    cell = dataRow.createCell(2);
	    cell.setCellValue(report.getMobile());
	    //最高学历
	    cell = dataRow.createCell(3);
	    cell.setCellValue(report.getTheHighestDegreeOfEducation());
	    //国家地区
	    cell = dataRow.createCell(4);
	    cell.setCellValue(report.getNationalArea());
	    //护照号
	    cell = dataRow.createCell(5);
	    cell.setCellValue(report.getPassportNo());
	    //籍贯
	    cell = dataRow.createCell(6);
	    cell.setCellValue(report.getNativePlace());
	    //生日
	    cell = dataRow.createCell(7);
	    cell.setCellValue(report.getBirthday());
	    //属相
	    cell = dataRow.createCell(8);
	    cell.setCellValue(report.getZodiac());
	    //入职时间
	    cell = dataRow.createCell(9);
	    cell.setCellValue(report.getTimeOfEntry());
	    //离职类型
	    cell = dataRow.createCell(10);
	    cell.setCellValue(report.getTypeOfTurnover());
	    //离职原因
	    cell = dataRow.createCell(11);
	    cell.setCellValue(report.getReasonsForLeaving());
	    //离职时间
	    cell = dataRow.createCell(12);
	    cell.setCellValue(report.getResignationTime());
    }
    String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8");
    response.setContentType("application/octet-stream");
    response.setHeader("content-disposition", "attachment;filename=" + new
    String(fileName.getBytes("ISO8859-1")));
    response.setHeader("filename", fileName);
    workbook.write(response.getOutputStream());
}

采用模板导出报表

    /**
     * 采用模板打印的形式完成报表生成
     *      模板
     *  参数：
     *      年月-月（2018-02%）
     */
    @RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
    public void export(@PathVariable String month) throws Exception {
        //1.获取报表数据
        List<EmployeeReportResult> list = userCompanyPersonalService.findByReport(companyId,month);

        //2.加载模板
        Resource resource = new ClassPathResource("excel-template/template-employee.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());

        //3.根据模板创建工作簿
        Workbook wb = new XSSFWorkbook(fis);
        //4.读取工作表
        Sheet sheet = wb.getSheetAt(0);  //第一个
        //5.抽取公共样式
        Row row = sheet.getRow(2); //抽取第三行的样式
        CellStyle styles [] = new CellStyle[row.getLastCellNum()];
        for(int i=0;i<row.getLastCellNum();i++) {
            Cell cell = row.getCell(i);
            styles[i] = cell.getCellStyle();
        }
        //6.构造单元格
        int rowIndex = 2;
        Cell cell=null;
        for (EmployeeReportResult employeeReportResult : list) {
            row = sheet.createRow(rowIndex++);  
            // 编号,
            cell = row.createCell(0);
            cell.setCellValue(employeeReportResult.getUserId());
            cell.setCellStyle(styles[0]);
            // 姓名,
            cell = row.createCell(1);
            cell.setCellValue(employeeReportResult.getUsername());
            cell.setCellStyle(styles[1]);
            // 手机,
            cell = row.createCell(2);
            cell.setCellValue(employeeReportResult.getMobile());
            cell.setCellStyle(styles[2]);
            // 最高学历,
            cell = row.createCell(3);
            cell.setCellValue(employeeReportResult.getTheHighestDegreeOfEducation());
            cell.setCellStyle(styles[3]);
            // 国家地区,
            cell = row.createCell(4);
            cell.setCellValue(employeeReportResult.getNationalArea());
            cell.setCellStyle(styles[4]);
            // 护照号,
            cell = row.createCell(5);
            cell.setCellValue(employeeReportResult.getPassportNo());
            cell.setCellStyle(styles[5]);
            // 籍贯,
            cell = row.createCell(6);
            cell.setCellValue(employeeReportResult.getNativePlace());
            cell.setCellStyle(styles[6]);
            // 生日,
            cell = row.createCell(7);
            cell.setCellValue(employeeReportResult.getBirthday());
            cell.setCellStyle(styles[7]);
            // 属相,
            cell = row.createCell(8);
            cell.setCellValue(employeeReportResult.getZodiac());
            cell.setCellStyle(styles[8]);
            // 入职时间,
            cell = row.createCell(9);
            cell.setCellValue(employeeReportResult.getTimeOfEntry());
            cell.setCellStyle(styles[9]);
            // 离职类型,
            cell = row.createCell(10);
            cell.setCellValue(employeeReportResult.getTypeOfTurnover());
            cell.setCellStyle(styles[10]);
            // 离职原因,
            cell = row.createCell(11);
            cell.setCellValue(employeeReportResult.getReasonsForLeaving());
            cell.setCellStyle(styles[11]);
            // 离职时间
            cell = row.createCell(12);
            cell.setCellValue(employeeReportResult.getResignationTime());
            cell.setCellStyle(styles[12]);
        }
        //7.下载
        //3.完成下载
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        new DownloadUtils().download(os,response,month+"人事报表.xlsx");
    }

百万数据报表导出

问题：
基于XSSFWork导出Excel报表，是通过将所有单元格对象保存到内存中，当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时，随着表格的不断创建，内存中对象越来越多，直至内存溢出。Apache Poi提供了SXSSFWork对象，专门用于处理大数据量Excel报表导出。

分析：
在实例化SXSSFWork这个对象时，可以指定在内存中所产生的POI导出相关对象的数量（默认100），一旦内存中的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中（XML的文件格式），就可以将这些对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。

解决方法：
在原有代码的基础上替换之前的XSSFWorkbook，使用SXSSFWorkbook完成创建过程即可。
XSSFWorkbook workbook = new XSSFWorkbook();//工作簿创建替换为一下代码
SXSSFWorkbook workbook = new SXSSFWorkbook();

百万数据报表导入

方案：
使用POI基于事件模式解析案例提供的Excel文件。

代码实现：
（1）自定义事件器

//自定义Sheet基于Sax的解析处理器
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
    //封装实体对象
    private PoiEntity entity;
    /**
    * 解析行开始
    */
    @Override
    public void startRow(int rowNum) {
	    if (rowNum >0 ) {
	    	entity = new PoiEntity();
	    }
    }
    /**
    * 解析每一个单元格
    */
    @Override
    public void cell(String cellReference, String formattedValue, XSSFComment comment)
    {
	    if(entity != null) {
		    switch (cellReference.substring(0, 1)) {
			    case "A":
			    entity.setId(formattedValue);
			    break;
			    case "B":
			    entity.setBreast(formattedValue);
			    break;
			    case "C":
			    entity.setAdipocytes(formattedValue);
			    break;
			    case "D":
			    entity.setNegative(formattedValue);
			    break;
			    case "E":
			    entity.setStaining(formattedValue);
			    break;
			    case "F":
			    entity.setSupportive(formattedValue);
			    break;
			    default:
			    break;
		    }
	    }
    }
    /**
    * 解析行结束
    */
    public void endRow(int rowNum) {
        //数据处理
 	   System.out.println(entity);
    }
    //处理头尾
    public void headerFooter(String text, boolean isHeader, String tagName) {
    }
}

（2）自定义解析

/**
* 自定义Excel解析器
*/
public class ExcelParser {
    public void parse (String path) throws Exception {
	    //1.根据Excel获取OPCPackage对象
	    OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);
	    try {
		    //2.创建XSSFReader对象
		    XSSFReader reader = new XSSFReader(pkg);
		    //3.获取SharedStringsTable对象
		    SharedStringsTable sst = reader.getSharedStringsTable();
		    //4.获取StylesTable对象
		    StylesTable styles = reader.getStylesTable();
		    //5.创建Sax的XmlReader对象
		    XMLReader parser = XMLReaderFactory.createXMLReader();
		    //6.设置处理器   调用自定义事件SheetHandler，完成数据处理
		    parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new
		    SheetHandler(), false));
		    XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)
		    reader.getSheetsData();
		    //7.逐行读取
		    while (sheets.hasNext()) {
			    InputStream sheetstream = sheets.next();
			    InputSource sheetSource = new InputSource(sheetstream);
			    try {
				    parser.parse(sheetSource);
			    } finally {
			    	sheetstream.close();
			    }
		    }
	    } finally {
	   	 	pkg.close();
	    }
    }
}

（3）使用new ExcelParser().parse(“C:\Users\ThinkPad\Desktop\demo.xlsx”);