1.前言
在开发中我们不可豁免的需要操作Excel文档,所以下文我们简单介绍下POI与EasyExcel如何去操作
Excel文档。
2.POI简介
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读
和写的功能。
HSSF - 提供读写Microsoft Excel格式档案的功能。(03版)
XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。(07版)
HWPF - 提供读写Microsoft Word格式档案的功能。
HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
HDGF - 提供读写Microsoft Visio格式档案的功能。
注意:
03版格式为xls 最大行数为65536行
07版格式为xlsx 最大行数为无限制
3.EasyExcel简介
EasyExcel是阿里开发的一个JAVA解析Excel工具。
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,
poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压
缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,能够将
原本一个3M的excel用POI sax依然需要100M左右内存降低到几M,并且再大的excel不会出现内存溢出,03版依
赖POI的sax模式。在上层做了模型转换的封装,让使用者更加简单方便
开发文档:
https://www.yuque.com/easyexcel/doc/easyexcel
4.搭建POI项目操作Excel文档
引入POI需要jar包
<!-- xls(03版) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- xlsx(07版) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!-- 日期格式化工具 -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
5.POI写操作
String PATH = "C:\\2020-10-07-ideaProject\\execl-poi\\";
@Test
void write03Excel() throws Exception {
//1.创建一个工作薄
Workbook workbook = new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("我是sheet1");
//3.在工作表创建一行 从0递增开始
Row row = sheet.createRow(0);
//4.在行上创建单元格
Cell cell_1_1 = row.createCell(0);
cell_1_1.setCellValue("你好明天");
Cell cell_1_2 = row.createCell(1);
cell_1_2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
workbook.write(new FileOutputStream(PATH+"03版本Excel.xls"));
}
@Test
void write07Excel() throws Exception {
//1.创建一个工作薄 SXSSFWorkbook是07版XSSFWorkbook对象增强处理大文件
Workbook workbook = new XSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("我是sheet1");
//3.在工作表创建一行 从0递增开始
Row row = sheet.createRow(0);
//4.在行上创建单元格
Cell cell_1_1 = row.createCell(0);
cell_1_1.setCellValue("你好明天");
Cell cell_1_2 = row.createCell(1);
cell_1_2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
workbook.write(new FileOutputStream(PATH+"07版本Excel.xlsx"));
}
6.POI写大数据操作
操作写大量数据规则:
HSSF(03版)
@Test
void write03Excel() throws Exception {
long startTime = System.currentTimeMillis();
//1.创建一个工作薄
Workbook workbook = new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("我是sheet1");
for(int i=0;i<65536;i++){
Row row = sheet.createRow(i);
for(int j=0;j<10;j++){
Cell rowCell = row.createCell(j);
rowCell.setCellValue(j);
}
}
workbook.write(new FileOutputStream(PATH+"03版本ExcelBig.xls"));
long endTime = System.currentTimeMillis();
System.out.println("消耗总秒数:"+((double)(endTime - startTime)/1000));
}
消耗总秒数:2.075
缺点:最多只能处理65536行,否则会抛出异常。
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快。
XSSF(07普通版)
@Test
void write07Excel() throws Exception {
long startTime = System.currentTimeMillis();
//1.创建一个工作薄
Workbook workbook = new XSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("我是sheet1");
for(int i=0;i<65536;i++){
Row row = sheet.createRow(i);
for(int j=0;j<10;j++){
Cell rowCell = row.createCell(j);
rowCell.setCellValue(j);
}
}
workbook.write(new FileOutputStream(PATH+"07版本ExcelBig.xlsx"));
long endTime = System.currentTimeMillis();
System.out.println("消耗总秒数:"+((double)(endTime - startTime)/1000));
}
消耗总秒数:11.851
缺点:写数据速度非常慢,非常耗内存,也会发生内存溢出,列如100万条数据
优点:可以写较大的数据量,如20万条
注意:XSSF写大量数据耗时大于HSSF
SXSSF(07升级版)
@Test
void write07Excel() throws Exception {
long startTime = System.currentTimeMillis();
//1.创建一个工作薄
Workbook workbook = new SXSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("我是sheet1");
for(int i=0;i<65536;i++){
Row row = sheet.createRow(i);
for(int j=0;j<10;j++){
Cell rowCell = row.createCell(j);
rowCell.setCellValue(j);
}
}
workbook.write(new FileOutputStream(PATH+"07版本ExcelBig增强版.xlsx"));
//清除临时文件
((SXSSFWorkbook)workbook).dispose();
long endTime = System.currentTimeMillis();
System.out.println("消耗总秒数:"+((double)(endTime - startTime)/1000));
}
消耗总秒数:2.819
优点:可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存。
注意:
过程中会产生临时文件,需要清理临时文件。
默认100记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件。
如果想要自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)
7.POI读操作
String PATH = "C:\\2020-10-07-ideaProject\\execl-poi\\";
@Test
void read07Excel() throws Exception {
FileInputStream inputStream = new FileInputStream(PATH + "hello.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
//获取总的工作表
for(int sheetNum = 0; sheetNum < workbook.getNumberOfSheets();sheetNum++){
Sheet sheet = workbook.getSheetAt(sheetNum);
if(null != sheet){
for(int rowNum = 0; rowNum < sheet.getPhysicalNumberOfRows();rowNum++){
Row row = sheet.getRow(rowNum);
if(null != row){
for(int cellNum=0; cellNum<row.getPhysicalNumberOfCells();cellNum++){
System.out.print(sheetNum+"--["+rowNum+","+cellNum+"]"+"\t");
Cell cell = row.getCell(cellNum);
if(null != cell){
cellType(cell);
}
}
}
System.out.println("\n");
}
}
}
inputStream.close();
}
void cellType(Cell cell){
String value = "";
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING :
System.out.print("【String】");
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.print("【Boolean】");
value = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK :
System.out.print("【NULL】");
break;
case HSSFCell.CELL_TYPE_NUMERIC :
System.out.print("【Number】");
if(HSSFDateUtil.isCellDateFormatted(cell)){
System.out.print("【Date】");
value = new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd HH:mm:ss");
}else{
System.out.print("数字转为字符输出");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
value = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR :
System.out.print("无法解析字段类型");
break;
}
System.out.print(value);
System.out.println("\n");
}
8.EasyExcel
参考开发文档:
https://www.yuque.com/easyexcel/doc/easyexcel