<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>
<!-- lombok 優(yōu)雅編程 -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.20</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>

/**
 * 需求：單實(shí)體導(dǎo)入(從磁盤將文件導(dǎo)入到應(yīng)用程序)
 * 導(dǎo)入Excel學(xué)員信息到系統(tǒng)。
 * 包含如下列：姓名、性別、出生日期
 * 模板詳見：學(xué)員信息.xlsx
 */

package com.taotie.test;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc
 */
// 基于lombok
@Data
@NoArgsConstructor
@AllArgsConstructor
@HeadRowHeight(20) // 指定列頭行高
@ColumnWidth(20) // 指定列寬
public class Student {
    /**
     * 學(xué)生姓名
     */
    @ExcelProperty(value = "學(xué)生姓名", index = 0)
    private String name;
    /**
     * 學(xué)生性別
     */
    @ExcelProperty(value = "學(xué)生性別", index = 2)
    private String gender;

    /**
     * 學(xué)生出生日期
     */
    @ExcelProperty(value = "學(xué)生出生日期", index = 1)
    private Date birthday;
    /**
     * id
     */
    // @ExcelProperty(value = "編號",index = 3)
    @ExcelIgnore // 忽略,不讀取
    private String id;
}

package com.taotie.test;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc
 */
@SpringBootTest 
public class TestEasyExcel {

    /**
     * 測試讀取數(shù)據(jù)
     */
    @Test
    public void testRead() {
        // 讀取文件，讀取完之后會(huì)自動(dòng)關(guān)閉
        /**
         * 參數(shù)1:pathName  文件路徑；"d:\\學(xué)員信息.xls"
         * 參數(shù)2:head		 每行數(shù)據(jù)對應(yīng)的實(shí)體；Student.class
         * 參數(shù)3:readListener	讀監(jiān)聽器，每讀一樣就會(huì)調(diào)用一次該監(jiān)聽器的invoke方法
         * 參數(shù)4:sheet方法參數(shù)： 工作表的順序號（從0開始）或者工作表的名字，不傳默認(rèn)為0
         */
        // // 封裝工作簿對象
        // ExcelReaderBuilder workBook = EasyExcel.read
        //         ("E:\\學(xué)員信息.xlsx",
        //                 Student.class,
        //                 new StudentReadListener( ));
        // // 封裝工作表
        // ExcelReaderSheetBuilder sheet1 = workBook.sheet( );
        // // 讀取
        // sheet1.doRead( );
        
        // 最簡單的寫法
        EasyExcel.read("E:\\學(xué)員信息.xlsx",Student.class,new StudentReadListener()).sheet().doRead();
    }
}

package com.taotie.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.taotie.domain.Student;

/**
 * @Author Vsunks.v
 * @Description:
 */
public class StudentReadListener extends AnalysisEventListener<Student> {
    // 每讀一行，會(huì)調(diào)用該invoke方法一次
    @Override
    public void invoke(Student data, AnalysisContext context) {
        System.out.println("data = " + data);
    }

    // 全部讀完之后，會(huì)調(diào)用該方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // TODO......
    }
}

/**
 * 需求：單實(shí)體導(dǎo)出(從應(yīng)用程序?qū)⑽募?dǎo)入到磁盤)
 * 導(dǎo)出多個(gè)學(xué)生對象到Excel表格
 * 包含如下列：姓名、性別、出生日期
 * 模板詳見：學(xué)員信息.xlsx
 */

// 還是上個(gè)實(shí)體類...

    /**
     * 測試寫出數(shù)據(jù)
     */
    @Test
    public void simpleWrite() {
        // 創(chuàng)造數(shù)據(jù)
        ArrayList<Student> list = new ArrayList<>( );
        list.add(new Student("張三","男",new Date(),"1001"));
        list.add(new Student("李四","女",new Date(),"1002"));
        list.add(new Student("王五","男",new Date(),"1003"));
        list.add(new Student("趙六","女",new Date(),"1004"));
        list.add(new Student("周期","男",new Date(),"1005"));
        list.add(new Student("茅十八","女",new Date(),"1006"));

        // 寫出的文件路徑,當(dāng)前項(xiàng)目下
        String fileName = "student.xlsx";

        // 這里 需要指定寫用哪個(gè)class去寫，然后寫到第一個(gè)sheet，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        // 如果這里想使用03 則 傳入excelType參數(shù)即可
        EasyExcel.write(fileName, Student.class).sheet("學(xué)生信息").doWrite(list);
        System.out.println("導(dǎo)出OK" );
    }

<!-- EasyExcel -->

<!-- lombok -->

