<!--        Easy POI-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.2.0</version>
</dependency>

<template>
	<div class="el-form-item__label">
            <el-upload
              class="upload-demo"
              :action="importUrl"
              :on-success="uploadSuccess"
              accept=".xlsx"
              :show-file-list="false"
              multiple
              :limit="3"
              :on-exceed="handleExceed"
            >
              <el-button type="success" plain icon="el-icon-download" size="small">導入</el-button>
            </el-upload>
        </div>
</template>

data() {
    return {
      importUrl: this.settings.apiUrl+'/sys/user/import_excel',
    };
  },

<template>
	<div class="el-form-item__label">
            <el-button type="warning" size="small" plain icon="el-icon-upload2" @click="exportExcel">導出</el-button>
        </div>
</template>

methods: {
    exportExcel() {
		window.open(this.settings.apiUrl + "/sys/user/export_excel")
	}
}

package com.chen.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.util.Date;

/**
 * Excel 類，主要用于映射excel表
 * @author whc
 */
@Data
public class SysUserExcel {

    @Excel(name = "序號", orderNum = "0", format = "isAddIndex")
    private Integer index = 1;

    @Excel(name = "用戶賬號")
    private String username;

    @Excel(name = "真實姓名")
    private String realName;

    @Excel(name = "性別", replace = {"男_1", "女_2"})
    private Integer gender;

    @Excel(name = "電子郵箱", width = 25)
    private String email;

    @Excel(name = "手機號碼")
    private String mobile;

    @Excel(name = "創(chuàng)建時間", format = "yyyy-MM-dd", width = 15)
    private Date createTime;
}

@PostMapping("import_excel")
    public ResultBean<Integer> import_excel(MultipartFile file) {
        ResultBean<Integer> res = sysUserService.importExcel(file);
        return res;
    }

@Override
public ResultBean<Integer> importExcel(MultipartFile file) {
    ImportParams importParams = new ImportParams();
    //標題行設置為1行，默認是0，可以不設置；依實際情況設置。
    importParams.setTitleRows(1);
    // 表頭設置為1行
    importParams.setHeadRows(1);
    try {
        //讀取excel
        List<SysUserExcel> sysUserExcelList = ExcelImportUtil.importExcel(file.getInputStream(), SysUserExcel.class, importParams);

        batchInsert(sysUserExcelList);
        return ResultBean.create(0, "success");
    } catch (Exception e) {
        log.error("導入 Excel 異常！ e ==> {}", e);
        return ResultBean.create(10, "導入excel 異常！"+e.getMessage());
    }
}


public void batchInsert(List<SysUserExcel> param) throws Exception{
    //轉換為dto集合
    List<SysUserDTO> sysUserDTOList = BeanUtil.copyToList(param, SysUserDTO.class);

    //轉換集合
    List<SysUserDTO> userDTOList = new ArrayList<>();
    for (SysUserDTO sysUserDTO : sysUserDTOList) {
        if (!StringUtils.isEmpty(sysUserDTO.getUsername())) {
            sysUserDTO.setPassword("123");
            userDTOList.add(sysUserDTO);
        }
    }
    //批量插入數(shù)據(jù)庫
    sysUserMapper.batchInsert(userDTOList);

}

Integer batchInsert(@Param("list") List<SysUserDTO> param);

<insert id="batchInsert">
    insert sys_user (username, password, real_name, gender, email, mobile, create_time)
    values
    <foreach collection="list" index="index" item="item" separator=",">
        (#{item.username}, #{item.password}, #{item.realName}, #{item.gender}, #{item.email}, #{item.mobile}, now())
    </foreach>
</insert>

@GetMapping("export_excel")
public void export_excel(Map<String, Object> param, HttpServletResponse response) {
    sysUserService.exportExcel(param, response);
}

@Override
public void exportExcel(Map<String, Object> param, HttpServletResponse response) {
    try {
        List<SysUserDTO> res = sysUserMapper.list(null);
        List<SysUserExcel> sysUserExcelList = BeanUtil.copyToList(res, SysUserExcel.class);
        //設置信息頭，告訴瀏覽器內容為excel類型
        response.setHeader("content-Type", "application/vnd.ms-excel");
        //設置下載名稱
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用戶列表.xls", StandardCharsets.UTF_8.name()));

        //字節(jié)流輸出
        ServletOutputStream out = response.getOutputStream();
        //設置excel參數(shù)
        ExportParams params = new ExportParams();
        //設置sheet名名稱
        params.setSheetName("用戶列表");
        //設置標題
        params.setTitle("用戶信息表");

        Workbook workbook = ExcelExportUtil.exportExcel(params, SysUserExcel.class, sysUserExcelList);
        workbook.write(out);
    } catch (Exception e) {
        log.error("導出失敗！ e ==> {}", e);
    }
}