1、前言
阿里的easyexcel性能比poi是要好很多，不会出现oom的问题。但是官方文档的说明太少，本人工作中遇到类似跨行合并导出的这种业务，花了一点时间研究出来，现整理在博客中。
2、导出
一、maven引用
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.0</version>
</dependency>
二、集成工具类
public class EasyExcelUtil {
/**
* 动态列
*/
public static void writeExcel(HttpServletResponse response, String fileName,String sheetName, Map<String, ?> keyMap, List<?> data,String ...defaultValue) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName.replaceAll("\\+", "_"), "UTF-8"));
writeExcel(response.getOutputStream(), fileName, sheetName, keyMap, data, defaultValue);
}
public static void writeExcel(HttpServletResponse response, String fileName,String sheetName, Map<String, ?> keyMap, List<?> data,List<WriteHandler> writeHandler,String ...defaultValue) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName.replaceAll("\\+", "_"), "UTF-8"));
writeExcel(response.getOutputStream(), fileName, sheetName, keyMap, data,writeHandler,defaultValue);
}
/**
* 动态列
*/
public static void writeExcel(OutputStream outputStream,
String fileName,
String sheetName,
Map<String, ?> keyMap,
List<?> data,
String ...defaultValue) throws Exception {
writeExcel(outputStream, fileName, sheetName, keyMap, data, null, defaultValue);
}
/**
* 动态列
*/
public static void writeExcel(OutputStream outputStream,
String fileName,
String sheetName,
Map<String, ?> keyMap,
List<?> data,
List<WriteHandler> writeHandler,
String ...defaultValue) throws Exception {
if(!(keyMap instanceof LinkedHashMap)){
throw new IllegalArgumentException("key map should be LinkedHashMap");
}
List<List<String>> header = new ArrayList<>();
for (Object key : keyMap.values()) {
if (Collection.class.isAssignableFrom(key.getClass())) {
List<String> heads = new ArrayList<>();
for (Object o : (Collection) key) {
heads.add(o.toString());
}
header.add(heads);
} else {
header.add(Lists.newArrayList(key.toString()));
}
}
List<List<Object>> listData = new ArrayList<>();
try {
Class clazz = null;
Field[] fields = null;
for (Object datum : data) {
if (clazz == null) {
clazz = datum.getClass();
fields = clazz.getDeclaredFields();
}
List<Object> rowData = new ArrayList<>();
if (Map.class.isAssignableFrom(clazz)) {
for (String field : keyMap.keySet()) {
Object e = ((Map) datum).get(field);
if ((e == null || org.apache.commons.lang3.StringUtils.isBlank(e.toString())) && defaultValue != null) {
e = defaultValue[0];
}
rowData.add(e);
}
} else {
for (String key : keyMap.keySet()) {
for (Field field : fields) {
if (key.equals(field.getName())) {
field.setAccessible(true);
Object value = ReflectionUtils.getField(field, datum);
if (null == value) {
String mtitle = "get";
if (field.getType() == Boolean.class) {
mtitle = "is";
}
String subMethod = field.getName().substring(0, 1).toUpperCase().concat(field.getName().substring(1));
Method method = clazz.getDeclaredMethod(mtitle.concat(subMethod));
method = null == method ? clazz.getDeclaredMethod("get".concat(subMethod)) : method;
if (null != method) {
value = method.invoke(datum);
}
}
if (StringUtils.isEmpty(value) && null != defaultValue && 0 < defaultValue.length) {
value = defaultValue[0];
}
rowData.add(value);
}
}
}
}
listData.add(rowData);
}
}catch (Exception e){
throw e;
}
ExcelWriterSheetBuilder sheet = EasyExcel.write(outputStream).head(header).sheet(sheetName);
if (writeHandler != null) {
for (WriteHandler handler : writeHandler) {
/* if (handler instanceof MergeWriteMapHandler) {
((MergeWriteMapHandler)handler).setData(listData);
}*/
sheet.registerWriteHandler(handler);
}
}
sheet.table().head(List.class).needHead(Boolean.FALSE).doWrite(listData);
}
/**
* 设置excel动态标头
* @param chooseField 表头name list
* @param clazz easyexcel导出用的bean，即带有com.alibaba.excel.annotation.ExcelProperty注解的bean
*/
public static Map<String, String> buildHeadMap(List<String> chooseField, Class clazz) {
Map<String, String> ret = new LinkedHashMap<>();
boolean allFlag = (chooseField == null || chooseField.size() <= 0);
for (Field declaredField : clazz.getDeclaredFields()) {
if (!allFlag && !chooseField.contains(declaredField.getName())) {
continue;
}
if (declaredField.isAnnotationPresent(ExcelProperty.class)) {
String[] values = declaredField.getAnnotation(ExcelProperty.class).value();
String key = declaredField.getName();
//暂时不支持多映射
String value = values.length <= 0 ? key : values[0];
ret.put(key, value);
}
}
return ret;
}
/**
* 设置excel多级动态标头
* 【对导出别名的支持】添加fieldName的alias，如果有多个相同别名/原名，则都会被导出
* 【默认导出字段】：如果ExcelPropertyAliasName的value使用默认值，那么不管前端传什么来，都给你导出来
* @param chooseField 表头name list
* @param clazz easyexcel导出用的bean，即带有com.alibaba.excel.annotation.ExcelProperty注解的bean
*/
public static Map<String, List<String>> buildMultiHeadMap(List<String> chooseField, Class clazz) {
Map<String, List<String>> ret = new LinkedHashMap<>();
boolean allFlag = (chooseField == null || chooseField.size() <= 0);
for (Field declaredField : clazz.getDeclaredFields()) {
List<String> aliasNameList = declaredField.isAnnotationPresent(ExcelPropertyAliasName.class) ?
Lists.newArrayList(declaredField.getAnnotation(ExcelPropertyAliasName.class).value()) :
new ArrayList<>();
//removeAll 判断是否有差集，有则说明可以remove，无则说明不可以remove
if (!allFlag && !aliasNameList.contains("true_ExcelPropertyAliasName") && !chooseField.contains(declaredField.getName()) && !aliasNameList.removeAll(chooseField)) {
continue;
}
//验证通过，添加至表头字段
if (declaredField.isAnnotationPresent(ExcelProperty.class)) {
String[] values = declaredField.getAnnotation(ExcelProperty.class).value();
String key = declaredField.getName();
List<String> heads = new ArrayList<>(Arrays.asList(values));
ret.put(key, heads);
}
}
return ret;
}
/**
* 将聚合的原始对象（即一个对象有多个list，并且这多个list参与导出，而且按照一定条件merge）
* 例如一个订单bean RetailDTO，包含productList、paymentList。其中retai.orderId是merge依赖
* 结果将是：retail-product-payment的展开。
* 注意：请保证bean中没有相同的fieldName要copy到retList中。或者这些相同的fieldName的值是相同的。
* @param aggregationObjList 原始聚合对象.
* @param retClazz easyexcel data
*/
public static <T> List<T> buildExcelData(List<?> aggregationObjList, Class<T> retClazz) throws IllegalAccessException, InstantiationException {
List<T> retList = new ArrayList<>();
if (CollectionUtils.isEmpty(aggregationObjList)) {
return retList;
}
Class<?> aClass = aggregationObjList.get(0).getClass();
//寻找聚合field
List<Field> aggregationFieldList = new ArrayList<>();
//寻找普通object
List<Field> objectFieldList = new ArrayList<>();
for (Field field : aClass.getDeclaredFields()) {
//取出所有list-field集合，作为聚合域
if (List.class.isAssignableFrom(field.getType())) {
field.setAccessible(true);
aggregationFieldList.add(field);
}else {
field.setAccessible(true);
objectFieldList.add(field);
}
}
Field indexField = null;
//序号field判断
List<String> indexNames = Arrays.asList("serial", "index");
for (Field declaredField : retClazz.getDeclaredFields()) {
if (indexNames.contains(declaredField.getName())) {
indexField = declaredField;
indexField.setAccessible(true);
break;
}
}
//遍历聚合对象，并平铺至retClass对应的对象中去
for (int j = 0; j < aggregationObjList.size(); j++) {
Object o = aggregationObjList.get(j);
//取出聚合项，并计算扩展之后的totalNum
int totalNum = 1;
List<List> aggregationFieldObjList = new ArrayList<>();
//取出o中所有的list对象
for (Field field : aggregationFieldList) {
Object aggregationFieldObj = field.get(o);
if (aggregationFieldObj != null && List.class.isAssignableFrom(aggregationFieldObj.getClass())) {
List afo = (List) aggregationFieldObj;
if (CollectionUtils.isEmpty(afo)) {
continue;
}
aggregationFieldObjList.add(afo);
totalNum *= afo.size();
}
}
//取出o中的object对象
List<Object> ObjectFieldObjList = new ArrayList<>();
for (Field field : objectFieldList) {
Object obj = field.get(o);
if (obj != null) {
ObjectFieldObjList.add(obj);
}
}
//去除重复列用的set
Set<String> duplicateIndexSet = new HashSet<>();
//开始扩展
for (int i = 0; i < totalNum; i++) {
T retObj = retClazz.newInstance();
BeanUtils.copyProperties(o, retObj);
StringBuilder duplicateIndex = new StringBuilder();
for (List list : aggregationFieldObjList) {
Object obj = list.get((i * list.size()) / totalNum);
BeanUtils.copyProperties(obj, retObj);
duplicateIndex.append((i * list.size()) / totalNum);
}
//如果某个排列已经存在于list中，则跳过。
String duplicateIndexString = duplicateIndex.toString();
if (duplicateIndexSet.contains(duplicateIndexString)) {
continue;
}
duplicateIndexSet.add(duplicateIndexString);
for (Object obj : ObjectFieldObjList) {
BeanUtils.copyProperties(obj, retObj);
}
//设置序号
if (indexField != null) {
indexField.set(retObj,j+1);
}
retList.add(retObj);
}
}
return retList;
}
/**
* 读取Excel中文件
* @param inputStream 文件流，用完请自己关闭
* @param clazz 接收类，row到bean的映射类
* @param listSize 最大读取行数，超过listSize的将被丢弃。
*/
public static <T> List<T> readEasyExcelData(InputStream inputStream, Class<T> clazz, int listSize) {
List<T> ret = new ArrayList<>();
//本地读取文件 并验证
SyncReadListener syncReadListener = new SyncReadListener(){
@Override
public void invoke(Object object, AnalysisContext context) {
if (super.getList().size() > listSize) {
//超过listSize行抛弃，节约内存。==》通过异常终止，会导致easyexcel抛出新的异常，不方便捕捉。所以只能通过类似自旋等待结束。
return;
}
//判断是否读取的是空行
boolean addFlag = false;
Class<?> aClass = object.getClass();
try {
for (Field declaredField : aClass.getDeclaredFields()) {
declaredField.setAccessible(true);
Object o = declaredField.get(object);
if (o != null) {
addFlag = true;
//去除前后空格
if (o instanceof String) {
declaredField.set(object, o.toString().trim());
}
}
}
} catch (IllegalAccessException e) {
throw new IllegalArgumentException(e);
}
if (addFlag) {
super.invoke(object, context);
}
}
};
ExcelReaderSheetBuilder sheet = EasyExcel.read(inputStream, clazz,syncReadListener).sheet();
sheet.doRead();
for (Object o : syncReadListener.getList()) {
if (clazz.isInstance(o)) {
ret.add(clazz.cast(o));
}
}
return ret;
}
public static Map<String,String> getHeadMap( List<Map<String,String>> exportHead) {
if (null != exportHead && 0< exportHead.size()) {
Map<String,String> headMap= Maps.newLinkedHashMap();
for (Map<String,String> h : exportHead) {
headMap.putAll(h);
}
return headMap;
}
return null;
}
public static List<String> filterMergeColumn(Map<String,String> headMap,List<String> columnList) {
for (Iterator<String> it = columnList.iterator();it.hasNext();) {
String column = it.next();
if (!headMap.containsKey(column)) {
it.remove();
}
}
return columnList;
}
/**
* 转换为excel专用的bean
*/
public static <T> List<T> turnToInstallmentOrderDto(List<?> from, Class<T> clazz) throws IllegalAccessException, InstantiationException {
List<T> ret = new ArrayList<>();
for (Object o : from) {
T t = clazz.newInstance();
BeanUtils.copyProperties(o, t);
ret.add(t);
}
return ret;
}
/**
* @param data excel对应rows的data
* @param clazz easyexcel对应的class模板
* @param fileName 文件名称
* @param sheetName sheet名称
* @param titleList clazz对应的fieldName
*/
public static <T> void exportExcel( HttpServletResponse response,List<T> data, Class<T> clazz, String fileName,String sheetName, List<String> titleList) throws Exception {
Map<String, List<String>> headMap = EasyExcelUtil.buildMultiHeadMap(titleList, clazz);
MergeWriteHandler mergeWriteHandler = new MergeWriteHandler();
mergeWriteHandler.setClazz(clazz);
mergeWriteHandler.setData(data);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName.replaceAll("\\+", "_")+".xlsx", "UTF-8"));
EasyExcelUtil.writeExcel(response.getOutputStream(), fileName, sheetName, headMap, data, Lists.newArrayList(mergeWriteHandler), "-");
}
}
三、跨行合并的相关代码
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.google.common.collect.Lists;
import lombok.Data;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import java.lang.reflect.Field;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;
/**
* 说明：计算excel单元格竖向合并<br>
* 1、没有@ExcelMergeProperty注解的，不参与合并。<br>
* 2、List.data是sheet中的row数据。如果不能做到data和sheet中row一致，将会产生错误。<br>
* 3、合并的前提，是data中的数据是有序的，无序的数据，将会造成合并不全。即需要合并的row，放在一起。<br>
* 4、clazz是你导入sheet的那个class，不传则默认为data对应的class<br>
* 5、当然，也支持List<Map>形式的data<br>
* 6、另外提供 EasyExcelUtil#buildExcelData，将聚合的bean展开为可以merge的data。
* 7、data需要遍历多次，有几种merge就要遍历几次，所以存在性能优化空间。
*
* @see EasyExcelUtil#buildExcelData(List, Class)
*/
@Data
public class MergeWriteHandler implements SheetWriteHandler {
//excel data 与sheet一毛一样
private List data;
//excel class 判断merge依赖，没有则用data中元素对应的class
private Class clazz;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (CollectionUtils.isEmpty(data)) {
return;
}
clazz = clazz == null ? data.get(0).getClass() : clazz;
//找到所有的依赖Map<String, Set<String>> key:index-fieldName value:依赖的index-fieldName
Map<String, Set<String>> mergeMap = this.getMergeMap();
//fieldName到title index的映射
Map<String, Integer> titleFieldNameMap = getTitleFieldNameMap(writeSheetHolder.getHead());
int headRowNumber = writeSheetHolder.getExcelWriteHeadProperty().getHeadRowNumber();
//merge
for (Map.Entry<String, Set<String>> stringSetEntry : mergeMap.entrySet()) {
//判断被merge的单元格，是否在excel显示，不显示则不能merge
if (titleFieldNameMap.get(stringSetEntry.getKey()) == null) {
continue;
}
//有可能easyexcel会异步刷新数据到excel ==》然后报错
String tmpIndexKey = null;
int stRowIndex = -1;
int edRowIndex = -1;
for (int i = 0; i < data.size(); i++) {
Object o = data.get(i);
String relyIndexKey = null;
try {
relyIndexKey = getRelyIndexKey(o, stringSetEntry.getValue());
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace();
}
if (tmpIndexKey == null) {
tmpIndexKey = relyIndexKey;
stRowIndex = i + headRowNumber;
} else if (tmpIndexKey.equals(relyIndexKey)) {
edRowIndex = i + headRowNumber;
} else {
//create merge
if (stRowIndex < edRowIndex) {
//映射到sheet.row.cell.index
CellRangeAddress cellAddresses = new CellRangeAddress(
stRowIndex, edRowIndex,
titleFieldNameMap.get(stringSetEntry.getKey()),
titleFieldNameMap.get(stringSetEntry.getKey())
);
//每次根据merge依赖字段分组，遍历每组，merge每组
writeSheetHolder.getSheet().addMergedRegionUnsafe(cellAddresses);
}
//init merge message
stRowIndex = edRowIndex = i + headRowNumber;
tmpIndexKey = relyIndexKey;
}
}
//最后一行没有合并的bug
if (stRowIndex < edRowIndex) {
//映射到sheet.row.cell.index
CellRangeAddress cellAddresses = new CellRangeAddress(
stRowIndex, edRowIndex,
titleFieldNameMap.get(stringSetEntry.getKey()),
titleFieldNameMap.get(stringSetEntry.getKey())
);
//每次根据merge依赖字段分组，遍历每组，merge每组
writeSheetHolder.getSheet().addMergedRegionUnsafe(cellAddresses);
}
}
}
private Map<String, Integer> getTitleFieldNameMap(List<List<String>> head) {
//fieldName到title index的映射
Map<String, Integer> titleFieldNameMap = new HashMap<>();
for (Field declaredField : clazz.getDeclaredFields()) {
List<String> fieldTitleName = this.getFieldTitleName(declaredField);
for (int i = 0; i < head.size(); i++) {
List<String> headName = head.get(i);
if (fieldTitleName.size() == headName.size() && fieldTitleName.containsAll(headName)) {
titleFieldNameMap.put(declaredField.getName(), i);
}
}
}
return titleFieldNameMap;
}
private List<String> getFieldTitleName(Field declaredField) {
List<String> titleName;
if (declaredField.isAnnotationPresent(ExcelProperty.class)) {
String[] values = declaredField.getAnnotation(ExcelProperty.class).value();
titleName = Lists.newArrayList(values);
} else {
titleName = Lists.newArrayList(declaredField.getName());
}
return titleName;
}
private Map<String, Set<String>> getMergeMap() {
//找到所有的依赖Map<Integer,int[]> key:index-fieldName value:依赖的index-fieldName
Map<String, Set<String>> mergeMap = new HashMap<>();
Map<String, Field> allFieldMap = Lists
.newArrayList(clazz.getDeclaredFields())
.stream()
.collect(Collectors.toMap(Field::getName, Function.identity(), (v1, v2) -> v1));
for (Field declaredField : clazz.getDeclaredFields()) {
if (declaredField.isAnnotationPresent(ExcelMergeProperty.class)) {
//定位到merge的index
ExcelMergeProperty excelMergeProperty = declaredField.getAnnotation(ExcelMergeProperty.class);
Set<String> relyFields = new HashSet<>();
for (String relyField : excelMergeProperty.mergeRely()) {
if (!allFieldMap.keySet().contains(relyField)) {
throw new IllegalArgumentException("bad ExcelMergeProperty relyField of" + declaredField.getName() + ":" + relyField);
}
relyFields.add(relyField);
}
relyFields.add(declaredField.getName());
mergeMap.put(declaredField.getName(), relyFields);
}
}
return mergeMap;
}
private String getRelyIndexKey(Object data, Set<String> fieldNameSet) throws NoSuchFieldException, IllegalAccessException {
StringBuilder relyIndex = new StringBuilder();
if (Map.class.isAssignableFrom(data.getClass())) {
for (String fieldName : fieldNameSet) {
relyIndex.append(((Map) data).get(fieldName));
}
} else {
for (String fieldName : fieldNameSet) {
Field declaredField = data.getClass().getDeclaredField(fieldName);
declaredField.setAccessible(true);
relyIndex.append(declaredField.get(data));
}
}
return relyIndex.toString();
}
}
四、使用跨行合并的自定义注解
import java.lang.annotation.*;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelMergeProperty {
/**
* 合并单元格依赖关系,比如第二列合并是基于第一列serial 则{"serial"}就可以了
* index 从0开始
*/
String[] mergeRely() default {""};
}
五、导出可以使用别名
/**
* 【注解目标】：为了和前端传过来的动态的导出字段一一对应，现在可以通过为导出字段设置别名，来映射前端传来的字段。
* 【使用方法】：ExcelPropertyAliasName()，如上，表示不管前端传啥，一定给导出来。比如“序号”等字段；
* 如果有重复的别名/原名，那么都会被导出来。
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelPropertyAliasName {
String[] value() default "true_ExcelPropertyAliasName";
}
3、使用
一、示例：
try {
//head 动态列，你需要导出的列，与导出的bean中属性一致，如不一致，导出的bean之中需要加别名注解
String head = "employeeId,employeeName,year,month,sumSalary,rewardSalary,punishSalary,rainSalary,grantSalary,surplusSalary,modifierName,projectName,employeeJob,reportingTimeStr,day,rainStatus,nowSumSalary,nowRewardSalary,nowPunishSalary";
List<String> headList = new ArrayList<>(Arrays.asList(head.split(",")));
String fileName = "员工薪资信息"+ DateUtil.getNowStr(DateUtil.DEFAULT_DATE2_FORMAT)+ UUID.randomUUID().toString().substring(0,4);
String sheetName = "薪资信息";
//调用工具类中的导出方方法，exportList需要导出的数据集合 ，EmployeeSalaryExportResult导出对应的bean
EasyExcelUtil.exportExcel(response,exportList, EmployeeSalaryExportResult.class,fileName,sheetName,headList);
}catch (Exception e){
logger.error("薪资信息导出失败:{}",e.getMessage());
}
二、导出的bean中的注解使用
@Data
public class EmployeeSalaryExportResult {
//@ExcelMergeProperty(mergeRely={"id"}) 跨行合并注解 ，其中的id就是你每条记录的id主键，以它为参考合并 所以使用合并注解时，需要找到合并的参照列
// @ExcelProperty("员工编号") 需要导出的列加上这个注解
@ExcelMergeProperty(mergeRely={"id"})
private Long id;
@ExcelProperty("员工编号")
@ExcelMergeProperty(mergeRely={"id"})
private Long employeeId;
@ExcelProperty("员工名称")
@ExcelMergeProperty(mergeRely={"id"})
private String employeeName;
@ExcelProperty("当前年度")
@ExcelMergeProperty(mergeRely={"id"})
private Integer year;
@ExcelProperty("当前月份")
@ExcelMergeProperty(mergeRely={"id"})
private Integer month;
@ExcelProperty("当月总薪资(元)")
@ExcelMergeProperty(mergeRely={"id"})
private BigDecimal sumSalary;
}
3965
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
