SpringBoot EasyExcel导入与导出

easyExcel 导入与导出详

环境：SpringBoot 2.+

1 依赖包

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.4</version>
        </dependency>
1
2
3
4
5

2 导入

excel数据如下

2.1 无模板

    /**
     * easyExcel 无模板导入解析
     *
     * @param excelFile
     * @return
     * @throws IOException
     */
    @PostMapping("/readNoEntity")
    public ResponseEntity noEntity(@RequestParam("file") MultipartFile excelFile) throws IOException {
        ResponseEntity responseEntity = new ResponseEntity();
         // sheet 读取第几个sheet的数据,索引从0开始
        List<Object> list = EasyExcel.read(excelFile.getInputStream()).sheet(0).doReadSync();
        responseEntity.setData(list);
        return responseEntity;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

结果

2.2 有模板

• 模板 ExcelProperty注解对应excel第一列名

@Data
public class EasyExcelTestDto {

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "性别")
    private String sex;

    @ExcelProperty(value = "年龄")
    private int age;
}
1
2
3
4
5
6
7
8
9
10
11
12

    /**
     * easyExcel 有模板导入解析
     *
     * @param excelFile
     * @return
     * @throws IOException
     */
    @PostMapping("/readToEntity")
    public ResponseEntity toEntity(@RequestParam("file") MultipartFile excelFile) throws IOException {
        ResponseEntity responseEntity = new ResponseEntity();
        List<EasyExcelTestDto> list = EasyExcel.read(excelFile.getInputStream(), EasyExcelTestDto.class, null).sheet(0).doReadSync();
        responseEntity.setData(list);
        return responseEntity;
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 结果
• 

2.3 自定义监听

• 继承AnalysisEventListener

    public class ExcelListener extends AnalysisEventListener<EasyExcelTestDto> {

        public List<EasyExcelTestDto> dataList = new ArrayList<>();

        // 每读取一行回调当前方法
        @Override
        public void invoke(EasyExcelTestDto easyExcelTestDto, AnalysisContext analysisContext) {
            logger.info("read item: {}", easyExcelTestDto);
            dataList.add(easyExcelTestDto);
        }

        // excel数据读取完毕回调
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            logger.info("{}条数据，开始存储数据库！", dataList.size());
            logger.info("所有数据解析完成！");
        }

        public List<EasyExcelTestDto> getDataList() {
            return dataList;
        }
    }

    @PostMapping("/readToEntityListener")
    public ResponseEntity toEntityListener(@RequestParam("file") MultipartFile excelFile) throws IOException {
        ResponseEntity responseEntity = new ResponseEntity();
        // 每次执行,listener必须重新定义new
        ExcelListener listener = new ExcelListener();
        EasyExcel.read(excelFile.getInputStream(), EasyExcelTestDto.class, listener).sheet(0).doRead();
        responseEntity.setData(listener.getDataList());
        return responseEntity;
    }
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

这里使用的是ExcelReaderSheetBuilder类的doRead()方法。doRead()和doReadSync()区别在于doReadSync()里面配了一个自定义的监听，并且返回读取到excel数据的List集合

• 结果
  
  

