实现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-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>