废话不多说,直接上代码:
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>
代码就这些,有不懂的可以私信我;我看到后会回复!!!!!