universitymanage/server/makeexcel.js

const fs = require('fs');
const path = require('path');
const uuidv4 = require('uuid');
var q = require('qiao-zip');
const os = require("os");
   /*
    name:biao[j][nameid],
    xuehao:biao[j][xuehaoid],
    sex:biao[j][sex],
    classid:biao[j][classid],
    xueyuan:biao[j][xueyaunid],
    idcard:biao[j][idcardid],
    ruxuetime:ruxue,
    iphone:biao[j][iphone],
    parentphone:biao[j][parentphone],
    parentname:biao[j][parentname],
    */
function nametop(){

}
async function makeExcel(exceljson) {

    let wblist=[]//excel文本列表
    wblist.push("姓名");
    wblist.push("学号")
    wblist.push("性别")
    wblist.push("专业");
    wblist.push("学院")
    wblist.push("身份证号")
    wblist.push("入学时间")
    wblist.push("手机号")
    wblist.push("默认密码")
    let listexcel=""
    //console.log(exceljson);
    exceljson.map((value,index) => {
        let nameindex = wblist.indexOf(value.name);

        if (nameindex == -1) {
            wblist.push(value.name);
            nameindex = wblist.indexOf(value.name);
        }
        let namexml = `<c r="A${index + 2}" t="s"><v>${nameindex}</v></c> `;
        let xuehaoindex
            = wblist.indexOf(value.xuehao);
        if (xuehaoindex === -1) {
            wblist.push(value.xuehao);
            xuehaoindex = wblist.indexOf(value.xuehao);
        }
        let xuehaoxml = `<c r="B${index + 2}" t="s"><v>${xuehaoindex}</v></c> `
        let sexindex = wblist.indexOf(value.sex);
        if (sexindex === -1) {
            wblist.push(value.sex)
            sexindex = wblist.indexOf(value.sex)
        }
        let sexxml = `<c r="C${index + 2}" t="s"><v>${sexindex}</v></c>`
        let classindex = wblist.indexOf(value.classid);
        if (classindex === -1) {
            wblist.push(value.classid);
            classindex = wblist.indexOf(value.classid);
        }
        let classxml = `<c r="D${index + 2}" t="s"><v>${classindex}</v></c>`
        let xueyuanindex = wblist.indexOf(value.xueyuan);
        if (xueyuanindex === -1) {
            wblist.push(value.xueyuan);
            xueyuanindex = wblist.indexOf(value.xueyuan);
        }
        let xueyuanxml = `<c r="E${index + 2}" t="s"><v>${xueyuanindex}</v></c>`
        let idcardindex = wblist.indexOf(value.idcard);
        if (idcardindex === -1) {
            wblist.push(value.idcard);
            idcardindex = wblist.indexOf(value.idcard);
        }
        let idcardxml = `<c r="F${index + 2}" t="s"><v>${idcardindex}</v></c>`
        let ruxuetimeindex = wblist.indexOf(value.ruxuetime);
        if (ruxuetimeindex === -1) {
            wblist.push(value.ruxuetime);
            ruxuetimeindex = wblist.indexOf(value.ruxuetime);
        }
        let ruxuetimexml = `<c r="G${index + 2}" t="s"><v>${ruxuetimeindex}</v></c>`
        let iphoneindex = wblist.indexOf(value.iphone);
        if (iphoneindex === -1) {
            wblist.push(value.iphone);
            iphoneindex = wblist.indexOf(value.iphone);
        }
        let iphonexml = `<c r="H${index + 2}" t="s"><v>${iphoneindex}</v></c>`
        let passwordindex = wblist.indexOf(value.password);
        console.log(value.password)
        if (passwordindex === -1) {
            wblist.push(value.password);
            passwordindex = wblist.indexOf(value.password);
        }
        let passwordxml = `<c r="I${index + 2}" t="s"><v>${passwordindex}</v></c>`
        const xmlall=namexml+xuehaoxml+sexxml+classxml+xueyuanxml+idcardxml+ruxuetimexml+iphonexml+passwordxml;
           const row=`<row r = "${index+2}" spans = "1:9" >${xmlall}</row> `
        listexcel+=row;
    })
    let moban=fs.readFileSync("sheet2.xml","utf8")
   let shuzu= moban.split("shuliang");
    moban=shuzu[0]+`I${exceljson.length+1}`+shuzu[1];
    shuzu=moban.split("charuwengjian");
    moban=shuzu[0]+listexcel+shuzu[1];
    let stringlist="";
    wblist.map((value,index)=>{
        const wbxml=`<si><t>${value}</t></si>`
        stringlist+=wbxml;
    })
   let wbxml=fs.readFileSync("sharedStrings.xml","utf8")
    let wbshuzu=wbxml.split("wblist");
    wbxml=wbshuzu[0]+stringlist+wbshuzu[1];
    const cacheDir = path.join(os.homedir(), '.cache');
    const filedir=cacheDir+'\\learn\\'+uuidv4.v4();
    console.log(filedir)
    fs.mkdirSync(filedir);
    await q.unzip("scexcelstu.zip", filedir)
    fs.writeFileSync(`${filedir}\\xl\\sharedStrings.xml`,wbxml)
    fs.writeFileSync(`${filedir}\\xl\\worksheets\\sheet1.xml`,moban)
    const uuidv41=uuidv4.v4();
    const dir=`C:\\Users\\l\\WebstormProjects\\examp\\serve\\nginx\\html\\stuexcel\\${uuidv41}.zip`;

    await q.zip(filedir, dir,false)
    fs.copyFileSync(dir,`C:\\Users\\l\\WebstormProjects\\examp\\serve\\nginx\\html\\stuexcel\\${uuidv41}.xlsx`)
    return(`http://localhost:89/stuexcel/${uuidv41}.xlsx`);
}
exports.makeExcel=makeExcel;