基于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中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;
    }
  }

最后贡献下相关的代码：springtest_jb51.rar

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。