java对excle操作：下载、上传以及上传中错误数据动态生成excle给用户下载
工作中经常遇到excle文件的上传下载，这里就总结一下相关的操作，尤其是最后一个方法“上传excle文件校验数据格式，挑出格式错误的数据”网上没有找到相关的例子，自己组合改写了一下，有更好的改进欢迎大家评论交流
其中包括
1、静态excle文件下载
2、excle文件上传
3、动态生成的excle文件下载
4、上传excle文件校验数据格式，挑出格式错误的数据，并动态生成excle文件让用户下载（业务场景：用户下载错误数据按照提示修改后，再次上传文件）
下面直接上代码
一、依赖
pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.1.6.RELEASE</version>
</dependency>
<!-- excle操作-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.8</version>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
</dependencies>
二、相关bean
package com.example.demo.excle.controller; import java.util.Date; public class User { String name; int age; Date birthday; public User(String name, int age, Date birthday) { this.name = name; this.age = age; this.birthday = birthday; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } }
package com.example.demo.excle.controller; public class Temp { String username; String realName; String deptCode; String deptName; String text; public Temp(String username, String realName, String deptCode, String deptName, String text) { this.username = username; this.realName = realName; this.deptCode = deptCode; this.deptName = deptName; this.text = text; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getRealName() { return realName; } public void setRealName(String realName) { this.realName = realName; } public String getDeptCode() { return deptCode; } public void setDeptCode(String deptCode) { this.deptCode = deptCode; } public String getDeptName() { return deptName; } public void setDeptName(String deptName) { this.deptName = deptName; } public String getText() { return text; } public void setText(String text) { this.text = text; } }
三、Controller（实现）
package com.example.demo.excle.controller; import cn.hutool.core.io.IoUtil; import cn.hutool.poi.excel.ExcelReader; import cn.hutool.poi.excel.ExcelUtil; import cn.hutool.poi.excel.ExcelWriter; import java.io.*; import java.nio.charset.StandardCharsets; import java.util.*; import org.apache.commons.lang3.ArrayUtils; import org.springframework.util.ResourceUtils; import org.springframework.web.bind.annotation.*; import org.springframework.web.multipart.MultipartFile; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.net.URLEncoder; @RestController @RequestMapping("/excle") public class ExcelController { //下载excle静态模板 @GetMapping("/template") @ResponseBody public void downloadExcel(HttpServletResponse response, HttpServletRequest request) { try { //获取文件的路径 File file = ResourceUtils.getFile("classpath:excle/用户信息表.xlsx"); String fileName = "用户信息表.xlsx" ; // 文件的默认保存名 // 读到流中 InputStream inStream = new FileInputStream(file);//文件的存放路径 // 设置输出的格式 response.reset(); response.setContentType("bin"); response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户信息表.xlsx", "UTF-8")); // 循环取出流中的数据 byte[] b = new byte[200]; int len; while ((len = inStream.read(b)) > 0){ response.getOutputStream().write(b, 0, len); } inStream.close(); } catch (IOException e) { e.printStackTrace(); } } //下载动态数据 @GetMapping("/download") @ResponseBody public void download(HttpServletResponse response) { //模拟数据 List<User> list= new ArrayList<User>(); list.add(new User("zhangsan",12,new Date())); list.add(new User("zhangsan1",18,new Date())); list.add(new User("zhangsan2",23,new Date())); list.add(new User("zhangsan3",25,new Date())); list.add(new User("zhangsan4",30,new Date())); list.add(new User("zhangsan5",36, new Date())); //通过工具类创建writer，默认创建xls格式 ExcelWriter writer = ExcelUtil.getWriter(); //自定义标题名 writer.addHeaderAlias("name","姓名"); writer.addHeaderAlias("age","年龄"); writer.addHeaderAlias("birthday","生日"); // 合并单元格后的标题行，使用默认标题样式 writer.merge(3,"申请人员信息"); //一次性写出内容，使用默认样式，强制输出标题 //Iterable<?> data writer.write(list,true); //response为HttpServeltReponse对象 response.setContentType("application/vnd.ms-excel;charset=utf-8"); //弹出下载对话框的文件名，不能为中文，中文请自行编码 String name = "申请学院"; try { name = URLEncoder.encode(name, StandardCharsets.UTF_8.toString()); response.setHeader("Content-Disposition","attachment;filename="+name+".xls"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } ServletOutputStream out= null; try{ out = response.getOutputStream(); writer.flush(out,true); }catch (IOException e){ e.printStackTrace(); }finally { //关闭writer,释放内存 writer.close(); } IoUtil.close(out); } //上传并解析数据 @PostMapping("/upload") @ResponseBody public String upload(@RequestParam("file") MultipartFile multipartFile) { String suffex = multipartFile.getOriginalFilename().split("\\.")[1]; if (!"xlsx".equals(suffex)) { return "导入失败,只支持xlsx文件上传"; } if (Objects.isNull(multipartFile)) { return "导入失败,上传文件为空"; } //得到文件流 InputStream inputStream = null; StringBuffer errMsg = new StringBuffer(""); try { inputStream = multipartFile.getResource().getInputStream(); String[] head={"用户账号","用户姓名","部门代码","部门名称"}; String[] headerAlias={"username","realName","deptCode","deptName"}; ExcelReader reader = ExcelUtil.getReader(inputStream); List<Object> header=reader.readRow(0); //替换表头关键字 if(ArrayUtils.isEmpty(head)||ArrayUtils.isEmpty(headerAlias)||head.length!=headerAlias.length){ return null; }else{ for(int i=0;i<head.length;i++){ if(head[i].equals(header.get(i))){ reader.addHeaderAlias(head[i],headerAlias[i]); }else{ return null; } } } //读取指点行开始的表数据（以下介绍的三个参数也可以使用动态传入，根据个人业务情况修改） //1：表头所在行数 2：数据开始读取位置 Integer.MAX_VALUE:数据读取结束行位置 List<Map<String,Object>> read = reader.read(0,1,Integer.MAX_VALUE); System.out.println(read); } catch (Exception e) { System.out.println(e.getMessage()); IoUtil.close(inputStream); } if (errMsg.length() > 0) { return errMsg.toString() + "行数据导入失败,其余导入成功."; } return "导入成功"; } //上传excle并且把错误的数据标识出来 @PostMapping("/uploadAndCheck") @ResponseBody public String uploadAndCheck(@RequestParam("file") MultipartFile multipartFile,HttpServletResponse response) { String suffex = multipartFile.getOriginalFilename().split("\\.")[1]; int correctNum = 0 ; int wrongNum = 0; if (!"xlsx".equals(suffex)) { return "导入失败,只支持xlsx文件上传"; } if (Objects.isNull(multipartFile)) { return "导入失败,上传文件为空"; } //得到文件流 InputStream inputStream = null; StringBuffer errMsg = new StringBuffer(""); try { inputStream = multipartFile.getResource().getInputStream(); String[] head={"用户账号","用户姓名","部门代码","部门名称"}; String[] headerAlias={"username","realName","deptCode","deptName"}; ExcelReader reader = ExcelUtil.getReader(inputStream); List<Object> header=reader.readRow(0); //替换表头关键字 if(ArrayUtils.isEmpty(head)||ArrayUtils.isEmpty(headerAlias)||head.length!=headerAlias.length){ return null; }else{ for(int i=0;i<head.length;i++){ if(head[i].equals(header.get(i))){ reader.addHeaderAlias(head[i],headerAlias[i]); }else{ return null; } } } //读取指点行开始的表数据（以下介绍的三个参数也可以使用动态传入，根据个人业务情况修改） //1：表头所在行数 2：数据开始读取位置 Integer.MAX_VALUE:数据读取结束行位置 List<Map<String,Object>> read = reader.read(0,1,Integer.MAX_VALUE); int size = read.size(); List<Temp> list= new ArrayList<Temp>(); for(int i=0; i<size; i++){ Map map = read.get(i); String username = map.get("username").toString(); String realName = map.get("realName").toString(); String deptCode = map.get("deptCode").toString(); String deptName = map.get("deptName").toString(); //假设该条数据中部门编码长度不符合标准 if(deptCode.length()>3){ String text = "1,部门编码长度不对"; list.add(new Temp(username,realName,deptCode,deptName,text)); wrongNum ++; } } correctNum = size - wrongNum; System.out.println(read); //生成错误数据的excle begin //通过工具类创建writer，默认创建xls格式 ExcelWriter writer = ExcelUtil.getWriter(); writer.addHeaderAlias("username","用户账号"); writer.addHeaderAlias("realName","用户姓名"); writer.addHeaderAlias("deptCode","部门代码"); writer.addHeaderAlias("deptName","部门名称"); writer.addHeaderAlias("text","错误提示"); // 合并单元格后的标题行，使用默认标题样式 writer.merge(4,"错误数据列表"); //一次性写出内容，使用默认样式，强制输出标题 //Iterable<?> data writer.write(list,true); //response为HttpServeltReponse对象 response.setContentType("application/vnd.ms-excel;charset=utf-8"); //弹出下载对话框的文件名，不能为中文，中文请自行编码 String name = "excleWrong"; try { name = URLEncoder.encode(name, StandardCharsets.UTF_8.toString()); response.setHeader("Content-Disposition","attachment;filename="+name+".xls"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } ServletOutputStream out= null; try{ out = response.getOutputStream(); writer.flush(out,true); }catch (IOException e){ e.printStackTrace(); }finally { //关闭writer,释放内存 writer.close(); } IoUtil.close(out); //生成错误数据的excle end } catch (Exception e) { System.out.println(e.getMessage()); IoUtil.close(inputStream); } if (errMsg.length() > 0) { return errMsg.toString() + "行数据导入失败,其余导入成功."; } System.out.println("导入成功数据"+correctNum+"条，错误数据"+wrongNum+"条，错误数据请下载修改后重新上传!"); return "导入成功数据"+correctNum+"条，错误数据"+wrongNum+"条，错误数据请下载修改后重新上传!"; } }
注：
1、 Controller 中downloadExcel方法里模板的存放路径
2、Controller 中upload方法中上传文件的内容
3、Controller 中uploadAndCheck方法中上传文件的内容和让用户下载数据格式有问题的数据
上传文件与第二步的相同
让用户下载数据格式有问题的数据如下：
资源丰富的的网盘资源：网盘资源大全！ 推荐一个适合零基础学习SQL的网站：不用安装数据库，在线轻松学习SQL！
