java poi动态生成单个或多个excel文件及数据渲染

目录

• 引入poi相关maven依赖
• 代码实现
  • excel模版动态生成方法
  • 配置完模版后，sheet页数据渲染方法实现
• 记录-多个sheet页源码解析

最近项目上，需要使用动态数据源来生成excel数据文件，需支持单个sheet页或者多个sheet页导出。在此记录下通过java poi实现的方法，有些方法在百度很容易搜到，但大部分实现时，还是有些问题。在此记录完整的实现方法。

引入poi相关maven依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>net.sf.jxls</groupId>
    <artifactId>jxls-core</artifactId>
    <version>1.0.3</version>
</dependency>

代码实现

excel模版动态生成方法

//列对象

public class ExcelColumnVO {

    /**
     * 列名code
     */
    private String columnCode;
    /**
     * 列名描述
     */
    private String columnDesc;
    /**
     * 列序号
     */
    private Integer seq;

}

/**
 * 创建临时excel单个sheet页模版文件
 * @param tempPath 模版预存路径
 * @param columnList excel列名数组
 * @return
 * @throws IOException
 */
public static File createSingleSheetTempExcel(String tempPath, List<ExcelColumnVO> columnList) throws IOException {
    Map<String, List<ExcelColumnVO>> columnMoreSheetMap = new HashMap<>();
    columnMoreSheetMap.put(EXCEL_DEFAULT_SHEET_NAME, columnList);
    return createMoreSheetTempExcel(tempPath, columnMoreSheetMap);
}

/**
 * 创建临时excel多个sheet页模版文件
 * @param tempPath 模版路径
 * @param columnMoreSheetMap 每个sheet页列名属性，每个sheet页map key值需指定为data1,data2,data3...
 * @return
 * @throws IOException
 */
public static File createMoreSheetTempExcel(String tempPath, Map<String, List<ExcelColumnVO>> columnMoreSheetMap) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    // 创建一个目录和文件名
    File tempFile = new File(tempPath);
    FileOutputStream out = new FileOutputStream(tempFile);
    Integer i = 1;
    for(Map.Entry<String, List<ExcelColumnVO>> entry : columnMoreSheetMap.entrySet()){
        String sheetName = entry.getKey();
        List<ExcelColumnVO> columnList = entry.getValue();
        // 创建一个工作表
        XSSFSheet spreadsheet = workbook.createSheet(sheetName);
        // 创建一行
        XSSFRow firstRow = spreadsheet.createRow(0);
        XSSFRow twoRow = spreadsheet.createRow(2);
        AtomicInteger firstCellid = new AtomicInteger();
        AtomicInteger secondCellid = new AtomicInteger();

        //设置模版行字段
        List<ExcelColumnVO> columnSortList = columnList.stream()
                .sorted(Comparator.comparing(ExcelColumnVO :: getSeq))
                .collect(Collectors.toList());
        int headerCellCount = 0;
        for (ExcelColumnVO columnVO : columnSortList) {
            String columnDesc = columnVO.getColumnDesc();
            spreadsheet.setColumnWidth(firstCellid.getAndIncrement(), columnDesc.getBytes().length * 2 * 256);
            Cell firstCell = firstRow.createCell(headerCellCount);
            firstCell.setCellValue(columnDesc);
            Cell secondCell = twoRow.createCell(secondCellid.getAndIncrement());
            secondCell.setCellValue("${data." + columnVO.getColumnCode() + "}");
            headerCellCount ++;
        }

        XSSFCellStyle style6 = workbook.createCellStyle();
        style6.setAlignment(HorizontalAlignment.CENTER);
        XSSFRow row1 = spreadsheet.createRow(1);
        Cell cell = row1.createCell(0);
        cell.setCellValue("<jx:forEach items = "${data" + i + "}" var="data">");
        cell.setCellStyle(style6);
        CellRangeAddress region = new CellRangeAddress(1, 1, 0, firstCellid.intValue());
        spreadsheet.addMergedRegion(region);

        XSSFRow thirdRow = spreadsheet.createRow(3);
        Cell thirdCell = thirdRow.createCell(0);
        thirdCell.setCellValue("</jx:forEach>");

        i ++;
    }

    // 输出
    workbook.write(out);
    out.close();
    return tempFile;
}

