【笔记】POI学习笔记

发表于 更新于 阅读次数：

前言

Apache POI - the Java API for Microsoft Documents（官网）

添加依赖

pom.xml
1
2
3
4
5
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.9</version>
</dependency>

创建Excel文件对象

xls

1
Workbook workbook = new HSSFWorkbook();
1
2
FileInputStream fis = new FileInputStream("<file>.xls");
Workbook workbook = new HSSFWorkbook(fis);

xlsx

1
Workbook workbook = new XSSFWorkbook();
1
2
FileInputStream fis = new FileInputStream("<file>.xls");
Workbook workbook = new XSSFWorkbook(fis);

工作簿

新增工作簿

1
Sheet sheet = workbook.createSheet("Sheet1");

获取工作簿

1
Sheet sheet = workbook.getSheet("Sheet1");

行

创建行

<index>：索引，从0开始

1
Row row = sheet.createRow(<index>);

获取行

1
Row row = sheet.getRow(<index>);

获取所有行数

1
int rowCount = sheet.getPhysicalNumberOfRows();

列

创建单元格

1
Cell cell = row.createCell(<index>);

获取单元格

1
Cell cell = row.getCell(<index>);

单元格内容

设置单元格内容

1
cell.setCellValue("文本内容");

获取单元格内容

字符串
1
String cellValue = cell.getStringCellValue();
浮点型
1
double cellValue = cell.getNumericCellValue();
布尔型
1
boolean cellValue = cell.getBooleanCellValue();

修改单元格数据类型

1
cell.setCellType(HSSFCell.CELL_TYPE_STRING);

获取单元格数据类型

1
int cellType = cell.getCellType();
根据单元格数据类型获取单元格内容
1
2
3
4
5
6
7
8
9
10
11
switch (cellType) {
    case HSSFCell.CELL_TYPE_STRING:
        String cellValue = cell.getStringCellValue();
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        double cellValue = cell.getNumericCellValue();
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        boolean cellValue = cell.getBooleanCellValue();
        break;
}

获取所有列数

1
int cellCount = row.getPhysicalNumberOfCells();

保存文件

1
2
3
FileOutputStream fos = new FileOutputStream("<file>.xls");
workbook.write(fos);
fos.close();

读取文件

1
FileInputStream fis = new FileInputStream("<file>.xls");

完成

参考文献

哔哩哔哩——Java大联盟