最简单的Java 集成EasyExcel实现excel的导入导出

1、导入pom依赖

     <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

先实现导出：
2、Controller类

    @ApiOperation(value = "通过uuid导出")
    @GetMapping(value = "/{uuid}")
    public void exportExcel(@PathVariable("uuid") String uuid,
                            HttpServletResponse response) throws IOException {
        try {
            //导出的文件名前缀，尽量不要有中文
            String prefix = "Aps";
            //导出的文件名前缀中的日期
            String today = new SimpleDateFormat("-yyyy-MM-dd").format(DateUtils.StartOfToday());
            //拼在一起，导出为Aps-2021-08-26.xlsx
            String filename = prefix.concat(today);
            //MSEXCEL格式
            response.setContentType("application/vnd.ms-excel");
            //指定的类型是文件的扩展名xlsx
            response.setHeader("Content-disposition",
                    "attachment; filename=" + filename + ExcelTypeEnum.XLSX.getValue());
            //实现方法，需要加入到excel中的数据
            apsService.writeToExcel(uuid, response.getOutputStream());
        } catch (IOException e) {
            log.error(String.format("导出异常：%s ", e.getMessage()));
        }
    }

3、ServiceImpl实现类（Service省略）

    @Override
    @Synchronized
    public void outTemplate(String uuid,OutputStream outputStream) throws IOException {
        //通过uuid找到导出的数据
        List<Aps> apsList = aps.findbyuuid(uuid);
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
        //指定excel按照Aps类来显示
        Sheet sheet1 = new Sheet(1, 0, Aps.class);
        //将apsList放入sheet1中
        writer.write(apsList, sheet1);
        //写入完毕后关闭输出流
        writer.finish();
        outputStream.flush();
        outputStream.close();
    }

4、model类

@Data
@NoArgsConstructor
@AllArgsConstructor
//单元格宽度
@ColumnWidth(21)
//设置单元格数据居中
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
//设置标题格式，默认居中，背景色为灰色
@HeadStyle()
public class Aps extends BaseRowModel {

    //导入Excel,index表示第几列
    @ExcelProperty(value = "开始日期(2021/07/27)", index = 0)
    private String beginDate;
    @ExcelProperty(value = "开始时间(18:30)", index = 1)
    private String beginTime;
    @ExcelProperty(value = "名称", index = 2)
    private String name;
}

再来看导入：
2、Controller类

    @ApiOperation(value = "导入")
    @ApiImplicitParam(name = "file", value = "MultipartFile", paramType = "file", required = true)
    @PostMapping(value = "/import")
    //RestResponse是自己写返回类
    public RestResponse<String> excelUpload(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return RestResponse.bad(-1, "file empty");
        }
        //后缀如果为xlsx或者xls才能导入
        if (!file.getOriginalFilename().endsWith(ExcelTypeEnum.XLSX.getValue()) &&
                !file.getOriginalFilename().endsWith(ExcelTypeEnum.XLS.getValue())) {
            return RestResponse.bad(-1, "only excel file support");
        }
        File dest = new File(String.format("%s/upload-%s", UPLOAD_DEST_DIR, file.getOriginalFilename()));
        try {
            //调用自己写的方法，传入导入的文件
            apsService.saveApsScheduleFromExcel(file, dest);
            return RestResponse.good("ok");
        } catch (Exception e) {
            log.error("upload ERROR:" + e.getMessage());
            return RestResponse.bad(500, "Internal Exception");
        }
    }

3、ServiceImpl实现类（Service省略）

    @Override
    public void saveApsScheduleFromExcel(MultipartFile file, File dest) throws IOException, ParseException, MAPEInputException {
        file.transferTo(dest);
        FileInputStream inputStream = new FileInputStream(dest);
        //ExcelListener为自己写的监听类
        ExcelListener listener = new ExcelListener();
        ExcelReader excelReader = new ExcelReader(inputStream, null, listener);
        //Aps为导入的数据对应的类
        excelReader.read(new Sheet(1, 1, Aps.class));
        //表格信息转换为list
        List<Object> data = listener.data;
        //excel中每行的数据封装为data，excel有多少条数据(行)，就有多少个data
        if (data.size() > 0) {
            data.forEach(obj -> {
            .......
            //做一系列操作
            });
            //读取完后清除list中的数据
            listener.clearData();
        }else {
            log.error("导入错误，数据为空");
        }

4、ExcelListener类

/**
 * 解析监听器，
 * 每解析一行会回调invoke()方法。
 * 整个excel解析结束会执行doAfterAllAnalysed()方法
 */
@Slf4j
public class ExcelListener extends AnalysisEventListener {

    public List<Object> data = new ArrayList<>();

    @Override
    public void invoke(Object object, AnalysisContext context) {
        System.out.println(object);
        data.add(object);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析结束销毁不用的资源
    }

    public void clearData() {
        data.clear();
    }
}