使用EasyExcel实现Excel的导入、导出、下载模板等功能_easyexcel下载模板

参考：https://easyexcel.opensource.alibaba.com/docs/2.x/quickstart/write

导入功能

依赖

在项目的pom文件导入easyexcel的依赖：

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

实体类

创建与数据库表对应的实体类，并且添加与excel表头对应的注解@ExcelProperty ，要记得修改下面index的值：value对应excel的表头字段名称，index为表头顺序

import com.alibaba.excel.annotation.ExcelProperty;

import lombok.Data;

@Data
public class Entity {
	//这里的value对应excel的表头字段名称，index为表头顺序
    @ExcelProperty(value = "名称1",index = 0)
    private String name1;
    @ExcelProperty(value = "名称2",index = 1)
    private String name2;
}

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

监听器

//监听器
public class ExcelListener extends AnalysisEventListener<Entity> {
    MyService myService;
    List<Entity> list = new ArrayList<>();
    public  ExcelListener (MyService myService){
        this.myService= myService;
    }

    //读取除表头外的每行数据执行该方法
    @Override
    public void invoke(Entity entity, AnalysisContext context) {
        list.add(entity);
    }
    
    //读取结束执行该方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //把与数据库重复的数据删除
        List<Entity> entityList = myService.queryListByExcel(list);
        for (Entity entity : entityList ) {
            list.remove(entity);
        }
		//保存到数据库中
        myService.saveList(list);
    }
}

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

控制器

	//控制器
    @PostMapping("/import")
    @ApiOperation("数据导入")
    public String easyExeclImport(@RequestParam(value = "file") MultipartFile file) throws IOException {
         InputStream inputStream = file.getInputStream();
         //service要通过参数传进去
         EasyExcel.read(inputStream, Entity.class, new NbqExcelListener(myService)).sheet().doRead();
       return "success";
    }

1
2
3
4
5
6
7
8
9
10

批量插入

