POI
easyExcel
easyExcel 官网地址
EasyExcel 是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。
EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中,
而是从磁盘上一行行读取数据,逐个解析。
下图是 EasyExcel 和 POI 在解析Excel时的对比图。
POI快速开始
写
- 创建项目
- 导入依赖
由于excel分为03和07版的所以下面两个版本都导入了,以便都练习一下,推荐使用07版本
<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 版本的写,就是对象不同,方法一样的!
需要注意:2003 版本和 2007 版本存在兼容性的问题!03最多只有 65535 行!
03版本:
@Test
public void testWrite03() throws Exception {
System.out.println("开始");
// 创建工作簿
Workbook workbook = new HSSFWorkbook();
// 创建工作表
Sheet sheet = workbook.createSheet("人员表");
// 创建行
Row row = sheet.createRow(0);
// 创建单元格
Cell cell01 = row.createCell(0);
cell01.setCellValue("张三");
// 创建单元格
Cell cell02 = row.createCell(1);
cell02.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//创建输出流,将新建的文件输出
FileOutputStream outputStream = new FileOutputStream(PATH+"统计表03.xls");
// 输出
workbook.write(outputStream);
// 关闭流
outputStream.close();
System.out.println("结束");
}
07版本
@Test
public void testWrite07() throws Exception{
System.out.println("开始");
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("人员表");
Row row = sheet.createRow(0);
Cell cell01 = row.createCell(0);
cell01.setCellValue("skr");
Cell cell02 = row.createCell(1);
cell02.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
FileOutputStream outputStream = new FileOutputStream(PATH+"统计表07.xlsx");
workbook.write(outputStream);
outputStream.close();
System.out.println("结束");
}
结果:
03版大数据量写入测试
public void testWrite03BigData() throws Exception {
long begin = System.currentTimeMillis();
System.out.println("开始");
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("大数据表");
for (int i = 0; i <= 65535; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
FileOutputStream outputStream = new FileOutputStream(PATH + "大数据表03.xls");
workbook.write(outputStream);
outputStream.close();
System.out.println("结束");
long over = System.currentTimeMillis();
System.out.println("用时:" + (over - begin) / 1000 + "秒");
}
}
由于03版的Excel有最大行数限制,65535行,超出后会报错
07版大数据量测试(普通版)
@Test
public void testWrite03BigData2() throws Exception {
long begin = System.currentTimeMillis();
System.out.println("开始");
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("大数据表");
for (int i = 0; i <= 65535; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
FileOutputStream outputStream = new FileOutputStream(PATH + "大数据表07普通.xlsx");
workbook.write(outputStream);
outputStream.close();
System.out.println("结束");
long over = System.currentTimeMillis();
System.out.println("用时:" + (over - begin) / 1000 + "秒");
}
缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
优点:可以写较大的数据量,如20万条
07版大数据量测试(快)
@Test
public void testWrite07BigData() throws Exception {
long begin = System.currentTimeMillis();
System.out.println("开始");
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("大数据表");
for (int i = 0; i <= 65535; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
FileOutputStream outputStream = new FileOutputStream(PATH + "大数据表07快.xlsx");
workbook.write(outputStream);
outputStream.close();
//清除临时文件
((SXSSFWorkbook)workbook).dispose();
System.out.println("结束");
long over = System.currentTimeMillis();
System.out.println("用时:" + (over - begin) / 1000 + "秒");
}
}
优点:可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存
注意:
过程中会产生临时文件,需要清理临时文件
默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件
如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook ( 数量 )
SXSSFWorkbook-来至官方的解释:实现“BigGridDemo”策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存,因为任何时候只有可配置的行部分被保存在内存中。
请注意,仍然可能会消耗大量内存,这些内存基于您正在使用的功能,例如合并区域,注释…仍然只存储在内存中,因此如果广泛使用,可能需要大量内存
读
03版本
没考虑数据类型
// 定义文件地址,方便存放文件
private static final String PATH = "D:\\newIdeaProject\\poi\\zhm-poi\\";
@Test
public void testRead03() throws Exception{
InputStream inputStream = new FileInputStream(PATH+"zhm-poi统计表03.xls");
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String stringCellValue = cell.getStringCellValue();
System.out.println(stringCellValue);
inputStream.close();
}
07版本
@Test
public void testRead07() throws Exception{
InputStream inputStream = new FileInputStream(PATH+"zhm-poi统计表03.xlsx");
Workbook workbook = new XSSFWorkbook( inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String stringCellValue = cell.getStringCellValue();
System.out.println(stringCellValue);
inputStream.close();
}
读取不同的数据类型
@Test
public void testCellType() throws Exception {
InputStream is = new FileInputStream(PATH + "/会员消费商品明细表.xls");
Workbook workbook = new HSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
// 读取标题所有内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {// 行不为空
// 读取cell
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) {// 行不为空
// 读取cell
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("【" + (rowNum + 1) + "-" + (cellNum + 1) + "】");
Cell cell = rowData.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
//判断单元格数据类型
String cellValue = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING://字符串
System.out.print("【STRING】");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN://布尔
System.out.print("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空
System.out.print("【BLANK】");
break;
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.print("【NUMERIC】");
//cellValue =String.valueOf(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
System.out.print("【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
// 不是日期格式,则防止当数字过长时以科学计数法显示算公式
System.out.print("【转换成字符串】");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("【数据类型错误】");
break;
}
System.out.println(cellValue);
}
}
}
}
is.close();
}
计算公式
@Test
public void testFormula() throws Exception {
InputStream is = new FileInputStream(PATH + "计算公式.xls");
Workbook workbook = new HSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
// 读取第五行第一列
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
//公式计算器
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
// 输出单元内容
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_FORMULA://2
//得到公式
String formula = cell.getCellFormula();
System.out.println(formula);
CellValue evaluate = formulaEvaluator.evaluate(cell);
//String cellValue = String.valueOf(evaluate.getNumberValue());
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
}
}
EasyExcel操作
- 导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
- 写入测试
2.1 创建实体类User.java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
@ExcelProperty("主键")
private Long id;
@ExcelProperty("姓名")
private String name;
@ExcelProperty("年龄")
private Integer age;
@ExcelProperty("邮箱")
private String email;
@ExcelIgnore
private Integer version;
@ExcelIgnore
private Integer deleted;
@ExcelProperty("创建时间")
private Date createTime;
@ExcelProperty("更新时间")
private Date updateTime;
}
2.2 测试写入数据
// 最简单的写
@Test
void testWriteEasyExcel() {
// 这是从数据查出来的数据
List<User> userList = userMapper.selectList(null);
String fileName = PATH + "统计.xlsx";
// 第一个参数:文件的地址和名称拼接
// 第二个参数:哪个对象
// 第三个参数:工作表名
// 第四个参数:数据集合
EasyExcel.write(fileName, User.class).sheet("用户").doWrite(userList);
}
2.3 测试读取excel文件中的数据并添加到数据库
监听器
@Component
public class StringExcelListener extends AnalysisEventListener<User> {
private static final Logger LOGGER = LoggerFactory.getLogger(StringExcelListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<User> list = new ArrayList<User>();
/**
* 注入对象,方便后面调用其中给的存储方法
*/
@Autowired
public UserMapper userMapper;
/**
* 这个每一条数据解析都会来调用
*/
@Override
public void invoke(User user, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(user));
list.add(user);
// 达到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());
/**
* 如果使用的是mybatis的话不建议单个存取,那样的话有损性能
* 自己写个批量存储的方法,直接调取
*/
userMapper.saveBatch(list);
LOGGER.info("存储数据库成功!");
}
}
测试
@Autowired
StringExcelListener stringExcelListener;
/**
* excel文件路径
*/
private static final String PATH = "D:\\newIdeaProject\\mybatisplus-excel\\src\\main\\resources\\";
@Test
void testReadEasyExcel() {
String fileName = PATH + "统计.xlsx";
/**
* 第一个参数:文件地址和文件名的拼接
* 第二个参数:哪个对象
* 第三个参数:定义好的监听器
*/
EasyExcel.read(fileName, User.class, stringExcelListener).sheet().doRead();
}