ssti-CollegeManage/src/components/tool/getExcel.js

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();
                console.log(x.response);
                reader.readAsArrayBuffer(x.response)          //读取返回回来的文件内容
                reader.onload = function(){                    //当读取操作成功完成时调用
                    console.log(reader);
                    resolve(reader.result)                       //reader.result返回文件内容
                }
            }
        }
    })
}




export default async function getExcel(data){     //async 申明 function 是异步的，而 await 等待某个操作完成。
    // console.log(data);
    const content = await getBuffer();       //等待getBuffer函数执行完成
    const workbook = new Excel.Workbook();
    await workbook.xlsx.load(content);      //从 buffer 加载
    //获取第一个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")*1+worksheet.getCell("C8")*1+worksheet.getCell("C9")*1+worksheet.getCell("C10")*1+worksheet.getCell("C11")*1}
        //已支付
        worksheet.getCell("D6").value = {formula:"SUM(D7:D11)",result:worksheet.getCell("D7")*1+worksheet.getCell("D8")*1+worksheet.getCell("D9")*1+worksheet.getCell("D10")*1+worksheet.getCell("D11")*1}
        //本次申请
        worksheet.getCell("E6").value = {formula:"SUM(E7:E11)",result:worksheet.getCell("E7")*1+worksheet.getCell("E8")*1+worksheet.getCell("E9")*1+worksheet.getCell("E10")*1+worksheet.getCell("E11")*1}
        //余额
        worksheet.getCell("F6").value = {formula:"C6-D6-E6",result:worksheet.getCell("C6")*1-worksheet.getCell("D6")*1-worksheet.getCell("E6")*1}
    //间接经费
        //项目成果鉴定费余额
        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")*1+worksheet.getCell("C14")*1+worksheet.getCell("C15")*1+worksheet.getCell("C16")*1+worksheet.getCell("C17")*1}
        //已支付
        worksheet.getCell("D12").value = {formula:"SUM(D13:D17)",result:worksheet.getCell("D13")*1+worksheet.getCell("D14")*1+worksheet.getCell("D15")*1+worksheet.getCell("D16")*1+worksheet.getCell("D17")*1}
        //本次申请
        worksheet.getCell("E12").value = {formula:"SUM(E13:E17)",result:worksheet.getCell("E13")*1+worksheet.getCell("E14")*1+worksheet.getCell("E15")*1+worksheet.getCell("E16")*1+worksheet.getCell("E17")*1}
        //余额
        worksheet.getCell("F12").value = {formula:"C12-D12-E12",result:worksheet.getCell("C12")*1-worksheet.getCell("D12")*1-worksheet.getCell("E12")*1}
    //经费支出
        //总预算
        worksheet.getCell("C5").value = {formula:"SUM(C6+C12)",result:worksheet.getCell("C6")*1+worksheet.getCell("C12")*1}
        //总支出
        worksheet.getCell("D5").value=  {formula:"SUM(D6+D12)",result:worksheet.getCell("D6")*1+worksheet.getCell("D12")*1}
        //总申请
        worksheet.getCell("E5").value = {formula:"SUM(E6+E12)",result:worksheet.getCell("E6")*1+worksheet.getCell("E12")*1}
        //总余额
        worksheet.getCell("F5").value = {formula:"SUM(F6+F12)",result:worksheet.getCell("F6")*1+worksheet.getCell("F12")*1}




    //下载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);   // 字符内容转变成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);
}