010_POI和EasyExcel
目录
常用场景
流行技术：Apache POI和EasyExcel
Apache POI简介
EasyExcel简介
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址:https://github.com/alibaba/easyexcel
JAVA解析Excel工具EasyExcel
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模式。在上层做了模型转换的封装，让使用者更加简单方便
64M内存1分钟内读取75M(46W行25列)的Excel
当然还有急速模式能更快，但是内存占用会在100M多一点
关于EasyExcel
Excel读写时候内存溢出
虽然POI是目前使用最多的用来做excel解析的框架，但这个框架并不那么完美。大部分使用POI都是使用他的userModel模式。userModel的好处是上手容易使用简单，随便拷贝个代码跑一下，剩下就是写业务转换了，虽然转换也要写上百行代码，相对比较好理解。然而userModel模式最大的问题是在于非常大的内存消耗，一个几兆的文件解析要用掉上百兆的内存。现在很多应用采用这种模式，之所以还正常在跑一定是并发不大，并发上来后一定会OOM或者频繁的full gc。
其他开源框架使用复杂
对POI有过深入了解的估计才知道原来POI还有SAX模式。但SAX模式相对比较复杂，excel有03和07两种版本，两个版本数据存储方式截然不同，sax解析方式也各不一样。想要了解清楚这两种解析方式，才去写代码测试，估计两天时间是需要的。再加上即使解析完，要转换到自己业务模型还要很多繁琐的代码。总体下来感觉至少需要三天，由于代码复杂，后续维护成本巨大。
其他开源框架存在一些BUG修复不及时
由于我们的系统大多数都是大并发的情况下运行的，在大并发情况下，我们会发现poi存在一些bug,如果让POI团队修复估计遥遥无期了。所以我们在easyexcel对这些bug做了规避。
Excel格式分析格式分析
- xls是Microsoft Excel2007前excel的文件存储格式，实现原理是基于微软的ole db是微软com组件的一种实现，本质上也是一个微型数据库，由于微软的东西很多不开源，另外也已经被淘汰，了解它的细节意义不大，底层的编程都是基于微软的com组件去开发的。
- xlsx是Microsoft Excel2007后excel的文件存储格式，实现是基于openXml和zip技术。这种存储简单，安全传输方便，同时处理数据也变的简单。
- csv 我们可以理解为纯文本文件，可以被excel打开。他的格式非常简单，解析起来和解析文本文件一样。
核心原理
写有大量数据的xlsx文件时，POI为我们提供了SXSSFWorkBook类来处理，这个类的处理机制是当内存中的数据条数达到一个极限数量的时候就flush这部分数据，再依次处理余下的数据，这个在大多数场景能够满足需求。
读有大量数据的文件时，使用WorkBook处理就不行了，因为POI对文件是先将文件中的cell读入内存，生成一个树的结构（针对Excel中的每个sheet，使用TreeMap存储sheet中的行）。如果数据量比较大，则同样会产生java.lang.OutOfMemoryError: Java heap space错误。POI官方推荐使用“XSSF and SAX（event API）”方式来解决。
分析清楚POI后要解决OOM有3个关键。
解压文件读取通过文件形式
避免将全部数据一次加载到内存
采用sax模式一行一行解析，并将一行的解析结果以观察者的模式通知处理。
抛弃不重要的数据
Excel解析时候会包含样式，字体，宽度等数据，但这些数据是我们不关心的，如果将这部分数据抛弃可以大大降低内存使用。Excel中数据如下Style占了相当大的空间。
POI-Excel基本写
创建空项目
创建子模块qing-poi
导入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.qing</groupId>
<artifactId>qing-poi</artifactId>
<version>1.0-SNAPSHOT</version>
<!--导入依赖-->
<dependencies>
<!--xls(03)-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!--xlsx(07)-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<!--日期格式化工具-->
<!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.10</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-api -->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.7.2</version>
</dependency>
</dependencies>
</project>
Excel的对象
工作簿
工作表
行
单元格
写03版Excel
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 写03版Excel
*/
@Test
public void testWrite03() throws IOException {
// 1.创建一个工作簿，03版使用对象HSSFWorkbook，07版使用对象XSSFWorkbook
Workbook workbook = new HSSFWorkbook();
// 2.创建一个工作表，不传参默认Sheet1
Sheet sheet = workbook.createSheet("自定义Sheet1");
// 3.创建一个行，0表示第一行
Row row1 = sheet.createRow(0);
// 4.创建一个单元格，0表示第一列的单元格
// 第一行
// (1,1)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("第一行第一列");
// (1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("第一行第二列");
// 第二行
Row row2 = sheet.createRow(1);
// (2,1)
Cell cell21 = row2.createCell(0);
cell21.setCellValue("第二行第一列");
// (2,2)
Cell cell22 = row2.createCell(1);
cell22.setCellValue("第二行第二列");
// 第三行
Row row3 = sheet.createRow(2);
// (3,1)
Cell cell31 = row3.createCell(0);
cell31.setCellValue("时间");
// (2,2)
Cell cell32 = row3.createCell(1);
// joda的日期比较好用
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell32.setCellValue(time);
// 5.生成一张表（IO流） 03版本必须使用xls结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03.xls");
// 输出
workbook.write(fileOutputStream);
// 6.关闭流
fileOutputStream.close();
System.out.println("03.xls生成完毕");
}
}
写07版Excel
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 写07版Excel
*/
@Test
public void testWrite07() throws IOException {
// 1.创建一个工作簿，03版使用对象HSSFWorkbook，07版使用对象XSSFWorkbook
Workbook workbook = new XSSFWorkbook();
// 2.创建一个工作表，不传参默认Sheet1
Sheet sheet = workbook.createSheet("自定义Sheet1");
// 3.创建一个行，0表示第一行
Row row1 = sheet.createRow(0);
// 4.创建一个单元格，0表示第一列的单元格
// 第一行
// (1,1)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("第一行第一列");
// (1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("第一行第二列");
// 第二行
Row row2 = sheet.createRow(1);
// (2,1)
Cell cell21 = row2.createCell(0);
cell21.setCellValue("第二行第一列");
// (2,2)
Cell cell22 = row2.createCell(1);
cell22.setCellValue("第二行第二列");
// 第三行
Row row3 = sheet.createRow(2);
// (3,1)
Cell cell31 = row3.createCell(0);
cell31.setCellValue("时间");
// (2,2)
Cell cell32 = row3.createCell(1);
// joda的日期比较好用
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell32.setCellValue(time);
// 5.生成一张表（IO流） 07版本必须使用xlsx结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07.xlsx");
// 输出
workbook.write(fileOutputStream);
// 6.关闭流
fileOutputStream.close();
System.out.println("07.xlsx生成完毕");
}
}
03和07版的不同
POI-Excel大文件写
HSSF写大文件（03）
- 优点
- 过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
- 缺点
- 最多只能处理65536行，否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 写03版Excel-大文件
*/
@Test
public void testWrite03BigData() throws IOException {
// 起始时间
long begin = System.currentTimeMillis();
// 1.创建一个工作簿，03版使用对象HSSFWorkbook，07版使用对象XSSFWorkbook
Workbook workbook = new HSSFWorkbook();
// 2.创建一个工作表，不传参默认Sheet1
Sheet sheet = workbook.createSheet("自定义Sheet1");
// 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);
}
}
// 4.生成一张表（IO流） 03版本必须使用xls结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03BigData.xls");
// 输出
workbook.write(fileOutputStream);
// 5.关闭流
fileOutputStream.close();
System.out.println("03BigData.xls生成完毕");
// 截止时间
long end = System.currentTimeMillis();
System.out.println("消耗时间：" + ((double)end-begin)/1000 + "s");
}
}
XSSF写大文件（07）
- 优点
- 可以写较大的数据量，如：20万条
- 缺点
- 写数据时速度非常慢，非常耗内存，也会发生内存溢出，如：100万条、1000万条
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 写07版Excel-大文件
*/
@Test
public void testWrite07BigData() throws IOException {
// 起始时间
long begin = System.currentTimeMillis();
// 1.创建一个工作簿，03版使用对象HSSFWorkbook，07版使用对象XSSFWorkbook
Workbook workbook = new XSSFWorkbook();
// 2.创建一个工作表，不传参默认Sheet1
Sheet sheet = workbook.createSheet("自定义Sheet1");
// 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);
}
}
// 4.生成一张表（IO流） 07版本必须使用xlsx结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BigData.xlsx");
// 输出
workbook.write(fileOutputStream);
// 5.关闭流
fileOutputStream.close();
System.out.println("07BigData.xlsx生成完毕");
// 截止时间
long end = System.currentTimeMillis();
System.out.println("消耗时间：" + ((double)end-begin)/1000 + "s");
}
}
SXSSF写大文件（07快速版）
- 优点
- 可以写非常大的数据量，如：100万条甚至更多条
- 写数据速度快，占用更少的内存
- 缺点
- 注意
- 过程中会产生临时文件，需要清理临时文件
((SXSSFWorkbook) workbook).dispose(); - 默认由100条记录被保存在内存中，如果超过该数量，则最前面的数据被写入临时文件
- 如果想自定义内存中数据的数量，可以使用
new SXSSFWorkbook(数量);
- 过程中会产生临时文件，需要清理临时文件
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 写07版Excel-大文件（SXSSF快速版）
*/
@Test
public void testWrite07BigDataS() throws IOException {
// 起始时间
long begin = System.currentTimeMillis();
// 1.创建一个工作簿，03版使用对象HSSFWorkbook，07版使用对象XSSFWorkbook，07快速版使用对象SXSSFWorkbook
Workbook workbook = new SXSSFWorkbook();
// 2.创建一个工作表，不传参默认Sheet1
Sheet sheet = workbook.createSheet("自定义Sheet1");
// 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);
}
}
// 4.生成一张表（IO流） 07版本必须使用xlsx结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BigDataS.xlsx");
// 输出
workbook.write(fileOutputStream);
// 5.关闭流
fileOutputStream.close();
// 清除临时文件
((SXSSFWorkbook) workbook).dispose();
System.out.println("07BigDataS.xlsx生成完毕");
// 截止时间
long end = System.currentTimeMillis();
System.out.println("消耗时间：" + ((double)end-begin)/1000 + "s");
}
}
POI-Excel基本读
读03版Excel
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
public class ExcelReadTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 读03版Excel
*/
@Test
public void testRead03() throws IOException {
// 1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "03.xls");
// 2.创建一个工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 3.得到表，可以通过名称或下标获取
Sheet sheet = workbook.getSheetAt(0);
// 4.得到行
Row row = sheet.getRow(0);
// 5.得到单元格
Cell cell = row.getCell(0);
// 6.读取值，读取值的时候要注意数据类型，否则会报错
// System.out.println(cell.getNumericCellValue());
System.out.println(cell.getStringCellValue());
// 7.关闭流
fileInputStream.close();
}
}
读07版Excel
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
public class ExcelReadTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 读07版Excel
*/
@Test
public void testRead07() throws IOException {
// 1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "07.xlsx");
// 2.创建一个工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);
// 3.得到表，可以通过名称或下标获取
Sheet sheet = workbook.getSheetAt(0);
// 4.得到行
Row row = sheet.getRow(0);
// 5.得到单元格
Cell cell = row.getCell(0);
// 6.读取值，读取值的时候要注意数据类型，否则会报错
// System.out.println(cell.getNumericCellValue());
System.out.println(cell.getStringCellValue());
// 7.关闭流
fileInputStream.close();
}
}
读取不同的数据类型
自己处理不同的数据类型
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
public class ExcelReadTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 读取不同的数据类型
*/
@Test
public void testCellType() throws IOException {
// 1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "明细表.xls");
// 2.创建一个工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 3.得到表，可以通过名称或下标获取
Sheet sheet = workbook.getSheetAt(0);
// 4.获取表头行内容
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) {
// 获取值类型
CellType cellType = cell.getCellType();
// 知道表头行都是字符串，所以不需要根据类型输出
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
}
// 5.获取表体行内容
// 获取行数
int rowCount = sheet.getPhysicalNumberOfRows();
// 跳过表头行，所有从1开始
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
System.out.println();
Row row = sheet.getRow(rowNum);
if (row != null) {
// 获取表头行列数
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum+1) + "-" + (cellNum+1) + "]");
Cell cell = row.getCell(cellNum);
if (cell != null) {
// 获取值类型
CellType cellType = cell.getCellType();
String cellValue = "";
// 匹配数据类型
switch (cellType) {
case _NONE: // 未知类型，仅限内部使用
System.out.print("[未知类型]");
break;
case NUMERIC: // 数字类型（日期、普通数字）
System.out.print("[数字类型（日期、普通数字）]");
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 STRING: // 字符串
System.out.print("[字符串]");
cellValue = cell.getStringCellValue();
break;
case FORMULA: // 公式
System.out.print("[公式]");
// 获取公式计算程序
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
// 计算公式
CellValue evaluate = formulaEvaluator.evaluate(cell);
cellValue = evaluate.formatAsString();
break;
case BLANK: // 空单元格，没值，但有单元格样式
System.out.print("[空]");
break;
case BOOLEAN: // 布尔值
System.out.print("[布尔值]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR: // 错误单元格
System.out.print("[错误单元格]");
break;
}
System.out.println(cellValue);
}
}
}
}
// 7.关闭流
fileInputStream.close();
}
}
使用cell.toString处理不同的数据类型，但会有日期和长数字会显示问题
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
public class ExcelReadTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 读取不同的数据类型-使用cell.toString()
*/
@Test
public void testCellTypeCellToString() throws IOException {
// 1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "明细表.xls");
// 2.创建一个工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 3.得到表，可以通过名称或下标获取
Sheet sheet = workbook.getSheetAt(0);
// 4.获取表头行内容
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) {
// 获取值类型
CellType cellType = cell.getCellType();
// 知道表头行都是字符串，所以不需要根据类型输出
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
}
// 5.获取表体行内容
// 获取行数
int rowCount = sheet.getPhysicalNumberOfRows();
// 跳过表头行，所有从1开始
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
System.out.println();
Row row = sheet.getRow(rowNum);
if (row != null) {
// 获取表头行列数
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum+1) + "-" + (cellNum+1) + "]");
Cell cell = row.getCell(cellNum);
if (cell != null) {
// 获取值类型
CellType cellType = cell.getCellType();
System.out.print("[" + cellType + "]");
String cellValue = cell.toString();
System.out.println(cellValue);
}
}
}
}
// 7.关闭流
fileInputStream.close();
}
}
使用cell.toString处理不同的数据类型，自己处理数字和日期显示
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
public class ExcelReadTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 读取不同的数据类型-使用cell.toString()-优化日期和数字的显示
*/
@Test
public void testCellTypeCellToStringNumeric() throws IOException {
// 1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "明细表.xls");
// 2.创建一个工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 3.得到表，可以通过名称或下标获取
Sheet sheet = workbook.getSheetAt(0);
// 4.获取表头行内容
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) {
// 获取值类型
CellType cellType = cell.getCellType();
// 知道表头行都是字符串，所以不需要根据类型输出
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
}
// 5.获取表体行内容
// 获取行数
int rowCount = sheet.getPhysicalNumberOfRows();
// 跳过表头行，所有从1开始
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
System.out.println();
Row row = sheet.getRow(rowNum);
if (row != null) {
// 获取表头行列数
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum+1) + "-" + (cellNum+1) + "]");
Cell cell = row.getCell(cellNum);
if (cell != null) {
// 获取值类型
CellType cellType = cell.getCellType();
System.out.print("[" + cellType + "]");
String cellValue = "";
// 匹配数据类型
switch (cellType) {
case NUMERIC: // 数字类型（日期、普通数字）
System.out.print("[数字类型（日期、普通数字）]");
if (DateUtil.isCellDateFormatted(cell)) { // 日期
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
// 不是日期格式，防止数字过长，转换为字符串输出
System.out.print("[数字]");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
}
break;
default:
cellValue = cell.toString();
}
System.out.println(cellValue);
}
}
}
}
// 7.关闭流
fileInputStream.close();
}
}
读取公式及计算
package com.qing;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
public class ExcelReadTest {
// 路径
String PATH = "D:\\code\\excel\\qing-poi\\";
/**
* 读取公式及计算
*/
@Test
public void testFormula() throws IOException {
// 1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "公式.xls");
// 2.创建一个工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 3.得到表，可以通过名称或下标获取
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
if (cell != null) {
// 获取公式计算程序
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
// 获取值类型
CellType cellType = cell.getCellType();
String cellValue = "";
// 匹配数据类型
switch (cellType) {
case FORMULA: // 公式
// 获取公式
String formula = cell.getCellFormula();
System.out.println("公式：" + formula);
// 计算公式
CellValue evaluate = formulaEvaluator.evaluate(cell);
cellValue = evaluate.formatAsString();
break;
}
System.out.println(cellValue);
}
// 7.关闭流
fileInputStream.close();
}
}
EasyExcel使用
打开github，找到pom.xml
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
复制EasyExcel依赖到本地pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.qing</groupId>
<artifactId>easyexcel</artifactId>
<version>1.0-SNAPSHOT</version>
<!--导入依赖-->
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
</dependencies>
</project>
测试简单的写
写实体
package com.qing;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
导入lombok依赖
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
写出文件
package com.qing;
import com.alibaba.excel.EasyExcel;
import org.junit.jupiter.api.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class EasyExcelTest {
// 路径
String PATH = "D:\\code\\excel\\easyexcel\\";
/**
* 最简单的写
*/
@Test
public void simpleWrite() {
// 文件名
String fileName = PATH + "EasyExcelTest.xlsx";
/*
write(fileName, DemoData.class) 设置文件名，写入的实体类
sheet("模板") 设置sheet名称
doWrite(data() 设置写的数据
*/
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
}
测试简单的读
写监听器
package com.qing;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import java.util.ArrayList;
import java.util.List;
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<DemoData> list = new ArrayList<DemoData>();
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private DemoDAO demoDAO;
public DemoDataListener() {
// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoDAO = new DemoDAO();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param demoDAO
*/
public DemoDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(DemoData data, AnalysisContext context) {
System.out.println("解析到一条数据:" + JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
System.out.println("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
System.out.println(list.size() + "条数据，开始存储数据库！");
demoDAO.save(list);
System.out.println("存储数据库成功！");
}
}
读取文件
package com.qing;
import com.alibaba.excel.EasyExcel;
import org.junit.jupiter.api.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class EasyExcelTest {
// 路径
String PATH = "D:\\code\\excel\\easyexcel\\";
/**
* 最简单的读
* <p>1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
* <p>3. 直接读即可
*/
@Test
public void simpleRead() {
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
String fileName = PATH + "EasyExcelTest.xlsx";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
}
