本文通过自定义注解实现excel数据导入导出、以及设置excel文件中列数据验证，即用户在excel文件中输入数据时就可以对数据格式验证是否符合，节省了程序中过多的数据验证操作，注解还额外提供了一系列功能请自行查看本文提供代码包含了详细注释
工具类项目链接 项目中有许多工具类用不到的话可以不用关注，重点观看本文讲解的自定义excel注解数据处理，工具类中使用了poi依赖自行添加
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>
项目拉取下来之后重点观看以下截图中标识的类
将工具类迁移到自己项目中之后使用工具类下载存在多列联动效果的模板案例如下
实体类使用excel注解标注字段
import java.util.Date;import java.util.List;import mon.annotation.Excel;import mon.annotation.Excel.ColumnType;import mon.annotation.Excel.Type;import mon.annotation.Excels;public class SysDept {private static final long serialVersionUID = 1L;/** 如实体类中有属性是其他对象，可以用 @Excels 指定该对象里面需要导出或导入属性即可*/@Excels({@Excel(name = "用户名称", targetAttr = "userName", type = Type.EXPORT),@Excel(name = "用户性别", targetAttr = "sex", type = Type.EXPORT)})private SysUser user;/** 部门ID */private Long deptId;/** 父部门ID */private Long parentId;/** 省 */@Excel(name = "省",sort = 1)private String province;/** 市 */@Excel(name = "市",sort = 2)private String city;/** 区 */@Excel(name = "区",sort = 3)private String areg;/** 部门名称 */@Excel(name = "部门名称",sort = 4)private String deptName;/** 联系电话 */@Excel(name = "联系电话",width = 25,checkType = ExcelCheckType.FORMULA,checkDataType = ExcelCheckDataType.FORMULA,formula = ExcelDataCheckFormula.IS_PHONE_FORMULA,errorContent = "请输入正确手机号: 格式为数字类型11位数,不可包含小数点",sort = 5)private String phone;/** 时间 */@Excel(name = "时间", width = 30, dateFormat = "yyyy/MM/dd",checkType = ExcelCheckType.FORMULA,checkDataType = ExcelCheckDataType.FORMULA,formula = ExcelDataCheckFormula.IS_NUMBER_FORMULA,errorContent="请根据格式 yyyy/MM/dd 输入日期，示例: /12/12",sort = 6)private Date createTime;/** 子部门 */private List<SysDept> children = new ArrayList<SysDept>();public String getProvince() {return province;}public void setProvince(String province) {this.province = province;}public String getCity() {return city;}public void setCity(String city) {this.city = city;}public String getAreg() {return areg;}public void setAreg(String areg) {this.areg = areg;}public Long getDeptId() {return deptId;}public void setDeptId(Long deptId) {this.deptId = deptId;}public Long getParentId() {return parentId;}public void setParentId(Long parentId) {this.parentId = parentId;}public String getDeptName() {return deptName;}public void setDeptName(String deptName) {this.deptName = deptName;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}public List<SysDept> getChildren() {return children;}public void setChildren(List<SysDept> children) {this.children = children;}public SysUser getUser(){return user;}public void setUser(SysUser user){this.user = user;}}
后端代码使用案例，浏览器直接访问该接口地址即可下载excel文件
/*** 下载数据导入模板文件* @return*/@GetMapping("/importTemplate")public void importTemplate(HttpServletRequest request,HttpServletResponse response) {ExcelUtil<SysDept> util = new ExcelUtil<SysDept>(SysDept.class);// 查询所有的省名称List<String> provNameList = new ArrayList<String>();provNameList.add("广东省");provNameList.add("湖南省");// 整理数据，放入一个Map中，mapkey存放父地点，value 存放该地点下的子区域Map<String, List<String>> siteMap = new HashMap<String, List<String>>();siteMap.put("广东省", Lists.newArrayList("广州市", "深圳市"));siteMap.put("深圳市", Lists.newArrayList("光明新区", "宝安区"));siteMap.put("广州市", Lists.newArrayList("天河区", "白云区"));siteMap.put("湖南省", Lists.newArrayList("长沙市", "常德市"));siteMap.put("长沙市", Lists.newArrayList("来安县", "凤阳县"));siteMap.put("常德市", Lists.newArrayList("武陵区", "鼎城区"));Map<Integer, Integer> columnRelationships = new HashMap<>();columnRelationships.put(-1,0); //顶级父级columnRelationships.put(0,1); // 第二级columnRelationships.put(1,2); // 第三级util.importTemplateAndMultiLevelLinkageMenuExcel("excel模板", provNameList,siteMap, columnRelationships,response,request);}
该案例代码最后生成的excel文件模板在拉取的项目中自行查看效果
使用工具类下载无多列联动效果的excel文件案例如下
@GetMapping("/importTemplate")public void importTemplate(HttpServletResponse response,HttpServletRequest request){ExcelUtil<SysUser> util = new ExcelUtil<SysUser>(SysUser.class);util.importTemplateExcel("用户数据",response,request);}
使用工具类导入数据代码案例代码如下
工具类会把excel中数据转化成list集合后续入库操作自行处理
@PostMapping("/importData")public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception{ExcelUtil<SysUser> util = new ExcelUtil<SysUser>(SysUser.class);List<SysUser> userList = util.importExcel(file.getInputStream());
将list数据导出到excel文件下载
public void export(SysUser user, HttpServletResponse response, HttpServletRequest request){List<SysUser> list = userService.selectUserList(user);ExcelUtil<SysUser> util = new ExcelUtil<SysUser>(SysUser.class);util.exportExcel(list, "用户数据",response,request);}
