maven+springmvc下载excle文件——ie8可用

最新推荐文章于 2022-04-26 22:56:48 发布

archer.wu

最新推荐文章于 2022-04-26 22:56:48 发布

阅读量1.6k

点赞数

分类专栏： springMVC java javascript tools

本文链接：https://blog.csdn.net/wild46cat/article/details/52471382

版权

java 同时被 3 个专栏收录

113 篇文章 0 订阅

订阅专栏

javascript

29 篇文章 0 订阅

订阅专栏

springMVC

22 篇文章 0 订阅

订阅专栏

springmvc下载excle文件

1、新建一个maven的web项目

mvn archetype:generate -DarchetypeCatalog=internal

2、修改pom.xml文件

<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 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.xueyoucto.xueyou</groupId>
    <artifactId>springmvnDownloadExcel</artifactId>
    <packaging>war</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>springmvnDownloadExcel Maven Webapp</name>
    <url>http://maven.apache.org</url>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-web -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context-support -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jms -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jms</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc-portlet -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc-portlet</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-messaging -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-messaging</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-instrument -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-instrument</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-framework-bom -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-framework-bom</artifactId>
            <version>4.0.5.RELEASE</version>
            <type>pom</type>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.codehaus.jackson/jackson-mapper-asl -->
        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>1.9.13</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.7</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>
    </dependencies>
    <build>
        <finalName>springmvnDownloadExcel</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
            </plugin>
            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <artifactId>tomcat7-maven-plugin</artifactId>
                <version>2.2</version>
                <configuration>
                    <url>http://localhost:8989/manager/text</url>
                    <username>tomcat</username>
                    <password>tomcat</password>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                    <!-- 指定编码格式，否则在DOS下运行mvn compile命令时会出现莫名的错误，因为系统默认使用GBK编码 -->
                    <compilerArguments>
                        <extdirs>src/main/webapp/WEB-INF/lib</extdirs>
                    </compilerArguments>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <encoding>UTF-8</encoding>
                    <!-- 指定编码格式，否则在DOS下运行mvn命令时当发生文件资源copy时将使用系统默认使用GBK编码 -->
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

3、修改web.xml文件

<!DOCTYPE web-app PUBLIC
        "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
        "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
    <display-name>Archetype Created Web Application</display-name>
    <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
        <init-param>
            <param-name>forceEncoding</param-name>
            <param-value>true</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
    <servlet>
        <servlet-name>springmvc</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>/WEB-INF/springmvc-servlet.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>springmvc</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
</web-app>

4、在WEB-INF文件夹下建立springmvc-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">

    <context:component-scan base-package="com.xueyoucto.xueyou.controller"/>
    <context:component-scan base-package="com.xueyoucto.xueyou.utils"/>

    <mvc:annotation-driven/>
    <mvc:resources location="/Component/" mapping="/Component/**"/>
    <mvc:resources location="/img/" mapping="/img/**"/>
    <mvc:resources location="/js/" mapping="/js/**"/>
    <mvc:resources location="/css/" mapping="/css/**"/>
</beans>

5、在webapp中建立文件夹和文件，如下图所示：

6、hello.js

/**
 * Created by Administrator on 2016-09-07.
 */
