xexcel

#!/usr/bin/env node
const Excel = require('exceljs');
const fs = require('fs-extra');
const path = require('path');
const _ = require('lodash');
const moment = require('moment');
const os = require('os');
const utils = require('./js/utils');
const program = require('./js/dialog').commander();
const logFile = path.join(os.homedir(), '.xn_history', '.xn_excel_history');

function getValues (values) {
    return _.map(values, (o, index) => {
        if (o && o.richText) {
            return _.map(o.richText, m => m.text).join('');
        }
        if (o && o.result) {
            return o.result;
        }
        if (o && o.error) {
            return undefined;
        }
        return o;
    }).map(o => o===undefined ? '' : o);
}
function writeFile(filename, line) {
    fs.appendFileSync(filename, line+'\n');
}
async function parseSingleExcelFile(srcFile, distFile) {
    const wb = new Excel.Workbook();
    console.log(`开始分析 ${srcFile}.xlsx`);;
    await wb.xlsx.readFile(srcFile);
    wb.eachSheet(ws => {
        console.log(`开始分析 ${ws.name}`);
        writeFile(distFile, `## ${ws.name}`);
        ws.eachRow(row=>{
            const values = getValues(row.values.slice(1));
            writeFile(distFile, `| ${values.join(' | ')} |`);
            rowNo === 1 && writeFile(distFile, `| ${values.map(o=>':-:').join(' | ')} |`);
        });
        writeFile(distFile, '');
        writeFile(distFile, '');
    });
}
function readDir (dir, distDir) {
    fs.readdirSync(dir).forEach(async(file, index)=>{
        const srcFile = path.join(dir, file);
        const info = fs.statSync(srcFile);
        if(info.isDirectory()) {
            readDir(srcFile, path.join(distDir, file));
        } else if (!/^~/.test(file) && /\.xlsx$/.test(file)) {
            const distFile = path.join(distDir, file.replace( /\.xlsx$/, '.md'));
            fs.ensureFileSync(distFile);
            await parseSingleExcelFile(srcFile, distFile);
        }
    });
}
function addHistory(line) {
    const list = fs.existsSync(logFile) ? fs.readFileSync(logFile, 'utf-8').split(/\n/) : [];
    !list.includes(line) && list.push(line);
    fs.writeFileSync(logFile, list.join('\n'), 'utf-8');
}
function parseItem(title, value) {
    while (_.first(value) === '(' && _.last(value) === ')') {
        value = value.substr(1, value.length-2);
    }
    value = value.trim();
    if (title) {
        return { title, value };
    }
    if (value === '*') {
        return { title: value, value };
    }
    const match = value.match(/^#(\d+)$/);
    if (match) {
        return { title: value, value: `&${match[1]}` };
    }
    return { value };
}
function parseLine(line) {
    const list = [];
    let i = -1, max = line.length - 1, item = '', title = '', amatch = 0, bmatch = 0;
    while (i++ < max) {
        const ch = line[i];
        if (ch === '(') {
            amatch++
        } else if (ch === ')') {
            amatch--
        } else if (ch === '{') {
            bmatch++
        } else if (ch === '}') {
            bmatch--
        }
        if ((ch === ':'||ch === '：') && amatch === 0 && bmatch === 0) {
            title = item;
            item='';
        } else if ((ch === ','||ch === '，') && amatch === 0 && bmatch === 0) {
            list.push(parseItem(title, item));
            item='';
            title='';
        } else {
            item=`${item}${ch}`;
        }
    }
    if (amatch === 0 && bmatch === 0) { // 如果括号不匹配，则丢弃
        item && list.push(parseItem(title, item));
    }
    return list;
}
async function fillNewValues({match, value, item, row, info, isCal}) {
    const total = match.length;
    const nvs = [];
    for (const m of match) {
        nvs.push({ nv: row[m.substr(1)-1], m });
    }
    await utils.until(()=>_.every(nvs, o=>o.nv.finish), 100);
    for (const n of nvs) {
         value = value.replace(n.m, isCal && _.isString(n.nv.value) ? `'${n.nv.value}'` : n.nv.value);
    }
    if (isCal) {
        const ret = utils.eval(value);
        value = ret.error || ret.value;
    }
    info.count++;
    item.value = value;
    item.finish = true;
}
function formatRow(info, {hasTitle, list, i, oldRow, oldIndexes, newIndexes, oldTitles, newTitles, oldValues, newValues}) {
    const row = [];
    for (const index in newTitles) {
        const title = newTitles[index];
        let value;
        const item = hasTitle ? _.find(list, o => o.title == title) : list[index];
        if (item) {
            value = item.value;
            if (!value) {
                info.count++;
                row.push({ value, finish: true });
                continue;
            }
            const isCal = /[+\-*/]+|=>/.test(value);
            let match = value.match(/&\d+/g);
            if (match) {
                match = _.orderBy(match, o=>o, 'desc');
                for (const m of match) {
                    const nv =  oldRow[m.substr(1)];
                    value = value.replace(m, isCal && _.isString(nv) ? `'${nv}'` : nv);
                }
                if (isCal) {
                    const ret = utils.eval(value);
                    value = ret.error || ret.value;
                }
                info.count++;
                row.push({ value, finish: true });
                continue;
            }
            match = value.match(/&/g);
            if (match) {
                for (const m of match) {
                    const nv =  oldRow[+index+1];
                    value = value.replace(m, isCal && _.isString(nv) ? `'${nv}'` : nv);
                }
                if (isCal) {
                    const ret = utils.eval(value);
                    value = ret.error || ret.value;
                }
                info.count++;
                row.push({ value, finish: true });
                continue;
            }
            if (/=>/.test(value)) {
                const ret = utils.eval(value);
                if (ret.error) {
                    throw(ret.error);
                }
                if (_.isFunction(ret.value)) {
                    const params = { _, moment, utils, i, or: oldRow, nr: row, ov: oldValues, nv: newValues, oi: oldIndexes, ni: newIndexes, ot: oldTitles, nt: newTitles };
                    if (ret.value[Symbol.toStringTag] === 'AsyncFunction') {
                        ret.value(params).then(v=>{
                            info.count++;
                            row.push({ value: v, finish: true });
                        });
                    } else {
                        value = ret.value(params);
                        info.count++;
                        row.push({ value, finish: true });
                    }
                    continue;
                }
            }
            match = value.match(/@\d+/g);
            if (match) {
                match = _.orderBy(match, o=>o, 'desc');
                const item = {};
                row.push(item);
                fillNewValues({match, value, item, row, info, isCal});
                continue;
            }
            info.count++;
            row.push({ value, finish: true });
            continue;
        }
        info.count++;
        row.push(oldRow[oldIndexes[title]]);
    }
    return row;
}
async function genNewExcel(srcFile, distFile, line = '', sheetIndex = 0) {
    if (!/\.xlsx$/.test(srcFile)) {
        srcFile = `${srcFile}.xlsx`;
    }
    if (!/\.xlsx$/.test(distFile)) {
        distFile = `${distFile}.xlsx`;
    }
    const oldValues = []; // 原表的值
    const newValues = []; // 新表的值
    let oldTitles = []; // 新表的值
    let newTitles = []; // 新表的标题栏
    let oldIndexes = {}; // 标题栏对应的index
    let newIndexes = {}; // 标题栏对应的index
    const list = parseLine(line);
    const hasTitle = _.every(list, o=>o.title);
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(srcFile);
    const ws = wb.worksheets[sheetIndex];
    const actualRowCount= ws.actualRowCount;
    const actualColumnCount= ws.actualColumnCount;
    let rowNo = 1, cnt = 0;
    while (1) {
        const values = ws.getRow(rowNo).values;
        if (!values.length) {
            oldValues.push(_.map(Array(actualColumnCount+1), o=>''));
        } else {
            oldValues.push(getValues(values));
            cnt++;
            if (cnt >= actualRowCount) {
                break;
            }
        }
        rowNo++;
    }
    const startIndex = _.findIndex(oldValues, o=>_.some(o));
    if (hasTitle) {
        oldTitles = oldValues[startIndex];
        const allIndex = _.findIndex(list, o=>o.title==='*');
        if (allIndex > -1) {
            list.splice(allIndex, 1, ..._.map(oldTitles.slice(1), (o, k)=>({title: o, value: `&${k+1}`})));
        }
        for (const item of list) {
            const match = item.title.match(/^#(\d+)$/);
            if (match) {
                item.title = oldTitles[match[1]];
            }
            newTitles.push(item.title);
        }
        oldIndexes = _.reduce(oldTitles, (r, n, k)=>(r[n]=k,r), {});
        newIndexes = _.reduce(['', ...newTitles], (r, n, k)=>(r[n]=k,r), {});
    } else {
        oldTitles = _.keys(oldValues[startIndex]);
        const allIndex = _.findIndex(list, o=>o.title==='*');
        if (allIndex > -1) {
            list.splice(allIndex, 1, ..._.map(oldTitles.slice(1), (o, k)=>({value: `&${k+1}`})));
        }
        for (const item of list) {
            const match = item.title && item.title.match(/^#(\d+)$/);
            if (match) {
                item.title = oldTitles[match[1]];
            }
            newTitles.push(item.title);
        }
        oldIndexes = _.reduce(oldValues[startIndex], (r, n, k)=>(r[k]=k,r), {});
        newIndexes = _.reduce(['', ...newTitles], (r, n, k)=>(r[k]=k,r), {});
    }
    hasTitle && (oldValues[startIndex] = _.map(oldValues[startIndex], o=>''));
    console.log('genNewExcel: params=', {hasTitle, list, oldIndexes, newIndexes, newTitles, oldValues, newValues});
    const info = { total: newTitles.length * oldValues.length, count: 0 };
    for (const i in oldValues) {
        let row = [];
        if (i < startIndex || (i == startIndex && hasTitle)) {
            if (i == startIndex && hasTitle) {
                row = _.map(newTitles, o=>({value:o, finish: true}));
            } else {
                row = _.map(newTitles, ()=>({value:'', finish: true}));
            }
            newValues.push(row);
            info.count += newTitles.length;
            continue;
        }
        if (_.every(oldValues[i], o=>o==='')) {
            newValues.push(_.map(newTitles, ()=>({value:'', finish: true})));
            info.count += newTitles.length;
            continue;
        }
        const params = { hasTitle, list, i, oldRow: oldValues[i], oldIndexes, newIndexes, oldTitles, newTitles, oldValues, oldValues, newValues };
        row = formatRow(info, params);
        newValues.push(row);
    }
    await utils.until(()=>info.total === info.count, 500);
    console.log('genNewExcel: newValues=', newValues);
    console.log('genNewExcel: info=', info);

    const wwb = new Excel.Workbook();
    const wws = wwb.addWorksheet(ws.name);
    for (const obj of newValues) {
        wws.addRow(_.map(obj, o=>o.value));
    }
    await wwb.xlsx.writeFile(distFile);
    addHistory(line);
}
// const line = `&5+123,@1+123,*,('1,2,3'),()=>{return 1}`;
// const line = `&5+123,@1+123,#1,#2,('1,2,3'),()=>{return 1}`;
// const line = `标题1:&5+123,标题2：@1+123,*，标题3：(1,2,3)，标题4：()=>{return 1}`;
// const line = `标题1:&5+123,标题2：@1+123,#1,#4，标题3：(1,2,3)，标题4：()=>{return 1}`;
// { _, moment, utils, i, or: oldRow, nr: row, ov: oldValues, nv: newValues, oi: oldIndexes, ni: newIndexes, ot: oldTitles, nt: newTitles }
// const line = `标题1:&5+123, 标题2：@1+123,#1,#4，标题3：(1,2,3)，标题4：({_, moment, utils, i, or, nr, oi, ni, t, ov})=>{console.log(ov[i-1]);return ov[i-1]&&ov[i-1][4]}`;
// const line = `标题1:&5+123, 标题2：@1+123,#1,#4，标题3：(1,2,3)，标题4：async ({utils, i})=>{const r=await utils.httpPost('http://localhost:4000/test', {a:i}); return r.a}`;
function showUpdateHelp() {
    console.log('生成新的excel规则如下：');
    console.log('    #1 原来的标题1，&1 原来的value1, & 原来的value, @1 现在的value1，各项之间用逗号分(,|，)分开，如果需要使用括号控制');
    console.log(`    &5+123,@1+123,*,('1,2,3'),()=>{return 1}`);
    console.log(`    &5+123,@1+123,#1,#2,('1,2,3'),()=>{return 1}`);
    console.log(`    标题1:&5+123,标题2：@1+123,*，标题3：(1,2,3)，标题4：()=>{return 1}`);
    console.log(`    标题1:&5+123,标题2：@1+123,#1,#4，标题3：(1,2,3)，标题4：()=>{return 1}`);
    console.log(`    标题1:&5+123, #1,#4，标题2：({ov})=>{console.log(ov[i-1]);return ov[i-1]&&ov[i-1][4]}`);
    console.log(`    标题：async ({utils, i})=>{const r=await utils.httpPost('http://localhost:4000/test', {a:i}); return r.a}`);
    console.log('函数的参数如下：');
    console.log(`    { _, moment, utils, i, or: oldRow, nr: row, ov: oldValues, nv: newValues, oi: oldIndexes, ni: newIndexes, ot: oldTitles, nt: newTitles }`);
}
async function parseExcelForScript(srcFile, distFile, sheetIndex = 0) {
    if (!/\.xlsx$/.test(srcFile)) {
        srcFile = `${srcFile}.xlsx`;
    }
    if (!/\.js$/.test(distFile)) {
        distFile = `${distFile}.js`;
    }
    const rows = await getExcelData(srcFile, sheetIndex);
    const func = utils.loadModule(distFile);
    func(rows, { _, moment, utils });
}
function showScriptHelp() {
    console.log('script的例子如下：');
    console.log(`module.exports = function(rows) {`);
    console.log(`   console.log(rows[0], rows[1]);`);
    console.log(`}`);
}
async function getExcelData(srcFile, sheetIndex = 0) {
    if (!/\.xlsx$/.test(srcFile)) {
        srcFile = `${srcFile}.xlsx`;
    }
    const rows = [];
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(srcFile);
    const ws = wb.worksheets[sheetIndex];
     ws.eachRow(row=>{
        const values = getValues(row.values.slice(1));
        rows.push(values);
    });
    return rows;
}
function getConcatTableValues(nrow, table, joinTitles, row, oindexes) {
    const list = _.map(joinTitles, o=>({index: table.indexes[o], value: row[oindexes[o]]}));
    const srow = _.find(table.data, r=>_.every(list, o=>r[o.index]===o.value));
    for (const i in srow) {
        if (_.findIndex(list, o=>o.index==i)==-1) {
            nrow.push(srow[i]);
        }
    }
}
async function joinExcels(files, joinTitles) {
    let distFile = _.last(files);
    const srcFiles = files.slice(0, -1);
    let origin;
    const joins = [];
    for (const file of srcFiles) {
        const list = file.split('#');
        const rows = await getExcelData(list[0], list[1]);
        const db = { titles: rows[0], data: rows.slice(1), indexes: _.reduce(rows[0], (r, n, k)=>(r[n]=k,r), {}) };
        if (_.some(joinTitles, o=>db.titles.indexOf(o)===-1)) {
            return `${file}没有关联字段`; 
        }
        if (!origin) {
            origin = db;
        } else {
            joins.push(db);
        }
    }

    const titles = [...origin.titles];
    for (const table of joins) {
        const list = _.map(joinTitles, o=>table.indexes[o]);
        for (const i in table.titles) {
            if (_.findIndex(list, o=>o==i)===-1) {
                titles.push(table.titles[i]);
            }
        }
    }
    const rows = [];
    for (const row of origin.data) {
        const nrow = [...row];
        for (const table of joins) {
            getConcatTableValues(nrow, table, joinTitles, row, origin.indexes);
        }
        rows.push(nrow);
    }
    console.log('joinExcels titles=', titles);
    console.log('joinExcels datas=', rows[0]);

    if (!/\.xlsx$/.test(distFile)) {
        distFile = `${distFile}.xlsx`;
    }
    const wwb = new Excel.Workbook();
    const wws = wwb.addWorksheet('汇总');
    wws.columns = _.map(titles, o=>({ header: o }));
    wws.addRows(rows);
    await wwb.xlsx.writeFile(distFile);
}
function main() {
    program
    .version('0.0.1')
    .option('-m, --markdown src[.]:dist[.]', '目标目录:源目录')
    .option('-s, --script [src#0:script]', '解析excel，每行执行js的script文件，script第一个参数为rows的数组， 第二个为 { _, moment, utils }')
    .option('-u, --update [src#0:dist:line]', '生成新的文件，后缀名.xlsx可以省略')
    .option('-j, --join [src1#0,src2,...,dist:title1,title2,...]', '通过字段join几个excel,#0为sheetIndex,默认为0')
    .option('-l, --history', '查看update的历史纪录')
    .parse(process.argv);

    let { markdown, script, update, join, history } = program;
    if (markdown !== undefined) {
        const [src, dist] = !markdown.split ? [] : markdown.split(':');
        return readDir(src || '.', dist || '.');
    }
    if (script !== undefined) {
        if (!script.split) {
            return showScriptHelp()
        }
        const [src, dist] = script.split(':');
        const list = src.split('#');
        return parseExcelForScript(list[0], dist, list[1]);
    }
    if (update !== undefined) {
        if (!update.split) {
            return showUpdateHelp()
        }
        const [src, dist, ...line] = update.split(':');
        const list = src.split('#');
        return genNewExcel(list[0], dist, line.join(':'), list[1]);
    }
    if (join !== undefined) {
        if (!join.split) {
            return console.log('参数错误');
        }
        let [files, joinTitles] = join.split(':');
        files = files.split(/,|，/);
        joinTitles = joinTitles.split(/,|，/);
        if (files.length < 3) {
            return console.log('源文件必须两个以上');
        }
        if (!joinTitles.length) {
            return console.log('至少有一个主键');
        }
        return joinExcels(files, joinTitles);
    }
    if (history) {
        return console.log(fs.readFileSync(logFile, 'utf-8'));
    }
}

main();