导出
后端：
@PostMapping("/exportExcelData") public void exportExcelData(HttpServletRequest request, HttpServletResponse response, @RequestBody ResData resData) throws IOException { List<Long> menuIds = resData.getMenuIds(); List<Conversion> conversions = new ArrayList<>(); List<String> ktrFilePaths = new ArrayList<>(); for (Long menuId : menuIds) { Conversion conversion = conversionMapper.selectById(menuId); if (conversion != null) { conversions.add(conversion); String ktrFilePath = fileService.getKtrFilePathById(menuId); if (ktrFilePath != null && !ktrFilePaths.contains(ktrFilePath)) { ktrFilePaths.add(ktrFilePath); } } } // 创建Excel工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表 Sheet sheet = workbook.createSheet("Conversions"); // 创建单元格样式，并设置为文本格式 CellStyle textStyle = workbook.createCellStyle(); DataFormat format = workbook.createDataFormat(); textStyle.setDataFormat(format.getFormat("@")); // "@" 表示文本格式 // 创建标题行 Row titleRow = sheet.createRow(0); // 创建单元格样式，并设置为文本格式 titleRow.createCell(0).setCellValue("主键"); titleRow.createCell(1).setCellValue("分组id"); titleRow.createCell(2).setCellValue("名称"); titleRow.createCell(3).setCellValue("备注"); titleRow.createCell(4).setCellValue("创建人"); titleRow.createCell(5).setCellValue("关联状态"); titleRow.createCell(6).setCellValue("XMl"); titleRow.createCell(7).setCellValue("创建时间"); // 应用文本格式到标题行的特定单元格 titleRow.getCell(0).setCellStyle(textStyle); titleRow.getCell(1).setCellStyle(textStyle); // 填充数据 int rowNum = 1; for (Conversion conversion : conversions) { Row row = sheet.createRow(rowNum++); Cell cell = row.createCell(0); cell.setCellValue(String.valueOf(conversion.getId())); cell.setCellStyle(textStyle); cell = row.createCell(1); cell.setCellValue(String.valueOf(conversion.getGroupId())); cell.setCellStyle(textStyle); // 应用文本格式 row.createCell(2).setCellValue(conversion.getName()); row.createCell(3).setCellValue(conversion.getRemark()); row.createCell(4).setCellValue(conversion.getCreateUserName()); row.createCell(5).setCellValue(conversion.getState()); row.createCell(6).setCellValue(conversion.getEditXml()); row.createCell(7).setCellValue(String.valueOf(conversion.getCreateTime())); } // 设置响应头 response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("conversions.xls", "UTF-8")); // 设置响应头 response.setContentType("application/zip"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("conversions.zip", "UTF-8")); ZipOutputStream zipOut = new ZipOutputStream(response.getOutputStream()); // 将Excel文件添加到压缩包 ZipEntry excelEntry = new ZipEntry("conversions.xls"); zipOut.putNextEntry(excelEntry); workbook.write(zipOut); zipOut.closeEntry(); workbook.close(); // 添加.ktr文件到ktr目录 for (String filePath : ktrFilePaths) { File ktrFile = new File(filePath); if (ktrFile.exists()) { FileInputStream fis = new FileInputStream(ktrFile); // 创建ZipEntry时，需要包含ktr目录 ZipEntry ktrEntry = new ZipEntry("ktr/" + ktrFile.getName()); zipOut.putNextEntry(ktrEntry); byte[] bytes = new byte[1024]; int length; while ((length = fis.read(bytes)) >= 0) { zipOut.write(bytes, 0, length); } fis.close(); zipOut.closeEntry(); } } // 完成压缩包 zipOut.finish(); zipOut.close(); }
导出后的文件组成：
编辑
编辑
编辑
excel文件：
编辑
前端：
<template> <div class="app-container" style="width:100%;"> <el-form label-width="80px" label-position="left"> <el-form-item label="模型树"> <el-tree ref="tree" :data="treeData" show-checkbox :default-expand-all="false" node-key="id" highlight-current :props="defaultProps" /> </el-form-item> </el-form> <div style="text-align: center;width:100%;"> <el-button type="primary" @click="onSave">导出</el-button> <el-button type="danger" @click="closePage">取消</el-button> </div> </div> </template> <script> import { getTreeData } from '@/api/dataSchema' import { exportData,exportExcelData } from '@/api/conversion' import { Message } from 'element-ui' export default { name: 'Zzjg', inject: ['getList'], props: { proid: { type: String, required: true } }, data() { return { defaultProps: { children: 'children', label: 'name' }, treeData: [] } }, methods: { getDetailed() { const loading = this.$loading({ lock: true, text: 'Loading', spinner: 'el-icon-loading', background: 'rgba(0, 0, 0, 0.7)' }) getTreeData().then(response => { this.treeData = response.data loading.close() }).catch(function() { loading.close() }) }, onSave() { var menuIds = this.$refs.tree.getCheckedKeys() if (menuIds.length === 0) { Message({ message: '请选择要导出的模型', type: 'error', duration: 5 * 1000 }) return } else { const loading = this.$loading({ lock: true, text: 'Loading', spinner: 'el-icon-loading', background: 'rgba(0, 0, 0, 0.7)' }) exportExcelData({ menuIds: menuIds }).then(response => { var fileName = 'download.zip' const contentDisposition = response.headers['content-disposition'] if (contentDisposition) { fileName = window.decodeURI(response.headers['content-disposition'].split('=')[1], 'UTF-8') } const blob = new Blob([response.data], { type: `application/zip` // word文档为msword,pdf文档为pdf }) const objectUrl = URL.createObjectURL(blob) const link = document.createElement('a') link.href = objectUrl link.setAttribute('download', fileName) document.body.appendChild(link) link.click() // 释放内存 window.URL.revokeObjectURL(link.href) Message({ message: '导出成功', type: 'success', duration: 5 * 1000 }) loading.close() this.$emit('update:visible', false) this.getList() }).catch(response => { loading.close() }) } }, closePage() { this.$emit('update:visible', false) this.getList() } } } </script>
代码拆解：
通过前端传来的menuIds进行遍历，把每一条数据插到excel里面并且通过menuIds找到文件名与之对应的ktr文件放到文件夹中。
for (Long menuId : menuIds) { Conversion conversion = conversionMapper.selectById(menuId); if (conversion != null) { conversions.add(conversion); String ktrFilePath = fileService.getKtrFilePathById(menuId); if (ktrFilePath != null && !ktrFilePaths.contains(ktrFilePath)) { ktrFilePaths.add(ktrFilePath); } } }
创建excel导出模板：
// 创建Excel工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表 Sheet sheet = workbook.createSheet("Conversions"); // 创建单元格样式，并设置为文本格式 CellStyle textStyle = workbook.createCellStyle(); DataFormat format = workbook.createDataFormat(); textStyle.setDataFormat(format.getFormat("@")); // "@" 表示文本格式 // 创建标题行 Row titleRow = sheet.createRow(0); // 创建单元格样式，并设置为文本格式 titleRow.createCell(0).setCellValue("主键"); titleRow.createCell(1).setCellValue("分组id"); titleRow.createCell(2).setCellValue("名称"); titleRow.createCell(3).setCellValue("备注"); titleRow.createCell(4).setCellValue("创建人"); titleRow.createCell(5).setCellValue("关联状态"); titleRow.createCell(6).setCellValue("XMl"); titleRow.createCell(7).setCellValue("创建时间"); // 应用文本格式到标题行的特定单元格 titleRow.getCell(0).setCellStyle(textStyle); titleRow.getCell(1).setCellStyle(textStyle); // 填充数据 int rowNum = 1; for (Conversion conversion : conversions) { Row row = sheet.createRow(rowNum++); Cell cell = row.createCell(0); cell.setCellValue(String.valueOf(conversion.getId())); cell.setCellStyle(textStyle); cell = row.createCell(1); cell.setCellValue(String.valueOf(conversion.getGroupId())); cell.setCellStyle(textStyle); // 应用文本格式 row.createCell(2).setCellValue(conversion.getName()); row.createCell(3).setCellValue(conversion.getRemark()); row.createCell(4).setCellValue(conversion.getCreateUserName()); row.createCell(5).setCellValue(conversion.getState()); row.createCell(6).setCellValue(conversion.getEditXml()); row.createCell(7).setCellValue(String.valueOf(conversion.getCreateTime())); }
我这里的id和groupId位数特别长，所以对这两列做了默认为文本的处理，否则会变成科学计数法，会丢精。
具体如下：
// 创建单元格样式，并设置为文本格式 CellStyle textStyle = workbook.createCellStyle(); DataFormat format = workbook.createDataFormat(); textStyle.setDataFormat(format.getFormat("@")); // "@" 表示文本格式 // 应用文本格式到标题行的特定单元格 titleRow.getCell(0).setCellStyle(textStyle); titleRow.getCell(1).setCellStyle(textStyle); Row row = sheet.createRow(rowNum++); Cell cell = row.createCell(0); cell.setCellValue(String.valueOf(conversion.getId())); cell.setCellStyle(textStyle); cell = row.createCell(1); cell.setCellValue(String.valueOf(conversion.getGroupId())); cell.setCellStyle(textStyle); // 应用文本格式
把excel文件添加到压缩包：
// 将Excel文件添加到压缩包 ZipEntry excelEntry = new ZipEntry("conversions.xls"); zipOut.putNextEntry(excelEntry); workbook.write(zipOut); zipOut.closeEntry(); workbook.close();
把ktr文件放到ktr命名的文件夹中，并关闭压缩文件流
// 添加.ktr文件到ktr目录 for (String filePath : ktrFilePaths) { File ktrFile = new File(filePath); if (ktrFile.exists()) { FileInputStream fis = new FileInputStream(ktrFile); // 创建ZipEntry时，需要包含ktr目录 ZipEntry ktrEntry = new ZipEntry("ktr/" + ktrFile.getName()); zipOut.putNextEntry(ktrEntry); byte[] bytes = new byte[1024]; int length; while ((length = fis.read(bytes)) >= 0) { zipOut.write(bytes, 0, length); } fis.close(); zipOut.closeEntry(); } } // 完成压缩包 zipOut.finish(); zipOut.close();
导出就完成了。
导入
后端
导入的时候有一个要求，就是把导出时的id作为老的id存到数据库里，并生成新的id把新的id作为对应ktr的文件名存到对应的路径下面
解析数据：
@PostMapping("/insertData") public ResultData insertData(@RequestAttribute Long _userId, HttpServletRequest request) { MultipartHttpServletRequest req = (MultipartHttpServletRequest) request; MultipartFile uploadFile = req.getFile("uploadfile_ant"); String originalName = uploadFile.getOriginalFilename(); String docPath = ""; List<Long> codes = new ArrayList<>(); // 用于存储所有导入的 ID try { String classpath = ResourceUtils.getURL("classpath:").getPath(); String path = classpath + File.separator + "static" + File.separator + "file" + File.separator + "yulan"; docPath = path + File.separator + originalName; File dir = new File(path); if (!dir.exists()) { dir.mkdirs(); } // 保存压缩文件 File zipFile = new File(docPath); FileCopyUtils.copy(uploadFile.getInputStream(), new FileOutputStream(zipFile)); // 解压压缩文件 File unzipDir = new File(zipFile.getPath().substring(0, zipFile.getPath().lastIndexOf(".zip"))); if (!unzipDir.exists()) { unzipDir.mkdirs(); } unzipFile(zipFile, unzipDir); // 处理解压后的文件 processUnzippedFiles(unzipDir); return ResultData.success("ok", codes); // 返回所有导入的 ID 列表 } catch (Exception e) { e.printStackTrace(); return ResultData.error("error"); } }
解压代码：
private void unzipFile(File zipFile, File unzipDir) throws IOException { try (ZipInputStream zipIn = new ZipInputStream(new FileInputStream(zipFile))) { ZipEntry entry = zipIn.getNextEntry(); while (entry != null) { String filePath = unzipDir.getPath() + File.separator + entry.getName(); if (!entry.isDirectory()) { extractFile(zipIn, filePath); } else { File dir = new File(filePath); dir.mkdirs(); } zipIn.closeEntry(); entry = zipIn.getNextEntry(); } } }
private void extractFile(ZipInputStream zipIn, String filePath) throws IOException { new File(filePath).getParentFile().mkdirs(); try (BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(filePath))) { byte[] bytesIn = new byte[4096]; int read = 0; while ((read = zipIn.read(bytesIn)) != -1) { bos.write(bytesIn, 0, read); } } }
根据不同的文件类型去做不同的处理：
private void processUnzippedFiles(File unzipDir) throws Exception { // 遍历解压后的目录，处理每个文件 Files.walk(unzipDir.toPath()) .forEach(filePath -> { try { if (Files.isRegularFile(filePath) && filePath.toString().endsWith(".xls")) { //如果是excel文件 processExcelFile(filePath, unzipDir.toPath()); // } else if (Files.isDirectory(filePath) && "ktr".equals(filePath.getFileName().toString())) { // //ktr文件夹 // processKtrDirectory(filePath); } } catch (Exception e) { e.printStackTrace(); } }); }
处理excel：
@Transactional public void processExcelFile(Path filePath, Path zipPath) throws Exception { Workbook workbook = null; try { FileInputStream excelFile = new FileInputStream(filePath.toFile()); workbook = WorkbookFactory.create(excelFile); // 支持多种格式 // 假设我们只处理第一个工作表 Sheet sheet = workbook.getSheetAt(0); // 跳过标题行 int startRowIndex = 1; DataFormatter formatter = new DataFormatter(); for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row != null) { // 假设Excel文件的列顺序和数据库字段对应 Cell idCell = row.getCell(0); Cell groupIdCell = row.getCell(1); Cell NameCell = row.getCell(2); Cell remarkCell = row.getCell(3); Cell creatorCell = row.getCell(4); Cell xmlCell = row.getCell(6); // 检查空值和数据转换 String setOldId = formatter.formatCellValue(row.getCell(0)); String groupId = formatter.formatCellValue(row.getCell(1)); String remark = (remarkCell != null) ? remarkCell.getStringCellValue() : null; String Name = (NameCell != null) ? NameCell.getStringCellValue() : null; String creator = (creatorCell != null) ? creatorCell.getStringCellValue() :null; String state = formatter.formatCellValue(row.getCell(5)); String XML = (xmlCell != null) ? xmlCell.getStringCellValue() : null; // 创建一个数据对象，例如DataObject，并填充字段 Conversion conversion = new Conversion(); conversion.setId(SnowflakeIdGenerator.getId()); conversion.setOldId(Long.parseLong(setOldId)); conversion.setGroupId(Long.parseLong(groupId)); conversion.setName(Name); conversion.setRemark(remark); conversion.setCreateUserId(creator); conversion.setState(Integer.parseInt(state)); conversion.setEditXml(XML); conversion.setCreateTime(LocalDateTime.now()); // 保存到数据库 conversionMapper.insert(conversion); //ktr文件夹 processKtrDirectory(zipPath, conversion.getId(), conversion.getOldId()); } } } catch (Exception e) { throw new Exception("Error processing Excel file", e); } finally { if (workbook != null) { try { workbook.close(); } catch (IOException e) { // Log and handle workbook close exception } } } }
处理ktr：
private void processKtrDirectory(Path ktrDir, Long newId, Long oldId) throws Exception { // 处理ktr文件夹，将文件保存到磁盘路径下的逻辑 // 例如： String targetPath = ktrPath + File.separator + Constant.kettleScriptFileName + File.separator + Constant.ktrFileName + File.separator; String newPath = ktrDir.toString()+"/ktr"; try { Files.copy(Paths.get(newPath + File.separator + oldId.toString()), Paths.get(targetPath + newId.toString())); } catch (IOException e) { e.printStackTrace(); } }
用copy（source,target）就可以实现把文件保存到指定路径
fileService.getKtrFilePathById:
/** * 根据menuId获取对应的.ktr文件路径。 * @return 文件路径 */ @Service @Transactional public class FileServiceImpl implements FileService { @Value("${ktr.path}") private String ktrPath; public String getKtrFilePathById(Long menuId) { // 假设.ktr文件存储在 "/path/to/ktr/files/" 目录下，文件名为 "menuId.ktr" String baseDir = ktrPath + File.separator + Constant.kettleScriptFileName + File.separator + Constant.ktrFileName+File.separator; File file = new File(baseDir + menuId); if (file.exists()) { return file.getAbsolutePath(); } else { return null; // 或者抛出一个异常，表示文件不存在 } } }
前端：
<template> <div class="app-container" style="margin: 0 auto;width:100%;"> <el-form ref="form" label-width="80px" label-position="left"> <!-- <el-form-item> <div slot="label">分组<font color="red">*</font></div> <el-select v-model="form.groupId" placeholder="请选择分组" style="width: 100%"> <el-option v-for="item in fzList" :key="item.id" :label="item.name" :value="item.id" /> </el-select> </el-form-item> --> <!-- <el-form-item> <div slot="label">模型名称<font color="red">*</font></div> <el-input v-model="form.name" style="width:100%;" :autosize="{ minRows: 2, maxRows: 2}" /> </el-form-item> --> <el-form-item> <div slot="label">导入模型<font color="red">*</font></div> <el-upload accept=".zip" ref="upload" name="uploadfile_ant" class="upload-demo" :limit="1" :action="uploadpath" :headers="uoloadheaders" :before-upload="beforeAvatarUpload" :on-success="handleAvatarSuccess" :on-change="handleChange" :on-remove="handleRemove" :on-exceed="handleExceed" :file-list="fileList" > <el-button size="small" icon="el-icon-upload" type="primary">选择模型文件</el-button> <span style="color:red;"> 上传文件大小不能超过100MB</span> </el-upload> </el-form-item> <!-- <el-form-item label="备注:"> <el-input v-model="form.remark" type="textarea" maxlength="200" rows="6" placeholder="备注" /> </el-form-item> --> </el-form> <!-- <div style="text-align: center;width:100%;"> <el-button type="primary" @click="onSave">保存</el-button> <el-button type="danger" @click="closePage">取消</el-button> </div> --> </div> </template> <script> import { getWorkList } from '@/api/dataSchema' import { updateData } from '@/api/conversion' import { Message, MessageBox } from 'element-ui' import tool from '@/utils/tool' export default { name: 'Zzjg', inject: ['getList'], props: { proid: { type: String, required: true } }, data() { return { uploadpath: '', uoloadheaders: {}, fileData: '', // 文件上传数据（多文件合一） fileList: [], // upload多文件数组 fzList: [], form: {}, code: '' } }, methods: { getDetailed() { getWorkList().then(response => { this.fzList = response.data let address = process.env.NODE_ENV == 'development' ? process.env.VUE_APP_URL_RECON : process.env.VUE_APP_BASE_API; var path = '/ltcloud/conversion/insertData' this.uploadpath = address + path this.uoloadheaders = { 'X-TOKEN' : tool.getCookie('X-Token'), 'client-url':location.href, 'applicationId':this.applicationId } }) }, handleAvatarSuccess(res, file) { if (res.code === 20000) { this.code = res.data Message({ message: '上传成功', type: 'success', duration: 5 * 1000 }) } else { Message({ message: res.msg, type: 'error', duration: 5 * 1000 }) } }, // 移除 handleRemove(file, fileList) { this.fileList = fileList }, beforeAvatarUpload(file) { const isLt2M = file.size / 1024 / 1024 < 100 if (!isLt2M) { this.$message.error('上传文件大小不能超过100MB!') } return isLt2M }, // 选取文件超过数量提示 handleExceed(files, fileList) { this.$message.warning(`当前限制选择 1 个文件，本次选择了 ${files.length} 个文件，共选择了 ${files.length + fileList.length} 个文件`) }, // 监控上传文件列表 handleChange(file, fileList) { const existFile = fileList.slice(0, fileList.length - 1).find(f => f.name === file.name) if (existFile) { this.$message.error('当前文件已经存在!') fileList.pop() } this.fileList = fileList }, onSave() { console.log('分组不能为空') if (!this.form.groupId) { this.$message.error('分组不能为空') return } else if (!this.form.name) { this.$message.error('模型名称不能为空') return } else if (this.fileList.length === 0) { this.$message.error('导入模型不能为空') return } else { const loading = this.$loading({ lock: true, text: 'Loading', spinner: 'el-icon-loading', background: 'rgba(0, 0, 0, 0.7)' }) this.form.idList = this.code updateData(this.form).then(response => { Message({ message: '编辑成功', type: 'success', duration: 5 * 1000 }) loading.close() this.$emit('update:visible', false) this.getList() }).catch(response => { loading.close() this.getList() }) } }, closePage() { this.$emit('update:visible', false) this.getList() } } } </script> <style lang="less"> /deep/ .el-dialog { width: 550px; height: 650px; } .displayCol { display: flex; } .newNum { height: 20px; width: 20px; border: 1px solid #333; border-radius: 50%; text-align: center; margin-top: 3px; line-height: 20px; } /deep/.el-form-item__label { text-align: left !important; padding: 0 10px; } .disabled-text { pointer-events: none; /* 阻止鼠标事件 */ cursor: default; /* 将鼠标光标设置为默认样式，表明文本不可点击 */ opacity: 0.5; /* 降低文本的不透明度以显示出它是不可交互的 */ user-select: none; /* 禁止文本被选中 */ } .el-upload-list { float: left; margin: 0; padding: 0; list-style: none; } .el-upload { margin-left: 0px; display: inline-block; text-align: center; cursor: pointer; outline: 0; } .el-upload__tip { font-size: 12px; color: #606266; margin-top: 7px; width: 300px; line-height: 45px; height: 10px; } </style>
