EasyExcel导出带下拉选数据的Excel数据导入模板

数据的Excel数据导入模板"/>

EasyExcel导出带下拉选数据的Excel数据导入模板

#因为项目中需要导入一些信息，但是这些信息比较不常见，且在项目字典数据中维护有这些数据，所以在导出模板的时候，把这些数据一并导出，可以减少用户的编写，避免在导入的时候因为数据错误，发生一些业务问题

直接开始​​​​​​​

一、首先引入依赖   easyexcel依赖

<!--easyexcel依赖--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.10</version></dependency>

二、创建自定义注解

import java.lang.annotation.*;/*** 标注导出的列为下拉框类型，并为下拉框设置内容*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {/*** 固定下拉内容*/String[] source() default {};/*** 动态下拉内容*/Class<? extends ExcelDynamicSelect>[] sourceClass() default {};/*** 设置下拉框的起始行，默认为第二行*/int firstRow() default 1;/*** 设置下拉框的结束行，默认为最后一行*/int lastRow() default 0x10000;
}

三、解析自定义注解接口

public interface ExcelDynamicSelect {/*** 获取动态生成的下拉框可选数据* @return 动态生成的下拉框可选数据*/String[] getSource();
}

四、创建一个实体类，（这边以我项目内的演示）

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 lombok.Data;
import org.springblade.basicdata.service.impl.DeptTemplateServiceImpl;
import org.springblade.basicdata.service.impl.SchoolTemplateServiceImpl;
import org.springblade.basicdata.service.impl.StationlateServiceImpl;
import org.springblade.basicdata.service.impl.ZgTypeslateServiceImpl;import java.io.Serializable;@Data
@HeadRowHeight(20)
@ContentRowHeight(18)
public class TeacherExcel implements Serializable {private static final long serialVersionUID = 1L;/*** 职工编号*/@ExcelProperty(index = 0, value = "职工编号")@ColumnWidth(20)private String no;/*** 姓名*/@ExcelProperty(index = 1, value = "姓名")@ColumnWidth(30)private String name;/*** 身份证*/@ExcelProperty(index = 2, value = "身份证号")@ColumnWidth(25)private String idCard;/*** 电话*/@ExcelProperty(index = 3, value = "联系电话")@ColumnWidth(15)private String phone;/*** 学校id*///需要自定义实现  SchoolTemplateServiceImpl，下面有具体的实现方式@ExcelSelected(sourceClass = SchoolTemplateServiceImpl.class)@ExcelProperty(index = 4, value = "校区")@ColumnWidth(45)private String schoolName;/*** 部门*///需要自定义实现  DeptTemplateServiceImpl，下面有具体的实现方式@ExcelSelected(sourceClass = DeptTemplateServiceImpl.class)@ExcelProperty(index = 5, value = "部门")@ColumnWidth(45)private String bumenName;/*** 岗位类别*///需要自定义实现  StationlateServiceImpl，下面有具体的实现方式@ExcelSelected(sourceClass = StationlateServiceImpl.class)@ExcelProperty(index = 6, value = "岗位类别")@ColumnWidth(15)private String station;/*** 职工类型*///需要自定义实现  ZgTypeslateServiceImpl，下面有具体的实现方式@ExcelSelected(sourceClass = ZgTypeslateServiceImpl.class)@ExcelProperty(index = 7, value = "职工类型")@ColumnWidth(15)private String zhigongleibie;/*** 备注*/@ExcelProperty(index = 8, value = "备注")@ColumnWidth(15)private String remarks;}

五、重点是以下的代码

1、以岗位类型为例，展示数据的实现方式

创建StationlateServiceImpl实现类，实现方法 为我自己的业务需求，你们可以根据你们的情况去修改，这边返回给注解的是一个字符串的数组，也就是需要导出的下拉选数据

import java.util.List;
import java.util.stream.Collectors;/*** 岗位类别下拉选数据实现类*/
public class StationlateServiceImpl implements ExcelDynamicSelect {private static IDictClient dictClient;static {dictClient = SpringUtil.getBean(IDictClient.class);}@Overridepublic String[] getSource() {R<List<Dict>> dictClientList =dictClient.getList(DictEnum.POST_TYPE.getName());if(Func.isNotEmpty(dictClientList) && dictClientList.getCode() == 200){if(Func.isNotEmpty(dictClientList.getData())){return Func.toStrArray(dictClientList.getData().stream().map(Dict::getDictValue).collect(Collectors.joining(",")));}}return new String[0];}
}

Func.toStrArray方法就是讲给定的逗号拼接的字符串转为字符串数组，这个百度一下，一大堆

六、EasyExcelUtil工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;
import org.apachemons.lang3.StringUtils;import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;@Slf4j
public class EasyExcelUtil {/*** 创建即将导出的sheet页（sheet页中含有带下拉框的列）* @param head 导出的表头信息和配置* @param sheetNo sheet索引* @param sheetName sheet名称* @param <T> 泛型* @return sheet页*/public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);return EasyExcel.writerSheet(sheetNo, sheetName).head(head).registerWriteHandler(new SelectedSheetWriteHandler(selectedMap)).build();}/*** 解析表头类中的下拉注解* @param head 表头类* @param <T> 泛型* @return Map<下拉框列索引, 下拉框内容> map*/private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性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) {ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();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;}public static boolean isIDNumber(String IDNumber) {if (IDNumber == null || "".equals(IDNumber)) {return false;}// 定义判别用户身份证号的正则表达式（15位或者18位，最后一位可以为字母）String regularExpression = "(^[1-9]\\d{5}(18|19|20)\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$)|" +"(^[1-9]\\d{5}\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}$)";//假设18位身份证号码:41000119910101123X  410001 19910101 123X//^开头//[1-9] 第一位1-9中的一个      4//\\d{5} 五位数字           10001（前六位省市县地区）//(18|19|20)                19（现阶段可能取值范围18xx-20xx年）//\\d{2}                    91（年份）//((0[1-9])|(10|11|12))     01（月份）//(([0-2][1-9])|10|20|30|31)01（日期）//\\d{3} 三位数字            123（第十七位奇数代表男，偶数代表女）//[0-9Xx] 0123456789Xx其中的一个 X（第十八位为校验值）//$结尾//假设15位身份证号码:410001910101123  410001 910101 123//^开头//[1-9] 第一位1-9中的一个      4//\\d{5} 五位数字           10001（前六位省市县地区）//\\d{2}                    91（年份）//((0[1-9])|(10|11|12))     01（月份）//(([0-2][1-9])|10|20|30|31)01（日期）//\\d{3} 三位数字            123（第十五位奇数代表男，偶数代表女），15位身份证不含X//$结尾boolean matches = IDNumber.matches(regularExpression);//判断第18位校验值if (matches) {if (IDNumber.length() == 18) {try {char[] charArray = IDNumber.toCharArray();//前十七位加权因子int[] idCardWi = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};//这是除以11后，可能产生的11位余数对应的验证码String[] idCardY = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"};int sum = 0;for (int i = 0; i < idCardWi.length; i++) {int current = Integer.parseInt(String.valueOf(charArray[i]));int count = current * idCardWi[i];sum += count;}char idCardLast = charArray[17];int idCardMod = sum % 11;if (idCardY[idCardMod].toUpperCase().equals(String.valueOf(idCardLast).toUpperCase())) {return true;} else {return false;}} catch (Exception e) {e.printStackTrace();return false;}}return false;}return matches;}public static  boolean isMobile(String phone){Pattern p = null;Matcher m = null;boolean b = false;// 验证手机号String s2="^[1](([3|5|6|7|8|9][\\d])|([4][4,5,6,7,8,9])|([6][2,5,6,7])|([7][^9])|([9][1,8,9]))[\\d]{8}$";if(StringUtils.isNotBlank(phone)){p = Patternpile(s2);m = p.matcher(phone);b = m.matches();}return b;}}

七、导出接口

/** 教师入职Excel模板导出 */@ApiLog("教师入职Excel模板导出")@GetMapping("export-template")@ApiOperationSupport(order = 7)@ApiOperation(value = "教师入职Excel模板导出")public void exportTeacher(HttpServletResponse response, HttpServletRequest request) {String filename = "教师入职信息导入模板";try {String userAgent = request.getHeader("User-Agent");if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {// 针对IE或者以IE为内核的浏览器：filename = java.URLEncoder.encode(filename, "UTF-8");} else {// 非IE浏览器的处理:filename = new String(filename.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);}response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", filename + Func.formatDateTime(new Date()) + ".xlsx"));response.setHeader("Cache-Control", "no-cache");response.setHeader("Pragma", "no-cache");response.setDateHeader("Expires", -1);response.setCharacterEncoding("UTF-8");ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(TeacherExcel.class, 0, "教师入职信息数据表");excelWriter.write(new ArrayList<String>(), writeSheet);excelWriter.finish();} catch (UnsupportedEncodingException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}

八、导出效果图

完结，有问题欢迎指正