您现在的位置是：首页 > 技术教程 正文

EasyExcel导出带下拉选数据的Excel数据导入模板（以及下拉选数据量多不展示的问题）

admin 阅读： 2024-03-24
后台-插件-广告管理-内容页头部广告（手机）

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

直接开始

一、首先引入依赖   easyexcel依赖

  2. <dependency>
  3. <groupId>com.alibabagroupId>
  4. <artifactId>easyexcelartifactId>
  5. <version>2.2.10version>
  6. dependency>

二、创建自定义注解

  3. import java.lang.annotation.*;
  5. /**
  6. * 标注导出的列为下拉框类型，并为下拉框设置内容
  7. */
  8. @Documented
  9. @Retention(RetentionPolicy.RUNTIME)
  10. @Target(ElementType.FIELD)
  11. public @interface ExcelSelected {
  12. /**
  13. * 固定下拉内容
  14. */
  15. String[] source() default {};
  17. /**
  18. * 动态下拉内容
  19. */
  20. Class<? extends ExcelDynamicSelect>[] sourceClass() default {};
  22. /**
  23. * 设置下拉框的起始行，默认为第二行
  24. */
  25. int firstRow() default 1;
  27. /**
  28. * 设置下拉框的结束行，默认为最后一行
  29. */
  30. int lastRow() default 0x10000;
  31. }

三、解析自定义注解接口

  2. public interface ExcelDynamicSelect {
  3. /**
  4. * 获取动态生成的下拉框可选数据
  5. * @return 动态生成的下拉框可选数据
  6. */
  7. String[] getSource();
  8. }

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

  1. import com.alibaba.excel.annotation.ExcelProperty;
  2. import com.alibaba.excel.annotation.write.style.ColumnWidth;
  3. import com.alibaba.excel.annotation.write.style.ContentRowHeight;
  4. import com.alibaba.excel.annotation.write.style.HeadRowHeight;
  5. import lombok.Data;
  6. import org.springblade.basicdata.service.impl.DeptTemplateServiceImpl;
  7. import org.springblade.basicdata.service.impl.SchoolTemplateServiceImpl;
  8. import org.springblade.basicdata.service.impl.StationlateServiceImpl;
  9. import org.springblade.basicdata.service.impl.ZgTypeslateServiceImpl;
  11. import java.io.Serializable;
  14. @Data
  15. @HeadRowHeight(20)
  16. @ContentRowHeight(18)
  17. public class TeacherExcel implements Serializable {
  19. private static final long serialVersionUID = 1L;
  21. /**
  22. * 职工编号
  23. */
  24. @ExcelProperty(index = 0, value = "职工编号")
  25. @ColumnWidth(20)
  26. private String no;
  27. /**
  28. * 姓名
  29. */
  30. @ExcelProperty(index = 1, value = "姓名")
  31. @ColumnWidth(30)
  32. private String name;
  33. /**
  34. * 身份证
  35. */
  36. @ExcelProperty(index = 2, value = "身份证号")
  37. @ColumnWidth(25)
  38. private String idCard;
  40. /**
  41. * 电话
  42. */
  43. @ExcelProperty(index = 3, value = "联系电话")
  44. @ColumnWidth(15)
  45. private String phone;
  46. /**
  47. * 学校id
  48. */
  49. //需要自定义实现 SchoolTemplateServiceImpl，下面有具体的实现方式
  50. @ExcelSelected(sourceClass = SchoolTemplateServiceImpl.class)
  51. @ExcelProperty(index = 4, value = "校区")
  52. @ColumnWidth(45)
  53. private String schoolName;
  54. /**
  55. * 部门
  56. */
  57. //需要自定义实现 DeptTemplateServiceImpl，下面有具体的实现方式
  58. @ExcelSelected(sourceClass = DeptTemplateServiceImpl.class)
  59. @ExcelProperty(index = 5, value = "部门")
  60. @ColumnWidth(45)
  61. private String bumenName;
  62. /**
  63. * 岗位类别
  64. */
  65. //需要自定义实现 StationlateServiceImpl，下面有具体的实现方式
  66. @ExcelSelected(sourceClass = StationlateServiceImpl.class)
  67. @ExcelProperty(index = 6, value = "岗位类别")
  68. @ColumnWidth(15)
  69. private String station;
  70. /**
  71. * 职工类型
  72. */
  73. //需要自定义实现 ZgTypeslateServiceImpl，下面有具体的实现方式
  74. @ExcelSelected(sourceClass = ZgTypeslateServiceImpl.class)
  75. @ExcelProperty(index = 7, value = "职工类型")
  76. @ColumnWidth(15)
  77. private String zhigongleibie;
  78. /**
  79. * 备注
  80. */
  81. @ExcelProperty(index = 8, value = "备注")
  82. @ColumnWidth(15)
  83. private String remarks;
  86. }

