import ExcelJS from "exceljs";
配上文档地址
下载方法
//数据格式
deviceJson:{
't(数据类型)':[{value:0,label:'浮点'},{value:1,label:'整型'},{value:2,label:'布尔'},{value:3,label:'字符串'}],
'o(读写方向)':[{value:0,label:'只读'},{value:1,label:'只写'},{value:2,label:'读写'}],
'oc_sav(断线缓存)':[{value:0,label:'否'},{value:1,label:'是'}],
}
let columnsFormulae = {
'o(读写方向)':['只读','只写','读写'],
}
const ExcelJSWb = new ExcelJS.Workbook();//创建实例
const Sheet1 = ExcelJSWb.addWorksheet("Sheet1");//工作簿名称
let columns = []//表头集合
let columnsFormulae = []//下拉框数据集合
//tagList 导出数据集合
//deviceJson 下拉框数据匹配集合
let keys = Object.keys(tagList[0])
for (const key in tagList[0]) {
if (Object.hasOwnProperty.call(tagList[0], key)) {
const element = tagList[0][key];
columns.push({
header:key,
key:key,
width: 10,
index:keys.indexOf(key)//记录表头下标 下拉数据使用
})
this.deviceJson[key] ? columnsFormulae[key] = this.deviceJson[key].map((item)=> item.label) : null
}
}
Sheet1.columns = columns//导出表 赋值表头
Sheet1.addRows(tagList);// 添加导出表体
tagList.forEach((tag, idx) => {// 遍历表体 赋值 下拉框数据
const row = idx + 2;
columns.map((colu,num)=>{
if (columnsFormulae.hasOwnProperty(colu.key)){
let str = `"`+columnsFormulae[colu.key].join()+`"`
Sheet1.getCell(row, num+1).dataValidation = {
type: "list",
formulae: [str]
};
}
})
});
writeBuffer 把写好的excel 转换成 ArrayBuffer 类型
const buffer = await ExcelJSWb.xlsx.writeBuffer();
_this.saveAs(
new Blob([buffer], {
type: "application/octet-stream",
}),
"deviceTag" +
"." +
(wopts.bookType == "biff2" ? "xls" : wopts.bookType)
);
saveAs(obj, fileName) {
//当然可以自定义简单的下载文件实现方式
let tmpa = document.createElement("a");
tmpa.download = fileName || "下载";
tmpa.href = URL.createObjectURL(obj); //绑定a标签
tmpa.click(); //模拟点击实现下载
setTimeout(function () {
//延时释放
URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
}, 100);
}
导入方法如下 input change事件
<inputtype="file" style="display: none" ref="input_file" accept=".xlsx" @change="importF"/>
importF(evt) {
let _this = this;
let rABS = false;
let obj = evt.target;
let arr = evt.target.files[0].name.split(".");
let len = evt.target.files[0].name.split(".").length;
let typeName = arr[len - 1];
if (typeName != "xlsx") {
this.$message({
type: "warning",
message: "请上传.xlsx类型文件",
});
return;
}
if (!obj.files) {
return;
}
let f = obj.files[0];
let reader = new FileReader();
reader.onload = function (e) {
let data = e.target.result;
let wb;
if (rABS) {
wb = XLSX.read(data, { type: "binary" });
} else {
let arr = _this.fixdata(data);
wb = XLSX.read(btoa(arr), { type: "base64" });
}
//deviceTagList 导入数据集合
let deviceTagList = XLSX.utils.sheet_to_json(
wb.Sheets[wb.SheetNames[0]]
);
if (rABS) reader.readAsBinaryString(f);
else reader.readAsArrayBuffer(f);
},
