EasyExcel處理Mysql百萬資料的匯入匯出案例，秒級效率，拿來即用！

JavaBuild發表於2024-05-11
ExcelMySql

一、寫在開頭

今天終於更新新專欄 《EfficientFarm》 的第二篇博文啦，本文主要來記錄一下對於EasyExcel的高效應用，包括對MySQL資料庫百萬級資料量的匯入與匯出操作，以及效能的最佳化（爭取做到秒級效能！）。

二、如何做技術選型

其實在市面上我們有很多常用的excel操作依賴庫，除了EasyExcel之外，還有EasyPOI、JXL、JXLS等等，他們各有千秋，依賴重點不同，我們在做技術選型的時候，要根據自己的需求去做針對性選擇，下面我們列舉了這幾種常見技術的特點對比

技術方案 優點 缺點
EasyExcel 簡單易用，API設計友好；
高效處理大量資料；
支援自定義樣式和格式化器等功能		 不支援老版本 Excel 檔案 (如 xls 格式)
POI Apache開源專案，穩定性高，EasyPOI基於它開發的，特點類似，進行了功能增強，這裡不單獨列舉；
支援多種格式（XLS、XLSX等）；
可以讀寫複雜表格（如帶有合併單元格或圖表的表格）		 API使用較為繁瑣；對於大資料量可能會存在效能問題
Jxls 具備良好的模板引擎機制，支援透過模板檔案生成 Excel 表格；
提供了視覺化設計器來快速建立報告模板		 效能相對其他兩個方案稍弱一些；
模板與程式碼耦合度較高。

而本文中主要針對的是大資料量的匯入與匯出，因此，我們果斷的選擇了EasyExcel技術進行實現。

三、應用場景模擬

假設我們在開發中接到了一個需求要求我們做一個功能：

1、匯出商城中所有的使用者資訊，由於使用者規模達到了百萬級，匯出等待時間不可太長
2、允許透過規定的excel模板進行百萬級使用者資訊的初始化（系統遷移時會發生）。

拿到這個需求後，經過技術選型EasyExcel後，我們在心裡有個大概的構想了，大概可以分三個內容 ：“模板下載”、“上傳資料”、“下載資料”。

想好這些後，我們就可以開整了！✊✊✊

四、資料準備

在資料準備階段，我們應該做如下幾點：

1. 在資料庫中建立一個使用者資訊表User；

-- 如果存在表先刪除
drop table if exists `user`;
--建表語句
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '員工姓名',
  `phone_num` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '聯絡方式',
  `address` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '住址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. 準備一個使用者資訊匯入的初始化模板；

image

3. 模擬創造百萬資料量在User表中；

這一點其實有2種方案，第一種就是在創造好的模板檔案xlsx中，手工造出100萬的資料，xlsx單個sheet頁最大可建立104萬行資料，剛剛好滿足，如果用xls單個sheet還不可以，這種肯定相對麻煩，並且100萬的資料有幾十M，開啟就已經很慢了；

另外一種方案，可以透過儲存過程向MySQL中加入100w條資料，不過效能也不好，畢竟資料量太大，自己斟酌吧，sql貼出來（效能不好的電腦，不建議這麼幹，容易把軟體跑崩）：

DELIMITER //
drop procedure IF EXISTS InsertTestData;
CREATE PROCEDURE InsertTestData()
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter < 1000000 DO
            INSERT INTO user (id, name, phone_num, address) VALUES
        (counter, CONCAT('name_', counter), CONCAT('phone_', counter), CONCAT('add_',counter)) ; 
            SET counter = counter + 1;
        END WHILE;
END //
DELIMITER;

-- 呼叫儲存過程插入資料
CALL InsertTestData();

五、SpringBoot中配置EasyExcel

5.1 pom.xml中引入依賴

本次程式碼中一共用到了如下這些依賴，很多小夥伴本地若已經引入了，可以忽略！

<!--lombok依賴-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<!--MyBatis Plus依賴-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>
<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.4</version>
</dependency>
<!-- hutool -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.25</version>
</dependency>