2.4 多个sheet的读取方法

    @RequestMapping("/readSheetToEntity")
    public ResponseEntity readSheetToEntity(@RequestParam("file") MultipartFile excelFile) throws IOException {
        ResponseEntity responseEntity = new ResponseEntity();
        List<List<EasyExcelTestDto>> resultList = new ArrayList<>();
        responseEntity.setData(resultList);
        ExcelListener listener = new ExcelListener();
        ExcelReaderBuilder builder = EasyExcel.read(excelFile.getInputStream(), EasyExcelTestDto.class, listener);
        ExcelReader reader = builder.build();
        //sheet集合
        List<ReadSheet> sheets = reader.excelExecutor().sheetList();
        for (ReadSheet sheet : sheets) {
            // 共用监听器,解析之前需要清空
            listener.getDataList().clear();
            //读取每一个sheet的内容
            logger.info("sheet name:{}", sheet.getSheetName());
            reader.read(sheet);
            List<EasyExcelTestDto> current = listener.getDataList();
            resultList.add(current);
            logger.info("content:{}", current);
        }
        reader.finish();
        return responseEntity;
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

结果

3 导出

3.1 无模板

• 代码

 @RequestMapping("/download")
    public void download(HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 随机生成文件名
        String fileName = UUID.randomUUID().toString();
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // sheet(0) 参数表示sheet名称
        EasyExcel.write(response.getOutputStream()).sheet(0).doWrite(data());
    }

    private List<?> data() {
        List<List<String>> rowList = new ArrayList<>();
        List<String> cell = new ArrayList<>();
        cell.add("cell1");
        cell.add("cell2");
        cell.add("cell3");
        List<String> cell2 = new ArrayList<>();
        cell2.add("中文");
        cell2.add("大哥");
        cell2.add("cell23");
        cell2.add("cell24");
        rowList.add(cell);
        rowList.add(cell2);
        return rowList;
    }
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

• 结果
  

3.2 有模板

• 代码

 /**
     * 有模板
     *
     * @param response
     * @throws Exception
     */
    @RequestMapping("/downloadTemplate")
    public void downloadTemplate(HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = UUID.randomUUID().toString();
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 头部背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(response.getOutputStream(), EasyExcelTestExportDto.class).sheet("sheet1").registerWriteHandler(horizontalCellStyleStrategy).doWrite(dataTemplate());
    }

    protected List<?> dataTemplate() {
        List<EasyExcelTestExportDto> rowList = new ArrayList<>();
        EasyExcelTestExportDto cell = new EasyExcelTestExportDto();
        EasyExcelTestExportDto cell2 = new EasyExcelTestExportDto();
        cell.setName("name1");
        cell.setSex("sex1");
        cell.setAge(1);
        cell2.setName("name2");
        cell2.setSex("sex");
        cell2.setAge(2);

        rowList.add(cell);
        rowList.add(cell2);
        return rowList;
    }
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

@Data
public class EasyExcelTestExportDto {

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "性别")
    private String sex;

    @ExcelProperty(value = "年龄")
    private int age;
}
1
2
3
4
5
6
7
8
9
10
11
12

• 结果
  

4 复杂模板导出

4.1 模板样式

注意：模板放在resources资源文件下

4.2 单元测试

4.3 导出结果

4.4 工具类-EasyExcelUtil

本工具类可根据自身需求进行调整

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.map.LinkedMap;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

public class EasyExcelUtil {

