easyexcel 自定义导出格式_fajing_feiyue的博客-CSDN博客
前言
easyexcel 由于其比poi更为简单api和更高性能的情况下，easyexcel 但格式下载不符合要求。比如下载格式设置自定义下拉框，对字体进行设置，对部分列进行隐藏的个性化需求。
实践
一、引入maven依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
二、定义需要设置状态的注解
@Target({ElementType.FIELD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelStyle {
String fontName() default "宋体";
short fontHeightInPoints() default 12;
HorizontalAlignment horizontalAlignment() default HorizontalAlignment.CENTER;
VerticalAlignment verticalAlignment() default VerticalAlignment.CENTER;
boolean wrapText() default false;
boolean richText() default false;
boolean isRequire() default false;
}
三、定义需要封装对象
@Data
public class CommonCallBackData<T> {
private Font font;
private CellStyle cellStyle;
private List<T> dataList;
private Integer relativeRowIndex;
private Cell cell;
private Workbook workbook;
private Field field;
private ExcelStyle annotation;
private Boolean isHead;
private Boolean isRequire;
}
四、定义需要导出一下需要设置注解
public class BaseType {
/**
* 如果要是excel是否需要使用富文本
* @return 是否需要使用富文本
*/
public Boolean isGetRich(){
return false;
};
/**
* 设置单元格格式
* @return 是设置单元格格式
*/
public Boolean isGetCellStyle(){
return false;
};
}
五、设置隐藏列和富文本和必填参数
@Slf4j
public class TitleHandler<T> implements CellWriteHandler {
private Class<?> c;
private List<T> dataList;
private Consumer<CommonCallBackData<T>> fontConsumer;
private Consumer<CommonCallBackData<T>> cellStyleConsumer;
private Consumer<CommonCallBackData<T>> richTextConsumer;
private List<Integer> hiddenColumnIndexes;
public TitleHandler(Class<?> c, List<T> dataList, Consumer<CommonCallBackData<T>> fontConsumer,
Consumer<CommonCallBackData<T>> cellStyleConsumer,
Consumer<CommonCallBackData<T>> richTextConsumer, List<Integer> hiddenColumnIndexes) {
this.c = c;
this.fontConsumer = fontConsumer;
this.cellStyleConsumer = cellStyleConsumer;
this.richTextConsumer = richTextConsumer;
this.dataList = dataList;
this.hiddenColumnIndexes = hiddenColumnIndexes;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
if(isHead){
try {
Field declaredField = c.getDeclaredField(head.getFieldName());
ExcelStyle annotation = declaredField.getAnnotation(ExcelStyle.class);
if (annotation != null) {
Workbook wb = cell.getSheet().getWorkbook();
CellStyle cellStyle = wb.createCellStyle();
cell.setCellStyle(cellStyle);
Font font = wb.createFont();
CommonCallBackData<T> callBackData = create(font, cellStyle, cell.getRowIndex(), cell, wb, annotation, declaredField, isHead);
font.setFontName(annotation.fontName());
font.setFontHeightInPoints(annotation.fontHeightInPoints());
if (fontConsumer != null) {
fontConsumer.accept(callBackData);
}
cellStyle.setFont(font);
cellStyle.setAlignment(annotation.horizontalAlignment());
cellStyle.setVerticalAlignment(annotation.verticalAlignment());
cellStyle.setWrapText(annotation.wrapText());
boolean richText = annotation.richText();
if (richText && richTextConsumer != null) {
richTextConsumer.accept(callBackData);
}
if (cellStyleConsumer != null) {
cellStyleConsumer.accept(callBackData);
}
if (CollectionUtils.isNotEmpty(hiddenColumnIndexes)) {
for (Integer hiddenColumnIndex : hiddenColumnIndexes) {
writeSheetHolder.getSheet().setColumnHidden(hiddenColumnIndex, true);
}
}
}
} catch (NoSuchFieldException e) {
log.error("ExcelStyleAnnotationCellWriteHandler error:",e);
}
}
}
private CommonCallBackData<T> create(Font font, CellStyle cellStyle, Integer relativeRowIndex, Cell cell, Workbook wb, ExcelStyle annotation, Field field, Boolean isHead){
CommonCallBackData<T> callBackData = new CommonCallBackData<>();
callBackData.setFont(font);
callBackData.setCellStyle(cellStyle);
callBackData.setRelativeRowIndex(relativeRowIndex);
callBackData.setDataList(this.dataList);
callBackData.setCell(cell);
callBackData.setWorkbook(wb);
callBackData.setAnnotation(annotation);
callBackData.setField(field);
callBackData.setIsHead(isHead);
return callBackData;
}
}
六、设置一下需要导出设置文本
public class TypeConsumer<T extends BaseType> {
public void richTextConsumer(CommonCallBackData<T> callBackData) {
if (Boolean.TRUE.equals(callBackData.getIsHead())) {
setRequireField(callBackData, "*");
return;
}
Integer relativeRowIndex = callBackData.getRelativeRowIndex();
List<T> dataList = callBackData.getDataList();
T dto = dataList.get(relativeRowIndex);
if (Boolean.TRUE.equals(dto.isGetRich())) {
setRequireField(callBackData, "富文本");
}
}
private static<T> void setRequireField(CommonCallBackData<T> callBackData, String markWord) {
if (callBackData.getAnnotation().isRequire() == false) {
return;
}
Workbook workbook = callBackData.getWorkbook();
Cell cell = callBackData.getCell();
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
String big = markWord;
String value = cell.getStringCellValue() + big;
int index = value.lastIndexOf(big);
//将*三个字设置为红色字体
RichTextString richTextString = new XSSFRichTextString(value);
richTextString.applyFont(index, index + 1, font);
cell.setCellValue(richTextString);
}
public void cellStyleTextConsumer(CommonCallBackData<T> callBackData) {
Integer relativeRowIndex = callBackData.getRelativeRowIndex();
List<T> dataList = callBackData.getDataList();
if(CollectionUtils.isEmpty(dataList)){
return;
}
T dto = dataList.get(relativeRowIndex);
if (Boolean.TRUE.equals(dto)) {
CellStyle cellStyle = callBackData.getCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
}
}
}
七、导出工具类
@Slf4j
public class ExcelUtil {
public static void writeExcel(HttpServletResponse response, List list, String fileName,
String sheetName, Class clazz) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String reFileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + reFileName + ".xlsx");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
EasyExcel.write(response.getOutputStream(), clazz)
.inMemory(true)
.sheet(sheetName).doWrite(list);
} catch (IOException e) {
log.error("write excel fail,io exception", e);
}
}
/**
* 导出excel
*
* @param response 输出流
* @param dataList 导出的数据
* @param clazz 模板类
* @param sheetName sheetName
* @param cellWriteHandlers 样式处理类
*/
public static void writeExcelWithRegisterWriteHandler(HttpServletResponse response, List dataList, String fileName,
String sheetName, Class clazz, SheetWriteHandler sheetWriteHandlers, CellWriteHandler... cellWriteHandlers) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String reFileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + reFileName + ".xlsx");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
writeWithTitleAndSpinnerHandler(response.getOutputStream(), sheetName, clazz, dataList, sheetWriteHandlers, cellWriteHandlers);
} catch (IOException e) {
log.error("write excel fail,io exception", e);
}
}
private static void writeWithTitleAndSpinnerHandler(OutputStream outputStream, String sheetName, Class clazz, List dataList,
SheetWriteHandler sheetWriteHandlers,
CellWriteHandler[] cellWriteHandlers){
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, clazz)
.inMemory(true)
.registerWriteHandler(sheetWriteHandlers)
.sheet(sheetName);
if (null != cellWriteHandlers && cellWriteHandlers.length > 0) {
for (int i = 0; i < cellWriteHandlers.length; i++) {
excelWriterSheetBuilder.registerWriteHandler(cellWriteHandlers[i]);
}
}
excelWriterSheetBuilder.doWrite(dataList);
}
}
八、导出实践
private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
@Override
public void exportExcel(HttpServletResponse response) {
//1、指定要隐藏的列
List<Integer> hiddenColumnIndexes = new ArrayList<>();
hiddenColumnIndexes.add(6);
//2、设置富文本,必选项
TypeConsumer<ExcelImport> consumer = new TypeConsumer<>();
Consumer<CommonCallBackData<ExcelImport>> cellStyleTextConsumer = consumer::cellStyleTextConsumer;
Consumer<CommonCallBackData<ExcelImport>> richTextConsumer = consumer::richTextConsumer;
TitleHandler titleHandler = new TitleHandler<ExcelImport>(ExcelImport.class, Collections.EMPTY_LIST,
null, cellStyleTextConsumer, richTextConsumer, hiddenColumnIndexes);
//3、设置下拉框
List<DropDownBox> buildBoxSpinners = new ArrayList<>();
DropDownBox dropDownBox = new DropDownBox(4, new String[]{"禁用", "启用"});
buildBoxSpinners.add(dropDownBox);
//4、设置导出文件
ExcelUtil.writeExcelWithRegisterWriteHandler(response, Collections.emptyList(),
"文件导出test" + FORMATTER.format(LocalDateTime.now()),
"sheet", ExcelImport.class, new CustomMeterInfoSpinnerWriteHandler(buildBoxSpinners), titleHandler);
}
九、导出设置的类
@Data
public class ExcelImport extends BaseType {
/**
* 父id
*/
@ExcelIgnore
private Long parentId;
/**
* 全量分类编码
*/
@ExcelProperty(index = 0,value = {"父级分类编码"})
@ColumnWidth(20)
@ExcelStyle(richText = true,isRequire = true)
private String parentClassifyCode;
/**
* 分类编码
*/
@ExcelProperty(index = 1,value = {"分类编码"})
@ColumnWidth(15)
private String classifyCode;
/**
* 分类名
*/
@ExcelProperty(index = 2,value = {"分类名称"})
@ColumnWidth(15)
private String classifyName;
/**
* 备注信息
*/
@ExcelProperty(index = 3,value = {"备注信息"})
@ColumnWidth(30)
private String classifyDesc;
@ExcelIgnore
private Integer isEnabled;
/**
* 是否启用
*/
@ExcelProperty(index = 4,value = {"是否启用"})
@ColumnWidth(10)
private String isEnabledValue;
/**
* 图片url
*/
@ExcelProperty(index = 5,value = {"图片url"})
@ColumnWidth(20)
private String image;
/**
* 失败原因
*/
@ExcelProperty(index = 6,value = {"失败原因"})
@ColumnWidth(20)
@ExcelStyle(isRequire = false)
private String reason;
@Override
public Boolean isGetCellStyle() {
return true;
}
@Override
public Boolean isGetRich() {
return true;
}
}
十、导出实践
附录：（可以用于转换）
public class TypeConverter implements Converter<Integer> {
public static final String DISABLE = "禁用";
public static final String ENABLE = "启用";
@Override
public Class supportJavaTypeKey() {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (cellData.getStringValue().equals(DISABLE)) {
return 0;
} else if (cellData.getStringValue().equals(ENABLE)) {
return 1;
}
return 9999;
}
@Override
public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (integer == 1) {
return new CellData(ENABLE);
} else if (integer == 0) {
return new CellData(DISABLE);
} else {
return new CellData("");
}
}
}
原网址: 访问
创建于: 2021-08-10 10:45:53
目录: default
标签: 无
未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到
最新评论