wps脚本

// ================ 通用工具函数 ================
/**
 * 延迟函数（毫秒）
 * @param {number} ms 毫秒数
 */
function delay(ms) {
    var start = new Date().getTime();
    while (new Date().getTime() < start + ms) {}
}

/**
 * 将列号转换为字母
 * @param {number} column 列号
 * @returns {string} 列字母
 */
function columnToLetter(column) {
    var temp, letter = '';
    while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
    }
    return letter;
}

/**
 * 验证当前工作表名称
 * @param {string|Array} expectedSheetNames 预期的工作表名称或数组
 * @returns {boolean} 是否验证通过
 */
function validateSheet(expectedSheetNames) {
    var sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    
    // 如果是字符串数组，检查是否在数组中
    if (Array.isArray(expectedSheetNames)) {
        if (!expectedSheetNames.includes(sheetName)) {
            MsgBox("当前工作表不是【" + expectedSheetNames.join("】或【") + "】表，请切换到正确的工作表！");
            return false;
        }
        return true;
    }
    
    // 如果是单个字符串
    if (sheetName !== expectedSheetNames) {
        MsgBox("当前工作表不是【" + expectedSheetNames + "】表，请切换到正确的工作表！");
        return false;
    }
    return true;
}

/**
 * 获取所有选中的单元格
 * @returns {Array|null} 选中单元格数组或null（无选择）
 */
function getAllSelectedCells() {
    var selection = Application.Selection;
    if (selection.Count === 0) {
        alert("请选择至少一个单元格");
        return null;
    }
    
    var selectedCells = [];
    var areas = selection.Areas;
    for (var a = 1; a <= areas.Count; a++) {
        var area = areas.Item(a);
        for (var i = 1; i <= area.Count; i++) {
            selectedCells.push(area.Item(i));
        }
    }
    return selectedCells;
}

/**
 * 显示处理结果消息
 * @param {number} successCount 成功数量
 * @param {number} failCount 失败数量
 * @param {Array} failMessages 失败消息数组
 */
function showResultMessage(successCount, failCount, failMessages) {
    var resultMessage = "处理完成:\n";
    resultMessage += "成功: " + successCount + " 个\n";
    resultMessage += "失败: " + failCount + " 个";
    
    if (failCount > 0 && failMessages.length > 0) {
        resultMessage += "\n\n失败详情:\n" + failMessages.join("\n");
    }
    
    alert(resultMessage);
}

/**
 * 在上方搜索最近匹配项
 * @param {object} sheet 工作表对象
 * @param {string} searchKey 搜索关键字
 * @param {number} startRow 起始行
 * @param {number} column 搜索列
 * @returns {object|null} 找到的单元格或null
 */
function findClosestMatchAbove(sheet, searchKey, startRow, column) {
    var foundCell = null;
    var closestDistance = Infinity;
    
    for (var row = startRow - 1; row >= 1; row--) {
        var cell = sheet.Cells.Item(row, column);
        if (cell.Text == searchKey) {
            var distance = startRow - row;
            if (distance < closestDistance) {
                closestDistance = distance;
                foundCell = cell;
            }
        }
    }
    return foundCell;
}

/**
 * 批量处理优化函数
 * @param {Function} processingFunc 处理函数
 * @param {string} errorMessage 错误消息
 */
function batchProcessing(processingFunc, errorMessage) {
    Application.ScreenUpdating = false;
    Application.Calculation = -4135; // xlCalculationManual
    Application.EnableEvents = false;
    
    try {
        processingFunc();
    } catch (error) {
        MsgBox(errorMessage + ": " + error.message);
        throw error;
    } finally {
        Application.ScreenUpdating = true;
        Application.Calculation = -4105; // xlCalculationAutomatic
        Application.EnableEvents = true;
    }
}

/**
 * 从日期字符串中提取"日"部分
 * @param {string} dateString 日期字符串
 * @returns {number} 日期号
 */
function getDayFromDate(dateString) {
    if (!dateString || dateString === "") return 0;
    
    // 处理 "2025/8/20" 格式
    var parts = dateString.split('/');
    if (parts.length >= 3) {
        return parseInt(parts[2]);
    }
    
    // 处理 "2025-8-20" 格式
    parts = dateString.split('-');
    if (parts.length >= 3) {
        return parseInt(parts[2]);
    }
    
    return 0;
}

/**
 * 检查值是否为零
 * @param {string} value 值
 * @returns {boolean} 是否为零
 */
function isZeroValue(value) {
    if (value === "0" || value === "0.0" || value === "0.00") {
        return true;
    }
    var numberValue = parseFloat(value);
    return numberValue === 0;
}

/**
 * 检查单元格是否有内容（非空且不是0）
 * @param {object} cell 单元格对象
 * @returns {boolean} 是否有内容
 */
function hasContent(cell) {
    var text = cell.Text;
    return text && text !== "" && text !== "0" && text !== "0.0" && text !== "0.00";
}

/**
 * 检查单元格是否为空（空字符串或null）
 * @param {object} cell 单元格对象
 * @returns {boolean} 是否为空
 */
function isEmptyCell(cell) {
    var text = cell.Text;
    return !text || text === "" || text.trim() === "";
}

// ================ 动态工作簿查找功能 ================
/**
 * 通过通配符名称获取工作簿
 * @param {string} pattern 工作簿名称模式（如"三香原物料库存*.xlsx"）
 * @returns {object} 工作簿对象
 */
