背景

在项目中，有时会频繁用到Excel读写操作，这里做一下常用操作的总结。

内容

依赖

implementation 'org.apache.poi:poi:3.15'
implementation 'org.apache.poi:poi-ooxml:3.15'

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

读Excel示例

try {
    Workbook workbook = WorkbookFactory.create(file.getInputStream());
    
    Sheet sheet = workbook.getSheetAt(0);
    for (int rowNum=1; rowNum<=sheet.getLastRowNum(); rowNum++) {
        Row row = sheet.getRow(rowNum);
        System.out.println(row.getCell(0).toString());
    }
} catch (InvalidFormatException | IOException e) {
    e.printStackTrace();
}

写Excel示例

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("sheet1");

// 表头
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("X");

// 内容
for (int index=1; index<10; index++) {
    row = sheet.createRow(index);
    row.createCell(0).setCellValue("90");
}

// 写入文件
try {
    FileOutputStream out = new FileOutputStream("rain.xlsx");
    workbook.write(out);
    out.close();
} catch (IOException e) {
    e.printStackTrace();
}

写入Excel并直接返回给前端

   public void downloadExcel(String param, HttpServletResponse response) {

        final String fileName = "我是Excel.xlsx";

        // 创建
        Workbook workbook = new XSSFWorkbook();
        // 写入内容
        Sheet sheet = workbook.createSheet("sheet1");
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("X");

        // 导出文件
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        workbook.write(response.getOutputStream());
    }