springBoot整合ApachePoi,vue3实现excel的数据导入和校验,导出,和错误报告的下载;

废话不多说,直接上代码:

1.后端代码:


1.1controller层

 @GetMapping("/export-excel")
    @Operation(summary = "导出扶优对象信息 Excel")
    @PreAuthorize("@ss.hasPermission('attention:object:export')")
    @ApiAccessLog(operateType = EXPORT)
    public void exportObjectExcel(@Valid ObjectPageReqVO pageReqVO,
                                  HttpServletResponse response) throws IOException {
        pageReqVO.setPageSize(PageParam.PAGE_SIZE_NONE);
        List<ObjectDO> list = objectService.getObjectPage(pageReqVO).getList();
        // 导出 Excel
        ExcelUtils.write(response, "扶优对象信息.xls", "数据", ObjectRespVO.class,
                BeanUtils.toBean(list, ObjectRespVO.class));
    }

    /**
     * @param request
     * @param response
     * @return ResultData
     * @Title: universityTemplate
     * @Description: 下载导入模板
     * @author: bzq
     * @date 2023-03-09
     */
    @GetMapping(value = "/get-import-template")
    public void problemTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {
        objectService.problemTemplate(request, response);
    }

    /**
     * @param _userId
     * @param reportDataReq
     * @Title: reportDepartment
     * @Description: 批量导入文体记录
     * @author: bzq
     * @date 2023-03-09
     */
    @PostMapping(value = "/reportDepartment")
    public ResultData reportDepartment(@RequestBody @Validated ReportDataReq reportDataReq) throws IOException {
        return objectService.reportDepartment(reportDataReq);
    }

    /*
     * 下载错误报告
     * */
    @PostMapping(value = "/downloadWrongReport")
    public void downloadWrongReport(HttpServletResponse response, @RequestBody @Validated ReportDataReq reportDataReq) throws IOException {
        objectService.downloadWrongReport(response, reportDataReq.getFileUrl());
    }

    /**
     * @param _userId
     * @param file
     * @return ResultData
     * @Title: uploadFile
     * @Description: 上传文件
     * @author: bzq
     * @date 2023-03-09
     */
    @PostMapping(value = "/uploadFile")
    public ResultData uploadFile(MultipartFile file) throws IOException {
        return objectService.uploadFile(file);
    }

1.2service层:         

 /**
     * 获得扶优对象信息分页
     *
     * @param pageReqVO 分页查询
     * @return 扶优对象信息分页
     */
    PageResult<ObjectDO> getObjectPage(ObjectPageReqVO pageReqVO);
    /**
     * 批量导入用户
     *
     * @return 导入结果
     */
//    ObjectImportRespVO importObjectList(List<ObjectImportExcelVO> importUsers, boolean isUpdateSupport);
    void problemTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException;

    ResultData reportDepartment( ReportDataReq reportDataReq) throws IOException;
    /*
    * 文件上传
    * */
    ResultData uploadFile( MultipartFile file) throws IOException;
/*
* 下载错误报告
* */
    void downloadWrongReport( HttpServletResponse response, String fileUrl) throws IOException;