function getWorkbookByPattern(pattern) {
    try {
        // 从模式中提取基础名称
        var baseName = pattern.replace("*", "").replace(".xlsx", "").replace(".xls", "");
        
        // 先尝试获取已打开的工作簿
        for (var i = 1; i <= Application.Workbooks.Count; i++) {
            var wb = Application.Workbooks.Item(i);
            if (wb.Name.indexOf(baseName) === 0 && 
                (wb.Name.indexOf(".xlsx") > -1 || wb.Name.indexOf(".xls") > -1)) {
                return wb;
            }
        }

        // 如果没有找到已打开的，尝试从默认路径打开
        // 获取目录下的文件
        var fso = new ActiveXObject("Scripting.FileSystemObject");
        var folderPath = "D:\\analysis\\盘点\\";
        var folder = fso.GetFolder(folderPath);
        var files = new Enumerator(folder.Files);
        
        for (; !files.atEnd(); files.moveNext()) {
            var file = files.item();
            var fileName = file.Name;
            if (fileName.indexOf(baseName) === 0 && 
                (fileName.indexOf(".xlsx") > -1 || fileName.indexOf(".xls") > -1)) {
                var fullPath = folderPath + fileName;
                return Application.Workbooks.Open(fullPath);
            }
        }
        
        throw new Error(`无法找到匹配模式 ${pattern} 的工作簿`);
    } catch (error) {
        throw new Error(`无法找到或打开工作簿: ${error.message}`);
    }
}

// ================ 出入库粘贴功能（合并版） ================
/**
 * 出入库粘贴处理（通用版）
 */
function processStockUniversal() {
    var sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    
    if (sheetName === "出库") {
        processStock("out");
    } else if (sheetName === "入库") {
        processStock("in");
    } else {
        MsgBox("当前工作表不是【出库】或【入库】表，请切换到正确的工作表！");
    }
}

/**
 * 出入库粘贴处理
 * @param {string} stockType 类型："out"出库，"in"入库
 */
function processStock(stockType) {
    var config = {
        "out": { sheetName: "出库", resultColumn: 7 },
        "in": { sheetName: "入库", resultColumn: 10 }
    };
    
    var cfg = config[stockType];
    if (!validateSheet(cfg.sheetName)) return;
    
    var sheet = Application.ActiveSheet;
    var selectedCells = getAllSelectedCells();
    if (!selectedCells) return;
    
    var successCount = 0;
    var failCount = 0;
    var failMessages = [];
    
    for (var i = 0; i < selectedCells.length; i++) {
        var targetCell = selectedCells[i];
        var targetRow = targetCell.Row;
        
        try {
            var searchKey = sheet.Cells.Item(targetRow, 2).Text;
            if (!searchKey) {
                failCount++;
                failMessages.push("行" + targetRow + ": B列单元格没有内容");
                continue;
            }
            
            var foundCell = findClosestMatchAbove(sheet, searchKey, targetRow, 2);
            
            if (foundCell) {
                var resultCell = sheet.Cells.Item(foundCell.Row, cfg.resultColumn);
                targetCell.Formula = resultCell.Text;
                successCount++;
            } else {
                failCount++;
                failMessages.push("行" + targetRow + ": 未在B列上方找到与 '" + searchKey + "' 匹配的单元格");
            }
        } catch (e) {
            failCount++;
            failMessages.push("行" + targetRow + ": 处理错误 - " + e.message);
        }
    }
    
    showResultMessage(successCount, failCount, failMessages);
}

// ================ 日盘粘贴功能 ================
/**
 * 日盘应当结余粘贴处理
 * @param {string} materialType 类型："原料"或"包装"
 */
function processDailyStock(materialType) {
    var config = {
        "原料": { targetSheet: "原料", targetColumn: 5 },
        "包装": { targetSheet: "包装", targetColumn: 6 }
    };
    
    var cfg = config[materialType];
    if (!validateSheet("日盘")) return;
    
    var activeSheet = Application.ActiveSheet;
    var selection = Application.Selection;
    
    if (selection.Count === 0) {
        MsgBox("请至少选中一个单元格");
        return;
    }
    
    var materialSheet = null;
    for (var i = 1; i <= Application.ActiveWorkbook.Sheets.Count; i++) {
        var sheet = Application.ActiveWorkbook.Sheets.Item(i);
        if (sheet.Name === cfg.targetSheet) {
            materialSheet = sheet;
            break;
        }
    }
    
    if (!materialSheet) {
        MsgBox("找不到名为'" + cfg.targetSheet + "'的工作表");
        return;
    }
    
    var lastRow = materialSheet.UsedRange.Rows.Count;
    var successCount = 0;
    var failCount = 0;
    
    for (var i = 1; i <= selection.Count; i++) {
        var selectedCell = selection.Item(i);
        var row = selectedCell.Row;
        var searchValue = activeSheet.Cells(row, 2).Text;
        var found = false;
        
        for (var j = 1; j <= lastRow; j++) {
            var cellValue = materialSheet.Cells(j, 3).Text;
            
            if (cellValue && cellValue.toString() === searchValue.toString()) {
                materialSheet.Cells(j, cfg.targetColumn).Formula = selectedCell.Text;
                found = true;
                successCount++;
                break;
            }
        }
        
        if (!found) {
            failCount++;
        }
    }
    
    var resultMsg = "操作完成！\n";
    resultMsg += "成功匹配并填充: " + successCount + " 个\n";
    resultMsg += "未找到匹配项: " + failCount + " 个";
    MsgBox(resultMsg);
}

// ================ 日盘清空单元格功能 ================
/**
 * 日盘清空单元格
 */
