[开发测试]Excel导入导出之EasyExcel工具类

废话不多说，直接复制就能用

添加依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

实体类(举例)

@Data
@NoArgsConstructor
@TableName("user")
public class User {
    
    /**
     * @ColumnWidth(value = 10) 列宽度
     * @ExcelProperty(value = "id列标题", index = 0) value是列标题，index是列下标，默认从0开始
     */
    @ColumnWidth(value = 10)
    @ExcelProperty(value = "id", index = 0)
    private Integer id;
    
    @ColumnWidth(value = 15)
    @ExcelProperty(value = "姓名", index = 1)
    private String name;
    
    @ColumnWidth(value = 10)
    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;
    
    @ColumnWidth(value = 30)
    @ExcelProperty(value = "电子邮件", index = 3)
    private String email;
    
    @ColumnWidth(value = 26)
    @ExcelProperty(value = "个性签名", index = 4)
    private String sign;
    
    @ColumnWidth(value = 20)
    @ExcelProperty(value = "修改时间", index = 5)
    private Date updateTime;
    
    @ColumnWidth(value = 20)
    @ExcelProperty(value = "新增时间", index = 6)
    private Date createTime;
    
    /**
     * @ExcelIgnore 表示忽略该字段不导出，默认是会被导出的
     */
    @ExcelIgnore
    private String other;
}

Mapper、Service等省略

Controller

为测试方便，这里不读取数据库数据，生成数据用于导出测试
导入为了测试方便写在Controller，正常业务应该写在Service中，可以更好的控制事务回滚

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;
    
    /**
     * 用户数据导出接口
     * 浏览器或者swagger访问 localhost:8080/user/export就可以把Excel文件下载下来
     * @param response
     * @return
     */
    @GetMapping("/export")
    @ResponseBody
    public Object exportDataToExcel(HttpServletResponse response) {
        List<User> userList = new ArrayList<>();
    
        for (int i = 1; i <= 100; i++) {
            User user = new User();
            user.setId(i);
            user.setName("soulmate" + i * 100);
            user.setAge(i + 10);
            user.setEmail("soulmateqx@163.com" + i * 100);
            user.setSign("" + i * 100);
            user.setUpdateTime(new Date());
            user.setCreateTime(new Date());
            userList.add(user);
        }
        ExcelUtils.ExportDataToExcel(userList, "用户数据", response);
        return "导出成功!";
    }
    
    @PostMapping("/read")
    public String readExcel(@RequestParam MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), User.class, new ReadDataListener<>(userService)).sheet().doRead();
        return "导入成功";
    }
}

工具类

导入(读取Excel)只有一行代码，就直接引用了，工具类里只有导出，导出上半段是设置样式，根据自己需求去自定义

@Slf4j
public class ExcelUtils {
    
    public static void ExportDataToExcel (List<?> data, String fileName, HttpServletResponse response) {
        //设置标题样式
        WriteCellStyle headStyle = new WriteCellStyle();
        //设置字体居中显示
        headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //构造字体对象
        WriteFont writeFont = new WriteFont();
        writeFont.setFontHeightInPoints((short)15);
        // headStyle.setWriteFont(writeFont);
        headStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE1.getIndex());
        //设置content样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        //设置字体垂直居中和水平居中
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        writeFont.setFontHeightInPoints((short)12);  //设置字体大小
        contentStyle.setWriteFont(writeFont);
        contentStyle.setWrapped(true);  //自动换行
        
        //设置边框样式
        contentStyle.setBorderLeft(BorderStyle.DOTTED);
        contentStyle.setBorderTop(BorderStyle.DOTTED);
        contentStyle.setBorderRight(BorderStyle.DOTTED);
        contentStyle.setBorderBottom(BorderStyle.DOTTED);
        
        //水平单元格样式策略, 头是头的样式 内容是内容的样式
        HorizontalCellStyleStrategy cellStyleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);
        
        ExcelWriter excelWriter = null;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            fileName = URLEncoder.encode(fileName + "-" + System.currentTimeMillis() , "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName + ".xlsx");
            // 这里 需要指定写用哪个class去写
            excelWriter = EasyExcel.write(response.getOutputStream(), data.get(0).getClass()).build();
            // 这里注意 如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").registerWriteHandler(cellStyleStrategy).build();
            excelWriter.write(data, writeSheet);
            log.info("数据导出完成");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
}

数据读取监听类

/**
 * 模板的读取类
 * 有个很重要的点 ReadDataListener不能被spring管理，
 * 要每次读取excel都要new,然后里面用到spring可以构造方法传进去
 */
@Slf4j
public class ReadDataListener<T> implements ReadListener<T> {
    /**
     * 每隔几条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 20;
    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 如果不用存储这个对象没用。
     */
    private final IService<T> service;

    /**
     * 每次创建Listener的时候需要把spring管理的类传进来
     * @param service
     */
    public ReadDataListener(IService<T> service) {
        this.service = service;
    }

    /**
     * 这个每一条数据解析都会来调用
     * @param context
     */
    @Override
    public void invoke(T t, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(t)); // 这里用的fastjson
        cachedDataList.add(t);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        if (cachedDataList.size() > 0) {
            saveData();
        }
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        service.saveOrUpdateBatch(cachedDataList);
        log.info("存储数据库成功！");
    }
}

application.yml

新建项目方便复制

server:
  port: 8080

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456

拜拜！

2022深度学习开发者峰会

5月20日13:00让我们相聚云端，共襄盛会！