1.需要效果.
2.pom.xml 依赖jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.2</version>
</dependency>
3.自定义注解
/**
* 自定义注解标记导出excel的下拉数据集
* Author xianyu
* Date 2021-01-20
*/
@Documented
// 作用在字段上
@Target(ElementType.FIELD)
// 运行时有效
@Retention(RetentionPolicy.RUNTIME)
public @interface DropDownSetField {
// 固定下拉内容
String[] source() default {};
String name() default "";
}
4.实体类添加 easyexecl注释 和自定义注释
5.逻辑处理
@Override
public void downTemplate(HttpServletResponse response) {
try {
// 获取该类声明的所有字段
Field[] fields = PersonModel.class.getDeclaredFields();
// 响应字段对应的下拉集合
Map<Integer, String[]> map = new HashMap<>();
Field field = null;
// 循环判断哪些字段有下拉数据集,并获取
for (int i = 0; i < fields.length; i++) {
field = fields[i];
// 解析注解信息
DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);
if (null != dropDownSetField) {
String name = dropDownSetField.name();
if (!StringUtils.isEmpty(name)) {
//这里简单处理,直接写死了 , 可以从库里查字典值,然后赋值 ,这里name是注解上的key值,匹配
if (name.equals(Constants.OLD_CATEGORY)) { // 老人类别 普通老人 2->孤寡老人 3->低保老人 4->残疾老人 5->空巢老人
List<String> list = new ArrayList<>();
list.add("普通老人");list.add("孤寡老人");list.add("低保老人");
list.add("残疾老人");list.add("空巢老人");
String[] params = list.toArray(new String[list.size()]);
insertMap(map, params, dropDownSetField, i);
}
if (name.equals(Constants.SEX)) { //当前栏为 销售方式 栏,处理 销售方式 下拉框
List<String> list = new ArrayList<>();
list.add("男性");
list.add("女性");
String[] params = list.toArray(new String[list.size()]);
insertMap(map, params, dropDownSetField, i);
}
if (name.equals(Constants.CHANGE_TYPE)) { //当前栏为 异动状态: 1->正常 2->迁出 3->去世
List<String> list = new ArrayList<>();
list.add("正常");
list.add("迁出");
list.add("去世");
String[] params = list.toArray(new String[list.size()]);
insertMap(map, params, dropDownSetField, i);
}
}else {
insertMap(map,null,dropDownSetField,i);
}
}
}
//文件以流形式返回前端下载
String fileName="lrmb.xlsx";
OutputStream fileOutputStream = null;
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setContentType("application/x-download");
response.setCharacterEncoding("UTF-8");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.flushBuffer();
fileOutputStream = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(fileOutputStream, PersonModel.class)
.registerWriteHandler(new ProductCellWriteHandler(map)).build();
WriteSheet sheet = EasyExcel.writerSheet(0, "老人模板").build();
excelWriter.write(null, sheet);
excelWriter.finish();
fileOutputStream.flush();
fileOutputStream.close();
}catch (Exception e){
e.printStackTrace();
throw new GlobalException("下载模板失败!");
}
}
//插入到map中
private void insertMap(Map<Integer, String[]> map, String[] params, DropDownSetField dropDownSetField, int i) {
String[] sources = ResoveDropAnnotationUtil.resove(dropDownSetField, params);
if (null != sources && sources.length > 0) {
map.put(i, sources);
}
}
6.工具类
public class ResoveDropAnnotationUtil {
private static String productCategoryName = "productCategoryName"; //商品分类名
private static String salesMode = "salesMode"; //销售方式
private static String pms_sales_mode = "pms_sales_mode"; //销售方式字典key
private static String format = "format"; //规格
private static String supplier = "supplier"; //供应商
private static String pms_supplier = "pms_supplier"; //供应商字典key
private static String productUnit = "productUnit"; //商品单位
private static String pms_product_unit = "pms_product_unit"; //商品单位字典key
private static String productBrand = "productBrand"; //商品品牌
private static String pms_product_brand = "pms_product_brand"; //商品品牌字典key
public static String[] resove(DropDownSetField dropDownSetField, String[] strings) {
if (!Optional.ofNullable(dropDownSetField).isPresent()) {
return null;
}
// 获取固定下拉信息
String[] source = dropDownSetField.source();
if (null != source && source.length > 0) {
return source;
}
if (null != strings && strings.length > 0) {
try {
String[] dynamicSource = strings;
if (null != dynamicSource && dynamicSource.length > 0) {
return dynamicSource;
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//插入到map中
private void insertMap(Map<Integer, String[]> map, String[] params, DropDownSetField dropDownSetField, int i) {
String[] sources = ResoveDropAnnotationUtil.resove(dropDownSetField, params);
if (null != sources && sources.length > 0) {
map.put(i, sources);
}
}
}
7.监听器
package com.zhzh.utils.execl;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.stereotype.Component;
import java.util.Map;
@Component
public class ProductCellWriteHandler implements SheetWriteHandler {
private Map<Integer,String[]> map = null;
public ProductCellWriteHandler(Map<Integer,String[]> map){
this.map = map;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
// k 为存在下拉数据集的单元格下表 v为下拉数据集
map.forEach((k, v) -> {
// 下拉列表约束数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示","此值与单元格定义格式不一致");
// validation.createPromptBox("填写说明:","填写内容只能为下拉数据集中的单位,其他单位将会导致无法入仓");
sheet.addValidationData(validation);
});
}
}