Springboot功能模块之EasyExcel

一、EasyExcel简介

1.1 程序简介

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模式。在上层做了模型转换的封装，让使用者更加简单方便。

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节省内存著称。

easyExcel能大大减少内存占用的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

内存问题：POI = 100w先加载到内存 OOM。。再写入文件而 easyExcel是一行一行的完成

EasyExcel的GitHub地址:GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具

EasyExcel的官方文档:EasyExcel（文档已经迁移） · 语雀

1.2 Excel格式分析

xls是Microsoft Excel2007前excel的文件存储格式，实现原理是基于微软的ole db是微软com组件的一种实现，本质上也是一个微型数据库，由于微软的东西很多不开源，另外也已经被淘汰，了解它的细节意义不大，底层的编程都是基于微软的com组件去开发的。
xlsx是Microsoft Excel2007后excel的文件存储格式，实现是基于openXml和zip技术。这种存储简单，安全传输方便，同时处理数据也变的简单。
csv 我们可以理解为纯文本文件，可以被excel打开。他的格式非常简单，解析起来和解析文本文件一样。

二、快速入门

EasyExcel所需的依赖:

 <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.71</version></dependency>

我们在引入EasyExcel依赖之后,我们需要注意下面的问题,因为EasyExcel里面已经集成了很多的依赖,并且里面就包含了POI的依赖:

2.1写入Excel

首先我们需要创建一个实体类.用来映射到我们在Excel中将要填充的对象

java">@Data
public class DemoData {@ColumnWidth(20)@ExcelProperty("字符串标题")private String string;@ExcelProperty("日期标题")@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")private Date date;@ExcelProperty("数字标题")@NumberFormat("#.##")private Double doubleData;/*** 忽略这个字段*/@ExcelIgnoreprivate String ignore;
}

报错解决

https://ask.csdn.net/questions/7703338

属性注解

@ExcelProperty(""):用来标注Excel中字段的标题

@ExcelIgnore:用来表示该字段忽略,不用添加到Excel中

@DateTimeFormat 日期格式转换

@NumberFormat数值格式转换

样式注解

@ColumnWidth设置某一列单元格宽度，如20

@ContentFontStyle 、@HeadFontStyle设置某一单元格字体样式

java">//字体
String fontName() default "";
//字体大小(short) 10
short fontHeightInPoints() default -1;
//斜体
boolean italic() default false;
//删除线
boolean strikeout() default false;
//字体颜色,在如下类中查找
//org.apache.poi.ss.usermodel.Font.COLOR_NORMAL
//org.apache.poi.ss.usermodel.Font.COLOR_RED
//org.apache.poi.hssf.util.HSSFColor
//org.apache.poi.ss.usermodel.IndexedColors
short color() default -1;
short typeOffset() default -1;
//下划线
//Font#U_NONE
//Font#U_SINGLE
//Font#U_DOUBLE
//Font#U_SINGLE_ACCOUNTING
//Font#U_DOUBLE_ACCOUNTING
byte underline() default -1;
//字符集
//FontCharset
//Font#ANSI_CHARSET
//Font#DEFAULT_CHARSET
//Font#SYMBOL_CHARSET
int charset() default -1;
//加粗
boolean bold() default false;
//示例
@ContentFontStyle(fontName = "微软雅黑",fontHeightInPoints = 20,italic = true,strikeout = true,color = 10,bold = true)
private String name;

@ContentRowHeight 所有数据行的行高

java">short value() default -1;
@ContentRowHeight(40)
//示例
public class User {
}

@ContentStyle 、@HeadStyle 设置内容行单元格的样式

java">//poi的dataformat功能,参数见BuiltinFormats类
short dataFormat() default -1;
// 是否隐藏
boolean hidden() default false;
// 是否锁定
boolean locked() default false;
//quotePrefix
boolean quotePrefix() default false;
//水平居中
HorizontalAlignment horizontalAlignment() default HorizontalAlignment.GENERAL;
//包装
boolean wrapped() default false;
//垂直居中
VerticalAlignment verticalAlignment() default VerticalAlignment.CENTER;
//内容旋转角度,value 0-180
short rotation() default -1;
//边框
BorderStyle borderLeft() default BorderStyle.NONE;
BorderStyle borderRight() default BorderStyle.NONE;
BorderStyle borderTop() default BorderStyle.NONE;
BorderStyle borderBottom() default BorderStyle.NONE;
//边框颜色
short leftBorderColor() default -1;
short rightBorderColor() default -1;
short topBorderColor() default -1;
short bottomBorderColor() default -1;
//设置前景色填充类型 org.apache.poi.ss.usermodel.FillPatternType
//要修改Excel的背景色必须同时设置以下两个值
//@ContentStyle(fillForegroundColor = 10,fillPatternType = FillPatternType.SOLID_FOREGROUND)
FillPatternType fillPatternType() default FillPatternType.NO_FILL;
//前景色 org.apache.poi.ss.usermodel.IndexedColors
short fillForegroundColor() default -1;
//背景色(基本不用) org.apache.poi.ss.usermodel.IndexedColors
short fillBackgroundColor() default -1;
//控制单元格是否应自动调整内容大小，以适应文本过长的情况
boolean shrinkToFit() default false;