1.3serviceimpl层

 @Override
    public PageResult<ObjectDO> getObjectPage(ObjectPageReqVO pageReqVO) {
        return objectMapper.selectPage(pageReqVO);
    }
    public void problemTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String path = "file/objectLoad.xlsx";
        URL excelUrl = getClass().getClassLoader().getResource(path);
        File excelFile = new File(excelUrl.getFile());
        FileInputStream inputStream = new FileInputStream(excelFile);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        ServletOutputStream outputStream = null;
        try {
            // 响应头
            response.setHeader("content-type", "application/octet-stream");
            // 防止乱码
            response.setHeader("content-disposition",
                    "attachment;filename=" + URLEncoder.encode("优抚对象导入模版.xlsx", "UTF-8"));
            response.setHeader("Access-Control-Expose-Headers", "content-disposition");
            outputStream = response.getOutputStream();
            xssfWorkbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != outputStream) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    @Override
    public ResultData reportDepartment(ReportDataReq reportDataReq) throws IOException {
        String fileUrl = reportDataReq.getFileUrl();
        File file = new File(fileUrl);
        Random random = new Random();
        long randomLong = random.nextLong();
        if (!file.exists()) {
            return ResultData.error("请确保文件上传成功");
        }
        FileInputStream inputStream = new FileInputStream(file);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum < 1) {
            return ResultData.error("请填写数据之后再上传");
        }
        DataFormatter formatter = new DataFormatter();
        boolean flag = true;
        for (int i = 1; i <= lastRowNum; i++) {
            StringBuffer stringBuffer = new StringBuffer();
            XSSFRow data = sheet.getRow(i);
            if(data == null){
                continue;
            }
            XSSFCell cell1 = data.getCell(0);
            String cell11 = formatter.formatCellValue(cell1);
            XSSFCell cell2 = data.getCell(1);
            String cell22 = formatter.formatCellValue(cell2);
            XSSFCell cell3 = data.getCell(2);
            String cell33 = formatter.formatCellValue(cell3);
            XSSFCell cell4 = data.getCell(3);
            String cell44 = formatter.formatCellValue(cell4);
            XSSFCell cell5 = data.getCell(4);
            String cell55 = formatter.formatCellValue(cell5);
            XSSFCell cell7 = data.getCell(6);
            String cell77 = formatter.formatCellValue(cell7);
            if (cell11 == null || StringUtils.isBlank(cell11)) {
                stringBuffer.append("A(所属小区)不能为空,");
                flag = false;
            }
            if (cell22 == null || StringUtils.isBlank(cell22)) {
                stringBuffer.append("B(居住房屋)不能为空,");
                flag = false;
            }
            if (cell33 == null || StringUtils.isBlank(cell33)) {
                stringBuffer.append("C(姓名)不能为空,");
                flag = false;
            }
            if (cell44 == null || StringUtils.isBlank(cell44)) {
                stringBuffer.append("D(身份证号)不能为空,");
                flag = false;
            }
            if (cell55 == null || StringUtils.isBlank(cell55)) {
                stringBuffer.append("E(联系电话)不能为空,");
                flag = false;
            }
            if (cell77 == null || StringUtils.isBlank(cell77)) {
                stringBuffer.append("G(优抚对象)不能为空,");
                flag = false;
            } else {
                if (cell11 != null) {
                    DeptCommUnityRespDDO deptCommUnityRespDDO = deptCommUnityApi.getCommUnityName(cell11);
                    if (deptCommUnityRespDDO == null) {
                        stringBuffer.append("A(所属小区)不存在,");
                        flag = false;
                    }
                }
                if (cell22 != null) {
                    DeptCommUnityRespDDO deptCommUnityRespDDO = deptCommUnityApi.getCommUnityName(cell11);
                    List<DeptHouseRespVO> list =deptCommUnityApi.getHouseList(String.valueOf(deptCommUnityRespDDO.getId()));
                    Optional<DeptHouseRespVO> optional = list.stream().filter(x -> x.getHoName().equals(cell22)).findAny();
                    if (!optional.isPresent()) {
                        stringBuffer.append("B(居住房屋)不存在,");
                        flag = false;
                    }
                }
                if (cell44 != null) {
                    QueryWrapper<ObjectDO> deptCommUnityDOQueryWrapper = new QueryWrapper<ObjectDO>().eq("fo_idcard",
                            cell44);

                    List<ObjectDO> list = objectMapper.selectList(deptCommUnityDOQueryWrapper);
                    if (list != null && list.size() > 0) {
                        stringBuffer.append("D(身份证号)已存在,");
                        flag = false;
                    }
                }
                if (cell55 != null) {
                    QueryWrapper<ObjectDO> deptCommUnityDOQueryWrapper = new QueryWrapper<ObjectDO>().eq("fo_phone",
                            cell55);

                    List<ObjectDO> list = objectMapper.selectList(deptCommUnityDOQueryWrapper);
                    if (list != null && list.size() > 0) {
                        stringBuffer.append("E(联系电话)已存在,");
                        flag = false;
                    }
                }
                if (stringBuffer.length() > 0) {
                    XSSFCell cell = data.createCell(20);
                    cell.setCellValue(stringBuffer.toString().substring(0, stringBuffer.length() - 1));
                }
            }
        }
        if (flag) {
            for (int j = 1; j <= lastRowNum; j++) {
                XSSFRow data2 = sheet.getRow(j);
              //  String foCommunityId = data2.getCell(0).getStringCellValue();
                String foCommunityId = formatter.formatCellValue(data2.getCell(0));
                String foBuildId = formatter.formatCellValue(data2.getCell(1));
                String foName = formatter.formatCellValue(data2.getCell(2));
                String foIdcard = formatter.formatCellValue(data2.getCell(3));
                String foPhone =formatter.formatCellValue(data2.getCell(4));
                String foSex = formatter.formatCellValue(data2.getCell(5));
                String foFavoredType = formatter.formatCellValue(data2.getCell(6));
                String foFavoredDetail = formatter.formatCellValue(data2.getCell(7));
                String foPersonType = formatter.formatCellValue(data2.getCell(8));
                String foPoliticalStatus = formatter.formatCellValue(data2.getCell(9));
                String foMaritalStatus = formatter.formatCellValue(data2.getCell(10));
                String foWorkUnit = formatter.formatCellValue(data2.getCell(11));
                String foAcconut = formatter.formatCellValue(data2.getCell(12));
                String foNativePlace = formatter.formatCellValue(data2.getCell(13));
                String foDomicileAddress = formatter.formatCellValue(data2.getCell(14));
                String fo2021 = formatter.formatCellValue(data2.getCell(15));
                String fo2022 = formatter.formatCellValue(data2.getCell(16));
                String fo2023 = formatter.formatCellValue(data2.getCell(17));
                String foRemark = formatter.formatCellValue(data2.getCell(18));
                ObjectDO objectDO = new ObjectDO();
                CommonResult<DictDataRespDTO> dictDataRespDTO = dictDataApi.parseDictData("system_user_sex", foSex);

                CommonResult<DictDataRespDTO> dictDataRespDTO2 = dictDataApi.parseDictData("fo_favored_type", foFavoredType);

                CommonResult<DictDataRespDTO> dictDataRespDTO3 = dictDataApi.parseDictData("fo_favored_detail", foFavoredDetail);

                CommonResult<DictDataRespDTO> dictDataRespDTO4 = dictDataApi.parseDictData("fo_person_type", foPersonType);

                CommonResult<DictDataRespDTO> dictDataRespDTO5 = dictDataApi.parseDictData("fo_political_status", foPoliticalStatus);

                CommonResult<DictDataRespDTO> dictDataRespDTO6 = dictDataApi.parseDictData("marital_status", foMaritalStatus);

                DeptCommUnityRespDDO deptCommUnityRespDDO = deptCommUnityApi.getCommUnityName(foCommunityId);
                objectDO.setFoCommunityId(deptCommUnityRespDDO.getId());

                List<DeptHouseRespVO> list =deptCommUnityApi.getHouseList(String.valueOf(deptCommUnityRespDDO.getId()));
                Optional<DeptHouseRespVO> optional = list.stream().filter(x -> x.getHoName().equals(foBuildId)).findAny();
                if (optional.isPresent()) {
                    objectDO.setFoBuildId(optional.get().getId());
                }
                objectDO.setFoName(foName);
                objectDO.setFoIdcard(foIdcard);
                objectDO.setFoPhone(foPhone);
                objectDO.setFoWorkUnit(foWorkUnit);
                objectDO.setFoAcconut(foAcconut);
                objectDO.setFoNativePlace(foNativePlace);
                objectDO.setFoDomicileAddress(foDomicileAddress);
                objectDO.setFo2021(fo2021);
                objectDO.setFo2022(fo2022);
                objectDO.setFo2023(fo2023);
                objectDO.setFoRemark(foRemark);
                objectDO.setFoBatchNo(randomLong);
                try {
                    objectDO.setFoSex(dictDataRespDTO.getData().getValue());
                    objectDO.setFoFavoredType(dictDataRespDTO2.getData().getValue());
                    objectDO.setFoFavoredDetail(dictDataRespDTO3.getData().getValue());
                    objectDO.setFoPersonType(dictDataRespDTO4.getData().getValue());
                    objectDO.setFoPoliticalStatus(dictDataRespDTO5.getData().getValue());
                    objectDO.setFoMaritalStatus(dictDataRespDTO6.getData().getValue());
                } catch (Exception e) {
                    e.printStackTrace();
                }
                save(objectDO);
            }
            return ResultData.success("ok");
        } else {

            File out = new File(fileUrl);
            FileOutputStream outputStream = new FileOutputStream(out);
            xssfWorkbook.write(outputStream);
            outputStream.close();
            return ResultData.error("导入文件中有数据不合法,请查看错误信息说明", reportDataReq.getFileUrl());
        }
    }

    @Override
    public ResultData uploadFile(MultipartFile file) throws IOException {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum < 1) {
            return ResultData.error("请确保填写数据之后再进行上传");
        }
        //URL url = getClass().getClassLoader().getResource("static/file/");
        String abstractPath = uploadPath + "static/file" + File.separator + new Date().getTime();
        File dir = new File(abstractPath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        String path = abstractPath + "/" + file.getOriginalFilename();
        File dest = new File(path);
        try {
            file.transferTo(dest);
            return ResultData.success("ok", path);
        } catch (Exception e) {
            e.printStackTrace();
            return ResultData.error("上传文件失败");
        }
    }

    public void downloadWrongReport(HttpServletResponse response, String fileUrl) throws IOException {
        File file = new File(fileUrl);
        // 父级id
        FileInputStream inputStream = new FileInputStream(file);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        ServletOutputStream outputStream = null;
        try {
            // 响应头
            response.setHeader("Access-Control-Expose-Headers", "content-disposition");
            // 防止乱码
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("错误报告.xlsx", "UTF-8"));
            response.setHeader("content-type", "application/octet-stream");
            outputStream = response.getOutputStream();
            xssfWorkbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != outputStream) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

