Easyexcel文档

注意：easyexcel必须导入对应版本的poi,否则会报错

<dependency>
		  <groupId>com.alibaba</groupId>
		  <artifactId>easyexcel</artifactId>
		  <version>2.1.6</version>
	  </dependency>
	  <dependency>
		  <groupId>org.apache.poi</groupId>
		  <artifactId>poi</artifactId>
		  <version>3.17</version>
	  </dependency>
	  <dependency>
		  <groupId>org.apache.poi</groupId>
		  <artifactId>poi-scratchpad</artifactId>
		  <version>3.17</version>
	  </dependency>
	  <dependency>
		  <groupId>org.apache.poi</groupId>
		  <artifactId>poi-ooxml</artifactId>
		  <version>3.17</version>
	  </dependency>

创建实体

public class dict implements BaseEntity {

    @ExcelProperty(value = "序号",index = 0)
    private String serialNo;

    @ExcelProperty(value = "编码",index = 1)
    private String code;

	//标识忽略
    @ExcelIgnore
    private String desc;

}

创建监听对象

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.dao.Dao;
import com.entity.Dict;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * @author yhh
 * @Description:
 * @date 2021/6/1 10:19
 */
@Component
public class DictListener extends AnalysisEventListener<Dict> {

    @Autowired
    private Dao dao ;

    private static final Logger LOGGER =
            LoggerFactory.getLogger(com.listener.DictListener.class);

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<Dict> list = new ArrayList<Dict>();

    /**
     * 每次创建Listener的时候需要把spring管理的类传进来
     * @param dao
     */
    public DictListener(Dao dao){
        this.dao =dao ;
    }

	//一条数据就会被调用一次
    @Override
    public void invoke(Dict dict, AnalysisContext analysisContext) {
        System.out.println("invoke方法被调用");
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(dict));
        list.add(dict);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("doAfterAllAnalysed方法 被调用");
        // 这里也要保存数据,分批次后最后几条不足5条避免丢失，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    public void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        dao.batchSave(list);
        LOGGER.info("存储数据库成功！");
    }
}

controller

/**
 * @author yhh
 * @Description:导入excel文档数据
 * @date 2021/6/1 9:32
 */
@RestController
@RequestMapping("excel")
public class ExcelController  {

    @Autowired
    private dao dao ;

    /**
     * 读取 excel
     * @return
     */
    @PostMapping("upload")
    @Transactional
    public String upload(MultipartFile file) throws IOException {
//         sheet里面可以传参 根据sheet下标读取或者根据名字读取 不传默认读取第一个
        EasyExcel.read(file.getInputStream(), Dict.class,
                new DictListener(dao)).sheet().doRead();
        return "success";
    }

    @GetMapping("download")
    @Transactional
    public void download(HttpServletResponse response) throws Exception {
        // 这里注意 可能使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("模板", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), Dict.class).sheet("模板").doWrite(dao.getDate());
    }

}

postman测试

成功导入保存到数据库