spring + jxls报表的导出_使用spring boot、jxls和jexl实现报表模板生成报表 ——————————————

spring + jxls报表的导出

xlsx模板，采用流传输，导出报表。

maven

<dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>[2.6.0-SNAPSHOT,)</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>[1.2.0-SNAPSHOT,)</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-jexcel</artifactId>
            <version>[1.0.8,)</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-reader</artifactId>
            <version>[2.0.5,)</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

jxls工具类

public class JxlsUtils{

    private static final String TEMPLATE_PATH="excel";

    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
        Context context = new Context();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        // 设置解析模板语法
        jxlsHelper.setEvaluateFormulas(true);
        Transformer transformer  = jxlsHelper.createTransformer(is, os);
        
//        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
//        Map<String, Object> funcs = new HashMap<String, Object>();
//        funcs.put("utils", new JxlsUtils());    //添加自定义功能
//        旧版本添加自定义功能
//        evaluator.getJexlEngine().setFunctions(funcs);
//       新版本添加自定义功能
//       JexlBuilder jb = new JexlBuilder();
//        jb.namespaces(funcs);
//        JexlEngine je = jb.create();
//        evaluator.setJexlEngine(je);
        jxlsHelper.processTemplate(context, transformer);
    }

    public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
        exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }

    public static void exportExcel(String templateName, OutputStream os, Map<String, Object> model) throws FileNotFoundException, IOException {
        File template = getTemplate(templateName);
        if(template!=null){
            exportExcel(new FileInputStream(template), os, model);
        }
    }

/**
     * 导出excel到字节数组中
     *
     * @param templateName
     * @param beans
     * @return
     */
    public static byte[] exportExcel2Bytes(String templateName, Map<String, Object> beans) {
        ByteArrayOutputStream os = null;
        try {
            os = new ByteArrayOutputStream();
            ClassPathResource classPathResource = new ClassPathResource(TEMPLATE_PATH + templateName);
            JxlsUtil.exportExcel(classPathResource.getInputStream(), os, beans);
            return os.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }
        return new byte[0];
    }

    //获取jxls模版文件
    public static File getTemplate(String name){
        String templatePath = JxlsUtils.class.getClassLoader().getResource(TEMPLATE_PATH).getPath();
        File template = new File(templatePath, name);
        if(template.exists()){
            return template;
        }
        return null;
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }

}
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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

实体类

导出代码

@RequestMapping(value = "exportDeviceModelMsg",method = RequestMethod.GET)
    public ResponseEntity<byte[]> exportDeviceModelMsg(HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        List<User> myTestList = setData();
        try {
            Map<String , Object> model = new HashMap<>();
            model.put("myTestList", myTestList);
            JxlsUtils.exportExcel("test.xlsx", os, model);

            //下载表格
            HttpHeaders headers = new HttpHeaders();
            String downloadFileName =  "小明测试" + UUID.randomUUID().toString().substring(0, 4) + ".xlsx";
            //防止中文名乱码
            downloadFileName = new String(downloadFileName.getBytes("UTF-8"), "ISO-8859-1");
            headers.setContentDispositionFormData("attachment", downloadFileName);
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            //返回
            byte[] bytes = os.toByteArray();
            os.close();
            return new ResponseEntity<byte[]>(bytes, headers, HttpStatus.CREATED);
        } catch (Exception e) {
            throw new RuntimeException(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

模拟数据：
public static List<User> setData(){
        List<User> arrayList = new ArrayList();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId(i);
            user.setAge(i * 10 + i);
            user.setName("test-" + i);
            user.setSex(i > 3 ? "男":"女");
            user.setBeginDt("201"+i + "-01-01");
            user.setEndDt("201"+i + "-12-12");
            user.setCycleDateList("tst");
            arrayList.add(user);
        }
        return arrayList;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

xlsx模板，采用字节数组转成字符串返回前段，导出报表。

    @GetMapping("/exportProject")
    public CommonResult exportStareWith(HttpServletRequest request,
                                        @RequestParam("type") Integer type) throws BusException, AccessDeniedException {
        User user = this.getCurrentUser();
        byte[] bytes = reportService.exportStareWith(ProjectPhaseEnum.parse(type), user);
        return CommonResult.success(bytes);
    }
1
2
3
4
5
6
7

前段代码：

// 接口返回的是字符串
    testBtn4 () {
      axios({
        method: 'get',
        url: 'http://localhost:8099/approval/?resultIds=34'
      }).then((data) => {
        // 获取返回的字符串数据
        var raw = window.atob(data.data)
        // 转编码
        var uInt8Array = new Uint8Array(raw.length)
        for (var i = 0; i < raw.length; i++) {
          uInt8Array[i] = raw.charCodeAt(i)
        }
        const blob = new Blob([uInt8Array], {
          type: 'application/octet-stream'
        })
        // 下载文件
        const link = document.createElement('a')
        link.style.display = 'none'
        link.href = URL.createObjectURL(blob)
        link.download = '导出报表' + Date.parse(new Date()) + '.zip'
        document.body.appendChild(link)
        link.click()
        document.body.removeChild(link)
        this.lcoalMsg = 'end'
      })
    },
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