<!-- junit -->

  <!-- 上傳excel的對話框 -->
  <el-dialog title="上傳計(jì)量單位Excel" :visible.sync="dialogExcelVisible" width="40%">
    <el-upload
      class="upload-demo"
      drag
      action="http://localhost:8888/md/unit/upload/excel"
      accept=".xlsx,.xls"
      :on-success="uploadExcelSuccess"
      :on-error="uploadExcelError"
      multiple>
      <i class="el-icon-upload"></i>
      <div class="el-upload__text">將文件拖到此處，或<em>點(diǎn)擊上傳</em></div>
      <div class="el-upload__tip" slot="tip">只能上傳.xlsx,.xls文件，且不超過500kb</div>
    </el-upload>
  </el-dialog>

uploadExcelSuccess(){
  this.$message({
    type:"success",
    message:"上傳成功"
  })
  this.dialogExcelVisible = false;
  this.fetchData();
},
uploadExcelError(err){
  this.$message({
    type:"error",
    message:err
  })
}

package com.qf.config;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.qf.entity.SysUser;
import com.qf.entity.UnitMeasure;
import com.qf.service.SysUserService;
import com.qf.service.UnitMeasureService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import java.util.ArrayList;

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc
 */
@Component
@Scope("prototype")    // 作者要求每次讀取都要使用新的Listener
public class MdUnitMeasureReadListener extends AnalysisEventListener<UnitMeasure> {
    // 有個(gè)很重要的點(diǎn) DemoDataListener 不能被spring管理，
    // 要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進(jìn)去
    private UnitMeasureService service;
    public MdUnitMeasureReadListener(){} // 空參構(gòu)造

    // 有參構(gòu)造
    public MdUnitMeasureReadListener(UnitMeasureService service){
        this.service = service;
    }

    // 每隔5條存儲(chǔ)數(shù)據(jù)庫，實(shí)際項(xiàng)目中使用時(shí)可以100條，然后清理list ，方便內(nèi)存回收
    private final int BATCH_COUNT = 5;
    private ArrayList<UnitMeasure> list = new ArrayList<>( );

    // 每讀一行，會(huì)調(diào)用該invoke方法一次
    @Override
    public void invoke(UnitMeasure data, AnalysisContext context) {
        list.add(data);
        // 達(dá)到BATCH_COUNT了，需要去存儲(chǔ)一次數(shù)據(jù)庫，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存，容易OOM
        if (list.size( ) >= BATCH_COUNT) {
            // 調(diào)用方法,執(zhí)行插入
            saveData( );
            // 存儲(chǔ)完成清理 list
            list.clear();
        }
    }

    // 全部讀完之后，會(huì)調(diào)用該方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 這里也要保存數(shù)據(jù)，確保最后遺留的數(shù)據(jù)也存儲(chǔ)到數(shù)據(jù)庫
        if(list.size() > 0) {
            saveData( );
        }
        System.out.println("所有數(shù)據(jù)解析完成！");
    }


    // 向數(shù)據(jù)庫插入數(shù)據(jù)
    private void saveData() {
        System.out.println("開始存儲(chǔ)數(shù)據(jù)庫！");

        // 調(diào)用業(yè)務(wù)層存數(shù)據(jù)庫
        service.saveBatch(list);
        System.out.println("存儲(chǔ)數(shù)據(jù)庫成功！");
    }
}

 /**
     * 上傳Excel文件
     */
    @PostMapping("/upload/excel")
    public R uploadExcel(MultipartFile file) {
        try {
            // 重點(diǎn),此處第三個(gè)參數(shù)需要傳入Service對象
            EasyExcel.read(file.getInputStream( ),
                            UnitMeasure.class,
                            new MdUnitMeasureReadListener(service))
                    .sheet( ).doRead( );
        }catch (Exception e){
            e.printStackTrace();
            return R.fail(e.getMessage());
        }
        return R.ok( );
    }

// 需要添加批量插入的方法

       <!-- 批量插入 -->
    <insert id="saveBatch">
        insert into
            md_unit_measure       (measure_code,measure_name,primary_flag,primary_id,change_rate,enable_flag)
        values
        <foreach collection="list" item="unit" separator=",">
            (#{unit.measureCode},
            #{unit.measureName},
            #{unit.primaryFlag},
            #{unit.primaryId},
            #{unit.changeRate},
            #{unit.enableFlag})
        </foreach>

// 略

/**
 * 下載Excel文件
 * 注意,返回值是void
 */
@GetMapping("/download/excel")
public void downloadExcel(HttpServletResponse response) throws IOException {

    //  查詢數(shù)據(jù)庫全部數(shù)據(jù)
    List<UnitMeasure> list = service.findAll(null);

    // 下面這個(gè)注釋是前端使用方式1,即a標(biāo)簽發(fā)請求時(shí)采用
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 這里URLEncoder.encode可以防止中文亂碼
    String fileName = URLEncoder.encode("計(jì)量單位信息", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

    // easyexcel導(dǎo)出工具
    EasyExcel.write(response.getOutputStream(), UnitMeasure.class).autoCloseStream(Boolean.FALSE).sheet("計(jì)量單位信息")
        .doWrite(list);
}