SpringBoot_报表_POI

忘是亡心i 2021-10-06 03:58 347阅读 0赞

### SpringBoot\_报表\_POI ###

*  依赖pom.xml
 *  配置文件bootstrap.yml
 *  配置源码
 *   *  导入报表
     *  导出报表
     *  采用模板导出报表
     *  百万数据报表导出
     *  百万数据报表导入

# 依赖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”);