编程随笔
华总
2023-11-06
0
0
目录

EasyExcel之Excel导入导出转载

# 基本导入导出

# Pom依赖

提示

图片的绿色小字就是最新版本： Maven central (opens new window)

如果看不到图片则直接打开：https://mvnrepository.com/artifact/com.alibaba/easyexcel (opens new window)

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>最新版本</version>
</dependency>
1
2
3
4
5

# 导出Excel

User类

package tech.pdai.springboot.file.excel.easyexcel.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author pdai
 */
@Builder
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements BaseEntity {

    /**
     * user id.
     */
    @ExcelProperty("ID")
    private Long id;

    /**
     * username.
     */
    @ExcelProperty("Name")
    private String userName;

    /**
     * email.
     */
    @ExcelProperty("Email")
    private String email;

    /**
     * phoneNumber.
     */
    @ExcelProperty("Phone")
    private long phoneNumber;

    /**
     * description.
     */
    @ExcelProperty("Description")
    private String description;

}
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

UserController中导出的方法

@ApiOperation("Download Excel")
@GetMapping("/excel/download")
public void download(HttpServletResponse response) {
    try {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition",
                "attachment;filename=user_excel_" + System.currentTimeMillis() + ".xlsx");
        userService.downloadExcel(response.getOutputStream());
    } catch (Exception e) {
        e.printStackTrace();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

UserServiceImple中导出Excel的主方法

@Override
public void downloadExcel(ServletOutputStream outputStream) {
    EasyExcelFactory.write(outputStream, User.class).sheet("User").doWrite(this::getUserList);
}
private List<User> getUserList() {
    return Collections.singletonList(User.builder()
            .id(1L).userName("pdai").email("pdai@pdai.tech").phoneNumber(121231231231L)
            .description("hello world")
            .build());
}
1
2
3
4
5
6
7
8
9
10

导出后的excel如下

img

# 导入Excel

我们将上面导出的excel文件导入。

UserController中导入的方法

@ApiOperation("Upload Excel")
@PostMapping("/excel/upload")
public ResponseResult<String> upload(@RequestParam(value = "file", required = true) MultipartFile file) {
    try {
        userService.upload(file.getInputStream());
    } catch (Exception e) {
        e.printStackTrace();
        return ResponseResult.fail(e.getMessage());
    }
    return ResponseResult.success();
}
1
2
3
4
5
6
7
8
9
10
11

UserServiceImple中导入Excel的主方法

@Override
public void upload(InputStream inputStream) throws IOException {
    // ReadListener不是必须的，它主要的设计是读取excel数据的后置处理(并考虑一次性读取到内存潜在的内存泄漏问题)
    EasyExcelFactory.read(inputStream, User.class, new ReadListener<User>() {

        @Override
        public void invoke(User user, AnalysisContext analysisContext) {
            cachedDataList.add(user);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            cachedDataList.forEach(user -> log.info(user.toString()));
        }
    }).sheet().doRead();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

通过PostMan进行接口测试

img

这里注意下，需要有字体的支持，比如如果没有字体支撑将会报如下告警：

Warning: the font "Times" is not available, so "Lucida Bright" has been substituted, but may have unexpected appearance or behavor. Re-enable the "Times" font to remove this warning.
1

# 填充Excel模板

我们先来准备一个excel模板，考虑了横向表和纵向列表，以及单一信息等，基本上能满足多数的应用场景。

img

UserController中下载填充后的Excel方法

@ApiOperation("Fill Excel Template")
@GetMapping("/excel/fill")
public void fillTemplate(HttpServletResponse response) {
    try {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition",
                "attachment;filename=user_excel_template_" + System.currentTimeMillis() + ".xlsx");
        userService.fillExcelTemplate(response.getOutputStream());
    } catch (Exception e) {
        e.printStackTrace();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

UserServiceImpl中填充excel模板的方法

// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
// {} 代表普通变量 {.} 代表是list的变量 {前缀.} 前缀可以区分不同的list
@Override
public void fillExcelTemplate(ServletOutputStream outputStream) {

    // 确保文件可访问，这个例子的excel模板，放在根目录下面
    String templateFileName = "/Users/pdai/Downloads/user_excel_template.xlsx";

    // 方案1
    try (ExcelWriter excelWriter = EasyExcelFactory.write(outputStream).withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
        // 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 userList，然后多个list必须用 FillWrapper包裹
        excelWriter.fill(new FillWrapper("userList", getUserList()), fillConfig, writeSheet);
        excelWriter.fill(new FillWrapper("userList", getUserList()), fillConfig, writeSheet);

        excelWriter.fill(new FillWrapper("userList2", getUserList()), writeSheet);
        excelWriter.fill(new FillWrapper("userList2", getUserList()), writeSheet);

        Map<String, Object> map = new HashMap<>();
        map.put("user", "pdai");
        map.put("date", new Date());

        excelWriter.fill(map, writeSheet);
    }
}
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

访问http://localhost:8080/user/excel/fill 下载

img

# 示例源码

https://github.com/realpdai/tech-pdai-spring-demos (opens new window)

# 参考文章

https://easyexcel.opensource.alibaba.com/docs/current/ (opens new window)

# easyExcel导入Excel 返回错误信息所属行，并加入非空验证判断

在项目中肯定会存在一些excel导入功能，存在的问题：导入的时候类型转换异常，如果只抛出错误异常的话，用户也看不懂错误信息，不如给用户提示是那一行的第几列的数据有异常。

导入的框架是easyExcel： https://easyexcel.opensource.alibaba.com/ (opens new window)

# 1. 返回错误信息所属行功能

# ExcelListener 监听器代码

@Slf4j
public final class ExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * 自定义用于暂时存储data
     * 可以通过实例获取该值
     */
    private List<T> datas = new ArrayList<>();

    /**
     * 每解析一行都会回调invoke()方法
     * @param data  读取后的数据对象
     * @param context 内容
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
       
            datas.add(data);
      
    }

    /**
     * 读取完后操作
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
     log.info("所有数据读取完成");
    }

    /**
     * 异常方法 (类型转换异常也会执行此方法)  （读取一行抛出异常也会执行此方法)
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.info("有异常");
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
            throw new RuntimeException("第"+excelDataConvertException.getRowIndex()+"行" +
                    "，第" + (excelDataConvertException.getColumnIndex() + 1) + "列读取错误");
        }
    }

    /**
     * 返回数据
     * @return 返回读取的数据集合
     **/
    public List<T> getDatas() {
        return datas;
    }
}
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

将监听器的类型定义成泛型的好处是 无论做那个表的导入功能 只需要这一个监听器即可，具体的业务方法交给了Service 注意：RuntimeException 异常是java的运行时异常，如果公司有专门定义的异常类 替换就可以

# 实体类代码

@Data
@TableName("sys_test")
public class SysTestEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * id
     */
    @TableId(type = IdType.ASSIGN_ID)
    private String id;

    /**
     * 姓名
     */
    @ExcelProperty("姓名")
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty("年龄")
    private Integer age;

    /**
     * 手机号
     */
    @ExcelProperty("手机号")
    private Integer phone;

    /**
     * 工资
     */
    @ExcelProperty("工资")
    private BigDecimal salary;

    /**
     * 生日
     */
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("生日")
    private Date birthday;

}
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

# 控制器代码

@RestController
@RequestMapping("test/systest")
public class SysTestController {
    @Autowired
    private SysTestService sysTestService;
    /**
 * 模版导入
 * @param file
 * @return
 * @throws IOException
 */
    @PostMapping("upload")
    @ResponseBody
    public Result upload(MultipartFile file) throws IOException {
            //使用泛型指定实体类
            ExcelListener<SysTestEntity> excelListener = new ExcelListener<>();
            //读取数据
           EasyExcel.read(file.getInputStream(),SysTestEntity.class,excelListener).headRowNumber(1).sheet(0).doRead();
            //获取读取的数据
            List<SysTestEntity> list = excelListener.getDatas();
             //使用批量添加方法
            sysTestService.saveBatch(list);
            return ResultUtil.success("导入成功");
    }
}
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

# 2. 实现非空校验

非空校验实现思路

  1. 自定义注解，定义一下错误信息
  2. 自定义解析器，通过反射获取类的信息，根据注解去做校验，如果输入为空就抛出异常

# 自定义注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelValid {
    String message() default "导入有未填入的字段";
}
1
2
3
4
5