function clearDailyCells() {
    if (!validateSheet("日盘")) return;
    
    var sheet = Application.ActiveSheet;
    var lastRow = sheet.UsedRange.Rows.Count;
    
    if (lastRow <= 1) {
        Console.log("只有一行数据，无需清空");
        return;
    }
    
    var columnsToClear = [2, 4, 5, 7, 11];
    
    for (var i = 0; i < columnsToClear.length; i++) {
        var col = columnsToClear[i];
        var range = sheet.Range(
            sheet.Cells(2, col),
            sheet.Cells(lastRow, col)
        );
        range.ClearContents();
    }
    
    MsgBox("B、D、E、G、K列已清空完成（除首行）");
}

// ================ 自动填充原物料公式功能 ================
/**
 * 自动填充原物料公式
 */
function autoFillMaterialFormulas() {
    var sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    
    if (sheetName !== "原料" && sheetName !== "包装") {
        MsgBox("当前工作表不是【原料或包装】表，请切换到正确的工作表！");
        return;
    }
    
    // 查找有数据的"领用"列
    var resultColumns = [];
    var headerRow = 2;
    var lastCol = sheet.UsedRange.Columns.Count;
    
    for (var col = 1; col <= lastCol; col++) {
        var headerValue = sheet.Cells(headerRow, col).Text;
        
        if (headerValue === "领用") {
            var hasData = false;
            for (var row = 3; row <= sheet.UsedRange.Rows.Count; row++) {
                if (sheet.Cells(row, col).Text !== "") {
                    hasData = true;
                    break;
                }
            }
            
            if (hasData) {
                var colLetter = columnToLetter(col);
                resultColumns.push(colLetter);
            }
        }
    }
    
    // 根据工作表类型配置公式
    var formulaConfig = [];
    if (sheetName === "原料") {
        formulaConfig = [
            { column: "E", formula: "0" },
            { column: "F", formula: "=SUMIF($K$2:$BT$2,$K$2,K{row}:BT{row})" },
            { column: "G", formula: "=SUMIF($K$2:$BT$2,$L$2,K{row}:BT{row})" },
            { column: "H", formula: "=E{row}+F{row}-G{row}" },
            { column: "J", formula: "=+IF($H{row}-$I{row}<0,\"不满足\",\"\")" },
        ];
    } else if (sheetName === "包装") {
        formulaConfig = [
            { column: "F", formula: "0" },
            { column: "G", formula: "=SUMIF($L$2:$BU$2,$L$2,L{row}:BU{row})" },
            { column: "H", formula: "=SUMIF($L$2:$BU$2,$M$2,L{row}:BU{row})" },
            { column: "J", formula: "=F{row}+G{row}-H{row}" },
            { column: "K", formula: "=+IF($J{row}-$I{row}<0,\"不满足\",\"\")" },
        ];
    }
    
    // 添加领用列公式
    for (var i = 0; i < resultColumns.length; i++) {
        formulaConfig.push({
            column: resultColumns[i],
            formula: "0"
        });
    }
    
    // 填充公式
    var lastRow = sheet.UsedRange.Rows.Count;
    var filledCount = 0;
    
    for (var i = 0; i < formulaConfig.length; i++) {
        var col = formulaConfig[i].column;
        var formulaTemplate = formulaConfig[i].formula;
        var colNum = sheet.Range(col + "1").Column;
        
        for (var row = 2; row <= lastRow; row++) {
            var cell = sheet.Cells.Item(row, colNum);
            if (!cell.Formula || cell.Formula === "") {
                var finalFormula = formulaTemplate.replace(/\{row\}/g, row);
                cell.Formula = finalFormula;
                filledCount++;
            }
        }
    }
    
    Application.StatusBar = "已为 " + formulaConfig.length + " 列填充 " + filledCount + " 个公式";
    delay(1500);
    Application.StatusBar = false;
}

// ================ 日盘自动填充数量功能 ================
/**
 * 日盘自动填充数量
 */
function autoFillDailyQuantity() {
    if (!validateSheet("日盘")) return;
    
    var sheet = Application.ActiveSheet;
    var lastRow = sheet.Range("C" + sheet.Rows.Count).End(-4162).Row; // xlUp = -4162
    
    // 公式映射表
    var formulaRules = {
        "0": function(row) {
            return "=D" + row + "*EVALUATE(SUBSTITUTE(SUBSTITUTE(C" + row + ",\"kg\",\"\"),\"L\",\"\"))+E" + row;
        },
        "件箱": function(row) {
            return "=D" + row + "+IF(E" + row + "=\"\",0,E" + row + ")";
        },
        "条": function(row) {
            return "=VALUE(LEFT(C" + row + ",2))*D" + row + "+IF(E" + row + "=\"\",0,E" + row + ")";
        },
        "卷": function(row) {
            return "=D" + row + "*EVALUATE(SUBSTITUTE(SUBSTITUTE(C" + row + ",\"卷/箱\",\"\"),\"卷/袋\",\"\"))+IF(E" + row + "=\"\",0,E" + row + ")";
        }
    };
    
    for (var row = 1; row <= lastRow; row++) {
        var cellF = sheet.Range("F" + row).Text;
        var cellB = sheet.Range("B" + row).Text;
        var cellC = sheet.Range("C" + row).Text;
        var formula = "";
        
        if (cellF === "0") {
            formula = formulaRules["0"](row);
        } else if (cellF.includes("件") || cellF.includes("箱")) {
            formula = formulaRules["件箱"](row);
        } else if (cellF.includes("条")) {
            formula = formulaRules["条"](row);
        } else if (cellF.includes("卷")) {
            formula = formulaRules["卷"](row);
        } else if (cellF.includes("套")) {
            formula = "=D" + row + "*EVALUATE(SUBSTITUTE(C" + row + ",\"套/件\",\"\"))+IF(E" + row + "=\"\",0,E" + row + ")";
        } else if (cellF.includes("包")) {
            formula = "=VALUE(LEFT(C" + row + ",1))*D" + row + "+IF(E" + row + "=\"\",0,E" + row + ")";
        } else if (cellF.includes("个") || cellF.includes("只")) {
            if (cellB.includes("纸箱") || cellB.includes("隔板") || cellC === "0" || cellC.includes("个/捆")) {
                formula = formulaRules["件箱"](row);
            } else {
                formula = "=D" + row + "*EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C" + row + ",\"套/件\",\"\"),\"个/捆\",\"\"),\"卷/袋\",\"\"),\"只/箱\",\"\"),\"个/箱\",\"\"),\"个/包\",\"\"),\"只/包\",\"\"),\"套/箱\",\"\"),\"个/件\",\"\"),\"只/件\",\"\"),\"个/件\",\"\"))+IF(E" + row + "=\"\",0,E" + row + ")";
            }
        } else if (cellF.includes("盒")) {
            formula = "=D" + row + "*EVALUATE(SUBSTITUTE(C" + row + ",\"盒/箱\",\"\"))+IF(E" + row + "=\"\",0,E" + row + ")";
        }
        
        if (formula) {
            sheet.Range("G" + row).Formula = formula;
        }
    }
    
    MsgBox("公式填充完成！");
}

