实际开发中经常会设计到excel的处理,如导出Excel,导入Excel到数据库中,操作Excel目前有两个框架,一个是apache 的poi, 另一个是 Java Excel(jxl)。当然这里还有一个是阿里巴巴对poi进一步封装的开源的框架EasyExcel。
Apache POI 简介是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office(Excel、Word、PowerPoint、Visio等)格式档案读和写的功能。
Java Excel是一开放源码项目,通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。jxl 特点是小巧 易用。
EasyExcel 是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。
Apache poi、jxl 框架都存在一个严重的问题就是非常的耗内存。EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。github地址:https://github.com/alibaba/easyexcel
学习目标:
- POI
- EasyExcel
1. POI简介
1.1 POI介绍
Apache POI 简介是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office(Excel、WORD、PowerPoint、Visio等)格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写。 学习思想: 其实POI这个类库,不难。我们不要上来就死记硬背代码。我们只要掌握了使用的思想就会变得非常简单。下面会讲到这种思想。官网以及参考的文档地址:官方主页: http://poi.apache.org/index.html API文档: http://poi.apache.org/apidocs/index.html ## 1.2 POI 常用的类和接口 Apache POI包含类和方法,来将MS Office所有OLE 2文档复合。此API组件的列表如下。
- HSSF提供读写Microsoft Excel XLS格式档案的功能。
- XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
- HWPF提供读写Microsoft Word DOC格式档案的功能。
- HSLF提供读写Microsoft PowerPoint格式档案的功能。
- HDGF提供读Microsoft Visio格式档案的功能。
- ……
1.3 POI常用类和方法
上面说了我们只讲操作如何操作Excel的,偏向于实际业务开发的。使用POI操作Excel的话无非两种情况一种是操作2003+ 版本的Excel,使用的接口对象是(HSSF )开头的对象,另一种是操作2007+版本的Excel,接口对象是(XSSF )开头。 Excel中的工作簿、工作表、行、单元格中的关系: 一个Excel文件对应于一个workbook(HSSFWorkbook,XSSFWorkbook), 一个workbook可以有多个sheet(HSSFSheet,XSSFSheet)组成, 一个sheet是由多个row(HSSFRow,XSSFRow)组成, 一个row是由多个cell(HSSFCell,XSSFCell)组成
- 2003版本常用类和方法
- HSSFWorkbook:工作簿
- 工作簿,代表一个excel的整个文档
- HSSFWorkbook(); // 创建一个新的工作簿
- HSSFWorkbook(InputStream inputStream); // 创建一个关联输入流的工作簿,可以将一个excel文件封装成工作簿
- HSSFSheet createSheet(String sheetname); 创建一个新的Sheet
- HSSFSheet getSheet(String sheetName); 通过名称获取Sheet
- HSSFSheet getSheetAt(int index); // 通过索引获取Sheet,索引从0开始
- HSSFCellStyle createCellStyle(); 创建单元格样式
- int getNumberOfSheets(); 获取sheet的个数
- setActiveSheet(int index); 设置默认选中的工作表
- write();
- write(File newFile);
- write(OutputStream stream);
- HSSFSheet:工作表
- HSSFRow createRow(int rownum); 创建新行,需要指定行号,行号从0开始
- HSSFRow getRow(int index); 根据索引获取指定的行
- int addMergedRegion(CellRangeAddress region); 合并单元格
- CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol); 单元格范围, 用于合并单元格,需要指定要合并的首行、最后一行、首列、最后一列。
- autoSizeColumn(int column); 自动调整列的宽度来适应内容
- getLastRowNum(); 获取最后的行的索引,没有行或者只有一行的时候返回0
- setColumnWidth(int columnIndex, int width); 设置某一列的宽度,width=字符个数 256,例如20个字符的宽度就是20 256
- HSSFRow :行
- HSSFCell createCell(int column); 创建新的单元格
- HSSFCell setCell(shot index);
- HSSFCell getCell(shot index);
- setRowStyle(HSSFCellStyle style); 设置行样式
- short getLastCellNum(); 获取最后的单元格号,如果单元格有第一个开始算,lastCellNum就是列的个数
- setHeightInPoints(float height); 设置行的高度
- HSSFCell:单元格
- setCellValue(String value); 设置单元格的值
- setCellType(); 设置单元格类型,如 字符串、数字、布尔等
- setCellStyle(); 设置单元格样式
- String getStringCellValue(); 获取单元格中的字符串值
- setCellStyle(HSSFCellStyle style); 设置单元格样式,例如字体、加粗、格式化
- setCellFormula(String formula); 设置计算公式,计算的结果作为单元格的值,也提供了异常常用的函数,如求和”sum(A1,C1)”、日期函数、字符串相关函数、CountIf和SumIf函数、随机数函数等
- 2007版本常用类和方法
- XSSFWorkbook:工作簿
- XSSF Workbook(); // 创建一个新的工作簿
- XSSF Workbook(InputStream inputStream); // 创建一个关联输入流的工作簿,可以将一个excel文件封装成工作簿
- XSSF Sheet createSheet(String sheetname); 创建一个新的Sheet
- XSSF Sheet getSheet(String sheetName); 通过名称获取Sheet
- XSSF Sheet getSheetAt(int index); // 通过索引获取Sheet,索引从0开始
- XSSF CellStyle createCellStyle(); 创建单元格样式
- int getNumberOfSheets(); 获取sheet的个数
- setActiveSheet(int index); 设置默认选中的工作表
- write();
- write(File newFile);
- write(OutputStream stream);
- XSSFSheet:工作表
- XSSF Row createRow(int rownum); 创建新行,需要指定行号,行号从0开始
- XSSF Row getRow(int index); 根据索引获取指定的行
- int addMergedRegion(CellRangeAddress region); 合并单元格
- CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol); 单元格范围, 用于合并单元格,需要指定要合并的首行、最后一行、首列、最后一列。
- autoSizeColumn(int column); 自动调整列的宽度来适应内容
- getLastRowNum(); 获取最后的行的索引,没有行或者只有一行的时候返回0
- setColumnWidth(int columnIndex, int width); 设置某一列的宽度,width=字符个数 256,例如20个字符的宽度就是20 256
- XSSFRow :行
- XSSF Cell createCell(int column); 创建新的单元格
- XSSF Cell setCell(shot index);
- XSSF Cell getCell(shot index);
- setRowStyle(XSSF CellStyle style); 设置行样式
- short getLastCellNum(); 获取最后的单元格号,如果单元格有第一个开始算,lastCellNum就是列的个数
- setHeightInPoints(float height); 设置行的高度
- XSSFCell:单元格
- setCellValue(String value); 设置单元格的值
- setCellType(); 设置单元格类型,如 字符串、数字、布尔等
- setCellStyle(); 设置单元格样式
- String getStringCellValue(); 获取单元格中的字符串值
- setCellStyle(XSSF CellStyle style); 设置单元格样式,例如字体、加粗、格式化
- setCellFormula(String formula); 设置计算公式,计算的结果作为单元格的值,也提供了异常常用的函数,如求和”sum(A1,C1)”、日期函数、字符串相关函数、CountIf和SumIf函数、XSSF
1.4 POI中Excel组成和步骤
组成
Excel的文件的组成形式,一个Excel文件对应于一个workbook(XSSFWorkbook - 工作薄),一个workbook可以有多个sheet(XSSFSheet - 工作表)组成,一个sheet是由多个row(Row - 工作行)组成,一个row是由多个cell(Cell - 工作单元格)组成。操作步骤
基于以上几条,如果想对excel文件进行读写的话就要需要执行下面这几个步骤- 用Workbook打开或者创建一个Excel文件的对象
- 用上一步的Excel对象创建或者获取到一个Sheet对象
- 用Sheet对象创建或获取一个Row对象
- 用Row对象创建或获取一个Cell对象
- 对Cell对象读写。
1.5 2003版本和2007版本
操作Excel文件区分版本:- 2003+版本(包含2003)文件的扩展名为 .xls 需要用 HSSFWorkbook 类操作
- 2007+ 版本(包含2007)文件的扩展名为 .xlsx 需要用 XSSFWorkbook 类操作
- 需要注意:2003 版本和 2007 版本存在兼容性的问题!2003最多只有 65536 行!2007最多是1048576
2. POI 操作案例 - 读写
上面大概讲解了一下POI的组成,操作Excel的步骤,常用的接口和实现类以及不同版本操作Excel的实现类是不同的,下面我就使用POI进行简单的读写。 在实际上手操作之前,必须要先把所需的环境搭建起来,下面我们来简单的搭建一个SpringBoot项目,进行测试POI对Excel读和写两大功能。项目结构说明:
创建了一个空项目,在空项目里面创建了两个SpringBoot项目: 如果你刚从码云上拉取下来的话,一定要配置一下数据库,其他的不用动,为了方便大家使用,项目技术之间耦合度非常低。😊- boot-easyexcel :这里面存放的是EasyExcel测试demo
- boot-poi : 这里面存放的是POI测试demo
- 注意:从版本4.0.1开始,POI需要Java 8或更高版本。
<!-- xls(2003)版本 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- xlsx(2007)版本及以上 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
- Hutool工具类,里面封装了很多常用的工具类,我们后面会讲到怎样直接使用Hutool里面的工具类进行操作Excel,我们要用到的工具类操作Excel,其实是Hutool对POI的封装。
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.10</version>
</dependency>
2.1 写Excel
其实我们都知道,Java中都是万物皆对象的。那操作POI道理也是一样的。我们只要知道了操作POI的对象,那使用起来都不会有什么难度的了。 下面打开Excel,我们根据Excel,来找找灵感:- 工作薄(Workbook)
- 工作表(Sheet)
- 行(行是由单元格组成)(Row)
- 单元格(Cell)
2.1.1 03版本的POI操作写Excel - (案例) Demo
运行结果:
@SpringBootTest
class {
//定义输出路径,路径改为你们要输出的路径即可
final String PATH = "D:\\blog\\POI\\poi-demo\\boot-poi\\";
@Test
void excelWrite03Test() throws Exception {
// 1. 创建工作薄
Workbook workbook = new HSSFWorkbook();
// 2. 创建工作表
Sheet sheet = workbook.createSheet("老陈写Excel测试");
// 3. 创建工作行
Row row1 = sheet.createRow(0); //参数0 就代表Excel中的第一行
// 4. 创建工作单元格
Cell cell1 = row1.createCell(0);//参数0 就代表Excel中的第一个单元格
//填充Excel中的第一行的第一个单元格(0,0)
cell1.setCellValue("姓名:老陈(chen)");
//填充Excel中的第一行的第二个单元格(0,1)
Cell cell2 = row1.createCell(1);
cell2.setCellValue("年龄:12岁");
//创建第二行,就是下面这样,以此类推(2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("姓名:宋祖儿(song)");
Cell cell22 = row2.createCell(1);
cell22.setCellValue("年龄:13岁");
//上面已经把所需要的数据已经构建成功,这时候需要生成一张Excel数据表。其实生成表的话本质上是IO操作,IO操作就需要流,所以说我们要构建一个流。输出到那个位置上
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "老陈测试03版本的Excel.xls");
((HSSFWorkbook) workbook).write(fileOutputStream);
//关闭流
System.out.println("-----------老陈通过POI写入Excel成功");
}
}
2.1.1 07版本的POI操作E写xcel - (案例) Demo
03版本的写出来之后,07版本的就很简单了。点进源码可以发现 Workbook 是一个接口,我们都只道Java是面向接口编程的,对象变了接口不用变,所以说们只需要把03版本的接口实现换为07版本的接口实现即可。(HSSFWorkbook >>>> 换为>>>> XSSFWorkbook)和把 xxx.xls 结尾的后缀改为 xxx.xlsx
//定义输出路径,路径改为你们要输出的路径即可
final String PATH = "D:\\blog\\POI\\poi-demo\\boot-poi\\";
@Test
void excelWrite07Test() throws Exception {
// 1. 创建工作薄
Workbook workbook = new XSSFWorkbook();
// 2. 创建工作表
Sheet sheet = workbook.createSheet("老陈写Excel测试");
// 3. 创建工作行
Row row1 = sheet.createRow(0); //参数0 就代表Excel中的第一行
// 4. 创建工作单元格
Cell cell1 = row1.createCell(0);//参数0 就代表Excel中的第一个单元格
//填充Excel中的第一行的第一个单元格(0,0)
cell1.setCellValue("姓名:老陈(chen)");
//填充Excel中的第一行的第二个单元格(0,1)
Cell cell2 = row1.createCell(1);
cell2.setCellValue("年龄:12岁");
//创建第二行,就是下面这样,以此类推(2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("姓名:宋祖儿(song)");
Cell cell22 = row2.createCell(1);
cell22.setCellValue("年龄:13岁");
//上面已经把所需要的数据已经构建成功,这时候需要生成一张Excel数据表。其实生成表的话本质上是IO操作,IO操作就需要流,所以说我们要构建一个流。输出到那个位置上
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "老陈测试07版本的Excel.xlsx");
workbook.write(fileOutputStream);
//关闭流
System.out.println("-----------老陈通过POI写入Excel 07版本成功");
}
2.1.3 探究Workbook对象
Workbook对象是POI提供的一个接口,不同版本的Excel提供了不同版本的实现类。比如07版本的是 XSSFWorkbook 03版本的是 HSSFWorkbook 。- HSSFWorkbook
- SXSSFWorkbook
- SXSSFWorkbookWithCustomZipEntrySource
- XSSFWorkbook
2.2 读Excel
读Excel也就是我们实际开发中的导入数据,简单点说就是Java程序把Excel中的数据读取到内存中。2.2.1 03版本的POI操作读Excel - (案例) Demo
//定义输出路径
final String PATH = "D:\\TyporaBlog\\POI\\poi-demo\\boot-poi\\";
@Test
void excelReader03Test() throws Exception {
//1. 获取文件的流
FileInputStream fileInputStream = new FileInputStream(PATH + "老陈测试03版本的Excel.xls");
//2. 创建工作薄,把获取的流传入
Workbook workbook = new HSSFWorkbook(fileInputStream);
//3. 创建表
Sheet sheet = workbook.getSheetAt(0);
//4. 创建行
Row row1 = sheet.getRow(0);
//5. 创建单元格
Cell cell1 = row1.getCell(0);
//6. 读取Excel内容
System.out.println(cell1.getStringCellValue());
//7. 关闭流
fileInputStream.close();
}
2.2.2 07版本的POI操作读Excel - (案例) Demo
//定义输出路径
final String PATH = "D:\\TyporaBlog\\POI\\poi-demo\\boot-poi\\";
@Test
void excelReader07Test() throws Exception {
//1. 获取文件的流
FileInputStream fileInputStream = new FileInputStream(PATH + "老陈测试07版本的Excel.xlsx");
//2. 创建工作薄,把获取的流传入
Workbook workbook = new XSSFWorkbook(fileInputStream);
//3. 创建表
Sheet sheet = workbook.getSheetAt(0);
//4. 创建行
Row row1 = sheet.getRow(0);
//5. 创建单元格
Cell cell1 = row1.getCell(0);
//6. 读取Excel内容
System.out.println(cell1.getStringCellValue());
//7. 关闭流
fileInputStream.close();
}
2.2.3 读取Excel时不同的数据类型怎样处理嘞🤔
用过Excel的童鞋们都应该知道Excel中是可以给列设置数据类型的,那不同的类型数据,我们应该怎样获取哪 ??这了是POI读取Excel的一个难点,把这个掌握了,那在工作过程中,不管遇到什么困难都不足为惧了,跟着老陈整起吧。 注意,在读取单元格的值时需要先判断数据类型,不然获取数据会报数据类型错误。
@Test
void excelCellTypeTest() throws Exception {
// 获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "POI-read - 不同类型读取03版本.xls");
// 第一步是先创建工作薄,把文件的流传入
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 第二步获取工作表
Sheet sheet = workbook.getSheetAt(0);
// Excel中一般第一行都是标题,所以我们先读取标题这行
Row row1Title = sheet.getRow(0);
//获取Excel表的标题
if (null!=row1Title){
//如果标题行不为空的话
int cellCount = row1Title.getPhysicalNumberOfCells();
for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
Cell cell = row1Title.getCell(cellNumber);
if (null!= cell){
CellType cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " || ");
}
}
System.out.println();
}
//获取表中的内容
int rowCount = sheet.getPhysicalNumberOfRows();
// 这里的下标要从1开始,排除标题行
for (int rowNumber = 1; rowNumber < rowCount; rowNumber++) {
Row rowData = sheet.getRow(rowNumber);
if (null!=rowData){
//读取列
int cellCount = row1Title.getPhysicalNumberOfCells();
for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
Cell cell = rowData.getCell(cellNumber);
if (null!=cell){
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType){
// 字符串
case STRING:
cellValue = cell.getStringCellValue();
break;
// 布尔
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
// 数字(日期、普通数字)
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)){ // 日期
System.out.print("【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else {
// 不是日期格式,防止数字过长!
System.out.print("【转换为字符串输出】");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
}
break;
// 空
case BLANK:
System.out.print("【BLANK】");
break;
case ERROR:
System.out.print("【数据类型错误】");
break;
}
System.out.println(cellValue);
}
}
}
}
fileInputStream.close();
}
3. 工具类 - Hutool
一般情况下我们在企业里面开发,或者自己编写代码的时候,为了方便的使用POI操作Excel或使代码的复用率更高我们一般选择封装一个工具类,这样我们就可以直接调用工具类即可。这里的话给大家演示一个工具(Hutool),这里面就有对POI的封装,我们直接使用即可。这里不提供自己封装的工具类,应为每个企业封装的工具类都有所不同。不希望大家记着一个工具类,这样只会是你的思想禁锢。 我建议大家学学这个Hutool工具类的集合,真的很好用,,反正我是特别喜欢。免去了自己百度封装的代码。推荐大家学习3.1 使用前必看
Hutool-poi是针对Apache POI的封装,因此需要 用户自行引入POI库,Hutool默认不引入。到目前为止,Hutool-poi支持:- Excel文件(xls, xlsx)的读取(ExcelReader)
- Excel文件(xls,xlsx)的写出(ExcelWriter)
- 推荐引入poi-ooxml,这个包会自动关联引入poi包,且可以很好的支持Office2007+的文档格式
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
- 说明 hutool-4.x的poi-ooxml 版本需高于 3.17(别问我3.8版本为啥不行,因为3.17 > 3.8 ) hutool-5.x的poi-ooxml 版本需高于 4.1.2xercesImpl版本高于2.12.0
3.2 Hutool 提供的类
引入后即可使用Hutool的方法操作Office文件了,Hutool提供的类有:- ExcelUtil Excel工具类,读取的快捷方法都被封装于此
- ExcelReader Excel读取器,Excel读取的封装,可以直接构造后使用。
- ExcelWriter Excel生成并写出器,Excel写出的封装(写出到流或者文件),可以直接构造后使用。
3.3 Hutool 读数据
使用Hutool进行对Excel文件读取的时候,只需要一行代码,是的,你没有听错只需要一行代码。下面我给大家演示一下- Excel 文件
- Java程序
@Test
void hutoolReaderTest(){
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("D:\\TyporaBlog\\POI\\poi-demo\\boot-poi\\老陈测试03版本的Excel.xls"));
List<Map<String, Object>> mapList = reader.readAll();
System.out.println(mapList.toString());
}
- 效果图
3.4 Hutool 写数据
Hutool写数据的原理 Hutool将Excel写出封装为ExcelWriter,原理为包装了Workbook对象,每次调用merge(合并单元格)或者write(写出数据)方法后只是将数据写入到Workbook,并不写出文件,只有调用flush或者close方法后才会真正写出文件。 由于机制原因,在写出结束后需要关闭ExcelWriter对象,调用close方法即可关闭,此时才会释放Workbook对象资源,否则带有数据的Workbook一直会常驻内存。@Test
void hutoolWriteTest(){
//数据库查询
List<User> userList = userServicer.getUserList();
ExcelWriter writer = ExcelUtil.getWriter("D:\\TyporaBlog\\POI\\poi-demo\\boot-poi\\HutoolWriteTest.xlsx");
//自定义标题别名
writer.addHeaderAlias("age", "用户账户");
writer.addHeaderAlias("score", "昵称");
writer.addHeaderAlias("isPass", "性别");
writer.addHeaderAlias("examDate", "简介");
writer.addHeaderAlias("examDate", "头像url");
writer.addHeaderAlias("examDate", "创建时间");
writer.addHeaderAlias("examDate", "修改时间");
writer.addHeaderAlias("examDate", "登录次数");
// 一次性写出内容,使用默认样式,强制输出标题
writer.write(userList, true);
// 关闭writer,释放内存
writer.close();
}
4. EasyExcel详解
4.1 为什么要使用EasyExcel
Java中操作Excel的框架poi,jxt,但是他们都存在一个问题:非常的消耗内存。这时候阿里的大佬在POI的基础上进一步重写,封装得到了easyexcel,他最大的好处就是解决了过于消耗内存问题。也让使用者使用更加便利。4.2 EasyExcel基本使用
4.2.1 写
@SpringBootTest
public class EasyExcelWriteTest {
@Autowired
private UserServicer userServicer;
/**
* 最简单的写
* 1. 创建excel对应的实体对象
* 2. 直接写即可
*/
@Test
public void simpleWrite() {
String fileName = "D:\\TyporaBlog\\POI\\poi-demo\\boot-easyexcel\\"+"EasyExcelWriteTest文件"+".xlsx";
// 这里 需要指定写用哪个class去读,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
List<User> userList = userServicer.getUserList();
EasyExcel.write(fileName, User.class).sheet("模板").doWrite(userList);
}
}
更多详细的内容请参考:
4.2.2 读
@SpringBootTest
public class EasyExcelReadTest {
/**
* 最简单的读
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
* 3. 直接读即可
*/
@Test
public void simpleRead() {
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
// 写法1:
String fileName = "D:\\TyporaBlog\\POI\\poi-demo\\boot-easyexcel\\"+"EasyExcelWriteTest文件"+".xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, User.class, new UserListener()).sheet().doRead();
}
}
public class UserListener extends AnalysisEventListener<User> {
@Override
public void invoke(User user, AnalysisContext analysisContext) {
//这里仅仅打印一下数据
System.out.println(user.toString());
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
- 官方说明也比较明确,使用简单fileName是路径+文件名,DemoData是Excel数据对应的实体类,DemoDataListener这看名字就是监听器,用来监听处理读取到的每一条数据
详细参考官网:
5. 实战练习
上面的内容讲解了一下POI在Java中怎样对Excel实现读和写的功能。下面我们要讲解一下POI在实际中是怎样开发的,这也是每个人以后进入工作要用到的一部分。其实在不管是写demo测试,还是实际开发,用到的都是上面所讲到到的知识,只是用法稍稍不同,无非实际开发中,是在页面展示。其实POI在实际开发中无非就两种情况,一中是数据的导入,另一种是数据的导出。 举个栗子: 导入:我们要做一个答题的一个业务,这时候我们需要很多题目,不可能说我们找一道题插入到数据库中,找一道题插入到数据库中。这样的办法是不可取的,这里我们一般把收集的题目放入到Excel中,批量导入即可。 导出:就是,有另外一个老师需要这些题目,这时候我们就批量导入到Excel中即可。5.1 读取Execl数据到数据库(导入)
说明:- User : 表中的数据我们只做导出,应为数据量比较小
- Products :我们做导入,因为这个表中的数据比较多,正好大数据量进行测试
5.1.1 POI 导入
@RequestMapping("/uploadFile")
@ResponseBody
public Object uploadFile(@RequestParam("fileName") MultipartFile file) {
Map<String,Object> map = new HashMap<String,Object>();
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
ExcelReader excelReader = ExcelUtil.getReader(inputStream);
List<Map<String, Object>> mapList = excelReader.readAll();
/**
* 这里暂时先不写详细。时间不太够了,后面会补上的
* mapList 中数据已经有了,最简单的方法直接遍历插入即可。但是实际开发中还是要判断数据是否对
*/
return map;
};
5.1.2 EasyExcel 导入
@RequestMapping("/uploadFile")
@ResponseBody
public Object uploadFile(@RequestParam("fileName") MultipartFile file) throws IOException {
Map<String,Object> map = new HashMap<String,Object>();
EasyExcel.read(file.getInputStream(), Products.class, new ProductsListener(productsService)).sheet().doRead();
return map;
};
- 监听器
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ProductsListener extends AnalysisEventListener<Products> {
private static final Logger LOGGER =
LoggerFactory.getLogger(ProductsListener.class);
/**
* 每隔100条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
List<Products> list = new ArrayList<Products>();
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private ProductsService productsService;
public ProductsListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
productsService = new ProductsServiceImpl();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param productsService
*/
public ProductsListener(ProductsService productsService) {
this.productsService = productsService;
}
/**
* 这个每一条数据解析都会来调用
* @param products
*/
@Override
public void invoke(Products products, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(products));
list.add(products);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
productsService.insert(list);
LOGGER.info("存储数据库成功!");
}
}
5.2 将数据库数据写入Execl中(导出)
导出是将所有单元格对象保存到内存中,当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时,随着表格的不断创建,内存中对象越来越多,直至内存溢出。Apache Poi提供SXSSFWork对象,专门用于处理大数据量Excel报表导出。5.2.1 导出
@Controller
@RequestMapping("/user")
public class UserController {
@Autowired
private UserServicer userService;
@RequestMapping("/exportExcel")
public void exportExcel(Model model, HttpServletResponse response) throws UnsupportedEncodingException {
//数据库查询
List<User> userList = userService.getUserList();
ExcelWriter writer = ExcelUtil.getWriter(true);
//自定义标题别名
writer.addHeaderAlias("age", "用户账户");
writer.addHeaderAlias("score", "昵称");
writer.addHeaderAlias("isPass", "性别");
writer.addHeaderAlias("examDate", "简介");
writer.addHeaderAlias("examDate", "头像url");
writer.addHeaderAlias("examDate", "创建时间");
writer.addHeaderAlias("examDate", "修改时间");
writer.addHeaderAlias("examDate", "登录次数");
writer.write(userList, true);
String name = URLEncoder.encode("用户数据", "utf-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename="+name+".xlsx");
// 一次性写出内容,使用默认样式,强制输出标题
ServletOutputStream out = null;
try {
out=response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
e.printStackTrace();
}finally {
// 关闭writer,释放内存
writer.close();
}
}
}
5.2.2 EasyExcel 导出
@Controller
@RequestMapping("/user")
public class UserController {
@Autowired
private UserServicer userService;
@RequestMapping("/exportExcel")
public void exportExcel(Model model, HttpServletResponse response) throws IOException {
List<User> userList = userService.getUserList();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 转换为UTF-8 ,不然输出乱码
String fileName = URLEncoder.encode("EasyExcel用户导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), User.class).sheet("模板").doWrite(userList);
}
}
5.3 大数据表处理
我之前处理百万数据的时候,网上的说法无非两种- 大文件读取使用SAX
- 大文件写入使用SXSSFWorkbook
5.4 总结
对于数据量不算太大的导入导出我可以使用原生的POI或者Hutool封装的工具类,其实理解了思想都非常简单。当遇到大数据的时候我们舍弃POI,使用阿里巴巴进行重写和封装的EasyExcel,效果会非常好。 到此我们的POI就基本讲解完毕了,但是还有很多地方不足,老陈也会努力修改打造一篇优质的教程。6. 大数据表处理
上面已经讲解了,POI基本操作Excel、EasyExcel操作Excel以及使用Hutool工具类进行对Excel操作。接着我们学习一下数据量特别大的时候,我们应该怎么办。 Excel可以分为早期的2003+ 版本(使用POI的HSSF对象操作)和2007+版本(使用POI的XSSF操作),两者对大数据的支持如下:- Excel 2003:在POI中使用HSSF对象时,excel 2003最多只允许存储65536条数据,一般用来处理较少的数据量。这时对于百万级别数据,Excel肯定容纳不了。
- Excel 2007:当POI升级到XSSF对象时,它可以直接支持excel2007以上版本,因为它采用ooxml格式。这时excel可以支持1048576条数据,单个sheet表就支持近百万条数据。但实际运行时还可能存在问题,原因是执行POI报表所产生的行对象,单元格对象,字体对象,他们都不会销毁,这就导致OOM的风险。
6.1 解决方案
- 用户模式:用户模式有许多封装好的方法操作简单,但创建太多的对象,非常耗内存(之前使用的方法)
- 事件模式:基于SAX方式解析XML,SAX全称Simple API for XML,它是一个接口,也是一个软件包。它是一种XML解析的替代方法,不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式,它逐行扫描文档,一边扫描,一边解析。
- SXSSF对象:是用来生成海量excel数据文件,主要原理是借助临时存储空间生成excel
6.2 大数据导出&导入
基于XSSFWork导出Excel报表,是通过将所有单元格对象保存到内存中,当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时,随着表格的不断创建,内存中对象越来越多,直至内存溢出。Apache Poi提供SXSSFWork对象,专门用于处理大数据量Excel报表导出。 在实例化SXSSFWork这个对象时,可以指定在内存中所产生的POI导出相关对象的数量(默认100),一旦内存中的对象的个数达到这个指定值时,就将内存中的这些对象的内容写入到磁盘中(XML的文件格式),就可以将这些对象从内存中销毁,以后只要达到这个值,就会以类似的处理方式处理,直至Excel导出完成。- 用户模式:加载并读取Excel时,是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel数据量较大时,由于不同的运行环境可能会造成内存不足甚至OOM异常。
- 事件模式:它逐行扫描文档,一边扫描一边解析。由于应用程序只是在读取数据时检查数据,因此不需要将数据存储在内存中,这对于大型文档的解析是个巨大优势。
- 大文件读取使用SAX
- 大文件写入使用SXSSFWorkbook