前言
Excel在数据处理中的重要性自不必说,我们经常会有需求,将数据库中的数据,做过一些处理后,导出一个Excel给运营人员分析。也有需求是将批量的商品数据等,批量录入到我们系统中。直观上可以理解为Excel是客户和系统批量数据交互的一个媒介。
1 开源组件之POI使用
1.1 简介
POI的官方网站:http://poi.apache.org/
1.2 使用
为保证格式差异,一般在后台提供Excel维护模板,运营人员下载模板维护数据,最后上次excel,后台代码解析处理数据,保证数据文件代码能正常处理。
1.2.1 坐标引入
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<!-- 解析Excel 2007及以上版本文件必须加上下面的 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
1.2.2 api介绍
不管是JXL,还是POI,它们在解析Excel文件上的思想流程都是差不多的,先读取工作簿 → 工作表 → 行 → 单元格。对于POI,可以表示为Workbook → Sheet → Row → Cell
HSSF – 提供读写Excel格式(03)xls文件
XSSF – 提供读写Excel OOXML格式(07)xlsx文件
- HSSF仅能保存65535行数据,XSSF无限制但速度比较慢,因此可以使用优化后的XSSF,即SXSSF(可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存)
- 会产生临时文件,需要清理临时文件(fileOutputStream.close();)(默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件,如果向自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)
1.2.2.1 样例Excel
下面代码演示都基于这一个Excel,文件名:测试01.xls 是2003版本的Excel,文件名:测试02.xlsx是2007版本的Excel,里面内容都相同如下所示:
1.2.2.2 读Excel
注意:下面只是测试代码,从代码通用性设计来说,一般读取文件每列的数据格式是固定的,真实业务场景,我们需要基于列名做对应数据类型转换,而不是所有数据当成字符串处理。读取Excel一般用于我们导入业务数据。下面写Excel不再赘述数据类型的问题。
1.2.2.2.1 读取03版本的Excel
@Test
public void POIExcel03Read() throws Exception {
String PATH = "E:\\2023-03\\test\\src\\main\\resources\\";
// 获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "测试01.xls");
// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
Workbook workbook = new HSSFWorkbook(inputStream);
// 2、得到表
Sheet sheet = workbook.getSheetAt(0);
// 3、得到行
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int i=firstRowNum;i<=lastRowNum;i++){
Row row = sheet.getRow(i);
// 4、得到列
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
for (int j = firstCellNum; j <lastCellNum; j++) {
Cell cell = row.getCell(j);
// 获取字符串类型 ,这里留意要保证单元格中数据格式和处理格式对应,否则会报错
String value = cell.getStringCellValue();
System.out.println("03版本的值:" + value);
}
}
inputStream.close();
}
输出结果为:
03版本的值:name
03版本的值:age
03版本的值:sex
03版本的值:score
03版本的值:张三
03版本的值:23
03版本的值:男
03版本的值:34.2
03版本的值:李四
03版本的值:34
03版本的值:男
03版本的值:66.7
1.2.2.2.1 读取07版本的Excel
@Test
public void POIExcel07Read() throws Exception {
String PATH = "E:\\test\\src\\main\\resources\\";
// 获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "测试02.xlsx");
// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
Workbook workbook = new XSSFWorkbook(inputStream);
// 2、得到表
Sheet sheet = workbook.getSheetAt(0);
// 3、得到行
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int i=firstRowNum;i<=lastRowNum;i++){
Row row = sheet.getRow(i);
// 4、得到列
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
for (int j = firstCellNum; j <lastCellNum; j++) {
Cell cell = row.getCell(j);
// 获取字符串类型 ,这里留意要保证单元格中数据格式和处理格式对应,否则会报错
String value = cell.getStringCellValue();
System.out.println("07版本的值:" + value);
}
}
inputStream.close();
}
输出结果为:
07版本的值:name
07版本的值:age
07版本的值:sex
07版本的值:score
07版本的值:张三
07版本的值:23
07版本的值:男
07版本的值:34.2
07版本的值:李四
07版本的值:34
07版本的值:男
07版本的值:66.7
对比03 和07 版本代码发现,其实差异性就是下面代码:
03版本
// 获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "测试01.xls");
// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
Workbook workbook = new HSSFWorkbook(inputStream);
07版本
// 获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "测试02.xlsx");
// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
Workbook workbook = new XSSFWorkbook(inputStream);
后面不再赘述两者差异,感兴趣自行复制代码比对验证
1.2.2.3 写Excel
1.2.2.3.1 写入03版本的Excel
@Test
public void POIExcel03write() throws Exception {
//创建测试数据,真实应该是从db或者ftp等数据源获取的
Book book1 = new Book(1L, "斗破苍穹", "天蚕土豆");
Book book2 = new Book(2L, "神墓", "辰东");
Book book3 = new Book(3L, "完美世界", "辰东");
List<Book> bookList= Arrays.asList(book1,book2,book3);
String PATH = "E:\\src\\main\\resources\\";
// 1、创建一个工作簿 03
Workbook workbook = new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("书籍统计表");
//维护标题list
List<String> titleList=new ArrayList<>();
//正常导出文件的每列顺序是固定的,方便用户习惯,当然可以自定义,这里只讲怎么导出,具体导出根据业务细节自己处理
for (int i = 0; i < bookList.size(); i++) {
// 3、创建一个行
Row row = sheet.createRow(i);
Book book = bookList.get(i);
//id
Cell cell = row.createCell(0, CellType.NUMERIC);
cell.setCellValue(book.id);
//书名
Cell nameCell=row.createCell(1,CellType.STRING);
nameCell.setCellValue(book.name);
//作者
Cell authorCell=row.createCell(2,CellType.STRING);
authorCell.setCellValue(book.author);
}
// 生成一张表(IO流) 03版本就是使用xls结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH+"书籍统计表.xls");
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
}
class Book {
private Long id;
private String name;
private String author;
}
代码运行结果,生成文件内容如下:
1.2.2.3.2 写入07版本的Excel
07 代码和03 类似,差别就是创建工作簿的时候选的类型不一样,这里不再赘述
// 1、创建一个工作簿 03
Workbook workbook = new XSSFWorkbook();
1.2.2.3.3 写文件优化
- HSSF仅能保存65535行数据,XSSF无限制但速度比较慢
- 因此可以使用优化后的XSSF,即SXSSF(可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存)
- 会产生临时文件,需要清理临时文件(fileOutputStream.close();)(默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件
- 如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)
@Test
public void POIExcel07BigDataS() throws Exception {
long begin = System.currentTimeMillis();
String PATH = "E:\\weixinData\\WeChat Files\\wxid_gv8xbkloz0wc22\\FileStorage\\File\\2023-03\\test\\src\\main\\resources\\";
// 1、创建一个工作簿 07
Workbook workbook = new SXSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("测试大数据量表");
// 3、写入数据
for (int rowNum = 0; rowNum < 105536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
// 生成一张表(IO流) 07版本就是使用xlsx结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试大数据量表.xlsx");
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
// 清除临时文件
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println("测试大数据量表 生成完毕,时间消耗:" + (double) (end - begin));
}
1.2.2.4 公式调用
公式处理在我们日常Excel使用中用的不多,这里只是作为扩展分享。
假设处理的文件如下,可以看到第六行是一个公式,统计的是 2,3,4行的分数,则可以代码运行计算值:
代码示例如下:
@Test
public void POIExcelTestFormula() throws Exception {
String PATH = "E:\\weixinData\\WeChat Files\\wxid_gv8xbkloz0wc22\\FileStorage\\File\\2023-03\\test\\src\\main\\resources\\";
// 获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "测试公式.xlsx");
// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
Workbook workbook = new XSSFWorkbook(inputStream);
// 2、得到表
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(5);
Cell cell = row.getCell(0);
//拿到计算公式 eval
XSSFFormulaEvaluator xssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//输出单元格的内容
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula); //SUM(A2,A3,A4)
//计算
CellValue evaluate = xssfFormulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
}
代码输出结果如下:
A2+A3+A4
291.11
1.3 优点
- api相对jxl 使用更方便
- 相对jxl 解决了一部分oom问题
- 功能支持相对完善,也是比较成熟的框架(很多成熟公司的既有框架选择),因为poi还有对ppt ,word等的支持
1.4 缺点
- 比较消耗内存
2 开源组件之EasyExcel使用
2.1 简介
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便
2.2 使用
2.2.1 坐标引入
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
2.2.2 api介绍
上面官网的测试demo讲的很详细,这里不再赘述。有几个使用过程注意的点如下:
- 监听器有多个,可以根据自己需求,选择不同的监听器
- 监听器不能交给spring管理,每次读取excel的时候需要自己new出来
- 自己new出来的对象不能交给spring管理,为此如果监听器里面用到了spring管理的bean对象,可以在构造的时候注入bean对象(比如传入线程池想异步处理数据,比如传入mapperbean 和数据库交互),也可以通过某些静态工具类获取(前提是应用启动的时候,静态类维护了bean容器)
- 编写监听器的时候,我们需要在继承的easyExcel监听器里传入泛型,告诉easyexcel要封装的对象类型。可以看到EasyExcel 在解析数据的时候,会将一行行数据封装为一个个对象
- 因为对象属性是固定的,如果有需求是你解析的excel数据每行列数是不固定的,此时,就不能自定义固定对象来管理。可以传一个Map<Integer, String>,excel 会将每行数据封装到这个map里面key是列数,value 是值,在具体的监听器里面,可以定制化的,基于map做相应的定制化处理,相对会灵活
6.在监听器里重写onException方法很关键,可以看到某一行,某一列的数据转换异常信息,精准定位数据问题,批量导入的时候,可以将这些异常数据单独落库或者其他方式管理,便于后续查看异常没有落库的数据,针对性校对数据
2.3 优点
- 使用方便,可以看到easyexcel如果单纯处理解析处理数据,没其他定制化业务逻辑的话,使用很简单,定义好数据对象,easyexcel 会直接解析封装好对象
- 按照官网描述,easyexcel处理效率相对更高,也解决了oom的问题
- 使用简单,上手容易
- 阿里开源,国内的组件,文档会偏向于中文
2.4 缺点
- 框架还在持续迭代,属于比较新的类库,一个是稳定性,一个是对excel其他的支持不完善
- 封装的太多,一定程度是优点,提高开发效率,但是很多数据具体处理细节,上层应用开发感知不到,如果文档不完善的话,可能只能靠猜测解决(这个是作者个人理解,见仁见智)
参考文献
poi操作Excel
EasyExcel使用
以上,如有错误,请不吝指正