2.前端代码:

<template>
  <Dialog v-model="dialogVisible" title="批量导入" width="400">
    <el-upload
      ref="uploadRef"
      v-model:file-list="fileList"
      style="text-align: center"
      class="upload-demo"
      :action="importUrl"
      drag
      :headers="uploadHeaders"
      :limit="1"
      :before-upload="beforeAvatarUpload"
      :on-error="submitFormError"
      :on-exceed="handleExceed"
      :on-success="uploadSuccess"
      name="file"
    >
      <Icon icon="ep:upload" />
      <div class="el-upload__text">将文件拖到此处,或<em>点击上传</em></div>
      <template #tip>
        <div class="el-upload__tip text-center">
          <span>仅允许导入 xls、xlsx 格式文件;请按照要求填写模版文件</span>
          <el-link
            :underline="false"
            style="font-size: 12px; vertical-align: baseline"
            type="primary"
            @click="importTemplate"
          >
            下载导入模板
          </el-link>
        </div>
      </template>
    </el-upload>
    <template #footer>
      <el-button :disabled="formLoading" type="primary" @click="reportDepartment">导入</el-button>
      <el-button @click="dialogVisible = false">取 消</el-button>
    </template>
  </Dialog>
</template>
<script lang="ts" setup>
import { ObjectApi } from '@/api/attention/object'
import { getAccessToken, getTenantId } from '@/utils/auth'
import download from '@/utils/download'
defineOptions({ name: 'ObjectImportForm' })
const message = useMessage() // 消息弹窗
const reportData = {
  fileUrl: null
}
const dialogVisible = ref(false) // 弹窗的是否展示
const formLoading = ref(false) // 表单的加载中
const uploadRef = ref()
const importUrl = import.meta.env.VITE_BASE_URL + import.meta.env.VITE_API_URL + '/attention/object/uploadFile'
const uploadHeaders = ref() // 上传 Header 头
const fileList = ref([]) // 文件列表
const updateSupport = ref(0) // 是否更新已经存在的用户数据
const file = ref()
/** 打开弹窗 */
const open = () => {
  dialogVisible.value = true
  fileList.value = []
  resetForm()
}
defineExpose({ open }) // 提供 open 方法,用于打开弹窗