五、重点是以下的代码

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

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

  2. import java.util.List;
  3. import java.util.stream.Collectors;
  5. /**
  6. * 岗位类别下拉选数据实现类
  7. */
  8. public class StationlateServiceImpl implements ExcelDynamicSelect {
  10. private static IDictClient dictClient;
  13. static {
  14. dictClient = SpringUtil.getBean(IDictClient.class);
  15. }
  19. @Override
  20. public String[] getSource() {
  21. R> dictClientList =
  22. dictClient.getList(DictEnum.POST_TYPE.getName());
  23. if(Func.isNotEmpty(dictClientList) && dictClientList.getCode() == 200){
  24. if(Func.isNotEmpty(dictClientList.getData())){
  25. return Func.toStrArray(dictClientList.getData().stream().map(Dict::getDictValue).collect(Collectors.joining(",")));
  26. }
  27. }
  28. return new String[0];
  29. }
  30. }

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

六、EasyExcelUtil工具类

  1. import com.alibaba.excel.EasyExcel;
  2. import com.alibaba.excel.annotation.ExcelProperty;
  3. import com.alibaba.excel.write.metadata.WriteSheet;
  4. import lombok.extern.slf4j.Slf4j;
  5. import org.apache.commons.lang3.StringUtils;
  7. import java.lang.reflect.Field;
  8. import java.util.HashMap;
  9. import java.util.Map;
  10. import java.util.regex.Matcher;
  11. import java.util.regex.Pattern;
  14. @Slf4j
  15. public class EasyExcelUtil {
  17. /**
  18. * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
  19. * @param head 导出的表头信息和配置
  20. * @param sheetNo sheet索引
  21. * @param sheetName sheet名称
  22. * @param 泛型
  23. * @return sheet页
  24. */
  25. public static WriteSheet writeSelectedSheet(Class head, Integer sheetNo, String sheetName) {
  26. Map selectedMap = resolveSelectedAnnotation(head);
  28. return EasyExcel.writerSheet(sheetNo, sheetName)
  29. .head(head)
  30. .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
  31. .build();
  32. }
  34. /**
  35. * 解析表头类中的下拉注解
  36. * @param head 表头类
  37. * @param 泛型
  38. * @return Map<下拉框列索引, 下拉框内容> map
  39. */
  40. private static Map resolveSelectedAnnotation(Class head) {
  41. Map selectedMap = new HashMap<>();
  43. // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
  44. Field[] fields = head.getDeclaredFields();
  45. for (int i = 0; i < fields.length; i++){
  46. Field field = fields[i];
  47. // 解析注解信息
  48. ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
  49. ExcelProperty property = field.getAnnotation(ExcelProperty.class);
  50. if (selected != null) {
  51. ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
  52. String[] source = excelSelectedResolve.resolveSelectedSource(selected);
  53. if (source != null && source.length > 0){
  54. excelSelectedResolve.setSource(source);
  55. excelSelectedResolve.setFirstRow(selected.firstRow());
  56. excelSelectedResolve.setLastRow(selected.lastRow());
  57. if (property != null && property.index() >= 0){
  58. selectedMap.put(property.index(), excelSelectedResolve);
  59. } else {
  60. selectedMap.put(i, excelSelectedResolve);
  61. }
  62. }
  63. }
  64. }
  65. return selectedMap;
  66. }
  68. public static boolean isIDNumber(String IDNumber) {
  69. if (IDNumber == null || "".equals(IDNumber)) {
  70. return false;
  71. }
  72. // 定义判别用户身份证号的正则表达式（15位或者18位，最后一位可以为字母）
  73. 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]$)|" +
  74. "(^[1-9]\\d{5}\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}$)";
  75. //假设18位身份证号码:41000119910101123X 410001 19910101 123X
  76. //^开头
  77. //[1-9] 第一位1-9中的一个 4
  78. //\\d{5} 五位数字 10001（前六位省市县地区）
  79. //(18|19|20) 19（现阶段可能取值范围18xx-20xx年）
  80. //\\d{2} 91（年份）
  81. //((0[1-9])|(10|11|12)) 01（月份）
  82. //(([0-2][1-9])|10|20|30|31)01（日期）
  83. //\\d{3} 三位数字 123（第十七位奇数代表男，偶数代表女）
  84. //[0-9Xx] 0123456789Xx其中的一个 X（第十八位为校验值）
  85. //$结尾
  87. //假设15位身份证号码:410001910101123 410001 910101 123
  88. //^开头
  89. //[1-9] 第一位1-9中的一个 4
  90. //\\d{5} 五位数字 10001（前六位省市县地区）
  91. //\\d{2} 91（年份）
  92. //((0[1-9])|(10|11|12)) 01（月份）
  93. //(([0-2][1-9])|10|20|30|31)01（日期）
  94. //\\d{3} 三位数字 123（第十五位奇数代表男，偶数代表女），15位身份证不含X
  95. //$结尾
  96. boolean matches = IDNumber.matches(regularExpression);
  98. //判断第18位校验值
  99. if (matches) {
  101. if (IDNumber.length() == 18) {
  102. try {
  103. char[] charArray = IDNumber.toCharArray();
  104. //前十七位加权因子
  105. int[] idCardWi = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
  106. //这是除以11后，可能产生的11位余数对应的验证码
  107. String[] idCardY = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"};
  108. int sum = 0;
  109. for (int i = 0; i < idCardWi.length; i++) {
  110. int current = Integer.parseInt(String.valueOf(charArray[i]));
  111. int count = current * idCardWi[i];
  112. sum += count;
  113. }
  114. char idCardLast = charArray[17];
  115. int idCardMod = sum % 11;
  116. if (idCardY[idCardMod].toUpperCase().equals(String.valueOf(idCardLast).toUpperCase())) {
  117. return true;
  118. } else {
  119. return false;
  120. }
  122. } catch (Exception e) {
  123. e.printStackTrace();
  124. return false;
  125. }
  126. }
  127. return false;
  128. }
  129. return matches;
  130. }
  133. public static boolean isMobile(String phone){
  134. Pattern p = null;
  135. Matcher m = null;
  136. boolean b = false;
  137. // 验证手机号
  138. 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}$";
  139. if(StringUtils.isNotBlank(phone)){
  140. p = Pattern.compile(s2);
  141. m = p.matcher(phone);
  142. b = m.matches();
  143. }
  144. return b;
  145. }
  147. }

