vue+Excelkit实现导入导出

导入/导出/下载模板

vue代码

<el-button v-if="isAuth('school:paper:import')" type="primary" @click="exportHandle(1)">导出</el-button>
<el-button v-if="isAuth('school:paper:import')" type="primary" @click="exportHandle(0)">下载模板</el-button>

<el-button v-if="isAuth('school:paper:import')" type="primary" @click="importHandle()">导入</el-button>

导入 弹框

<!-- 弹窗, 导入 -->
<import-excel v-if="importVisible" ref="importExcel" @refreshDataList="getDataList"></import-excel>

引入导入页面

import ImportExcel from './paper-import'
components: {
  ImportExcel
},

// 下载模板
exportHandle(val){
  this.$http({
    url: this.$http.adornUrl('/school/paper/downTemplate/'+val),
    method: 'get',
    responseType: 'blob',
  }).then(res => {
    let fileName = '题库模板'+(new Date()).getTime()+'.xlsx'
    this.download(res.data,fileName)
  });
},
download (data,fileName) {
 if (!data) {
      return
  }
  let url = window.URL.createObjectURL(new Blob([data]))
  let link = document.createElement('a')
  link.style.display = 'none'
  link.href = url
  link.setAttribute('download', fileName)
  document.body.appendChild(link)
  link.click()
},

// 导入
importHandle(){
  this.importVisible = true
  this.$nextTick(() => {
    this.$refs.importExcel.init()
  })
},

导入页面代码

<template>
  <el-dialog
    :title="'导入'"
    :close-on-click-modal="false"
    :visible.sync="visible">
    <el-form @keyup.enter.native="submitUpload()" label-width="80px">
      <el-form-item label="上传文件" prop="explain">
        <el-upload
          class="upload-demo"
          ref="upload"
          action="doUpload"
          accept=".xls,.xlsx"
          :limit="1"
          :file-list="fileList"
          :before-upload="beforeUpload">
          <el-button slot="trigger" size="small" type="primary">上传题库</el-button>
          <div slot="tip" class="el-upload__tip">请选择xls/xlsx文件上传</div>
          <div slot="tip" class="el-upload-list__item-name">{{fileName}}</div>
        </el-upload> 
      </el-form-item>
    </el-form>
    <span slot="footer" class="dialog-footer">
      <el-button @click="visible = false">取消</el-button>
      <el-button type="primary" @click="submitUpload()">确定</el-button>
    </span>
  </el-dialog>
</template>

<script>
  export default {
    data () {
      return {
        visible: false,
        files:"",
        fileName:'',
        fileList: [],
      }
    },
    methods: {
      init () {
        this.visible = true
      },
      submitUpload() {
        debugger
        console.log('上传'+this.files.name)
        if(this.fileName == ""){
          this.$message.warning('请选择要上传的文件！')
          return false
        }
        let fileFormData = new FormData();
        fileFormData.append('file', this.files, this.fileName);
        let requestConfig = {
          headers: {
            'Content-Type': 'multipart/form-data'
          },
        }
        this.$http.post(this.$http.adornUrl(`/school/paper/import`), fileFormData, requestConfig).then(({data}) => {
            if (data && data.code === 0) {
            this.$message({
              message: '操作成功',
              type: 'success',
              duration: 1500,
              onClose: () => {
                this.visible = false
                this.$emit('refreshDataList')
              }
            })
          } else if(data.code === -1){
            this.$message.error(data.msg)
            this.visible = false
          } else {
            this.$message.error(data.msg)
            this.visible = false
          }
        })  
      },
      // 上传前对文件的大小的判断
      beforeUpload(file){
        this.files = file;
        const extension = file.name.split('.')[1] === 'xls'
        const extension2 = file.name.split('.')[1] === 'xlsx'
        const isLt2M = file.size / 1024 / 1024 < 5
        if (!extension && !extension2) {
          this.$message.warning('上传模板只能是 xls、xlsx格式!')
          return
        }
        if (!isLt2M) {
          this.$message.warning('上传模板大小不能超过 5MB!')
          return
        }
        this.fileName = file.name;
        return false // 返回false不会自动上传
      },
  
    }
  }
</script>

后台

引入依赖

<dependency>
	<groupId>xml-apis</groupId>
	<artifactId>xml-apis</artifactId>
	<version>1.4.01</version>
</dependency>

controller
一行代码构建 Excel 导入模板
使用 ExcelKit 提供的API 构建导入模板, 会根据配置生成批注, 下拉框等

/**
 * 导出
 */
@GetMapping("/downTemplate/{type}")
@RequiresPermissions("school:paper:import")
public void downTemplate(HttpServletResponse response,@PathVariable("type") Integer type) {
    List<PaperEntity> list = new ArrayList<>();
    if(type == 1){
        list = paperService.selectList(new EntityWrapper<PaperEntity>());
        ExcelKit.$Export(PaperEntity.class, response).downXlsx(list, false);
    }else{
        list = paperService.getListLimit();
        ExcelKit.$Export(PaperEntity.class, response).downXlsx(list, true);
    }
}

