## POI的组件
针对不同应用的文件，使用时需要引入对应的 maven 依赖，这里给出官方给出的指引。如果我们不使用 SAX API 方式读写 excel，一般只会用到这个 org.apache.poi.ss 中的 API，具体的实现类放在 org.apache.poi.hssf 或 org.apache.poi.xssf 。
| 组件 | 作用 | Maven依赖 |
| :---------------------------------------: | :-----------------------------: | :----------------------------------------------------------: |
| POIFS | OLE2 Filesystem | *poi* |
| HPSF | OLE2 Property Sets | *poi* |
| HSSF | Excel XLS | *poi* |
| HSLF | PowerPoint PPT | *poi-scratchpad* |
| HWPF | Word DOC | *poi-scratchpad* |
| HDGF | Visio VSD | *poi-scratchpad* |
| HPBF | Publisher PUB | *poi-scratchpad* |
| HSMF | Outlook MSG | *poi-scratchpad* |
| DDF | Escher common drawings | *poi* |
| HWMF | WMF drawings | *poi-scratchpad* |
| OpenXML4J | OOXML | *poi-ooxml* plus either *poi-ooxml-schemas* or *ooxml-schemas* and *ooxml-security* |
| XSSF | Excel XLSX | *poi-ooxml* |
| XSLF | PowerPoint PPTX | *poi-ooxml* |
| XWPF | Word DOCX | *poi-ooxml* |
| XDGF | Visio VSDX | *poi-ooxml* |
| Common SL | PowerPoint PPT 和 PPTX 共用组件 | *poi-scratchpad* and *poi-ooxml* |
| Common SS | Excel XLS 和 XLSX 共用组件 | *poi-ooxml* |
# 怎么使用POI
## 工程环境
JDK：1.8.0_201
maven：3.6.1
IDE：Spring Tool Suite 4.3.2.RELEASE
POI：4.1.2
easyexcel：2.1.6
mysql：5.7.28
## 创建项目
项目类型Maven Project，打包方式 jar。
## 引入依赖
```xml
## 写excel--入门案例
### 需求
生成一个 excel 文件，并给第一个单元格赋值为"测试"，并设置列宽 26，行高 20.25，内容居中，下框线，单元格橙色填充。
### 编写测试方法
`CellUtil`是 POI 自带的工具类，这里简化了三句代码（创建单元格，设置样式，赋值）。注意，当写入 xlsx 的大文件时，可以考虑使用`SXSSFWorkbook`来避免 OOM。
```java
@Test
public void test01() throws FileNotFoundException, IOException {
// 处理XSSF
String path = "extend\\file\\poi_test_01.xlsx";
// 处理HSSF
// String path = "extend\\file\\poi_test_01.xls";
// 创建工作簿
boolean flag = path.endsWith(".xlsx");
Workbook wb = WorkbookFactory.create(flag ? true : false);
// Workbook wb = new SXSSFWorkbook(100);//内存仅保留100行数据，可避免OOM
// 创建工作表
Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("MySheet001"));
// 设置列宽
sheet.setColumnWidth(0, 26 * 256);
// 创建行(索引从0开始)
Row row = sheet.createRow(0);
// 设置行高
row.setHeightInPoints(20.25f);
// 创建单元格样式对象
CellStyle style = wb.createCellStyle();
// 设置样式
style.setAlignment(HorizontalAlignment.CENTER); // 横向居中
style.setVerticalAlignment(VerticalAlignment.CENTER);// 纵向居中
style.setBorderBottom(BorderStyle.THIN);
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 创建单元格、设置样式和内容
CellUtil.createCell(row, 0, "测试", style);
// 保存到本地目录
OutputStream out = new FileOutputStream(new File(path));
wb.write(out);
// 释放资源
out.close();
wb.close();
}
```
### 测试
运行以上方法，指定路径下生成了 excel 文件，并填充了第一个单元格：
## 读excel--批量导入excel数据到数据库
### 需求
将 excel 中的用户数据导入到数据库（sql 已提供，在当前项目的 extend/sql 下），数据格式如下：
该文件总计1000条数据，xls 大小 128 KB，xlsx 大小 40 KB，两种类型文件内容一致。
### 编写测试方法
一般 excel 的内容格式是提前约定好的，我们知道用户数据哪一列是用户名，哪一列是电话号码，所以，在获取单元格数据后可以准确地转换，但这种方式需要针对不同的对象分别定义一个转换方法。
```java
@Test
public void test02() throws SQLException, IOException {
// 处理XSSF
String path = "extend\\file\\user_data.xlsx";
// 处理HSSF
//String path = "extend\\file\\user_data.xls";
// 定义集合，用于存放excel中的用户数据
List通过行数据构造用户对象
*/ private UserDTO constructUserByRow(Row row) { UserDTO userDTO = new UserDTO(); Cell cell = null; // 用户名 cell = row.getCell(1); userDTO.setName(cell.getStringCellValue()); // 性别 cell = row.getCell(2); userDTO.setGenderStr(cell.getStringCellValue()); // 年龄 cell = row.getCell(3); userDTO.setAge(((Double)cell.getNumericCellValue()).intValue()); // 电话 cell = row.getCell(4); userDTO.setPhone(cell.getStringCellValue()); return userDTO; } ``` ### 测试 运行以上方法，可以在数据库看到导入的数据：
## 写excel--批量导出数据库数据到excel
### 需求
将数据库的用户数据导出到excel中。这个例子使用模板进行导出，模板如下（如果是 xlsx 的大文件，为了能够使用`SXSSFWorkbook`最好不要用模板）。
### 编写测试方法
写入的时候使用样式还是比较繁琐，实际开发能不使用尽量不要用，或者也可以单独封装成一个方法。注意，构造`Workbook`时不要使用`WorkbookFactory.create(file)`方式，否则，模板也会被修改。
```java
@Test
public void test02() throws SQLException, IOException {
// 处理XSSF
String templatePath = "extend\\file\\user_data_template.xlsx";
String outpath = "extend\\file\\user_data.xlsx";
// 处理HSSF
// String templatePath = "extend\\file\\user_data_template.xls";
// String path = "extend\\file\\user_data.xls";
InputStream in = new FileInputStream(templatePath);
// 创建工作簿，注意，这里如果传入File对象，模板也会被改写
Workbook wb = WorkbookFactory.create(in);
// 读取工作表
Sheet sheet = wb.getSheetAt(0);
// 定义复用变量
int rowIndex = 0; // 行的索引
int cellIndex = 1; // 单元格的索引
Row nRow = null;
Cell nCell = null;
// 读取大标题行
nRow = sheet.getRow(rowIndex++); // 使用后 +1
// 读取大标题的单元格
nCell = nRow.getCell(cellIndex);
// 设置大标题的内容
nCell.setCellValue("2020年2月用户表");
// 跳过第二行(模板的小标题)
rowIndex++;
// 读取第三行,获取它的样式
nRow = sheet.getRow(rowIndex);
// 读取行高
float lineHeight = nRow.getHeightInPoints();
// 获取第三行的4个单元格中的样式
CellStyle cs1 = nRow.getCell(cellIndex++).getCellStyle();
CellStyle cs2 = nRow.getCell(cellIndex++).getCellStyle();
CellStyle cs3 = nRow.getCell(cellIndex++).getCellStyle();
CellStyle cs4 = nRow.getCell(cellIndex++).getCellStyle();
// 查询用户列表
List
## 读xls--使用SAX方式
### 需求
使用 SAX 的方式将 xls 中的用户数据导入到数据库，数据与以上例子一样。
### 编写测试方法
相比前面的例子，使用 SAX 方式内存占用小，效率高，但是 POI 提供的这套 API 用起来非常繁琐，使用时不得不必须去了解 xls 文件的结构。我这里只是简单展示，监听器部分的代码不太严谨，实际项目还是用 easyexcel 来操作吧。
```java
@Test
public void test02() throws Exception {
// 创建POIFSFileSystem
String filename = "extend\\file\\user_data.xls";
POIFSFileSystem poifs = new POIFSFileSystem(new File(filename));
// 创建HSSFRequest，并添加自定义监听器
HSSFRequest req = new HSSFRequest();
EventExample listener = new EventExample();
req.addListenerForAllRecords(listener);
// 解析和触发事件
HSSFEventFactory factory = new HSSFEventFactory();
factory.processWorkbookEvents(req, poifs);
// 保存用户到数据库
new UserService().save(listener.getList());
poifs.close();
}
private static class EventExample implements HSSFListener {
private SSTRecord sstrec;
private int lastCellRow = -1;
private int lastCellColumn = -1;
private List
## 读xlsx--使用SAX方式
### 需求
使用 SAX 的方式将 xlsx 中的用户数据导入到数据库，数据与以上例子一样。
### 编写测试方法
POI 针对 xlsx 的 SAX API 也是非常繁琐，属于非常低级的封装，这里竟然需要使用 JDK 原生的 SAX 解析来处理事件，定义事件处理器时，我必须去了解 xml 的节点结构。和上面例子一样，这里也只是简单地演示这套 API 的使用，具体代码不太严谨，当然，实际开发我们不会采用这种方式，建议还是使用 easyexcel 吧。
```java
@Test
public void test01() throws Exception {
String filename = "extend\\file\\user_data.xlsx";
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
// 获取sharedStrings.xml的内容，这里存放着excel中的字符
SharedStringsTable sst = r.getSharedStringsTable();
// 接下来就是采用SAX方式解析xml的过程
// 构造解析器，这里会设置自定义的处理器
XMLReader parser = XMLHelper.newXMLReader();
SheetHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
// 解析指定的sheet
InputStream sheet2 = r.getSheet("rId1");
parser.parse(new InputSource(sheet2));
// 保存用户到数据库
new UserService().save(handler.getList());
// handler.getList().forEach(System.err::println);
sheet2.close();
}
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String cellContents;
private boolean cellContentsIsString;
private int cellColumn = -1;
private int cellRow = -1;
List
# 使用easyexcel读写excel
通过以上例子，我们会发现，POI SAX 方式的 API 确实非常繁琐，使用时我必须熟悉地掌握 OLE2 或 OOXML 的规范，才能够使用。这是比较低层级的封装。相比之下，ss.usermodel 的 API 要好用很多，但是这套 API 底层解析 方式有点类似 DOM，效率较低，且内存占用较大。
前面已经讲过，easyexcel 对 POI 进行了高级封装，极大地方便了我们读写 excel，而且只会采用 SAX 这种更快的方式来读取，下面补充下如何使用 easyexcel 读写 excel。
## 创建实体
使用 easyexcel 读写 excel 时，我们不需要自己写 row => entity 或者 entity => row 的方法，只要按照以下注解好就行。被`@ExcelProperty`注解的属性对应 row 中的具体内容，而被`@ExcelIgnore`注解表示不需要与 row 进行转换。
```java
@ContentRowHeight(16)
public class UserDTO implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelIgnore
private String id;
/**
* 用户名
*/ @ExcelProperty(value = { "用户名" }, index = 1) private String name; /** *性别
*/ @ExcelProperty(value = { "性别" }, index = 2) private String genderStr; /** *年龄
*/ @ExcelProperty(value = { "年龄" }, index = 3) private Integer age; /** *电话号码
*/ @ExcelProperty(value = { "手机号" }, index = 4) @ColumnWidth(14) private String phone; @ExcelIgnore private Integer gender = 0; // 以下省略setter/getter方法 } ``` ## 批量导入excel 数据到数据库 easyexcel 封装或重写了 POI SAX 部分的 API，所以也是需要设置回调的监听器，以下方式会采用默认的监听器，并返回封装好的对象。 ```java @Test public void test02() throws SQLException, IOException { // XSSF String path = "D:\\growUp\\git_repository\\09-poi-demo\\extend\\file\\user_data.xlsx"; // HSSF // String path = "D:\\growUp\\git_repository\\09-poi-demo\\extend\\file\\user_data.xls"; // 读取excel List
