前端数据传到后台动态生成Excel文件并提供文件下载_根据前端传的数据后端生成动态excel表头

需求描述：
需要将前端的某些数据生成Excel文件，并提供下载功能。
解决方案：
前端通过ajax的方式将数据信息传到后台，后台使用POI组件动态生成Excel文件流，并写入数据信息，返回前端供前端下载。
代码示例：

// 前端代码
$.ajax({
                    url: "/ExportExcelServer/SubmitExcelData",
                    type: "post",
                    dataType: "json",
                    data:paramData,
                    success: function (data) {
                        if (data&&data.code=="1") {
                             window.location.href = "/ExportExcelServer/DownloadExcel";                         
                        } else {
                           $.dialog.alert("下载失败!");
                        }
                    },
                    error: function (err) {
                         $.dialog.alert("下载失败");
                    }
                });                             
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

// 后台接口代码
 @RequestMapping(value="/SubmitExcelData",method = RequestMethod.POST)
    public String submitExcelData(HttpServletRequest request, HttpServletResponse response,String paramJson)throws IOException  {
        // 设置响应和请求编码utf-8
        request.setCharacterEncoding("UTF-8");

        JSONObject jsonResult=new JSONObject();         // 返回界面端的json对象
        if (paramJson == null) {
            jsonResult.put("code","0");
            jsonResult.put("msg","失败，传入参数为空");
            return jsonResult.toString();
        }
        // 将参数信息存入session中
        HttpSession session = request.getSession();
        try {
            session.setAttribute("param", paramJson);
            jsonResult.put("code","1");
            jsonResult.put("msg","成功！");
        } catch (Exception e) {
            jsonResult.put("code","0");
            jsonResult.put("msg","失败，失败原因："+e.getMessage());
            return jsonResult.toString();
        }
        return jsonResult.toString();
    }
@RequestMapping(value="/DownloadExcel")
    public void downloadExcel(HttpServletRequest request, HttpServletResponse response)throws IOException {
        // 设置响应和请求编码utf-8
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");

        HttpSession session = request.getSession();
        String strData = (String) session.getAttribute("param");
        if (strData == null||strData.equals("")) {
            response.getWriter().write("失败，失败原因：参数为空！");
            return;
        }       
                // 创建Excel
                // 省略.......
                // region 解析base64位的编码为图片
                BASE64Decoder decoder = new BASE64Decoder();
                // 图片base编码解密
                byte[] byteImgs = decoder.decodeBuffer(strImgBase64);
                // 处理数据
                for (int j = 0; j < byteImgs.length; ++j) {
                    if (byteImgs[j] < 0) {
                        byteImgs[j] += 256;
                    }
                }
                // endregion              

                //region 设置返回头文件
                String strFileName="data.xls";// 默认Excel名称
                response.setContentType("application/octet-stream; charset=utf-8");
                if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0){
                    response.setHeader("Content-Disposition", "attachment; filename="
                            +  new String(strFileName.getBytes("UTF-8"), "ISO8859-1"));// firefox浏览器
                } else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0){
                    response.setHeader("Content-Disposition", "attachment; filename="
                            + URLEncoder.encode(strFileName, "UTF-8"));// IE浏览器
                }else{
                    response.setHeader("Content-disposition", "attachment;filename="+strFileName);
                }
                // endregion
                 response.flushBuffer();
                 workbook.write(response.getOutputStream());
            }
        } catch (Exception e) {
            response.getWriter().write("失败，失败原因："+e.getMessage());
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71