// ================ 出入库自动填充数量功能（合并版） ================
/**
 * 出入库自动填充数量处理（通用版）
 */
function autoFillStockQuantityUniversal() {
    var sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    
    if (sheetName === "出库") {
        autoFillStockQuantity("out");
    } else if (sheetName === "入库") {
        autoFillStockQuantity("in");
    } else {
        MsgBox("当前工作表不是【出库】或【入库】表，请切换到正确的工作表！");
    }
}

/**
 * 出入库自动填充数量处理
 * @param {string} stockType 类型："out"出库，"in"入库
 */
function autoFillStockQuantity(stockType) {
    var config = {
        "out": { 
            sheetName: "出库",
            startRow: 19345,
            searchCol: "G",
            fillCol: "H",
            unitCol: "C",
            qtyCol: "D",
            specCol: "I",
            materialUnitCol: "J"
        },
        "in": {
            sheetName: "入库",
            startRow: 4947,
            searchCol: "J",
            fillCol: "K",
            unitCol: "D",
            qtyCol: "E",
            specCol: "L",
            materialUnitCol: "M"
        }
    };
    
    var cfg = config[stockType];
    if (!validateSheet(cfg.sheetName)) return;
    
    var sheet = Application.ActiveSheet;
    var lastRow = sheet.UsedRange.Rows.Count;
    
    // 性能优化：批量处理，减少与Excel的交互
    Application.ScreenUpdating = false;
    Application.Calculation = -4135; // xlCalculationManual
    Application.EnableEvents = false;
    
    var processedCount = 0;
    var skippedCount = 0;
    var errorCount = 0;
    var zeroFilledCount = 0; // 记录填充0的数量
    
    try {
        // 使用单行处理，避免数组操作问题
        for (var row = cfg.startRow; row <= lastRow; row++) {
            try {
                // 检查A列是否有日期（第一列）
                var dateCell = sheet.Cells.Item(row, 1);
                if (isEmptyCell(dateCell)) {
                    // A列没有日期，跳过该行
                    skippedCount++;
                    continue;
                }
                
                var targetCell = sheet.Range(cfg.fillCol + row);
                
                // 检查目标单元格是否已有内容
                if (hasContent(targetCell)) {
                    // 已有内容，跳过
                    skippedCount++;
                    continue;
                }
                
                var searchValue = sheet.Range(cfg.searchCol + row).Text;
                if (!searchValue) {
                    // 搜索列为空，跳过
                    continue;
                }
                
                var unitValue = sheet.Range(cfg.unitCol + row).Text;
                var qtyText = sheet.Range(cfg.qtyCol + row).Text;
                var specValue = sheet.Range(cfg.specCol + row).Text;
                var materialUnitValue = sheet.Range(cfg.materialUnitCol + row).Text;
                
                var qtyValue = parseFloat(qtyText) || 0;
                
                var result = calculateQuantity(unitValue, qtyValue, specValue, searchValue, materialUnitValue, stockType);
                
                if (result !== null) {
                    // 使用.Formula属性写入
                    targetCell.Formula = result.toString();
                    processedCount++;
                    
                    // 记录填充0的数量
                    if (result === 0) {
                        zeroFilledCount++;
                    }
                } else {
                    // 结果为null，根据不同类型处理
                    if (stockType === "out") {
                        // 出库：填充0（满足需求1）
                        targetCell.Formula = "0";
                        processedCount++;
                        zeroFilledCount++;
                    }
                    // 入库：不填充，保持原样（满足需求2）
                }
                
                // 每处理1000行显示一次进度
                if (row % 1000 === 0) {
                    Application.StatusBar = "正在处理 " + cfg.sheetName + "，已处理: " + row + "/" + lastRow;
                }
            } catch (e) {
                // 单行错误不中断整个处理过程
                errorCount++;
                continue;
            }
        }
        
        // 填充完成后再显示提示
        var msg = cfg.sheetName + " " + cfg.fillCol + "列填充完成！\n";
        msg += "成功处理: " + processedCount + " 行\n";
        
        if (stockType === "out") {
            msg += "其中填充0: " + zeroFilledCount + " 行\n";
        }
        
        msg += "跳过无日期/已有内容: " + skippedCount + " 行\n";
        if (errorCount > 0) {
            msg += "处理错误: " + errorCount + " 行\n";
        }
        MsgBox(msg);
    } catch (error) {
        MsgBox("处理过程中出错: " + error.message);
    } finally {
        Application.ScreenUpdating = true;
        Application.Calculation = -4105; // xlCalculationAutomatic
        Application.EnableEvents = true;
        Application.StatusBar = false;
    }
}

