excel2x/dist/ExcelUtil.js

"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
var xlsx = require("xlsx");
var MathUtil_1 = require("./MathUtil");
var Config_1 = require("./Config");
var StringUtil_1 = require("./StringUtil");
var ExcelUtil = /** @class */ (function () {
    function ExcelUtil() {
    }
    /**
     * 倒序查找一个字符串种字母第一次出现的位置
     * @param str
     */
    ExcelUtil.getLastCharIndex = function (str) {
        var len = str.length;
        if (!len || len < 1) {
            return -1;
        }
        for (var i = len - 1; i >= 0; --i) {
            var ch = str[i];
            if ((ch >= 'a' && ch <= 'z') || (ch >= 'A' && ch <= 'Z')) {
                return i;
            }
        }
        return -1;
    };
    /**
     * 根据一个地址获取行号
     * @param address
     */
    ExcelUtil.getCellRowNum = function (address) {
        return parseInt(address.substring(this.getLastCharIndex(address) + 1), 10);
    };
    ExcelUtil.getCellColName = function (address) {
        return address.substring(0, this.getLastCharIndex(address) + 1);
    };
    /**
     * 读取表格自定义表头信息
     * @param workbookArg
     * @param sheetname
     */
    ExcelUtil.getExcelInfo = function (excelPath) {
        var _a;
        var workbook = xlsx.readFile(excelPath, { cellStyles: true, bookFiles: true });
        var sheetname = workbook.SheetNames[0];
        var worksheet = workbook.Sheets[sheetname];
        var ret = {
            keys: [],
            excelPath: excelPath,
            sheetname: sheetname,
            worksheet: [],
            cols: [],
            minRow: 3,
            maxRow: 2,
        };
        var _loop_1 = function (address) {
            if (address[0] === '!') {
                return "continue";
            }
            // jump the value type of e and z (type: b Boolean, e Error, n Number, d Date, s Text, z Stub)
            if (worksheet[address].v == undefined ||
                worksheet[address].t == 'e' ||
                worksheet[address].t == 'z') {
                return "continue";
            }
            ret.worksheet[address] = worksheet[address];
            var row = this_1.getCellRowNum(address);
            var col = this_1.getCellColName(address);
            // console.log('col', col, address, )
            var name_1 = worksheet[address].v;
            if ((name_1 == undefined || name_1 == null) && row == 1) {
                Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, ret), " \n\u7F3A\u5931\u8868\u5934\u540D\u5B57!,\u662F\u4E0D\u662F\u63D2\u5165\u4E86\u7A7A\u5217\uFF1F"));
            }
            if (ret.cols.findIndex(function (v) { return v == col; }) == -1 && worksheet[col + '1'] && worksheet[col + '1'].v) {
                ret.cols.push(col);
            }
            if (ret.cols.length > 0 && ret.maxRow < row && name_1 != undefined) {
                ret.maxRow = row;
            }
            if (row == 1) {
                // 读取表头参数
                var ls = name_1.indexOf('[');
                var le = name_1.indexOf(']');
                var outputclient = false;
                var outputserver = false;
                var type = '';
                var isUniq = false; // 重复的行
                var isArray = false; // 是否是数组
                var canZeroStr = false; // 是否允许字符串数据为'0'
                if (ls != -1 && le != -1 && le > ls + 1) {
                    var s = name_1.substring(ls + 1, le);
                    name_1 = name_1.substring(0, ls);
                    for (var i = 0; i < s.length; i++) {
                        if (s[i] == 'c') {
                            outputclient = true;
                        }
                        else if (s[i] == 's') {
                            outputserver = true;
                        }
                        else if (s[i] == 'n') {
                            type = 'number';
                        }
                        else if (s[i] == 't') {
                            type = 'string';
                        }
                        else if (s[i] == 'u') { // uniq
                            isUniq = true;
                        }
                        else if (s[i] == 'a') { // array
                            isArray = true;
                        }
                        else if (s[i] == 'k') { // key
                            Config_1.Config.objectKey = name_1;
                        }
                        else if (s[i] == 'z') {
                            canZeroStr = true;
                        }
                        else {
                            Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, ret), " \n\u610F\u5916\u7684\u8868\u5934\u5B9A\u4E49\uFF01").concat(s));
                        }
                    }
                }
                if (ret.keys.findIndex(function (v) { return v == name_1; }) != -1) {
                    Config_1.Config.checkState(Config_1.RetCode.COL_NAME_REPEAT, "".concat(StringUtil_1.default.getCheckString(address, ret), " \n\u540C\u4E00\u4E2A\u8868\u4E2D\u53D1\u73B0\u5217\u540D\u91CD\u590D\uFF01").concat(name_1));
                }
                ret.keys.push({
                    name: name_1,
                    outputclient: outputclient,
                    outputserver: outputserver,
                    type: type,
                    isUniq: isUniq,
                    isArray: isArray,
                    canZeroStr: canZeroStr,
                    des: (_a = worksheet[col + 2]) === null || _a === void 0 ? void 0 : _a.v
                });
                delete worksheet[address]; // 删除表头信息
            }
        };
        var this_1 = this;
        // console.log(excelPath, sheetname)
        for (var address in worksheet) {
            _loop_1(address);
        }
        return ret;
    };
    ExcelUtil.checkArr = function (value, address, headInfo) {
        var ret = {
            isOneArray: false,
            isTwoArray: false,
            isThreeArray: false,
        };
        if (MathUtil_1.default.isNumber(value)) {
            return ret;
        }
        if (value.indexOf(Config_1.Config.THREEE_ARRAY_SYMBOL) != -1) {
            ret.isThreeArray = true;
            // if (value.indexOf(Config.TWO_ARRAY_SYMBOL) != -1) {
            //     if (value.indexOf(Config.ONE_ARRAY_SYMBOL) != -1) {
            //         ret.isOneArray = true;
            //         ret.isTwoArray = true;
            //         ret.isThreeArray = true;
            //     } else {
            //         Config.checkState(RetCode.NOT_FOUND_ONE_ARRAY_SYMBOL, `${StringUtil.getCheckString(address, headInfo)} \n数组分割符使用有误， 发现了三维分割符以及二维分隔符，但是未发现一维分割符！`)
            //     }
            // } else {
            //     if (value.indexOf('color') == -1) {
            //         Config.checkState(RetCode.NOT_FOUND_TWO_ARRAY_SYMBOL, `${StringUtil.getCheckString(address, headInfo)} \n数组分割符使用有误， 发现了三维分割符，但是未发现二维分割符！`)
            //     }
            // }
        }
        if (value.indexOf(Config_1.Config.TWO_ARRAY_SYMBOL) != -1) {
            ret.isTwoArray = true;
            // if (value.indexOf(Config.ONE_ARRAY_SYMBOL) != -1) {
            //     ret.isOneArray = true;
            //     ret.isTwoArray = true;
            // } else {
            //     Config.checkState(RetCode.NOT_FOUND_TWO_ARRAY_SYMBOL, `${StringUtil.getCheckString(address, headInfo)} \n数组分割符使用有误， 发现了二维分割符，但是未发现一维分割符！`)
            // }
        }
        if (value.indexOf(Config_1.Config.ONE_ARRAY_SYMBOL) != -1) {
            ret.isOneArray = true;
        }
        return ret;
    };
    ExcelUtil.tryGetArray = function (value, isMult, isNumber, canZeroStr) {
        var ret = '';
        value = StringUtil_1.default.replaceAll(value, '<color=#', '<color=@');
        var threeArr = value.split(Config_1.Config.THREEE_ARRAY_SYMBOL);
        for (var threeIndex = 0; threeIndex < threeArr.length; threeIndex++) {
            var twoArr = threeArr[threeIndex].split(Config_1.Config.TWO_ARRAY_SYMBOL);
            if (isMult) {
                ret += '[';
            }
            for (var twoIndex = 0; twoIndex < twoArr.length; twoIndex++) {
                var oneArr = twoArr[twoIndex].split(Config_1.Config.ONE_ARRAY_SYMBOL);
                ret += '[';
                for (var oneIndex = 0; oneIndex < oneArr.length; oneIndex++) {
                    var oneUnit = oneArr[oneIndex];
                    if (MathUtil_1.default.isNumber(oneUnit) && isNumber) {
                        ret += oneUnit;
                    }
                    else {
                        if ((canZeroStr && oneUnit.toString() == '0') || oneUnit.toString() != '0') {
                            ret += "'".concat(oneUnit, "'"); // 支持字符串数组
                        }
                    }
                    if (oneIndex != oneArr.length - 1) {
                        ret += ', ';
                    }
                }
                ret += '], ';
            }
            if (ret.substr(ret.length - 2, 2) == ', ') {
                ret = ret.substr(0, ret.length - 2);
            }
            if (isMult) {
                ret += '], ';
            }
        }
        if (ret.substr(ret.length - 2, 2) == ', ') {
            ret = ret.substr(0, ret.length - 2);
        }
        if (threeArr.length > 1) {
            ret = "[".concat(ret, "]");
        }
        ret = StringUtil_1.default.replaceAll(ret, '<color=@', '<color=#');
        return ret;
    };
    ExcelUtil.getObj = function (values, isMultArr, isArray, headInfo, row) {
        var cobj = {};
        var sobj = {};
        for (var i = 0; i < values.length; i++) {
            var temp = values[i].toString();
            temp = StringUtil_1.default.replaceDoubleMid(temp, '$', function (mid) { return (parseInt(mid, 10) * Config_1.Config.yuanRate).toString(); }, true);
            temp = StringUtil_1.default.replaceDoubleMid(temp, '￥', function (mid) { return Config_1.Config.replaceStr ? Config_1.Config.replaceStr : mid; });
            if (headInfo.keys[i].outputserver) {
                sobj[headInfo.keys[i].name] = temp;
            }
            if (headInfo.keys[i].outputclient) {
                var address = headInfo.cols[i] + row;
                if (headInfo.keys[i].type == 'number' || headInfo.keys[i].type == 'number[]') {
                    if (headInfo.keys[i].isArray) {
                        headInfo.keys[i].type = 'number[]';
                        temp = this.tryGetArray(temp, isMultArr[i], true);
                        var regex = /^[0-9\[\],"\s\-.]+$/;
                        if (!regex.test(temp))
                            Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \n\u6570\u7EC4\u5185\u5BB9\u5B58\u5728\u5B57\u7B26\u4E32\uFF01"));
                    }
                    else {
                        temp = Number(temp);
                        if (isNaN(temp)) {
                            Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \n\u6570\u5B57\u5185\u5BB9\u5B58\u5728\u5B57\u7B26\u4E32\uFF01"));
                        }
                    }
                }
                else if (headInfo.keys[i].type == 'string' || headInfo.keys[i].type == 'string[]') {
                    if (isArray[i]) {
                        headInfo.keys[i].type = 'string[]';
                        temp = this.tryGetArray(temp, isMultArr[i], false, headInfo.keys[i].canZeroStr);
                    }
                    else {
                        if (!headInfo.keys[i].canZeroStr && temp == '0')
                            temp = '';
                    }
                }
                cobj[headInfo.keys[i].name] = temp;
            }
        }
        return {
            c: cobj,
            s: sobj,
        };
    };
    ExcelUtil.creatObj = function (headInfo) {
        var worksheet = headInfo.worksheet;
        var cobjlist = [];
        var sobjlist = [];
        var errorlist = [];
        for (var col = 0; col < headInfo.cols.length; col++) {
            var uniq = [];
            var colError = [];
            if (headInfo.keys[col].isUniq) {
                for (var row = headInfo.minRow; row <= headInfo.maxRow; row++) {
                    var address = headInfo.cols[col] + row;
                    if (!worksheet[address]) {
                        // console.log(address);
                        Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \u5355\u5143\u683C\u4E3A\u7A7A0\uFF01"));
                    }
                    if (worksheet[address].v == undefined) {
                        Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \u6570\u636E\u9519\u8BEF1\uFF01"));
                    }
                    var value = worksheet[address].v;
                    var index = MathUtil_1.default.findSame(uniq, value, 'value');
                    if (index == -1) {
                        uniq.push({ address: address, value: value });
                    }
                    else {
                        var first = uniq[index];
                        if (MathUtil_1.default.findSame(colError, first.address, 'address') == -1) {
                            colError.push(first);
                        }
                        if (MathUtil_1.default.findSame(colError, address, 'address') == -1) {
                            colError.push({ address: address, value: value });
                        }
                    }
                }
            }
            if (colError.length > 0) {
                errorlist.push({ name: headInfo.cols[col], list: colError });
            }
        }
        if (errorlist.length > 0) {
            var out_1 = '';
            errorlist.forEach(function (error) {
                out_1 += error.name + ' ';
                console.log("".concat(error.name, "\u884C"));
                error.list.forEach(function (unit) {
                    console.log(unit);
                });
            });
            Config_1.Config.checkState(Config_1.RetCode.SAME_COL_ROW_UNIQ_REPEAT, "".concat(StringUtil_1.default.getCheckString(out_1, headInfo), "\u540C\u4E00\u5217\u884C\u5185\u5BB9\u91CD\u590D\uFF01"));
        }
        // 遍历整张表，对每一列进行分析
        var isMultArr = [];
        var isArray = [];
        for (var col = 0; col < headInfo.cols.length; col++) {
            var arrayNum = 0;
            if (headInfo.keys[col].isArray) {
                var isOne = 1;
                var isTwo = 0;
                var isThree = 0;
                for (var row = headInfo.minRow; row <= headInfo.maxRow; row++) {
                    var address = headInfo.cols[col] + row;
                    if (!worksheet[address]) {
                        // console.log(address);
                        Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \u5355\u5143\u683C\u4E3A\u7A7A1\uFF01"));
                    }
                    if (worksheet[address].v == undefined) {
                        Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \u6570\u636E\u9519\u8BEF2\uFF01"));
                    }
                    var value = worksheet[address].v;
                    var result = this.checkArr(value, address, headInfo);
                    if (result.isOneArray) {
                        isOne++;
                    }
                    if (result.isTwoArray) {
                        isTwo++;
                    }
                    if (result.isThreeArray) {
                        isThree++;
                    }
                }
                isMultArr.push(isTwo > 0 || isThree > 0);
                isArray.push(isOne > 0);
                if (isOne > 0) {
                    arrayNum = 1;
                }
                if (isTwo > 0) {
                    arrayNum = 2;
                }
                if (isThree > 0) {
                    arrayNum = 3;
                }
            }
            else {
                isMultArr.push(false);
                isArray.push(false);
            }
            headInfo.keys[col].arrayNum = arrayNum;
        }
        // 初始化
        for (var row = headInfo.minRow; row <= headInfo.maxRow; row++) {
            var values = [];
            var address = '';
            // 获取一列的对象
            for (var col = 0; col < headInfo.cols.length; col++) {
                address = headInfo.cols[col] + row;
                if (worksheet[address] == undefined) {
                    if (headInfo.cols[col] == 'A') {
                        var lastAddress = headInfo.cols[headInfo.cols.length - 1] + row; // 获取最后一个的地址
                        if (worksheet[lastAddress] == undefined) {
                            if (row == 2) {
                                console.log("\u8B66\u544A\uFF1A".concat(StringUtil_1.default.getCheckString('行' + row, headInfo), " \u53EF\u80FD\u662F\u7A7A\u8868\uFF01"));
                                return {
                                    sheetname: headInfo.sheetname,
                                    jsobj: cobjlist,
                                    csvobj: sobjlist,
                                    headInfo: headInfo
                                };
                            }
                            else {
                                Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString('行' + row, headInfo), " \u53EF\u80FD\u5B58\u5728\u7A7A\u884C\uFF01\uFF0C\u8BF7\u68C0\u67E5"));
                            }
                        }
                        else {
                            Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(lastAddress, headInfo), " \u6570\u636E\u9519\u8BEF3\uFF01"));
                        }
                    }
                    Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \u6570\u636E\u9519\u8BEF4\uFF01"));
                }
                var value = worksheet[address].v;
                // jump the value type of e and z (type: b Boolean, e Error, n Number, d Date, s Text, z Stub)
                if (worksheet[address].t == 'e' || worksheet[address].t == 'z') {
                    continue;
                }
                if (value.toString().indexOf(',') != -1) {
                    // if (headInfo.keys[col].outputserver) {
                    //     Config.checkState(RetCode.EXCEL_DATA_ERROR, `${StringUtil.getCheckString(address, headInfo)} 单元格内存在英文逗号，影响csv输出！`)
                    // } else
                    if (headInfo.keys[col].outputclient) {
                        var reg = new RegExp("[\\u4E00-\\u9FFF]+", "g");
                        if (reg.test(value)) {
                            Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \u5355\u5143\u683C\u5185\u5305\u542B\u4E2D\u6587\u5374\u4F7F\u7528\u4E86\u82F1\u6587\u9017\u53F7\uFF01"));
                        }
                    }
                }
                //最多保留两位小数
                if (typeof value === 'number' && !isNaN(value)) {
                    value = parseFloat(value.toFixed(2));
                }
                values.push(value);
            }
            if (headInfo.keys.length != values.length) {
                Config_1.Config.checkState(Config_1.RetCode.EXCEL_DATA_ERROR, "".concat(StringUtil_1.default.getCheckString(address, headInfo), " \u6570\u636E\u9519\u8BEF5\uFF01"));
            }
            var obj = this.getObj(values, isMultArr, isArray, headInfo, row);
            cobjlist.push(obj.c);
            sobjlist.push(obj.s);
        }
        return {
            sheetname: headInfo.sheetname,
            jsobj: cobjlist,
            csvobj: sobjlist,
            headInfo: headInfo
        };
    };
    return ExcelUtil;
}());
exports.default = ExcelUtil;