详解POI的使用方法(DOM和SAX的方式)及存在的不足
子月生 人气:0
# 简介
Apache POI是一套基于 **OOXML 标准**（Office Open XML）和 **OLE2 标准**来读写各种格式文件的 Java API，也就是说只要是遵循以上标准的文件，POI 都能够进行读写，而不仅仅只能操作我们熟知的办公程序文件。本文只会涉及到 excel 相关内容，其他文件的操作可以参考[poi官方网站]( http://poi.apache.org/components )。
这里先总结下 POI 的使用体验。POI 面向接口的设计非常巧妙，使用 `ss.usermodel` 包读写 xls 和 xlsx 时，可以使用同一套代码，即使这两种文件格式采用的是完全不同的标准。POI 提供了`SXSSFWorkbook`用于解决 xlsx 写大文件时容易出现的 OOM 问题。但是，还是存在以下不足（都只针对读场景）：
1. **使用 ss.usermodel 包解析 excel 效率较低、内存占用较大，且容易出现 OOM**。类似于 xml 中的 DOM，这种方式会在内存中构建整个文档的结构，在处理大文件时容易出现 OOM。然而，**大部分场景我们并不需要随机地去访问 excel 中的节点**。
2. **POI 提供的 SAX 解析可以解决第一个问题，但是 API 太过复杂**。为了解决第一个问题，POI 提供了基于事件驱动的 SAX 方式，这种方式内存占用小、效率高， 但是 API 太过繁琐，**开发者必须在熟知文档规范的前提下才能使用**，而且 xls 和 xlsx 使用的是完全不同的两套 API，实际项目中必须针对不同文件类型分别实现。这一点可以从本文的例子看出来。
针对以上问题，阿里的 easyexcel 对 POI 进行高级封装，提供了一套非常简便的 API，其中，读部分只封装了 SAX 部分 API，事实上，使用 easyexcel 读 excel 只会采用 SAX 方式，另外，easyexcel 重写了 POI 对 xlsx 的解析，能够原本一个3M的 excel 用 POI SAX 依然需要100M左右内存降低到几M，easyexcel 的内容本文也会涉及到。
## 什么是 OLE2 和 OOXML
OLE2 和 OOXML 本质上都是一种文件格式规范或标准，平时看到的 excel 中，有字体、公式、颜色、图片等等，看起来非常复杂，但是在文件结构上都遵循着固定的格式。
OLE2 文件一般包括 xls、doc、ppt 等，是二进制格式的文件。 相关内容可以参考：[ 复合文档Ole对象二进制储存格式 ]( 复合文档Ole对象二进制储存格式 )。
OOXML文件一般包括 xlsx、docx、pptx 等。该类文件以指定格式的 xml 为基础并以 zip 格式压缩，这里我利用解压工具解压本地的一个 xml 文件，可以看到以下文件结构，在本文例子中，我们会重点关注 sharedStrings.xml 和 sheet1.xml 的内容，因为使用 SAX API 时必须用到：
## 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
junit
junit
4.12
test
org.apache.poi
poi-ooxml
4.1.2
com.alibaba
easyexcel
2.1.6
com.zaxxer
HikariCP
2.6.1
mysql
mysql-connector-java
8.0.15
```
## 读excel--入门案例
### 需求
读取指定 excel 第一个单元格的内容。该指定文件第一个单元格内容为“测试”。
### 编写测试方法
建议采用`WorkbookFactory`来获取`Workbook`实例，而不是根据文件类型写死具体的实现类。另外，获取单元格对象时建议采用 `SheetUtil`获取，里面会对行对象进行判空操作。
```java
@Test
public void test01() throws IOException {
// 处理XSSF
String path = "extend\\file\\poi_test_01.xlsx";
// 处理HSSF
//String path = "extend\\file\\poi_test_01.xls";
// 创建工作簿，会根据excel命名选择不同的Workbook实现类
Workbook wb = WorkbookFactory.create(new File(path));
// 获取工作表
Sheet sheet = wb.getSheetAt(0);
// 获取行
Row row = sheet.getRow(0);
// 获取单元格
Cell cell = row.getCell(0);
// 也可以采用以下方式获取单元格
// Cell cell = SheetUtil.getCell(sheet, 0, 0);
// 获取单元格内容
String value = cell.getStringCellValue();
System.err.println("第一个单元格字符：" + value);
// 释放资源
wb.close();
}
```
### 测试
运行以上方法，控制台打印出第一个单元格的内容：
## 写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 list = new ArrayList<>();
InputStream in = new FileInputStream(path);
// 创建工作簿
Workbook wb = WorkbookFactory.create(in);
// 获取工作表
Sheet sheet = wb.getSheetAt(0);
// 获取所有行
Iterator iterator = sheet.iterator();
int rowNum = 0;
// 遍历行
while(iterator.hasNext()) {
Row row = iterator.next();
// 跳过标题行
if(rowNum == 0 || rowNum == 1) {
rowNum++;
continue;
}
// 将用户对象保存到集合中
list.add(constructUserByRow(row));
}
// 批量保存
new UserService().save(list);
// 释放资源
in.close();
wb.close();
}
/**
* 
## 写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 userList = new UserService().findAll().stream().map((x) -> new UserDTO(x)).collect(Collectors.toList());
// 遍历数据
for(UserDTO user : userList) {
// 创建数据行
nRow = sheet.createRow(rowIndex++);
// 设置数据行高
nRow.setHeightInPoints(lineHeight);
// 重置cellIndex,从第一列开始写数据
cellIndex = 1;
// 创建数据单元格，设置单元格内容和样式
// 用户名
nCell = nRow.createCell(cellIndex++);
nCell.setCellStyle(cs1);
nCell.setCellValue(user.getName());
// 性别
nCell = nRow.createCell(cellIndex++);
nCell.setCellStyle(cs2);
nCell.setCellValue(user.getGenderStr());
// 年龄
nCell = nRow.createCell(cellIndex++);
nCell.setCellStyle(cs3);
nCell.setCellValue(user.getAge());
// 手机号
nCell = nRow.createCell(cellIndex++);
nCell.setCellStyle(cs4);
nCell.setCellValue(user.getPhone());
}
// 保存到本地目录
OutputStream out = new FileOutputStream(new File(outpath));
wb.write(out);
// 释放资源
out.close();
wb.close();
}
```
### 测试
运行以上方法，在指定文件夹可以看到生成的文件：
## 读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 list = new ArrayList();
private UserDTO user;
@Override
public void processRecord(Record record) {
switch(record.getSid()) {
// 进入新的sheet
case BoundSheetRecord.sid:
lastCellRow = -1;
lastCellColumn = -1;
break;
// excel中的数值类型和字符存放在不同的位置
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord)record;
// 用户年龄
user.setAge(Double.valueOf(numrec.getValue()).intValue());
lastCellRow = numrec.getRow();
lastCellColumn = numrec.getColumn();
break;
// SSTRecords中存储着excel中使用的字符，重复的会合并为一个
case SSTRecord.sid:
sstrec = (SSTRecord)record;
break;
// 读取到单元格的字符
case LabelSSTRecord.sid:
LabelSSTRecord lrec = (LabelSSTRecord)record;
int thisRow = lrec.getRow();
// 用户数据从第三行开始
if(thisRow >= 2) {
// 进入新行时，原对象放入集合，并创建新对象
if(thisRow != lastCellRow) {
if(user != null) {
list.add(user);
}
user = new UserDTO();
}
// 根据列数为用户对象设置属性
switch(lrec.getColumn()) {
case 1:
// 用户名
user.setName(sstrec.getString(lrec.getSSTIndex()).getString());
break;
case 2:
// 用户性别
user.setGenderStr(sstrec.getString(lrec.getSSTIndex()).getString());
break;
case 4:
// 用户电话
user.setPhone(sstrec.getString(lrec.getSSTIndex()).getString());
break;
default:
break;
}
lastCellRow = thisRow;
lastCellColumn = lrec.getColumn();
}
break;
case EOFRecord.sid:
// 最后一行读取完后直接放入集合
if(lastCellRow != -1 && user != null && lastCellColumn == 4) {
list.add(user);
}
break;
default:
break;
}
}
public List getList() {
return 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 list = new ArrayList<>();
UserDTO user;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
// 读取到行
if("row".equals(name)) {
cellRow++;
if(cellRow >= 2) {
// 换行时重新创建用户实例
user = new UserDTO();
}
}
// 读取到列 c => cell
if("c".equals(name) && cellRow >= 2) {
// 设置当前读取到哪一列
char columnChar = attributes.getValue("r").charAt(0);
switch(columnChar) {
case 'B':
cellColumn = 1;
break;
case 'C':
cellColumn = 2;
break;
case 'D':
cellColumn = 3;
break;
case 'E':
cellColumn = 4;
break;
default:
cellColumn = -1;
break;
}
// 当前单元格中的值是否为字符，是的话对应的值被放在SharedStringsTable中
if("s".equals(attributes.getValue("t"))) {
cellContentsIsString = true;
}
}
// Clear contents cache
cellContents = "";
}
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
// 跳过标题
if(cellRow < 2) {
return;
}
// v节点是c的子节点，表示单元格的值
if(name.equals("v")) {
int idx;
if(cellContentsIsString) {
idx = Integer.parseInt(cellContents);
} else {
idx = Double.valueOf(cellContents).intValue();
}
switch(cellColumn) {
case 1:
user.setName(sst.getItemAt(idx).getString());
break;
case 2:
user.setGenderStr(sst.getItemAt(idx).getString());
break;
case 3:
// 年龄的值是数值类型，不在SharedStringsTable中
user.setAge(idx);
break;
case 4:
user.setPhone(sst.getItemAt(idx).getString());
break;
default:
break;
}
}
// 读取完一行，将用户对象放入集合中
if("row".equals(name) && user != null) {
list.add(user);
}
// 重置参数
if("c".equals(name)) {
cellColumn = -1;
cellContentsIsString = false;
}
}
@Override
public void characters(char[] ch, int start, int length) {
cellContents += new String(ch, start, length);
}
public List getList() {
return 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;
/**
* list = EasyExcel.read(path).head(UserDTO.class).sheet(0).headRowNumber(2).doReadSync();
// 保存
new UserService().save(list);
}
```
当然，我们也可以采用自定义的监听器，如下：
```java
@Test
public void test01() 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";
List list = new ArrayList();
// 定义回调监听器
ReadListener syncReadListener = new AnalysisEventListener() {
@Override
public void invoke(UserDTO data, AnalysisContext context) {
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// TODO Auto-generated method stub
}
};
// 读取excel
EasyExcel.read(path, UserDTO.class, syncReadListener).sheet(0).headRowNumber(2).doRead();
// 保存
new UserService().save(list);
}
```
## 批量导出数据库数据到excel
和读一样，这里也只用了一行代码就完成了对 excel 的操作。
```java
@Test
public void test01() 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";
// 获取用户数据
List list = new UserService().findAll().stream().map((x) -> new UserDTO(x)).collect(Collectors.toList());
// 写入excel
EasyExcel.write(path, UserDTO.class).sheet(0).relativeHeadRowIndex(1).doWrite(list);
}
```
# 参考资料
[Apache POI - the Java API for Microsoft Documents]( http://poi.apache.org)
> 相关源码请移步：[https://github.com/ZhangZiSheng001/poi-demo](https://github.com/ZhangZiSheng001/poi-demo)
>本文为原创文章，转载请附上原文出处链接： [https://www.cnblogs.com/ZhangZiSheng001/p/12329937.html](https://www.cnblogs.com/ZhangZiSheng001/p/12329937.html)
## 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加载全部内容