根据excel模板（带计算公式）实现导出功能

一、需求

    需求是需要实现一个导出excel文件的功能，但是提供的模板较为复杂，并且大量单元格附带公式。

ps：这只是需要导出的一小部分，带颜色的单元格都是含有公式的，而且需要导出的是一个xlsx中有3个类似的sheet。

二、思路

    既然提供模板文件已经很复杂化了，所以不打算用代码进行生成了（写起来会很麻烦），第三方也没有找到能满足我需求的，所以想直接读取现有的模板，然后将需要的值一个个填入进去，最后刷新sheet表的公式（这个很重要，不然只会显示填入了值，其他带公式的单元格不会自动计算数值）。

三、代码

直接上代码吧，其实也就是用了poi的基本操作

public void exportCalculation(Long id, HttpServletRequest request, HttpServletResponse response) {
        InputStream is = null;

        try {
            //根据id查询详细信息
            LandTaxCalculationEntity landTaxCalculation = this.baseMapper.selectOneLandTaxCalculation(id);
            //获取模板
            is = this.getClass().getClassLoader().getResourceAsStream("doc/土增税测算表导出模板.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            //设置第一张sheet表
            //setSheetOne(landTaxCalculation, workbook);
            //设置第二张sheet表（first表）
            setFirstAndExpectSheet(landTaxCalculation, workbook,1);
            //设置第三张sheet表（Expect表）
            setFirstAndExpectSheet(landTaxCalculation, workbook,2);
            //设置文件名称
            String table = "土增税测算表";
            //设置返回header
            setResponseHeader2007(table, request, response);
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
/**
     * 设置导出表
     * @param landTaxCalculation 源数据
     * @param workbook  模板文件
     * @param sheetNum  sheet编号 1：首次表  2：预期表
     */

/**
*   源数据和sheet编号是属于自己的业务字段，可以不用参考，主要还是思路，这里只贴出部分代码
*/
    private void setFirstAndExpectSheet(LandTaxCalculationEntity landTaxCalculation, XSSFWorkbook workbook,int sheetNum) {
        XSSFSheet sheetAt = workbook.getSheetAt(sheetNum);
        List<IncomeAreaSummaryEntity> incomeAreaSummary = null;

        if (sheetNum == 1){
            incomeAreaSummary = landTaxCalculation.getIncomeAreaSummaryFirst();
        } else {
            incomeAreaSummary = landTaxCalculation.getIncomeAreaSummaryExpect();
        }

        //设置与收入相关的面积汇总
        setIncomeAreaSummary(sheetAt, incomeAreaSummary);

        //强制刷新公式
        sheetAt.setForceFormulaRecalculation(true);
    }


/**
*    设置与收入相关的面积汇总
*/
private void setIncomeAreaSummary(XSSFSheet sheetAt, List<IncomeAreaSummaryEntity> incomeAreaSummary) {
        for (int i = 2; i <= 6 ; i++) {
            IncomeAreaSummaryEntity entity = incomeAreaSummary.get(i - 2);
            XSSFRow row1 = sheetAt.getRow(i);
            if (entity.getOrdinaryResidence() != null){
                XSSFCell cell22 = row1.getCell(2);
                cell22.setCellValue(entity.getOrdinaryResidence().doubleValue());
            }
            if (entity.getNoOrdinaryResidence() != null){
                XSSFCell cell23 = row1.getCell(3);
                cell23.setCellValue(entity.getNoOrdinaryResidence().doubleValue());
            }
            if (entity.getBusiness() != null){
                XSSFCell cell24 = row1.getCell(4);
                cell24.setCellValue(entity.getBusiness().doubleValue());
            }
            if (entity.getParkingSpace() != null){
                XSSFCell cell25 = row1.getCell(5);
                cell25.setCellValue(entity.getParkingSpace().doubleValue());
            }
            if (entity.getOffice() != null){
                XSSFCell cell26 = row1.getCell(6);
                cell26.setCellValue(entity.getOffice().doubleValue());
            }
            if (entity.getUndergroundFacilities() != null){
                XSSFCell cell27 = row1.getCell(7);
                cell27.setCellValue(entity.getUndergroundFacilities().doubleValue());
            }
            if (entity.getGroundFacilities() != null){
                XSSFCell cell28 = row1.getCell(8);
                cell28.setCellValue(entity.getGroundFacilities().doubleValue());
            }
            if (entity.getRemarks() != null){
                XSSFCell cell29 = row1.getCell(9);
                cell29.setCellValue(entity.getRemarks());
            }
        }
        XSSFRow row8 = sheetAt.getRow(8);
        IncomeAreaSummaryEntity entity6 = incomeAreaSummary.get(6);
        if (entity6.getOrdinaryResidence() != null){
            XSSFCell cell22 = row8.getCell(2);
            cell22.setCellValue(entity6.getOrdinaryResidence().doubleValue());
        }
        if (entity6.getNoOrdinaryResidence() != null){
            XSSFCell cell23 = row8.getCell(3);
            cell23.setCellValue(entity6.getNoOrdinaryResidence().doubleValue());
        }
        if (entity6.getBusiness() != null){
            XSSFCell cell24 = row8.getCell(4);
            cell24.setCellValue(entity6.getBusiness().doubleValue());
        }
        if (entity6.getParkingSpace() != null){
            XSSFCell cell25 = row8.getCell(5);
            cell25.setCellValue(entity6.getParkingSpace().doubleValue());
        }
        if (entity6.getOffice() != null){
            XSSFCell cell26 = row8.getCell(6);
            cell26.setCellValue(entity6.getOffice().doubleValue());
        }
        if (entity6.getRemarks() != null){
            XSSFCell cell29 = row8.getCell(9);
            cell29.setCellValue(entity6.getRemarks());
        }
    }

/**
     * 发送响应流方法
     *
     * @param request
     * @param response
     * @return void
     */
    public static void setResponseHeader2007(String table, HttpServletRequest request, HttpServletResponse response) {
        try {
            //响应头的处理
            response.setContentType("application/x-download");
            String fileName = table;
            if(!table.endsWith(".zip")){
                fileName = table + ".xlsx";
            }
            final String userAgent = request.getHeader("USER-AGENT");
            if (StringUtils.contains(userAgent, "MSIE") || StringUtils.contains(userAgent, "Trident") || StringUtils.contains(userAgent, "Edge")) {
                //IE浏览器
                fileName = URLEncoder.encode(fileName, "UTF8");
            } else if (StringUtils.contains(userAgent, "Mozilla")) {
                //google,火狐浏览器
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } else {
                //其他浏览器
                fileName = URLEncoder.encode(fileName, "UTF8");
            }
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        } catch (UnsupportedEncodingException e1) {
            log.error("列表导出URLEncoder filename UTF-8异常!" + e1);
        }
    }

四、补充

在模板中，还可能遇见需要插入行操作，提供一个方法，插入行

 /**
     * 向sheet表中插入行
     * @param sheet
     * @param starRow  开始行
     * @param rows  插入行数
     * @throws IOException
     */
    public static void excelInsertRow(XSSFSheet sheet,int starRow, int rows) throws IOException {
        sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), rows,true,false);
        starRow = starRow - 1;
        for (int i = 0; i < rows; i++) {
            XSSFRow sourceRow = null;
            XSSFRow targetRow = null;
            XSSFCell sourceCell = null;
            XSSFCell targetCell = null;
            short m;
            starRow = starRow + 1;
            sourceRow = sheet.getRow(starRow);
            targetRow = sheet.createRow(starRow + 1);
            targetRow.setHeight(sourceRow.getHeight());
            for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
                sourceCell = sourceRow.getCell(m);
                targetCell = targetRow.createCell(m);
                targetCell.setCellStyle(sourceCell.getCellStyle());
                targetCell.setCellType(sourceCell.getCellType());
            }
        }
    }

五、总结

对于复杂的excel导出，可以直接读取现有模板，在模板上进行填值操作，然后刷新sheet页公式，保证公式生效，完成此次需求。