• Excel模版生成样式如下图

<jx:forEach items = "${data1}" var="data"> 中"data1"可以随意命名，但需要与自己定义的EXCEL_DEFAULT_SHEET_NAME值一致，多个sheet页的时候，就从data1，data2，data3...递增开始就可以了，这样可以方便统一管理。多个sheet页原理一样，只不过要保证每个sheet页的循环便利的数据源命名要不一样。

配置完模版后，sheet页数据渲染方法实现

• 使用的工具类

  • XLSTransformer.transformXLS 方法解析

/**

     * 

     * @param srcFilePath Excel模版文件路径

     * @param beanParams Map<String, Object>类型参数，key为模版中值对象名

     * @param destFilePath 导出文件路径

     */

public void transformXLS(String srcFilePath, Map beanParams, String destFilePath) throws ParsePropertyException, IOException, InvalidFormatException {

        InputStream is = new BufferedInputStream(new FileInputStream(srcFilePath));

        Workbook workbook = this.transformXLS(is, beanParams);

        OutputStream os = new BufferedOutputStream(new FileOutputStream(destFilePath));

        workbook.write(os);

        is.close();

        os.flush();

        os.close();

    }

• 代码实现

/**
 * 导出单个sheet页excel文件
 * @param exportPath
 * @param columnList
 * @param columnValueList
 */
public static File export(String exportPath, List<ExcelColumnVO> columnList, List<Map<String, Object>> columnValueList) {
    Map<String, Object> paramMap = new HashMap<String, Object>();
    paramMap.put(EXCEL_DEFAULT_DATA, columnValueList);
    XLSTransformer transformer = new XLSTransformer();

    try {
        String tempPath = getTempFilePath();
        File tempFile = createSingleSheetTempExcel(tempPath, columnList);
        transformer.transformXLS(tempPath, paramMap, exportPath);

        tempFile.delete();
    } catch (ParsePropertyException e) {
        LOGGER.error(e.getMessage());
    } catch (IOException e) {
        LOGGER.error(e.getMessage());
    } catch (InvalidFormatException e) {
        LOGGER.error(e.getMessage());
    }
    return new File(exportPath);
}

/**
 * 创建临时excel单个sheet页模版文件
 * @param tempPath 模版预存路径
 * @param columnList excel列名数组
 * @return
 * @throws IOException
 */
public static File createSingleSheetTempExcel(String tempPath, List<ExcelColumnVO> columnList) throws IOException {
    Map<String, List<ExcelColumnVO>> columnMoreSheetMap = new HashMap<>();
    columnMoreSheetMap.put(EXCEL_DEFAULT_SHEET_NAME, columnList);
    return createMoreSheetTempExcel(tempPath, columnMoreSheetMap);
}

/**
 * 导出多个sheet页excel文件
 */
public static File exportMoreSheet(String exportPath, Map<String, List<ExcelColumnVO>> columnMoreSheetMap, Map<String, List<Map<String, Object>>> columnValueMap) {
    XLSTransformer transformer = new XLSTransformer();
    File exportFile = new File(exportPath);
    try {
        String tempPath = getTempFilePath();
        File tempFile = createMoreSheetTempExcel(tempPath, columnMoreSheetMap);
        Workbook hssfWorkbook = WorkbookFactory.create(new FileInputStream(tempFile));
        transformer.transformWorkbook(hssfWorkbook, columnValueMap);
        FileOutputStream fileOut = new FileOutputStream(exportFile);

        hssfWorkbook.write(fileOut);
        fileOut.close();

        tempFile.delete();
    } catch (ParsePropertyException e) {
        LOGGER.error(e.getMessage());
    } catch (IOException e) {
        LOGGER.error(e.getMessage());
    } catch (InvalidFormatException e) {
        LOGGER.error(e.getMessage());
    }
    return exportFile;
}


/**
 * 创建临时excel多个sheet页模版文件
 * @param tempPath 模版路径
 * @param columnMoreSheetMap 每个sheet页列名属性，每个sheet页map key值需指定为data1,data2,data3...
 * @return
 * @throws IOException
 */
