基于Spring Mvc实现的Excel文件上传下载的实例代码
最近工作遇到一个需求,需要下载excel模板,编辑后上传解析存储到数据库。因此为了更好的理解公司框架,我就自己先用spring mvc实现了一个样例。 基础框架 之前曾经介绍过一个最简单的spring mvc的项目如何搭建,传送门在这里。 这次就基于这个工程,继续实现上传下载的小例子。需要做下面的事情: 1 增加index.html,添加form提交文件 2 引入commons-fileupload、commons-io、jxl等工具包 3 创建upload download接口 4 注入multipartResolver bean 5 在upload中使用HttpServletRequest获取文件流,通过WorkBook进行解析 6 在download中通过HttpServerResponse返回文件流,实现下载 页面 页面很简单,其实就是一个form标签,需要注意的是:
<form role="form" action="/upload" method="POST" enctype="multipart/form-data"> <div class="form-group"> <label for="file">上传文件</label> <input type="file" id="file" name="file"> </div> <button type="submit" class="btn btn-default">提交</button> </form> 引入commons-fileupload、jxl等工具包 涉及的jar包有:
<!-- springframework begins --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>4.2.4.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.2.4.RELEASE</version> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>4.0.0-b01</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-io/commons-io --> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.5</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload --> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.2</version> </dependency> <!-- https://mvnrepository.com/artifact/jexcelapi/jxl --> <dependency> <groupId>jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6</version> </dependency> Xml的配置 在web.xml中需要配置默认的访问页面,因为之前已经设置过拦截的请求是/,因此如果不设置所有的静态页面都会被拦截下来。 <welcome-file-list> <welcome-file>index.html</welcome-file> </welcome-file-list> 在spring的配置文件中,加入CommonsMultipartResolver的bean。 <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <!-- set the max upload size100MB --> <property name="maxUploadSize"> <value>104857600</value> </property> <property name="maxInMemorySize"> <value>4096</value> </property> </bean> 上传代码 @RequestMapping("upload") public void upload(HttpServletRequest request,HttpServletResponse response) throws IOException,BiffException,WriteException { MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest) request; MultipartFile file = mRequest.getFile("file"); Workbook workbook = Workbook.getWorkbook(file.getInputStream()); //遍历Sheet页 Arrays.stream(workbook.getSheets()) .forEach(sheet -> { int size = sheet.getRows(); for(int i=0; i<size; i++){ //遍历每一行,读取每列信息 Arrays.stream(sheet.getRow(i)).forEach(cell -> System.out.println(cell.getContents().equals("")?'空':cell.getContents())); } }); response.setHeader("Content-Disposition","attachment; filename=return.xls"); WritableWorkbook writableWorkbook = ExcelUtils.createTemplate(response.getOutputStream()); writableWorkbook.write(); writableWorkbook.close(); } 下载代码 @RequestMapping("download") public void download(HttpServletRequest request,WriteException { response.setHeader("Content-Disposition","attachment; filename=template.xls"); WritableWorkbook writableWorkbook = ExcelUtils.createTemplate(response.getOutputStream()); writableWorkbook.write(); writableWorkbook.close(); } 模板类 static class ExcelUtils { public static WritableWorkbook createTemplate(OutputStream output) throws IOException,WriteException { WritableWorkbook writableWorkbook= Workbook.createWorkbook(output); WritableSheet wsheet = writableWorkbook.createSheet("测试title",0); CellFormat cf = writableWorkbook.getSheet(0).getCell(1,0).getCellFormat(); WritableCellFormat wc = new WritableCellFormat(); // 设置居中 wc.setAlignment(Alignment.CENTRE); // 设置边框线 // wc.setBorder(Border.ALL,BorderLineStyle.THIN); wc.setBackground(jxl.format.Colour.GREEN); Label nc0 = new Label(0,"标题1",wc);//Label(x,y,z)其中x代表单元格的第x+1列,第y+1行,单元格的内容是z Label nc1 = new Label(1,"标题2",wc); Label nc2 = new Label(2,"标题3",wc); Label nc3 = new Label(0,1,"dddd"); Label nc4 = new Label(1,"ffff"); wsheet.addCell(nc0); wsheet.addCell(nc1); wsheet.addCell(nc2); wsheet.addCell(nc3); wsheet.addCell(nc4); return writableWorkbook; } } 最后贡献下相关的代码 有需要的可以拿去参考 网盘下载链接 以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持编程小技巧。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |