easyexcel导入

VUE部分:

<el-button
  type="primary"
  icon="el-icon-upload"
  size="mini"
  @click="exportExcel"
>导入数据</el-button>


<el-dialog :title="upload.title" :visible.sync="upload.open" width="400px" :close-on-click-    modal="false" append-to-body="true">
      <el-upload
        ref="upload"
        :limit="1"
        accept=".xlsx, .xls"
        :headers="upload.headers"
        :action="upload.url + '?updateSupport=' + upload.updateSupport"
        :disabled="upload.isUploading"
        :on-progress="handleFileUploadProgress"
        :on-success="handleFileSuccess"
        :auto-upload="false"
        drag
      >
        <i class="el-icon-upload"></i>
        <div class="el-upload__text">
          将文件拖到此处，或
          <em>点击上传</em>
        </div>
        <div class="el-upload__tip" style="color: red" slot="tip">
          提示：仅允许导入“xls”或“xlsx”格式文件！
        </div>
      </el-upload>
      <div slot="footer" class="dialog-footer">
        <el-button type="primary" @click="submitFileForm">确 定</el-button>
        <el-button @click="upload.open = false">取 消</el-button>
      </div>
    </el-dialog>

data() {
    return {
        upload: {
        // 是否显示弹出层（用户导入）
        open: false,
        // 弹出层标题（用户导入）
        title: '',
        // 是否禁用上传
        isUploading: false,
        // 是否更新已经存在的用户数据
        updateSupport: 0,
        // 设置上传的请求头部
        headers: { Authorization: getToken() },
        // 上传的地址
        url: window._CONFIG['BASE_URL'] + '/dbgl/dbgl/upload'
      }
    }
}
methods: {
    exportExcel() {
      this.upload.open = true
    },
        // 文件上传中处理
    handleFileUploadProgress(event, file, fileList) {
      this.upload.isUploading = true
    },
    // 文件上传成功处理
    handleFileSuccess(response, file, fileList) {
      this.upload.open = false
      this.upload.isUploading = false
      this.$refs.upload.clearFiles()
      this.$alert(response.msg, '导入结果', { dangerouslyUseHTMLString: true })
      // 刷新列表
      this.getList()
    },
    // 提交上传文件
    submitFileForm() {
      this.$refs.upload.submit()
    },
}

JAVA部分：
controller:

    @RequestMapping("/upload")
    public String upload(@RequestParam(value = "file") MultipartFile uploadFile , HttpServletRequest request){
        try {
            dbGlLcService.insertOutsideDataCompare(uploadFile.getInputStream(), request);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return "ok";
    }

service:

    @Override
    public int insertOutsideDataCompare(InputStream inputStream, HttpServletRequest request) {
        EasyExcel.read(inputStream, DbGlLcTagterExcel.class, new ExcelDataListener<DbGlLcTagterExcel>(dataList -> {
            List<String> collect = dataList.stream().map(e -> e.getTitle()).collect(Collectors.toList());
            System.out.println("--------------->>>>" + collect);
        })).sheet().headRowNumber(1).doRead();
        return 0;
    }

utils:
ExcelDataListener:

package com.bjsasc.asp.dev.dbgl.utils.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;

import java.util.List;

/**
 * excel监听器 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
 *
 */
public class ExcelDataListener<T> extends AnalysisEventListener<T> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100000;

    /**
     * 缓存的数据
     */
    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private final ExcelImporter<T> importer;

    public ExcelDataListener(ExcelImporter<T> importer) {
        this.importer = importer;
    }


    /**
     * 每一行数据解析都需要执行
     * @param data
     * @param analysisContext
     */
    @Override
    public void invoke(T data, AnalysisContext analysisContext) {
        cachedDataList.add(data);

        // 达到BATCH_COUNT，则调用importer方法入库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {

            // 调用importer方法
            importer.save(cachedDataList);

            // 存储完成清理list
            cachedDataList.clear();
        }
    }

    /**
     * 解析完成，都需要执行
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        // 最后的保存，防止数量少于100。
        importer.save(cachedDataList);

        // 存储完成清理list
        cachedDataList.clear();
    }
}

ExcelImporter:

package com.bjsasc.asp.dev.dbgl.utils.easyexcel;

import java.util.List;

/**
 * excel保存的接口
 */
public interface ExcelImporter<T> {

    /**
     * 保存数据接口
     * @param datas
     */
    void save(List<T> datas);

}

实体类中 ：
@ExcelProperty(index = 1) 表示第几列进行映射