@HeadRowHeight 标题行的行高

java">short value() default -1;
@HeadRowHeight(40)
public class User {
}

这样我们的数据写入就完成了,运行代码之后我们就可以看到已经在我们的项目路径下生成了easyexcel文件了

简单读

java">    private static  String PATH = System.getProperty("user.dir") + java.io.File.separator + "files";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;}/*** 最简单的写* <p>1. 创建excel对应的实体对象 参照{@link DemoData}* <p>2. 直接写即可*/@Testpublic void simpleWrite() {// 写法1
//        String fileName = PATH+ java.io.File.separator+ "easyexcel.xlsx";
//        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
//        // 如果这里想使用03 则 传入excelType参数即可
//        EasyExcel.write(fileName, DemoData.class)
//                .sheet("模板")
//                .doWrite(data());// 写法2String   fileName = PATH+ java.io.File.separator+ "easyexcel.xlsx";// 这里 需要指定写用哪个class去写ExcelWriter excelWriter = null;try {excelWriter = EasyExcel.write(fileName, DemoData.class).build();WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();excelWriter.write(data(), writeSheet);} finally {// 千万别忘记finish 会帮忙关闭流if (excelWriter != null) {excelWriter.finish();}}}

写示例

2.2读取Excel

首先我们需要创建一个监听器:

java">// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);/*** 每隔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*/@Overridepublic void invoke(DemoData data, AnalysisContext context) {System.out.println(JSON.toJSONString(data));LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));list.add(data);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (list.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listlist.clear();}}/*** 所有数据解析完成了 都会来调用** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData();LOGGER.info("所有数据解析完成！");}/*** 加上存储数据库*/private void saveData() {LOGGER.info("{}条数据，开始存储数据库！", list.size());demoDAO.save(list);LOGGER.info("存储数据库成功！");}
}

之后我们需要根据自己的需要创建一个DAO功能其实就类似于我们的service层,可以在这里面定义我们后来可能加入的与数据库的相关操作的方法

java">/*** 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。**/
public class DemoDAO {public void save(List<DemoData> list) {// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入}
}

创建完成之后我们的功能基本就可以了,之后就可以进行测试了:

java">/*** 最简单的读* <p>1. 创建excel对应的实体对象 参照{@link DemoData}* <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}* <p>3. 直接读即可*/@Testpublic void simpleRead() {// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去// 写法1：String fileName = PATH+ "easyexcel.xlsx";// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();//        // 写法2：
//        String fileName = PATH+ "easyexcel.xlsx";
//        ExcelReader excelReader = null;
//        try {
//            excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
//            ReadSheet readSheet = EasyExcel.readSheet(0).build();
//            excelReader.read(readSheet);
//        } finally {
//            if (excelReader != null) {
//                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
//                excelReader.finish();
//            }
//        }}

具体

写案例

2.3 填充Excel模板

EasyExcel自2.1.1后提供了更强大的表格填充功能，提供一个模板文件，写一些模板参数，就可以快速生成

编写模板 -> 传入模板绝对路径 -> 渲染数据

（1）模板

将Excel实体类的属性用{.属性名}填入模板文件，样式也会被复制。注意：填充List时需要带个点，如果单个就不需要。如下：

姓名	年龄	其他
{.name}	{.age}	要想显示大括号可用反斜杠转译\ {

（2）API：填充列表

java">//API与写入Excel相同,多一项填充
public ExcelWriterBuilder withTemplate(InputStream templateInputStream) {}
public ExcelWriterBuilder withTemplate(File templateFile) {}
public ExcelWriterBuilder withTemplate(String pathName) {}
public void doFill(Object data) {}
public void doFill(Object data, FillConfig fillConfig) {}
//示例
EasyExcel.write(response.getOutputStream()).withTemplate(templateFilePath).sheet().doFill(list)