universitymanage/server/parseexcel.js

const xml2js = require('xml2js');
const parser = new xml2js.Parser();
const fs = require('fs');
const moment = require('moment');
const os = require('os');
const path = require('path');
var q = require('qiao-zip');
const uuidv4 = require('uuid');

const sheetlist=async(xlsx)=>{
    const cacheDir = path.join(os.homedir(), '.cache');
    const filedir=cacheDir+'\\learn\\'+uuidv4.v4();
    fs.mkdirSync(filedir);
    await q.unzip(xlsx, filedir)
    const xmlwb=fs.readFileSync(filedir+"/[Content_Types].xml",'utf8');
    const wbresult=await new Promise((resolve,reject)=>{
        parser.parseString(xmlwb, (err, result) => {
            if (err) {
                console.error(err);
                throw err;
            }
            resolve(result);
        });
    })
    //console.log(wbresult);
    let slist=[];
    let wblist=await wblsit(filedir+"/xl/sharedStrings.xml")

    wbresult.Types.Override.map((value,index)=>{
        //console.log(value);
        if(value['$'].ContentType==="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"){
           slist.push(value['$'].PartName);
        }

    })
    let chengji=[];
    //console.log(slist);
    let chengname=[];
    for(let i=0;i<slist.length;i++){

        const biao=await wjb(filedir+slist[i],wblist);
        //
        let xiangnum=-1;//姓名项在第几列
            for(let j=0;j<biao.length;j++){
                if(biao[j].includes("姓名")){
                    xiangnum=j;
                    break;
                }
            }
            let nameid=biao[xiangnum].indexOf("姓名");
            let xuehaoid=biao[xiangnum].indexOf("学号");
            let sex=biao[xiangnum].indexOf("性别");
            let classid=biao[xiangnum].indexOf("班级");
            let xueyaunid=biao[xiangnum].indexOf("学院");
            let idcardid=biao[xiangnum].indexOf("身份证号");
            let ruxuetime=biao[xiangnum].indexOf("入学日期")
            let iphone=biao[xiangnum].indexOf("学生手机号");
            let parentname=biao[xiangnum].indexOf("家长姓名")
            let parentphone=biao[xiangnum].indexOf("家长手机号")
       //console.log(nameid)
          //  console.log(xiangnum);
        //console.log(biao);
        const stulsit=[];
        for(let j=xiangnum+1;j<biao.length;j++){
            let ruxue=""    ;
            if( /^\d+$/.test(biao[j][ruxuetime])){
                if (parseInt(biao[j][ruxuetime])<80000){
                    ruxue=moment("1900-01-01").add(parseInt(biao[j][ruxuetime])-2,'day').format();
                    //console.log(mom);
                }
            }else{
                ruxue=moment(biao[j][ruxuetime]).format();
            }
            const stu={
                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],
                choose:true
            }
           // console.log(stu);
            stulsit.push(stu);
        }
        chengji.push({stu:stulsit,name:path.parse(filedir+slist[i]).name,add:false});
        chengname.push(path.parse(filedir+slist[i]).name);
    }
   //
  //  console.log(chengname);
   // console.log(chengji);
    return chengji;
}
const wblsit=async (file)=>{
    const xmlwb=fs.readFileSync(file,'utf8');
   // console.log(xmlwb);
    const wbresult=await new Promise((resolve,reject)=>{
        parser.parseString(xmlwb, (err, result) => {
            if (err) {
                console.error(err);
                throw err;
            }
            resolve(result);
        });
    })
    let wbone=[];
    wbresult.sst.si.map((value)=>{
        //console.log(value);
        wbone.push(value.t[0]);
    })
    //console.log(wbone);
    return wbone;
}
const wjb=async(file,list)=>{
    const xmlwb=fs.readFileSync(file,'utf8');
    const wbresult=await new Promise((resolve,reject)=>{
        parser.parseString(xmlwb, (err, result) => {
            if (err) {
                console.error(err);
                throw err;
            }
            resolve(result);
        });
    })
    const excerow=wbresult.worksheet.sheetData[0].row;
    let allexcel=[]

    wbresult.worksheet.sheetData[0].row.map((value,index)=>{
        let hangexcel=[];
        value.c.map((value,index)=>{
            //console.log(value);
            const t=value['$'].t;
            const v=value.v[0];
            let zhi="";
          //  if (parseInt(v)<40000&&parseInt(v)>80000) {
                if (t === undefined) {
                    zhi = v;
                } else if (t === 's') {
                    zhi = list[parseInt(v)];
                } else {
                    zhi = v;
                }
         //   }else{
           //     zhi =moment("1900-01-01").add(parseInt(v),'days').format('YYYY-MM-DD');
            //}

            hangexcel.push(zhi);

        })
        allexcel.push(hangexcel);
    });
 // console.log(allexcel);
    return allexcel;
}
//wblsit("C:/Users/l/Desktop/a1/xl/sharedStrings.xml").then(t=>{
  //  wjb("C:/Users/l/Desktop/a1/xl/worksheets/sheet1.xml",t)
//})
//sheetlist("C:\\Users\\l\\Desktop\\12\\a141.xlsx")
const cacheDir = path.join(os.homedir(), '.cache');
console.log(cacheDir);
exports.sheetlist=sheetlist;