/**
 * 计算数量逻辑
 * @param {string} unit 单位
 * @param {number} qty 数量
 * @param {string} spec 规格
 * @param {string} searchValue 搜索值
 * @param {string} materialUnit 物料单位
 * @param {string} stockType 出入库类型
 * @returns {number|null} 计算结果
 */
function calculateQuantity(unit, qty, spec, searchValue, materialUnit, stockType) {
    var result = null;
    
    // 处理规格包含L或kg的情况
    if (spec && (spec.includes("L") || spec.includes("kg"))) {
        // 处理普通单位
        if (["袋","桶","包","盒","瓶","罐","块","个","条","片"].includes(unit)) {
            var match = spec.match(/\*(\d+\.?\d*)(L|kg)/);
            if (match) {
                var quantity = parseFloat(match[1]);
                result = qty * quantity;
            }
        }
        // 处理"箱"、"件"、"筐"单位
        else if (["箱","件","筐"].includes(unit)) {
            var numbers = spec.match(/\d+\.?\d*/g);
            if (numbers) {
                result = qty * numbers.reduce(function(a, b) { return a * parseFloat(b); }, 1);
            }
        }
        // 处理"斤"单位
        else if (unit === "斤") {
            result = qty / 2;
        }
        // 处理kg/公斤单位
        else if (["kg", "公斤","KG","千克"].includes(unit)) {
            result = qty;
        }
        // 处理"吨"单位（仅入库）
        else if (stockType === "in" && unit === "吨") {
            result = qty * 1000;
        }
    }
    // 处理其他规格情况
    else {
        // 简单单位直接取数量
        var simpleUnits = ["盒","卷","条","张","个","PS","PCS","只","套","包"];
        if (simpleUnits.includes(unit)) {
            result = qty;
        }
        // 处理箱/件单位
        else if (["箱","件"].includes(unit)) {
            if (["件","箱"].includes(materialUnit)) {
                result = qty;
            }
            else if (materialUnit === "条") {
                var numbers = spec.match(/^\d{2,}/);
                if (numbers) {
                    result = qty * parseInt(numbers[0].substring(0, 2), 10);
                }
            }
            else if (["个","卷","套","盒"].includes(materialUnit)) {
                var numbers = spec.match(/\d+\.?\d*/g);
                if (numbers) {
                    result = qty * numbers.reduce(function(a, b) { return a * parseFloat(b); }, 1);
                }
            }
        }
    }
    
    // 特殊规则（仅出库）
    if (stockType === "out" && unit === "板") {
        if (searchValue.includes("好成麦芽糖浆M70型")) result = qty * 8 * 75;
        else if (searchValue.includes("南华白砂糖（一级）")) result = qty * 20 * 50;
        else if (searchValue.includes("海藻糖") || searchValue.includes("香飘飘糯米糍粉（糕点预拌粉）1")) {
            result = qty * 40 * 25;
        }
    }
    
    return result;
}

// ================ 出入库自动填充领用功能（合并版） ================
/**
 * 出入库自动填充领用处理（通用版）
 */
function autoFillStockUsageUniversal() {
    var sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    
    if (sheetName === "出库") {
        autoFillStockUsage("out");
    } else if (sheetName === "入库") {
        autoFillStockUsage("in");
    } else {
        MsgBox("当前工作表不是【出库】或【入库】表，请切换到正确的工作表！");
    }
}

/**
 * 出入库自动填充领用处理
 * @param {string} stockType 类型："out"出库，"in"入库
 */
function autoFillStockUsage(stockType) {
    var config = {
        "out": { 
            sheetName: "出库",
            searchCol: 7,
            quantityCol: 8,
            typeCol: 10,
            rawOffset: 12,
            packOffset: 13
        },
        "in": {
            sheetName: "入库",
            searchCol: 10,
            quantityCol: 11,
            typeCol: 13,
            rawOffset: 11,
            packOffset: 12
        }
    };
    
    batchProcessing(function() {
        var log = processStockUsage(stockType, config[stockType]);
        showUsageLog(log, stockType);
    }, "执行过程中出错");
}

/**
 * 处理出入库领用数据
 * @param {string} stockType 类型
 * @param {object} config 配置
 * @returns {object} 日志对象
 */
