MybatisPlus+EasyExcel使用

MybatisPlus+EasyExcel使用

1-引入EasyExcel

      <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.83</version>
        </dependency>
         <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
            <version>3.5.5</version>
        </dependency>

2-数据类

@Getter
@Setter
@EqualsAndHashCode
@TableName("`demo_data`")
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;

}

3-Mapper接口

@Mapper
public interface DamoDataMapper extends BaseMapper<DemoData> {
}

4-监听器

@Slf4j
@Component
@Scope("prototype")
public class DemoDataListener implements ReadListener<DemoData> {
    private static final int BATCH_COUNT = 100;
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
//        System.out.println("解析到一条数据:{}"+ JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        System.out.println("所有数据解析完成！");
    }

    @Autowired
    @Lazy
    private TestService testService;
    private void saveData() {
        System.out.println("{}条数据，开始存储数据库！"+ cachedDataList.size());
        testService.saveBatch(cachedDataList);
        System.out.println("存储数据库成功！");
    }
}

5-新建Service接口

public interface TestService extends IService<DemoData> {
    void importExcel(MultipartFile multipartFile);
}

6-新建ServiceImpl类

@Service
public class TestServiceImpl extends ServiceImpl<DamoDataMapper, DemoData> implements TestService{

    @Resource
    private DemoDataListener importDataListener;

    @SneakyThrows
    @Override
    public void importExcel(MultipartFile multipartFile) {
        InputStream inputStream = multipartFile.getInputStream();
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
         EasyExcel.read(multipartFile.getInputStream(), DemoData.class, importDataListener).sheet().doRead();
    }
}

7-接口类

1-下载

@GetMapping("/download")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
    try {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        // 这里需要设置不关闭流
        EasyExcel.write(response.getOutputStream(), DemoData.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                .doWrite(data());
    } catch (Exception e) {
        // 重置response
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        Map<String, String> map = MapUtils.newHashMap();
        map.put("status", "failure");
        map.put("message", "下载文件失败" + e.getMessage());
        response.getWriter().println(com.alibaba.fastjson.JSON.toJSONString(map));
    }
}

2-上传

@Resource
private TestService testService;
@PostMapping("/upload")
@ResponseBody
public Result<String> upload(MultipartFile file) throws IOException {
    testService.importExcel(file);
    return Result.success("上传完成");
}

引入Mysql配置

spring:
  servlet:
    multipart:
      max-file-size: 20MB
      max-request-size: 20MB

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/idea_demo?useSSL=false&useUnicode=true&characterEncoding=utf-8&servetTimeZone=Asia/Shanghai&rewriteBatchedStatements=true
    username: root
    password: root