Excel之Apache POI

基本概念

Apache POI是用来处理Excel文件的，Excel中的概念和Poi对象关系如下：

| Excel中的概念 | Poi对应的对象 | | ---------------- | ---------------------------------------- | | Excel 文件 | HSSFWorkbook (xls)XSSFWorkbook(xlsx) | | Excel 的工作表 | HSSFSheet | | Excel 的行 | HSSFRow | | Excel 中的单元格 | HSSFCell | | Excel 字体 | HSSFFont | | Excel 单元格样式 | HSSFCellStyle | | Excel 颜色 | HSSFColor | | 合并单元格 | CellRangeAddress |

引入依赖

```java org.apache.poi poi 5.2.1

org.apache.poi poi-ooxml 5.2.1 ```

读取数据

```java public static void main(String[] args) { try { FileInputStream file = new FileInputStream(new File("C:\Users\test.xlsx"));

XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterable = sheet.iterator();
    while (rowIterable.hasNext()){
        Row row = rowIterable.next();
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()){
            Cell cell = cellIterator.next();
            switch (cell.getCellType()){
                case NUMERIC:
                    System.out.println(cell.getNumericCellValue());
                    break;
                case STRING:
                    System.out.println(cell.getStringCellValue());
                    break;
            }
        }
        System.out.println("==============Excel一行结束=============");
    }

    file.close();
} catch (Exception e) {
    e.printStackTrace();
}

} ```

插入数据

```java public static void main(String[] args) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("mysheet");

// 模拟待写入数据
Map<String,Object[]> data = new TreeMap<>();
data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
data.put("2", new Object[] {1, "Amit", "Shukla"});
data.put("3", new Object[] {2, "Lokesh", "Gupta"});
data.put("4", new Object[] {3, "John", "Adwards"});
data.put("5", new Object[] {4, "Brian", "Schultz"});

// 遍历数据写入表中
Set<String> keySet = data.keySet();
int rowNum = 0;
for (String key : keySet){
    Row row = sheet.createRow(rowNum++);
    Object [] objArr = data.get(key);
    int cellNum = 0;
    for (Object obj: objArr){
        Cell cell  = row.createCell(cellNum++);
        if (obj instanceof String){
            cell.setCellValue((String)obj);
        }else if(obj instanceof Integer){
            cell.setCellValue((Integer)obj);
        }
    }
}

try {
    File file = new File("C:\\Users\\xxx.xlsx");
    FileOutputStream out = new FileOutputStream(file);
    workbook.write(out);
} catch (Exception e) {
    e.printStackTrace();
}

} ```

合并单元格

```java public static void main(String[] args) throws IOException { Workbook wb=new XSSFWorkbook(); Sheet sheet=wb.createSheet();

// 设定被合并的单元格范围, 可以在一个表中合并多个单元格
CellRangeAddress cra=new CellRangeAddress(2, 3, 3, 5); 
sheet.addMergedRegion(cra);

//  设置另一处被合并的单元格
//  CellRangeAddress cra2=new CellRangeAddress(5, 8, 4, 7);
//  sheet.addMergedRegion(cra2);

// 只能在被合并的单元格的最左上角的位置写入数据, 其它被合并的单元格位置无法写入数据
Row row = sheet.createRow(2);
Cell cell_1 = row.createCell(3);
cell_1.setCellValue("When you're right , no one remembers, when you're wrong ,no one forgets .");

FileOutputStream fos=new FileOutputStream("C:\\Users\\qqqq.xlsx");
wb.write(fos);
fos.close();

} ```

相关文章

SpringBoot图文教程9—SpringBoot 导入导出 Excel 「Apache Poi」 - 掘金 (juejin.cn)

springboot中使用poi导出excel - 掘金 (juejin.cn)