/**
* 导入文件
*/
@SysLog("导入题库")
@PostMapping("/import")
@RequiresPermissions("school:paper:import")
public R upload(@RequestParam("file") MultipartFile file) {
   if (file.isEmpty()) {
       throw new RRException("上传文件不能为空");
   }
   //上传文件
   String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
   if(StringUtils.equalsAnyIgnoreCase(suffix,".xls")
           ||StringUtils.equalsAnyIgnoreCase(suffix,".xlsx")){
       List<PaperEntity> successList = new ArrayList();
       List<Map<String, Object>> errorList = new ArrayList();
       try {
           //题号
           Integer type1 = 0;Integer type2 = 0;Integer type3 = 0;Integer type4 = 0;
           List<PaperEntity> mapType = paperService.getTypeNumMax();
           for (PaperEntity obj : mapType) {
               type1 = "1".equals(obj.getType())?obj.getNum():0;
               type2 = "2".equals(obj.getType())?obj.getNum():0;
               type3 = "3".equals(obj.getType())?obj.getNum():0;
               type4 = "4".equals(obj.getType())?obj.getNum():0;
           }
           Integer num1 = type1;
           Integer num2 = type2;
           Integer num3 = type3;
           Integer num4 = type4;
           ExcelKit.$Import(PaperEntity.class).readXlsx(file.getInputStream(), new ExcelReadHandler<PaperEntity>() {

               @Override
               public void onSuccess(int sheetIndex, int rowIndex, PaperEntity entity) {
                   Integer num = 0;
                   switch (entity.getType()){
                       case "1":
                           num = num1 + 1;
                           break;
                       case "2":
                           num = num2 + 1;
                           break;
                       case "3":
                           num = num3 + 1;
                           break;
                       case "4":
                           num = num4 + 1;
                           break;
                       default:
                           num = 0;
                   }
                   entity.setNum(num);
                   successList.add(entity); // 单行读取成功，加入sucessList。
               }

               @Override
               public void onError(int sheetIndex, int rowIndex,
                                   List<ExcelErrorField> errorFields) {
                   // 读取数据失败，记录了当前行所有失败的数据
                   Map<String, Object> map = new HashMap<>();
                   map.put("sheetIndex", sheetIndex);
                   map.put("rowIndex", rowIndex);
                   map.put("errorFields", errorFields);
                   errorList.add(map);
               }
           });
       } catch (IOException e) {
           e.printStackTrace();
           logger.error("导入失败:"+e.getMessage());
           throw new RRException("导入失败");
       }
       if(successList.size() > 0){
           paperService.insertBatch(successList);
       }
       if(errorList.size() > 0){
           return R.error(-1,"导入失败数据",errorList);
       }
       return R.ok("成功导入数据:"+successList.size()+"条");
   }else{
       logger.error("上传文件类型不允许，请请上传xls/xlsx文件");
       throw new RRException("上传文件类型不允许，请请上传xls/xlsx文件");
   }
}

entity 根据自己的业务场景实现，目前只展示部分代码

@Excel("题库")
@TableName("paper")
public class PaperEntity implements Serializable {
	private static final long serialVersionUID = 1L;

	/**
	 * 主键
	 */
	@TableId
	private Integer id;
	/**
	 * 问题
	 */
	@ExcelField(value = "问题",required = true,comment = "请填写问题")
	private String questions;
	/**
	 * 选项1
	 */
	@ExcelField(value = "选项1",required = true,comment = "请设置选项1")
	private String answer1;
	/**
	 * 选项2
	 */
	@ExcelField(value = "选项2",required = true,comment = "请设置选项2")
	private String answer2;
	/**
	 * 选项3
	 */
	@ExcelField(value = "选项3",required = true,comment = "请设置选项3")
	private String answer3;
	/**
	 * 选项4
	 */
	@ExcelField(value = "选项4",required = true,comment = "请设置选项4")
	private String answer4;
	/**
	 * 正确答案
	 */
	@ExcelField(value = "正确答案",required = true,comment = "请设置正确答案")
	private String trueanswer;
	/**
	 * 类型
	 */
    @ExcelField(value = "类型",required = true,comment = "请填写类型",
			options = PaperType.class,
			// 写文件时，将数字转字符串
			writeConverter = PaperTypeWrite.class,
			// 读文件时，将字符串转数字
			readConverter = PaperTypeRead.class)
	private String type;
	/**
	 * 解析
	 */
	@ExcelField(value = "解析",required = true,comment = "请填写解析")
	private String explain;
}

实现相关的转换器
设备导出的下拉选择

public class PaperType implements Options{
    @Override
    public String[] get() {
        return new String[]{"中国近代史", "中国古代史","世界古代史","世界近现代史"};
    }
}

写文件时，将值进行转换（此处示例为将数值拼接为指定格式的字符串）

public class PaperTypeWrite implements WriteConverter {
    @Override
    public String convert(Object o) throws ExcelKitWriteConverterException {
        String typeName = "";
        String type = o.toString();
        switch (type){
            case "1" :
                typeName = "中国近代史";
                break; 
            case "2" :
                typeName = "中国古代史";
                break; 
            case "3" :
                typeName = "世界古代史";
                break; 
            case "4" :
                typeName = "世界近现代史";
                break; 
            default:
                return "";
        }
        return typeName;
    }

}

写文件时，将值进行转换（此处示例为将数值拼接为指定格式的字符串）

public class PaperTypeWrite implements WriteConverter {
    @Override
    public String convert(Object o) throws ExcelKitWriteConverterException {
        String typeName = "";
        String type = o.toString();
        switch (type){
            case "1" :
                typeName = "中国近代史";
                break; 
            case "2" :
                typeName = "中国古代史";
                break; 
            case "3" :
                typeName = "世界古代史";
                break; 
            case "4" :
                typeName = "世界近现代史";
                break; 
            default:
                return "";
        }
        return typeName;
    }

}

以上就可实现通过ExcelKit 导入/导出excel文件