sspringboot整合easyexcel

excel导入导出

product.java 实体类 

依赖 

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

 实体类   注意：lombok插件和easyexcel会有冲突  导致插入数据为空

value: 表头名称 

index: 列的号, 0表示第一列

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Product extends BaseRowModel implements Serializable {

  @ExcelProperty(value = {"订单号"},index = 0)
  private Integer id;
  @ExcelProperty(value = {"商品名"},index = 1)
  private String name;
  @ExcelProperty(value = {"商品号"},index = 2)
  private Integer parentId;
  @ExcelProperty(value = {"类型"},index = 3)
  private Integer type;

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public Integer getParentId() {
    return parentId;
  }

  public void setParentId(Integer parentId) {
    this.parentId = parentId;
  }

  public Integer getType() {
    return type;
  }

  public void setType(Integer type) {
    this.type = type;
  }
}

ServiceImpl业务方法 

  /**
     * 通过http请求响应得到的
     * @param response
     * @throws IOException
     */
    @Override
    public void excelExport(HttpServletResponse response) throws IOException {
        List<Product> list = productMapper.products();
        //文件名称
        String fileName = "商品名单";
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xls");
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS,true);
        Sheet sheet = new Sheet(1,0,Product.class);
        //设置自适应宽度
        sheet.setAutoWidth(Boolean.TRUE);
        sheet.setSheetName("商品名单");
        writer.write(list,sheet);
        writer.finish();
        out.flush();
        response.getOutputStream().close();
        out.close();
    }

 

controller类中的方法   直接访问路径即可 

 /**
     * 导出
     * @param response
     * @throws IOException
     */
    @GetMapping("/excel")
    public void excelExport(HttpServletResponse response) throws IOException {
        productService.excelExport(response);
    }

 

---

---

导入 

业务逻辑serviceImpl

/**
     * 导入
     * @param file
     * @throws IOException
     */
    @Override
    public void excelImport(MultipartFile file) throws IOException {
        if(!file.getOriginalFilename().equals("商品名单.xls") && !file.getOriginalFilename().equals("商品名单.xlsx") ){
            return;
        }
        InputStream inputStream = new BufferedInputStream(file.getInputStream());
        //实例化实现了AnalysisEventListener接口的类
        ExcelListener excelListener = new ExcelListener(productMapper);
        ExcelReader reader = new ExcelReader(inputStream,null,excelListener);
        //读取信息
        //第一个1代表sheet1, 第二个1代表从第几行开始读取数据，行号最小值为0
        reader.read(new Sheet(1,1,Product.class));
    }

监听类 对mapper进行监听 

public class ExcelListener extends AnalysisEventListener<Product> {
    private List<Product> datas = new ArrayList<>();
    private static final int BATCH_COUNT = 3000;
    private ProductMapper productMapper;

    public ExcelListener(ProductMapper productMapper){
        this.productMapper = productMapper;
    }

    @Override
    public void invoke(Product product, AnalysisContext analysisContext) {
        //数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        datas.add(product);
        //达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(datas.size() >= BATCH_COUNT){
            saveData();
            // 存储完成清理datas
            datas.clear();
        }
    }

    private void saveData() {
        System.out.println("save"+datas);
        for(Product product : datas){
            //dao层添加的数据
            productMapper.add(product);
        }
    }

    public List<Product> getDatas() {
        return datas;
    }

    public void setDatas(List<Product> datas) {
        this.datas = datas;
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();//确保所有数据都能入库
    }
}

 controller控制类

/**
     * 导入
     * @param file
     * @return
     * @throws IOException
     */
    @PostMapping("/import")
    public String excelImport(@RequestParam("file")MultipartFile file) throws IOException {
        productService.excelImport(file);
        return "success";
    }