Exel文件的导入和导出

古城微笑少年丶 2022-04-24 02:20 155阅读 0赞

# Exel文件的导入和导出 #

# 1.POM文件添加依赖 #

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>1.1.2-beat1</version>
    </dependency>

#  #

# 2. 导出静态文件模板 #

在resource包下，添加一个Excel文件，然后在controller中定义文件名字符串，如下：  
  
![image.png][]

/** * 导入excel模板文件名 */
        private static final String TEMPLATE_EXCEL_FILE_NAME = "xxxx.xlsx";
    
    		@ApiOperation("获取模板")
        @GetMapping(path = "getTemplate", produces = "application/octet-stream;charset=ISO8859-1")
        public void getTemplate() throws IOException { 
            response.setHeader("Content-Disposition", "attachment;filename=xxxx.xlsx");
            InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(TEMPLATE_EXCEL_FILE_NAME);
            ServletOutputStream outputStream = response.getOutputStream();
            IOUtils.copy(inputStream, outputStream);
            outputStream.flush();
        }

#  #

# 3. 生成Excel文件导出 #

定义好需要导出的数据类，谨记需要继承BaseRowModel类

/** * @author yanhuada */
    @Setter
    @Getter
    @ApiModel
    public class ExcelOrderRsp extends BaseRowModel { 
    
        @ExcelProperty(value = "订单号", index = 0)
        private String id;
    
        @ExcelProperty(value = "用户姓名", index = 1)
        @ApiModelProperty(value = "用户姓名")
        private String username;
    
        @ExcelProperty(value = "联系电话", index = 2)
        private String phone;
    
        @ExcelProperty(value = "商品名称", index = 3)
        private String goodName;
    
        @ExcelProperty(value = "订单类型", index = 4)
        private String type;
    
        @ExcelProperty(value = "订单金额", index = 5)
        private BigDecimal money;
    
        @ExcelProperty(value = "支付途径", index = 6)
        private String payWay;
    
        @ExcelProperty(value = "订单状态", index = 7)
        private String status;
    
        @ExcelProperty(value = "下单时间", index = 8)
        protected String createdAt;
    
        public ExcelOrderRsp(
                String id,
                String username,
                String phone,
                String goodName,
                String type,
                BigDecimal money,
                String payWay,
                String status,
                String createdAt) { 
            this.setId(id);
            this.setUsername(username);
            this.setPhone(phone);
            this.setGoodName(goodName);
            this.setType(type);
            this.setMoney(money);
            this.setPayWay(payWay);
            this.setStatus(status);
            this.setCreatedAt(createdAt);
        }
    
    }

在controller中导出时，需要做一些处理，如下：

@ApiOperation(value = "Excel导出", nickname = "/exportExcel")
        @GetMapping(value = "/exportExcel")
        public ResponseDto exportExcel(@Validated ExcelOrderReq req) throws IOException { 
            response.setHeader("Content-Disposition", "attachment;filename=Order.xlsx");
            ServletOutputStream out = response.getOutputStream();
            List<Order> sourceList = platformOrderService.exportExcel(req);
            List<ExcelOrderRsp> rspList = OrderPoDtoUtil.copyExcelOrderRspList(sourceList);
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            Sheet sheet = new Sheet(1, 0, ExcelOrderRsp.class);
            writer.write(rspList, sheet).finish();
            out.flush();
            return new ResponseDto();
        }

# 4.导入Excel文件数据 #

同样，第一步，我们得先定义好数据载体对象类，如下：

/** * @author yanhuada */
    
    @Setter
    @Getter
    @ApiModel
    public class EnrollmentPlanExcelImportBo extends BaseRowModel { 
    
        @ExcelProperty(value = "学校名称", index = 0)
        private String universityName;
    
        @ExcelProperty(value = "年份", index = 1)
        private Integer year;
    
        @ExcelProperty(value = "学校编码", index = 2)
        private String code;
    
        @ExcelProperty(value = "学历层次", index = 3)
        private String degree;
    
        @ExcelProperty(value = "专业名称", index = 4)
        private String professionName;
    
        @ExcelProperty(value = "招生人数", index = 5)
        private Integer enrollCount;
    
        @ExcelProperty(value = "科类", index = 6)
        private String subjectType;
    
        @ExcelProperty(value = "专业类别", index = 7)
        private String proType;
    }

然后，我们在controller层接收导入数据，如下：

@ApiOperation(value = "导入Excel", nickname = "/importExcel")
        @PostMapping(value = "/importExcel")
        public ResponseDto importExcel(MultipartFile file) throws IOException { 
            InputStream inputStream = file.getInputStream();
            List<Object> objectList = EasyExcelFactory.read(inputStream, new Sheet(1, 2, EnrollmentPlanExcelImportBo.class));
            platformEnrollmentPlanService.importExcel(objectList);
            return new ResponseDto();
        }

# 5.总结。 #

Excel导出和导入就是这么简单，我们需要记得，对象类载体必须要继承BaseRowModel类。

[image.png]: /images/20220221/c800b9896c864e698d1b44a3a716bd8e.png