easyPoi框架的excel导入导出

一 导出

<a-button @click="exportDataList" icon="download" type="success" style="margin-right: 8px">导出数据</a-button>

 //导出表格
    exportDataList () {
      //导出表格的查询条件
      this.queryParam.type = this.activeKey
      //导出
      exportData(this.queryParam).then(res => {
        //创建二进制数据对象
        const blbo = new Blob([res], { type: 'application/vnd.ms-excel;charset=utf-8' })
        //创建a标签
        const link = document.createElement('a')
        //创建一个指向二进制文件的URL
        link.href = window.URL.createObjectURL(blbo)
        //文件名
        link.download = '物流公司信息.xls'
        //触发下载
        link.click()
      })
    },

 /**
     * 导出物流公司信息
     *
     * @param fisLogisticsCompanyInfo 导出实体类
     * @param commonQuery             //查询参数
     * @param response                //http响应
     */
    @SneakyThrows
    @GetMapping("/logisticsCompany/export")
    @ApiOperation(value = "导出物流公司信息")
    public void export(FisLogisticsCompanyInfo fisLogisticsCompanyInfo, CommonQuery commonQuery, HttpServletResponse response) {
        //导出的查询条件
        QueryWrapper<FisLogisticsCompanyInfo> autoQueryWrapper = QueryGenerator.initQueryWrapper(fisLogisticsCompanyInfo, commonQuery);
        //查询出所有要导出的数据
        List<FisLogisticsCompanyInfo> fisLogisticsCompanyInfos = fisLogisticsCompanyInfoService.list(autoQueryWrapper);
        //利用导出工具类将数据导出
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("物流公司信息", "container"),
                FisLogisticsCompanyInfo.class, fisLogisticsCompanyInfos);
        //写道输出流
        workbook.write(response.getOutputStream());
    }

二 下载导入

　//导入向导按钮 点击调用importData方法 
 <a-button @click="importData" icon="upload" style="margin-right: 8px" type="plain">导入向导</a-button>

//触发组件的show方法 展示文件上传页面

importData () { 

　　 this.$refs.BImportFile.show()

    },

//当前页面使用了文件上传组件 
<b-import-file @ok="queryPage" :actionUrl="actionUrl" :des="des" ref="BImportFile" @download="download"/>

//引入文件上传组件
import BImportFile from '@/components/BImportFile'

//注册到组件
components: {
    MESSuperQuery,
    modalForm,
    BImportFile //封装好的导入组件
  },

//点击组件的下载导入模板  触发组件的download 
<div @click='download' class='upload-dragger'>
          <a-upload-dragger
            :headers='headers'
            :multiple='true'
            :openFileDialogOnClick='false'
            @change='handleChange'
            action='/api/storage/upload'
            name='file'
          >
            <p class='ant-upload-drag-icon'>
              <a-icon type='cloud-download' />
            </p>
            <p class='ant-upload-text'>先下载要上传的模板</p>
            <p class='ant-upload-hint'>
              支持扩展名：xls,xlsx
            </p>
          </a-upload-dragger>
        </div>

//向父组件传递事件
 download() {
      this.$emit('download')
    },

 //父组件接受子组件传来的事件 下载excel模板
    download () {
      logisticsCompanyTemplate().then(res => {
        //创建二进制数据对象
        const blbo = new Blob([res], { type: 'application/vnd.ms-excel;charset=utf-8' })
        //创建a标签
        const link = document.createElement('a')
        //创建一个指向二进制文件的URL
        link.href = window.URL.createObjectURL(blbo)
        link.download = '物流公司导入模板.xlsx'
        //点击a标签
        link.click()
      }).catch((e) => {
        this.$message.warning('下载失败')
      })
    },

 /**
     * 下载物流公司导入模板
     *
     * @return
     */
    @SneakyThrows
    @GetMapping("/logisticsCompany/template")
    @ApiOperation(value = "物流公司导入模板")
    public void downTemplate(HttpServletResponse response) {
        //获取数据库中模板文件名称
        String rfidTemplate = Global.getConfig("logisticsCompanyTemplate");
        //获取bucketName 默认bootfile
        String bucketName = Global.getConfig("sys.file.bucketName", "bootfile");
        //根据buckName和文件名称返回二进制流
        S3Object object = minioTemplate.getObject(bucketName, rfidTemplate);
        IoUtil.copy(object.getObjectContent(), response.getOutputStream());
    }

三 导入

<div
          class='upload-dragger'
        >
          <a-upload-dragger
            :beforeUpload='beforeFileUpload'
            :fileList='fileList'
            :headers='headers'
            :multiple='true'
            @change='handleChange'
            :action='actionUrl'
            name='file'
          >
            <p class='ant-upload-drag-icon'>
              <a-icon type='cloud-upload' />
            </p>
            <p class='ant-upload-text'>上传要导入的文件</p>
            <p class='ant-upload-hint'>
              支持扩展名：xls,xlsx
            </p>
          </a-upload-dragger>
        </div>

 /**
     * 导入物流公司信息
     *
     * @param file
     * @return
     */
    @SneakyThrows
    @PostMapping("/uploadBom")
    public Result upload(@RequestParam("file") MultipartFile file) {

        List<FisLogisticsCompanyInfo> logisticsCompanyInfoList = FileUtil.importExcel(file, 0, 1, FisLogisticsCompanyInfo.class);


        logisticsCompanyInfoList = logisticsCompanyInfoList.stream().map(fisLogisticsCompanyInfo -> {
            fisLogisticsCompanyInfo.setUpdateBy(SecurityUtil.getUser().getUserId());
            fisLogisticsCompanyInfo.setCreateBy(SecurityUtil.getUser().getUserId());
            fisLogisticsCompanyInfo.setUpdateTime(new Date());
            fisLogisticsCompanyInfo.setCreateTime(new Date());
            return fisLogisticsCompanyInfo;
        }).collect(Collectors.toList());
        fisLogisticsCompanyInfoService.saveBatch(logisticsCompanyInfoList);

        return Res.ok();
    }

 public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        ZipSecureFile.setMinInflateRatio(-1.0d);
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
//            System.out.println("excel文件不能为空");
            throw new NoSuchElementException("excel文件不能为空");
        } catch (Exception e) {
            System.out.println(e.getMessage());
//            throw new NormalException();
            throw new NoSuchElementException(e.getMessage());
        }
        return list;
    }