逻辑：

1、json数据导出到excel

2、json数据从excel导入

3、导入导出前的验证

4、excel时间转换，

Vue模板代码：

<el-form :inline="true" label-width="120px"><el-form-item><el-button type="primary" @click="downloadExcelTemplate">下载模板</el-button></el-form-item><el-form-item><el-upload action="" accept=".xlsx" :auto-upload="false" :show-file-list="false" :limit="1"ref="upload" :before-upload="beforeExcelUpload" :on-change="handleExcelChange":data="{ directoryName: 'xlsx', sheetName: '设备能耗统计时间段' }"><el-button type="primary">导入</el-button></el-upload></el-form-item></el-form>

Vue，methods代码

downloadExcelTemplate () {if (this.subitemData.length == 0) return;let jsonData = [];let dropdownOptions = [];for (const subitem of this.subitemData) {dropdownOptions.push({ 设备名称: subitem.name, });}for (const subitem of this.subitemData) {let obj = {设备名称: '',生产开始时间: '',生产结束时间: '',}jsonData.push(obj);}const workbook = new ExcelJS.Workbook();const worksheet = workbook.addWorksheet('数据录入');const worksheet2 = workbook.addWorksheet('机列');worksheet2.state = 'hidden';// 定义边框样式const borderStyle = {top: { style: 'thin' },left: { style: 'thin' },bottom: { style: 'thin' },right: { style: 'thin' }};// 添加表头（假设所有对象有相同的键）const headers = Object.keys(jsonData[0]);worksheet.addRow(headers);worksheet2.addRow(Object.keys(dropdownOptions[0]));worksheet.getRow(1).eachCell((cell) => {cell.font = { bold: true };cell.border = borderStyle;});// 设置列宽headers.forEach((header, index) => {worksheet.getColumn(index + 1).width = 20; // 根据需要调整宽度});// 设置表头样式worksheet.getRow(1).eachCell((cell) => {cell.font = { bold: true };});// 使用 addRows 方法一次性添加所有数据行worksheet2.addRows(dropdownOptions.map(item => Object.values(item)));jsonData.forEach(item => {const row = worksheet.addRow(Object.values(item));row.eachCell(cell => {cell.border = borderStyle;});});// 设置数据验证以创建下拉列表worksheet.getColumn(1).eachCell((cell, rowNumber) => {if (rowNumber > 1) { // 跳过表头行cell.dataValidation = {type: 'list',formulae: [`机列!$A$2:$A${dropdownOptions.length + 1}`],allowBlank: true,showDropDown: true,};}});// 保存文件workbook.xlsx.writeBuffer().then((data) => {const blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });saveAs(blob, 'exported_data.xlsx');});},beforeExcelUpload (file) {// 创建一个Promisereturn new Promise((resolve, reject) => {const isExcel = /\.(xlsx)$/.test(file.name);if (!isExcel) {this.$message.error('只能上传.xlsx 文件!');reject();}toast.confirm({message: '上传后将重置本页面数据',confirm: () => {resolve(); // 允许上传},cancel: () => {toast.error("用户取消上传")reject(); // 不允许上传}})});},handleExcelChange (file) {const fileblob = file.raw;const reader = new FileReader();reader.onload = async (e) => {const data = new Uint8Array(e.target.result);const workbook = new ExcelJS.Workbook();try {await workbook.xlsx.load(data);const worksheet = workbook.getWorksheet('数据录入');const jsonData = [];// 获取表头作为键名const headers = worksheet.getRow(1).values.slice(1); // 忽略第一项（即行号）// 遍历所有行并构建 JSON 对象worksheet.eachRow((row, rowNumber) => {if (rowNumber === 1) return; // 跳过表头行const item = {};row.eachCell((cell, colNumber) => {if (colNumber > 0) { // 忽略第一项（即行号）item[headers[colNumber - 1]] = cell.text || cell.value;}});jsonData.push(item);});console.log('Imported JSON Data:', jsonData); // 这里可以替换为实际处理逻辑this.handleExcelData(jsonData)this.$refs.upload.clearFiles(); // 清空文件列表} catch (error) {console.error('读取文件失败:', error);}};reader.readAsArrayBuffer(fileblob);},handleExcelData (excelJson) {if (this.subitemData.length == 0) return;let inputDataList = [];for (const element of excelJson) {let subitemId = 0;let subitemName = element.设备名称;let inputTimeStart = element.生产开始时间;let inputDateTimeEnd = element.生产结束时间;let dateTimeRange = [];if (isEmpty(subitemName)) {toast.error("设备名称不能为空，模板已破坏，请重新下载模板");continue;}if (isEmpty(inputTimeStart) || isEmpty(inputDateTimeEnd)) {inputTimeStart = inputDateTimeEnd = null;}else {inputTimeStart = this.convertExcelDate(inputTimeStart).setSeconds(0, 0);inputDateTimeEnd = this.convertExcelDate(inputDateTimeEnd).setSeconds(0, 0);dateTimeRange = [inputTimeStart, inputDateTimeEnd];}let subitem = this.subitemData.find(s => s.name == subitemName);if (isEmpty(subitem)) continue;let inputData = {stationId: this.stationId,classificationSubitemId: subitem.id,classificationSubitemName: subitem.name,collectTime: inputTimeStart && moment(inputTimeStart).format('YYYY-MM-DD 00:00:00'),startTime: inputTimeStart && moment(inputTimeStart).format('YYYY-MM-DD HH:mm:ss'),endTime: inputDateTimeEnd && moment(inputDateTimeEnd).format('YYYY-MM-DD HH:mm:ss'),dateTimeRange: dateTimeRange,ref: `DateTime-${(Math.random() * 1000).toFixed(0)}`,key: `key-${(Math.random() * 1000).toFixed(0)}`,subitemData: [...this.subitemData]};for (let j = 0; j < inputData.subitemData.length; j++) {let subitem = inputData.subitemData[j]subitem.key = 'key-' + subitem.id + '-' + (Math.random() * 1000).toFixed(0)}inputDataList.push(inputData);};if (inputDataList.length > 0) {this.adddialog.addingData = inputDataList;}},convertExcelDate (serialNumber) {const epochStart = new Date(1900, 0, 1); // 1900-01-01const offsetMilliseconds = 5 * 60 * 1000 + 43 * 1000; // 323000 毫秒，历史原因需要减去 5 分钟和 43 秒epochStart.setTime(epochStart.getTime() + offsetMilliseconds);const daysSinceEpoch = serialNumber - 2; // 减去两天是因为 Excel 认为 1900 是闰年const date = new Date(epochStart.getTime() + daysSinceEpoch * 24 * 60 * 60 * 1000);return date;//return moment(date);},