基于Spring Mvc实现的Excel文件上传下载

时间：2024-10-20 08:03:49

最近工作遇到一个需求，需要下载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中enctype="multipart/form-data"
  • action指定访问的url
  • input中需要设置name属性，这样后端才能获取到文件对象
<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包有：

  • commons-fileupload 用于获取上传文件
  • jxl 用于解析excel
<!-- 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, HttpServletResponse response) throws IOException, BiffException, 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, 0, "标题1",wc);//Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是z

            Label nc1 = new Label(1, 0, "标题2",wc);

            Label nc2 = new Label(2, 0, "标题3",wc);

            Label nc3 = new Label(0, 1, "dddd");

            Label nc4 = new Label(1, 1, "ffff");

            wsheet.addCell(nc0);

            wsheet.addCell(nc1);

            wsheet.addCell(nc2);

            wsheet.addCell(nc3);

            wsheet.addCell(nc4);

            return writableWorkbook;

        }

    }

最后贡献下相关的代码

有需要的可以拿去参考

网盘下载链接

相关文章