    public static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);

    /**
     * @param cellMap  LinkedMap<String, String>  key List存储对象成员变量,value excel头部
     * @param dataList List excel 内容体
     * @throws Exception
     */
    public static void dataExport(LinkedMap<String, String> cellMap, List dataList, HttpServletResponse response) throws Exception {

        try {
            // 获取excel翻译文件内的Map数据
            List<List<String>> cellList = new ArrayList<>();
            // excel头部数据
            cellList.add(handleHeaderData(cellMap));
            // excel 内容数据
            handleContent(cellList, cellMap, dataList);
            download(cellList, response);
        } catch (Exception e) {
            throw e;
        }
    }

    /**
     * @param cellMap
     * @param dataList
     * @param fileName
     * @throws Exception
     */
    public static void dataExport(LinkedMap<String, String> cellMap, List dataList, String fileName, HttpServletResponse response) throws Exception {

        try {
            // 获取excel翻译文件内的Map数据
            List<List<String>> cellList = new ArrayList<>();
            // excel头部数据
            cellList.add(handleHeaderData(cellMap));
            // excel 内容数据
            handleContent(cellList, cellMap, dataList);
            download(cellList, fileName, response);
        } catch (Exception e) {
            throw e;
        }
    }

    /**
     * excel头部数据
     *
     * @param cellMap
     * @return
     */
    private static List<String> handleHeaderData(LinkedMap<String, String> cellMap) {
        List<String> resultList = new ArrayList<>();
        for (String key : cellMap.keySet()) {
            resultList.add(cellMap.get(key));
        }
        return resultList;
    }

    /**
     * excel 内容数据封装
     *
     * @param cellList
     * @param cellMap
     * @param dataList
     * @return
     */
    private static List<List<String>> handleContent(List<List<String>> cellList, LinkedMap<String, String> cellMap, List<?> dataList) {
        if (CollectionUtils.isEmpty(dataList)) {
            return cellList;
        }
        cellList.addAll(
                dataList.stream().map(dto -> {
                    List<String> resultList = new ArrayList<>();
                    for (String key : cellMap.keySet()) {
                        Class clazz = dto.getClass();
                        // 无该方法,赋空值
                        try {
                            Method method = clazz.getDeclaredMethod("get" + key.substring(0, 1).toUpperCase() + key.substring(1));
                            resultList.add(method.invoke(dto).toString());
                        } catch (Exception e) {
                            resultList.add("");
                        }
                    }
                    return resultList;
                }).collect(Collectors.toList()));

        return cellList;

    }

    // 下载随机文件名
    private static void download(List<List<String>> cellData, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 随机生成文件名
        String fileName = UUID.randomUUID().toString();
        response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 头部背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(response.getOutputStream()).sheet("sheet").registerWriteHandler(horizontalCellStyleStrategy).doWrite(cellData);

    }

    // 下载设置文件名
    private static void download(List<List<String>> cellData, String fileName, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 随机生成文件名
        if (StringUtils.isEmpty(fileName)) {
            fileName = UUID.randomUUID().toString();
        }
        response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));

        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 头部背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(response.getOutputStream()).sheet("sheet").registerWriteHandler(horizontalCellStyleStrategy).doWrite(cellData);
    }

    /**
     * 获取excel导出模板文件
     *
     * @param excelTemplatePath resources目录下路径
     * @return
     */
    public static InputStream getResourcesFileByPath(String excelTemplatePath) {
        ClassPathResource resource = new ClassPathResource(excelTemplatePath);
        try {
            return resource.getInputStream();
        } catch (Exception e) {
            logger.error("error {}", e);
        }
        return null;
    }

    /**
     * @param templatePath 导出模板路径,必须在resource路径下
     * @param mapData      map
     * @param listData     list
     * @param response
     * @throws Exception
     */
    public static void dataExportByTemplate(String templatePath, Map<String, String> mapData, List listData, HttpServletResponse response) throws Exception {

        InputStream templateInputStream = getResourcesFileByPath(templatePath);
        if (templateInputStream == null) {
            throw new Exception("excel模板不存在:" + templatePath);
        }
        try {
            // 下载,数据填充
            httpDownloadByTemplate(mapData, listData, templateInputStream, null, response);
        } catch (Exception e) {
            throw e;
        } finally {
            templateInputStream.close();
        }

    }

    /**
     * @param templatePath 导出模板路径,必须在resource路径下
     * @param mapData      map
     * @param listData     list
     * @param fileName     导出文件名
     * @param response
     * @throws Exception
     */
    public static void dataExportByTemplate(String templatePath, Map<String, String> mapData, List listData, String fileName, HttpServletResponse response) throws Exception {

        InputStream templateInputStream = getResourcesFileByPath(templatePath);
        if (templateInputStream == null) {
            throw new Exception("excel模板不存在:" + templatePath);
        }
        // 下载,数据填充
        httpDownloadByTemplate(mapData, listData, templateInputStream, fileName, response);
    }


    /**
     * @param mapData     其他填充数据
     * @param listData    列表数据
     * @param inputStream 模板文件路径
     * @param fileName    文件名称
     * @param response    输出六
     * @throws Exception
     */
    public static void httpDownloadByTemplate(Map<String, String> mapData, List listData, InputStream inputStream, String fileName, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 随机生成文件名
        if (StringUtils.isEmpty(fileName)) {
            fileName = UUID.randomUUID().toString();
        }
        response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        //读取excel
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(inputStream).build();
        // 第一个sheet
        WriteSheet writeSheet = EasyExcel.writerSheet(0).build();

        if (Objects.nonNull(mapData)) {
            excelWriter.fill(mapData, writeSheet);
        }
        if (CollectionUtils.isNotEmpty(listData)) {
            excelWriter.fill(listData, writeSheet);
        }
        excelWriter.finish();

    }

}


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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247