Java 根据Excel模板 导出Excel报表

工作中肯定会有的报表导出的功能，咱先来理下思路

1. 先定义好模板文件，存放到一个有权限访问的目录内
2. 根据模板路径，获取模板文件

	/**
	* 根据模板的路径获取模板文件对象
	* classpath根目录下：/template/report.xlsx
	*/
	private File getTemplateFile(String template) throws URISyntaxException {
        URL resource = Thread.currentThread().getContextClassLoader().getResource("/");
        Path classpath = Paths.get(resource.toURI());
        Path path = Paths.get(classpath.toString(), template);
        return path.toFile();
    }

3. 读取报表需要的数据

	private List<Report> getReport(Long reportId, String day) {
        Map<String,Object> key=new HashMap<>(2);
        key.put("day",day);
        key.put("reportId",reportId);
        return dao.select("xxx.xxx",key);
    }

4. 处理数据，使其符合模板的数据格式，方便渲染数据（也可以直接单元格逐个填充，则可省略该步骤）

5. 填充数据

 	// 读取excel模板文件
   	File reportFile = getTemplateFile(report.getTemplatePath());
   	// 拼装excel报表文件
    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(reportFile));
    if (Objects.nonNull(workbook)) {
        if (CollectionUtils.isNotEmpty(modelList)) {
           // 逐个sheet填充数据
           sheetProcessorHolder.process(modelList,workbook);
        }
    }
    // 设置第一个sheet为活动sheet
    workbook.setActiveSheet(0);
    return workbook;

6. 关键的下载步骤：

 /**
   * 导出并下载excel报表文件
   *
   * @param reportId
   * @param day
   * @param response Http的响应对象，用于向用户输出excel文件流下载使用
   */
  public void exportExcel(String day, HttpServletResponse response) throws Exception {
      XSSFWorkbook workbook = getProcessor(report).process(report, day);
      String fileName = buildFileName(day);
      response.setHeader("Content-disposition", String.format("attachment; filename=%s.xlsx", URLEncoder.encode(fileName, "UTF-8")));
      response.setContentType("application/msexcel");
      try (ServletOutputStream outputStream = response.getOutputStream()) {
          workbook.write(outputStream);
          outputStream.flush();
      }
  }

7. 涉及的关键代码

/**
 * 根据坐标获取单元格A2，B4...
 *
 * @param sheet
 * @param cell   单元格列坐标A,B,C...
 * @param rowNum 单元格行坐标1,2,3...
 * @return
 */
private XSSFCell getXssfCell(XSSFSheet sheet, String cell, int rowNum) {
    CellReference reference = new CellReference(String.format("%s%d", cell, rowNum));
    XSSFRow row = sheet.getRow(reference.getRow());
    if (Objects.nonNull(row)) {
        return row.getCell(reference.getCol());
    }
    return null;
}

XSSFCell cell = getXssfCell(sheet, column, row);
// 单元格内赋值
cell.setCellValue(value); 
// 设置单元格的公式：SUM(B2:B6)
cell.setCellFormula(formula);

 //强制执行该sheet中所有公式
 sheet.setForceFormulaRecalculation(true);

/**
 * 插入一行
 *
 * @param sheet
 * @param starRow
 * @param rows
 * @return
 */
protected XSSFRow insertRow(XSSFSheet sheet, int starRow, int rows) {

    //参数：
    // startRow-要开始移位的行
    // endRow-要结束移位的行
    // n-要移位的行数
    // copyRowHeight-是否在移位期间复制行高
    // resetOriginalRowHeight-是否将原始行的高度设置为默认值
    sheet.shiftRows(starRow, sheet.getLastRowNum(), rows, true, false);
    // 所有行下移，并复制上一行的所有列的样式、公式
    for (int i = 0; i < rows; i++) {
        XSSFRow sourceRow = sheet.getRow(starRow - 1 - i);
        XSSFRow targetRow = sheet.createRow(starRow + i);
        targetRow.setHeight(sourceRow.getHeight());

        for (int cn = sourceRow.getFirstCellNum(); cn < sourceRow.getLastCellNum(); cn++) {
            XSSFCell sourceCell = sourceRow.getCell(cn);
            XSSFCell targetCell = targetRow.createCell(cn);

            targetCell.setCellStyle(sourceCell.getCellStyle());
        }
    }
    return sheet.getRow(starRow);
}

调用上面的插入1行的代码

List list = ....
for (int i = 0; i < list.size(); i++) {
     int rowNum = i + 2; 	//行号，1开始
     int rowIndex = i + 1; 	// 行的索引，0开始
     XSSFRow row;
     if (rowIndex == 1) {	//第一行都是表头，所以从第二行开始插入数据
         row = sheet.getRow(rowIndex);
     } else {
         // 当插入的行号大于第二行，则创建新行并复制上一行所有列的样式，公式等
         row = insertRow(sheet, rowIndex, 1);
     }
     后面的处理....
}

 // 获取行的有效使用的总列数
 int cellCount = row.getPhysicalNumberOfCells();