Java 导出 Excel 文件

1.引入项目依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version>
</dependency>

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

2.构建方法进行行元素的类型转换

private static void createCell(HSSFRow row, int colNum, Object value) {
    HSSFCell cell = row.createCell(colNum);
    if (value instanceof String) {
        cell.setCellValue((String) value);
    } else if (value instanceof Long) {
        cell.setCellValue((Long) value);
    } else if (value instanceof Integer) {
        cell.setCellValue((Integer) value);
    } else if (value instanceof BigDecimal) {
        cell.setCellValue(((BigDecimal) value).doubleValue());
    }
}

3.创建表格文件及工作簿插入对应行内容

public static HSSFWorkbook exportToExcel(List<T> dataList) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");

        int rowNum = 0;
        for (T data : dataList) {
            HSSFRow row = sheet.createRow(rowNum++);
            int colNum = 0;
            // 插入传入列表对象的对应属性
            createCell(row, colNum++, data.get..());
            createCell(row, colNum++, data.get..());
        }

        return workbook;
    }

4.如若需将文件导出至本地

public static void exportExcel(List<T> list,String filePath){
        // 创建表格文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建对应工作簿
        HSSFSheet sheet = workbook.createSheet("Sheet1");

        int rowNum = 0;
        for (T data : list) {
            HSSFRow row = sheet.createRow(rowNum++);
            int colNum = 0;
            createCell(row, colNum++, data.get..());
            createCell(row, colNum++, data.get..());
        }

        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

5.踩的坑

如果遇到报错提示 java.lang.illegalargumentexception: invalid row number (65536) outside allowable range (0..65535)

原因是xls文件存在行数限制，所以需要将导出的文件名后缀改成 .xlsx 。同时将方法中的HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell 改成 XSSFWorkbook, XSSFSheet, XSSFRow, XSSFCell。