<!-- EasyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.demo.mapper.MedicnesMapper">
    <insert id="saveMedicnesList" parameterType="cn.demo.entity.Medicnes">
        insert into medices
        (food,remark)
        values
        <foreach collection="list" item="rm" separator=",">
            (#{rm.food},#{rm.remark})
        </foreach>
    </insert>
    <select id="medicnesList" parameterType="cn.demo.entity.Medicnes" resultType="cn.demo.entity.Medicnes">
        SELECT
        food,
        remark
        FROM
        medices
    </select>
</mapper>

package cn.demo.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 @Data
@AllArgsConstructor
@NoArgsConstructor
public class Medicnes {
    @ExcelProperty("食物名稱")
    private String  food;
 
    @ExcelProperty("食物產(chǎn)地")
    private String  remark;
}

package cn.demo.config;
 
import cn.demo.entity.Medicnes;
import cn.demo.service.EmpService;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.springframework.beans.factory.annotation.Autowired;
 
import java.util.ArrayList;
import java.util.List;
/**
 * 有個(gè)很重要的點(diǎn) ExcelListener
 * 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進(jìn)去
 */
public class ExcelListener extends AnalysisEventListener<Medicnes> {
 
    private List<Medicnes> list = new ArrayList<>();
 
    //每隔5條存儲(chǔ)數(shù)據(jù)庫，實(shí)際使用中可以3000條，然后清理list ，方便內(nèi)存回收
    private static final int BATCH_COUNT = 5;
 
    //假設(shè)這個(gè)是一個(gè)DAO，當(dāng)然有業(yè)務(wù)邏輯這個(gè)也可以是一個(gè)service。當(dāng)然如果不用存儲(chǔ)這個(gè)對(duì)象用
    @Autowired
    private EmpService empService;
     /**
     * 如果使用了spring,請(qǐng)使用這個(gè)構(gòu)造方法。
     * 每次創(chuàng)建Listener的時(shí)候需要把spring管理的類傳進(jìn)來
     */
    public ExcelListener(EmpService empService) {
        this.empService = empService;
    }
    /**
     * 這個(gè)每一條數(shù)據(jù)解析都會(huì)來調(diào)用
     */
    @Override
    public void invoke(Medicnes goods, AnalysisContext analysisContext) {
        System.out.println("解析到一條數(shù)據(jù):========================"+goods.toString());
        // 數(shù)據(jù)存儲(chǔ)到data，供批量處理或后續(xù)自己業(yè)務(wù)邏輯處理。
        list.add(goods);
        // 達(dá)到BATCH_COUNT了，需要去存儲(chǔ)一次數(shù)據(jù)庫，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存，容易OOM
        if(list.size() >= BATCH_COUNT){
            saveData();
            // 存儲(chǔ)完成清理data
            list.clear();
        }
 
    }
    /**
     * 所有數(shù)據(jù)解析完成了 都會(huì)來調(diào)用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //確保所有數(shù)據(jù)都能入庫
        saveData();
    }
    /**
     * 加上存儲(chǔ)數(shù)據(jù)庫
     */
    private void saveData() {
        System.out.println("=============================="+list.size()+"條數(shù)據(jù)，開始存儲(chǔ)到數(shù)據(jù)庫");
        empService.saveMedicnesList(list);
    }
}

public class DownExcel {
    public static void download(HttpServletResponse response, Class t, List list) throws IOException, IllegalAccessException,InstantiationException {
        response.setContentType("application/vnd.ms-excel");// 設(shè)置文本內(nèi)省
        response.setCharacterEncoding("utf-8");// 設(shè)置字符編碼
        response.setHeader("Content-disposition", "attachment;filename=demo.xlsx"); // 設(shè)置響應(yīng)頭
        EasyExcel.write(response.getOutputStream(), t).sheet("模板").doWrite(list); //用io流來寫入數(shù)據(jù)
    }
}
 
//導(dǎo)出為Excel
@RequestMapping("/downloadexcel.do")
public void getExcel(HttpServletResponse response) throws IllegalAccessException, IOException, 
InstantiationException {
    List<Medicnes> list = sysUserService.medicnesList();
    DownExcel.download(response,Medicnes.class,list);
}

//導(dǎo)入Excel
@RequestMapping("/importexcel.do")
@ResponseBody
public String importexcel(@RequestParam(value = "excelFile") MultipartFile file) throws IOException{
    EasyExcel.read(file.getInputStream(), Medicnes.class, new ExcelListener(sysUserService)).sheet().doRead();
    return "success";
}