示例

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.demo.mapper.MedicnesMapper">
    <insert id="saveMedicnesList" parameterType="cn.demo.entity.Medicnes">
        insert into medices
        (food,remark)
        values
        <foreach collection="list" item="rm" separator=",">
            (#{rm.food},#{rm.remark})
        </foreach>
    </insert>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12

导出功能

实体类

@Data
public class ExcelLog {

    @ColumnWidth(40)  //设置列宽
    @ExcelProperty("ID") //Excel表头名
    private String id;

    @ColumnWidth(15)
    @ExcelProperty("日期")
    private String statis_date;
}
1
2
3
4
5
6
7
8
9
10
11

使用

Controller

    @ApiOperation("Excel导出日志")
    @PostMapping("/exportExcel")
    public ResultBean exportExcel(@RequestBody OperationLog dailyLog,
                                   HttpServletResponse response) throws IOException {
        return ResultBean.success(logService.exportExcel(dailyLog,response));
    }
1
2
3
4
5
6

ServiceImpl

    @Override
    public String exportExcel(OperationLog dailyLog, HttpServletResponse response) throws IOException {
		// 根据条件查询出数据
        List<ExcelLog> list = logMapper.queryLogExcel(dailyLog);

		// 设置响应信息，让浏览器下载文件
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("日志表", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        // 向Excel中写入数据
        EasyExcel.write(response.getOutputStream(), ExcelLog.class)
                .sheet("sheet1")
                .doWrite(list);

        return "导出成功！";
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

---

另一种，导出到本地指定文件路径

    public String exportExcel(OperationLog dailyLog) {
        List<ExcelLog> list = logMapper.queryLogExcel(dailyLog);
        String filename = "D:\\linshi\\log1.xlsx";

        // 向Excel中写入数据 也可以通过 head(Class<?>) 指定数据模板
        EasyExcel.write(filename, ExcelLog.class)
                .sheet("日志信息")
                .doWrite(list);

        return "导出成功！";
    }
1
2
3
4
5
6
7
8
9
10
11

EasyExcel实现Excel文件导入导出
easyexcel导出
EasyExcel导出详解

导出多个表头和数据

依赖

        <!-- easyexcel工具 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
1
2
3
4
5
6

实体类

表1实体类

@Data
public class SourceBean {
    @ColumnWidth(11)
    @ExcelProperty("渠道")
    private String source_type;
    @ColumnWidth(11)
    @ExcelProperty("请求量")
    private String request_num;

}
1
2
3
4
5
6
7
8
9
10

表2实体类

@Data
public class SceneBean {

    @ColumnWidth(11)
    @ExcelProperty("日期")
    private String statis_date;
    @ColumnWidth(11)
    @ExcelProperty("场景")
    private String scene_name;
    @ColumnWidth(11)
    @ExcelProperty("数量")
    private String num;
}
1
2
3
4
5
6
7
8
9
10
11
12
13

设置文本内容

    private List<List<String>> getIndexContent(String content, Boolean isBankRow) {
        // 设置内容
        List<List<String>> res = new ArrayList<>();
        res.add(Collections.singletonList(content));
        if (isBankRow) {
            res.add(Collections.singletonList(""));
            res.add(Collections.singletonList(""));
        }
        return res;
    }
1
2
3
4
5
6
7
8
9
10

获取数据

    private List<SourceBean> getSourceData() {
        List<SourceBean> res = exceptionMapper.getSourceData();
        // 设置数据后面空行
        res.add(new SourceBean());
        res.add(new SourceBean());
        return res;
    }

    private List<SceneBean> getSceneData() {
        List<SceneBean> res = exceptionMapper.getSceneData();
        res.add(new SceneBean());
        res.add(new SceneBean());
        return res;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

使用

    public String exportExcel() {
        // 配置
        String file = "D:\\temp\\excel";
        File dir = new File(file);
        dir.mkdirs();
        String fileName = file + "\\分析"+ LocalDate.now().toString().replace("-","") +".xlsx";
        //创建EXCEL对象
        ExcelWriterBuilder builder = EasyExcel.write(fileName);
        //设置处理器，合并单元格，列宽处理器等
        builder.registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 15, (short) 15));
        builder.registerWriteHandler(new SimpleColumnWidthStyleStrategy(10));
        //获取writer对象
        ExcelWriter writer = builder.build();

        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 头的背景设置为黄色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW1.getIndex());
        WriteFont headWriteFont = new WriteFont();
        // 头的字体粗细
        headWriteFont.setFontHeightInPoints((short)10);
        // 头的字体颜色
        //headWriteFont.setColor(IndexedColors.YELLOW.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
         这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        //contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
         背景绿色
        //contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        //WriteFont contentWriteFont = new WriteFont();
         字体大小
        //contentWriteFont.setFontHeightInPoints((short)10);
        //contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        WriteSheet sheet = EasyExcel.writerSheet(0, "分析表").registerWriteHandler(horizontalCellStyleStrategy).build();

        // 渠道统计
        List<List<String>> headSource = getIndexContent("渠道统计",false);
        writer.write(headSource, sheet);
        List<SourceBean> dataSource = getSourceData();
        // 注意这里 table 的编号
        WriteTable tableSource = EasyExcel.writerTable(1).needHead(Boolean.TRUE).head(SourceBean.class).build();
        writer.write(dataSource, sheet, tableSource);

        // 场景统计
        List<List<String>> headScene = getIndexContent("场景统计",false);
        writer.write(headScene, sheet);
        List<SceneBean> dataScene = getSceneData();
        // 注意这里 table 的编号
        WriteTable tableScene = EasyExcel.writerTable(2).needHead(Boolean.TRUE).head(SceneBean.class).build();
        writer.write(dataScene, sheet, tableScene);

        // 关闭
        writer.finish();
        return "导出成功";
    }
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

效果

下载模板

实体类

@Data
public class EntityBean {
    @ExcelProperty(value = "aa",index = 0)
    private String aa;
    @ExcelProperty(value = "bb",index = 1)
    private String bb;
}
1
2
3
4
5
6
7

控制层

    @ApiOperation("下载Excel模板")
    @RequestMapping(value = "/myExcelDownload",method = RequestMethod.GET)
    public CommonResult<String> myExcelDownload(HttpServletResponse response) throws IOException {
        service.myExcelDownload(response);
        return CommonResult.success(null, "下载成功!");
    }
1
2
3
4
5
6

业务层

    /**
     * 下载excel模板
     * @param response
     */
    public void myExcelDownload(HttpServletResponse response) throws IOException {

        List<ExcelBean> list = new ArrayList<>();
        ExcelBean bean = new ExcelBean();
        bean.setAa("aa");
        bean.setBb("bb");

        list.add(bean);

        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("数据表格模板", "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ExcelBean.class).sheet("my模板").doWrite(list);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

下载资源下的excel模板

模板路径：classpath:static/templates/信息表(模板).xlsx

    /**
     * 下载信息excel模板
     * @return
     * @throws Exception
     */
    @RequestMapping("/downloadTemplate")
    public ResponseEntity<Resource> downloadTemplate() throws Exception {
        String fileName = "信息表(模板).xlsx";
        HttpHeaders headers = new HttpHeaders();
        headers.add("Content-disposition", "attachment;filename=".concat(URLEncoder.encode(fileName, "UTF-8")));
        headers.setContentType(MediaType.parseMediaType("application/vnd.ms-excel"));
        headers.set(HttpHeaders.CONTENT_ENCODING, "utf-8");

        ResourceLoader loader = new DefaultResourceLoader();
        Resource resource = loader.getResource("classpath:static/templates/信息表(模板).xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(resource);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20