# 自定义检验器

public class ExcelImportValid {
    /**
     * Excel导入字段校验
     *
     * @param object 校验的JavaBean 其属性须有自定义注解
     */
    public static void valid(Object object) {
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            //设置可访问
            field.setAccessible(true);
            //属性的值
            Object fieldValue = null;
            try {
                fieldValue = field.get(object);
            } catch (IllegalAccessException e) {
                throw new RuntimeException("导入参数检查失败");
            }
            //是否包含必填校验注解
            boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);
            if (isExcelValid && Objects.isNull(fieldValue)) {
                System.out.println("导入错误");
                System.out.println(field.getAnnotation(ExcelValid.class).message());
                throw new RuntimeException("NULL"+field.getAnnotation(ExcelValid.class).message());
            }
        }
    }
}
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

# 实体类加入注解

@Data
@TableName("sys_test")
public class SysTestEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * id
     */
    @TableId(type = IdType.ASSIGN_ID)
    private String id;

    /**
     * 姓名
     */
    @ExcelProperty("姓名")
    @ExcelValid(message = "姓名不能为空")
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty("年龄")
    private Integer age;

    /**
     * 手机号
     */
    @ExcelProperty("手机号")
    private Integer phone;

    /**
     * 工资
     */
    @ExcelProperty("工资")
    private BigDecimal salary;

    /**
     * 生日
     */
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("生日")
    private Date birthday;

}
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

