devbox
笔触狂放9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：笔触狂放9 | 2024-05-18 23:54:15

赞

踩

easyexcel 下拉框

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

直接开始

一、首先引入依赖   easyexcel依赖

  1. <!--easyexcel依赖-->
  2.         <dependency>
  3.             <groupId>com.alibaba</groupId>
  4.             <artifactId>easyexcel</artifactId>
  5.             <version>2.2.10</version>
  6.         </dependency>

二、创建自定义注解

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

三、解析自定义注解接口

  1.  
  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;
  10.  
  11. import java.io.Serializable;
  12.  
  13.  
  14. @Data
  15. @HeadRowHeight(20)
  16. @ContentRowHeight(18)
  17. public class TeacherExcel implements Serializable {
  18.  
  19. 	private static final long serialVersionUID = 1L;
  20.  
  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;
  39.  
  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;
  84.  
  85.  
  86. }

五、重点是以下的代码

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

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

  1.  
  2. import java.util.List;
  3. import java.util.stream.Collectors;
  4.  
  5. /**
  6.  * 岗位类别下拉选数据实现类
  7.  */
  8. public class StationlateServiceImpl implements ExcelDynamicSelect {
  9.  
  10. 	private static IDictClient dictClient;
  11.  
  12.  
  13. 	static {
  14. 		dictClient = SpringUtil.getBean(IDictClient.class);
  15. 	}
  16.  
  17.  
  18.  
  19. 	@Override
  20. 	public String[] getSource() {
  21. 		R<List<Dict>> 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;
  6.  
  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;
  12.  
  13.  
  14. @Slf4j
  15. public class EasyExcelUtil {
  16.  
  17. 	/**
  18. 	 * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
  19. 	 * @param head 导出的表头信息和配置
  20. 	 * @param sheetNo sheet索引
  21. 	 * @param sheetName sheet名称
  22. 	 * @param <T> 泛型
  23. 	 * @return sheet页
  24. 	 */
  25. 	public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
  26. 		Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);
  27.  
  28. 		return EasyExcel.writerSheet(sheetNo, sheetName)
  29. 			.head(head)
  30. 			.registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
  31. 			.build();
  32. 	}
  33.  
  34. 	/**
  35. 	 * 解析表头类中的下拉注解
  36. 	 * @param head 表头类
  37. 	 * @param <T> 泛型
  38. 	 * @return Map<下拉框列索引, 下拉框内容> map
  39. 	 */
  40. 	private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
  41. 		Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();
  42.  
  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. 	}
  67.  
  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. 		//$结尾
  86.  
  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);
  97.  
  98. 		//判断第18位校验值
  99. 		if (matches) {
  100.  
  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. 					}
  121.  
  122. 				} catch (Exception e) {
  123. 					e.printStackTrace();
  124. 					return false;
  125. 				}
  126. 			}
  127. 			return false;
  128. 		}
  129. 		return matches;
  130. 	}
  131.  
  132.  
  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. 	}
  146.  
  147. }

七、SelectedSheetWriteHandler

  1.  
  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;
  12.  
  13. import java.util.Map;
  14.  
  15. @Data
  16. @AllArgsConstructor
  17. public class SelectedSheetWriteHandler implements SheetWriteHandler {
  18.  
  19. 	private final Map<Integer, ExcelSelectedResolve> selectedMap;
  20.  
  21. 	/**
  22. 	 * 设置阈值，避免生成的导入模板下拉值获取不到，可自行设置数量大小
  23. 	 */
  24. 	private static final Integer LIMIT_NUMBER = 25;
  25.  
  26. 	/**
  27. 	 * Called before create the sheet
  28. 	 */
  29. 	@Override
  30. 	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  31.  
  32. 	}
  33.  
  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. 	}
  83.  
  84.  
  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;
  8.  
  9. 	/**
  10. 	 * 设置下拉框的起始行，默认为第二行
  11. 	 */
  12. 	private int firstRow;
  13.  
  14. 	/**
  15. 	 * 设置下拉框的结束行，默认为最后一行
  16. 	 */
  17. 	private int lastRow;
  18.  
  19. 	public String[] resolveSelectedSource(ExcelSelected excelSelected) {
  20. 		if (excelSelected == null) {
  21. 			return null;
  22. 		}
  23.  
  24. 		// 获取固定下拉框的内容
  25. 		String[] source = excelSelected.source();
  26. 		if (source.length > 0) {
  27. 			return source;
  28. 		}
  29.  
  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. 	}
  45.  
  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");
  23.  
  24. 			ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
  25. 			WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(TeacherExcel.class, 0, "教师入职信息数据表");
  26. 			excelWriter.write(new ArrayList<String>(), writeSheet);
  27. 			excelWriter.finish();
  28. 		} catch (UnsupportedEncodingException e) {
  29. 			e.printStackTrace();
  30. 		} catch (IOException e) {
  31. 			e.printStackTrace();
  32. 		}
  33. 	}

十一、导出效果图

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/590485
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号