实现Excel文件和其他文件导出为压缩包，并导入

导出

后端：

	@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-treeref="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.dataloading.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 = objectUrllink.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<>(); // 用于存储所有导入的 IDtry {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：

@Transactionalpublic 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-uploadaccept=".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.datalet 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 + paththis.uoloadheaders = {'X-TOKEN' : tool.getCookie('X-Token'),'client-url':location.href,'applicationId':this.applicationId}})},handleAvatarSuccess(res, file) {if (res.code === 20000) {this.code = res.dataMessage({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 < 100if (!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.codeupdateData(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>