spring boot + VUE 实现简单的Excel批量导入

spring boot + VUE 实现简单的Excel批量导入

前端样式及代码

1.样式：

2.代码：

<template>
  <div class="app-container">
    <el-upload
      class="upload-demo"
      ref="upload"
      :action="uploadUrl()"
      :data="uploadData"
      name="excelFile"
      :on-preview="handlePreview"
      :on-remove="handleRemove"
      :file-list="fileList"
      :on-error="uploadFalse"
      :on-success="uploadSuccess"
      :auto-upload="false"
      :before-upload="beforeAvatarUpload">
      <el-button slot="trigger" size="small" type="primary">选取文件</el-button>
      <el-button style="margin-left: 10px;" size="small" type="success" @click="submitUpload">批量导入</el-button>
      <div slot="tip" class="el-upload__tip">只能上传excel文件</div>
    </el-upload>
  </div>
</template>
<script>
  export default {
   
    name: 'UploadExcel',
    data() {
   
      return {
   
        tableData: [],
        tableHeader: []
      }
    },
    methods: {
   
      uploadUrl: function () {
   
        return (
          "http://localhost:2107/api/import/ExcelInsertStore" +
          "?businessName=" +
          this.businessName +
          "&businessStatus=" +
          this.businessStatus +
          "&businessType=" +
          this.businessType
        );
      },
      uploadSuccess(response, file, fileList) {
   
        if (response>0) {
   
          alert("文件导入成功"+response+"条记录");
        } else {
   
          alert("文件导入失败");
        }
      },
      uploadFalse(response, file, fileList) {
   
        alert("文件上传失败！");
      },
      // 上传前对文件的大小的判断
      beforeAvatarUpload(file) {
   
        const extension = file.name.split(".")[1] === "xls";
        const extension2 = file.name.split(".")[1] === "xlsx";
        const isLt2M = file.size / 1024 / 1024 < 10;
        if (!extension && !extension2) {
   
          alert("上传模板只能是 xls、xlsx 格式!");
        }
        if (!isLt2M) {
   
          console.log("上传模板大小不能超过 10MB!");
        }
        return (extension || extension2) && isLt2M;
      },
      submitUpload() {
   
        if (this.businessType != null) {
   
          //触发组件的action
          this.$refs.upload.submit();
        }
        if (this.businessType == null) {
   
          this.businessType = "businessType不能为空";
        }
      },
      handleRemove(file, fileList) {
   
        console.log(file, fileList);
      },
      handlePreview(file) {
   
        if (file.response>0) {
   
          alert("此文件导入成功"+file.response+"条记录");
        } else {
   
          alert("此文件导入失败");
        }
      }
    }
  }
</script>

后端

1.工具类

public class ExcelUtil {
   
    /**
     * xls 后缀
     */
    public static final String XLS = "xls";
    /**
     * xlsx 后缀
     */
    public static final String XLS_X = "xlsx";
    /**
     * 列不对等
     */
    public static final String ROW_NUM_ERROR = "导入模板异常！";

    /**
     * 文件不存在
     */
    public static final String FILE_NOT_ERROR = "文件不存在！";
    /**
     * 表头错误
     */
    public static final String NAME_ERROR = "表头错误！";
    /**
     * 实体空异常
     */
    public static final String BEAN_ERROR = "实体空异常！";
    /**
     * 科学计数
     */
    public static final String E = "e";


    /**
     * 传入文本对象输出list集合（导入）
     *
     * @param file  流文件
     * @param clazz 要转义成的类对象
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz) {
   
        // 检查文件
        Workbook workbook = getWorkBook(file);
        List<T> list = new ArrayList<T>();
        checkFile(file);
        // 获得HSSFWorkbook工作薄对象
        //获取对象总数量并按注解排序
        Field[] fields = getSortFieldsImport(clazz);

        if (workbook != null) {
   
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
   
                // 获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null || sheet.getLastRowNum() == 0) {
   
                    continue;
                }
                // 获取当前sheet工作表的列总数
                int firstLine = sheet.getRow(0).getPhysicalNumberOfCells();
                if (fields.length != firstLine) {
   
                    throw new BusinessException(ROW_NUM_ERROR);
                }
                // 获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                // 获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                // 循环所有行
                for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
   
                    // 获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
   
                        continue;
                    }
                    Object obj = getNewInstance(clazz);
                    for (int cellNum = 0; cellNum < firstLine; cellNum++) {
   
                        // 取出对应注解
                        ExcelImport excelImport = fields[cellNum].getAnnotation(ExcelImport.class);
                        Cell cell = row.getCell(cellNum);
                        if (rowNum == 0) {
   
                            // 第一行 判断表头名称
                            if (cell == null || StringUtils.isEmpty(cell.getStringCellValue())
                                    || !cell.getStringCellValue().equals(excelImport.titleName())) {
   
                                throw new BusinessException(NAME_ERROR);
                            }
                            continue;
                        }
                        Object value = getCellValue(cell);
                        // 判断注解是否允许空值
                        if (!excelImport.empty()) {
   
                            if (value == null || "".equals(value)) {
   
                                log.error("【excel导入】{} 列不能为空！", excelImport.titleName());
                                throw new BusinessException(excelImport.titleName() + "列不能为空");
                            }
                        }
                        // 根绝类型 实体类赋值
                        createBean(fields[cellNum], obj, value);
                    }
                    if (rowNum == 0) {
   
                        // 表头不做记录
                        continue;
                    }
                    list.add((T) obj);
                }
            }
        }
        return list;
    }

    /**
     * 传入文本对象输出Class（导入）
     *
     * @param file  流文件
     * @param clazz 要转义成的类对象
     * @return
     */
    public static <T> T importMultiple(MultipartFile file, Class<T> clazz) {
   
        // 检查文件
        checkFile(file);
        // 获得HSSFWorkbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //获取对象总数量
        Field[] fields = getSortFieldsByExcelSheet(clazz);
        Object obj = getNewInstance(clazz);
        if (workbook != null) {
   
            ExcelSheet excel = null;
            Field field = null;
            int order = 0;
            Sheet sheet = null;
            for (int fieldNum = 0; fieldNum < fields.length; fieldNum++) {
   
                field = fields[fieldNum];
                excel = field.getAnnotation(ExcelSheet.class);
                order = excel.sheetNum();
                sheet = workbook.getSheetAt(order);
                Type genericType = field.getGenericType();
                ParameterizedType pt = (ParameterizedType) genericType;
                //得到泛型里的class类型对象
                Class<?> genericClazz = (Class<?>) pt.getActualTypeArguments()[0];
                createBean(field, obj, importMultiple(sheet, genericClazz, excel));

            }

        }

        return (T) obj;
    }

    private static <T> List<T> importMultiple(Sheet sheet, Class<T> clazz, ExcelSheet excelSheet) {
   
        int titleRow = excelSheet.titleRow();
        Field[] fields = getSortFieldsImport(clazz);
        List<T> list = new ArrayList<T>();
        // 获取当前sheet工作表的列总数
        int firstLine = sheet.getRow(titleRow).getPhysicalNumberOfCells();
        if