最好的小说网站排名,做旅游网站的写手,中小企业服务中心网站建设,专业网站建设公司兴田德润简介作者#xff1a;后端小肥肠 #x1f347; 我写过的文章中的相关代码放到了gitee#xff0c;地址#xff1a;xfc-fdw-cloud: 公共解决方案 #x1f34a; 有疑问可私信或评论区联系我。 #x1f951; 创作不易未经允许严禁转载。 姊妹篇#xff1a; 基于AOP的数据字典实现… 作者后端小肥肠 我写过的文章中的相关代码放到了gitee地址xfc-fdw-cloud: 公共解决方案 有疑问可私信或评论区联系我。 创作不易未经允许严禁转载。 姊妹篇 基于AOP的数据字典实现实现前端下拉框的可配置更新_数据字典下拉框怎么写-CSDN博客 目录 1. 前言
2. Fastexcel介绍
3. 技术实现
3.1. 表结构及实体类说明
3.2. 适配PostgresSQL中text[]类型handler编写
3.3. 注解编写
3.4. 动态设置下拉框核心工具类编写 3.5. 制作多选下拉框exel模板
3.6. 导出模板方法
3.7 导入数据方法
4. 源码地址
5. 结语 1. 前言
在当今的软件开发中数据的导入与导出是常见的需求尤其是在企业级应用中Excel文件作为数据交互的一种重要形式被广泛使用。传统的Excel导入导出功能虽然基本满足需求但在处理大数据量或需要动态配置时往往显得效率低下且灵活性不足。
本文将围绕在Java中基于实体类的高效Excel数据导入导出展开介绍如何利用FastExcel这一库实现高性能和灵活的Excel处理。同时结合动态下拉框和多选下拉框的设置使得数据的导入导出不仅高效还能具备较强的可定制性和交互性。通过本篇文章你将能够掌握在Java中使用实体类驱动Excel导入导出的技术并学会如何在系统中动态生成下拉框和多选框的配置。
2. Fastexcel介绍
FastExcel 是一个高性能的 Java 库旨在提供高效的 Excel 文件操作尤其是在处理大数据量时其性能远超常见的POI库。相比其他 Excel 处理库FastExcel 采用了更加优化的内存管理和流式处理方式使得它在内存占用和速度上具有显著优势。尤其在导入导出大量数据时FastExcel 可以有效避免内存溢出和性能瓶颈保证程序的稳定运行。
FastExcel 的特点
高性能 快速的读取和写入速度特别适用于大数据量的 Excel 文件。低内存占用 采用流式读取和写入的方式极大地减少了内存的使用。简洁易用 相较于其他复杂的Excel操作库FastExcel提供了简洁的API接口易于上手。Excel文件格式支持 支持 .xlsx 格式的文件且兼容大部分常见的 Excel 文件操作需求。动态功能扩展 可以灵活地与 Java 实体类进行绑定支持动态生成表头、表格内容和格式设置。 为什么选择FastExcel
在实际开发中Excel文件的导入导出经常用于大规模的数据交换尤其是在财务、报表等领域。对于这些场景传统的 Excel 处理库如 Apache POI可能在面对大数据量时会出现性能瓶颈尤其是在需要频繁进行读写操作的情况下。而FastExcel通过采用流式读取和写入的方式有效解决了这一问题并且通过内存管理优化使得应用能够在处理大量数据时仍保持高效运行。
由于FastExcel的这些优势它成为了许多Java开发者在实现Excel数据导入导出时的首选工具尤其是对于需要处理海量数据或需要提高导入导出性能的应用场景。
3. 技术实现
模拟需求本案例模拟导出学生的相关信息包括学号、姓名、性别、父母职业类型和家庭住址所属区域等内容。具体来说学生信息包含以下字段
学号唯一标识学生的编号作为数据的主键。姓名学生的姓名文本类型字段。性别此字段通过下拉框进行选择支持男、女等选项方便用户快速选择。父母职业类型此字段也是一个下拉框列出了多种常见的职业类型便于系统自动识别父母的职业分类。家庭住址所属区域该字段设置为多选下拉框支持学生家庭地址涉及多个区域的情况。例如假设某些富裕学生的家庭可能在不同城市或区域拥有多个房产因此可以选择多个区域。此设置充分考虑了复杂的地址情况提高了数据录入的灵活性。
本文将实现excel文件下拉框基于java程序动态设置并支持多选下拉框。
动态下拉框的实现步骤如下
1. 创建支持宏的xlsm模板文件
2. 编写vba代码
3. 基于java代码动态写入下拉框数据
4. 导出动态设置下拉框的excel模板 3.1. 表结构及实体类说明
1. PostgreSQL数据库表结构SQL
我这里创建了一个比较简单的数据字典表存储下拉框数据完整版数据字典请移步基于AOP的数据字典实现实现前端下拉框的可配置更新_数据字典下拉框怎么写-CSDN博客
CREATE TABLE students (student_id VARCHAR(20) PRIMARY KEY, -- 学号name VARCHAR(100) NOT NULL, -- 姓名gender VARCHAR(10), -- 性别parent_occupation VARCHAR(100), -- 父母职业类型home_area TEXT[] -- 家庭住址所属区域 (使用数组类型来存储多个区域)
);-- 创建一个独立的字典表用于存储性别、父母职业类型等下拉框选项
CREATE TABLE gender_options (id SERIAL PRIMARY KEY,gender VARCHAR(10) NOT NULL
);CREATE TABLE parent_occupation_options (id SERIAL PRIMARY KEY,occupation VARCHAR(100) NOT NULL
);-- 插入默认的字典数据
INSERT INTO gender_options (gender) VALUES(男),(女);INSERT INTO parent_occupation_options (occupation) VALUES(教师),(医生),(工程师),(律师),(其他);在student表中插入10条数据
INSERT INTO students (student_id, name, gender, parent_occupation, home_area)
VALUES
(S10001, 张三, 男, 教师, {北京, 上海}),
(S10002, 李四, 女, 医生, {广州, 深圳}),
(S10003, 王五, 男, 工程师, {北京}),
(S10004, 赵六, 女, 律师, {杭州, 南京}),
(S10005, 孙七, 男, 商人, {上海, 广州}),
(S10006, 周八, 女, 公务员, {北京, 武汉}),
(S10007, 吴九, 男, 教师, {成都}),
(S10008, 郑十, 女, 护士, {重庆, 成都}),
(S10009, 冯十一, 男, 程序员, {深圳, 上海}),
(S10010, 陈十二, 女, 医生, {北京, 上海, 广州});2. 实体类
Data
EqualsAndHashCode(callSuper false)
Accessors(chain true)
ApiModel(valueStudents对象, description)
TableName(value students,autoResultMap true)
public class Students implements Serializable {private static final long serialVersionUID 1L;TableId(value student_id, type IdType.ASSIGN_ID)ExcelProperty(value 学号,index 0)private String studentId;ExcelProperty(value 姓名,index 1)private String name;ExcelProperty(value 性别,index 2)DropDownSetField(source {男, 女})private String gender;ExcelProperty(value 父母职业, index 3)DropDownSetField(dynamicSource ParentOccupationOptions.class)private String parentOccupation;ExcelProperty(value 所属区域,index 4,converter SimpleStringToListConverter.class)DropDownSetField(source {东城区, 西城区, 海淀区, 朝阳区, 丰台区, 石景山区, 门头沟区, 房山区, 通州区, 顺义区, 昌平区, 大兴区, 怀柔区, 平谷区, 密云区, 延庆区})TableField(typeHandler StringArrayTypeHandler.class)private ListString homeArea;}3.2. 适配PostgresSQL中text[]类型handler编写
ConditionalOnClass({BaseTypeHandler.class})
MappedTypes({List.class})
public class StringArrayTypeHandler extends BaseTypeHandlerListString {Overridepublic void setNonNullParameter(PreparedStatement ps, int i, ListString parameter, JdbcType jdbcType)throws SQLException {Connection conn ps.getConnection();Array array conn.createArrayOf(text, parameter.toArray(new String[0]));ps.setArray(i, array);}Overridepublic ListString getNullableResult(ResultSet rs, String columnName) throws SQLException {Array array rs.getArray(columnName);return array null ? null : Arrays.asList((String[]) array.getArray());}Overridepublic ListString getNullableResult(ResultSet rs, int columnIndex) throws SQLException {Array array rs.getArray(columnIndex);return array null ? null : Arrays.asList((String[]) array.getArray());}Overridepublic ListString getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {Array array cs.getArray(columnIndex);return array null ? null : Arrays.asList((String[]) array.getArray());}
}
这个类是一个 MyBatis 的类型处理器TypeHandler主要功能是
数据转换实现 PostgreSQL 数据库中的数组类型与 Java 中的 ListString 类型之间的双向转换
Java - DB将 ListString 转换为 PostgreSQL 的 text[] 数组类型
DB - Java将 PostgreSQL 的 text[] 数组类型转换为 ListString
应用场景适用于需要在单个字段中存储多个值的情况如学生所属区域可以属于多个区域的存储和读取
技术特点
继承自 BaseTypeHandlerListString
使用 MappedTypes 注解指定处理 List 类型
使用 ConditionalOnClass 实现条件化配置
3.3. 注解编写
Documented
Target(ElementType.FIELD)
Retention(RetentionPolicy.RUNTIME)
public interface DropDownSetField {String[] source() default {};String value() default ;Class?[] dynamicSource() default {};
}3.4. 动态设置下拉框核心工具类编写
Component
Slf4j
public class ExchangeSheetUtils {Autowiredprivate IDropDownDataService dropDownDataService;private static final int MAX_EXCEL_ROWS 65536;private static final String HIDDEN_SHEET_NAME 字典sheet;private final char[] alphabet new char[]{A, B, C, D, E, F, G, H, I, J, K, L,M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z};// 使用ThreadLocal来确保线程安全private final ThreadLocalListString dropDownArrays ThreadLocal.withInitial(ArrayList::new);private final ThreadLocalMapInteger, ListString dropDownMap ThreadLocal.withInitial(HashMap::new);// 在方法结束时清理ThreadLocalpublic void clearThreadLocals() {dropDownArrays.get().clear();dropDownMap.get().clear();}/*** 根据实体类解析字段并获取动态或固定的下拉数据。*/public void getEntityField(Class? clazz) {try {Field[] fields clazz.getDeclaredFields();for (Field field : fields) {processDropDownField(field);}} catch (Exception e) {log.error(处理实体类字段失败, e);clearThreadLocals();throw new RuntimeException(处理实体类字段失败, e);}}/*** 处理下拉框字段*/private void processDropDownField(Field field) {ExcelProperty excelProperty field.getAnnotation(ExcelProperty.class);if (excelProperty null || excelProperty.value().length 0) {log.warn(字段 {} 缺少 ExcelProperty 注解或 value 为空, field.getName());return;}String columnName excelProperty.value()[0];dropDownArrays.get().add(columnName);DropDownSetField dropDownSetField field.getAnnotation(DropDownSetField.class);if (dropDownSetField ! null) {ListString dropDownOptions new ArrayList();if (dropDownSetField.dynamicSource().length 0) {dropDownOptions getDropDownDataFromDynamicSource(dropDownSetField.dynamicSource());} else if (dropDownSetField.source().length 0) {dropDownOptions Arrays.asList(dropDownSetField.source());}if (!dropDownOptions.isEmpty()) {int columnIndex dropDownArrays.get().size() - 1;dropDownMap.get().put(columnIndex, dropDownOptions);}}}/*** 从动态数据源获取下拉数据*/private ListString getDropDownDataFromDynamicSource(Class?[] dynamicSourceClasses) {ListString dropDownOptions new ArrayList();for (Class? dynamicSourceClass : dynamicSourceClasses) {try {// 调用动态数据源的接口获取下拉数据例如通过远程接口ListString data dropDownDataService.fetchDynamicDropDownData(dynamicSourceClass);dropDownOptions.addAll(data);} catch (Exception e) {log.error(获取动态下拉框数据失败错误信息: {}, e.getMessage());}}return dropDownOptions;}/*** 创建并更新隐藏Sheet页添加下拉框*/public void updateHiddenSheet(Sheet curSheet, Workbook templateWorkbook) {if (dropDownMap.get().isEmpty()) {return; // 如果没有下拉框数据则不进行处理}DataValidationHelper helper curSheet.getDataValidationHelper();String hiddenSheetName HIDDEN_SHEET_NAME;Sheet hiddenSheet templateWorkbook.createSheet(hiddenSheetName);hideOtherSheets(templateWorkbook);clearOldNamedRanges(templateWorkbook);// 填充隐藏Sheet的数据SetMap.EntryInteger, ListString entrySet dropDownMap.get().entrySet();for (Map.EntryInteger, ListString entry : entrySet) {createDropDownList(helper, hiddenSheet, entry);}}/*** 隐藏所有除第一个外的Sheet*/private void hideOtherSheets(Workbook templateWorkbook) {int totalSheets templateWorkbook.getNumberOfSheets();for (int i 1; i totalSheets; i) {templateWorkbook.setSheetHidden(i, true);}}/*** 清除之前的命名范围*/private void clearOldNamedRanges(Workbook templateWorkbook) {for (int i 0; i 26; i) {Name workbookName templateWorkbook.getName(dict i);if (workbookName ! null) {templateWorkbook.removeName(workbookName); // 使用 Name 对象删除}}}/*** 创建并配置下拉框*/private void createDropDownList(DataValidationHelper helper, Sheet hiddenSheet, Map.EntryInteger, ListString entry) {Integer column entry.getKey();ListString values entry.getValue();// 填充数据到隐藏sheetint rowLen values.size();for (int i 0; i rowLen; i) {Row row hiddenSheet.getRow(i);if (row null) {row hiddenSheet.createRow(i);}Cell cell row.createCell(column);cell.setCellValue(values.get(i));}String excelColumn getExcelColumn(column);String refersTo HIDDEN_SHEET_NAME !$ excelColumn $1:$ excelColumn $ rowLen;// 创建命名范围Name name hiddenSheet.getWorkbook().createName();name.setNameName(dict column);name.setRefersToFormula(refersTo);// 获取第一个sheet主sheetSheet mainSheet hiddenSheet.getWorkbook().getSheetAt(0);// 创建数据验证DataValidationConstraint constraint helper.createFormulaListConstraint(dict column);CellRangeAddressList addressList new CellRangeAddressList(1, MAX_EXCEL_ROWS, column, column);DataValidation validation helper.createValidation(constraint, addressList);// 设置验证属性validation.setSuppressDropDownArrow(true);validation.setShowErrorBox(true);validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.createErrorBox(提示, 此值与单元格定义格式不一致);// 将验证添加到主sheetmainSheet.addValidationData(validation);}/*** 将数字列转化为字母列*/private String getExcelColumn(int num) {int len alphabet.length;int first num / len;int second num % len;if (num len) {return String.valueOf(alphabet[num]);} else {return String.valueOf(alphabet[first - 1]) alphabet[second - 1];}}/*** 设置数据Sheet页的初始化*/public void setDataSheet(Sheet sheet, Workbook templateWorkbook) {Row row sheet.createRow(0);ListString arrays dropDownArrays.get();for (int i 0; i arrays.size(); i) {row.createCell(i).setCellValue(arrays.get(i));}}}ExchangeSheetUtils 核心方法说明
1. getEntityField
public void getEntityField(Class? clazz)
功能解析实体类的字段注解收集下拉框配置信息处理流程 获取类的所有字段通过 processDropDownField 处理每个字段的注解将下拉框数据存入 ThreadLocal
2. processDropDownField
private void processDropDownField(Field field)
功能处理单个字段的下拉框配置处理流程 读取 ExcelProperty 注解获取列名读取 DropDownSetField 注解获取下拉选项将数据保存到 dropDownArrays 和 dropDownMap
3. updateHiddenSheet
public void updateHiddenSheet(Sheet curSheet, Workbook templateWorkbook)
功能创建和配置隐藏的数据字典sheet处理流程 创建隐藏sheet清理旧的命名范围通过 createDropDownList 设置下拉框
4. createDropDownList
private void createDropDownList(DataValidationHelper helper, Sheet hiddenSheet, Map.EntryInteger, ListString entry)
功能创建Excel下拉框处理流程 在隐藏sheet中填充下拉选项创建命名范围Named Range设置数据验证规则配置下拉框和错误提示
这些方法通过 ThreadLocal 实现线程安全通过 POI 提供的 API 实现 Excel 的各种操作最终生成一个带有下拉框的 Excel 模板文件。 3.5. 制作多选下拉框exel模板
1. 新建.xlsx文件 2. 点击顶部【文件】后点击【选项】 3. 在弹出的弹窗中点击【信任中心】选项页中的【信任中心设置】按钮 4. 开启宏 5. 另存为.xlsm文件 6. 编写VBA代码
选中Sheet右键弹出菜单选择【查看代码】将下面代码粘进去 Sub Worksheet_Change(ByVal Target As Range) 让数据有效性选择可以多选且不可重复Dim rngDV As RangeDim oldVal As StringDim newVal As String 如果修改的范围超过1个单元格则退出If Target.Count 1 Then GoTo exitHandlerOn Error Resume NextSet rngDV Cells.SpecialCells(xlCellTypeAllValidation)On Error GoTo exitHandlerIf rngDV Is Nothing Then GoTo exitHandlerIf Intersect(Target, rngDV) Is Nothing Then 如果目标单元格不在数据验证区域什么都不做ElseApplication.EnableEvents FalsenewVal Target.Value 假设字段映射如下 第3列是 gender 性别 第4列是 parentOccupation 父母职业类型 第5列是 homeArea 家庭住址所属区域 如果修改的是 gender 或 parentOccupation则是单选直接替换 如果修改的是 homeArea则是多选去重并追加If Target.Column 3 Or Target.Column 4 Then 对性别gender和父母职业类型parentOccupation做单选处理Application.UndooldVal Target.ValueTarget.Value newVal 如果原值与新值不同直接替换If oldVal newVal ThenTarget.Value newValEnd IfElseIf Target.Column 5 Then 对家庭住址所属区域homeArea做多选处理Application.UndooldVal Target.ValueTarget.Value newValIf oldVal Then 如果原值为空直接返回ElseIf newVal Then 如果新值为空什么都不做Else 去除重复项If InStr(1, oldVal, newVal) 0 Then 如果新值在旧值中已存在If InStr(1, oldVal, newVal) Len(newVal) - 1 Len(oldVal) Then 如果是最后一个选项重复则删除Target.Value Left(oldVal, Len(oldVal) - Len(newVal) - 1)Else 否则删除逗号后面的重复值Target.Value Replace(oldVal, newVal ,, )End IfElse 如果是新选项则追加Target.Value oldVal , newValEnd IfEnd IfEnd IfEnd IfEnd IfexitHandler:Application.EnableEvents True
End Sub
这是一个 Excel 工作表的 Worksheet_Change 事件处理程序主要实现了单元格数据验证的自定义处理逻辑
功能目标实现单选和多选下拉框的不同处理逻辑
具体实现
第3列性别和第4列父母职业实现单选功能新值直接替换旧值
第5列所属区域实现多选功能
允许多个选项用逗号分隔
自动去重避免重复选择
支持取消选择点击已选项可移除
使用 Application.Undo 和 Application.EnableEvents 确保操作的原子性和避免事件循环
该代码通过 VBA 扩展了 Excel 默认的下拉框功能使其支持更复杂的业务需求特别是实现了多选下拉框的去重和动态更新功能。
将上述步骤保存支持动态下拉框的模板文件.xlsm就制作完成了。
3.6. 导出模板方法 public void exportTemplate(HttpServletResponse response) {Workbook templateWorkbook null;FileInputStream fileInputStream null;try {// 设置响应头response.setContentType(application/vnd.ms-excel.sheet.macroEnabled.12);response.setCharacterEncoding(utf-8);String name 学生数据模板;response.setHeader(Content-Disposition, attachment; filename java.net.URLEncoder.encode(name, UTF-8) .xlsm);// 读取模板文件File file new File(D:/学生数据模板.xlsm);fileInputStream new FileInputStream(file);templateWorkbook WorkbookFactory.create(fileInputStream);// 获取数据 - 这三个方法的调用顺序不能变exchangeSheetUtils.getEntityField(Students.class);Sheet outputSheet templateWorkbook.getSheetAt(0);templateWorkbook.setSheetName(0, name);exchangeSheetUtils.updateHiddenSheet(outputSheet, templateWorkbook);exchangeSheetUtils.setDataSheet(outputSheet, templateWorkbook);// 输出文件templateWorkbook.write(response.getOutputStream());} catch (Exception e) {log.error(导出学生模板失败:e.getMessage(), e);throw new RuntimeException(导出模板失败, e);} finally {// 清理 ThreadLocal 资源exchangeSheetUtils.clearThreadLocals();// 关闭其他资源if (fileInputStream ! null) {try {fileInputStream.close();} catch (IOException e) {log.error(关闭文件流失败, e);}}if (templateWorkbook ! null) {try {templateWorkbook.close();} catch (IOException e) {log.error(关闭工作簿失败, e);}}}}
这是一个用于导出 Excel 模板文件的方法其核心功能是
读取预设的 Excel 模板文件D:/student.xlsm通过 ExchangeSheetUtils 工具类解析 Students 实体类的注解信息ExcelProperty 和 DropDownSetField设置下拉框和数据验证最后将处理好的模板文件包含表头、下拉框配置和 VBA 代码以 .xlsm 格式输出到 HTTP 响应流中。整个过程包含了完整的资源管理使用 try-finally 确保资源正确关闭和线程安全处理通过 clearThreadLocals 清理 ThreadLocal 资源。
关键步骤
设置响应头.xlsm 格式读取模板文件处理下拉框配置输出文件清理资源
3.7 导入数据方法 OverrideTransactional(rollbackFor Exception.class)public String importData(MultipartFile file) throws IOException {try {final ListStudents studentsList new ArrayList();// 使用Map方式读取数据EasyExcel.read(file.getInputStream()).sheet(0).headRowNumber(1) // 将表头行设置为1因为第0行是表头.registerReadListener(new AnalysisEventListenerMapInteger, String() {Overridepublic void invoke(MapInteger, String data, AnalysisContext context) {log.info(读取到一行数据: {}, JSON.toJSONString(data));// 手动转换为Students对象使用正确的keyStudents student new Students();student.setStudentId(data.get(0)); // 学号student.setName(data.get(1)); // 姓名student.setGender(data.get(2)); // 性别student.setParentOccupation(data.get(3)); // 家长职业// 使用与SimpleStringToListConverter相同的逻辑处理homeAreaString areaStr data.get(4);if (areaStr ! null !areaStr.trim().isEmpty()) {student.setHomeArea(Arrays.asList(areaStr.split(,)));}studentsList.add(student);}Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.info(所有数据解析完成共读取到 {} 条数据, studentsList.size());}}).doRead();if (CollectionUtils.isEmpty(studentsList)) {return Excel中没有数据;}// 保存数据this.saveBatch(studentsList);return 导入成功共导入 studentsList.size() 条数据;} catch (Exception e) {log.error(导入失败, e);throw e;}}
这个 importData 函数的主要功能是导入Excel文件中的学生数据。具体流程如下
使用 Transactional 注解确保数据导入的事务性如果出现异常会自动回滚
创建一个 studentsList 列表用于存储解析后的数据
使用 EasyExcel 读取上传的 Excel 文件
读取第一个 sheetsheet(0)
设置表头行号为1headRowNumber(1)
使用 Map 方式读取数据其中 key 是列索引0-4value 是单元格内容
在 invoke 方法中处理每一行数据
将 Map 数据手动转换为 Students 对象
特别处理 homeArea 字段将字符串用逗号分割转换为 List
最后批量保存数据到数据库saveBatch
如果过程中出现异常会记录错误日志并抛出异常触发事务回滚
4. 源码地址
源码里面有导出模板导入数据和导出数据三个接口实现了功能闭环完整代码
xfc-fdw-cloud: 公共解决方案
5. 结语
本文介绍了如何通过 FastExcel 实现高效的 Excel 数据导入导出基于实体类动态设置excel下拉框支持多选解决了实际开发中的常见需求。如有疑问欢迎在评论区留言我看到都会回复。
