MEST Table Exporter

美尔斯通导出表格

// ==UserScript==
// @name         MEST Table Exporter
// @namespace    joyings.com.cn
// @version      1.9.0
// @description  美尔斯通导出表格
// @author       zmz125000
// @match        http://*/mest/*
// @require      https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.core.min.js
// @require      https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.1/FileSaver.js
// @icon         https://www.google.com/s2/favicons?sz=64&domain=openwrt.org
// @grant        none
// @license      MIT
// @run-at      document-end
// ==/UserScript==

(function () {
    'use strict';

    // Your code here...
    var _loadScript = function (path) {
        var script = document.createElement('script');
        script.type = 'text/javascript';
        script.src = path;
        document.head.appendChild(script);
    }
    //_loadScript("https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.core.min.js");
    //_loadScript("https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.1/FileSaver.js");
    _loadScript("https://cdnjs.cloudflare.com/ajax/libs/TableExport/5.2.0/js/tableexport.js");

    window.CheckBox = false;

    window.onload = function () {
        addButtons();
        addBasicButton();
        addObserverIfDesiredNodeAvailable();
    };
    // Select the node that will be observed for mutations

    function addObserverIfDesiredNodeAvailable() {
        var composeBox = document.querySelectorAll('[class="el-tabs__item is-top is-active is-closable"]')[0];
        if (!composeBox) {
            //The node we need does not exist yet.
            //Wait 500ms and try again
            window.setTimeout(addObserverIfDesiredNodeAvailable, 500);
            return;
        }
        var config = {
            attributes: true,
        };
        var composeObserver = new MutationObserver(function () {
            window.setTimeout(addButtons, 500);
            composeObserver.disconnect();
            addObserverIfDesiredNodeAvailable();
        });
        composeObserver.observe(composeBox, config);
    }

    function addBasicButton() {
        var navbar = document.querySelectorAll('[class="el-tabs__nav-scroll"]')[0];
        var btn = document.createElement('button');
        btn.setAttribute('title', '手动添加按钮');
        btn.setAttribute('id', 'basicBtn');
        btn.setAttribute('type', 'button');
        btn.onclick = addButtons;
        btn.appendChild(document.createTextNode('添加按钮'));
        navbar.appendChild(btn);
    }

    function addButtons() {
        if (document.querySelector('[class="el-tabs__item is-top is-active is-closable"]').textContent == null)
            return;
        var btn = document.createElement('button');
        btn.setAttribute('title', '导出表体(表体刷新后请重新点击本按钮)');
        btn.setAttribute('type', 'button');
        btn.onclick = loadtableexport;
        btn.appendChild(document.createTextNode('TableExport'));

        var btn2 = document.createElement('button');
        btn2.setAttribute('title', '一键导出当前表格');
        btn2.setAttribute('id', 'oneKeyButton');
        btn2.setAttribute('type', 'button');
        btn2.onclick = oneKeyDownload;
        btn2.appendChild(document.createTextNode('⇩一键导出'));

        var dropdownDiv = document.createElement('div');
        var dropdownContent = document.createElement('div');
        dropdownDiv.setAttribute('class', "dropdown");
        dropdownContent.setAttribute('class', 'dropdown-content');
        dropdownContent.setAttribute('id', 'dropdownContentList');
        var btn3 = document.createElement('button');
        btn3.setAttribute('title', '添加当前表格到合并导出列表(多页表体请用AppendTableBody按钮添加每个分页)');
        btn3.setAttribute('id', 'addTable');
        btn3.setAttribute('type', 'button');
        btn3.onclick = addCurrentTable;
        btn3.appendChild(document.createTextNode('➕添加表格 (' + listCounter + ')'));
        dropdownDiv.appendChild(btn3);
        dropdownDiv.appendChild(dropdownContent);

        {
            var dropdownExportDiv = document.createElement('div');
            var dropdownExportBtns = document.createElement('div');
            dropdownExportDiv.setAttribute('class', "dropdown");
            dropdownExportBtns.setAttribute('class', 'dropdown-content');
            dropdownExportBtns.setAttribute('id', 'dropdownExportButtons');
            var btn4 = document.createElement('button');
            btn4.setAttribute('title', '合并导出');
            btn4.setAttribute('id', 'multiexport');
            btn4.setAttribute('type', 'button');
            btn4.onclick = exportSheets;
            btn4.appendChild(document.createTextNode('⇩批量导出'));
            dropdownExportDiv.appendChild(btn4);
            dropdownExportDiv.appendChild(dropdownExportBtns);

            // 生成生产排料表
            var btn7 = document.createElement('button');
            btn7.setAttribute('title', '合成生产排料表');
            // btn7.setAttribute('class', 'dropbtn');
            btn7.setAttribute('id', 'gerenateMaterialList');
            btn7.setAttribute('type', 'button');
            btn7.onclick = processCompositeList;
            btn7.appendChild(document.createTextNode('⇩生成排料单'));
            dropdownExportBtns.appendChild(btn7);

            // 生成采购计划表
            var btn9 = document.createElement('button');
            btn9.setAttribute('title', '合成采购计划表');
            // btn9.setAttribute('class', 'dropbtn');
            btn9.setAttribute('id', 'gerenatePurchasingList');
            btn9.setAttribute('type', 'button');
            btn9.onclick = processPurchasingList;
            btn9.appendChild(document.createTextNode('⇩生成采购单'));
            dropdownExportBtns.appendChild(btn9);
        }

        var btn5 = document.createElement('button');
        btn5.setAttribute('title', '清空合并导出列表');
        btn5.setAttribute('id', 'clearexportlist');
        btn5.setAttribute('type', 'button');
        btn5.onclick = clearExport;
        btn5.appendChild(document.createTextNode('✘清空导出列表'));

        var btn6 = document.createElement('button');
        btn6.setAttribute('title', '添加表体到当前表格');
        btn6.setAttribute('id', 'appendTableBody');
        btn6.setAttribute('type', 'button');
        btn6.onclick = appendTableBody;
        btn6.appendChild(document.createTextNode('➕添加表体'));

        var btn8 = document.createElement('button');
        btn8.setAttribute('title', '包含勾选框');
        btn8.setAttribute('id', 'expoertCheckBox');
        btn8.setAttribute('type', 'button');
        btn8.onclick = toggleCheckBox;
        btn8.appendChild(document.createTextNode(window.CheckBox ? "✔" : "✘"));

        var succeed = false;

        var header1 = document.querySelectorAll('[class="ml5"]')[0];
        var header2 = document.querySelectorAll('[class="el-form-item__content"]')[0];
        var header3 = document.querySelectorAll('[class="el-button-group"]')[0];
        var header4 = document.querySelectorAll('[class="tool-button-group"]')[0];
        var headers = {
            header1,
            header2,
            header3,
            header4
        };

        for (let headerName in headers) {
            var header = headers[headerName];
            if (!succeed && typeof (header) != "undefined" && header != null) {
                //header.appendChild(btn);
                header.appendChild(btn2);
                //header.appendChild(btn3);
                header.appendChild(dropdownDiv);
                header.appendChild(btn6);
                //header.appendChild(btn4);
                header.appendChild(dropdownExportDiv);
                header.appendChild(btn5);
                //header.appendChild(btn7);
                header.appendChild(btn8);
                succeed = true;
            }
        }
        if (window.sheetnames.length > 0)
            restoreBtnList();
    }

    function restoreBtnList() {
        for (let name of window.sheetnames) {
            let id = name.match(/\d+$/)[0];
            let btnElm = document.createElement('button');
            btnElm.setAttribute('title', name);
            btnElm.setAttribute('type', 'button');
            btnElm.setAttribute('id', 'dropdown' + id);
            btnElm.addEventListener('click', function () {
                deleteTable(btnElm);
            });
            btnElm.appendChild(document.createTextNode(name));
            document.getElementById("dropdownContentList").appendChild(btnElm);
        }
    }

    function loadtableexport() {
        formatAllToString();
        var oldcaption = document.querySelectorAll('[class="tableexport-caption"]')[0];
        while (typeof (oldcaption) != 'undefined' && oldcaption != null) {
            oldcaption.remove();
            oldcaption = document.querySelectorAll('[class="tableexport-caption"]')[0];
        };
        var pageName = document.querySelector('[class="el-tabs__item is-top is-active is-closable"]').textContent;
        TableExport(document.getElementsByTagName("table"), {
            headers: true, // (Boolean), display table headers (th or td elements) in the <thead>, (default: true)
            footers: true, // (Boolean), display table footers (th or td elements) in the <tfoot>, (default: false)
            formats: ["xlsx", "csv", "txt"], // (String[]), filetype(s) for the export, (default: ['xlsx', 'csv', 'txt'])
            filename: pageName + "导出表体", // (id, String), filename for the downloaded file, (default: 'id')
            bootstrap: false, // (Boolean), style buttons using bootstrap, (default: true)
            exportButtons: true, // (Boolean), automatically generate the built-in export buttons for each of the specified formats (default: true)
            position: "top", // (top, bottom), position of the caption element relative to table, (default: 'bottom')
            ignoreRows: null, // (Number, Number[]), row indices to exclude from the exported file(s) (default: null)
            ignoreCols: null, // (Number, Number[]), column indices to exclude from the exported file(s) (default: null)
            trimWhitespace: false, // (Boolean), remove all leading/trailing newlines, spaces, and tabs from cell text in the exported file(s) (default: false)
            RTL: false, // (Boolean), set direction of the worksheet to right-to-left (default: false)
            sheetname: pageName // (id, String), sheet name for the exported spreadsheet, (default: 'id')
        });
        var randomColor = Math.floor(Math.random() * 16777215).toString(16);
        document.querySelectorAll('[class="button-default xlsx"]')[1].style["background-color"] = "#" + randomColor;
    }

    function toggleCheckBox() {
        if (window.CheckBox) {
            window.CheckBox = false;
            document.getElementById("expoertCheckBox").firstChild.nodeValue = "✘";
        } else {
            window.CheckBox = true;
            document.getElementById("expoertCheckBox").firstChild.nodeValue = "✔";
        }
    }

    function getCurrentTable() {
        formatAllToString();
        var ignoreCol0 = null;
        var pageName = document.querySelector('[class="el-tabs__item is-top is-active is-closable"]').textContent;
        var tableCount = document.querySelectorAll('[class="has-gutter"]').length - 2;
        var tableIndex = tableCount >= 0 ? tableCount : 0;
        var tableElement = document.querySelectorAll("table.el-table__body")[tableIndex];
        var headerElement = document.querySelectorAll('[class="has-gutter"]')[tableIndex];
        var checkedElm = tableElement.querySelectorAll('[class="el-checkbox is-checked"]');

        if (window.CheckBox) {
            var headerCheckBox = headerElement.querySelectorAll('[class="el-checkbox"]');
            if (headerCheckBox.length != 0) {
                let headerCheckBoxText = document.createTextNode("勾选");
                headerCheckBox[0].appendChild(headerCheckBoxText);
            }

            for (let index = 0; index < checkedElm.length; index++) {
                let content = document.createTextNode("✔");
                checkedElm[index].appendChild(content);
            }
        } else {
            var hasCheckBox = !!document.querySelector('[class="el-checkbox__inner"]');
            ignoreCol0 = hasCheckBox ? 0 : null;
        }
        var instance1 = new TableExport(headerElement, {
            formats: ['xlsx'],
            exportButtons: false,
            ignoreCols: ignoreCol0,
            sheetname: pageName // (id, String), sheet name for the exported spreadsheet, (default: 'id')
        });
        var exportData1 = instance1.getExportData();
        var xlsxData1 = exportData1[Object.keys(exportData1)]['xlsx'];

        var instance2 = new TableExport(tableElement, {
            formats: ['xlsx'],
            exportButtons: false,
            ignoreCols: ignoreCol0
        });
        var exportData2 = instance2.getExportData();
        var xlsxData2 = exportData2[Object.keys(exportData2)]['xlsx'];
        xlsxData1.data = xlsxData1.data.concat(xlsxData2.data);
        formatColumns(xlsxData1.data);
        if (window.CheckBox) {
            for (let index = 0; index < checkedElm.length; index++) {
                checkedElm[index].innerHTML = checkedElm[index].innerHTML.replace(/✔/g, "");

            }
            if (headerCheckBox.length != 0) {
                headerCheckBox[0].innerHTML = headerCheckBox[0].innerHTML.replace("勾选", "");

            }
        }
        return {
            xlsxData1,
            instance1,
            pageName,
            exportData1
        };
    }

    function getCurrentTableBody(ignore0) {
        formatAllToString();
        var ignoreCol0 = ignore0;
        var pageName = document.querySelector('[class="el-tabs__item is-top is-active is-closable"]').textContent;
        var tableCount = document.querySelectorAll('[class="has-gutter"]').length - 2;
        var tableIndex = tableCount >= 0 ? tableCount : 0;
        var tableElement = document.querySelectorAll("table.el-table__body")[tableIndex];
        var checkedElm = tableElement.querySelectorAll('[class="el-checkbox is-checked"]');

        if (window.CheckBox) {
            for (let index = 0; index < checkedElm.length; index++) {
                let content = document.createTextNode("✔");
                checkedElm[index].appendChild(content);
            }
        } else {
            let hasCheckBox = !!document.querySelector('[class="el-checkbox__inner"]');
            ignoreCol0 = hasCheckBox ? 0 : null;
        }
        var instance1 = new TableExport(tableElement, {
            formats: ['xlsx'],
            exportButtons: false,
            ignoreCols: ignoreCol0
        });
        var exportData1 = instance1.getExportData();
        var xlsxData1 = exportData1[Object.keys(exportData1)]['xlsx'];
        if (window.CheckBox) {
            for (let index = 0; index < checkedElm.length; index++) {
                checkedElm[index].innerHTML = checkedElm[index].innerHTML.replace(/✔/g, "");
            }
        }
        return {
            xlsxData1,
            instance1,
            pageName,
            exportData1
        };
    }

    function oneKeyDownload() {
        var tableObject = getCurrentTable();
        var tableexport = tableObject.instance1;
        var xlsxData1 = tableObject.xlsxData1;
        tableexport.export2file(xlsxData1.data, xlsxData1.mimeType, tableObject.pageName + "导出表格 " + (new Date()).toLocaleTimeString(), xlsxData1.fileExtension, xlsxData1.merges, xlsxData1.RTL, tableObject.pageName);
        var randomColor = Math.floor(Math.random() * 16777215).toString(16);
        document.getElementById("oneKeyButton").style["background-color"] = "#" + randomColor;
    }

    function formatAllToString() {
        var cells = document.getElementsByTagName("td");
        var index = 0,
            length = cells.length;
        for (; index < length; index++) {
            cells[index].classList.add("tableexport-string");
        };
    }

    function formatColumnUtil(data, col, fmt) {
        var cols = data.map(x => x[col]);
        for (let i = 1; i < cols.length; i++) {
            cols[i].t = fmt;
        }
    }

    function formatColumns(data) {
        var dateColsIndex = [];
        var numsColsIndex = [];
        const datefmt = 'd';
        const numsfmt = 'n';
        const header = data[0];
        for (let index = 0; index < header.length; index++) {
            let str = header[index].v;
            if (str.includes("日期")) {
                dateColsIndex.push(index);
            } else if (str.includes("数量") || str.includes("金额") || str.includes("单价") || str.includes("下达量") || str.includes("现存量") || str.includes("总量") || str.includes("总量")) {
                numsColsIndex.push(index);
            }
        }
        for (let col of dateColsIndex) {
            formatColumnUtil(data, col, datefmt);
        }
        for (let col of numsColsIndex) {
            formatColumnUtil(data, col, numsfmt);
        }
        return data;
    }

    // export multiple pages
    window.exportTable = null;
    window.export_tables = null;
    window.tables_data = null;

    window.export_data = [];
    window.xlsx_info = {};
    window.sheetnames = [];
    window.listCounter = 0;

    function initMultiExport() {
        window.exportTable = document.querySelectorAll("has-gutter");
        window.export_tables = new TableExport(window.exportTable, {
            formats: ['xlsx'],
            bootstrap: false,
            exportButtons: false
        });
        window.tables_data = window.export_tables.getExportData();
    }

    window.tablePageCount = 0;

    function addCurrentTable() {
        if (window.exportTable == null) {
            initMultiExport();
        }
        var tableObject = getCurrentTable();
        var pageName = tableObject.pageName;
        var old_key = Object.keys(tableObject.exportData1)[0];
        listCounter += 1;
        Object.defineProperty(tableObject.exportData1, pageName + listCounter,
            Object.getOwnPropertyDescriptor(tableObject.exportData1, old_key));
        delete tableObject.exportData1[old_key];
        Object.assign(window.tables_data, tableObject.exportData1);
        window.sheetnames.push(pageName + listCounter);
        window.tablePageCount = 1;
        document.getElementById("appendTableBody").firstChild.nodeValue = "➕AppendTableBody (" + window.tablePageCount + ')';
        document.getElementById("addTable").firstChild.nodeValue = "➕添加表格 (" + listCounter + ')';

        // 添加下拉按钮
        var btnElm = document.createElement('button');
        btnElm.setAttribute('title', pageName + listCounter);
        btnElm.setAttribute('type', 'button');
        btnElm.setAttribute('id', 'dropdown' + listCounter);
        btnElm.addEventListener('click', function () {
            deleteTable(btnElm);
        });
        btnElm.appendChild(document.createTextNode(pageName + listCounter));
        document.getElementById("dropdownContentList").appendChild(btnElm);
    }

    function deleteTable(elm) {
        var name = elm.title;
        var id = elm.id;
        delete window.tables_data[name];
        var index = window.sheetnames.indexOf(name);
        if (index !== -1) {
            window.sheetnames.splice(index, 1);
        }
        elm.remove();
    }

    function appendTableBody() {
        if (window.sheetnames.length == 0) {
            alert("请先添加当前表格\n插件版本号: " + GM_info.script.version);
            return;
        }
        if (window.tables_data[Object.keys(window.tables_data)[Object.keys(window.tables_data).length - 1]]['xlsx'].data[0][0].v == '') {
            var tableObject = getCurrentTableBody(null);
        } else {
            tableObject = getCurrentTableBody();
        }
        var pageName = tableObject.pageName;
        var lastSheetName = window.sheetnames[window.sheetnames.length - 1];
        if (!lastSheetName.includes(pageName)) {
            alert("请先添加当前表格\n插件版本号: " + GM_info.script.version);
            return;
        }
        window.tables_data[Object.keys(window.tables_data)[Object.keys(window.tables_data).length - 1]]['xlsx'].data = window.tables_data[Object.keys(window.tables_data)[Object.keys(window.tables_data).length - 1]]['xlsx'].data.concat(tableObject.xlsxData1.data);
        formatColumns(window.tables_data[Object.keys(window.tables_data)[Object.keys(window.tables_data).length - 1]]['xlsx'].data);
        window.tablePageCount++;
        document.getElementById("appendTableBody").firstChild.nodeValue = "➕AppendTableBody (" + window.tablePageCount + ')';
    }

    function clearExport() {
        window.exportTable = null;
        window.export_tables = null;
        window.tables_data = null;
        window.export_data = [];
        window.xlsx_info = {};
        window.sheetnames = [];
        window.listCounter = 0;
        window.tablePageCount = 0;
        initMultiExport();
        document.getElementById("appendTableBody").firstChild.nodeValue = "➕AppendTableBody";
        document.getElementById("addTable").firstChild.nodeValue = "➕添加表格 (" + listCounter + ')';
        document.getElementById('dropdownContentList').textContent = '';
    }

    function exportSheets() {
        if (window.sheetnames.length == 0) {
            alert("请先添加表格\n插件版本号: " + GM_info.script.version);
            return;
        }
        window.export_data = [];
        for (let table_id in window.tables_data) {
            window.export_data.push(window.tables_data[table_id]["xlsx"].data);
        }
        window.xlsx_info = window.tables_data[Object.keys(window.tables_data)[0]]["xlsx"];
        // exportSheetsUtil(window.export_data, "合并导出表格 " + (new Date()).toLocaleTimeString(), window.sheetnames);
        exportFormulaSheet(window.export_data, "合并导出表格 " + (new Date()).toLocaleTimeString(), window.sheetnames);
    }

    // get procudtion excel
    // materialData[0] 订单 [1] 生产派工 [2] 材料单
    window.materialDataArr = Array(3);
    window.materialListObj = {};


    function wrongCompositeList(info) {
        alert(info + "\n请重新添加材料计划、生产派工、订单列表到合并导出列表\n材料单以生产工序派工表为基础合成,合成时会自动筛选并引用另外两个表的信息\n多页表体请用AppendTableBody按钮添加每个分页\n插件版本号: " + GM_info.script.version);
    }

    function processCompositeList() {
        window.materialDataArr = Array(3);
        window.materialListObj = {};
        try {
            var tableObjectKeys = Object.keys(window.tables_data);
        } catch (e) {
            wrongCompositeList();
            return;
        }
        let listOK = false;
        if (tableObjectKeys.length < 3) {
            wrongCompositeList("缺少相关表格数据\n" + tableObjectKeys);
            return;
        } else if (tableObjectKeys.length > 3 && !confirm('检测到多余表格,是否继续')) {
            return;
        }

        for (let key of tableObjectKeys) {
            switch (window.tables_data[key]['xlsx'].sheetname) {
                case '订单列表':
                    if (window.materialDataArr[0] != null) {
                        wrongCompositeList('检测到多余的订单列表\n' + tableObjectKeys)
                        return;
                    }
                    window.materialDataArr[0] = structuredClone(window.tables_data[key]['xlsx'].data);
                    break;
                case '生产工序派工':
                    if (window.materialDataArr[1] != null) {
                        wrongCompositeList('检测到多余的生产派工表\n' + tableObjectKeys)
                        return;
                    }
                    window.materialDataArr[1] = structuredClone(window.tables_data[key]['xlsx'].data);
                    break;
                case '材料计划':
                    if (window.materialDataArr[2] != null) {
                        wrongCompositeList('检测到多余的材料计划表\n' + tableObjectKeys)
                        return;
                    }
                    window.materialDataArr[2] = structuredClone(window.tables_data[key]['xlsx'].data);
                    break;
                default:
                    wrongCompositeList("无关表格\n" + tableObjectKeys)
                    return;
            }
        }
        listOK = true;
        for (let item of window.materialDataArr) {
            if (item == null)
                listOK = false;
        }
        if (!listOK) {
            wrongCompositeList("缺少相关表格数据\n" + tableObjectKeys);
            return;
        }
        window.materialListObj["生产派工单"] = structuredClone(window.tables_data[tableObjectKeys[0]]);
        window.materialListObj["生产排料单"] = structuredClone(window.tables_data[tableObjectKeys[0]]);
        window.materialListObj["车间领料表"] = structuredClone(window.tables_data[tableObjectKeys[0]]);
        window.materialListObj["生产派工单"]['xlsx'].sheetname = "生产派工单";
        window.materialListObj["生产排料单"]['xlsx'].sheetname = "生产排料单";
        window.materialListObj["车间领料表"]['xlsx'].sheetname = "车间领料表";
        window.materialListObj["生产派工单"]['xlsx'].data = structuredClone(window.materialDataArr[1]);
        window.materialListObj["生产排料单"]['xlsx'].data = [];
        window.materialListObj["车间领料表"]['xlsx'].data = [];

        generateMaterialListUtil();
    }

    function generateMaterialListUtil() {
        // 用来搜索的表格-派工表
        let PGBCPCodeCols = null;
        let PGWorkshopCols = null;
        for (let index = 0; index < window.materialDataArr[1][0].length; index++) {
            switch (window.materialDataArr[1][0][index].v) {
                case '半成品编码':
                    PGBCPCodeCols = window.materialDataArr[1].map(x => x[index]);
                    break;
                case '车间名称':
                    PGWorkshopCols = window.materialDataArr[1].map(x => x[index]);
                    break;
                default:
            }
        }


        // 用来搜索的表格-材料表
        let materialBCPCols = null;
        let materialCols = null;
        let materiaSCDlCols = null;
        let materiaCatCols = null;
        let materiaRequireCols = null;
        let materialProductSNCols = null;
        let materialUnitCols = null;
        let materialCatCodeCols = null;
        let materialAlias = null;
        let materialSNCols = null;
        let materialSizeCols = null;
        for (let index = 0; index < window.materialDataArr[2][0].length; index++) {
            switch (window.materialDataArr[2][0][index].v) {
                case '领取材料规格':
                    materialSizeCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '领取材料别名':
                    materialAlias = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '计量单位':
                    materialUnitCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '领取材料分类编码':
                    materialCatCodeCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '应用半成品编码':
                    materialBCPCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '领取材料':
                    materialCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '生产单号':
                    materiaSCDlCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '领取材料分类名称':
                    materiaCatCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '所需总领料数量':
                    materiaRequireCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '商品编码':
                    materialProductSNCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                case '领取材料编码':
                    materialSNCols = window.materialDataArr[2].map(x => x[index]);
                    break;
                default:
            }
        }

        // 用来搜索的表格-订单表
        let orderClient = null;
        let orderNo = null;
        let orderProductSN = null;
        let orderProductName = null;
        let orderProductCount = null;
        let orderCreatedDate = null;
        let orderDeliverDate = null;
        for (let index = 0; index < window.materialDataArr[0][0].length; index++) {
            switch (window.materialDataArr[0][0][index].v) {
                case '客户名称':
                    orderClient = window.materialDataArr[0].map(x => x[index]);
                    break;
                case '订单号':
                    orderNo = window.materialDataArr[0].map(x => x[index]);
                    break;
                case '存货编码':
                    orderProductSN = window.materialDataArr[0].map(x => x[index]);
                    break;
                case '存货名称':
                    orderProductName = window.materialDataArr[0].map(x => x[index]);
                    break;
                case '下单数量':
                    orderProductCount = window.materialDataArr[0].map(x => x[index]);
                    break;
                case '单据日期':
                    orderCreatedDate = window.materialDataArr[0].map(x => x[index]);
                    break;
                case '发货日期':
                    orderDeliverDate = window.materialDataArr[0].map(x => x[index]);
                    break;
                default:
            }
        }
        // 派工单
        {
            let aoa = window.materialListObj["生产派工单"]['xlsx'].data;
            if (aoa[0][aoa[0].length - 1].v == '')
                aoa[0].pop();
            let aoaHeaderOrigLength = aoa[0].length;
            aoa[0].push({
                'v': '原料分类编码',
                't': 's'
            });
            aoa[0].push({
                'v': '原料分类名称',
                't': 's'
            });
            aoa[0].push({
                'v': '原料别名',
                't': 's'
            });
            aoa[0].push({
                'v': '原料名',
                't': 's'
            });
            aoa[0].push({
                'v': '领料重量kg',
                't': 's'
            });
            aoa[0].push({
                'v': '领料数量',
                't': 's'
            });
            // 要生成的表格
            let BCPCols = null;
            let SCDCols = null;
            for (let index = 0; index < aoa[0].length; index++) {
                switch (aoa[0][index].v) {
                    case '半成品编码':
                        BCPCols = aoa.map(x => x[index]);
                        break;
                    case '生产单号':
                        SCDCols = aoa.map(x => x[index]);
                        break;
                }
            }

            for (let rowNum = 1; rowNum < aoa.length; rowNum++) {
                let searchTarget = BCPCols[rowNum].v;
                let searchSCD = SCDCols[rowNum].v;
                let aoaYuanLiaoCategoryCode = aoaHeaderOrigLength;
                let aoaYuanLiaoCategory = aoaHeaderOrigLength + 1;
                let aoaYuanLiaoAlias = aoaHeaderOrigLength + 2;
                let aoaYuanLiao = aoaHeaderOrigLength + 3;
                let aoaYuanLiaoRequireKG = aoaHeaderOrigLength + 4;
                let aoaYuanLiaoRequireNums = aoaHeaderOrigLength + 5;
                for (let origRowNum = 1; origRowNum < materialBCPCols.length; origRowNum++) {
                    if (searchTarget == materialBCPCols[origRowNum].v && searchSCD == materiaSCDlCols[origRowNum].v) {
                        aoa[rowNum][aoaYuanLiao] = structuredClone(materialCols[origRowNum]);
                        aoa[rowNum][aoaYuanLiaoAlias] = structuredClone(materialAlias[origRowNum]);
                        aoa[rowNum][aoaYuanLiao] = structuredClone(materialCols[origRowNum]);
                        aoa[rowNum][aoaYuanLiaoCategoryCode] = structuredClone(materialCatCodeCols[origRowNum]);
                        aoa[rowNum][aoaYuanLiaoCategory] = structuredClone(materiaCatCols[origRowNum]);
                        if (materialUnitCols[origRowNum].v == 'KG' || materialUnitCols[origRowNum].v == 'Kg' || materialUnitCols[origRowNum].v == 'kg')
                            aoa[rowNum][aoaYuanLiaoRequireKG] = structuredClone(materiaRequireCols[origRowNum]);
                        else
                            aoa[rowNum][aoaYuanLiaoRequireNums] = structuredClone(materiaRequireCols[origRowNum]);
                    }
                }
            }

            let aoaSlim = [];
            let headerTextObjArr = aoa[0];
            let newIndex = 0;
            let dataLength = aoa.length;
            for (let index = 0; index < headerTextObjArr.length; index++) {
                switch (headerTextObjArr[index].v) {
                    case '计划开始时间':
                    case '计划结束时间':
                    case '客户名称':
                    case '标识':
                    case '订单状态':
                    case '设备':
                    case '模具':
                    case '操作人':
                    case '操作':
                        continue;
                    default:
                        for (let rowNum = 0; rowNum < dataLength; rowNum++) {
                            let col = aoa.map(x => x[index]);
                            if (aoaSlim[rowNum] == null)
                                aoaSlim[rowNum] = [];
                            aoaSlim[rowNum][newIndex] = col[rowNum];
                        }
                        newIndex++;
                }
            }
            window.materialListObj["生产派工单"]['xlsx'].data = aoaSlim;
        }

        // 排料单
        {
            let aoa = window.materialListObj["生产排料单"]['xlsx'].data;
            let headerText = ["订单号", "半成品编码", "生产单号", "成品编码", "半成品名", "生产数", "出货数", "半成品序号", "产品名", "单件用量", "领取材料编码", "领取材料名称", "材料别名", "开小料尺寸", "开料数", "大料尺寸", "物料分类编码", "物料分类", "工序", '生产车间', '物料来源', '单位', "重量Kg", "件数"];
            // let headerText = ["订单号", "半成品编码", "生产单号", "成品编码", "配件名称", "生产数", "出货数", "零配件", "产品名", "单件用量", "领取材料编码", "开小料尺寸", "开料数", "大料尺寸", "大料数", "产品名", "卷料编号", "物料分类", "喷涂面积/件/m2", "重量Kg", "件数"];
            let headerRow = [];
            for (let text of headerText) {
                let obj = {};
                obj["v"] = text;
                obj["t"] = 's';
                headerRow.push(obj);
            }
            aoa.push(headerRow);

            // 初始化数据
            let headerLength = window.materialDataArr[1][0].length;
            for (let colNum = 0; colNum < headerLength; colNum++) {
                let aoaBCPCol = headerText.indexOf('半成品编码');
                let aoaOrderNo = headerText.indexOf('订单号');
                let aoaSCDCol = headerText.indexOf('生产单号');
                let aoaKaiLiaoShu = headerText.indexOf('开料数');
                let aoaComponentName = headerText.indexOf('产品名');
                let aoaProductName = headerText.indexOf('半成品名');
                let aoaWorkPieceSize = headerText.indexOf('开小料尺寸');
                let aoaWorkshopName = headerText.indexOf('生产车间');
                let aoaProcessName = headerText.indexOf('工序');
                let obj = window.materialDataArr[1][0][colNum];
                let dataLength = window.materialDataArr[1].length;
                switch (obj['v']) {
                    case '半成品规格':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaWorkPieceSize] = col[rowNum];
                        }
                        break;
                    case '半成品编码':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaBCPCol] = col[rowNum];
                        }
                        break;
                    case '工序名称':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaProcessName] = col[rowNum];
                        }
                        break;
                    case '工序总量':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaKaiLiaoShu] = col[rowNum];
                        }
                        break;
                    case '生产单号':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaSCDCol] = col[rowNum];
                        }
                        break;
                    case '输出半成品':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaProductName] = col[rowNum];
                        }
                        break;
                    case '最终成品':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaComponentName] = col[rowNum];
                        }
                        break;
                    case '订单号':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaOrderNo] = col[rowNum];
                        }
                        break;
                    case '车间名称':
                        for (let rowNum = 1; rowNum < dataLength; rowNum++) {
                            let col = window.materialDataArr[1].map(x => x[colNum]);
                            if (aoa[rowNum] == null)
                                aoa[rowNum] = [];
                            aoa[rowNum][aoaWorkshopName] = col[rowNum];
                        }
                        break;
                }
            }

            // 获取索引
            let BCPCols = null;
            let SCDCols = null;
            for (let index = 0; index < aoa[0].length; index++) {
                switch (aoa[0][index].v) {
                    case '半成品编码':
                        BCPCols = aoa.map(x => x[index]);
                        break;
                    case '生产单号':
                        SCDCols = aoa.map(x => x[index]);
                        break;
                }
            }

            // 根据材料计划填入数据
            // 重量Kg 待更改
            for (let rowNum = 1; rowNum < aoa.length; rowNum++) {
                let searchTarget = BCPCols[rowNum].v;
                let searchSCD = SCDCols[rowNum].v;
                let aoaProductSN = headerText.indexOf('成品编码');
                // let aoaProductCount = headerText.indexOf('出货数');
                let aoaMaterialCat = headerText.indexOf('物料分类');
                let aoaMaterialCatCode = headerText.indexOf('物料分类编码');
                let aoaMaterialSN = headerText.indexOf('领取材料编码');
                let aoaMaterialName = headerText.indexOf('领取材料名称');
                let aoaMaterialAlias = headerText.indexOf('材料别名');
                let aoaMaterialWeight = headerText.indexOf('重量Kg');
                let aoaMaterialNums = headerText.indexOf('件数');
                let aoaMaterialSize = headerText.indexOf('大料尺寸');
                let aoaMaterialUnit = headerText.indexOf('单位');
                for (let origRowNum = 1; origRowNum < materialBCPCols.length; origRowNum++) {
                    if (searchTarget == materialBCPCols[origRowNum].v && searchSCD == materiaSCDlCols[origRowNum].v) {
                        aoa[rowNum][aoaProductSN] = structuredClone(materialProductSNCols[origRowNum]);
                        aoa[rowNum][aoaMaterialSN] = structuredClone(materialSNCols[origRowNum]);
                        aoa[rowNum][aoaMaterialName] = structuredClone(materialCols[origRowNum]);
                        aoa[rowNum][aoaMaterialAlias] = structuredClone(materialAlias[origRowNum]);
                        aoa[rowNum][aoaMaterialCatCode] = structuredClone(materialCatCodeCols[origRowNum]);
                        if (materialUnitCols[origRowNum].v == 'KG' || materialUnitCols[origRowNum].v == 'Kg' || materialUnitCols[origRowNum].v == 'kg')
                            aoa[rowNum][aoaMaterialWeight] = structuredClone(materiaRequireCols[origRowNum]);
                        else
                            aoa[rowNum][aoaMaterialNums] = structuredClone(materiaRequireCols[origRowNum]);
                        aoa[rowNum][aoaMaterialCat] = structuredClone(materiaCatCols[origRowNum]);
                        aoa[rowNum][aoaMaterialCat].v = aoa[rowNum][aoaMaterialCat].v.split('-')[1];
                        aoa[rowNum][aoaMaterialSize] = structuredClone(materialSizeCols[origRowNum]);
                        aoa[rowNum][aoaMaterialUnit] = structuredClone(materialUnitCols[origRowNum]);
                    }
                }
            }
            // 一个SCD对应一个产品
            // for (let rowNum = 1; rowNum < aoa.length; rowNum++) {
            //     let searchSCD = SCDCols[rowNum].v;
            //     let aoaProductSN = headerText.indexOf('成品编码');
            //     for (let origRowNum = 1; origRowNum < materialBCPCols.length; origRowNum++) {
            //         if (searchSCD == materiaSCDlCols[origRowNum].v) {
            //             aoa[rowNum][aoaProductSN] = structuredClone(materialProductSNCols[origRowNum]);
            //         }
            //     }
            // }

            // 根据订单填入数据
            let orderNoCols = aoa.map(x => x[headerText.indexOf("订单号")]);
            let productSNCols = aoa.map(x => x[headerText.indexOf("成品编码")]);
            for (let rowNum = 1; rowNum < aoa.length; rowNum++) {
                try {
                    let searchTarget = productSNCols[rowNum].v;
                    let orderNoTarget = orderNoCols[rowNum].v;
                    let aoaProductCount = headerText.indexOf('出货数');
                    for (let origRowNum = 1; origRowNum < orderProductSN.length; origRowNum++) {
                        try {
                            if (searchTarget == orderProductSN[origRowNum].v && orderNoTarget == orderNo[origRowNum].v) {
                                aoa[rowNum][aoaProductCount] = structuredClone(orderProductCount[origRowNum]);
                            }
                        } catch (e) {
                            continue;
                        }
                    }
                } catch (e) {
                    continue;
                }
            }

            // 计算单件用量
            // 暂时用出货数计算,待更改
            let productCount = aoa.map(x => x[headerText.indexOf("出货数")]);
            let kaiLiaoShu = aoa.map(x => x[headerText.indexOf("开料数")]);
            let danJianYongLiangIndex = headerText.indexOf("单件用量");
            for (let rowNum = 1; rowNum < aoa.length; rowNum++) {
                try {
                    aoa[rowNum][danJianYongLiangIndex] = {
                        'v': +kaiLiaoShu[rowNum].v / +productCount[rowNum].v,
                        't': 'n'
                    };
                } catch (e) {
                    continue;
                }
            }

            // 填入物料来源
            let aoaOriginWorkshopName = headerText.indexOf('物料来源');
            let PGBCPColsValues = structuredClone(PGBCPCodeCols);
            let aoaMaterialCodeColsValues = structuredClone(aoa.map(x => x[headerText.indexOf('领取材料编码')]));
            for (let i = 0; i < PGBCPColsValues.length; i++)
                PGBCPColsValues[i] = PGBCPColsValues[i].v;
            for (let i = 0; i < aoaMaterialCodeColsValues.length; i++) {
                if (aoaMaterialCodeColsValues[i] == null)
                    aoaMaterialCodeColsValues[i] = '';
                else
                    aoaMaterialCodeColsValues[i] = aoaMaterialCodeColsValues[i].v;
            }
            for (let i = 1; i < aoa.length; i++) {
                let oi = PGBCPColsValues.indexOf(aoaMaterialCodeColsValues[i]);
                if (oi < 0 && aoaMaterialCodeColsValues[i] != '' && aoaMaterialCodeColsValues[i].split('-')[0] != '02') {
                    if (aoa[i] == null)
                        break
                    aoa[i][aoaOriginWorkshopName] = {
                        't': 's',
                        'v': '仓库'
                    }
                } else if (oi < 0)
                    continue;
                else
                    aoa[i][aoaOriginWorkshopName] = {
                        't': 's',
                        'v': PGWorkshopCols[oi].v
                    }
            }


            // 生产排料单(原料)
            window.materialListObj["生产排料单(原料)"] = structuredClone(window.materialListObj["车间领料表"]);
            window.materialListObj["生产排料单(原料)"]['xlsx'].sheetname = "生产排料单(原料)";
            window.materialListObj["生产排料单(原料)"]['xlsx'].data = [];

            {
                let aoa = window.materialListObj["生产排料单(原料)"]['xlsx'].data;
                let aoaOrig = window.materialListObj["生产排料单"]['xlsx'].data;
                let materialCatCode = headerText.indexOf("物料分类编码");
                let leadingRow = 1;
                aoa[0] = structuredClone(aoaOrig[0]);
                for (let i = 0; i < aoaOrig.length; i++) {
                    if (aoaOrig[i][materialCatCode] != null && aoaOrig[i][materialCatCode].v.split('-')[0] == '01') {
                        aoa[leadingRow] = structuredClone(aoaOrig[i]);
                        leadingRow++;
                    }
                }
            }
        }

        // 车间领料表
        {
            let paigongAoa = window.materialListObj["生产排料单"]['xlsx'].data;
            let aoa = window.materialListObj["车间领料表"]['xlsx'].data
            let headerText = ['物料编号', '物料分类编码', '物料分类', '物料名', '别名', '物料规格', '生产车间', '物料来源', '领料单位', '领料重量kg', '领料数', "实际出库数", "差额"]
            let headerRow = [];
            for (let text of headerText) {
                let obj = {};
                obj["v"] = text;
                obj["t"] = 's';
                headerRow.push(obj);
            }
            aoa.push(headerRow);
            // 准备数据
            let materialName = null;
            let materialCode = null;
            let materialAlias = null;
            let materialCat = null;
            let materialCatCode = null;
            let materialSpecs = null;
            let workshopName = null;
            let originworkshopName = null;
            let materialUnit = null;
            let materialQtyKg = null;
            let materialQty = null;
            for (let index = 0; index < paigongAoa[0].length; index++) {
                switch (paigongAoa[0][index].v) {
                    case '件数':
                        materialQty = paigongAoa.map(x => x[index]);
                        break;
                    case '重量Kg':
                        materialQtyKg = paigongAoa.map(x => x[index]);
                        break;
                    case '材料别名':
                        materialAlias = paigongAoa.map(x => x[index]);
                        break;
                    case '生产车间':
                        workshopName = paigongAoa.map(x => x[index]);
                        break;
                    case '物料来源':
                        originworkshopName = paigongAoa.map(x => x[index]);
                        break;
                    case '领取材料名称':
                        materialName = paigongAoa.map(x => x[index]);
                        break;
                    case '物料分类':
                        materialCat = paigongAoa.map(x => x[index]);
                        break;
                    case '物料分类编码':
                        materialCatCode = paigongAoa.map(x => x[index]);
                        break;
                    case '大料尺寸':
                        materialSpecs = paigongAoa.map(x => x[index]);
                        break;
                    case '领取材料编码':
                        materialCode = paigongAoa.map(x => x[index]);
                        break;
                    case '单位':
                        materialUnit = paigongAoa.map(x => x[index]);
                        break;
                    default:
                }
            }

            let aoaMaterialCode = headerText.indexOf('物料编号');
            let aoaMaterialName = headerText.indexOf('物料名');
            let aoaMaterialAlias = headerText.indexOf('别名');
            let aoaMaterialCat = headerText.indexOf('物料分类');
            let aoaMaterialCatCode = headerText.indexOf('物料分类编码');
            let aoaMaterialSpecs = headerText.indexOf('物料规格');
            let aoaWorkshopName = headerText.indexOf('生产车间');
            let aoaOriginWorkshopName = headerText.indexOf('物料来源');
            let aoaMaterialUnit = headerText.indexOf('领料单位');
            let aoaMaterialQtyKg = headerText.indexOf('领料重量kg');
            let aoaMaterialQty = headerText.indexOf('领料数');

            let aoaIndexMap = ['物料编号'];
            let aoaLeadingRowNum = 1;

            for (let rowNum = 1; rowNum < paigongAoa.length; rowNum++) {
                // working aoaRow
                if (materialCode[rowNum] == null)
                    continue;
                let aoaRow = -1;
                for (let i = 0; i < aoaIndexMap.length; i++) {
                    if (aoaIndexMap[i] == materialCode[rowNum].v && aoa[i][aoaWorkshopName].v == workshopName[rowNum].v) {
                        aoaRow = i;
                        break
                    }
                }
                if (aoaRow < 0) {
                    aoa[aoaLeadingRowNum] = [];
                    aoaIndexMap.push(materialCode[rowNum].v);
                    aoaRow = aoaLeadingRowNum;
                    aoaLeadingRowNum++;
                    aoa[aoaRow][aoaMaterialName] = structuredClone(materialName[rowNum]);
                    aoa[aoaRow][aoaMaterialCode] = structuredClone(materialCode[rowNum]);
                    aoa[aoaRow][aoaMaterialCat] = structuredClone(materialCat[rowNum]);
                    aoa[aoaRow][aoaMaterialSpecs] = structuredClone(materialSpecs[rowNum]);
                    aoa[aoaRow][aoaWorkshopName] = structuredClone(workshopName[rowNum]);
                    aoa[aoaRow][aoaMaterialQtyKg] = structuredClone(materialQtyKg[rowNum]);
                    aoa[aoaRow][aoaOriginWorkshopName] = structuredClone(originworkshopName[rowNum]);
                    aoa[aoaRow][aoaMaterialQty] = structuredClone(materialQty[rowNum]);
                    aoa[aoaRow][aoaMaterialUnit] = structuredClone(materialUnit[rowNum]);
                    aoa[aoaRow][aoaMaterialCatCode] = structuredClone(materialCatCode[rowNum]);
                    aoa[aoaRow][aoaMaterialAlias] = structuredClone(materialAlias[rowNum]);
                } else {
                    if (materialQtyKg[rowNum] != null)
                        aoa[aoaRow][aoaMaterialQtyKg].v = +aoa[aoaRow][aoaMaterialQtyKg].v + +materialQtyKg[rowNum].v;
                    if (materialQty[rowNum] != null)
                        aoa[aoaRow][aoaMaterialQty].v = +aoa[aoaRow][aoaMaterialQty].v + +materialQty[rowNum].v;
                }
            }

            // 车间领料表(原料)
            window.materialListObj["车间领料表(原料)"] = structuredClone(window.materialListObj["车间领料表"]);
            window.materialListObj["车间领料表(原料)"]['xlsx'].sheetname = "车间领料表(原料)";
            window.materialListObj["车间领料表(原料)"]['xlsx'].data = [];

            {
                let aoa = window.materialListObj["车间领料表(原料)"]['xlsx'].data;
                let aoaOrig = window.materialListObj["车间领料表"]['xlsx'].data;
                let materialCatCode = headerText.indexOf("物料分类编码");
                let difference = headerText.indexOf("差额");
                let realOut = headerText.indexOf("实际出库数");
                let wlWeight = headerText.indexOf("领料重量kg");
                let wlNums = headerText.indexOf("领料数");
                const buildDiff = String.fromCharCode(realOut + 65);
                let leadingRow = 1;
                aoa[0] = structuredClone(aoaOrig[0]);
                for (let i = 0; i < aoaOrig.length; i++) {
                    if (aoaOrig[i][materialCatCode] != null && aoaOrig[i][materialCatCode].v.split('-')[0] == '01') {
                        aoa[leadingRow] = structuredClone(aoaOrig[i]);
                        aoa[leadingRow][difference] = {
                            't': 'n',
                            'v': 0,
                            'f': '',
                        };
                        if (aoa[leadingRow][wlWeight].v > 0) {
                            aoa[leadingRow][difference].f = buildDiff.concat(leadingRow + 1, '-', String.fromCharCode(wlWeight + 65), leadingRow + 1);
                        } else {
                            aoa[leadingRow][difference].f = buildDiff.concat(leadingRow + 1, '-', String.fromCharCode(wlNums + 65), leadingRow + 1);
                        }
                        leadingRow++;
                    }
                }
            }
        }

        // export xlsx
        var xlsxData1 = window.materialListObj["生产派工单"]['xlsx'];
        var xlsxData2 = window.materialListObj["生产排料单"]['xlsx'];
        var xlsxData3 = window.materialListObj["车间领料表"]['xlsx'];
        var xlsxData4 = window.materialListObj["生产排料单(原料)"]['xlsx'];
        var xlsxData5 = window.materialListObj["车间领料表(原料)"]['xlsx'];
        // tableexport.export2file(xlsxData1.data, xlsxData1.mimeType, xlsxData1.sheetname + (new Date()).toLocaleTimeString(), xlsxData1.fileExtension, xlsxData1.merges, xlsxData1.RTL, xlsxData1.sheetname);
        // tableexport.export2file(xlsxData2.data, xlsxData2.mimeType, xlsxData2.sheetname + (new Date()).toLocaleTimeString(), xlsxData2.fileExtension, xlsxData2.merges, xlsxData2.RTL, xlsxData2.sheetname);
        exportFormulaSheet([xlsxData1.data, xlsxData2.data, xlsxData3.data, xlsxData4.data, xlsxData5.data], "合并派工领料单" + (new Date()).toLocaleTimeString(), [xlsxData1.sheetname, xlsxData2.sheetname, xlsxData3.sheetname, xlsxData4.sheetname, xlsxData5.sheetname]);
    }

    // 合成采购计划表
    window.purchasingListObject = {}
    window.purchasingData = null;

    function wrongPurchasingData(info) {
        alert(info + "\n请重新添加采购计划到合并导出列表\n多页表体请用AppendTableBody按钮添加每个分页\n插件版本号: " + GM_info.script.version);
    }

    function processPurchasingList() {
        window.purchasingListObject = {};
        window.purchasingData = null;
        try {
            var tableObjectKeys = Object.keys(window.tables_data);
        } catch (e) {
            wrongPurchasingData();
            return;
        }
        let listOK = false;
        if (tableObjectKeys.length < 1) {
            wrongPurchasingData("缺少相关表格数据\n" + tableObjectKeys);
            return;
        } else if (tableObjectKeys.length > 1 && !confirm('检测到多余表格,是否继续')) {
            return;
        }

        for (let key of tableObjectKeys) {
            switch (window.tables_data[key]['xlsx'].sheetname) {
                case '采购计划':
                    if (window.purchasingData != null) {
                        wrongPurchasingData('重复表格\n' + tableObjectKeys);
                        return;
                    }
                    window.purchasingData = window.tables_data[key]['xlsx'].data;
                    break;
                default:
                    wrongPurchasingData("无关表格\n" + tableObjectKeys);
                    return;
            }
        }
        listOK = true;
        for (let item of window.purchasingData) {
            if (item == null)
                listOK = false;
        }
        if (!listOK) {
            wrongPurchasingData("缺少相关表格数据\n" + tableObjectKeys);
            return;
        }
        window.purchasingListObject["采购计划表"] = structuredClone(window.tables_data[tableObjectKeys[0]]);
        window.purchasingListObject["采购计划表"]['xlsx'].sheetname = "采购计划表";
        window.purchasingListObject["采购汇总报表"] = structuredClone(window.tables_data[tableObjectKeys[0]]);
        window.purchasingListObject["采购汇总报表"]['xlsx'].sheetname = "采购汇总报表";
        window.purchasingListObject["采购汇总报表"]['xlsx'].data = [];
        generatePurchasingList();
    }

    function createNewPurchasingSheet(sheetname, headerText) {
        if (window.purchasingListObject[sheetname] == null) {
            window.purchasingListObject[sheetname] = structuredClone(window.purchasingListObject["采购汇总报表"]);
            window.purchasingListObject[sheetname]['xlsx'].sheetname = sheetname;
            window.purchasingListObject[sheetname]['xlsx'].data = [];

            let aoa = window.purchasingListObject[sheetname]['xlsx'].data;
            let headerRow = [];
            for (let text of headerText) {
                let obj = {};
                obj["v"] = text;
                obj["t"] = 's';
                headerRow.push(obj);
            }
            aoa.push(headerRow);
        }
    }


    function generatePurchasingList() {
        // 采购汇总报表
        {
            let aoa = window.purchasingListObject["采购汇总报表"]['xlsx'].data;
            var headerText = ["状态", "商品分类名称", "订单号", "仓库", "采购物料", "物料规格", "物料别名", '下单数', "采购单位", "网页操作", '下单数减下达量', "供应商", "单价", "税率", "总金额", "税后总金额", "订单单据日期", "物料编码", "物料分类名称", "总采购数量", "系统下达量", "物料可用量", "物料现存量", "单据来源", "生产单号"];
            var warehouseHeaderText = ["商品分类名称", "物料分类名称", "订单号", "仓库", "采购物料", "物料规格", "物料别名", "入库数", "采购单位", "供应商", "入库数减采购数", "单价", "总金额", "物料编码", '采购数', "物料现存量", "单据来源"];
            // var headerText = ["序号", "单据来源", "订单号", "采购物料分类名称", "订单单据日期", "状态", "商品分类名称", "采购物料", "生产单号", "采购物料编码", "采购物料可用量", "采购物料现存量", "物料规格", "总采购数量", "本次下达量", '下达量减可用量', "采购单位", "供应商"];
            let headerRow = [];
            for (let text of headerText) {
                let obj = {};
                obj["v"] = text;
                obj["t"] = 's';
                headerRow.push(obj);
            }
            aoa.push(headerRow);
        }

        // 根据原料分类添加表格
        {
            let aoa = window.purchasingListObject["采购计划表"]['xlsx'].data;
            var purchasingPlanHeader = [];
            var materialCatList = [];
            for (let headerObj of aoa[0]) {
                purchasingPlanHeader.push(headerObj.v);
            }
            let index = purchasingPlanHeader.indexOf('采购物料分类名称');
            let rowLimit = aoa.length;
            for (let row = 1; row < rowLimit; row++) {
                let cellObj = aoa[row][index];
                if (materialCatList.includes(cellObj.v))
                    continue;
                materialCatList.push(cellObj.v);
            }

            createNewPurchasingSheet('订单汇总', headerText);
            createNewPurchasingSheet('采购入库单', warehouseHeaderText);
            createNewPurchasingSheet('其他分类物料汇总', headerText);
            for (let sheetname of materialCatList) {
                switch (sheetname.split('-')[0]) {
                    case '线材汇总':
                        createNewPurchasingSheet('线材汇总', headerText);
                        break;
                    case '板材汇总':
                        createNewPurchasingSheet('板材汇总', headerText);
                        break;
                    case '管材汇总':
                        createNewPurchasingSheet('管材汇总', headerText);
                        break;
                    case '五金汇总':
                        createNewPurchasingSheet('五金汇总', headerText);
                        break;
                    case '包装物料汇总':
                        createNewPurchasingSheet('包装物料汇总', headerText);
                        break;
                }
            }
            // 添加子分类
            for (let sheetname of materialCatList) {
                createNewPurchasingSheet(sheetname.split('-')[1], headerText);
            }
        }

        // 准备数据
        let createType = null; // 单据来源 aps 或 add
        let purchasingStatus = null;
        let orderNo = null;
        let orderDate = null;
        let productCat = null;
        let purchasingTotal = null;
        let purchasingCurrent = null;
        let materialCode = null;
        let materialName = null;
        let materialCat = null;
        let materialSize = null;
        let materialAlias = null;
        let taxPrice = null;
        let materialUnit = null;
        let materialCaq = null;
        let materialCqd = null;
        let unitPrice = null;
        let warehouse = null;
        let taxRate = null;
        let SCD = null;

        for (let index = 0; index < window.purchasingData[0].length; index++) {
            switch (window.purchasingData[0][index].v) {
                case '单据来源':
                    createType = window.purchasingData.map(x => x[index]);
                    break;
                case '仓库选择':
                    warehouse = window.purchasingData.map(x => x[index]);
                    break;
                case '状态':
                    purchasingStatus = window.purchasingData.map(x => x[index]);
                    break;
                case '采购物料别名':
                    materialAlias = window.purchasingData.map(x => x[index]);
                    break;
                case '税后总金额':
                    taxPrice = window.purchasingData.map(x => x[index]);
                    break;
                case '订单号':
                    orderNo = window.purchasingData.map(x => x[index]);
                    break;
                case '单价':
                    unitPrice = window.purchasingData.map(x => x[index]);
                    break;
                case '税率':
                    taxRate = window.purchasingData.map(x => x[index]);
                    break;
                case '生产单号':
                    SCD = window.purchasingData.map(x => x[index]);
                    break;
                case '订单单据日期':
                    orderDate = window.purchasingData.map(x => x[index]);
                    break;
                case '商品分类名称':
                    productCat = window.purchasingData.map(x => x[index]);
                    break;
                case '总采购数量':
                    purchasingTotal = window.purchasingData.map(x => x[index]);
                    break;
                case '本次下达量':
                    purchasingCurrent = window.purchasingData.map(x => x[index]);
                    break;
                case '采购物料':
                    materialName = window.purchasingData.map(x => x[index]);
                    break;
                case '采购物料分类名称':
                    materialCat = window.purchasingData.map(x => x[index]);
                    break;
                case '物料规格':
                    materialSize = window.purchasingData.map(x => x[index]);
                    break;
                case '采购单位':
                    materialUnit = window.purchasingData.map(x => x[index]);
                    break;
                case '采购物料可用量':
                    materialCaq = window.purchasingData.map(x => x[index]);
                    break;
                case '采购物料现存量':
                    materialCqd = window.purchasingData.map(x => x[index]);
                    break;
                case '采购物料编码':
                    materialCode = window.purchasingData.map(x => x[index]);
                    break;
                default:
            }
        }

        // 填入数据到汇报总表
        {
            let aoa = window.purchasingListObject["采购汇总报表"]['xlsx'].data;
            let aoaIndexMap = ['物料编码'];
            let aoaLeadingRowNum = 1;
            let aoaCreateType = headerText.indexOf('单据来源');
            let aoaOrderNo = headerText.indexOf('订单号');
            let aoaSCD = headerText.indexOf('生产单号');
            // let aoaOrderDate = headerText.indexOf('订单单据日期');
            let aoaProductCat = headerText.indexOf('商品分类名称');
            let aoaPurchasingTotal = headerText.indexOf('总采购数量');
            let aoaPurchasingCurrent = headerText.indexOf('系统下达量');
            let aoaMaterialName = headerText.indexOf('采购物料');
            let aoaMaterialCode = headerText.indexOf('物料编码');
            let aoaMaterialCat = headerText.indexOf('物料分类名称');
            let aoaMaterialSize = headerText.indexOf('物料规格');
            let aoaMaterialUnit = headerText.indexOf('采购单位');
            let aoaStatus = headerText.indexOf('状态');
            let aoaMaterialAlias = headerText.indexOf('物料别名');
            let aoaTaxPrice = headerText.indexOf('税后总金额');
            let aoaMaterialCaq = headerText.indexOf('物料可用量');
            let aoaOperation = headerText.indexOf('网页操作');
            let aoaTaxRate = headerText.indexOf('税率');
            let aoaPurchasingReal = headerText.indexOf('下单数');
            let aoaMaterialCqd = headerText.indexOf('物料现存量');
            let aoaUnitPrice = headerText.indexOf('单价');
            let aoaWarehouse = headerText.indexOf('仓库');
            let aoaTotalPrice = headerText.indexOf('总金额');
            // let aoaReqMinusCaq = headerText.indexOf('下达量减可用量');
            // let aoaReqMinusCqd = headerText.indexOf('下达量减现存量');

            for (let rowNum = 1; rowNum < purchasingData.length; rowNum++) {
                if (purchasingStatus[rowNum].v == '异常' || purchasingStatus[rowNum].v == '已完成') {
                    continue;
                }

                // working aoaRow
                let aoaRow = aoaIndexMap.indexOf(materialCode[rowNum].v);
                if (aoaRow < 0 || createType[rowNum].v == "手工新增") {
                    aoa[aoaLeadingRowNum] = [];
                    aoaIndexMap.push(materialCode[rowNum].v)
                    aoaRow = aoaLeadingRowNum;
                    aoaLeadingRowNum++;
                    aoa[aoaRow][aoaCreateType] = structuredClone(createType[rowNum]);
                    aoa[aoaRow][aoaOrderNo] = structuredClone(orderNo[rowNum]);
                    // aoa[aoaRow][aoaOrderDate] = structuredClone(orderDate[rowNum]);
                    aoa[aoaRow][aoaProductCat] = structuredClone(productCat[rowNum]);
                    aoa[aoaRow][aoaSCD] = structuredClone(SCD[rowNum]);
                    aoa[aoaRow][aoaPurchasingTotal] = structuredClone(purchasingTotal[rowNum]);
                    aoa[aoaRow][aoaPurchasingCurrent] = structuredClone(purchasingCurrent[rowNum]);
                    aoa[aoaRow][aoaMaterialName] = structuredClone(materialName[rowNum]);
                    aoa[aoaRow][aoaMaterialCode] = structuredClone(materialCode[rowNum]);
                    aoa[aoaRow][aoaTaxRate] = structuredClone(taxRate[rowNum]);
                    aoa[aoaRow][aoaMaterialCat] = structuredClone(materialCat[rowNum]);
                    aoa[aoaRow][aoaTaxPrice] = structuredClone(taxPrice[rowNum]);
                    // aoa[aoaRow][aoaMaterialAlias] = structuredClone(materialAlias[rowNum]);
                    aoa[aoaRow][aoaMaterialSize] = structuredClone(materialSize[rowNum]);
                    aoa[aoaRow][aoaWarehouse] = structuredClone(warehouse[rowNum]);
                    aoa[aoaRow][aoaMaterialUnit] = structuredClone(materialUnit[rowNum]);
                    aoa[aoaRow][aoaMaterialCaq] = structuredClone(materialCaq[rowNum]);
                    aoa[aoaRow][aoaMaterialCqd] = structuredClone(materialCqd[rowNum]);
                    aoa[aoaRow][aoaStatus] = structuredClone(purchasingStatus[rowNum]);
                    aoa[aoaRow][aoaUnitPrice] = structuredClone(unitPrice[rowNum]);
                } else {
                    aoa[aoaRow][aoaPurchasingTotal].v = +aoa[aoaRow][aoaPurchasingTotal].v + +purchasingTotal[rowNum].v;
                    aoa[aoaRow][aoaPurchasingCurrent].v = +aoa[aoaRow][aoaPurchasingCurrent].v + +purchasingCurrent[rowNum].v;
                    if (!aoa[aoaRow][aoaSCD].v.includes(SCD[rowNum]))
                        aoa[aoaRow][aoaSCD].v = aoa[aoaRow][aoaSCD].v.concat(',', SCD[rowNum].v);
                    if (!aoa[aoaRow][aoaOrderNo].v.includes(orderNo[rowNum].v)) {
                        aoa[aoaRow][aoaOrderNo].v = aoa[aoaRow][aoaOrderNo].v.concat(',', orderNo[rowNum].v)
                    }
                }
                // aoa[aoaRow][aoaReqMinusCaq] = {
                //     't': 'n',
                //     'v': 'v'
                // };
            }
            for (let i = 1; i < aoa.length; i++) {

                // 实际采购数(下单数)
                aoa[i][aoaPurchasingReal] = structuredClone(aoa[i][aoaMaterialCaq]);
                aoa[i][aoaPurchasingReal].t = 'n';
                if (+aoa[i][aoaPurchasingReal].v < 0) {
                    aoa[i][aoaPurchasingReal].v = (0 - +aoa[i][aoaPurchasingReal].v).toString();
                } else aoa[i][aoaPurchasingReal].v = 0;

                // 下单数减下达量
                let aoaRealMinusReq = headerText.indexOf("下单数减下达量")
                aoa[i][aoaRealMinusReq] = {
                    't': 'n',
                    'v': 0,
                    'f': '',
                };
                const buildRMR = String.fromCharCode(aoaPurchasingReal + 65);
                aoa[i][aoaRealMinusReq].f = buildRMR.concat(i + 1, '-', String.fromCharCode(aoaPurchasingCurrent + 65), i + 1);
                // 填总价
                aoa[i][aoaTotalPrice] = {
                    't': 'n',
                    'v': 0,
                    'f': '',
                };
                const buildFunc = String.fromCharCode(aoaUnitPrice + 65);
                aoa[i][aoaTotalPrice].f = buildFunc.concat(i + 1, '*', String.fromCharCode(aoaPurchasingReal + 65), i + 1);
                // 填税后总价
                aoa[i][aoaTaxPrice] = {
                    't': 'n',
                    'v': 0,
                    'f': '',
                };
                const buildTaxFunc = String.fromCharCode(aoaTotalPrice + 65);
                aoa[i][aoaTaxPrice].f = buildTaxFunc.concat(i + 1, '*', String.fromCharCode(aoaTaxRate + 65), i + 1, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), i + 1);

                // // 填入下达量减可用量
                // aoa[i][aoaReqMinusCaq] = {
                //     't': 'n',
                //     'v': 0
                // };
                // let CaqMinusReq = +aoa[i][aoaPurchasingCurrent].v - +aoa[i][aoaMaterialCaq].v;
                // aoa[i][aoaReqMinusCaq].v = (CaqMinusReq > 0) ? CaqMinusReq : '0';
                // // 填入下达量减现存量
                // aoa[i][aoaReqMinusCqd] = {
                //     't': 'n',
                //     'v': 0
                // };
                // let CqdMinusReq = +aoa[i][aoaPurchasingCurrent].v - +aoa[i][aoaMaterialCqd].v;
                // aoa[i][aoaReqMinusCqd].v = (CqdMinusReq > 0) ? CqdMinusReq : '0';
                // 填入网页操作
                aoa[i][aoaOperation] = {
                    't': 's',
                    'v': 0
                };
                if (aoa[i][aoaPurchasingReal].v == 0)
                    aoa[i][aoaOperation].v = '❗转完成';
                else if (aoa[i][aoaPurchasingReal].v != aoa[i][aoaPurchasingCurrent].v)
                    aoa[i][aoaOperation].v = '❗修改下达量';
                else
                    aoa[i][aoaOperation].v = '✅直接下达';
            }

        }

        // 采购入库单
        {
            let aoa = window.purchasingListObject["采购入库单"]['xlsx'].data;
            let aoaOrig = window.purchasingListObject["采购汇总报表"]['xlsx'].data;
            const buildFunc = String.fromCharCode(warehouseHeaderText.indexOf("单价") + 65);
            const buildUnitPrice = '采购汇总报表!' + String.fromCharCode(headerText.indexOf("单价") + 65);
            const buildCount = '采购汇总报表!' + String.fromCharCode(headerText.indexOf("下单数") + 65);
            for (let i = 1; i < aoaOrig.length; i++) {
                aoa[i] = [];
                aoa[i][warehouseHeaderText.indexOf('商品分类名称')] = structuredClone(aoaOrig[i][headerText.indexOf("商品分类名称")]);
                aoa[i][warehouseHeaderText.indexOf('订单号')] = structuredClone(aoaOrig[i][headerText.indexOf("订单号")]);
                aoa[i][warehouseHeaderText.indexOf('仓库')] = structuredClone(aoaOrig[i][headerText.indexOf("仓库")]);
                aoa[i][warehouseHeaderText.indexOf('采购物料')] = structuredClone(aoaOrig[i][headerText.indexOf("采购物料")]);
                aoa[i][warehouseHeaderText.indexOf('物料规格')] = structuredClone(aoaOrig[i][headerText.indexOf("物料规格")]);
                aoa[i][warehouseHeaderText.indexOf('物料别名')] = structuredClone(aoaOrig[i][headerText.indexOf("物料别名")]);
                aoa[i][warehouseHeaderText.indexOf('采购数')] = structuredClone(aoaOrig[i][headerText.indexOf("下单数")]);
                aoa[i][warehouseHeaderText.indexOf('入库数')] = structuredClone(aoaOrig[i][headerText.indexOf("下单数")]);
                aoa[i][warehouseHeaderText.indexOf('采购单位')] = structuredClone(aoaOrig[i][headerText.indexOf("采购单位")]);
                aoa[i][warehouseHeaderText.indexOf('供应商')] = structuredClone(aoaOrig[i][headerText.indexOf("供应商")]);
                aoa[i][warehouseHeaderText.indexOf('单价')] = structuredClone(aoaOrig[i][headerText.indexOf("单价")]);
                aoa[i][warehouseHeaderText.indexOf('物料编码')] = structuredClone(aoaOrig[i][headerText.indexOf("物料编码")]);
                aoa[i][warehouseHeaderText.indexOf('单据来源')] = structuredClone(aoaOrig[i][headerText.indexOf("单据来源")]);
                aoa[i][warehouseHeaderText.indexOf('物料现存量')] = structuredClone(aoaOrig[i][headerText.indexOf("物料现存量")]);
                aoa[i][warehouseHeaderText.indexOf('入库数减采购数')] = structuredClone(aoaOrig[i][headerText.indexOf("下单数")]);
                aoa[i][warehouseHeaderText.indexOf('物料分类名称')] = structuredClone(aoaOrig[i][headerText.indexOf("物料分类名称")]);
                aoa[i][warehouseHeaderText.indexOf('物料分类名称')] = aoa[i][warehouseHeaderText.indexOf('物料分类名称')].v.split('-')[1];
                aoa[i][warehouseHeaderText.indexOf('总金额')] = structuredClone(aoaOrig[i][headerText.indexOf("总金额")]);
                // 公式
                aoa[i][warehouseHeaderText.indexOf('采购数')].f = buildCount.concat(i + 1);
                aoa[i][warehouseHeaderText.indexOf('入库数')].f = buildCount.concat(i + 1);
                aoa[i][warehouseHeaderText.indexOf('单价')].f = buildUnitPrice.concat(i + 1);
                aoa[i][warehouseHeaderText.indexOf('总金额')].f = buildFunc.concat(i + 1, '*', String.fromCharCode(warehouseHeaderText.indexOf('入库数') + 65), i + 1);
            }
            const buildInboundModifier = String.fromCharCode(warehouseHeaderText.indexOf('入库数') + 65);
            for (let i = 1; i < aoa.length; i++) {
                aoa[i][warehouseHeaderText.indexOf('入库数减采购数')].f = buildInboundModifier.concat(i + 1, '-', String.fromCharCode(warehouseHeaderText.indexOf('采购数') + 65), i + 1);
            }
        }

        // 填入数据到订单汇总表
        {
            var orderNoArr = [];
            for (let order of orderNo) {
                if (orderNoArr.indexOf(order.v) < 0)
                    orderNoArr.push(order.v);
            }
            let aoa = window.purchasingListObject["订单汇总"]['xlsx'].data;
            let aoaIndexMap = ['物料编码'];
            let aoaLeadingRowNum = 1;
            let aoaLeadingRowOrder = 1;
            let aoaCreateType = headerText.indexOf('单据来源');
            let aoaOrderNo = headerText.indexOf('订单号');
            let aoaSCD = headerText.indexOf('生产单号');
            let aoaOrderDate = headerText.indexOf('订单单据日期');
            let aoaProductCat = headerText.indexOf('商品分类名称');
            let aoaPurchasingTotal = headerText.indexOf('总采购数量');
            let aoaPurchasingCurrent = headerText.indexOf('系统下达量');
            let aoaMaterialName = headerText.indexOf('采购物料');
            let aoaPurchasingReal = headerText.indexOf('下单数');
            let aoaMaterialCode = headerText.indexOf('物料编码');
            let aoaMaterialCat = headerText.indexOf('物料分类名称');
            let aoaMaterialSize = headerText.indexOf('物料规格');
            let aoaMaterialUnit = headerText.indexOf('采购单位');
            let aoaTaxPrice = headerText.indexOf('税后总金额');
            let aoaTaxRate = headerText.indexOf('税率');
            let aoaWarehouse = headerText.indexOf('仓库');
            let aoaMaterialCaq = headerText.indexOf('物料可用量');
            let aoaMaterialCqd = headerText.indexOf('物料现存量');
            let aoaStatus = headerText.indexOf('状态');
            let aoaMaterialAlias = headerText.indexOf('物料别名');
            let aoaUnitPrice = headerText.indexOf('单价');
            let aoaOperation = headerText.indexOf('网页操作');
            let aoaReqMinusCaq = headerText.indexOf('下达量减可用量');
            let aoaReqMinusCqd = headerText.indexOf('下达量减现存量');
            let aoaTotalPrice = headerText.indexOf('总金额');

            const buildUnitPrice = '采购汇总报表!' + String.fromCharCode(aoaUnitPrice + 65);
            const buildOperation = '采购汇总报表!' + String.fromCharCode(aoaOperation + 65);
            const buildTaxRate = '采购汇总报表!' + String.fromCharCode(aoaTaxRate + 65);
            let summaryTableCodeCols = structuredClone(window.purchasingListObject["采购汇总报表"]['xlsx'].data.map(x => x[aoaMaterialCode]));
            for (let i = 0; i < summaryTableCodeCols.length; i++)
                summaryTableCodeCols[i] = summaryTableCodeCols[i].v;
            for (let orderNoValue of orderNoArr) {
                for (let rowNum = 1; rowNum < purchasingData.length; rowNum++) {
                    if (orderNoValue != orderNo[rowNum].v)
                        continue;

                    if (purchasingStatus[rowNum].v == '异常' || purchasingStatus[rowNum].v == '已完成') {
                        continue;
                    }
                    // working aoaRow
                    let aoaRow = -1;
                    for (let i = aoaLeadingRowOrder; i < aoaLeadingRowNum; i++)
                        if (aoa[i][aoaMaterialCode].v == materialCode[rowNum].v)
                            aoaRow = i;
                    if (aoaRow < 0 || createType[rowNum].v == "手工新增") {
                        aoa[aoaLeadingRowNum] = [];
                        aoaIndexMap.push(materialCode[rowNum].v)
                        aoaRow = aoaLeadingRowNum;
                        aoaLeadingRowNum++;
                        aoa[aoaRow][aoaCreateType] = structuredClone(createType[rowNum]);
                        aoa[aoaRow][aoaOrderNo] = structuredClone(orderNo[rowNum]);
                        aoa[aoaRow][aoaOrderDate] = structuredClone(orderDate[rowNum]);
                        // aoa[aoaRow][aoaMaterialAlias] = structuredClone(materialAlias[rowNum]);
                        aoa[aoaRow][aoaProductCat] = structuredClone(productCat[rowNum]);
                        aoa[aoaRow][aoaWarehouse] = structuredClone(warehouse[rowNum]);
                        aoa[aoaRow][aoaTaxPrice] = structuredClone(taxPrice[rowNum]);
                        aoa[aoaRow][aoaTaxRate] = structuredClone(taxRate[rowNum]);
                        aoa[aoaRow][aoaSCD] = structuredClone(SCD[rowNum]);
                        aoa[aoaRow][aoaPurchasingTotal] = structuredClone(purchasingTotal[rowNum]);
                        aoa[aoaRow][aoaPurchasingCurrent] = structuredClone(purchasingCurrent[rowNum]);
                        aoa[aoaRow][aoaMaterialName] = structuredClone(materialName[rowNum]);
                        aoa[aoaRow][aoaMaterialCode] = structuredClone(materialCode[rowNum]);
                        aoa[aoaRow][aoaMaterialCat] = structuredClone(materialCat[rowNum]);
                        aoa[aoaRow][aoaMaterialSize] = structuredClone(materialSize[rowNum]);
                        aoa[aoaRow][aoaMaterialUnit] = structuredClone(materialUnit[rowNum]);
                        aoa[aoaRow][aoaMaterialCaq] = structuredClone(materialCaq[rowNum]);
                        aoa[aoaRow][aoaMaterialCqd] = structuredClone(materialCqd[rowNum]);
                        aoa[aoaRow][aoaStatus] = structuredClone(purchasingStatus[rowNum]);
                        aoa[aoaRow][aoaUnitPrice] = {
                            't': 'n',
                            'v': 0,
                            'f': '',
                        };
                        let codeRow = summaryTableCodeCols.indexOf(aoa[aoaRow][aoaMaterialCode].v);
                        aoa[aoaRow][aoaUnitPrice].f = buildUnitPrice.concat(codeRow + 1);
                        aoa[aoaRow][aoaTaxRate].f = buildTaxRate.concat(codeRow + 1);
                        // 填入网页操作
                        aoa[aoaRow][aoaOperation] = {
                            't': 's',
                            'v': 0
                        };
                        aoa[aoaRow][aoaOperation].f = buildOperation.concat(codeRow + 1);
                    } else {
                        aoa[aoaRow][aoaPurchasingTotal].v = +aoa[aoaRow][aoaPurchasingTotal].v + +purchasingTotal[rowNum].v;
                        aoa[aoaRow][aoaPurchasingCurrent].v = +aoa[aoaRow][aoaPurchasingCurrent].v + +purchasingCurrent[rowNum].v;
                        if (!aoa[aoaRow][aoaSCD].v.includes(SCD[rowNum]))
                            aoa[aoaRow][aoaSCD].v = aoa[aoaRow][aoaSCD].v.concat(',', SCD[rowNum].v);
                    }
                }
                aoaLeadingRowOrder = aoaLeadingRowNum;
            }

            for (let i = 1; i < aoa.length; i++) {
                // 实际采购数(下单数)
                aoa[i][aoaPurchasingReal] = structuredClone(aoa[i][aoaMaterialCaq]);
                aoa[i][aoaPurchasingReal].t = 'n';
                if (+aoa[i][aoaPurchasingReal].v < 0) {
                    aoa[i][aoaPurchasingReal].v = -1;
                } else aoa[i][aoaPurchasingReal].v = 0;

                // 填入总金额
                aoa[i][aoaTotalPrice] = {
                    't': 'n',
                    'v': 0,
                    'f': '',
                };
                const buildFunc = String.fromCharCode(aoaUnitPrice + 65);
                aoa[i][aoaTotalPrice].f = buildFunc.concat(i + 1, '*', String.fromCharCode(aoaPurchasingReal + 65), i + 1);

                // 填税后总价
                aoa[i][aoaTaxPrice] = {
                    't': 'n',
                    'v': 0,
                    'f': '',
                };
                const buildTaxFunc = String.fromCharCode(aoaTotalPrice + 65);
                aoa[i][aoaTaxPrice].f = buildTaxFunc.concat(i + 1, '*', String.fromCharCode(aoaTaxRate + 65), i + 1, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), i + 1);


                // // 填入下达量减可用量
                // aoa[i][aoaReqMinusCaq] = {
                //     't': 'n',
                //     'v': 0
                // };
                // let CaqMinusReq = +aoa[i][aoaPurchasingCurrent].v - +aoa[i][aoaMaterialCaq].v;
                // aoa[i][aoaReqMinusCaq].v = (CaqMinusReq > 0) ? CaqMinusReq : -1;
                // // 填入下达量减现存量
                // aoa[i][aoaReqMinusCqd] = {
                //     't': 'n',
                //     'v': 0
                // };
                // let CqdMinusReq = +aoa[i][aoaPurchasingCurrent].v - +aoa[i][aoaMaterialCqd].v;
                // aoa[i][aoaReqMinusCqd].v = (CqdMinusReq > 0) ? CqdMinusReq : -1;
            }
        }


        // 按物料分类拆分
        {
            let aoaTotal = window.purchasingListObject["采购汇总报表"]['xlsx'].data;
            let totalCatCol = headerText.indexOf('物料分类名称');
            for (let totalRow = 1; totalRow < aoaTotal.length; totalRow++) {
                let catName = aoaTotal[totalRow][totalCatCol].v.split('-')[0];
                // let orderNo = aoaTotal[totalRow][orderNoCol].v;
                let subCatName = aoaTotal[totalRow][totalCatCol].v.split('-')[1];
                let aoaReqMinusCaq = headerText.indexOf('下达量减可用量');
                let aoaReqMinusCqd = headerText.indexOf('下达量减现存量');
                let aoaRealMinusReq = headerText.indexOf("下单数减下达量")
                let aoaPurchasingCurrent = headerText.indexOf('系统下达量');
                let aoaTotalPrice = headerText.indexOf('总金额');
                let aoaPurchasingReal = headerText.indexOf('下单数');
                let aoaUnitPrice = headerText.indexOf('单价');
                let aoaTaxRate = headerText.indexOf('税率');
                let aoaTaxPrice = headerText.indexOf('税后总金额');
                const buildFunc = String.fromCharCode(aoaUnitPrice + 65);
                const buildUnitPrice = '采购汇总报表!' + String.fromCharCode(aoaUnitPrice + 65);
                const buildCount = '采购汇总报表!' + String.fromCharCode(aoaPurchasingReal + 65);
                const buildTaxFunc = String.fromCharCode(aoaTotalPrice + 65);
                const buildTaxRate = '采购汇总报表!' + String.fromCharCode(aoaTaxRate + 65);
                const buildRMR = String.fromCharCode(aoaPurchasingReal + 65);
                // 填入子分类
                let target = window.purchasingListObject[subCatName]['xlsx'].data;
                target[target.length] = structuredClone(aoaTotal[totalRow]);
                target[target.length - 1][aoaTotalPrice].f = buildFunc.concat(target.length, '*', String.fromCharCode(aoaPurchasingReal + 65), target.length);
                target[target.length - 1][aoaUnitPrice].f = buildUnitPrice.concat(totalRow + 1);
                target[target.length - 1][aoaPurchasingReal].f = buildCount.concat(totalRow + 1);
                target[target.length - 1][aoaTaxRate].f = buildTaxRate.concat(totalRow + 1);
                target[target.length - 1][aoaTaxPrice].f = buildTaxFunc.concat(target.length, '*', String.fromCharCode(aoaTaxRate + 65), target.length, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), target.length);
                target[target.length - 1][aoaRealMinusReq].f = buildRMR.concat(target.length, '-', String.fromCharCode(aoaPurchasingCurrent + 65), target.length);


                // 填入大分类汇总表
                switch (catName) {
                    case '线材汇总':
                        target = window.purchasingListObject['线材汇总']['xlsx'].data;
                        target[target.length] = structuredClone(aoaTotal[totalRow]);
                        target[target.length - 1][aoaTotalPrice].f = buildFunc.concat(target.length, '*', String.fromCharCode(aoaPurchasingReal + 65), target.length);
                        target[target.length - 1][aoaUnitPrice].f = buildUnitPrice.concat(totalRow + 1);
                        target[target.length - 1][aoaPurchasingReal].f = buildCount.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxRate].f = buildTaxRate.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxPrice].f = buildTaxFunc.concat(target.length, '*', String.fromCharCode(aoaTaxRate + 65), target.length, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), target.length);
                        target[target.length - 1][aoaRealMinusReq].f = buildRMR.concat(target.length, '-', String.fromCharCode(aoaPurchasingCurrent + 65), target.length);
                        break;
                    case '板材汇总':
                        target = window.purchasingListObject['板材汇总']['xlsx'].data;
                        target[target.length] = structuredClone(aoaTotal[totalRow]);
                        target[target.length - 1][aoaTotalPrice].f = buildFunc.concat(target.length, '*', String.fromCharCode(aoaPurchasingReal + 65), target.length);
                        target[target.length - 1][aoaUnitPrice].f = buildUnitPrice.concat(totalRow + 1);
                        target[target.length - 1][aoaPurchasingReal].f = buildCount.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxRate].f = buildTaxRate.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxPrice].f = buildTaxFunc.concat(target.length, '*', String.fromCharCode(aoaTaxRate + 65), target.length, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), target.length);
                        target[target.length - 1][aoaRealMinusReq].f = buildRMR.concat(target.length, '-', String.fromCharCode(aoaPurchasingCurrent + 65), target.length);
                        break;
                    case '管材汇总':
                        target = window.purchasingListObject['管材汇总']['xlsx'].data;
                        target[target.length] = structuredClone(aoaTotal[totalRow]);
                        target[target.length - 1][aoaTotalPrice].f = buildFunc.concat(target.length, '*', String.fromCharCode(aoaPurchasingReal + 65), target.length);
                        target[target.length - 1][aoaUnitPrice].f = buildUnitPrice.concat(totalRow + 1);
                        target[target.length - 1][aoaPurchasingReal].f = buildCount.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxRate].f = buildTaxRate.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxPrice].f = buildTaxFunc.concat(target.length, '*', String.fromCharCode(aoaTaxRate + 65), target.length, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), target.length);
                        target[target.length - 1][aoaRealMinusReq].f = buildRMR.concat(target.length, '-', String.fromCharCode(aoaPurchasingCurrent + 65), target.length);
                        break;
                    case '五金汇总':
                        target = window.purchasingListObject['五金汇总']['xlsx'].data;
                        target[target.length] = structuredClone(aoaTotal[totalRow]);
                        target[target.length - 1][aoaTotalPrice].f = buildFunc.concat(target.length, '*', String.fromCharCode(aoaPurchasingReal + 65), target.length);
                        target[target.length - 1][aoaUnitPrice].f = buildUnitPrice.concat(totalRow + 1);
                        target[target.length - 1][aoaPurchasingReal].f = buildCount.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxRate].f = buildTaxRate.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxPrice].f = buildTaxFunc.concat(target.length, '*', String.fromCharCode(aoaTaxRate + 65), target.length, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), target.length);
                        target[target.length - 1][aoaRealMinusReq].f = buildRMR.concat(target.length, '-', String.fromCharCode(aoaPurchasingCurrent + 65), target.length);
                        break;
                    case '包装物料汇总':
                        target = window.purchasingListObject['包装物料汇总']['xlsx'].data;
                        target[target.length] = structuredClone(aoaTotal[totalRow]);
                        target[target.length - 1][aoaTotalPrice].f = buildFunc.concat(target.length, '*', String.fromCharCode(aoaPurchasingReal + 65), target.length);
                        target[target.length - 1][aoaUnitPrice].f = buildUnitPrice.concat(totalRow + 1);
                        target[target.length - 1][aoaPurchasingReal].f = buildCount.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxRate].f = buildTaxRate.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxPrice].f = buildTaxFunc.concat(target.length, '*', String.fromCharCode(aoaTaxRate + 65), target.length, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), target.length);
                        target[target.length - 1][aoaRealMinusReq].f = buildRMR.concat(target.length, '-', String.fromCharCode(aoaPurchasingCurrent + 65), target.length);
                        break;
                    default:
                        target = window.purchasingListObject['其他分类物料汇总']['xlsx'].data;
                        target[target.length] = structuredClone(aoaTotal[totalRow]);
                        target[target.length - 1][aoaTotalPrice].f = buildFunc.concat(target.length, '*', String.fromCharCode(aoaPurchasingReal + 65), target.length);
                        target[target.length - 1][aoaUnitPrice].f = buildUnitPrice.concat(totalRow + 1);
                        target[target.length - 1][aoaPurchasingReal].f = buildCount.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxRate].f = buildTaxRate.concat(totalRow + 1);
                        target[target.length - 1][aoaTaxPrice].f = buildTaxFunc.concat(target.length, '*', String.fromCharCode(aoaTaxRate + 65), target.length, '*0.01+', String.fromCharCode(aoaTotalPrice + 65), target.length);
                        target[target.length - 1][aoaRealMinusReq].f = buildRMR.concat(target.length, '-', String.fromCharCode(aoaPurchasingCurrent + 65), target.length);
                        break;
                }
                // 更新公式
            }
        }

        // export xlsx
        var sheetnames = []
        var dataArr = [];
        for (let obj in window.purchasingListObject) {
            sheetnames.push(obj);
            dataArr.push(window.purchasingListObject[obj]['xlsx'].data);
        }
        // exportSheetsUtil(dataArr, "生成采购报表" + (new Date()).toLocaleTimeString(), sheetnames);
        exportFormulaSheet(dataArr, "生成采购报表" + (new Date()).toLocaleTimeString(), sheetnames);
    }

    // Util 函数
    function addGlobalStyle(css) {
        var head, style;
        head = document.getElementsByTagName('head')[0];
        if (!head) {
            return;
        }
        style = document.createElement('style');
        // style.type = 'text/css';
        style.innerHTML = css;
        head.appendChild(style);
    }

    function exportFormulaSheet(data, filename, sheetnames) {
        var wb = XLSX.utils.book_new();
        if (!wb.Props) wb.Props = {};
        for (let i = 0; i < data.length; i++) {
            let ws = XLSX.utils.aoa_to_sheet(data[i]);
            XLSX.utils.book_append_sheet(wb, ws, sheetnames[i]);
        }
        XLSX.writeFile(wb, filename.concat('.xlsx'));
    }

    function clickAlert(name) {
        alert("clicked " + name);
    }

    addGlobalStyle(`/* Dropdown Button */
        .dropbtn {
            background-color: #04AA6D;
            color: white;
            padding: 16px;
            font-size: 16px;
            border: none;
        }

        /* The container <div> - needed to position the dropdown content */
        .dropdown {
            position: relative;
            display: inline-block;
        }

        /* Dropdown Content (Hidden by Default) */
        .dropdown-content {
            display: none;
            position: absolute;
            background-color: #f1f1f1;
            min-width: 160px;
            box-shadow: 0px 8px 16px 0px rgba(0, 0, 0, 0.2);
            z-index: 1;
        }

        /* Buttons inside the dropdown */
        .dropdown-content button {
            background-color: #4CAF50;
            border: none;
            color: white;
            padding: 6px 6px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            border-radius: 12px;
        }

        /* Change color of dropdown buttons on hover */
        .dropdown-content button:hover {
            background-color: #008CBA;
        }

        /* Show the dropdown menu on hover */
        .dropdown:hover .dropdown-content {
            z-index: 300;
            position: absolute;
            display: block;
        }

        /* Change the background color of the dropdown button when the dropdown content is shown */
        .dropdown:hover .dropbtn {
            background-color: #3e8e41;
        }`);
})();