function processStockUsage(stockType, config) {
    var log = {
        successCount: 0,
        failCount: 0,
        messages: [],
        clearedCount: 0
    };
    
    var sourceSheet = Application.ActiveSheet;
    var selectedCell = Application.Selection;
    
    if (sourceSheet.Name !== config.sheetName) {
        log.messages.push("错误: 当前工作表不是【" + config.sheetName + "】表");
        return log;
    }
    
    if (!selectedCell) {
        log.messages.push("错误: 未选择单元格");
        return log;
    }
    
    var selectedRow = selectedCell.Row;
    var fullDateText = sourceSheet.Cells(selectedRow, 1).Text;
    var dayOfMonth = getDayFromDate(fullDateText);
    
    if (dayOfMonth === 0) {
        log.messages.push("错误: 无法从日期中提取日信息");
        return log;
    }
    
    // 使用动态工作簿名称匹配
    var destBook;
    try {
        destBook = getWorkbookByPattern("三香原物料库存*.xlsx");
    } catch (error) {
        log.messages.push("错误: " + error.message);
        return log;
    }
    
    if (!destBook) {
        log.messages.push("错误: 无法找到或打开原物料库存工作簿");
        return log;
    }
    
    var rawSheet = destBook.Worksheets.Item("原料");
    var packSheet = destBook.Worksheets.Item("包装");
    
    // 计算目标列
    var rawTargetCol = config.rawOffset + (dayOfMonth - 1) * 2;
    var packTargetCol = config.packOffset + (dayOfMonth - 1) * 2;
    
    // 收集数据
    var lastRowSource = sourceSheet.UsedRange.Rows.Count;
    var rawDataToSync = [];
    var packDataToSync = [];
    
    for (var i = 1; i <= lastRowSource; i++) {
        var rowDateText = sourceSheet.Cells(i, 1).Text;
        if (rowDateText && rowDateText === fullDateText) {
            var searchValue = sourceSheet.Cells(i, config.searchCol).Text;
            var typeValue = sourceSheet.Cells(i, config.typeCol).Text;
            
            if (searchValue) {
                var quantityValue = sourceSheet.Cells(i, config.quantityCol).Text;
                var quantity = parseFloat(quantityValue) || 0;
                
                var isTypeColumnZero = isZeroValue(typeValue);
                
                if (isTypeColumnZero) {
                    rawDataToSync.push({
                        row: i,
                        product: searchValue,
                        quantity: quantity
                    });
                } else {
                    packDataToSync.push({
                        row: i,
                        product: searchValue,
                        quantity: quantity
                    });
                }
            }
        }
    }
    
    // 处理数据 - 修复：对于入库表，也需要清空未匹配的单元格
    var rawLog = syncDataToSheetWithClear(rawDataToSync, rawSheet, rawTargetCol, "原料", stockType);
    var packLog = syncDataToSheetWithClear(packDataToSync, packSheet, packTargetCol, "包装", stockType);
    
    // 合并日志
    log.successCount = rawLog.successCount + packLog.successCount;
    log.failCount = rawLog.failCount + packLog.failCount;
    log.messages = log.messages.concat(rawLog.messages, packLog.messages);
    log.clearedCount = rawLog.clearedCount + packLog.clearedCount;
    
    // 保存工作簿
    try {
        destBook.Save();
    } catch (e) {
        log.messages.push("警告: 保存工作簿时出错 - " + e.message);
    }
    
    return log;
}

/**
 * 同步数据到工作表（清空未匹配的单元格）
 * @param {Array} dataToSync 要同步的数据
 * @param {object} targetSheet 目标工作表
 * @param {number} targetCol 目标列
 * @param {string} sheetType 工作表类型
 * @param {string} stockType 出入库类型
 * @returns {object} 日志对象
 */
function syncDataToSheetWithClear(dataToSync, targetSheet, targetCol, sheetType, stockType) {
    var log = {
        successCount: 0,
        failCount: 0,
        messages: [],
        clearedCount: 0
    };
    
    // 按产品汇总数量
    var productSums = {};
    for (var i = 0; i < dataToSync.length; i++) {
        var item = dataToSync[i];
        var productName = item.product;
        if (productSums[productName]) {
            productSums[productName] += item.quantity;
        } else {
            productSums[productName] = item.quantity;
        }
    }
    
    // 获取目标工作表产品映射
    var lastRowDest = targetSheet.UsedRange.Rows.Count;
    var productMap = {};
    
    // 从第3行开始扫描
    for (var i = 3; i <= lastRowDest; i++) {
        var productName = targetSheet.Cells(i, 3).Text;
        if (productName) {
            productMap[productName] = i;
        }
    }
    
    // 记录哪些产品有数据需要更新
    var productsToUpdate = {};
    for (var productName in productSums) {
        productsToUpdate[productName] = true;
    }
    
    // 清空未匹配到的单元格（填充0）- 修复：入库表也需要清空
    for (var i = 3; i <= lastRowDest; i++) {
        var productName = targetSheet.Cells(i, 3).Text;
        if (productName && productName !== "" && !productsToUpdate[productName]) {
            // 检查当前单元格是否有数据（非空且不是0）
            var currentCell = targetSheet.Cells(i, targetCol);
            var currentValue = currentCell.Text;
            
            // 只有当单元格有数据时才清空（填充0）
            if (hasContent(currentCell)) {
                targetSheet.Cells(i, targetCol).Formula = "0";
                log.clearedCount++;
                log.messages.push(sheetType + "表行" + i + " [" + productName + "] 已清空为0");
            }
        }
    }
    
    // 写入新数据
    var updatedCount = 0;
    
    for (var productName in productSums) {
        if (productSums.hasOwnProperty(productName)) {
            if (productMap[productName]) {
                var rowNum = productMap[productName];
                targetSheet.Cells(rowNum, targetCol).Formula = productSums[productName].toString();
                updatedCount++;
                log.successCount++;
            } else {
                log.messages.push(sheetType + "表中未找到产品: " + productName);
                log.failCount++;
            }
        }
    }
    
    log.messages.push(sheetType + "数据同步完成! 更新 " + updatedCount + " 个产品");
    
    // 记录清空信息
    if (log.clearedCount > 0) {
        log.messages.push("已清空 " + log.clearedCount + " 个未匹配产品的数据");
    }
    
    return log;
}

/**
 * 显示领用填充日志
 * @param {object} log 日志对象
 * @param {string} stockType 出入库类型
 */
function showUsageLog(log, stockType) {
    var logMessage = (stockType === "out" ? "出库" : "入库") + "自动填充领用完成！\n\n";
    logMessage += "成功处理: " + log.successCount + " 个\n";
    logMessage += "失败处理: " + log.failCount + " 个\n";
    logMessage += "清空未匹配数据: " + log.clearedCount + " 个\n\n";
    
    if (log.messages.length > 0) {
        logMessage += "详细信息:\n";
        // 只显示前15条详细信息，避免消息过长
        var maxMessages = Math.min(log.messages.length, 15);
        for (var i = 0; i < maxMessages; i++) {
            logMessage += log.messages[i] + "\n";
        }
        if (log.messages.length > 15) {
            logMessage += "... 还有 " + (log.messages.length - 15) + " 条信息未显示\n";
        }
    }
    
    MsgBox(logMessage);
}