七、SelectedSheetWriteHandler

  2. import com.alibaba.excel.write.handler.SheetWriteHandler;
  3. import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
  4. import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
  5. import lombok.AllArgsConstructor;
  6. import lombok.Data;
  7. import org.apache.poi.ss.usermodel.DataValidation;
  8. import org.apache.poi.ss.usermodel.DataValidationConstraint;
  9. import org.apache.poi.ss.usermodel.DataValidationHelper;
  10. import org.apache.poi.ss.usermodel.Sheet;
  11. import org.apache.poi.ss.util.CellRangeAddressList;
  13. import java.util.Map;
  15. @Data
  16. @AllArgsConstructor
  17. public class SelectedSheetWriteHandler implements SheetWriteHandler {
  19. private final Map selectedMap;
  21. /**
  22. * 设置阈值，避免生成的导入模板下拉值获取不到，可自行设置数量大小
  23. */
  24. private static final Integer LIMIT_NUMBER = 25;
  26. /**
  27. * Called before create the sheet
  28. */
  29. @Override
  30. public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  32. }
  34. /**
  35. * Called after the sheet is created
  36. */
  37. @Override
  38. public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  39. // 这里可以对cell进行任何操作
  40. Sheet sheet = writeSheetHolder.getSheet();
  41. DataValidationHelper helper = sheet.getDataValidationHelper();
  42. selectedMap.forEach((k, v) -> {
  43. // 设置下拉列表的行： 首行，末行，首列，末列
  44. CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
  45. // 如果下拉值总数大于25，则使用一个新sheet存储，避免生成的导入模板下拉值获取不到
  46. if (v.getSource().length > LIMIT_NUMBER) {
  47. //定义sheet的名称
  48. //1.创建一个隐藏的sheet 名称为 hidden + k
  49. String sheetName = "hidden" + k;
  50. Workbook workbook = writeWorkbookHolder.getWorkbook();
  51. Sheet hiddenSheet = workbook.createSheet(sheetName);
  52. for (int i = 0, length = v.getSource().length; i < length; i++) {
  53. // 开始的行数i，列数k
  54. hiddenSheet.createRow(i).createCell(k).setCellValue(v.getSource()[i]);
  55. }
  56. Name category1Name = workbook.createName();
  57. category1Name.setNameName(sheetName);
  58. String excelLine = getExcelLine(k);
  59. // =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
  60. String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.getSource().length + 1);
  61. // 将刚才设置的sheet引用到你的下拉列表中
  62. DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
  63. DataValidation dataValidation = helper.createValidation(constraint, rangeList);
  64. writeSheetHolder.getSheet().addValidationData(dataValidation);
  65. // 设置存储下拉列值得sheet为隐藏
  66. int hiddenIndex = workbook.getSheetIndex(sheetName);
  67. if (!workbook.isSheetHidden(hiddenIndex)) {
  68. workbook.setSheetHidden(hiddenIndex, true);
  69. }
  70. }
  71. // 设置下拉列表的值
  72. DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
  73. // 设置约束
  74. DataValidation validation = helper.createValidation(constraint, rangeList);
  75. // 阻止输入非下拉选项的值
  76. validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
  77. validation.setShowErrorBox(true);
  78. // validation.setSuppressDropDownArrow(true);
  79. validation.createErrorBox("提示", "请输入下拉选项中的内容");
  80. sheet.addValidationData(validation);
  81. });
  82. }
  85. /**
  86. * 返回excel列标A-Z-AA-ZZ
  87. *
  88. * @param num 列数
  89. * @return java.lang.String
  90. */
  91. private String getExcelLine(int num) {
  92. String line = "";
  93. int first = num / 26;
  94. int second = num % 26;
  95. if (first > 0) {
  96. line = (char) ('A' + first - 1) + "";
  97. }
  98. line += (char) ('A' + second) + "";
  99. return line;
  100. }
  101. }

