vue中使用exceljs组件库导入导出json到excel
逻辑：
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) {
// 创建一个Promise
return 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-01
const 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);
},