前言
现在市面上已经有很多优秀的excel处理工具组件,比如出自阿里的 EasyExcel,已经很好用了,我们为什么还要自己动手去实现一个呢?
答:因为每个项目需求不一样,万一项目里有一些奇形怪状的需求,现有组件无法很好的满足,是不是需要自己去定制?有现成的工具类固然是一件好事,但是当现成的工具类无法满足当前需求的时候,如果我们能参考这些工具的实现原理,自己去动手撸一个差不多功能的组件,并且加上自己想要的功能,还运用到实际项目中去检验,是不是一件很有成就感的事?
背景
本文准备实现的这个excel读取导出组件是2018年还在上家公司的时候写的,那时做的是一个物联网项目,经常需要做一些导入、导出excel的操作,而且导入、导出的需求中还有一些本来很正常,但是当前组件没有的功能,比如数据表中一个字段(card_type)存的是一个varchar
类型的枚举值如下:
导出的时候我们要导出对应的中文注释,其实这个也比较好弄,要么在查出数据集合的时候在sql语句中就处理好,要么就是查出原始数据后在代码中来转换。
假如现在能在导出工具中自动就处理了呢?
比如我们定义一个注解,注解上有一个字段可以定义要转换的映射关系(如下面TestBean
中@ExcelField
注解中的parseJson
信息),导入导出的时候工具类读取到这个字段定义的映射关系,在读取excel的时候帮我们把表格中的中文转义为英文,写出的时候能帮我们把英文转义成中文并且写到excel表格中,这样是不是很方便?
@ExcelSheet
@Data
public class TestBean {
@ExcelField(name = "卡类型", parseJson = "{'A':'语音卡', 'B':'流量卡', 'AA':'体验卡', 'BB':'流量短信卡','C':'流量池卡', 'D':'短信卡'}")
String cardType;
}
或者导出的时候要求根据不同的用户导出不同的字段,比如导出实体bean中定义了1~9
九个字段,要求A用户导出的时候只导出1,2,3,9
字段,B用户导出的时候只导出1,4,5,7,9
字段,如果这样的需求多了,我们在代码里手动去处理就显得很繁琐。
上面这个需求我故意没实现,就当一个习题吧,看有没有同学能搞定
下面我们就来看看怎么自己动手来优雅的搞定这些需求!
代码中主要应用到了
自定义注解
、反射
等基础功能,在处理大表的时候,引入了流式处理
,避免内存溢出问题,当然这个组件只是实现了最基础的导入导出功能,像单元格合并这些就没去实现,有兴趣的同学在搞清楚原理后可以自己动手去实现一下
下面先把代码贴出来,大家最好建个java项目手动跑一遍验证一下,项目结构如下:
一、引入相关依赖
核心依赖用到了poi(用到了其中的一些类)、流式处理组件xlsx-streamer,其它组件可以选择性依赖
<?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.yinchd</groupId>
<artifactId>excel</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<slf4j-version>1.7.7</slf4j-version>
<log4j-version>1.2.17</log4j-version>
<xlsx-streamer-version>2.1.0</xlsx-streamer-version>
<commons-lang3-version>3.5</commons-lang3-version>
<fastjson-version>1.2.53</fastjson-version>
<servlet-version>3.1.0</servlet-version>
<poi-version>4.0.1</poi-version>
</properties>
<dependencies>
<!-- 日志 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j-version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j-version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j-version}</version>
</dependency>
<!--流方式读取Excel组件-->
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>${xlsx-streamer-version}</version>
</dependency>
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi-version}</version>
</dependency>
<!-- 通用组件包 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>${commons-lang3-version}</version>
</dependency>
<!-- JavaEE -->
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-api</artifactId>
<version>7.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>${servlet-version}</version>
<scope>provided</scope>
</dependency>
<!-- JSON -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson-version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
<scope>provided</scope>
</dependency>
</dependencies>
</project>
二、定义@ExcelSheet
注解
该注解主要用于对应excel文件的sheet表单
,name
可以指定excel中sheet表单的名称
,如果指定了name
,在使用ExcelReader
读取excel的时候,可以定向去读此name的表单,如果不指定,则默认读取excel中第一个表单,headColor
主要用于ExcelWriter
导出的时候使用,可以指定导出来的标题行的颜色;
package com.yinchd.excel;
import org.apache.poi.hssf.util.HSSFColor;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel与实体之间的映射 </br>
* 添加注解的格式为 : </br>
* eg:@ExcelSheet(name="用户列表", headColor=HSSFColor.HSSFColorPredefined.LIGHT_GREEN)</br>
* name:Excel中sheet的名称,headColor:标题头的颜色
* @author yinchd
* @since 2018-02-13
*/
@Target({
java.lang.annotation.ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelSheet {
/**
* sheet表单名称
*/
String name() default "";
/**
* 统一的表头颜色
*/
HSSFColor.HSSFColorPredefined headColor() default HSSFColor.HSSFColorPredefined.LIGHT_GREEN;
}
excel文件中的sheet表单,表单名称对应name
属性
导出excel的标题颜色,标题颜色对应headColor
属性
三、定义@ExcelField
注解
该注解主要标识在字段属性上,用来设置各个字段的参数
package com.yinchd.excel;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 字段注解,用于实体属性与Excel值之间的映射关系 </br>
* eg:@ExcelField(name="名称", index=1, width=30*256, parseJson="{'1': '有效', '2': '无效')</br>
* 其中name为必填,其它可以选填
* @author yinchd
* @since 2018-02-13
*/
@Target({
java.lang.annotation.ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelField {
/**
* 导入时:name的值代表我们导入的excel文件中的列标题,在具体解析excel的过程中,会将实际读到的标题与ExcelField注解中name中定义值的作对比,据此判断导入文件是否合法;
* 导出时:name的值代表是实体字段对应的中文名称,比如有个字段叫‘hobby’,ExcelField注解中name的值是‘爱好’,则导出的excel文件中hobby列的表头为‘爱好’;
* eg:@ExcelField(name = "hobby")
* 默认忽略大小写
*/
String name();
/**
* 列宽,默认会自动根据内容适应宽度
* eg:@ExcelField(width = 30*256)
* 参见org.apache.poi.hssf.usermodel.HSSFSheet#setColumnWidth(int, int)中的参数定义说明
*/
int width() default 0;
/**
* 字段排序权重,用于对字段顺序顺序进行排序,不指定值的话默认按实体中定义的字段顺序排序
* 导入的时候,如果实体中定义的字段顺序和表格中的表头顺序不一致,可以通过指定sortWeight来调整顺序,默认按sortWeight的值从小到大来排序
* 导出的时候也一样,根据sortWeight来调整导出列的顺序
* 注意:有一种情况,有些字段指定了权重,有些没指定,这些没指定的权重值默认值为0,排序的时候会排到前面去,所以这点大家注意一下,
* 所以sortWeight的值要么都指定,要就都不指定,用默认排序就好
*/
int sortWeight() default 0;
/**
* 时间格式
* eg: "yyyy-MM-dd HH:mm:ss" , "yyyy-MM-dd"
*/
String dateFormat() default "yyyy-MM-dd HH:mm:ss";
/**
* 自定义转换参数,导出的时候比如字段值是枚举或者想转义成其它字符,这里可以定义一个json串,key为待转的字段,value为想转义出来值
* eg:convertJson="{'1': '有效', '2': '无效', '3': '正常'}"
*/
String parseJson() default "";
}
三、Excel读取工具:ExcelReader
基本原理是先用StreamingReader(流式引擎)将excel读成workbook,然后我们基于反射的原理来将数据映射成一个个实体
package com.yinchd.excel;
import com.monitorjbl.xlsx.StreamingReader;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;
import java.util.stream.Collectors;
/**
* ExcelReader,通过流式方法读取excel,避免读取大文件时出现内存溢出问题
* @author yinchd
* @since 2018/2/14 14:26
**/
@Slf4j
public class ExcelReader {
/**
* 数据格式化工具类
*/
private static final DataFormatter DF = new DataFormatter();
/**
* <p>
* 根据文件路径、数据起始值读取文件到集合 List<T>中
* eg: List<Person> personList = ExcelReader.getListByFilePath(filePath, Person.class); // 读全部行
* eg: List<Person> personList = ExcelReader.getListByFilePath(filePath, Person.class, 1, 100); // 1, 100 分别为行的下标
* </p>
* @param filePath 文件绝对路径 eg: e:/xxx/xxx.xlsx
* @param clazz 需要将表格内容读成的类型,如: Person.class
* @param dataStartOrEndIndex 数据起始行、数据结束行(非必填,注意,起始和结束行的值都是下标值,下标值是从0开始,如第一行的下标值为0)
* @param <T> 数据类型
* @return List<clazz>
*/
@SneakyThrows
public static <T> List<T> getListByFilePath(String filePath, Class<T> clazz, int... dataStartOrEndIndex) {
try (Workbook wb = getWorkbookByFile(new File(filePath))) {