不定时更新!!!
目录
自适应行宽:
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 254) {
columnWidth = 254;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
/**
* 计算长度
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
WriteCellData<?> cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
// 换行符(数据需要提前解析好)
int index = cellData.getStringValue().indexOf("\n");
return index != -1 ?
cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
使用方法:
EasyExcel.registerWriteHandler(new CustomCellWriteWeightConfig())
自适应行高:
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import java.util.Iterator;
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
/**
* 默认高度
*/
private static final Integer DEFAULT_HEIGHT = 300;
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
}
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
Iterator<Cell> cellIterator = row.cellIterator();
if (!cellIterator.hasNext()) {
return;
}
// 默认为 1行高度
Integer maxHeight = 1;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case STRING:
if (cell.getStringCellValue().indexOf("\n") != -1) {
int length = cell.getStringCellValue().split("\n").length;
maxHeight = Math.max(maxHeight, length);
}
break;
default:
break;
}
}
row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
}
}
使用方法:
EasyExcel.registerWriteHandler(new CustomCellWriteHeightConfig())
通用枚举转换器:
指定枚举自定义类的注解:
import java.lang.annotation.*;
/**
* 指定枚举自定义类的注解
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface EasyExcel {
/**
* 控件类型
* @return
*/
Class<? extends Enum> type();
}
通用枚举转换器:
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.jingyun.platform.tpm.global.annotations.EasyExcel;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
/**
* 通用枚举转换器
*/
public class EConverter implements Converter<Integer> {
@Override
public Class supportJavaTypeKey() {
//指定转换器接收参数类型
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
//指定返回的参数类型
return CellDataTypeEnum.STRING;
}
@Override
public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
try {
//value:状态码 contentProperty:字段属性 globalConfiguration:全局配置
//获取字段属性中的注解
Field field = contentProperty.getField();
//获取该字段所属枚举
EasyExcel easyExcel = field.getAnnotation(EasyExcel.class);
//获取注解中的枚举信息
Class<? extends Enum> type = easyExcel.type();
//获取枚举类的方法名 “codeOf”就是自己编写的函数,Integer.class 指定入参类型
Method codeOf = type.getMethod("codeOf", Integer.class);
//反射执行方法,此方法得到的是一个枚举实例(具体得到什么,结合自身项目)
Object invoke = codeOf.invoke(type, value);
//枚举实例调用getName方法,得到name的值
Method getName = invoke.getClass().getMethod("getName");
String name = String.valueOf(getName.invoke(invoke));
return new WriteCellData<>(name);
} catch (Exception e) {
// 如果出现异常,返回原始的Integer值的字符串形式
return new WriteCellData<>(String.valueOf(value));
}
}
}
枚举类:
import lombok.Getter;
import java.util.HashMap;
import java.util.Map;
/**
* 计划周期单位-枚举转化
*/
@Getter
public enum OffOnType {
ONE(0, "否"),
TWO(1, "是"),
;
public static Map<Integer, OffOnType> MAPS = new HashMap<>();
static {
for (OffOnType e : values()) {
MAPS.put(e.getCode(), e);
}
}
private final Integer code;
private final String name;
OffOnType(Integer code, String name) {
this.code = code;
this.name = name;
}
public static OffOnType codeOf(Integer code) {
return MAPS.getOrDefault(code, null);
}
}
使用方法:
实体类@ExcelProperty中指定对应converter属性,实体类上额外添加注解@EasyExcel并指定对应枚举类
@ExcelProperty(value = "是否",converter = EConverter.class)
@EasyExcel(type = OffOnType.class)
如果实体类属性为Long类型或其他,可以复制枚举转换器EConverter类并将里面的Integer改为Long,对应新增的枚举类也改为Long。
注解实现自定义单元格合并:
注解类:
import java.lang.annotation.*;
/**
* 自定义注解,用于判断是否需要合并以及合并的主键
* 标记哪些属性需要合并,哪个是主键
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CollectCustomMerge {
/**
* 是否需要合并单元格
*/
boolean needMerge() default false;
/**
* 是否是主键,即该字段相同的行合并
*/
boolean isPk() default false;
}
策略类CustomMergeStrategy:
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.jingyun.common.annotation.CollectCustomMerge;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* @Title: CustomMergeStrategy
* @Description: 自定义单元格合并策略类CustomMergeStrategy,当Excel中两列主键相同时,合并被标记需要合并的列
*/
public class CustomMergeStrategy implements RowWriteHandler {
/**
* 主键下标
*/
private Integer pkIndex;
/**
* 需要合并的列的下标集合
*/
private List<Integer> needMergeColumnIndex = new ArrayList<>();
private int mergeRowNum = 0;
/**
* DTO数据类型
*/
private Class<?> elementType;
public CustomMergeStrategy(Class<?> elementType) {
this.elementType = elementType;
}
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
// 如果是标题,则直接返回
if (isHead) {
return;
}
// 获取当前sheet
Sheet sheet = writeSheetHolder.getSheet();
// 获取标题行
Row titleRow = sheet.getRow(0);
if (null == pkIndex) {
this.lazyInit(writeSheetHolder);
}
// 判断是否需要和上一行进行合并
// 不能和标题合并,只能数据行之间合并
if (row.getRowNum() <= 1) {
return;
}
// 获取上一行数据
Row lastRow = sheet.getRow(row.getRowNum() - 1);
String lastName = new DataFormatter().formatCellValue(lastRow.getCell(pkIndex));
String rowName = new DataFormatter().formatCellValue(row.getCell(pkIndex));
// 将本行和上一行是同一类型的数据(通过主键字段进行判断),则需要合并
// 注意需要提前将list按主键排序,如果中间乱入别的主键,则后面的不会合并
if (lastName.equalsIgnoreCase(rowName)) {
//有要合并的则加1
mergeRowNum ++;
}else {
if(mergeRowNum > 0){
//合并所有连续相同的行
for (Integer needMerIndex : needMergeColumnIndex) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(lastRow.getRowNum() - mergeRowNum, lastRow.getRowNum(), needMerIndex, needMerIndex);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
mergeRowNum = 0;
}
}
}
/**
* 初始化主键下标和需要合并字段的下标
*/
private void lazyInit(WriteSheetHolder writeSheetHolder) {
// 获取当前sheet
Sheet sheet = writeSheetHolder.getSheet();
// 获取标题行
Row titleRow = sheet.getRow(0);
// 获取DTO的类型
Class<?> eleType = this.elementType;
// 获取DTO所有的属性
Field[] fields = eleType.getDeclaredFields();
// 遍历所有的字段,因为是基于DTO的字段来构建excel,所以字段数 >= excel的列数
for (Field theField : fields) {
// 获取@ExcelProperty注解,用于获取该字段对应在excel中的列的下标
ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
// 为空,则表示该字段不需要导入到excel,直接处理下一个字段
if (null == easyExcelAnno) {
continue;
}
// 获取自定义的注解,用于合并单元格
CollectCustomMerge collectCustomMerge = theField.getAnnotation(CollectCustomMerge.class);
// 没有@CustomMerge注解的默认不合并
if (null == collectCustomMerge) {
continue;
}
for (int index = 0; index < fields.length; index++) {
Cell theCell = titleRow.getCell(index);
// 当配置为不需要导出时,返回的为null,这里作一下判断,防止NPE
if (null == theCell) {
continue;
}
// 将字段和excel的表头匹配上
if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
if (collectCustomMerge.isPk()) {
pkIndex = index;
}
if (collectCustomMerge.needMerge()) {
needMergeColumnIndex.add(index);
}
}
}
}
// 没有指定主键,则异常
if (null == this.pkIndex) {
throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
}
}
}
使用方法:
注意:查询出来的为平铺数据!
在实体类中需要合并的属性上加:
@CollectCustomMerge(needMerge = true)
根据哪个字段相同则合并,比如id相同则合并,那么在属性上加:
@CollectCustomMerge(needMerge = true, isPk = true)
子项的数据,也就是不需要合并的,一对多中的多,则不需要添加注解,但是需要修改@ExcelProperty注解中的属性:
value = {"父标题" , "子标题1"}
value = {"父标题" , "子标题2"}
value = {"父标题" , "子标题3"}
如:
班级与学生关系
@ExcelProperty(value = "班级编号")
@CollectCustomMerge(needMerge = true, isPk = true)
private String classCode;
@ExcelProperty(value = "班级名")
@CollectCustomMerge(needMerge = true)
private String className;
@ExcelProperty(value = {"学生", "姓名"})
private String name;
@ExcelProperty(value = {"学生", "性别"})
private String gender;
@ExcelProperty(value = {"学生", "年龄"})
private Integer age;
最后加上
EasyExcel.registerWriteHandler(new CustomMergeStrategy(你的导出实体类.class))
Long类型太长导致导出变成科学计数法
一、对应的字段加注解
@NumberFormat(value = "#")
private Long id;
@NumberFormat(value = "0.00")
private BigDecimal money;
或
二、设置转换器
EasyExcel.registerConverter(new LongStringConverter())