// 判断文件格式
const beforeAvatarUpload = (file) => {
  // 提交请求
  uploadHeaders.value = {
    Authorization: 'Bearer ' + getAccessToken(),
    'tenant-id': getTenantId()
  }
  const fileName = file.name
  file.value = file.name
  let flag = false
  // 取出上传文件的扩展名
  const index = fileName.lastIndexOf('.')
  const ext = fileName.substring(index + 1)
  // 循环比较
  if (ext === 'xlsx' || ext === 'xls') {
    flag = true
  }
  if (!flag) {
    message.error('此格式文件不允许上传!')
    return false
  }
  return true
}
// 上传成功之后文件非空判断
const uploadSuccess = (response) => {
  if (response.code === 0) {
    reportData.fileUrl = response.data
  } else {
    reportData.fileUrl = null
    message.error('文件内容不能为空,请确保填写数据之后再进行上传')
    return
  }
}
//保存导入
/** 提交表单 */
const emit = defineEmits(['success']) // 定义 success 事件,用于操作成功后的回调
const reportDepartment = async () => {
  if (!reportData.fileUrl) {
    message.error('请先上传文件只有在进行保存')
    return
  }
  uploadHeaders.value = {
    Authorization: 'Bearer ' + getAccessToken(),
    'tenant-id': getTenantId()
  }
  formLoading.value = true
 
  await ObjectApi.reportDepartment(reportData).then((res: any) => {
    message.success('上传成功'); 
    dialogVisible.value = false
    emit('success')
  }).catch((res: any) => {
    ObjectApi.downloadWrongReport(reportData).then((res: any) => {
      downloadByData(res, "优抚对象导入模版错误信息.xlsx");
    });
  });
  formLoading.value = false
}