var BASE_URL = "http://localhost:8989/springmvnDownloadExcel/";
$(function () {
    var tempjson= [
        {
            "code": "001",
            "name": "张三",
            "age": "13",
            "address": "青岛市",
            "birthdate": "2000-01-01 00:00:00"
        },
        {
            "code": "002",
            "name": "李四",
            "age": "16",
            "address": "济南市",
            "birthdate": "1997-01-01 00:00:00"
        }
    ];
    var bodyData = JSON.stringify(tempjson);
    bodyData = bodyData.replace(/"/g,'*');
    var Exceldata = {
        headData:['编号','姓名','年龄','地址','生日'],
        bodyData:bodyData
    };
    //下载excel
    $('#excelDownload').click(function () {
        alert('下载excel');
        alert(Exceldata.bodyData);
        DownLoadFile({
            url:BASE_URL + 'Download/testDownLoad',
            data:Exceldata
        })
    });
    //测试按钮
    $('#testDownload').click(function () {
        $.ajax({
            type: "POST",
            url: BASE_URL + 'TestJson/getJson',
            data: {param:bodyData},
            dataType: "json",
            success: function (data) {
                alert(data);
                console.log(data);
            }
        })
    });
});

var DownLoadFile = function (options) {
    var config ={ method: 'post' };
    var $form = $('<form method="' + config.method + '" />');
    $(document.body).append($form);
    $form.attr('action', options.url);
    for (var key in options.data) {
        $form.append('<input type="hidden" name="' + key + '" value="' + options.data[key] + '" />');
    }
    $form[0].submit();
    $form.remove();
};

7、hello.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" pageEncoding="utf-8" %>
<html>
<head>
  <script type="text/javascript" src="${pageContext.request.contextPath}/Component/jquery-1.12.2.min.js"></script>
  <script type="text/javascript" src="${pageContext.request.contextPath}/Component/json2.js"></script>
  <script type="text/javascript" src="${pageContext.request.contextPath}/js/hello.js"></script>
    <title>ccc</title>
    <meta charset="UTF-8"/>
</head>
<body>
<h1>hello</h1>
<input id="testDownload" type="button" value="测试服务器响应">
<input id="excelDownload" type="button" value="下载excel">
<form action="${pageContext.request.contextPath}/TestJson/getJson" method="post">
  <input name="param" value="就是我"/>
  <input type="submit" value="提交">
</form>
</body>
</html>

8、建立如下后台java文件，如图所示：

9、Download.java

package com.xueyoucto.xueyou.controller;

import com.alibaba.fastjson.JSON;
import com.xueyoucto.xueyou.utils.ExcelExport;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016-09-06.
 */
@RestController
@RequestMapping(value = "/Download", method = {RequestMethod.GET, RequestMethod.POST}, produces = "application/json;charset=UTF-8")
public class Download {
    @RequestMapping(value = "/testDownLoad")
    public void testDownLoad(HttpServletResponse response, String headData, String bodyData) {
        System.out.println(headData);
        bodyData = bodyData.replaceAll("\\*", "\"");
        System.out.println(bodyData);
        String[] titleArray = headData.split(",");
        String[] keyArray = {"code", "name", "age", "address", "birthdate"};
        System.out.println("================================");
        ExcelExport.getExcelExport().ExportJSONExcel(response, "测试导出", bodyData, titleArray, keyArray);
    }
}

10、TestJson.java

package com.xueyoucto.xueyou.controller;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.io.UnsupportedEncodingException;
import java.util.HashMap;
import java.util.Map;

/**
 * Created by Administrator on 2016-09-06.
 */
@RestController
@RequestMapping(value = "/TestJson",method = {RequestMethod.GET,RequestMethod.POST},produces = "application/json;charset=UTF-8")
public class TestJson {
    @RequestMapping(value = "/getJson")
    public Map<String,Object> getJson(String param){
        System.out.println(param);
        Map<String,Object> resMap = new HashMap<String, Object>();
        resMap.put("resCode","1");
        resMap.put("resMessage","成功");
        return resMap;
    }

    @RequestMapping(value = "/getJson2")
    public Map<String,Object> inputCustomer(){
        Map<String,Object> resMap = new HashMap<String, Object>();
        resMap.put("ccc", 123123);
        return resMap;
    }
}

11、ExcelExport.java

package com.xueyoucto.xueyou.utils;

import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Administrator on 2016/9/5.
 */
public class ExcelExport {
    private static ExcelExport ee;
    private HSSFCellStyle headStyle = null;
    private HSSFWorkbook wb = null;

    public static ExcelExport getExcelExport() {
        if (ee == null) {
            ee = new ExcelExport();
        }
        return ee;
    }

    /**
     * 设置head样式
     */
    private void headFont() {
        HSSFFont headFont = wb.createFont();
        headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short) 11);
        headStyle = wb.createCellStyle();
        headStyle.setFont(headFont);
        headStyle.setBorderTop((short) 1);
        headStyle.setBorderRight((short) 1);
        headStyle.setBorderBottom((short) 1);
        headStyle.setBorderLeft((short) 1);
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    }

    /**
     * 导出excel
     * @param response 响应
     * @param fileName 文件名
     * @param strArray 二维数组
     * @throws IOException
     */
    public void ExportExcel(HttpServletResponse response, String fileName, String[][] strArray) throws IOException {
        if (strArray.length == 0) {
            return;
        }
        int arrayRowCount = strArray.length;
        int arrayColCount = strArray[0].length;
        response.reset();
        String time = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(Calendar.getInstance().getTime());
        response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName + time + ".xls", "UTF-8"));
        response.setContentType("application/octet-stream; charset=utf-8");
        wb = new HSSFWorkbook();
        headFont();
        Sheet sh = wb.createSheet();
        //设置表头
        Row trow = sh.createRow(0);
        for (int i = 0; i < arrayColCount; i++) {
            Cell tcell = trow.createCell(i);
            tcell.setCellStyle(headStyle);
            tcell.setCellValue(strArray[0][i]);
        }

        for (int rownum = 1; rownum < arrayRowCount; rownum++) {
            Row row = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < arrayColCount; cellnum++) {
                Cell cell = row.createCell(cellnum);
                if (strArray[rownum][cellnum] != null) {
                    cell.setCellValue(strArray[rownum][cellnum]);
                }
            }
        }
        OutputStream out = response.getOutputStream();
        try {
            wb.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
        out.flush();
        try {
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过JSON导出excel
     * @param response
     * @param fileName
     * @param bodyData
     * @param titleArray
     * @param keyArray
     */
    public void ExportJSONExcel(HttpServletResponse response,String fileName,String bodyData, String[] titleArray, String[] keyArray){
        if (fileName == null || fileName.equals("")) {
            System.out.println("文件名不能为空!");
            return;
        }
        if (bodyData == null || bodyData.equals("")) {
            System.out.println("导出数据不能为空!");
            return;
        }
        if(titleArray.length != keyArray.length){
            System.out.println("标题内容和JSON中的键个数不同");
            return;
        }

        Map<String, Object> tempMap = new HashMap<String, Object>();
        List<Map<String, Object>> resList = new ArrayList<Map<String, Object>>();
        resList.add(tempMap);
        resList = JSON.parseObject(bodyData, resList.getClass());

        String[][] tempStrArray = JSONtoStringArray(resList,titleArray,keyArray);
        try {
            ExportExcel(response,fileName,tempStrArray);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 把JSON和title组成String[][]
     * @param jsonList
     * @param titleArray
     * @param keyArray
     * @return
     */
    public String[][] JSONtoStringArray(List<Map<String, Object>> jsonList, String[] titleArray, String[] keyArray){
        int colCount = titleArray.length;
        int rowCount = jsonList.size()+1;
        String[][] resStringArray = new String[rowCount][colCount];
        //标题
        for (int i = 0; i < titleArray.length; i++) {
            resStringArray[0][i] = titleArray[i];
        }
        //内容
        int k =1;
        for (Map<String, Object> stringObjectMap : jsonList) {
            for (int i = 0; i < keyArray.length; i++) {
                resStringArray[k][i]=stringObjectMap.get(keyArray[i]).toString();
            }
            k++;
        }
        return  resStringArray;
    }
}

12、编译、部署、运行