EasyExcel导出excel模板的几种解决方案
1、导出含有下拉框的excel
1.1、解决思路
excel下拉框包含两种情况，一种是固定内容的下拉框，一种是动态内容的下拉框，所以在设计的时候要考虑这两种情况。实现方式是通过自定义注解和自定义处理器的方式结合easyExcel进行模板下载，自定义注解应用到具体的字段中，在字段中传入相关参数，如果是固定内容的下拉框，则字段的参数直接传具体内容的数组即可，若是动态内容的下拉框则需要具体实现类以及数据类型，在实现类中通过参数来查询动态数据并以数组的方式返回。在注解解析工具类判断该字段是固定内容还是动态内容，并将内容进行解析和返回。在自定义处理器中将解析出的数据写入到一张sheet表中作为下拉框的数据源表，然后再隐藏，将下拉框数据设置到指定行列之后，统一设置下拉框表格除下拉列表意外的所有值都禁止输入。
1.2、核心代码实现
自定义注解
/**
* 动态下拉框注解
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {
/**
* 固定下拉内容
*/
String[] source() default {};
/**
* 动态下拉内容
*/
Class<? extends ExcelDynamicSelect>[] sourceClass() default {};
/**
* 参数
*
* @return
*/
String params() default "";
/**
* 设置下拉框的起始行，默认为第二行
*/
int firstRow() default 1;
/**
* 设置下拉框的结束行，默认为最后一行
*/
int lastRow() default 0x10000;
}
注解解析工具类
/**
* 解析下拉内容
*/
@Data
@Slf4j
public class ExcelSelectedResolveUtil {
/**
* 下拉内容
*/
private String[] source;
/**
* 设置下拉框的起始行，默认为第二行
*/
private int firstRow;
/**
* 设置下拉框的结束行，默认为最后一行
*/
private int lastRow;
public String[] resolveSelectedSource(ExcelSelected excelSelected) {
if (excelSelected == null) {
return null;
}
// 获取固定下拉框的内容
String[] source = excelSelected.source();
if (source.length > 0) {
return source;
}
// 获取动态下拉框的内容
Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
String params = excelSelected.params();
if (classes.length > 0) {
try {
ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
String[] dynamicSelectSource = excelDynamicSelect.getSource(params);
if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
return dynamicSelectSource;
}
} catch (InstantiationException | IllegalAccessException e) {
log.error("解析动态下拉框数据异常", e);
}
}
return null;
}
}
easyExcel处理器
@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {
private final Map<Integer, ExcelSelectedResolveUtil> selectedMap;
/**
* 设置阈值，避免生成的导入模板下拉值获取不到，可自行设置数量大小
*/
private static final Integer LIMIT_NUMBER = 30;
/**
* Called before create the sheet
*/
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* Called after the sheet is created
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
selectedMap.forEach((k, v) -> {
// 设置下拉列表的行： 首行，末行，首列，末列
CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
// 如果下拉值总数大于25，则使用一个新sheet存储，避免生成的导入模板下拉值获取不到
if (v.getSource().length > LIMIT_NUMBER) {
//定义sheet的名称
//1.创建一个隐藏的sheet 名称为 hidden + k
String sheetName = "hidden" + k;
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet hiddenSheet = workbook.createSheet(sheetName);
for (int i = 0, length = v.getSource().length; i < length; i++) {
// 开始的行数i，列数k
hiddenSheet.createRow(i).createCell(k).setCellValue(v.getSource()[i]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
String excelLine = getExcelLine(k);
// =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.getSource().length + 1);
// 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, rangeList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
// 设置存储下拉列值得sheet为隐藏
int hiddenIndex = workbook.getSheetIndex(sheetName);
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
}
// 设置下拉列表的值
DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请输入下拉选项中的内容");
sheet.addValidationData(validation);
});
}
/**
* 返回excel列标A-Z-AA-ZZ
*
* @param num 列数
* @return java.lang.String
*/
private String getExcelLine(int num) {
String line = "";
int first = num / 26;
int second = num % 26;
if (first > 0) {
line = (char) ('A' + first - 1) + "";
}
line += (char) ('A' + second) + "";
return line;
}
}
easyExcel导出excel工具类
package com.devboy.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.devboy.annotation.ExcelSelected;
import com.devboy.handler.SelectedSheetWriteHandler;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelUtil {
public static <T> void exportSelectedSheet(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response) {
try {
Map<Integer, ExcelSelectedResolveUtil> selectedMap = resolveSelectedAnnotation(clazz);
// String filename = encodingFilename(sheetName);
// response.reset();
// FileUtils.setAttachmentResponseHeader(response, filename);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
ServletOutputStream os = response.getOutputStream();
EasyExcel.write(os, clazz)
.autoCloseStream(false)
// 自动适配
.registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
.inMemory(Boolean.TRUE)
.sheet(sheetName).doWrite(list);
} catch (IOException e) {
throw new RuntimeException("导出Excel异常");
}
}
private static <T> Map<Integer, ExcelSelectedResolveUtil> resolveSelectedAnnotation(Class<T> head) {
Map<Integer, ExcelSelectedResolveUtil> selectedMap = new HashMap<>();
Field[] fields = head.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
// 解析注解信息
ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
if (selected != null) {
ExcelSelectedResolveUtil excelSelectedResolve = new ExcelSelectedResolveUtil();
String[] source = excelSelectedResolve.resolveSelectedSource(selected);
if (source != null && source.length > 0) {
excelSelectedResolve.setSource(source);
excelSelectedResolve.setFirstRow(selected.firstRow());
excelSelectedResolve.setLastRow(selected.lastRow());
if (property != null && property.index() >= 0) {
selectedMap.put(property.index(), excelSelectedResolve);
} else {
selectedMap.put(i, excelSelectedResolve);
}
}
}
}
return selectedMap;
}
}
使用方式
package com.devboy.domain;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.devboy.annotation.ExcelSelected;
import com.devboy.service.impl.DictTypeSelectImpl;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
@HeadRowHeight(30)
@HeadStyle(fillPatternType = FillPatternTypeEnum.NO_FILL, fillForegroundColor = 10)
@ColumnWidth(25)
@ContentRowHeight(25)
public class Student {
/**
* 序号
*/
@ExcelProperty({"序号"})
@ColumnWidth(10)
private Long id;
/**
* 姓名
*/
@ExcelProperty({"姓名"})
@ColumnWidth(10)
private String name;
/**
* 性别 性别为固定下拉框
*/
@ExcelProperty({"性别"})
@ColumnWidth(10)
@ExcelSelected(source = {"男", "女"})
private String sex;
/**
* 年级 这里的年级作为动态数据字典，可动态配置，本demo仅进行数据模拟不再使用数据库
*/
@ExcelProperty({"年级"})
@ColumnWidth(10)
@ExcelSelected(sourceClass = DictTypeSelectImpl.class, params = "grade")
private String grade;
/**
* 角色
*/
@ExcelProperty({"角色"})
@ColumnWidth(10)
@ExcelSelected(sourceClass = DictTypeSelectImpl.class, params = "role")
private String role;
}
效果图
2、导出标题带有批注的excel
2.1、解决思路
导出标题带有批注的excel思路同上，都是通过自定义注解和自定义处理器的方式来做，主要是处理方式不同，这里主要解释一下如何处理批注，处理方式是通过在注解中传入批注内容，所在行、列，然后在注解解析工具类中对注解进行解析，返回解析出的批注内容、行、列，然后在自定义处理器中将批注写入对应行列的excel单元格中。
2.2、代码实现
自定义注解
package com.devboy.annotation;
import com.devboy.service.ExcelRichTextSelect;
import java.lang.annotation.*;
/**
* 自定义批注注解
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelRichText {
/**
* 批注内容
*
* @return
*/
String value();
/**
* 所在行
*
* @return
*/
int rowNum() default 0;
/**
* 所在列
*
* @return
*/
int colNum() default 0;
String dictCode()default "";
/**
* 动态下拉内容
*/
Class<? extends ExcelRichTextSelect>[] sourceClass() default {};
}
自定义处理器
package com.devboy.handler;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.devboy.domain.RichText;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.util.List;
/**
* 批注处理器
*/
@Data
@AllArgsConstructor
public class RichTextWriterHandler implements CellWriteHandler {
private final List<RichText> richTextList;
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<com.alibaba.excel.metadata.data.WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
if (aBoolean) { // 判断是否为表头，true则是
Integer row = cell.getRowIndex();// 行号（从0开始）
int columnIndex = cell.getColumnIndex();
for (RichText richText : richTextList) {
if (richText.getColNum() == columnIndex && richText.getRowNum() == row) {
Sheet sheet = writeSheetHolder.getSheet();
Drawing draw = sheet.createDrawingPatriarch();
//定位批注的显示区域
Comment comment = draw.createCellComment(new XSSFClientAnchor(1, 1, 1, 1, (short) columnIndex + 1, (short) columnIndex + 1, (short) columnIndex + 2, (short) columnIndex + 5));
// 批注内容
comment.setString(new XSSFRichTextString(richText.getValue()));
cell.setCellComment(comment);
}
}
}
}
}
easyExcel工具类以及注解数据解析方法
package com.devboy.utils;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.util.StringUtils;
import com.devboy.annotation.ExcelRichText;
import com.devboy.annotation.ExcelSelected;
import com.devboy.domain.RichText;
import com.devboy.handler.RichTextWriterHandler;
import com.devboy.handler.SelectedSheetWriteHandler;
import com.devboy.service.ExcelRichTextSelect;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;
public class ExcelUtil {
public static <T> void exportSelectedSheet(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response) {
try {
Map<Integer, ExcelSelectedResolveUtil> selectedMap = resolveSelectedAnnotation(clazz);
List<RichText> richTexts = resolveRichText(clazz);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
ServletOutputStream os = response.getOutputStream();
EasyExcel.write(os, clazz)
.autoCloseStream(false)
// 注册下拉框处理器
.registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
// 注册自定义批注处理器
.registerWriteHandler(new RichTextWriterHandler(richTexts))
.inMemory(Boolean.TRUE)
.sheet(sheetName).doWrite(list);
} catch (IOException e) {
throw new RuntimeException("导出Excel异常");
}
}
private static <T> Map<Integer, ExcelSelectedResolveUtil> resolveSelectedAnnotation(Class<T> head) {
Map<Integer, ExcelSelectedResolveUtil> selectedMap = new HashMap<>();
Field[] fields = head.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
// 解析注解信息
ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
if (selected != null) {
ExcelSelectedResolveUtil excelSelectedResolve = new ExcelSelectedResolveUtil();
String[] source = excelSelectedResolve.resolveSelectedSource(selected);
if (source != null && source.length > 0) {
excelSelectedResolve.setSource(source);
excelSelectedResolve.setFirstRow(selected.firstRow());
excelSelectedResolve.setLastRow(selected.lastRow());
if (property != null && property.index() >= 0) {
selectedMap.put(property.index(), excelSelectedResolve);
} else {
selectedMap.put(i, excelSelectedResolve);
}
}
}
}
return selectedMap;
}
private static <T> List<RichText> resolveRichText(Class<T> head) {
List<RichText> res = new ArrayList<>();
Field[] fields = head.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
ExcelRichText richText = field.getAnnotation(ExcelRichText.class);
if (richText != null) {
RichText text = new RichText();
String s = richText.dictCode();
if (StringUtils.isNotBlank(s)) {
Class<? extends ExcelRichTextSelect>[] classes = richText.sourceClass();
try {
ExcelRichTextSelect excelRichTextSelect = classes[0].newInstance();
String[] source = excelRichTextSelect.getSource(s);
String sourceLabel = Arrays.stream(source).collect(Collectors.joining("、"));
String a = StrUtil.format(richText.value(), sourceLabel);
text.setValue(a);
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
text.setValue(richText.value());
}
} else {
text.setValue(richText.value());
}
text.setColNum(richText.colNum());
text.setRowNum(richText.rowNum());
res.add(text);
}
}
return res;
}
}
具体实体类
package com.devboy.domain;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.devboy.annotation.ExcelRichText;
import com.devboy.service.impl.DictTypeRichTextImpl;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
@HeadRowHeight(30)
@HeadStyle(fillPatternType = FillPatternTypeEnum.NO_FILL, fillForegroundColor = 10)
@ColumnWidth(25)
@ContentRowHeight(25)
public class Computer {
/**
* 序号
*/
@ExcelProperty({"序号"})
@ColumnWidth(10)
private Long id;
/**
* 名称
*/
@ExcelProperty({"名称"})
@ColumnWidth(10)
private String name;
/**
* 类型
*/
@ExcelProperty({"类型"})
@ColumnWidth(10)
@ExcelRichText(value = "请从一下系统中存在的类型，多个类型通过、进行分割。可选类型：{}", rowNum = 0, colNum = 2, dictCode = "computer", sourceClass = DictTypeRichTextImpl.class)
private String type;
}
效果截图
num.NO_FILL, fillForegroundColor = 10)
@ColumnWidth(25)
@ContentRowHeight(25)
public class Computer {
/**
* 序号
*/
@ExcelProperty({“序号”})
@ColumnWidth(10)
private Long id;
/**
* 名称
*/
@ExcelProperty({"名称"})
@ColumnWidth(10)
private String name;
/**
* 类型
*/
@ExcelProperty({"类型"})
@ColumnWidth(10)
@ExcelRichText(value = "请从一下系统中存在的类型，多个类型通过、进行分割。可选类型：{}", rowNum = 0, colNum = 2, dictCode = "computer", sourceClass = DictTypeRichTextImpl.class)
private String type;
}
``
效果截图
具体代码可参考：https://gitee.com/liu-ye-ly/some-solutions-for-easy-excel
扫一扫
3318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
