import Excel from 'exceljs' let getBuffer = function(){ return new Promise((resolve,reject)=>{ const x = new XMLHttpRequest(); x.open("GET", 'file/附件 5 ：学校创客专项资金使用申请表.xlsx', true); x.responseType = "blob"; x.send() x.onreadystatechange = function(){ if(x.readyState==4 && x.status==200){ let reader = new FileReader(); reader.readAsArrayBuffer(x.response) reader.onload = function(){ resolve(reader.result) } } } }) } export default async function getExcel(data){ console.log(data); const content = await getBuffer(); const workbook = new Excel.Workbook(); await workbook.xlsx.load(content); //获取第一个worksheet let worksheet = workbook.getWorksheet(1); // 在这里设置表格的内容 //项目类型 // worksheet.getCell("G2").value='项目类型：创客空间 □ 创客实践室 □ 个人创客 □ 创客活动 □ ' //项目名称 worksheet.getCell('G3').value=`项目名称： ${data.PName}`; //备注 worksheet.getCell("G18").value=data.remarks; // let direct = 7; //直接经费预算 从C7====>C11 for(let i in data.direct.budget){ switch(i){ case "facility": if(data.direct.budget[i]!=0)worksheet.getCell("C7").value=data.direct.budget[i]; break; case "cl": if(data.direct.budget[i]!=0)worksheet.getCell("C8").value=data.direct.budget[i]; break; case "jg": if(data.direct.budget[i]!=0)worksheet.getCell("C9").value=data.direct.budget[i]; break; case "xz": if(data.direct.budget[i]!=0)worksheet.getCell("C10").value=data.direct.budget[i]; break; case "qt": if(data.direct.budget[i]!=0)worksheet.getCell("C11").value=data.direct.budget[i]; break; } } //直接经费已支付D7====>D11 for(let i in data.direct.expended){ switch(i){ case "facility": if(data.direct.expended[i]!=0)worksheet.getCell("D7").value=data.direct.expended[i]; break; case "cl": if(data.direct.expended[i]!=0)worksheet.getCell("D8").value=data.direct.expended[i]; break; case "jg": if(data.direct.expended[i]!=0)worksheet.getCell("D9").value=data.direct.expended[i]; break; case "xz": if(data.direct.expended[i]!=0)worksheet.getCell("D10").value=data.direct.expended[i]; break; case "qt": if(data.direct.expended[i]!=0)worksheet.getCell("D11").value=data.direct.expended[i]; break; } } //直接经费本次申请E7====>E11 for(let i in data.direct.apply){ switch(i){ case "facility": if(data.direct.apply[i]!=0)worksheet.getCell("E7").value=data.direct.apply[i]; break; case "cl": if(data.direct.apply[i]!=0)worksheet.getCell("E8").value=data.direct.apply[i]; break; case "jg": if(data.direct.apply[i]!=0)worksheet.getCell("E9").value=data.direct.apply[i]; break; case "xz": if(data.direct.apply[i]!=0)worksheet.getCell("E10").value=data.direct.apply[i]; break; case "qt": if(data.direct.apply[i]!=0)worksheet.getCell("E11").value=data.direct.apply[i]; break; } } //直接经费事由G7====>G11 for(let i in data.direct.reason){ switch(i){ case "reason": worksheet.getCell("G7").value=data.direct.reason[i]; break; case "reason1": worksheet.getCell("G8").value=data.direct.reason[i]; break; case "reason2": worksheet.getCell("G9").value=data.direct.reason[i]; break; case "reason3": worksheet.getCell("G10").value=data.direct.reason[i]; break; case "reason4": worksheet.getCell("G11").value=data.direct.reason[i]; break; } } //间接经费预算C13====>C17 for(let i in data.indirect.budget){ switch(i){ case "facility": if(data.indirect.budget[i]!=0)worksheet.getCell("C13").value=data.indirect.budget[i]; break; case "cl": if(data.indirect.budget[i]!=0)worksheet.getCell("C14").value=data.indirect.budget[i]; break; case "jg": if(data.indirect.budget[i]!=0)worksheet.getCell("C15").value=data.indirect.budget[i]; break; case "xz": if(data.indirect.budget[i]!=0)worksheet.getCell("C16").value=data.indirect.budget[i]; break; case "qt": if(data.indirect.budget[i]!=0)worksheet.getCell("C17").value=data.indirect.budget[i]; break; } } //间接经费已支付D13====>D17 for(let i in data.indirect.expended){ switch(i){ case "facility": if(data.indirect.expended[i]!=0)worksheet.getCell("D13").value=data.indirect.expended[i]; break; case "cl": if(data.indirect.expended[i]!=0)worksheet.getCell("D14").value=data.indirect.expended[i]; break; case "jg": if(data.indirect.expended[i]!=0)worksheet.getCell("D15").value=data.indirect.expended[i]; break; case "xz": if(data.indirect.expended[i]!=0)worksheet.getCell("D16").value=data.indirect.expended[i]; break; case "qt": if(data.indirect.expended[i]!=0)worksheet.getCell("D17").value=data.indirect.expended[i]; break; } } //间接经费本次申请E13====>E17 for(let i in data.indirect.apply){ switch(i){ case "facility": if(data.indirect.apply[i]!=0)worksheet.getCell("E13").value=data.indirect.apply[i]; break; case "cl": if(data.indirect.apply[i]!=0)worksheet.getCell("E14").value=data.indirect.apply[i]; break; case "jg": if(data.indirect.apply[i]!=0)worksheet.getCell("E15").value=data.indirect.apply[i]; break; case "xz": if(data.indirect.apply[i]!=0)worksheet.getCell("E16").value=data.indirect.apply[i]; break; case "qt": if(data.indirect.apply[i]!=0)worksheet.getCell("E17").value=data.indirect.apply[i]; break; } } //间接经费本次申请G13====>G17 for(let i in data.indirect.reason){ switch(i){ case "reason": worksheet.getCell("G13").value=data.indirect.reason[i]; break; case "reason1": worksheet.getCell("G14").value=data.indirect.reason[i]; break; case "reason2": worksheet.getCell("G15").value=data.indirect.reason[i]; break; case "reason3": worksheet.getCell("G16").value=data.indirect.reason[i]; break; case "reason4": worksheet.getCell("G17").value=data.indirect.reason[i]; break; } } //自动计算的单元格 //直接经费 //小型仪器设备费余额 worksheet.getCell("F7").value = {formula:"C7-D7-E7",result:worksheet.getCell("C7")-worksheet.getCell("D7")-worksheet.getCell("E7")} //材料费余额 worksheet.getCell("F8").value = {formula:"C8-D8-E8",result:worksheet.getCell("C8")-worksheet.getCell("D8")-worksheet.getCell("E8")} //测试化验加工费余额 worksheet.getCell("F9").value = {formula:"C9-D9-E9",result:worksheet.getCell("C9")-worksheet.getCell("D9")-worksheet.getCell("E9")} //项目协作费余额 worksheet.getCell("F10").value = {formula:"C10-D10-E10",result:worksheet.getCell("C10")-worksheet.getCell("D10")-worksheet.getCell("E10")} //其他支出余额 worksheet.getCell("F11").value = {formula:"C11-D11-E11",result:worksheet.getCell("C11")-worksheet.getCell("D11")-worksheet.getCell("E11")} //总预算 worksheet.getCell("C6").value = {formula:"SUM(C7:C11)",result:worksheet.getCell("C7")+worksheet.getCell("C8")+worksheet.getCell("C9")+worksheet.getCell("C10")+worksheet.getCell("C11")} //已支付 worksheet.getCell("D6").value = {formula:"SUM(D7:D11)",result:worksheet.getCell("D7")+worksheet.getCell("D8")+worksheet.getCell("D9")+worksheet.getCell("D10")+worksheet.getCell("D11")} //本次申请 worksheet.getCell("E6").value = {formula:"SUM(E7:E11)",result:worksheet.getCell("E7")+worksheet.getCell("E8")+worksheet.getCell("E9")+worksheet.getCell("E10")+worksheet.getCell("E11")} //余额 worksheet.getCell("F6").value = {formula:"C6-D6-E6",result:worksheet.getCell("C6")-worksheet.getCell("D6")-worksheet.getCell("E6")} //间接经费 //项目成果鉴定费余额 worksheet.getCell("F13").value = {formula:"C13-D13-E13",result:worksheet.getCell("C13")-worksheet.getCell("D13")-worksheet.getCell("E13")} //参展参赛费余额 worksheet.getCell("F14").value = {formula:"C14-D14-E14",result:worksheet.getCell("C14")-worksheet.getCell("D14")-worksheet.getCell("E14")} //创客交流活动费余额 worksheet.getCell("F15").value = {formula:"C15-D15-E15",result:worksheet.getCell("C15")-worksheet.getCell("D15")-worksheet.getCell("E15")} //知识产权事务费余额 worksheet.getCell("F16").value = {formula:"C16-D16-E16",result:worksheet.getCell("C16")-worksheet.getCell("D16")-worksheet.getCell("E16")} //其他支出余额 worksheet.getCell("F17").value = {formula:"C17-D17-E17",result:worksheet.getCell("C17")-worksheet.getCell("D17")-worksheet.getCell("E17")} //总预算 worksheet.getCell("C12").value = {formula:"SUM(C13:C17)",result:worksheet.getCell("C13")+worksheet.getCell("C14")+worksheet.getCell("C15")+worksheet.getCell("C16")+worksheet.getCell("C17")} //已支付 worksheet.getCell("D12").value = {formula:"SUM(D13:D17)",result:worksheet.getCell("D13")+worksheet.getCell("D14")+worksheet.getCell("D15")+worksheet.getCell("D16")+worksheet.getCell("D17")} //本次申请 worksheet.getCell("E12").value = {formula:"SUM(E13:E17)",result:worksheet.getCell("E13")+worksheet.getCell("E14")+worksheet.getCell("E15")+worksheet.getCell("E16")+worksheet.getCell("E17")} //余额 worksheet.getCell("F12").value = {formula:"C12-D12-E12",result:worksheet.getCell("C12")-worksheet.getCell("D12")-worksheet.getCell("E12")} //下载excel文件 let blob = new Blob([await workbook.xlsx.writeBuffer()], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"}); let url = window.URL.createObjectURL(blob); const link = document.createElement('a'); link.style.display = 'none'; link.href = url; link.setAttribute('download', '学校创客专项资金使用申请表.xlsx'); document.body.appendChild(link); link.click(); document.body.removeChild(link); }