/**
* 拿到文件流后的下载的方法
* @param {*} data
* @param {*} filename
* @param {*} mime
* @param {*} bom
*/
const downloadByData = async (data: BlobPart, filename: string, mime?: string, bom?: BlobPart) =>  {
  const blobData = typeof bom !== 'undefined' ? [bom, data] : [data];
  const blob = new Blob(blobData, { type: mime || 'application/octet-stream' });

  const blobURL = window.URL.createObjectURL(blob);
  const tempLink = document.createElement('a');
  tempLink.style.display = 'none';
  tempLink.href = blobURL;
  tempLink.setAttribute('download', filename);
  if (typeof tempLink.download === 'undefined') {
    tempLink.setAttribute('target', '_blank');
  }
  document.body.appendChild(tempLink);
  tempLink.click();
  document.body.removeChild(tempLink);
  window.URL.revokeObjectURL(blobURL);
}
/** 上传错误提示 */
const submitFormError = (): void => {
  message.error('上传失败,请您重新上传!')
  formLoading.value = false
}

/** 重置表单 */
const resetForm = () => {
  // 重置上传状态和文件
  formLoading.value = false
  uploadRef.value?.clearFiles()
}

/** 文件数超出提示 */
const handleExceed = (): void => {
  message.error('最多只能上传一个文件!')
}

/** 下载模板操作 */
const importTemplate = async () => {
  const res = await ObjectApi.importUserTemplate()
  download.excel(res, '优抚对象导入模版.xls')
}
</script>

代码就这些,有不懂的可以私信我;我看到后会回复!!!!!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

m87里的光

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值