# ExcelListener 监听器代码

@Slf4j
public final class ExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * 自定义用于暂时存储data
     * 可以通过实例获取该值
     */
    private List<T> datas = new ArrayList<>();

    /**
     * 每解析一行都会回调invoke()方法
     * @param data  读取后的数据对象
     * @param context 内容
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        try {
            ExcelImportValid.valid(data);
            datas.add(data);
        } catch (Exception e) {
            // 校验失败，处理异常
            System.out.println("校验失败：" + e.getMessage());
            // 可以根据需要采取其他处理措施
            throw new ApiException(e.getMessage());
        }
    }

    /**
     * 读取完后操作
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
     log.info("所有数据读取完成");
    }

    /**
     * 异常方法 (类型转换异常也会执行此方法)  （读取一行抛出异常也会执行此方法)
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.info("有异常");
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
            throw new ApiException("第"+excelDataConvertException.getRowIndex()+"行" +
                    "，第" + (excelDataConvertException.getColumnIndex() + 1) + "列读取错误");
        }
        //抛出非空校验异常
        throw new ApiException(exception.getMessage());
    }

    /**
     * 返回数据
     * @return 返回读取的数据集合
     **/
    public List<T> getDatas() {
        return datas;
    }
}
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

有个坑： invoke方法抛出异常后 系统还是显示导入成功，也打印校验失败错误信息，在我仔细阅读官方文档后发现，抛出异常后会执行onException方法，需要也在onException方法将异常信息抛出去才可以

#后端#springboot#EasyExcel
上次更新: 2023/12/09 16:19:24
HTTP各种参数发送
EasyExcel具体使用

← EasyExcel具体使用→

最近更新
01
element-plus多文件手动上传 原创
11-03
02
TrueLicense 创建及安装证书 原创
10-25
03
手动修改迅捷配置 原创
09-03
04
安装 acme.sh 原创
08-29
05
zabbix部署 原创
08-20
更多文章>
Copyright © 2023-2024 liyao52033