八、ExcelDynamicSelect

  1. public interface ExcelDynamicSelect {
  2. /**
  3. * 获取动态生成的下拉框可选数据
  4. * @return 动态生成的下拉框可选数据
  5. */
  6. String[] getSource();
  7. }

九、ExcelSelectedResolve

  1. @Data
  2. @Slf4j
  3. public class ExcelSelectedResolve {
  4. /**
  5. * 下拉内容
  6. */
  7. private String[] source;
  9. /**
  10. * 设置下拉框的起始行，默认为第二行
  11. */
  12. private int firstRow;
  14. /**
  15. * 设置下拉框的结束行，默认为最后一行
  16. */
  17. private int lastRow;
  19. public String[] resolveSelectedSource(ExcelSelected excelSelected) {
  20. if (excelSelected == null) {
  21. return null;
  22. }
  24. // 获取固定下拉框的内容
  25. String[] source = excelSelected.source();
  26. if (source.length > 0) {
  27. return source;
  28. }
  30. // 获取动态下拉框的内容
  31. Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
  32. if (classes.length > 0) {
  33. try {
  34. ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
  35. String[] dynamicSelectSource = excelDynamicSelect.getSource();
  36. if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
  37. return dynamicSelectSource;
  38. }
  39. } catch (InstantiationException | IllegalAccessException e) {
  40. log.error("解析动态下拉框数据异常", e);
  41. }
  42. }
  43. return null;
  44. }
  46. }

十、导出接口

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

十一、导出效果图

完结，有问题欢迎指正，QQ 781999475

标签:
声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。

在线投稿：投稿 站长QQ：1888636

后台-插件-广告管理-内容页尾部广告（手机）
相关文章
关注我们

扫一扫关注我们，了解最新精彩内容

搜索
排行榜
最近发表
标签列表