5.2 建立實體類

@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(25)
public class User {
    /**
     * 主鍵
     *
     * @mbg.generated
     */
    @ExcelProperty("id")
    private Integer id;

    /**
     * 員工姓名
     *
     * @mbg.generated
     */
    @ExcelProperty("姓名")
    private String name;

    /**
     * 聯絡方式
     *
     * @mbg.generated
     */
    @ExcelProperty("聯絡方式")
    private String phoneNum;

    /**
     * 住址
     *
     * @mbg.generated
     */
    @ExcelProperty("聯絡地址")
    private String address;
    
}

【註解說明】

  • @ExcelProperty：宣告列名。
  • @ColumnWidth：設定列寬。也可以直接作用在類上。統一每一列的寬度

5.3 建立資料關係對映

UserMapper 檔案

//*注：這裡面繼承了mybatis-plus的BaseMapper介面，供後面進行分頁查詢使用。*
public interface UserMapper extends BaseMapper<User> {
    int deleteByPrimaryKey(Integer id);

    int insertAll(User record);

    void insertSelective(@Param("list") List<User> list);

    User selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);
    Integer countNum();
}

UserMapper .xml檔案

<?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="org.javaboy.vhr.mapper.UserMapper">
  <resultMap id="BaseResultMap" type="org.javaboy.vhr.pojo.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="phone_num" jdbcType="VARCHAR" property="phoneNum" />
    <result column="address" jdbcType="VARCHAR" property="address" />
  </resultMap>
  <sql id="Base_Column_List">
    id, name, phone_num, address
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=INTEGER}
  </select>
  <select id="countNum" resultType="java.lang.Integer">
    select count(*) from user
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from user
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insertAll" keyColumn="id" keyProperty="id" parameterType="org.javaboy.vhr.pojo.User" useGeneratedKeys="true">
    insert into user (name, phone_num, address
      )
    values (#{name,jdbcType=VARCHAR}, #{phoneNum,jdbcType=VARCHAR}, #{address,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="org.javaboy.vhr.pojo.User">
    insert into user
    (id,name, phone_num, address
      )
    values
    <foreach collection="list" item="item" separator=",">
        (#{item.id},#{item.name},#{item.phoneNum},#{item.address})
    </foreach>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="org.javaboy.vhr.pojo.User">
    update user
    <set>
      <if test="name != null">
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="phoneNum != null">
        phone_num = #{phoneNum,jdbcType=VARCHAR},
      </if>
      <if test="address != null">
        address = #{address,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="org.javaboy.vhr.pojo.User">
    update user
    set name = #{name,jdbcType=VARCHAR},
      phone_num = #{phoneNum,jdbcType=VARCHAR},
      address = #{address,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

六、前端設計

前端頁面採用Vue框架實現，咱們就按照上文中構想的那三點來設計就行，可以簡單點實現，如果想要更加炫酷的前端樣式，比如匯入的檔案格式校驗，資料量提示等等，可以自行網上學習哈。

<template>
    <el-card>
        <div>
            <!--匯入資料-->
            <el-upload
                    :show-file-list="false"
                    :before-upload="beforeUpload"
                    :on-success="onSuccess"
                    :on-error="onError"
                    :disabled="importDataDisabled"
                    style="display: inline-flex;margin-right: 8px"
                    action="/employee/excel/import">
                <!--匯入資料-->
                <el-button :disabled="importDataDisabled" type="success" :icon="importDataBtnIcon">
                    {{importDataBtnText}}
                </el-button>
            </el-upload>
            <el-button type="success" @click="exportEasyExcel" icon="el-icon-download">
                匯出資料
            </el-button>
            <el-button type="success" @click="exportExcelTemplate" icon="el-icon-download">
                匯出模板
            </el-button>
        </div>
    </el-card>

</template>

<script>
    import {Message} from 'element-ui';
    export default {
        name: "Export",
        data() {
            return {
                importDataBtnText: '匯入資料',
                importDataBtnIcon: 'el-icon-upload2',
                importDataDisabled: false,
            }
        },
        methods: {
            onError(res) {
                this.importDataBtnText = '匯入資料';
                this.importDataBtnIcon = 'el-icon-upload2';
                this.importDataDisabled = false;
                console.log(res);
            },
            onSuccess(res) {
                this.importDataBtnText = '匯入資料';
                this.importDataBtnIcon = 'el-icon-upload2';
                this.importDataDisabled = false;
                console.log(res.msg);
                if (res.msg == '檔案匯入成功'){
                    Message.success("檔案匯入完成")
                }
               // this.initEmps();

            },
            beforeUpload() {
                this.importDataBtnText = '正在匯入';
                this.importDataBtnIcon = 'el-icon-loading';
                this.importDataDisabled = true;
            },
            exportEasyExcel() {
                window.open('/employee/excel/easyexcelexport', '_parent');
            },
            exportExcelTemplate(){
                window.open('/employee/excel/exporttemplate', '_parent');
            }
        }
    }

</script>

<style scoped>

</style>

效果如下：
image

七、匯入匯出實現

7.1 模板下載

1️⃣ 將準備好的使用者資訊模板.xlsx檔案放入resource對應路徑下。

image

2️⃣ 構建一個控制器類，用以接收匯出模板、匯入資料、匯出資料的請求。

@RestController
@RequestMapping("/employee/excel")
@AllArgsConstructor
@Slf4j
public class EasyExcellController {
/**
     * 下載使用者資訊模板
     * @param response
     */
    @RequestMapping("/exporttemplate")
    public void downloadTemplate(HttpServletResponse response){
        try {
            //設定檔名
            InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("template/使用者資訊模板.xlsx");
            //設定標頭檔案,注意檔名若為中文，使用encode進行處理
            response.setHeader("Content-disposition", "attachment;fileName=" +  java.net.URLEncoder.encode("使用者資訊模板.xlsx", "UTF-8"));
            //設定檔案傳輸型別與編碼
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            OutputStream outputStream = response.getOutputStream();
            byte[] bytes = new byte[2048];
            int len;
            while((len = inputStream.read(bytes)) != -1){
                outputStream.write(bytes,0,len);
            }
            outputStream.flush();
            outputStream.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

這部分程式碼中需要注意的是，如果你的模板是中文名字，需要加上java.net.URLEncoder.encode("使用者資訊模板.xlsx", "UTF-8")解決亂碼問題。

7.2 匯入資料

1️⃣ 在EasyExcellController類中增加匯入資料的請求處理方法；

	@Autowired
	EasyExcelServiceImpl easyExcel;
	
 /**
 * 匯入百萬excel檔案
  * @param file
  * @return
  */
 @RequestMapping("/import")
 public RespBean easyExcelImport(MultipartFile file){
     if(file.isEmpty()){
         return RespBean.error("檔案不可為空");
     }
     easyExcel.easyExcelImport(file);
     return RespBean.ok("檔案匯入成功");
 }

程式碼中的RespBean是自己定義的一個響應工具類。

public class RespBean {
    private Integer status;
    private String msg;
    private Object obj;

    public static RespBean build() {
        return new RespBean();
    }

    public static RespBean ok(String msg) {
        return new RespBean(200, msg, null);
    }

    public static RespBean ok(String msg, Object obj) {
        return new RespBean(200, msg, obj);
    }

    public static RespBean error(String msg) {
        return new RespBean(500, msg, null);
    }

    public static RespBean error(String msg, Object obj) {
        return new RespBean(500, msg, obj);
    }

    private RespBean() {
    }

    private RespBean(Integer status, String msg, Object obj) {
        this.status = status;
        this.msg = msg;
        this.obj = obj;
    }

    public Integer getStatus() {
        return status;
    }

    public RespBean setStatus(Integer status) {
        this.status = status;
        return this;
    }

    public String getMsg() {
        return msg;
    }

    public RespBean setMsg(String msg) {
        this.msg = msg;
        return this;
    }

    public Object getObj() {
        return obj;
    }

    public RespBean setObj(Object obj) {
        this.obj = obj;
        return this;
    }
}

2️⃣ 在控制器中引入的easyExcel.easyExcelImport(file)方法中進行匯入邏輯的實現。

@Service
@Slf4j
@AllArgsConstructor
public class EasyExcelServiceImpl implements EasyExcelService {

    private final ApplicationContext applicationContext;
    /**
     * excle檔案匯入實現
     * @param file
     */
    @Override
    public void easyExcelImport(MultipartFile file) {
        try {
            long beginTime = System.currentTimeMillis();
            //載入檔案讀取監聽器
            EasyExcelImportHandler listener = applicationContext.getBean(EasyExcelImportHandler.class);
            //easyexcel的read方法進行資料讀取
            EasyExcel.read(file.getInputStream(), User.class,listener).sheet().doRead();
            log.info("讀取檔案耗時：{}秒",(System.currentTimeMillis() - beginTime)/1000);
        } catch (IOException e) {
            log.error("匯入異常", e.getMessage(), e);
        }
    }
}

這部分程式碼的核心是檔案讀取監聽器：EasyExcelImportHandler。

3️⃣ 構建檔案讀取監聽器

@Slf4j
@Service
public class EasyExcelImportHandler implements ReadListener<User> {
    /*成功資料*/
    private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
    /*單次處理條數*/
    private final static int BATCH_COUNT = 20000;
    @Resource
    private ThreadPoolExecutor threadPoolExecutor;
    @Resource
    private UserMapper userMapper;



    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        if(StringUtils.isNotBlank(user.getName())){
            successList.add(user);
            return;
        }
        if(successList.size() >= BATCH_COUNT){
            log.info("讀取資料：{}", successList.size());
            saveData();
        }

    }

    /**
     * 採用多執行緒讀取資料
     */
    private void saveData() {
        List<List<User>> lists = ListUtil.split(successList, 20000);
        CountDownLatch countDownLatch = new CountDownLatch(lists.size());
        for (List<User> list : lists) {
            threadPoolExecutor.execute(()->{
                try {
                    userMapper.insertSelective(list.stream().map(o -> {
                        User user = new User();
                        user.setName(o.getName());
                        user.setId(o.getId());
                        user.setPhoneNum(o.getPhoneNum());
                        user.setAddress(o.getAddress());
                        return user;
                    }).collect(Collectors.toList()));
                } catch (Exception e) {
                    log.error("啟動執行緒失敗,e:{}", e.getMessage(), e);
                } finally {
                    //執行完一個執行緒減1,直到執行完
                    countDownLatch.countDown();
                }
            });
        }
        // 等待所有執行緒執行完
        try {
            countDownLatch.await();
        } catch (Exception e) {
            log.error("等待所有執行緒執行完異常,e:{}", e.getMessage(), e);
        }
        // 提前將不再使用的集合清空，釋放資源
        successList.clear();
        lists.clear();
    }

    /**
     * 所有資料讀取完成之後呼叫
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //讀取剩餘資料
        if(CollectionUtils.isNotEmpty(successList)){
            log.info("讀取資料：{}條",successList.size());
            saveData();
        }
    }
}

在這部分程式碼中我們需要注意兩個問題，第一個是多執行緒，第二個是EasyExcel提供的ReadListener監聽器。

第一個，由於我們在程式碼裡採用了多執行緒匯入，因此我們需要配置一個合理的執行緒池，以提高匯入效率。

@Configuration
public class EasyExcelThreadPoolExecutor {

    @Bean(name = "threadPoolExecutor")
    public ThreadPoolExecutor easyExcelStudentImportThreadPool() {
        // 系統可用處理器的虛擬機器數量
        int processors = Runtime.getRuntime().availableProcessors();
        return new ThreadPoolExecutor(processors + 1,
                processors * 2 + 1,
                10 * 60,
                TimeUnit.SECONDS,
                new LinkedBlockingQueue<>(1000000));
    }
}

第二個，對於ReadListener，我們需要搞清楚它提供的方法的作用。

  • invoke()：讀取表格內容，每一條資料解析都會來呼叫；
  • doAfterAllAnalysed()：所有資料解析完成了呼叫；
  • invokeHead() ：讀取標題，裡面實現在讀完標題後會回撥，本篇文章中未使用到；
  • onException()：轉換異常 獲取其他異常下會呼叫本介面。丟擲異常則停止讀取。如果這裡不丟擲異常則 繼續讀取下一行，本篇文章中未使用到。

4️⃣ 匯入100萬資料量耗時測試

在做匯入測試前，由於100萬資料量的excel檔案很大，所以我們要在application.yml檔案中進行最大可上傳檔案的配置：

spring:
 servlet:
    multipart:
      max-file-size: 128MB        # 設定單個檔案最大大小為10MB
      max-request-size: 128MB    # 設定多個檔案大小為100MB

對100萬資料進行多次匯入測試，所損耗時間大概在500秒左右，8分多鐘，這對於我們來說肯定無法接受，所以我們在後面針對這種匯入進行徹底最佳化！

image

7.3 匯出資料

1️⃣ 在EasyExcellController類中增加匯出資料的請求處理方法；

/**
     * 匯出百萬excel檔案
     * @param response
     */
    @RequestMapping("/easyexcelexport")
    public void easyExcelExport(HttpServletResponse response){
        try {
            //設定內容型別
            response.setContentType("text/csv");
            //設定響應編碼
            response.setCharacterEncoding("utf-8");
            //設定檔名的編碼格式,防止檔名亂碼
            String fileName = URLEncoder.encode("使用者資訊", "UTF-8");
            //固定寫法,設定響應頭
            response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
            Integer total = userMapper.countNum();
            if (total == 0) {
                log.info("查詢無資料");
                return;
            }
            //指定用哪個class進行寫出
            ExcelWriter build = EasyExcel.write(response.getOutputStream(), User.class).build();
            //設定一個sheet頁儲存所有匯出資料
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
            long pageSize = 10000;
            long pages = total / pageSize;
            long startTime = System.currentTimeMillis();
            //資料量只有一頁時直接寫出
            if(pages < 1){
                List<User> users = userMapper.selectList(null);
                build.write(users, writeSheet);
            }
            //大資料量時，進行分頁查詢寫入
            for (int i = 0; i <= pages; i++) {
                Page<User> page = new Page<>();
                page.setCurrent(i + 1);
                page.setSize(pageSize);
                Page<User> userPage = userMapper.selectPage(page, null);
                build.write(userPage.getRecords(), writeSheet);
            }
            build.finish();
            log.info("匯出耗時/ms:"+(System.currentTimeMillis()-startTime)+",匯出資料總條數："+total);
        } catch (Exception e) {
            log.error("easyExcel匯出失敗，e:{}",e.getMessage(),e);
        }
    }

由於資料量比較大，我們在這裡採用分頁查詢，寫入到一個sheet中，如果匯出到xls格式的檔案中，需要寫入到多個sheet中，這種可能會慢一點。

且在Mybatis-Plus中使用分頁的話，需要增加一個分頁外掛的配置

@Configuration
public class MybatisPlusPageConfig {
    /**
     * 新版分頁外掛配置
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

2️⃣ 百萬資料量匯出測試

經過多次測試發現，100萬資料量平均匯出耗時在40秒左右，在可以接受的範圍內！

image

八、總結

以上就是SpringBoot專案下，透過阿里開源的EasyExcel技術進行百萬級資料的匯入與匯出，不過針對百萬資料量的匯入，時間在分鐘級別，這很明顯不夠優秀，但考慮到本文的篇幅已經很長了，我們在下一篇文章針對匯入進行效能最佳化，敬請期待！

九、結尾彩蛋

如果本篇部落格對您有一定的幫助，大家記得留言+點贊+收藏呀。原創不易，轉載請聯絡Build哥！

image

如果您想與Build哥的關係更近一步，還可以關注“JavaBuild888”，在這裡除了看到《Java成長計劃》系列博文，還有提升工作效率的小筆記、讀書心得、大廠面經、人生感悟等等，歡迎您的加入！

image

相關文章