// ================ 批量修改品名功能 ================
/**
 * 批量修改品名
 */
function batchModifyProductNames() {
    if (!validateSheet("修改品名")) return;
    
    Application.DisplayAlerts = false;
    Application.ScreenUpdating = false;
    
    try {
        var selectedRange = Application.Selection;
        if (!selectedRange) {
            MsgBox("请先选中包含替换规则的单元格区域");
            return;
        }
        
        var activeSheet = Application.ActiveSheet;
        var replaceRules = {};
        
        // 构建替换规则
        for (var i = 1; i <= selectedRange.Rows.Count; i++) {
            var row = selectedRange.Row + i - 1;
            var oldValue = activeSheet.Cells(row, 1).Text;
            var newValue = activeSheet.Cells(row, 2).Text;
            
            if (oldValue && newValue) {
                replaceRules[oldValue.trim()] = newValue.trim();
            }
        }
        
        if (Object.keys(replaceRules).length === 0) {
            MsgBox("选中的区域没有有效的替换规则");
            return;
        }
        
        // 确认替换规则
        var confirmMessage = "请确认以下替换规则：\n\n";
        for (var key in replaceRules) {
            confirmMessage += "将 \"" + key + "\" 替换为 \"" + replaceRules[key] + "\"\n";
        }
        confirmMessage += "\n是否继续执行替换操作？";
        
        if (MsgBox(confirmMessage, 4, "确认替换规则") !== 6) {
            return;
        }
        
        // 定义需要处理的工作簿配置
        var fileConfigs = [
            {
                "file_name": "进销明细-副本.xlsx",
                "sheets": [
                    {"name": "入库", "col": "J"},
                    {"name": "出库", "col": "G"}
                ]
            },
            {
                "file_name": "三香原物料库存*.xlsx",  // 使用通配符
                "sheets": [
                    {"name": "原料", "col": "C"},
                    {"name": "包装", "col": "C"}
                ]
            },
            {
                "file_name": "原物料名称.xlsx",
                "sheets": [
                    {"name": "Sheet1", "col": "A"},
                    {"name": "Sheet2", "col": "A"}
                ]
            }
        ];
        
        var allResults = [];
        var processedFiles = 0;
        var detailedLog = "替换完成详情：\n\n";
        
        // 处理所有工作簿
        for (var i = 0; i < fileConfigs.length; i++) {
            var config = fileConfigs[i];
            var workbook;
            var isWorkbookOpenedByUs = false;
            
            // 如果是通配符模式
            if (config.file_name.includes("*")) {
                try {
                    workbook = getWorkbookByPattern(config.file_name);
                    isWorkbookOpenedByUs = true;
                } catch (e) {
                    continue;
                }
            } else {
                // 精确匹配文件名
                workbook = findOpenWorkbook(config.file_name);
                
                // 如果工作簿没有打开，尝试自动打开
                if (!workbook) {
                    try {
                        workbook = Application.Workbooks.Open(Application.ActiveWorkbook.Path + "\\" + config.file_name);
                        isWorkbookOpenedByUs = true;
                    } catch (e) {
                        continue;
                    }
                }
            }
            
            if (!workbook) continue;
            
            var fileResults = [];
            var workbookName = workbook.Name;
            var workbookLog = "";
            
            for (var j = 0; j < config.sheets.length; j++) {
                var sheetConfig = config.sheets[j];
                try {
                    var sheet = getSheetByName(workbook, sheetConfig.name);
                    if (sheet) {
                        var sheetResults = processReplacements(sheet, sheetConfig.col, replaceRules, workbookName);
                        fileResults = fileResults.concat(sheetResults);
                        
                        if (sheetResults.length > 0) {
                            if (!workbookLog) {
                                workbookLog = "工作簿: " + workbookName + "\n";
                            }
                            workbookLog += "  工作表: " + sheetConfig.name + " - 替换了 " + sheetResults.length + " 个单元格\n";
                        }
                    }
                } catch (e) {
                    // 静默处理错误
                }
            }
            
            if (fileResults.length > 0) {
                allResults = allResults.concat(fileResults);
                processedFiles++;
                detailedLog += workbookLog + "\n";
                
                try {
                    workbook.Save();
                } catch (e) {
                    // 静默处理保存错误
                }
            }
            
            // 只有当我们打开的工作簿才需要关闭
            if (isWorkbookOpenedByUs) {
                try {
                    workbook.Close(false);
                } catch (e) {
                    // 静默处理关闭错误
                }
            }
        }
        
        // 显示最终结果
        if (allResults.length > 0) {
            detailedLog += "总计: 共处理了 " + processedFiles + " 个工作簿，替换了 " + allResults.length + " 个单元格";
            MsgBox(detailedLog);
        } else {
            MsgBox("没有找到需要替换的内容。");
        }
        
    } catch (error) {
        MsgBox("发生错误: " + error.message);
    } finally {
        Application.ScreenUpdating = true;
        Application.DisplayAlerts = true;
    }
}

/**
 * 查找已打开的工作簿
 * @param {string} fileName 文件名
 * @returns {object|null} 工作簿对象或null
 */
function findOpenWorkbook(fileName) {
    try {
        for (var i = 1; i <= Application.Workbooks.Count; i++) {
            var workbook = Application.Workbooks.Item(i);
            if (workbook.Name === fileName) {
                return workbook;
            }
        }
        return null;
    } catch (e) {
        return null;
    }
}

