文章目录
一、介绍
1、POI
Apache POI是基于Office Open XML标准(OOXML)和Microsoft的OLE 2复合文档格式(OLE2)处理各种文件格式的开源项目。 简而言之,您可以使用Java读写MS Excel文件,可以使用Java读写MS Word和MS PowerPoint文件。
POI需要占用较大内存的主要原因是在解析Excel时将文件数据一次性全部加载到内存中。
本文章只是介绍Java操作Excel,我们知道2003版的Excel和2007+版的Excel是有比较大的差异的
,所以操作 2003版的Excel 和 2007+版的Excel 的对象是不同的。
-
HSSF - 提供读写Microsoft Excel XLS格式(
Microsoft Excel 97 (-2003)
)档案的功能。 -
XSSF - 提供读写Microsoft Excel OOXML XLSX格式(
Microsoft Excel XML (2007+)
)档案的功能。 -
SXSSF - 提供读写Microsoft Excel OOXML XLSX格式(
Microsoft Excel XML (2007+)
)档案的功能,速度更快,使用缓存,具有临时文件。
2、EasyExcel
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从 磁盘上一行行读取数据,逐个解析
。
EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。
64M内存1分钟内读取75M(46W行25列)的Excel
当然还有急速模式能更快,但是内存占用会在100M多一点
Excel导入导出的应用场景
- 数据导入:减轻录入工作量
- 数据导出:统计信息归档
- 数据传输:异构系统之间数据传输
二、POI 实现 Excel 的基本写入
注意是否为空判断(我没有写)
1、相关依赖
<!-- office 2003 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- office 2007 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<!-- 日期格式化工具 -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.10</version>
</dependency>
2、将数据写入xls中
@Test
void contextLoads01() throws Exception {
String path = "E:\\ideaProject\\ExcelOpt\\excel_opt-poi-01\\";
// 1 创建一个xls工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 2 创建一个sheet(设置名字为demo)
HSSFSheet sheet = workbook.createSheet("demo");
// 3 在sheet中创建第一行(是从0开始的)
HSSFRow row1 = sheet.createRow(0);
// 4 在第一行中创建第一个单元格(0,0)
HSSFCell cell11 = row1.createCell(0);
// 5 设置单元格的值
cell11.setCellValue("名称");
// 6 在第一行中创建第二个单元格(0,1)
HSSFCell cell12 = row1.createCell(1);
// 7 设置单元格的值
cell12.setCellValue("studioustiger");
// 在sheet中创建第二行
HSSFRow row2 = sheet.createRow(1);
// 在第二行中创建第一个单元格(1,0)
HSSFCell cell21 = row2.createCell(0);
// 设置单元格的值
cell21.setCellValue("创建时间");
// 在第二行中创建第二个单元格(1,1)
HSSFCell cell22 = row2.createCell(1);
// 设置单元格的值
cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
// 8 创建一个文件输出流(指定Excel的位置和名称及其后缀)
FileOutputStream fileOutputStream = new FileOutputStream(path + "demo01.xls");
// 9 写入工作目标文件
workbook.write(fileOutputStream);
// 10 关闭文件流
fileOutputStream.close();
System.out.println("ok");
}
3、将数据写入xlsx中
xlsx和xls的唯一一点不同就是使用的是XSSF,其他方面没有区别
@Test
void contextLoads02() throws Exception {
String path = "E:\\ideaProject\\ExcelOpt\\excel_opt-poi-01\\";
// 1 创建一个xlsx工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 2 创建一个sheet
XSSFSheet sheet = workbook.createSheet("demo");
// 3 在sheet中创建第一行
XSSFRow row1 = sheet.createRow(0);
// 4 在第一行中创建第一个单元格(0,0)
XSSFCell cell11 = row1.createCell(0);
// 5 设置单元格的值
cell11.setCellValue("名称");
// 6 在第一行中创建第二个单元格(0,1)
XSSFCell cell12 = row1.createCell(1);
// 7 设置单元格的值
cell12.setCellValue("studioustiger");
// 在sheet中创建第二行
XSSFRow row2 = sheet.createRow(1);
// 在第二行中创建第一个单元格(1,0)
XSSFCell cell21 = row2.createCell(0);
// 设置单元格的值
cell21.setCellValue("创建时间");
// 在第二行中创建第二个单元格(1,1)
XSSFCell cell22 = row2.createCell(1);
// 设置单元格的值
cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
// 8 创建一个文件输出流
FileOutputStream fileOutputStream = new FileOutputStream(path + "demo02.xlsx");
// 9 写入工作目标文件
workbook.write(fileOutputStream);
// 10 关闭文件流
fileOutputStream.close();
System.out.println("ok");
}
三、POI 实现大量数据的 Excel 写入
1、使用 HSSF 写大量数据
使用HSSF的方式写入Excel,有一个局限性是写入的数据最大不能超过65536条。
当我们的数据条数超过65536条数据时,会出现如下的错误:
注意是否为空判断(我没有写)
写入65536条数据时
// 使用 HSSF 写大量数据(写入数据不可以超过65536)
@Test
void contextLoads03() throws Exception {
long begin = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("sheet01");
for (int i = 0; i < 65536; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j+1);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(path + "bigDataInsert.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double)(end-begin)/1000 +" s");
}
2、使用 XSSF 写大量数据
使用XSSF的方式写入Excel,对于写入的数据的条数没有要求,但是不足的是耗时比较长,而且当我们写入的数据过大时,会出现内存移除现象(例如:一次性写入10000000条数据)
写入100000条数据时
// 使用 XSSF 写大量数据(写入数据可以超过65536,速度比较慢)
@Test
void contextLoads04() throws Exception {
long begin = System.currentTimeMillis();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("sheet01");
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+1);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(path + "bigDataInsert.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double)(end-begin)/1000 +" s");
}
3、使用 SXSSF 写大量数据
SXSSF是在XSSF的基础上进行改进的,其优点就是耗时更小了,这是因为SXSSF使用了缓存技术提高了效率。默认是一次将100条数据写入缓存区。因为使用到了缓冲区,所以存在临时文件
写入100000条数据时
// 使用 SXSSF 写大量数据(写入数据可以超过65536,速度更快)
@Test
void contextLoads05() throws Exception {
long begin = System.currentTimeMillis();
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("sheet01");
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+1);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(path + "bigDataInsertSuper.xlsx");
workbook.write(fileOutputStream);
((SXSSFWorkbook)workbook).dispose(); // 清除临时文件
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double)(end-begin)/1000 +" s");
}
四、POI 实现 Excel 的基本读取
注意是否为空判断(我没有写)
1、简单获取2003版本中的数据
workbook.getSheetAt(0):通过索引获取sheet表
workbook.getSheet(“demo”):通过名称获取sheet表
cell.getStringCellValue():这个和原生的jdbc很类似,因为获取的内容,要是用与其类型相符的方法去获取
// 简单获取2003版本中的数据
@Test
void contextLoads06() throws Exception {
// 1 创建一个文件输入流
FileInputStream fileInputStream = new FileInputStream(path + "demo01.xls");
// 2 获取workbook,将创建一个文件输入流作为workbook的参数
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 通过sheet的名称获取指定的表
// Sheet demo = workbook.getSheet("demo");
// 3 通过索引获取指定的表(0 表示第一个sheet表)
Sheet sheet = workbook.getSheetAt(0);
// 4 获取指定的行
Row row = sheet.getRow(0);
// 5 获取行中的指定的额单元格
Cell cell = row.getCell(0);
// 6 因为(0,0)的数据时String类型,所以这里使用getStringCellValue
String value = cell.getStringCellValue();
// 7 关闭流
fileInputStream.close();
System.out.println(value);
// 可以获取不同类型的数据
// double value = cell.getNumericCellValue();
// boolean value = cell.getBooleanCellValue();
// Date value = cell.getDateCellValue();
}
2、简单获取2007+版本中的数据
// 简单获取2007+版本中的数据
@Test
void contextLoads07() throws Exception {
FileInputStream fileInputStream = new FileInputStream(path + "demo02.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String value = cell.getStringCellValue();
fileInputStream.close();
System.out.println(value);
}
五、POI实现读取不同数据类型的数据(☆)
通常情况下,一个Excel表中的数据会有很多中,如下:
对于这种情况,我们需要使用不同的方法来获取其中的数据,但是我们通常又不知道Excel表中的数据类型,所以我们又必须进行判断,然后进行获取。
下面我们以xls的Excel为例,进行读取不同的数据类型的数据(如果你就得比较麻烦,那么你可以将其封装成一个工具类)
// 读取不同的数据类型的数据(2003版)
@Test
void contextLoads08() throws Exception{
FileInputStream fileInputStream = new FileInputStream(path + "demo01.xls");
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 获取名称为demo的sheet表
Sheet sheet = workbook.getSheet("demo");
// 获取sheet表中行数
int rowNum = sheet.getPhysicalNumberOfRows();
// 遍历sheet表中的行
for (int rowIndex = 0; rowIndex < rowNum; rowIndex++) {
// 获取一行
Row row = sheet.getRow(rowIndex);
// 判断是否为空
if(row!=null){
// 获取该行中的单元格的个数
int cellNum = row.getPhysicalNumberOfCells();
// 遍历该行中的单元格
for (int cellIndex = 0; cellIndex < cellNum; cellIndex++) {
// 获取单元格
Cell cell = row.getCell(cellIndex);
// 判断是否为空
if (cell!=null){
// 获取当前单元格数据的类型
CellType cellType = cell.getCellType();
switch (cellType){
case _NONE: // none
System.out.print(" \t");
break;
case BLANK: // 空
System.out.print(" \t");
break;
case ERROR: // 错误
System.out.println("error"+"\t");
break;
case STRING: // 字符串
System.out.print(String.valueOf(cell.getStringCellValue()+"\t"));
break;
case BOOLEAN: // 布尔值
System.out.print(String.valueOf(cell.getBooleanCellValue()+"\t"));
break;
case FORMULA: // 公式
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);
CellValue evaluate = formulaEvaluator.evaluate(cell);
System.out.println(evaluate.formatAsString()+"\t");
break;
case NUMERIC: // 数字(数字和日期)
System.out.print(String.valueOf(cell.getNumericCellValue()+"\t"));
break;
}
}
}
System.out.println();
}
}
// 关闭流
fileInputStream.close();
}
六、计算公式(拓展)
在Excel中会使用大量的公式,对于使用公式的单元格,我们并不能使用上述的方法直接将其读取出来。对于使用公式的单元格POI给我们提供的专门的操作方法。
如下图所示的单元格就是一个求和公式,下面我们就会使用
HSSFFormulaEvaluator((HSSFWorkbook)workbook):公式直译器,就是通过这个对象可以将Excel中的公式进行翻译计算
cell.getCellFormula():获取公式
formulaEvaluator.evaluate(cell).formatAsString():计算公式结果并格式化成字符串
// 翻译计算公式
@Test
void contextLoads09() throws Exception {
FileInputStream fileInputStream = new FileInputStream(path + "demo01.xls");
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 获取名称为demo的sheet表
Sheet sheet = workbook.getSheet("demo");
Row row = sheet.getRow(3);
Cell cell = row.getCell(1);
// 创建一个公式直译器(将workbook作为参数传递进去)
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);
// 获取一下单元格的类型
CellType cellType = cell.getCellType();
// 匹配一下
switch (cellType){
case FORMULA:
// 获取公式
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);
// 计算公式结果
CellValue evaluate = formulaEvaluator.evaluate(cell);
// 格式化成字符串
String formulaRes = evaluate.formatAsString();
System.out.println(formulaRes);
}
}
七、EasyExcel 操作 Excel(☆)
easyexcel文档:https://www.yuque.com/easyexcel/doc/read
1、依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
2、写Excel
在easyExcel中实现Excel的写是通过对象进行写入的,easyExcel会自动解析对象中的属性,写入Excel
案例一
效果图
我们可以定义一个实体类,类中包含三个字段,即上图对应的三列
@Data
public class DemoData {
@ExcelProperty(value = "字符串标题")
private String string;
@ExcelProperty(value = "日期标题")
private Date date;
@ExcelProperty(value = "数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
向DemoData中填入数据,将一个一个的实体类封装早list集合中
private List<DemoData> data01(){
ArrayList<DemoData> lists = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DemoData demoData = new DemoData();
demoData.setString("tiger"+(i+1));
// 比较有意思的其会自动格式化成yyyy-MM-dd HH:mm:ss
demoData.setDate(new Date());
demoData.setDoubleData(1.8);
lists.add(demoData);
}
return lists;
}
写入Excel
fileName:Excel的路径
DemoData.class:Excel对应的实体类
sheet(“模板”):sheet表的名称
doWrite(data01()):写入的数据,参数是List类型
private String path = "E:\\ideaProject\\ExcelOpt\\easy_excel-01\\";
@Test
void contextLoads01() {
String fileName = path + "simpleWrite.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data01());
}
案例二
效果图
实体类
@Data
public class IndexData {
@ExcelProperty(value = "字符串标题", index = 0)
private String string;
@ExcelProperty(value = "日期标题", index = 1)
private Date date;
/**
* 这里设置3 会导致第二列空的
*/
@ExcelProperty(value = "数字标题", index = 3)
private Double doubleData;
}
填充list
private List<IndexData> data02(){
ArrayList<IndexData> lists = new ArrayList<>();
for (int i = 0; i < 10; i++) {
IndexData data = new IndexData();
data.setString("tiger"+(i+1));
// 比较有意思的其会自动格式化成yyyy-MM-dd HH:mm:ss
data.setDate(new Date());
data.setDoubleData(1.8);
lists.add(data);
}
return lists;
}
写入Excel
private String path = "E:\\ideaProject\\ExcelOpt\\easy_excel-01\\";
void contextLoads02(){
String fileName = path + "indexWrite.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, IndexData.class).sheet("模板").doWrite(data02());
}
案例三
效果图
实体类
@Data
public class ComplexHeadData {
@ExcelProperty({"主标题", "字符串标题"})
private String string;
@ExcelProperty({"主标题", "日期标题"})
private Date date;
@ExcelProperty({"主标题", "数字标题"})
private Double doubleData;
}
填充list
private List<ComplexHeadData> data04(){
ArrayList<ComplexHeadData> lists = new ArrayList<>();
for (int i = 0; i < 10; i++) {
ComplexHeadData data = new ComplexHeadData();
data.setString("tiger"+(i+1));
// 比较有意思的其会自动格式化成yyyy-MM-dd HH:mm:ss
data.setDate(new Date());
data.setDoubleData(1.8);
lists.add(data);
}
return lists;
}
写入Excel
@Test
void contextLoads04(){
String fileName = path + "complexHeadWrite.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, ComplexHeadData.class).sheet("模板").doWrite(data04());
}
案例四
效果图
实体类
@Data
public class ConverterData {
@ExcelProperty("字符串标题")
private String string;
/**
* 我想写到excel 用年月日的格式
*/
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty("日期标题")
private Date date;
/**
* 我想写到excel 用百分比表示
*/
@NumberFormat("#.##%")
@ExcelProperty(value = "数字标题")
private Double doubleData;
}
填充list
private List<ConverterData> data03(){
ArrayList<ConverterData > lists = new ArrayList<>();
for (int i = 0; i < 10; i++) {
ConverterData data = new ConverterData ();
data.setString("tiger"+(i+1));
// 比较有意思的其会自动格式化成yyyy-MM-dd HH:mm:ss
data.setDate(new Date());
data.setDoubleData(0.8);
lists.add(data);
}
return lists;
}
写入Excel
@Test
void contextLoads03(){
String fileName = path + "converterWrite.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, ConverterData.class).sheet("模板").doWrite(data03());
}
3、读Excel
在easyExcel中,读取数据的步骤有些复杂,但是很高效,它是将数据的读取与持久化写在了一起,接下来我们就以读取如下的Excel的数据为例
实体类
对于读取的每一都是一个对象,索引我们需要一个与Excel中的行相对应的对象
@Data
public class DemoData {
private String string;
private Date date;
private Double doubleData;
}
监听器
easyExcel是通过监控 EasyExcel.read(…) 实现读读操作和持久化的
public class DemoDataListener extends AnalysisEventListener<DemoData> {
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 3000;
/**
* 缓存的数据(使用List做缓存)
*/
private List<DemoData> list = new ArrayList<>(BATCH_COUNT);
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用持久化这个对象没用。
*/
private DemoDAO demoDAO;
public DemoDataListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoDAO = new DemoDAO();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来(☆☆☆)
*/
public DemoDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
/**
* 这个每一条数据解析都会来调用
*/
@Override
public void invoke(DemoData data, AnalysisContext context) {
// 打印读取的数据
System.out.println(JSON.toJSONString(data));
// 将读取的数据添加到list(缓存)中
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库(持久化),防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
// 对应的是DemoDAO中的save()
saveData();
// 存储完成清理 list(所谓的清理list就是分配一个空的list将原来的list进行覆盖)
list = new ArrayList<>(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @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("存储数据库成功!");
}
}
持久化
如果你需要将读取的数据进行持久化,那么你需要在下面的类中调用自己的持久化业务
/**
* 假设这个是你的DAO存储。当然还要这个类让spring管理,当然你不用需要存储,也不需要这个类。
**/
public class DemoDAO {
public void save(List<DemoData> list) {
// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
}
}
读Excel
// 最简单的读
@Test
public void simpleRead() {
String fileName = path+"simpleWrite.xlsx";
// 这里需要指定用哪一个class去读,然后默认读第一个sheet(你也可以设置参数,指定读取哪一个sheet),文件流会自动关闭
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
提示:其他的一些高阶的Excel操作你可以查看 easyexcel文档:https://www.yuque.com/easyexcel/doc/read
✈ ❀ 希望平凡の我,可以给你不凡の体验 ☂ ✿ ,白嫖有罪 ☠ ,记得关注哦 ❥(^_-)