pom.xml
关键jar包如下：
<!-- 文件上传 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.3</version>
</dependency>
<!-- apache poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
其中, commons-fileupload 适用于文件上传和下载的jar包, poi-ooxml 是支持操作excel文件的jar包.
spring-web.xml
需要在spring的配置文件中配置上传文件的解析器.
<!-- 4: 文件上传解析器 -->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!-- 设定默认编码 -->
<property name="defaultEncoding" value="UTF-8"/>
<!-- 设定文件上传的最大值为5MB, 5*1024*1024 = 5242880 -->
<property name="maxUploadSize" value="5242880"/>
<!-- 设定文件上传时写入内存的最大值, 如果小于这个参数不会生成临时文件, 默认为10240 -->
<property name="maxInMemorySize" value="40960"/>
<!-- 上传文件的临时路径 -->
<property name="uploadTempDir" value="upload/temp/"/>
<!-- 延迟文件解析 -->
<property name="resolveLazily" value="true" />
</bean>
单个excel文件上传
前端代码(使用thymeleaf模板引擎)
<div>
<p>单个excel文件上传</p>
<form th:action="@{/excel/import}" method="post" enctype="multipart/form-data">
<input type="file" name="excelFile"/>
<input type="submit" value="导入单个excel文件"/>
</form>
</div>
还可以使用 直接请求 后台controller 的方法：
window.location.href = 请求后台的路径
后端代码
这里使用了 POIUtil 工具类解析excel文件, 传送门
/** * * 从web页面导入单个excel文件 * * @param excelFile 上传的excel文件 * @param request Request对象 * @return 内容 * @throws IOException */ @PostMapping("/excel/import") @ResponseBody public List<ExcelUserInfo> importExcel(@RequestParam("excelFile") MultipartFile excelFile, HttpServletRequest request) throws IOException { // 1: 转存文件 if (!excelFile.isEmpty()) { /** * 这里的getRealPath("/")是打包之后的项目的根目录。 * 也就是 target项目名-1.0-SNAPSHOT */ String storePath = request.getSession().getServletContext().getRealPath("/") + "upload/temp/"; excelFile.transferTo(new File(storePath + excelFile.getOriginalFilename())); } // 2: 解析excel数据 List<String[]> excelData = POIUtil.readExcelFile(excelFile, 1); List<ExcelUserInfo> userInfoList = new ArrayList<>(); for (String[] arr : excelData) { ExcelUserInfo userInfo = new ExcelUserInfo(); userInfo.setId(Integer.valueOf(arr[0])); userInfo.setName(arr[1]); userInfo.setAge(Integer.valueOf(arr[2])); userInfoList.add(userInfo); } // 3: 输出excel数据 return userInfoList; }
多个excel文件上传
前端代码(使用thymeleaf模板引擎)
div>
<p>多个excel文件上传</p>
<form th:action="@{/excel/multiImport}" method="post" enctype="multipart/form-data">
<div>
<input type="file" name="excelFiles"/>
</div>
<div>
<input type="file" name="excelFiles"/>
</div>
<input type="submit" value="导入多个excel文件"/>
</form>
</div>
后端代码
/** * 从web页面导入多个excel文件 * * @param excelFiles excel文件数组 * @param request Request对象 * @return 内容 */ @PostMapping("/excel/multiImport") @ResponseBody public Map<String, List<ExcelUserInfo>> importMultiExcel(@RequestParam("excelFiles") MultipartFile[] excelFiles, HttpServletRequest request) throws IOException { if (excelFiles == null) { throw new RuntimeException("excel files can not be null"); } // 1: 转存文件 if (excelFiles != null && excelFiles.length > 0) { String storePath = request.getSession().getServletContext().getRealPath("/") + "upload/temp/"; for (MultipartFile excelFile : excelFiles) { excelFile.transferTo(new File(storePath + excelFile.getOriginalFilename())); } } // 2: 解析文件 Map<String, List<ExcelUserInfo>> fileMap = new LinkedHashMap<>(); for (MultipartFile excelFile : excelFiles) { List<String[]> excelData = POIUtil.readExcelFile(excelFile, 1); List<ExcelUserInfo> userInfoList = new ArrayList<>(); for (String[] arr : excelData) { ExcelUserInfo userInfo = new ExcelUserInfo(); userInfo.setId(Integer.valueOf(arr[0])); userInfo.setName(arr[1]); userInfo.setAge(Integer.valueOf(arr[2])); userInfoList.add(userInfo); } fileMap.put(excelFile.getOriginalFilename(), userInfoList); } // 3: 返回数据 return fileMap; }
带参数上传excel文件
前端代码(使用thymeleaf模板引擎)
<div>
<p>带参数上传excel文件</p>
<form th:action="@{/excel/importWithParams}" method="post" enctype="multipart/form-data">
<div>
作者:<input type="text" name="author"/>
</div>
<div>
描述:<input type="text" name="desc"/>
</div>
<input type="file" name="excelFile"/>
<input type="submit" value="导入单个excel文件"/>
</form>
</div>
后端代码
/** * 带参数上传excel文件 * * @param author 作者 * @param desc 描述 * @param excelFile excel文件 * @param request Request对象 * @return 内容 * @throws IOException */ @PostMapping("/excel/importWithParams") @ResponseBody public Map<String, Object> importExcelFileWithParams(@RequestParam("author") String author, @RequestParam("desc") String desc, @RequestParam("excelFile") MultipartFile excelFile, HttpServletRequest request) throws IOException { // 1: 转存文件 if (!excelFile.isEmpty()) { /** * 这里的getRealPath("/")是打包之后的项目的根目录。 * 也就是 target项目名-1.0-SNAPSHOT */ String storePath = request.getSession().getServletContext().getRealPath("/") + "upload/temp/"; excelFile.transferTo(new File(storePath + excelFile.getOriginalFilename())); } // 2: 解析excel数据 List<String[]> excelData = POIUtil.readExcelFile(excelFile, 1); List<ExcelUserInfo> userInfoList = new ArrayList<>(); for (String[] arr : excelData) { ExcelUserInfo userInfo = new ExcelUserInfo(); userInfo.setId(Integer.valueOf(arr[0])); userInfo.setName(arr[1]); userInfo.setAge(Integer.valueOf(arr[2])); userInfoList.add(userInfo); } // 3: 输出excel数据 Map<String, Object> resultMap = new LinkedHashMap<>(); resultMap.put("author", author); resultMap.put("desc", desc); resultMap.put("excelContent", userInfoList); return resultMap; }
下载excel文件
前端代码(使用thymeleaf模板引擎)
<div>
<p>下载excel文件</p>
<input type="button" value="下载excel文件" onclick="downloadExcelFile()"/>
</div>
<script>
// 下载excel文件
function downloadExcelFile() {
window.location.href = "/ss/excel/download";
}
</script>
后端代码
/** * 下载服务器中的excel文件 * * @param request Request对象 * @param response Response对象 * @return */ @GetMapping("/excel/download") public String downloadExcelFile(HttpServletRequest request, HttpServletResponse response) { // 1: 找到excel文件 String storePath = request.getSession().getServletContext().getRealPath("/") + "upload/temp/"; String fileName = "userInfo1.xlsx"; File file = new File(storePath + fileName); if (!file.exists()) { throw new RuntimeException("file do not exist"); } InputStream inputStream = null; ServletOutputStream servletOutputStream = null; // 重置response response.reset(); //设置http头信息的内容 response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.ms-excel"); response.addHeader("Content-Disposition", "attachment;filename="" + fileName + """); int fileLength = (int) file.length(); response.setContentLength(fileLength); try { if (fileLength != 0) { inputStream = new FileInputStream(file); byte[] buf = new byte[4096]; // 创建输出流 servletOutputStream = response.getOutputStream(); int readLength; // 读取文件内容并输出到response的输出流中 while ((readLength = inputStream.read(buf)) != -1) { servletOutputStream.write(buf, 0, readLength); } } } catch (IOException e) { throw new RuntimeException("download file error"); } finally { try { // 关闭ServletOutputStream if (servletOutputStream != null) { servletOutputStream.flush(); servletOutputStream.close(); } // 关闭InputStream if (inputStream != null) { inputStream.close(); } } catch (IOException e) { e.printStackTrace(); } } return null; }
创建并下载excel文件
后端代码
/** * 创建并下载excel文件 * * @param request 请求 * @param response 响应 * @return */ @GetMapping("/excel/createAndDownload") public String createAndDownloadExcelFile(HttpServletRequest request, HttpServletResponse response) { // 查询数据 List<ExcelUserInfo> userInfoList = this.getData(); // 组装输入参数 List<String> attributes = Arrays.asList("编号", "姓名", "年龄"); List<List<String>> data = new ArrayList<>(); for (ExcelUserInfo excelUserInfo : userInfoList) { List<String> rowInfo = new ArrayList<>(); rowInfo.add(String.valueOf(excelUserInfo.getId())); rowInfo.add(excelUserInfo.getName()); rowInfo.add(String.valueOf(excelUserInfo.getAge())); data.add(rowInfo); } // 生成workbook Workbook workbook = POIUtil.createExcelFile(attributes, data, POIUtil.XLS); if (workbook == null) { throw new RuntimeException("create excel file error"); } // 输出到临时文件 String storePath = request.getSession().getServletContext().getRealPath("/") + "upload/temp/"; String fileName = "userInfo-" + (new Date()).getTime() + "." + POIUtil.XLS; FileOutputStream fout = null; try { fout = new FileOutputStream(storePath + fileName); workbook.write(fout); } catch (IOException e) { e.printStackTrace(); } finally { try { if (fout != null) { fout.close(); } } catch (IOException e) { e.printStackTrace(); } } // 找到文件并下载 File file = new File(storePath + fileName); if (!file.exists()) { throw new RuntimeException("file do not exist"); } InputStream inputStream = null; ServletOutputStream servletOutputStream = null; // 重置response response.reset(); //设置http头信息的内容 response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.ms-excel"); response.addHeader("Content-Disposition", "attachment;filename="" + fileName + """); int fileLength = (int) file.length(); response.setContentLength(fileLength); try { if (fileLength != 0) { inputStream = new FileInputStream(file); byte[] buf = new byte[4096]; // 创建输出流 servletOutputStream = response.getOutputStream(); int readLength; // 读取文件内容并输出到response的输出流中 while ((readLength = inputStream.read(buf)) != -1) { servletOutputStream.write(buf, 0, readLength); } } } catch (IOException e) { throw new RuntimeException("download file error"); } finally { try { // 关闭ServletOutputStream if (servletOutputStream != null) { servletOutputStream.flush(); servletOutputStream.close(); } // 关闭InputStream if (inputStream != null) { inputStream.close(); } // 删除临时文件 file.delete(); } catch (IOException e) { e.printStackTrace(); } } return null; } /** * 模拟从数据库中查询出数据 * * @return 数据 */ private List<ExcelUserInfo> getData() { List<ExcelUserInfo> userInfoList = new ArrayList<>(); for (int i = 1; i <= 5; i++) { ExcelUserInfo userInfo = new ExcelUserInfo(); userInfo.setId(i); userInfo.setName("导出的用户_" + i); userInfo.setAge(20 + i); userInfoList.add(userInfo); } return userInfoList; }