/**
 * 通过名称获取工作表
 * @param {object} workbook 工作簿
 * @param {string} sheetName 工作表名
 * @returns {object|null} 工作表对象或null
 */
function getSheetByName(workbook, sheetName) {
    try {
        return workbook.Worksheets.Item(sheetName);
    } catch (e) {
        for (var i = 1; i <= workbook.Worksheets.Count; i++) {
            var currentSheet = workbook.Worksheets.Item(i);
            if (currentSheet.Name.replace(/\s+/g, '') === sheetName.replace(/\s+/g, '')) {
                return currentSheet;
            }
        }
        return null;
    }
}

/**
 * 处理工作表替换
 * @param {object} sheet 工作表
 * @param {string} column 列字母
 * @param {object} replaceRules 替换规则
 * @param {string} workbookName 工作簿名称
 * @returns {Array} 替换结果数组
 */
function processReplacements(sheet, column, replaceRules, workbookName) {
    var results = [];
    var usedRange = sheet.UsedRange;
    if (!usedRange) return results;
    
    var lastRow = usedRange.Rows.Count;
    
    for (var row = 1; row <= lastRow; row++) {
        var cellRange = sheet.Range(column + row);
        var cellText = cellRange.Text;
        
        if (cellText && cellText !== "") {
            var cellValue = cellText.trim();
            
            if (replaceRules.hasOwnProperty(cellValue)) {
                try {
                    var oldValue = cellText;
                    cellRange.Formula = replaceRules[cellValue];
                    
                    results.push({
                        workbookName: workbookName,
                        sheetName: sheet.Name,
                        cellAddress: column + row,
                        oldValue: oldValue,
                        newValue: replaceRules[cellValue]
                    });
                } catch (e) {
                    continue;
                }
            }
        }
    }
    
    return results;
}

// ================ 向下兼容的函数（确保原有按钮仍可工作） ================
/**
 * 向下兼容：出库粘贴
 */
function demo1() {
    processStock("out");
}

/**
 * 向下兼容：入库粘贴
 */
function demo2() {
    processStock("in");
}

/**
 * 向下兼容：原料应当结余粘贴
 */
function demo3() {
    processDailyStock("原料");
}

/**
 * 向下兼容：物料应当结余粘贴
 */
function demo4() {
    processDailyStock("包装");
}

/**
 * 向下兼容：日盘清空单元格
 */
function demo5() {
    clearDailyCells();
}

/**
 * 向下兼容：自动填充原物料公式
 */
function demo6() {
    autoFillMaterialFormulas();
}

/**
 * 向下兼容：日盘自动填充数量
 */
function demo7() {
    autoFillDailyQuantity();
}

/**
 * 向下兼容：出库自动填充数量
 */
function demo8() {
    autoFillStockQuantity("out");
}

/**
 * 向下兼容：出库自动填充领用
 */
function demo9() {
    autoFillStockUsage("out");
}

/**
 * 向下兼容：批量修改品名
 */
function demo10() {
    batchModifyProductNames();
}

/**
 * 向下兼容：入库自动填充数量
 */
function demo11() {
    autoFillStockQuantity("in");
}

/**
 * 向下兼容：入库自动填充领用
 */
function demo12() {
    autoFillStockUsage("in");
}

// ================ 功能区按钮配置 ================
/**
 * 当工作簿打开时创建功能区按钮
 */
function Workbook_Open() {
    // 功能区1：出入库操作
    var bar1 = Application.CommandBars.Add('出入库操作');
    bar1.Visible = true;
    
    var btn1 = bar1.Controls.Add(1); // msoControlButton
    btn1.Caption = '出入库粘贴';
    btn1.OnAction = 'processStockUniversal';
    
    // 功能区2：日盘操作
    var bar2 = Application.CommandBars.Add('日盘操作');
    bar2.Visible = true;
    
    var btn2 = bar2.Controls.Add(1);
    btn2.Caption = '原料应当结余粘贴';
    btn2.OnAction = 'demo3';
    
    var btn3 = bar2.Controls.Add(1);
    btn3.Caption = '物料应当结余粘贴';
    btn3.OnAction = 'demo4';
    
    // 功能区3：清空与公式
    var bar3 = Application.CommandBars.Add('清空与公式');
    bar3.Visible = true;
    
    var btn4 = bar3.Controls.Add(1);
    btn4.Caption = '日盘清空单元格';
    btn4.OnAction = 'demo5';
    
    var btn5 = bar3.Controls.Add(1);
    btn5.Caption = '自动填充原物料公式';
    btn5.OnAction = 'demo6';
    
    // 功能区4：数量填充
    var bar4 = Application.CommandBars.Add('数量填充');
    bar4.Visible = true;
    
    var btn6 = bar4.Controls.Add(1);
    btn6.Caption = '日盘自动填充数量';
    btn6.OnAction = 'demo7';
    
    var btn7 = bar4.Controls.Add(1);
    btn7.Caption = '出入库自动填充数量';
    btn7.OnAction = 'autoFillStockQuantityUniversal';
    
    // 功能区5：数据同步
    var bar5 = Application.CommandBars.Add('数据同步');
    bar5.Visible = true;
    
    var btn8 = bar5.Controls.Add(1);
    btn8.Caption = '出入库自动填充领用';
    btn8.OnAction = 'autoFillStockUsageUniversal';
    
    // 功能区6：其他功能
    var bar6 = Application.CommandBars.Add('其他功能');
    bar6.Visible = true;
    
    var btn9 = bar6.Controls.Add(1);
    btn9.Caption = '批量修改品名';
    btn9.OnAction = 'demo10';
    
    Console.log("所有功能区按钮已加载完成。");
}