POI和easyExcel
常用场景
1、将用户信息导出为excel表格(导出数据…)
2、将Excel表中的信息录入到网站数据库(习题上传…)
开发中经常会设计到excel的处理,导出excel,导入excel到数据库中!
操作Excel目前比较流行的就是 Apache POI 和阿里巴巴的 easyExcel !
Apache POI
Apache POI 官网:http://poi.apache.org/
easyExcel
easyExcel 官网地址:https://github.com/alibaba/easyexcel
easyExcel 是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。
POI 是先将全部数据加载到内存中,可能造成OOM。
easyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
官方文档:https://www.yuque.com/easyexcel/doc/easyexcel
POI-Excel写
导入依赖
<!--导入依赖-->
<dependencies>
<!--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>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
03 | 07 版本的写,就是对象不同,方法一样的!
xls(03):最多有65536条数据。
xlsx(07):没有限制。
工作簿:Workbook
工作表:Sheet
行:Row
列:Cell
03版本:
@Test
public void testPoiWrite03() throws Exception {
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
// 1、创建一个工作簿
Workbook workbook = new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
// 3、创建一行(1,1)
Row row1 = sheet.createRow(0);
// 4、创建一个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("观众");
// 5、(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue(666);
// 第二行
// 3、创建一行(1,1)
Row row2 = sheet.createRow(1);
// 4、创建一个单元格
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
// 5、(1,2)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 生成一张表(IO流)
FileOutputStream fileOutputStream = new FileOutputStream(path + "统计表03.xls");
// 输出
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
System.out.println("生成完毕!");
}
07版本:
@Test
public void testPoiWrite07() throws Exception {
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
// 1、创建一个工作簿
Workbook workbook = new XSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
// 3、创建一行(1,1)
Row row1 = sheet.createRow(0);
// 4、创建一个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("观众");
// 5、(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue(666);
// 第二行
// 3、创建一行(1,1)
Row row2 = sheet.createRow(1);
// 4、创建一个单元格
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
// 5、(1,2)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 生成一张表(IO流)
FileOutputStream fileOutputStream = new FileOutputStream(path + "统计表07.xlsx");
// 输出
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
System.out.println("生成完毕!");
}
Workbook 接口实现类和后缀名不一样
数据批量导入!
大文件写HSSFWorkbook(03)
缺点:最多只能处理65536行,否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度块。
@Test
public void testWrite03BigData() throws Exception {
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
long begin = System.currentTimeMillis();
// 1、创建一个工作簿
Workbook workbook = new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet();
// 3、写入数据
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
// 生成一张表(IO流)
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite03BigData.xls");
// 输出
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
大文件写XSSFWorkbook(07)
缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条。
优点:可以写较大的数据量,如20万条。
@Test
public void testWrite07BigData() throws Exception {
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
long begin = System.currentTimeMillis();
// 1、创建一个工作簿
Workbook workbook = new XSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet();
// 3、写入数据
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
// 生成一张表(IO流)
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite07BigData.xlsx");
// 输出
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
大文件写SXSSFWorkbook(07升级版)
缺点:可以写非常大的数据量,如100万条甚至更多条,写数据速度块,占用更少的内存。
注意:
过程中会产生临时文件,需要清理临时文件。
默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件。
如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)
@Test
public void testWrite07BigDataS() throws Exception {
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
long begin = System.currentTimeMillis();
// 1、创建一个工作簿
Workbook workbook = new SXSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet();
// 3、写入数据
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
// 生成一张表(IO流)
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite07BigDataS.xlsx");
// 输出
workbook.write(fileOutputStream);
fileOutputStream.flush();
// 关闭流
fileOutputStream.close();
// 清除临时文件
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
出现OOM,可以使用Jprofile分析!
POI-Excel读
03 | 07
03版本:
@Test
public void testPoiRead03() throws Exception {
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
// 1、获取文件流
FileInputStream inputStream = new FileInputStream(path + "统计表03.xls");
// 2、创建一个工作簿,使用excel能操作的这边都可以操作!
Workbook workbook = new HSSFWorkbook(inputStream);
// 3、得到工作表
Sheet sheet = workbook.getSheetAt(0);
// 4、得到行
Row row = sheet.getRow(0);
// 4、得到列
Cell cell = row.getCell(1);
// 读取值的时候,有类型区别
// getStringCellValue() 字符串类型
System.out.println(cell.getNumericCellValue());
inputStream.close();
}
07版本:
@Test
public void testPoiRead07() throws Exception {
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
// 1、获取文件流
FileInputStream inputStream = new FileInputStream(path + "统计表07.xlsx");
// 2、创建一个工作簿,使用excel能操作的这边都可以操作!
Workbook workbook = new XSSFWorkbook(inputStream);
// 3、得到工作表
Sheet sheet = workbook.getSheetAt(0);
// 4、得到行
Row row = sheet.getRow(0);
// 4、得到列
Cell cell = row.getCell(1);
// 读取值的时候,有类型区别
// getStringCellValue() 字符串类型
System.out.println(cell.getNumericCellValue());
inputStream.close();
}
注意获取值的类型即可(表格思想)
读取不同的数据类型
public void testCellType(FileInputStream inputStream) throws Exception {
// 创建一个工作簿,使用excel能操作的这边都可以操作!
Workbook workbook = new HSSFWorkbook(inputStream);
// 得到工作表
Sheet sheet = workbook.getSheetAt(0);
// 得到标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "|");
}
}
System.out.println();
}
// 得到表中的内容
// 读取行
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {
// 读取列
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowData.getCell(cellNum);
// 匹配列的数据类型
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK: // 空
break;
case HSSFCell.CELL_TYPE_NUMERIC: // 数字(日期,普通数字)
if (HSSFDateUtil.isCellDateFormatted(cell)){ // 日期
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
// 不是日期格式,防止数字过长
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
case HSSFCell.CELL_TYPE_ERROR: // 错误
break;
}
System.out.println(cellValue);
}
}
}
}
inputStream.close();
}
.
easyExcel操作
导入依赖
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
写入测试
@Test
public void simpleWrite() {
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
// 写法1
String fileName = path + "EasyExcel.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// write(fileName,格式类)
// sheet(表明)
// doWrite(数据)
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}
https://www.yuque.com/easyexcel/doc/write
固定实体类格式进行写入
读取测试
@Test
public void simpleRead() {
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
String path = "D:\\ideaWorkSpace\\study-redis\\poi\\";
String fileName = path + "EasyExcel.xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
https://www.yuque.com/easyexcel/doc/read
根据监听器设置的规则进行读取!