集成EasyExcel导入导出

1.导入pom依赖

  <!-- easy excel依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

2.创建监听器

package com.jh.config;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.jd.home.entity.PayBillVo;import java.time.LocalDateTime;
import org.springframework.context.annotation.Scope;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * 功能描述: easy excel 监听器
 *
 * @author Edison_Chan
 * @date 2022/12/21 17:32
 */
@Component
@Scope("prototype")//标记此处的监听器为多例的，防止并发读操作时出现错误
public class ExcelReadListener extends AnalysisEventListener<PayBillVo> {

  @Override
  public void invoke(PayBillVo vipPayBill, AnalysisContext analysisContext) {
    //每读取一行数据都会调用一次，会把每一行数据封装
    //属性不一致时，使用属性拷贝
   VipPayBill payBill = new VipPayBill();

　　BeanUtils.copyProperties(vipPayBill,vo);
　　//插入操作
　　//add

 } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { //所有数据解析完毕执行该方法  } }

3.控制器

package com.jh.home.controller;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.fastjson.JSONObject;
import com.jd.biz.controller.BaseController;
import com.jd.config.ExcelReadListener;
import com.jd.home.entity.PayBillPo;
import com.jd.home.entity.PayBillVo;
import com.jd.home.service.VipPayBillService;
import com.jd.vo.ResultMessage;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

/**
 * <p>
 * 两费收取 - 应缴账单表 前端控制器
 * </p>
 *
 * @author lins
 * @since 2022-12-21
 */
@RestController
@RequestMapping("/pay/vip-pay-bill")
public class VipPayBillController {/**
   *
   * @param file 获得前端上传的文件  EasyExcel.read 需要传入三个参数 文件流 操作实体类的字节码 监听器
   * @return 0 成功上传
   * @throws IOException
   */
  @RequestMapping("/read")
  @ResponseBody
  public ResultMessage readExcel(@RequestParam(value = "file") MultipartFile file) throws IOException {
    // 得到excel读取对象  //通过文件获得流， 获得读取文件的class    填入监听器 监听器每读取一行就执行一次新增
    ExcelReaderBuilder read = EasyExcel.read(file.getInputStream(), PayBillVo.class, listener);
    //获取表格
    ExcelReaderSheetBuilder sheet = read.sheet();
    //读取表格
    sheet.doRead();
    return ResultMessage.success("导入成功");
  }

  @RequestMapping("/write")
  public void writeExcel(String nos, HttpServletResponse response) throws IOException {
    //设置响应头
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    //导出的文件名
    String filename = URLEncoder.encode("DownloadTemplate","utf-8");
    //设置响应头
    response.setHeader("Content-Disposition","attachment;filename="+filename+".xlsx");
    //获得流对象
    ServletOutputStream outputStream = response.getOutputStream();
    //获得write对象
    ExcelWriterBuilder write = EasyExcel.write(outputStream, PayBillVo.class);
    //获得数据表对象
    ExcelWriterSheetBuilder sheet = write.sheet();
    //准备需要输出的数据   调用业务层，获得所有需要导出的数据
    List<PayBillVo> list = payBillService.list();
    //生成表格文件
    sheet.doWrite(list);
  }
}

4.实体

package com.jd.home.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import java.math.BigDecimal;
import lombok.EqualsAndHashCode;

@EqualsAndHashCode
@ContentRowHeight(15)
@HeadRowHeight(20)
public class PayBillVo {

  @ColumnWidth(20)
  @ExcelProperty(value = "账号ID",index = 0)
  private Integer accountId;

  @ColumnWidth(20)
  @ExcelProperty(value = "农户账号",index = 1)
  private String accountName;

  @ColumnWidth(20)
  @ExcelProperty(value = "缴费类型",index = 3)
  private String payType;

  @ColumnWidth(20)
  @ExcelProperty(value = "农户姓名",index = 2)
  private String personName;

  @ColumnWidth(20)
  @ExcelProperty(value = "应缴总额",index = 4)
  private BigDecimal amount;

  public void setAccountId(Integer accountId) {
    this.accountId = accountId;
  }

  public void setAccountName(String accountName) {
    this.accountName = accountName;
  }

  public void setPayType(String payType) {
    this.payType = payType;
  }

  public void setPersonName(String personName) {
    this.personName = personName;
  }

  public void setAmount(BigDecimal amount) {
    this.amount = amount;
  }

  public Integer getAccountId() {
    return accountId;
  }

  public String getAccountName() {
    return accountName;
  }

  public String getPayType() {
    return payType;
  }

  public String getPersonName() {
    return personName;
  }

  public BigDecimal getAmount() {
    return amount;
  }
}

　　此处不用lombok 手动生成get set方法

@EqualsAndHashCode
@ContentRowHeight(15) 内容行高
@HeadRowHeight(20)　　首行高度

@ColumnWidth(20) 列宽

@ExcelProperty(value = "账号ID",index = 0) name index都可匹配excel内容 index按顺序 name则名称匹配