EasyPoi

EasyPoi

使用模板导出数据

1.引入依赖

        <dependency>
            <groupId>cn.afterturngroupId>
            <artifactId>easypoi-spring-boot-starterartifactId>
            <version>4.2.0version>
        dependency>
1
2
3
4
5

2.工具类

package com.junfeng.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel工具类
 */
public class ExcelUtil {
    /**
     * 导出
     *
     * @param list           数据列表
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      元素类型
     * @param fileName       文件名
     * @param isCreateHeader 是否创建列头
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 导出
     *
     * @param list      数据列表
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass 元素类型
     * @param fileName  文件名
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * 导出,适用于导出多个sheet的Excel,配合createOneSheet方法一起使用
     *
     * @param list     数据列表(元素是Map)
     * @param fileName 文件名
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 导入
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return Collections.emptyList();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return Collections.emptyList();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }

    /**
     * 功能描述：根据接收的Excel文件来导入多个sheet,根据索引可返回一个集合
     *
     * @param file       导入文件
     * @param sheetIndex 导入sheet索引,从0开始
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     */
    public static <T> List<T> importExcel(MultipartFile file, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return Collections.emptyList();
        }
        // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
        ImportParams params = new ImportParams();
        // 第几个sheet页
        params.setStartSheetIndex(sheetIndex);
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }

    /**
     * 功能描述：根据接收的Excel文件来导入多个sheet,根据索引可返回一个集合
     *
     * @param filePath   导入文件路径
     * @param sheetIndex 导入sheet索引
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     */
    public static <T> List<T> importExcel(String filePath, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        ImportParams params = new ImportParams();
        params.setStartSheetIndex(sheetIndex);
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }

    /**
     * 用于导出多sheet的Excel文件
     *
     * @param sheetName 自定义sheetName
     * @param clazz     pojo实体类
     * @param data      List> or List
     * @return map
     */
    public static Map<String, Object> createOneSheet(String sheetName, Class<?> clazz, List<?> data) {
        ExportParams params = new ExportParams("", sheetName, ExcelType.XSSF);
        return createOneSheet(params, clazz, data);
    }

    /**
     * 创建一个表格并填充内容,返回map供工作簿使用,map的key必须写死
     *
     * @param params 导出配置
     * @param clazz  带@Excel注解字段的POJO实体类
     * @param data   List> or List
     * @return map
     */
    private static Map<String, Object> createOneSheet(ExportParams params, Class<?> clazz, List<?> data) {
        Map<String, Object> map = new HashMap<>(8);
        map.put("title", params);
        map.put("entity", clazz);
        map.put("data", data);
        return map;
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    }
}
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175

3.excel模板

4.导出代码

 @Override
    public void export(List<StorageCapacityVO> result, HttpServletResponse response) throws IOException {
        TemplateExportParams params = new TemplateExportParams(
                "doc/example.xlsx");
        Map<String, Object> map = new HashMap<>();

        List<Map<String, String>> listMap = new ArrayList<>();
        String yyyymm = "";
        String dd = "";
        for (int i = 0; i < result.size(); i++) {
            Map<String, String> lm = new HashMap<>();
            StorageCapacityVO vo = result.get(i);
            Date tm = vo.getTm();
            dd = DateUtil.format(tm, "dd");
            String time = DateUtil.format(tm, "HH:mm:ss");
            yyyymm = DateUtil.format(tm, "yyyy 年 MM 月");

            lm.put("time", time == null ? "" : time);
            lm.put("water", vo.getWater() == null ? "" : vo.getWater() + "");
            lm.put("power", vo.getPower() == null ? "" : vo.getPower() + "");
            lm.put("ecologyFlow", vo.getEcologyFlow() == null ? "" : vo.getEcologyFlow() + "");
            lm.put("downFlow", vo.getDownFlow() == null ? "" : vo.getDownFlow() + "");
            lm.put("warehousing", vo.getWarehousing() == null ? "" : vo.getWarehousing() + "");
            lm.put("q", vo.getQ() == null ? "" : vo.getQ() + "");
            lm.put("z", vo.getZ() == null ? "" : vo.getZ() + "");
            listMap.add(lm);
        }
        map.put("maplist", listMap);

        map.put("yyyymm", yyyymm);
        map.put("dd", dd);

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
       /*
       	//导出文件到本地
        File savefile = new File("D:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("D:/excel/水位记录表_map.xls");
        workbook.write(fos);
        fos.close();*/
        String fileName = "file_" + dd + ".xlsx";
        //返回二进制流给前端
        downLoadExcel(fileName, response, workbook);
    }
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