public static File createMoreSheetTempExcel(String tempPath, Map<String, List<ExcelColumnVO>> columnMoreSheetMap) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    // 创建一个目录和文件名
    File tempFile = new File(tempPath);
    FileOutputStream out = new FileOutputStream(tempFile);
    Integer i = 1;
    for(Map.Entry<String, List<ExcelColumnVO>> entry : columnMoreSheetMap.entrySet()){
        String sheetName = entry.getKey();
        List<ExcelColumnVO> columnList = entry.getValue();
        // 创建一个工作表
        XSSFSheet spreadsheet = workbook.createSheet(sheetName);
        // 创建一行
        XSSFRow firstRow = spreadsheet.createRow(0);
        XSSFRow twoRow = spreadsheet.createRow(2);
        AtomicInteger firstCellid = new AtomicInteger();
        AtomicInteger secondCellid = new AtomicInteger();

        //设置模版行字段
        List<ExcelColumnVO> columnSortList = columnList.stream()
                .sorted(Comparator.comparing(ExcelColumnVO :: getSeq))
                .collect(Collectors.toList());
        int headerCellCount = 0;
        for (ExcelColumnVO columnVO : columnSortList) {
            String columnDesc = columnVO.getColumnDesc();
            spreadsheet.setColumnWidth(firstCellid.getAndIncrement(), columnDesc.getBytes().length * 2 * 256);
            Cell firstCell = firstRow.createCell(headerCellCount);
            firstCell.setCellValue(columnDesc);
            Cell secondCell = twoRow.createCell(secondCellid.getAndIncrement());
            secondCell.setCellValue("${data." + columnVO.getColumnCode() + "}");
            headerCellCount ++;
        }

        XSSFCellStyle style6 = workbook.createCellStyle();
        style6.setAlignment(HorizontalAlignment.CENTER);
        XSSFRow row1 = spreadsheet.createRow(1);
        Cell cell = row1.createCell(0);
        cell.setCellValue("<jx:forEach items = "${data" + i + "}" var="data">");
        cell.setCellStyle(style6);
        CellRangeAddress region = new CellRangeAddress(1, 1, 0, firstCellid.intValue());
        spreadsheet.addMergedRegion(region);

        XSSFRow thirdRow = spreadsheet.createRow(3);
        Cell thirdCell = thirdRow.createCell(0);
        thirdCell.setCellValue("</jx:forEach>");

        i ++;
    }

    // 输出
    workbook.write(out);
    out.close();
    return tempFile;
}

/**
 * 生成一个随机文件路径
 * @return
 */
public static String getTempFilePath() {
    String tmpDir = System.getProperty("java.io.tmpdir");
    return tmpDir + UUID.randomUUID().toString().replace("-", "") + ".xlsx";
}

记录-多个sheet页源码解析

• XLSTransformer类中提供了transformMultipleSheetsList方法，但在具体调用时，发现每个sheet页的参数实现的并不是友好，源码如下
  

• • beanParams在接受参数时，key值限制的很死，字符串拼接，动态效果不好，所以我直接没使用这个方法，因为在这个方法里我看到了
    transformWorkbook(hssfWorkbook, (Map)beanParams)，我不用它自带的for循环获取参数值，我自己重新封装
• • 改写后的方法如下，自己重新分装每个sheet页的数据，transformer.transformWorkbook(hssfWorkbook, columnValueMap)渲染，最后写入指定目录的文件，搞定

public static File exportMoreSheet(String exportPath, Map<String, List<ExcelColumnVO>> columnMoreSheetMap, Map<String, List<Map<String, Object>>> columnValueMap) {
    XLSTransformer transformer = new XLSTransformer();
    File exportFile = new File(exportPath);
    try {
        String tempPath = getTempFilePath();
        File tempFile = createMoreSheetTempExcel(tempPath, columnMoreSheetMap);
        Workbook hssfWorkbook = WorkbookFactory.create(new FileInputStream(tempFile));
        transformer.transformWorkbook(hssfWorkbook, columnValueMap);
        FileOutputStream fileOut = new FileOutputStream(exportFile);

        hssfWorkbook.write(fileOut);
        fileOut.close();

        tempFile.delete();
    } catch (ParsePropertyException e) {
        LOGGER.error(e.getMessage());
    } catch (IOException e) {
        LOGGER.error(e.getMessage());
    } catch (InvalidFormatException e) {
        LOGGER.error(e.getMessage());
    }
    return exportFile;
}