springmvc文件上传和下载,下载excel模板和导入excel数据(poi解析)
1、springmvc文件下载
2、springmvc文件上传
3、下载excel模板的demo
4、导入excel数据的demo
1、springmvc文件下载 <--返回目录
1.1、
文件下载是web项目中常用的服务,在springmvc中常用ResponseEntity类来事项文件下载
1.2、ResponseEntity
ResponseEntity类实现响应头、文件数据(以字节存储)、状态封装在一起交给浏览器处理以实现浏览器的文件下载。简单的说ResponseEntity可以折这HttpHeaders和HttpStatus,通过对HttpHeaders和HttpStatus的设置可以使浏览器执行下载操作。
1.3、实现文件下载
步骤:
- 获取到文件的存放的真实路径
- 根据接收到的文件名和文件真实路径创建文件实例(注意:这里不是创建一个文件,而是创建一个File型的实例)
- 设置响应头Content-Disposition浏览器根据这个响应头执行相应的操作和要下载的文件名
- 设置响应内容的MIME类型,以二进制流形式传输
- 返回ResponseEntity
@RequestMapping("/download") public ResponseEntity<byte[]> download(@RequestParam("fileName") String fileName, HttpServletRequest req) throws IOException { // 获取文件存放的真实路径 String realPath = req.getServletContext().getRealPath("/WEB-INF/file"); //创建文件实例 File file = new File(realPath, fileName); //修改文件名的编码格式 String downloadFileName = new String(fileName.getBytes("UTF-8"), "UTF-8"); //设置httpHeaders,使浏览器响应下载 HttpHeaders headers = new HttpHeaders(); //告诉浏览器执行下载的操作,“attachment”告诉了浏览器进行下载,下载的文件 文件名为 downloadFileName headers.setContentDispositionFormData("attachment", downloadFileName); //设置响应方式为二进制,以二进制流传输 headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.CREATED); }
1.4、火狐浏览器测试
从图中我们可以看到,我们设置的Content-Disposition起效果,传输类型也为二进制。
2、springmvc文件上传 <--返回目录
2.1、
文件的上传与下载基本上是web项目中会用到的技术,在web学习中我们用到的是 Apache fileupload这个组件来实现上传,在springmvc中对它进行了封装,让我们使用起来比较方便,但是底层还是由Apache fileupload来实现的。springmvc中由MultipartFile接口来实现文件上传。
2.2、MultipartFile接口
该接口用来实现springmvc中的文件上传操作,它有两个实现类:
接口定义的方法:
2.3、实现文件上传
导入jar包
- commons-fileupload
- commons-io
commons-io可以不用自己导入,maven会自动导入对应版本的jar
<dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.2</version> </dependency>
前端jsp页面
-
input的type设置为file
-
form表单的method设为post,
-
form表单的enctype设置为multipart/form-data,以二进制的形式传输数据。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="ISO-8859-1"> <title>Insert title here</title> </head> <body> <form action="/ssm/file/imgUpload" enctype="multipart/form-data" method="post"> <input type="file" name="file"><br><br> <input type="submit" value="上传"> </form> </body> </html>
Controller层接收
使用MultipartFile对象作为参数,接收前端发送过来的文件,将文件写入本地文件中,就完成了上传操作
@RequestMapping("/upload") public String upload(@RequestParam("file") MultipartFile file, HttpServletRequest req) throws IllegalStateException, IOException { // 判断文件是否为空,空则返回失败页面 if (file.isEmpty()) { return "failed"; } // 获取文件存储路径(绝对路径) String path = req.getServletContext().getRealPath("/WEB-INF/file"); // 获取原文件名 String fileName = file.getOriginalFilename(); // 创建文件实例 File filePath = new File(path, fileName); // 如果文件目录不存在,创建目录 if (!filePath.getParentFile().exists()) { filePath.getParentFile().mkdirs(); System.out.println("创建目录" + filePath); } // 写入文件 file.transferTo(filePath); return "success"; }
springmvc.xml配置CommonsMultipartResolver
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <!--上传文件的最大大小,单位为字节 --> <property name="maxUploadSize" value="17367648787"></property> <!-- 上传文件的编码 --> <property name="defaultEncoding" value="UTF-8"></property> </bean>
3、下载excel模板的demo <--返回目录
excel模板设置单元格数据格式
pom.xml
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.5.RELEASE</version> <relativePath /> <!-- lookup parent from repository --> </parent> <groupId>com.oy</groupId> <artifactId>boot-demo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>boot-demo</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
PoiController
package com.oy.controller; import java.io.InputStream; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; @Controller public class PoiController { @RequestMapping("/poi/download") public void download(HttpServletResponse response) throws Exception { InputStream is = getClass().getClassLoader().getResourceAsStream("static/exceltemplates/export1.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); ResponseUtil.export(response, wb, "下载模板excel.xls"); } }
ResponseUtil
package com.oy.controller; import java.io.OutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.poi.ss.usermodel.Workbook; public class ResponseUtil { /** * 导出(或下载模板) * * @param response * @param wb excel 工作簿 * @param fileName 导出 excel 的文件名, 含后缀 * @throws Exception */ public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1")); response.setContentType("application/ynd.ms-excel;charset=UTF-8"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); } }
4、导入excel数据的demo <--返回目录
ExcelController
package com.guanxin.pm.controller.common; import com.guanxin.base.annotation.Controller; import com.guanxin.base.annotation.RequestParam; import com.guanxin.base.annotation.Wrapper; import com.guanxin.base.exception.ServiceException; import com.guanxin.pm.facade.common.ExcelFacade; import com.guanxin.web.utils.datatable.ctr.AjaxResponseWrapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import org.springframework.util.Assert; import org.springframework.web.multipart.MultipartFile; @Controller @Component("/zxl_pm/common/excel.svc") @Wrapper(AjaxResponseWrapper.class) public class ExcelController { @Autowired private ExcelFacade excelFacade; /** * 模板数据(数据实体和数据字段)导入数据库: 下载模板后, 手动填充数据后上传; 后台读取数据, 导入数据库; * * @param file */ public void importEntityAndFieldFromTemplate(@RequestParam("upFile") MultipartFile file, @RequestParam("zxlPrdId") String zxlPrdId) throws Exception { Assert.hasText(zxlPrdId, "zxlPrdId不能为空"); // 判断文件是否为空, 空则直接返回 if (file == null || file.isEmpty()) return; String fileName = file.getOriginalFilename(); //获取上传文件原名 // 判断文件是否为 excel 文件 if (!(fileName.endsWith(".xls") || fileName.endsWith(".xlsx"))) { throw new ServiceException("请上传 excel 格式文件"); } excelFacade.importEntityAndFieldFromTemplate(file, zxlPrdId); } }
ExcelFacadeImpl
package com.guanxin.pm.facade.common.impl; import com.guanxin.base.exception.ServiceException; import com.guanxin.hibernate.annotations.Tx; import com.guanxin.hibernate.support.HibernateSupport; import com.guanxin.pm.dto.product.request.ZxlPrdRequestEntityDto; import com.guanxin.pm.dto.product.request.ZxlPrdRequestFieldDto;import com.guanxin.pm.entity.product.request.ZxlPrdRequestEntity;import com.guanxin.pm.enums.product.request.*; import com.guanxin.pm.facade.common.ExcelFacade; import com.guanxin.pm.facade.product.request.*; import com.guanxin.pm.utils.CommonUtil; import com.guanxin.pm.utils.ExcelUtils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import org.springframework.util.Assert; import org.springframework.web.multipart.MultipartFile; import java.io.InputStream; /** * @Author oy * @Date 2020/11/10 17:20 */ @Tx @Component public class ExcelFacadeImpl extends HibernateSupport implements ExcelFacade { @Autowired private EntityFacade entityFacade; @Autowired private FieldFacade fieldFacade;/** * 模板数据导入数据库: 下载模板后, 手动填充数据后上传; 后台读取数据, 导入数据库; * * @param file * @throws Exception */ @Override public void importEntityAndFieldFromTemplate(MultipartFile file, String zxlPrdId) throws Exception { //InputStream is = new FileInputStream("e:\\entity_field_template_test.xls"); // 用于测试 InputStream is = file.getInputStream(); // file ==> 输入流 POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); // 输入流 ==> excel 工作簿 HSSFSheet entitySheet = wb.getSheetAt(0); // 获取第一个Sheet页(数据实体) if (entitySheet == null) return; importEntity(entitySheet, zxlPrdId); HSSFSheet fieldSheet = wb.getSheetAt(1); // 获取第二个Sheet页(数据字段) if (fieldSheet == null) return; importField(fieldSheet, zxlPrdId); }/** * 导入数据实体 * * @param hssfSheet * @throws Exception */ private void importEntity(HSSFSheet hssfSheet, String zxlPrdId) throws Exception { // 遍历行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } ZxlPrdRequestEntityDto dto = new ZxlPrdRequestEntityDto(); dto.setZxlPrdId(zxlPrdId); // 遍历列Cell for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) { HSSFCell hssfCell = hssfRow.getCell(cellNum); if (hssfCell == null) { continue; } switch (cellNum) { case 0: // 数据实体名 dto.setEntityName(ExcelUtils.getCellValue(hssfCell)); break; case 1: // 数据实体编号 dto.setEntityNo(ExcelUtils.getCellValue(hssfCell)); break; case 2: // 类型 String entityTypeVal = CommonUtil.getEnumNameByDesc(ExcelUtils.getCellValue(hssfCell), RequestEntityTypeEnum.class); if (StringUtils.isNotBlank(entityTypeVal)) { dto.setEntityType(RequestEntityTypeEnum.valueOf(entityTypeVal)); } break; case 3: // 修改模式 String editModelVal = CommonUtil.getEnumNameByDesc(ExcelUtils.getCellValue(hssfCell), RequestEntityEditModelEnum.class); if (StringUtils.isNotBlank(editModelVal)) { dto.setEditModel(RequestEntityEditModelEnum.valueOf(editModelVal)); } break; case 4: // 计算方式 String capacityTypeVal = CommonUtil.getEnumNameByDesc(ExcelUtils.getCellValue(hssfCell), RequestEntityCapacityTypeEnum.class); if (StringUtils.isNotBlank(capacityTypeVal)) { dto.setCapacityType(RequestEntityCapacityTypeEnum.valueOf(capacityTypeVal)); } break; case 5: // 数量 String capacityVal = ExcelUtils.getCellValue(hssfCell); int index = capacityVal.lastIndexOf("."); if (index > 0) { capacityVal = capacityVal.substring(0, index); } dto.setEntityCapacity(Integer.valueOf(capacityVal)); break; case 6: // 说明 dto.setNote(ExcelUtils.getCellValue(hssfCell)); break; default: break; } } Assert.hasText(dto.getZxlPrdId(), "产品id不能为空"); Assert.hasText(dto.getEntityName(), "数据实体中文名不能为空"); Assert.notNull(dto.getEntityType(), "数据实体类型不能为空"); Assert.notNull(dto.getEditModel(), "数据修改模式不能为空"); Assert.notNull(dto.getCapacityType(), "数据计算方式不能为空"); entityFacade.saveOrUpdate(dto); } dao.flush(); } /** * 导入数据字段 * * @param hssfSheet * @throws Exception */ private void importField(HSSFSheet hssfSheet, String zxlPrdId) throws Exception { // 遍历行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } ZxlPrdRequestFieldDto dto = new ZxlPrdRequestFieldDto(); dto.setZxlPrdId(zxlPrdId); // 遍历列Cell for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) { HSSFCell hssfCell = hssfRow.getCell(cellNum); if (hssfCell == null) { continue; } switch (cellNum) { case 0: // 数据实体名称 String entityNameVal = ExcelUtils.getCellValue(hssfCell); ZxlPrdRequestEntity entity = entityFacade.findByName(zxlPrdId, entityNameVal); if (entity == null) { throw new ServiceException("导入数据字段时, 找不到数据实体为[" + entityNameVal + "]的记录"); } dto.setEntityId(entity.getEntityId()); break; case 1: // 字段中文名称 dto.setFieldName(ExcelUtils.getCellValue(hssfCell)); break; case 2: // 字段类型 String fieldTypeVal = CommonUtil.getEnumNameByDesc(ExcelUtils.getCellValue(hssfCell), RequestFieldTypeEnum.class); if (StringUtils.isNotBlank(fieldTypeVal)) { dto.setFieldType(RequestFieldTypeEnum.valueOf(fieldTypeVal)); } break; case 3: // 输入方式 String inputControlVal = CommonUtil.getEnumNameByDesc(ExcelUtils.getCellValue(hssfCell), RequestFieldInputControlEnum.class); if (StringUtils.isNotBlank(inputControlVal)) { dto.setInputControl(RequestFieldInputControlEnum.valueOf(inputControlVal)); } break; case 4: // 字段长度 String fieldLengthVal = ExcelUtils.getCellValue(hssfCell); int index = fieldLengthVal.lastIndexOf("."); if (index > 0) { fieldLengthVal = fieldLengthVal.substring(0, index); } dto.setFieldLength(Integer.valueOf(fieldLengthVal)); break; case 5: // 默认值 dto.setDefaultValue(ExcelUtils.getCellValue(hssfCell)); break; case 6: // 字段描述 dto.setFieldDescribe(ExcelUtils.getCellValue(hssfCell)); break; case 7: // 是否允许为空 String isNullVal = ExcelUtils.getCellValue(hssfCell); Boolean isNull = ("是".equals(isNullVal) || StringUtils.isBlank(isNullVal)); // 默认允许为空 dto.setIsNull(isNull); break; case 8: // 是否为主键 String isKeyVal = ExcelUtils.getCellValue(hssfCell); Boolean isKey = ("是".equals(isKeyVal) || StringUtils.isBlank(isKeyVal)); dto.setIsKey(isKey); break; default: break; } } Assert.hasText(dto.getZxlPrdId(), "产品id不能为空"); Assert.hasText(dto.getEntityId(), "数据实体id不能为空"); Assert.hasText(dto.getFieldName(), "中文名称不能为空"); Assert.notNull(dto.getFieldType(), "字段类型不能为空"); fieldFacade.saveOrUpdate(dto); } dao.flush(); } }
ExcelUtils
package com.guanxin.pm.utils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.ss.usermodel.Workbook; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; /** * @Author oy * @Date 2020/11/10 14:49 */ public class ExcelUtils { /** * 导出(或下载模板) * * @param response * @param wb excel 工作簿 * @param fileName 导出 excel 的文件名, 含后缀 * @throws Exception */ public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1")); response.setContentType("application/ynd.ms-excel;charset=UTF-8"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); } /** * 获取 excel 单元格的值 * * @param hssfCell * @return */ public static String getCellValue(HSSFCell hssfCell) { if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return String.valueOf(hssfCell.getNumericCellValue()); } else { return String.valueOf(hssfCell.getStringCellValue()); } } }
---
posted on 2020-11-12 10:33 wenbin_ouyang 阅读(1789) 评论(1) 编辑 收藏 举报