SpringBoot 实现Execl 导入导出

1、引包

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

2、导入样例?

 @ApiOperation(notes = "Excel业务导入", value = "Excel业务导入")
    @PostMapping("/businessFromExcel")
    public HttpResponse businessFromExcel(@RequestPart("file") MultipartFile file) throws Exception {
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        List<Business> list = ExcelImportUtil.importExcel(
                file.getInputStream(),
                Business.class, params);
        boolean result = businessService.insertBusiness(list);
        if(result){
            return new HttpResponse<>().success();
        }else{
            return new HttpResponse<>().error(500, "业务导入失败");
        }
    }

execl 样例

3、导出样例

 @ApiOperation(notes = "查询扩缩容导出Excel", value = "查询扩缩容导出Excel")
    @GetMapping("/resourceToExcel")
    public Object getScaleResourceToExcel(@RequestParam(name = "resourceType", required = false)  @ApiParam(value = "资源类型") String resourceType,
                                          @RequestParam(name = "status", required = false) @ApiParam(value = "状态") Integer status,
                                          @RequestParam(name = "startTime", required = false) @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @ApiParam(value = "开始时间", format = "yyyy-MM-dd HH:mm:ss", example = "2024-01-01 10:10:10") Date startTime,
                                          @RequestParam(name = "endTime", required = false) @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @ApiParam(value = "结束时间", format = "yyyy-MM-dd HH:mm:ss", example = "2024-01-01 10:10:10") Date endTime) throws IOException {
        List<Scale> list = scaleService.getScaleResourceList(resourceType, status,  startTime, endTime);

        ExportParams exportParams = new ExportParams();
        exportParams.setType(ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Scale.class, list);

        ByteArrayOutputStream byteOutputStream  = new ByteArrayOutputStream();
        workbook.write(byteOutputStream);
        Map dataBase64 = ImmutableMap.of("filename","扩缩容.xlsx","data", Base64.encodeBase64String(byteOutputStream.toByteArray()));
        return new HttpResponse<>().success(dataBase64);
    }