wps脚本

function demo1() {
    var sheet = Application.ActiveSheet;
    var selection = Application.Selection;
    // 出库粘贴 - 多选批量操作版本
    var sheetName = sheet.Name;
    if (sheetName !== "出库") {
        MsgBox("当前工作表不是【出库】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }
    try {
        // 检查是否选中了单元格
        if (selection.Count === 0) {
            alert("请选择至少一个单元格");
            return;
        }
        // 获取所有选中单元格
        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));
            }
        }
        // 用于记录处理结果
        var successCount = 0;
        var failCount = 0;
        var failMessages = [];
        
        // 处理每个选中的单元格
        for (var s = 0; s < selectedCells.length; s++) {
            var targetCell = selectedCells[s];
            var targetRow = targetCell.Row;
            
            try {
                // 获取同行第二列(B列)的内容
                var searchKey = sheet.Cells.Item(targetRow, 2).Text; // 2表示B列
                
                // 检查搜索内容是否有效
                if (searchKey === null || searchKey === "") {
                    failCount++;
                    failMessages.push("行" + targetRow + ": B列单元格没有内容");
                    continue;
                }
                
                // 初始化变量
                var foundCell = null;
                var closestDistance = Infinity;
                
                // 从当前行-1开始向上搜索B列（行号-1到第1行）
                for (var row = targetRow - 1; row >= 1; row--) {
                    var cell = sheet.Cells.Item(row, 2); // 始终搜索B列
                    if (cell.Text == searchKey) {
                        // 计算与当前行的距离
                        var distance = targetRow - row;
                        if (distance < closestDistance) {
                            closestDistance = distance;
                            foundCell = cell;
                        }
                    }
                }
                
                // 如果找到匹配项
                if (foundCell != null) {
                    // 获取同行的第7列(G列)的值
                    var resultCell = sheet.Cells.Item(foundCell.Row, 7); // 7表示G列
                    targetCell.Formula = resultCell.Text;
                    successCount++;
                } else {
                    failCount++;
                    failMessages.push("行" + targetRow + ": 未在B列上方找到与 '" + searchKey + "' 匹配的单元格");
                }
            } catch (e) {
                failCount++;
                failMessages.push("行" + targetRow + ": 处理错误 - " + e.message);
            }
        }
        
        // 显示处理结果摘要
        var resultMessage = "处理完成:\n";
        resultMessage += "成功: " + successCount + " 个\n";
        resultMessage += "失败: " + failCount + " 个";
        
        if (failCount > 0) {
            resultMessage += "\n\n失败详情:\n" + failMessages.join("\n");
        }
        
        alert(resultMessage);
        
    } catch (e) {
        alert("发生错误: " + e.message);
    }


}

function demo2() {
    // 入库粘贴 - 多选批量操作版本
    var sheet = Application.ActiveSheet;
    var selection = Application.Selection;
    var sheetName = sheet.Name;
    if (sheetName !== "入库") {
        MsgBox("当前工作表不是【入库】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }
    try {
        
        // 检查是否选中了单元格
        if (selection.Count === 0) {
            alert("请选择至少一个单元格");
            return;
        }
        
        // 获取所有选中单元格
        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));
            }
        }
        
        // 用于记录处理结果
        var successCount = 0;
        var failCount = 0;
        var failMessages = [];
        
        // 处理每个选中的单元格
        for (var s = 0; s < selectedCells.length; s++) {
            var targetCell = selectedCells[s];
            var targetRow = targetCell.Row;
            
            try {
                // 获取同行第二列(B列)的内容
                var searchKey = sheet.Cells.Item(targetRow, 2).Text; // 2表示B列
                
                // 检查搜索内容是否有效
                if (searchKey === null || searchKey === "") {
                    failCount++;
                    failMessages.push("行" + targetRow + ": B列单元格没有内容");
                    continue;
                }
                
                // 初始化变量
                var foundCell = null;
                var closestDistance = Infinity;
                
                // 从当前行-1开始向上搜索B列（行号-1到第1行）
                for (var row = targetRow - 1; row >= 1; row--) {
                    var cell = sheet.Cells.Item(row, 2); // 始终搜索B列
                    if (cell.Text == searchKey) {
                        // 计算与当前行的距离
                        var distance = targetRow - row;
                        if (distance < closestDistance) {
                            closestDistance = distance;
                            foundCell = cell;
                        }
                    }
                }
                
                // 如果找到匹配项
                if (foundCell != null) {
                    // 获取同行的第7列(G列)的值
                    var resultCell = sheet.Cells.Item(foundCell.Row, 10); // 10表示G列
                    targetCell.Formula = resultCell.Text;
                    successCount++;
                } else {
                    failCount++;
                    failMessages.push("行" + targetRow + ": 未在B列上方找到与 '" + searchKey + "' 匹配的单元格");
                }
            } catch (e) {
                failCount++;
                failMessages.push("行" + targetRow + ": 处理错误 - " + e.message);
            }
        }
        
        // 显示处理结果摘要
        var resultMessage = "处理完成:\n";
        resultMessage += "成功: " + successCount + " 个\n";
        resultMessage += "失败: " + failCount + " 个";
        
        if (failCount > 0) {
            resultMessage += "\n\n失败详情:\n" + failMessages.join("\n");
        }
        
        alert(resultMessage);
        
    } catch (e) {
        alert("发生错误: " + e.message);
    }

}

function demo3() {
    var sheet = Application.ActiveSheet;
    if (sheet.Name !== "日盘") {
        MsgBox("当前工作表不是【日盘】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }
    try {
        // 获取当前活动工作表和选中区域
        let activeSheet = Application.ActiveSheet;
        let selection = Application.Selection;
        
        // 检查是否选中了单元格
        if (selection.Count === 0) {
            MsgBox("请至少选中一个单元格");
            return;
        }
        
        // 获取"原料"工作表
        let materialSheet = null;
        for (let sheet of Application.ActiveWorkbook.Sheets) {
            if (sheet.Name === "原料") {
                materialSheet = sheet;
                break;
            }
        }
        
        if (!materialSheet) {
            throw new Error("找不到名为'原料'的工作表");
        }
        
        let lastRow = materialSheet.UsedRange.Rows.Count;
        let successCount = 0;
        let failCount = 0;
        
        // 遍历所有选中的单元格
        for (let i = 1; i <= selection.Count; i++) {
            let selectedCell = selection.Item(i);
            let row = selectedCell.Row;
            
            // 获取选中单元格所在行的第二列内容
            let searchValue = activeSheet.Cells(row, 2).Value2;
            let found = false;
            
            // 在"原料"工作表的第三列中搜索匹配项
            for (let j = 1; j <= lastRow; j++) {
                let cellValue = materialSheet.Cells(j, 3).Value2;
                
                // 比较值（注意处理可能的null/undefined）
                if ((cellValue !== null && cellValue !== undefined) && 
                    cellValue.toString() === searchValue.toString()) {
                    // 找到匹配项，将选中单元格内容填入第5列
                    materialSheet.Cells(j, 5).Value2 = selectedCell.Value2;
                    found = true;
                    successCount++;
                    break;
                }
            }
            
            if (!found) {
                failCount++;
            }
        }
        
        // 显示操作结果摘要
        let resultMsg = "操作完成！\n";
        resultMsg += "成功匹配并填充: " + successCount + " 个\n";
        resultMsg += "未找到匹配项: " + failCount + " 个";
        MsgBox(resultMsg);
        
    } catch (error) {
        MsgBox("发生错误: " + error.message);
    }
}

function demo4() {
    var sheet = Application.ActiveSheet;
    if (sheet.Name !== "日盘") {
        MsgBox("当前工作表不是【日盘】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }
    try {
        // 获取当前活动工作表和选中区域
        let activeSheet = Application.ActiveSheet;
        let selection = Application.Selection;
        
        // 检查是否选中了单元格
        if (selection.Count === 0) {
            MsgBox("请至少选中一个单元格");
            return;
        }
        
        // 获取"原料"工作表
        let materialSheet = null;
        for (let sheet of Application.ActiveWorkbook.Sheets) {
            if (sheet.Name === "包装") {
                materialSheet = sheet;
                break;
            }
        }
        
        if (!materialSheet) {
            throw new Error("找不到名为'包装'的工作表");
        }
        
        let lastRow = materialSheet.UsedRange.Rows.Count;
        let successCount = 0;
        let failCount = 0;
        
        // 遍历所有选中的单元格
        for (let i = 1; i <= selection.Count; i++) {
            let selectedCell = selection.Item(i);
            let row = selectedCell.Row;
            
            // 获取选中单元格所在行的第二列内容
            let searchValue = activeSheet.Cells(row, 2).Value2;
            let found = false;
            
            // 在"原料"工作表的第三列中搜索匹配项
            for (let j = 1; j <= lastRow; j++) {
                let cellValue = materialSheet.Cells(j, 3).Value2;
                
                // 比较值（注意处理可能的null/undefined）
                if ((cellValue !== null && cellValue !== undefined) && 
                    cellValue.toString() === searchValue.toString()) {
                    // 找到匹配项，将选中单元格内容填入第6列
                    materialSheet.Cells(j, 6).Value2 = selectedCell.Value2;
                    found = true;
                    successCount++;
                    break;
                }
            }
            
            if (!found) {
                failCount++;
            }
        }
        
        // 显示操作结果摘要
        let resultMsg = "操作完成！\n";
        resultMsg += "成功匹配并填充: " + successCount + " 个\n";
        resultMsg += "未找到匹配项: " + failCount + " 个";
        MsgBox(resultMsg);
        
    } catch (error) {
        MsgBox("发生错误: " + error.message);
    }
}

function demo5(){
    //清空B,D,E,G,K列除了第一行外的所有内容
        // 获取当前活动工作表
    var sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    if (sheetName !== "日盘") {
        MsgBox("当前工作表不是【日盘】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }
    // 获取最大行数（根据已用范围）
    var lastRow = sheet.UsedRange.Rows.Count;
    
    // 如果只有一行或没有数据，直接返回
    if (lastRow <= 1) {
        Console.log("只有一行数据，无需清空");
        return;
    }
    // 要清空的列：B(2), D(4), E(5), G(7), K(11)
    var columnsToClear = [2, 4, 5, 7, 11];
    
    // 遍历每一列，清空数据（从第2行开始）
    for (var i = 0; i < columnsToClear.length; i++) {
        var col = columnsToClear[i];
        var range = sheet.Range(
            sheet.Cells(2, col),      // 起始单元格（第2行，目标列）
            sheet.Cells(lastRow, col) // 结束单元格（最后一行，目标列）
        );
        range.ClearContents(); // 清空内容（保留格式）
    }
    
    Console.log("B、D、E、G、K列已清空完成（除首行）");

}

function demo6() {
    // 获取当前活动工作表
    var sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    var formulaConfig;
    if (sheetName !== "原料" && sheetName !== "包装") {
        MsgBox("当前工作表不是【原料或包装】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }
    // 获取第二行（标题行）
    var headerRow = 2;
    var lastCol = sheet.UsedRange.Columns.Count;
    
    // 存储符合条件的列字母
    var resultColumns = [];
    
    // 遍历所有列
    for (var col = 1; col <= lastCol; col++) {
        // 获取第二行的单元格值
        var headerValue = sheet.Cells(headerRow, col).Text;
        
        // 检查是否是"领用"列
        if (headerValue === "领用") {
            // 检查该列是否有数据（从第3行开始检查）
            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);
                
                // 输出该列的数据（调试用）
                // console.log("列 " + colLetter + " 有领用数据:");
                for (var row = 3; row <= sheet.UsedRange.Rows.Count; row++) {
                    var cellValue = sheet.Cells(row, col).Text;
                    if (cellValue !== "") {
                        // console.log("行 " + row + ": " + cellValue);
                    }
                }
            }
        }
    }

    //-----------------------------------------------
    try {

        if (sheetName === "原料") {
            console.log('执行原料')
            // 配置需要填充的列及对应公式（修改这里！）
            formulaConfig = [
                { column: "E", formula: "0" },    // C列填充A+B
                { column: "F", formula: "=SUMIF($K$2:$BT$2,$K$2,K@:BT@)" },  // E列条件判断
                { column: "G", formula: "=SUMIF($K$2:$BT$2,$L$2,K@:BT@)" },  // E列条件判断
                { column: "H", formula: "=E@+F@-G@" },  // E列条件判断
                { column: "J", formula: "=\+IF($H@-$I@<0,\"不满足\",\"\")" },  // E列条件判断
            ];
        }  else if (sheetName === "包装") {
            console.log('执行包装')
            formulaConfig = [
                { column: "F", formula: "0" }, 
                { column: "G", formula: "=SUMIF($L$2:$BU$2,$L$2,L@:BU@)" },
                { column: "H", formula: "=SUMIF($L$2:$BU$2,$M$2,L@:BU@)" }, 
                { column: "J", formula: "=F@+G@-H@" },  
                { column: "K", formula: "=\+IF($J@-$I@<0,\"不满足\",\"\")" },  
            ];
        } else{
            console.log('退出')
            Application.StatusBar = "当前工作表不支持自动填充";
            delay(1500);
            Application.StatusBar = false;
            return; // 如果不是"原料"或"物料"表，直接退出
        }

        // 将 resultColumns 中的数据添加到 formulaConfig 数组中
        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;
            
            // 从第2行开始填充（假设第1行是标题）
            for (var row = 2; row <= lastRow; row++) {
                var cell = sheet.Cells.Item(row, colNum);
                if (!cell.Formula || cell.Formula === "") {
                    // 替换@为当前行号
                    var finalFormula = formulaTemplate.replace(/@/g, row);
                    cell.Formula = finalFormula;
                    filledCount++;
                }
            }
        }

        // 状态栏提示
        Application.StatusBar = "已为 " + formulaConfig.length + " 列填充 " + filledCount + " 个公式";
        
    } catch (e) {
        Application.StatusBar = "错误: " + e.message;
    } finally {
        delay(1500);
        Application.StatusBar = false;
    }
}

function demo7(){
        //日盘获取数据
    const sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    if (sheetName !== "日盘") {
        MsgBox("当前工作表不是【日盘】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }

    const lastRow = sheet.Range("C" + sheet.Rows.Count).End(xlUp).Row; // 获取C列最后一行
    for (let row = 1; row <= lastRow; row++) {
        const cellB = sheet.Range("B" + row).Text; // 获取B列单元格内容
        const cellC = sheet.Range("C" + row).Text; // 获取C列单元格内容
        const cellF = sheet.Range("F" + row).Text; // 获取C列单元格内容
        //卷  件 箱 条 张 套 包 只 盒 个
        if (cellF === "0") {
            // =D2*EVALUATE(SUBSTITUTE(SUBSTITUTE(C2,"kg",""),"L",""))+E2
            sheet.Range("G" + row).Formula = "=D" + row + "*EVALUATE(SUBSTITUTE(SUBSTITUTE(C" + row + ",\"kg\",\"\"),\"L\",\"\"))+E" + row;
        } else if(cellF.includes("件") || cellF.includes("箱")){
            //=D2+IF(E2="",0,E2)
            sheet.Range("G" + row).Formula = "=D" + row + "+IF(E" + row + "=\"\",0,E" + row + ")"
        } else if (cellF.includes("条")){
            //=VALUE(LEFT(C2,2))*D2+IF(E2="",0,E2)
            sheet.Range("G" + row).Formula = "=VALUE(LEFT(C" + row + ",2))*D" + row + "+IF(E" + row + "=\"\",0,E" + row + ")" 
        } else if (cellF.includes("卷")){
            //=D2*EVALUATE(SUBSTITUTE(SUBSTITUTE(C2,"卷/箱",""),"卷/袋",""))+IF(E2="",0,E2)
            sheet.Range("G" + row).Formula =  "=D" + row + "*EVALUATE(SUBSTITUTE(SUBSTITUTE(C" + row + ",\"卷/箱\",\"\"),\"卷/袋\",\"\"))" + "+IF(E" + row + "=\"\",0,E" + row + ")" 
        } else if (cellF.includes("套")){
            //=D2*EVALUATE(SUBSTITUTE(C2,"套/件",""))+IF(E2="",0,E2)
            sheet.Range("G" + row).Formula = "=D" + row + "*EVALUATE(SUBSTITUTE(C" + row + ",\"套/件\",\"\"))+IF(E" + row + "=\"\",0,E" + row + ")"  
        } else if (cellF.includes("包")){
            //=VALUE(LEFT(C2,1))*D2+IF(E2="",0,E2)
            sheet.Range("G" + row).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("个/捆")){
                console.log(cellC)
                //=D2+IF(E2="",0,E2)
                sheet.Range("G" + row).Formula = "=D" + row + "+IF(E" + row + "=\"\",0,E" + row + ")"
            } else{
                //=D2*EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"套/件",""),"个/捆",""),"卷/袋",""),"只/箱",""),"个/箱",""),"个/包",""),"只/包",""),"套/箱",""),"个/件",""),"只/件",""),"个/件",""))+IF(E2="",0,E2)
                sheet.Range("G" + row).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("盒")){
            //=D2*EVALUATE(SUBSTITUTE(C2,"盒/箱",""))+IF(E2="",0,E2)
                sheet.Range("G" + row).Formula = "=D" + row + "*EVALUATE(SUBSTITUTE(C" + row + ",\"盒/箱\",\"\"))+IF(E" + row + "=\"\",0,E" + row + ")"
        }
        // 如果不包含任何关键字，则不处理
    }
    
    MsgBox("公式填充完成！"); // 修改这里，使用MsgBox替代Application.Alert

}

function demo8() {
    const sheet = Application.ActiveSheet;
    var sheetName = sheet.Name;
    const startRow = 19345;
    const lastRow = sheet.UsedRange.Rows.Count;
    if (sheet.Name !== "出库") {
        MsgBox("当前工作表不是【出库】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }
    for (let row = startRow; row <= lastRow; row++) {
        // 使用.Text获取单元格文本内容
        const gValue = sheet.Range("G" + row).Text; // G列
        const hValue = sheet.Range("H" + row).Text; // H列
        const cValue = sheet.Range("C" + row).Text; // C列（单位）
        const dValueText = sheet.Range("D" + row).Text; // D列（数量）
        const iValue = sheet.Range("I" + row).Text; // I列（规格）
        const jValue = sheet.Range("J" + row).Text; // J列（物料单位）

        // 将数量转换为数值
        const dValue = parseFloat(dValueText) || 0;

        // 如果G列没有内容或者H列已有内容，则跳过
        if (!gValue || hValue) continue;

        let result = null;

        /* 计算逻辑 */
        // 1. 处理规格包含L或kg的情况
        if (iValue && (iValue.includes("L") || iValue.includes("kg"))) {
            // 1.1 处理普通单位
            if (["袋","桶","包","盒","瓶","罐","块","个","条","片"].includes(cValue)) {
                // const match = iValue.match(/\*(\d+)(L|kg)/);
                // if (match) result = dValue * parseInt(match[1]);
                const match = iValue.match(/\*(\d+\.?\d*)(L|kg)/);
                if (match) {
                    const quantity = parseFloat(match[1]); // 使用parseFloat支持小数
                    result = dValue * quantity;
                }
            }
            // 1.2 处理"箱"单位
            else if (cValue === "箱") {
                // const numbers = iValue.match(/\d+/g);
                // if (numbers) result = dValue * numbers.reduce((a, b) => a * parseInt(b), 1);
                const numbers = iValue.match(/\d+\.?\d*/g); // 匹配整数和小数
                if (numbers) {
                    result = dValue * numbers.reduce((a, b) => a * parseFloat(b), 1);
                }
            }
            // 1.3 处理"斤"单位
            else if (cValue === "斤") result = dValue / 2;
            // 1.4 处理kg/公斤单位
            else if (["kg", "公斤"].includes(cValue)) result = dValue;
            // 1.5 处理"板"单位
            else if (cValue === "板") {
                if (gValue.includes("好成麦芽糖浆M70型")) result = dValue * 8 * 75;
                else if (gValue.includes("南华白砂糖（一级）")) result = dValue * 20 * 50;
                else if (gValue.includes("海藻糖") || gValue.includes("香飘飘糯米糍粉（糕点预拌粉）1")) result = dValue * 40 * 25;
            }
        }
        // 2. 处理其他规格情况
        else {
            // 2.1 简单单位直接取数量
            if (["盒","卷","条","张","个"].includes(cValue)) result = dValue;
            // 2.2 处理箱/包单位
            else if (["箱","包"].includes(cValue)) {
                if (["件","箱"].includes(jValue)) result = dValue;
                else if (["个","卷","套"].includes(jValue)) {
                    // const numbers = iValue.match(/\d+/g);
                    // if (numbers) result = dValue * numbers.reduce((a, b) => a * parseInt(b), 1);
                    const numbers = iValue.match(/\d+\.?\d*/g);  // 匹配整数和小数
                    if (numbers) {
                        result = dValue * numbers.reduce((a, b) => a * parseFloat(b), 1);
                    }
                }
            }
        }

        // 填充结果到H列（使用.Formula属性赋值）
        if (result !== null) {
            sheet.Range("H" + row).Formula = result.toString();
        }

    }

    MsgBox("H列填充完成！共处理了" + (lastRow - startRow + 1) + "行数据");
}
//---------------

// 主函数：同步进销明细数据到库存表
function demo9() {
    try {
        // 获取当前活动工作表和选中的单元格
        let sourceSheet = Application.ActiveSheet;
        let selectedCell = Application.Selection;

        if (sourceSheet.Name !== "出库") {
            MsgBox("当前工作表不是【出库】表，请切换到正确的工作表！");
            return; // 直接退出函数
        }
        if (!selectedCell) {
            Console.log("请先选择一个单元格");
            return;
        }

        // 获取选中单元格所在的行
        let selectedRow = selectedCell.Row;

        // 获取选中行的完整日期文本
        let fullDateText = sourceSheet.Cells(selectedRow, 1).Text; // A列
        Console.log("获取到的完整日期文本: '" + fullDateText + "'");

        // 从完整日期文本中提取日
        let dayOfMonth = getDayFromDate(fullDateText);
        Console.log("从表1日期中提取的日期号: " + dayOfMonth);

        // 获取表2（库存表）和工作表
        let destBook = getWorkbookByName("三香原物料库存202512.xlsx");
        let rawSheet = destBook.Worksheets.Item("原料");
        let packSheet = destBook.Worksheets.Item("包装");

        // 计算目标列
        let rawTargetCol = 12 + (dayOfMonth - 1) * 2; // 原料目标列
        let packTargetCol = 13 + (dayOfMonth - 1) * 2; // 包装目标列
        let rawTargetColLetter = getColumnLetter(rawTargetCol);
        let packTargetColLetter = getColumnLetter(packTargetCol);

        Console.log("原料目标列: " + rawTargetColLetter + " (第" + rawTargetCol + "列)");
        Console.log("包装目标列: " + packTargetColLetter + " (第" + packTargetCol + "列)");

        // 获取表1中所有相同日期的行
        let lastRowSource = sourceSheet.UsedRange.Rows.Count;
        let rawDataToSync = []; // J列为0的数据（原料）
        let packDataToSync = []; // J列非0的数据（包装）

        Console.log("开始扫描表1数据，行数: " + lastRowSource);

        for (let i = 1; i <= lastRowSource; i++) {
            // 检查是否同一日期（使用完整日期文本匹配）
            let rowDateText = sourceSheet.Cells(i, 1).Text; // A列
            if (!rowDateText || rowDateText === "") continue;

            if (rowDateText === fullDateText) {
                // 检查G列不为空
                let colGValue = sourceSheet.Cells(i, 7).Text; // G列
                let colJValue = sourceSheet.Cells(i, 10).Text; // J列

                if (colGValue && colGValue !== "") {
                    let quantityValue = sourceSheet.Cells(i, 8).Text; // H列
                    let quantity = parseFloat(quantityValue) || 0;

                    // 判断J列是否为数字0（而不是文本"0"）
                    let isJColumnZero = false;
                    if (colJValue === "0" || colJValue === "0.0" || colJValue === "0.00") {
                        isJColumnZero = true;
                    } else {
                        // 尝试转换为数字判断
                        let colJNumber = parseFloat(colJValue);
                        isJColumnZero = (colJNumber === 0);
                    }

                    if (isJColumnZero) {
                        // J列为0，归为原料（去原料工作簿处理）
                        Console.log("找到原料行 " + i + ": " + colGValue + " - " + quantity + " (J列: " + colJValue + ")");
                        rawDataToSync.push({
                            row: i,
                            product: colGValue,
                            quantity: quantity
                        });
                    } else {
                        // J列非0，归为包装（去包装工作簿处理）
                        Console.log("找到包装行 " + i + ": " + colGValue + " - " + quantity + " (J列: " + colJValue + ")");
                        packDataToSync.push({
                            row: i,
                            product: colGValue,
                            quantity: quantity
                        });
                    }
                }
            }
        }

        Console.log("找到 " + rawDataToSync.length + " 条原料数据");
        Console.log("找到 " + packDataToSync.length + " 条包装数据");

        // 处理原料数据（J列为0）- 在原料工作簿中处理
        processRawData(rawDataToSync, rawSheet, rawTargetCol, rawTargetColLetter);

        // 处理包装数据（J列非0）- 在包装工作簿中处理
        processPackData(packDataToSync, packSheet, packTargetCol, packTargetColLetter);

        MsgBox("数据同步完成!");

    } catch (error) {
        Console.log("执行过程中出错: " + error.message);
    }
}

// 处理原料数据（J列为0）- 在原料工作簿中处理
function processRawData(dataToSync, rawSheet, targetCol, targetColLetter) {
    try {
        Console.log("开始处理原料数据...");

        // 按产品名称汇总数量
        let productSums = {};
        for (let i = 0; i < dataToSync.length; i++) {
            let item = dataToSync[i];
            if (productSums[item.product]) {
                productSums[item.product] += item.quantity;
            } else {
                productSums[item.product] = item.quantity;
            }
        }

        Console.log("原料汇总后的产品数据:");
        for (let product in productSums) {
            Console.log("  " + product + ": " + productSums[product]);
        }

        // 获取原料工作表中所有产品名称（从C列）
        let lastRowDest = rawSheet.UsedRange.Rows.Count;
        let rawProducts = {};
        let allRawProducts = []; // 存储所有原料产品行号

        Console.log("扫描原料工作表的C列产品名称，行数: " + lastRowDest);
        for (let i = 2; i <= lastRowDest; i++) {
            let productName = rawSheet.Cells(i, 3).Text; // C列
            if (productName && productName !== "") {
                rawProducts[productName] = i;
                allRawProducts.push(i); // 记录所有产品行号
            }
        }

        // 验证目标列是否存在
        if (targetCol > rawSheet.UsedRange.Columns.Count) {
            Console.log("错误: 原料目标列 " + targetColLetter + " 超出表格范围");
            return;
        }

        // 将汇总后的数据写入原料工作表，并将未匹配的填充为0
        let updatedCount = 0;
        let filledZeroCount = 0;

        // 先处理有数据的行
        for (let productName in productSums) {
            if (productSums.hasOwnProperty(productName)) {
                if (rawProducts[productName]) {
                    let rowNum = rawProducts[productName];
                    let oldValue = rawSheet.Cells(rowNum, targetCol).Value2;
                    rawSheet.Cells(rowNum, targetCol).Value2 = productSums[productName];
                    Console.log(`已更新原料 ${productName}: 行${rowNum} 列${targetColLetter} = ${productSums[productName]} (原值: ${oldValue})`);
                    updatedCount++;
                } else {
                    Console.log(`未找到匹配的原料产品: '${productName}'`);
                }
            }
        }

        // 将未匹配到的产品行填充为0
        for (let i = 0; i < allRawProducts.length; i++) {
            let rowNum = allRawProducts[i];
            let productName = rawSheet.Cells(rowNum, 3).Text;

            // 如果这个产品不在本次同步的数据中，则填充为0
            if (!productSums[productName]) {
                let oldValue = rawSheet.Cells(rowNum, targetCol).Value2;
                // 只有当原值不为0时才填充0（避免重复填充）
                if (oldValue !== 0 && oldValue !== "0") {
                    rawSheet.Cells(rowNum, targetCol).Value2 = 0;
                    Console.log(`填充原料为0: ${productName} - 行${rowNum} 列${targetColLetter}`);
                    filledZeroCount++;
                }
            }
        }

        Console.log("原料数据同步完成! 共更新 " + updatedCount + " 个产品，填充 " + filledZeroCount + " 个0值");

    } catch (error) {
        Console.log("处理原料数据时出错: " + error.message);
    }
}

// 处理包装数据（J列非0）- 在包装工作簿中处理
function processPackData(dataToSync, packSheet, targetCol, targetColLetter) {
    try {
        Console.log("开始处理包装数据...");

        // 按产品名称汇总数量
        let productSums = {};
        for (let i = 0; i < dataToSync.length; i++) {
            let item = dataToSync[i];
            if (productSums[item.product]) {
                productSums[item.product] += item.quantity;
            } else {
                productSums[item.product] = item.quantity;
            }
        }

        Console.log("包装汇总后的产品数据:");
        for (let product in productSums) {
            Console.log("  " + product + ": " + productSums[product]);
        }

        // 获取包装工作表中所有产品名称（从C列）
        let lastRowDest = packSheet.UsedRange.Rows.Count;
        let packProducts = {};
        let allPackProducts = []; // 存储所有包装产品行号

        Console.log("扫描包装工作表的C列产品名称，行数: " + lastRowDest);
        for (let i = 2; i <= lastRowDest; i++) {
            let productName = packSheet.Cells(i, 3).Text; // C列
            if (productName && productName !== "") {
                packProducts[productName] = i;
                allPackProducts.push(i); // 记录所有产品行号
            }
        }

        // 验证目标列是否存在
        if (targetCol > packSheet.UsedRange.Columns.Count) {
            Console.log("错误: 包装目标列 " + targetColLetter + " 超出表格范围");
            return;
        }

        // 将汇总后的数据写入包装工作表，并将未匹配的填充为0
        let updatedCount = 0;
        let filledZeroCount = 0;

        // 先处理有数据的行
        for (let productName in productSums) {
            if (productSums.hasOwnProperty(productName)) {
                if (packProducts[productName]) {
                    let rowNum = packProducts[productName];
                    let oldValue = packSheet.Cells(rowNum, targetCol).Value2;
                    packSheet.Cells(rowNum, targetCol).Value2 = productSums[productName];
                    Console.log(`已更新包装 ${productName}: 行${rowNum} 列${targetColLetter} = ${productSums[productName]} (原值: ${oldValue})`);
                    updatedCount++;
                } else {
                    Console.log(`未找到匹配的包装产品: '${productName}'`);
                }
            }
        }

        // 将未匹配到的产品行填充为0
        for (let i = 0; i < allPackProducts.length; i++) {
            let rowNum = allPackProducts[i];
            let productName = packSheet.Cells(rowNum, 3).Text;

            // 如果这个产品不在本次同步的数据中，则填充为0
            if (!productSums[productName]) {
                let oldValue = packSheet.Cells(rowNum, targetCol).Value2;
                // 只有当原值不为0时才填充0（避免重复填充）
                if (oldValue !== 0 && oldValue !== "0") {
                    packSheet.Cells(rowNum, targetCol).Value2 = 0;
                    Console.log(`填充包装为0: ${productName} - 行${rowNum} 列${targetColLetter}`);
                    filledZeroCount++;
                }
            }
        }

        Console.log("包装数据同步完成! 共更新 " + updatedCount + " 个产品，填充 " + filledZeroCount + " 个0值");

    } catch (error) {
        Console.log("处理包装数据时出错: " + error.message);
    }
}

// 从日期字符串中提取"日"部分
function getDayFromDate(dateString) {
    if (!dateString || dateString === "") return 0;

    // 处理 "2025/8/20" 格式
    let 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;
}

// 将列索引转换为列字母
function getColumnLetter(columnNumber) {
    let dividend = columnNumber;
    let columnLetter = '';
    let modulo;

    while (dividend > 0) {
        modulo = (dividend - 1) % 26;
        columnLetter = String.fromCharCode(65 + modulo) + columnLetter;
        dividend = parseInt((dividend - modulo) / 26);
    }

    return columnLetter;
}

// 通过名称获取工作簿
function getWorkbookByName(name) {
    try {
        // 先尝试获取已打开的工作簿
        for (let i = 1; i <= Application.Workbooks.Count; i++) {
            let wb = Application.Workbooks.Item(i);
            if (wb.Name === name) {
                return wb;
            }
        }

        // 如果没有找到已打开的，尝试从默认路径打开
        // let fullPath = "C:\\Users\\k\\Desktop\\1\\" + name;
        let fullPath = "D:\\analysis\\盘点\\" + name;
        return Application.Workbooks.Open(fullPath);
    } catch (error) {
        throw new Error(`无法找到或打开工作簿: ${name}`);
    }
}
//---------------

// ---
function demo10() {
            // 获取当前活动工作表和选中的单元格

    try {
        // 先定义内部函数
        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;
            }
        }

        function processSheet(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);
                
                // 使用 .Text 属性获取显示的文本内容
                var cellText = cellRange.Text;
                
                if (cellText && cellText !== "") {
                    var cellValue = cellText.trim();
                    
                    if (replaceRules.hasOwnProperty(cellValue)) {
                        try {
                            var oldValue = cellText; // 使用Text作为旧值
                            // 使用 Formula 属性设置单元格内容
                            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 selectFile(defaultName) {
            try {
                var fileDialog = Application.FileDialog(1); // msoFileDialogOpen
                fileDialog.Title = "请选择文件: " + defaultName;
                fileDialog.Filters.Clear();
                fileDialog.Filters.Add("Excel文件", "*.xlsx;*.xls");
                fileDialog.InitialFileName = defaultName;
                
                if (fileDialog.Show() === -1) {
                    return fileDialog.SelectedItems.Item(1);
                }
                return null;
            } catch (e) {
                return 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;
            }
        }

        // 主逻辑开始
        Application.Interactive = true;
        Application.DisplayAlerts = false; // 关闭警告提示
        Application.ScreenUpdating = false;
        
        // 获取当前选中的单元格
        var selectedRange = Application.Selection;
        if (!selectedRange) {
            MsgBox("请先选中包含替换规则的单元格区域");
            return;
        }
        
        // 获取活动工作表
        var activeSheet = Application.ActiveSheet;
        
        if (activeSheet.Name !== "修改品名") {
            MsgBox("当前工作表不是【修改品名】表，请切换到正确的工作表！");
            return; // 直接退出函数
        }
        // 从选中的单元格构建替换规则
        var replaceRules = {};
        var ruleCount = 0;
        
        for (var i = 1; i <= selectedRange.Rows.Count; i++) {
            var row = selectedRange.Row + i - 1;
            
            var aCell = activeSheet.Cells(row, 1);
            var bCell = activeSheet.Cells(row, 2);
            
            // 使用Text获取显示值
            var aValue = aCell.Text;
            var bValue = bCell.Text;
            
            if (aValue && bValue && aValue !== "" && bValue !== "") {
                replaceRules[aValue.trim()] = bValue.trim();
                ruleCount++;
            }
        }
        
        if (ruleCount === 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": "三香原物料库存202512.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 = findOpenWorkbook(config.file_name);
            var isWorkbookOpenedByUs = false;
            
            // 如果工作簿没有打开，尝试自动打开
            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 = "";
            var hasChanges = false;
            
            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 = processSheet(sheet, sheetConfig.col, replaceRules, workbookName);
                        fileResults = fileResults.concat(sheetResults);
                        
                        if (sheetResults.length > 0) {
                            hasChanges = true;
                            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;
    }
}
// ---

// ----
function demo11() {
    const sheet = Application.ActiveSheet;
    const startRow = 4947;
    const lastRow = sheet.UsedRange.Rows.Count;
    if (sheet.Name !== "入库") {
        MsgBox("当前工作表不是【入库】表，请切换到正确的工作表！");
        return; // 直接退出函数
    }
    for (let row = startRow; row <= lastRow; row++) {
        // 使用.Text获取单元格文本内容
        const jValue = sheet.Range("J" + row).Text; // J列(原物料名称)
        const kValue = sheet.Range("K" + row).Text; // K列(需要填充的数量)
        const dValue = sheet.Range("D" + row).Text; // D列（单位）
        const eValueText = sheet.Range("E" + row).Text; // D列（数量）
        const lValue = sheet.Range("L" + row).Text; // I列（规格）
        const mValue = sheet.Range("M" + row).Text; // J列（物料单位）

        // 将数量转换为数值
        const eValue = parseFloat(eValueText) || 0;

        // 如果G列没有内容或者H列已有内容，则跳过
        if (!jValue || kValue) continue;

        let result = null;

        /* 计算逻辑 */
        // 1. 处理规格包含L或kg的情况
        if (lValue && (lValue.includes("L") || lValue.includes("kg"))) {
            // 1.1 处理普通单位
            if (["袋","桶","包","盒","瓶","罐","块","个","条","片"].includes(dValue)) {
                // const match = lValue.match(/\*(\d+)(L|kg)/);
                // if (match) result = eValue * parseInt(match[1]);
                const match = lValue.match(/\*(\d+\.?\d*)(L|kg)/);
                if (match) {
                    const quantity = parseFloat(match[1]); // 使用parseFloat支持小数
                    result = eValue * quantity;
                }
            }
            // 1.2 处理"箱"单位
            // else if (dValue === "箱") {
            else if (["箱","件","筐"].includes(dValue)) {
                // const numbers = lValue.match(/\d+/g);
                // if (numbers) result = eValue * numbers.reduce((a, b) => a * parseInt(b), 1);
                const numbers = lValue.match(/\d+\.?\d*/g); // 匹配整数和小数
                if (numbers) {
                    result = eValue * numbers.reduce((a, b) => a * parseFloat(b), 1);
                }
            }
            // 1.3 处理"斤"单位
            else if (dValue === "斤") result = eValue / 2;
            // 1.4 处理kg/公斤单位
            else if (["kg", "公斤","KG","千克"].includes(dValue)) result = eValue;
            else if (dValue === "吨") result = eValue * 1000;

        }
        // 2. 处理其他规格情况
        else {
            // 2.1 简单单位直接取数量
            if (["盒","卷","条","张","个","PS","PCS","只","套","包"].includes(dValue)) result = eValue;
            // 2.2 处理箱/包单位
            else if (["箱","件"].includes(dValue)) {
                if (["件","箱"].includes(mValue)) result = eValue;
                else if(["条"].includes(mValue)) {
                    const numbers = lValue.match(/^\d{2,}/);
                    if (numbers) {
                        result = eValue * parseInt(numbers[0].substring(0, 2), 10);
                    }
                }
                else if (["个","卷","套","盒"].includes(mValue)) {
                    // const numbers = lValue.match(/\d+/g);
                    // if (numbers) result = eValue * numbers.reduce((a, b) => a * parseInt(b), 1);
                    const numbers = lValue.match(/\d+\.?\d*/g);  // 匹配整数和小数
                    if (numbers) {
                        result = eValue * numbers.reduce((a, b) => a * parseFloat(b), 1);
                    }
                }
            }
        }

        // 填充结果到H列（使用.Formula属性赋值）
        if (result !== null) {
            sheet.Range("K" + row).Formula = result.toString();
        }

    }

    MsgBox("H列填充完成！共处理了" + (lastRow - startRow + 1) + "行数据");
}
// ----

//---------------
function demo12() {
    try {
        // 获取当前活动工作表和选中的单元格
        let sourceSheet = Application.ActiveSheet;
        let selectedCell = Application.Selection;

        if (sourceSheet.Name !== "入库") {
            MsgBox("当前工作表不是【入库】表，请切换到正确的工作表！");
            return;
        }
        if (!selectedCell) {
            Console.log("请先选择一个单元格");
            return;
        }

        // 关闭屏幕更新和自动计算以提升性能
        Application.ScreenUpdating = false;
        Application.Calculation = -4135; // xlCalculationManual
        Application.EnableEvents = false;

        try {
            // 获取选中单元格所在的行
            let selectedRow = selectedCell.Row;

            // 获取选中行的完整日期文本
            let fullDateText = sourceSheet.Cells(selectedRow, 1).Text; // A列
            Console.log("获取到的完整日期文本: '" + fullDateText + "'");

            // 从完整日期文本中提取日
            let dayOfMonth = HgetDayFromDate(fullDateText);
            Console.log("从表1日期中提取的日期号: " + dayOfMonth);

            // 获取表2（库存表）和工作表
            let destBook = HgetWorkbookByName("三香原物料库存202512.xlsx");
            let rawSheet = destBook.Worksheets.Item("原料");
            let packSheet = destBook.Worksheets.Item("包装");

            // 计算目标列
            let rawTargetCol = 11 + (dayOfMonth - 1) * 2;
            let packTargetCol = 12 + (dayOfMonth - 1) * 2;
            
            let rawTargetColLetter = HgetColumnLetter(rawTargetCol);
            let packTargetColLetter = HgetColumnLetter(packTargetCol);

            Console.log("原料目标列: " + rawTargetColLetter + " (第" + rawTargetCol + "列)");
            Console.log("包装目标列: " + packTargetColLetter + " (第" + packTargetCol + "列)");

            // 获取表1中所有相同日期的行
            let lastRowSource = sourceSheet.UsedRange.Rows.Count;
            let rawDataToSync = []; // M列为0的数据（原料）
            let packDataToSync = []; // M列非0的数据（包装）

            Console.log("开始扫描表1数据，行数: " + lastRowSource);

            // 先收集需要检查的行号，减少循环内的逻辑判断
            let dateMatchRows = [];
            for (let i = 1; i <= lastRowSource; i++) {
                let rowDateText = sourceSheet.Cells(i, 1).Text; // A列
                if (rowDateText && rowDateText !== "" && rowDateText === fullDateText) {
                    dateMatchRows.push(i);
                }
            }

            Console.log("找到 " + dateMatchRows.length + " 行匹配的日期");

            // 只处理匹配日期的行
            for (let i = 0; i < dateMatchRows.length; i++) {
                let rowNum = dateMatchRows[i];
                let colJValue = sourceSheet.Cells(rowNum, 10).Text; // J列
                let colMValue = sourceSheet.Cells(rowNum, 13).Text; // M列

                if (colJValue && colJValue !== "") {
                    let quantityValue = sourceSheet.Cells(rowNum, 11).Text; // K列
                    let quantity = parseFloat(quantityValue) || 0;

                    let isMColumnZero = false;
                    if (colMValue === "0" || colMValue === "0.0" || colMValue === "0.00") {
                        isMColumnZero = true;
                    } else {
                        let colMNumber = parseFloat(colMValue);
                        isMColumnZero = (colMNumber === 0);
                    }

                    if (isMColumnZero) {
                        rawDataToSync.push({
                            row: rowNum,
                            product: colJValue,
                            quantity: quantity
                        });
                    } else {
                        packDataToSync.push({
                            row: rowNum,
                            product: colJValue,
                            quantity: quantity
                        });
                    }
                }
            }

            Console.log("找到 " + rawDataToSync.length + " 条原料数据");
            Console.log("找到 " + packDataToSync.length + " 条包装数据");

            // 处理原料数据
            HprocessRawData(rawDataToSync, rawSheet, rawTargetCol, rawTargetColLetter);

            // 处理包装数据
            MprocessPackData(packDataToSync, packSheet, packTargetCol, packTargetColLetter);

            MsgBox("数据同步完成!");

        } finally {
            // 恢复设置
            Application.ScreenUpdating = true;
            Application.Calculation = -4105; // xlCalculationAutomatic
            Application.EnableEvents = true;
        }

    } catch (error) {
        // 确保出错时也恢复设置
        Application.ScreenUpdating = true;
        Application.Calculation = -4105;
        Application.EnableEvents = true;
        Console.log("执行过程中出错: " + error.message);
    }
}

// 处理原料数据 - 优化版本
function HprocessRawData(dataToSync, rawSheet, targetCol, targetColLetter) {
    try {
        Console.log("开始处理原料数据...");

        // 按产品名称汇总数量
        let productSums = {};
        for (let i = 0; i < dataToSync.length; i++) {
            let item = dataToSync[i];
            let product = item.product;
            if (productSums[product]) {
                productSums[product] += item.quantity;
            } else {
                productSums[product] = item.quantity;
            }
        }

        Console.log("原料汇总后的产品数据:");
        for (let product in productSums) {
            Console.log("  " + product + ": " + productSums[product]);
        }

        // 获取原料工作表中所有产品名称（从C列）
        let lastRowDest = rawSheet.UsedRange.Rows.Count;
        let rawProducts = {};

        Console.log("扫描原料工作表的C列产品名称，行数: " + lastRowDest);
        
        // 从第3行开始扫描
        for (let i = 3; i <= lastRowDest; i++) {
            let productName = rawSheet.Cells(i, 3).Text; // C列
            if (productName && productName !== "") {
                rawProducts[productName] = i;
            }
        }

        // 验证目标列是否存在
        if (targetCol > rawSheet.UsedRange.Columns.Count) {
            Console.log("错误: 原料目标列 " + targetColLetter + " 超出表格范围");
            return;
        }

        // 记录哪些产品有数据需要更新
        let productsToUpdate = {};
        for (let productName in productSums) {
            productsToUpdate[productName] = true;
        }

        // 先收集需要清空的单元格（只清空有数据的）
        let cellsToClear = [];
        for (let i = 3; i <= lastRowDest; i++) {
            let productName = rawSheet.Cells(i, 3).Text; // C列
            if (productName && productName !== "" && !productsToUpdate[productName]) {
                let currentValue = rawSheet.Cells(i, targetCol).Text;
                // 只有当单元格有数据时才需要清空
                if (currentValue && currentValue !== "" && currentValue !== "0") {
                    cellsToClear.push(i);
                }
            }
        }

        // 先清空需要清空的单元格
        for (let i = 0; i < cellsToClear.length; i++) {
            let rowNum = cellsToClear[i];
            let productName = rawSheet.Cells(rowNum, 3).Text;
            rawSheet.Cells(rowNum, targetCol).Value2 = "";
            Console.log(`已清空原料 ${productName}: 行${rowNum} 列${targetColLetter}`);
        }

        // 然后写入新数据
        let updatedCount = 0;
        for (let productName in productSums) {
            if (productSums.hasOwnProperty(productName)) {
                if (rawProducts[productName]) {
                    let rowNum = rawProducts[productName];
                    let oldValue = rawSheet.Cells(rowNum, targetCol).Text;
                    rawSheet.Cells(rowNum, targetCol).Value2 = productSums[productName];
                    Console.log(`已更新原料 ${productName}: 行${rowNum} 列${targetColLetter} = ${productSums[productName]} (原值: ${oldValue})`);
                    updatedCount++;
                } else {
                    Console.log(`未找到匹配的原料产品: '${productName}'`);
                }
            }
        }

        Console.log("原料数据同步完成! 共更新 " + updatedCount + " 个产品，清空 " + cellsToClear.length + " 个有数据单元格");

    } catch (error) {
        Console.log("处理原料数据时出错: " + error.message);
    }
}

// 处理包装数据 - 优化版本
function MprocessPackData(dataToSync, packSheet, targetCol, targetColLetter) {
    try {
        Console.log("开始处理包装数据...");

        // 按产品名称汇总数量
        let productSums = {};
        for (let i = 0; i < dataToSync.length; i++) {
            let item = dataToSync[i];
            let product = item.product;
            if (productSums[product]) {
                productSums[product] += item.quantity;
            } else {
                productSums[product] = item.quantity;
            }
        }

        Console.log("包装汇总后的产品数据:");
        for (let product in productSums) {
            Console.log("  " + product + ": " + productSums[product]);
        }

        // 获取包装工作表中所有产品名称（从C列）
        let lastRowDest = packSheet.UsedRange.Rows.Count;
        let packProducts = {};

        Console.log("扫描包装工作表的C列产品名称，行数: " + lastRowDest);
        
        // 从第3行开始扫描
        for (let i = 3; i <= lastRowDest; i++) {
            let productName = packSheet.Cells(i, 3).Text; // C列
            if (productName && productName !== "") {
                packProducts[productName] = i;
            }
        }

        // 验证目标列是否存在
        if (targetCol > packSheet.UsedRange.Columns.Count) {
            Console.log("错误: 包装目标列 " + targetColLetter + " 超出表格范围");
            return;
        }

        // 记录哪些产品有数据需要更新
        let productsToUpdate = {};
        for (let productName in productSums) {
            productsToUpdate[productName] = true;
        }

        // 先收集需要清空的单元格（只清空有数据的）
        let cellsToClear = [];
        for (let i = 3; i <= lastRowDest; i++) {
            let productName = packSheet.Cells(i, 3).Text; // C列
            if (productName && productName !== "" && !productsToUpdate[productName]) {
                let currentValue = packSheet.Cells(i, targetCol).Text;
                // 只有当单元格有数据时才需要清空
                if (currentValue && currentValue !== "" && currentValue !== "0") {
                    cellsToClear.push(i);
                }
            }
        }

        // 先清空需要清空的单元格
        for (let i = 0; i < cellsToClear.length; i++) {
            let rowNum = cellsToClear[i];
            let productName = packSheet.Cells(rowNum, 3).Text;
            packSheet.Cells(rowNum, targetCol).Value2 = "";
            Console.log(`已清空包装 ${productName}: 行${rowNum} 列${targetColLetter}`);
        }

        // 然后写入新数据
        let updatedCount = 0;
        for (let productName in productSums) {
            if (productSums.hasOwnProperty(productName)) {
                if (packProducts[productName]) {
                    let rowNum = packProducts[productName];
                    let oldValue = packSheet.Cells(rowNum, targetCol).Text;
                    packSheet.Cells(rowNum, targetCol).Value2 = productSums[productName];
                    Console.log(`已更新包装 ${productName}: 行${rowNum} 列${targetColLetter} = ${productSums[productName]} (原值: ${oldValue})`);
                    updatedCount++;
                } else {
                    Console.log(`未找到匹配的包装产品: '${productName}'`);
                }
            }
        }

        Console.log("包装数据同步完成! 共更新 " + updatedCount + " 个产品，清空 " + cellsToClear.length + " 个有数据单元格");

    } catch (error) {
        Console.log("处理包装数据时出错: " + error.message);
    }
}

// 从日期字符串中提取"日"部分
function HgetDayFromDate(dateString) {
    if (!dateString || dateString === "") return 0;

    // 处理 "2025/8/20" 格式
    let 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;
}

// 将列索引转换为列字母
function HgetColumnLetter(columnNumber) {
    let dividend = columnNumber;
    let columnLetter = '';
    let modulo;

    while (dividend > 0) {
        modulo = (dividend - 1) % 26;
        columnLetter = String.fromCharCode(65 + modulo) + columnLetter;
        dividend = parseInt((dividend - modulo) / 26);
    }

    return columnLetter;
}

// 通过名称获取工作簿
function HgetWorkbookByName(name) {
    try {
        // 先尝试获取已打开的工作簿
        for (let i = 1; i <= Application.Workbooks.Count; i++) {
            let wb = Application.Workbooks.Item(i);
            if (wb.Name === name) {
                return wb;
            }
        }

        // 如果没有找到已打开的，尝试从默认路径打开
        let fullPath = "D:\\analysis\\盘点\\" + name;
        return Application.Workbooks.Open(fullPath);
    } catch (error) {
        throw new Error(`无法找到或打开工作簿: ${name}`);
    }
}
//---------------

// 辅助函数：将列号转换为字母（如1→A，27→AA）
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;
}

// 延迟函数（毫秒）
function delay(ms) {
    var start = new Date().getTime();
    while (new Date().getTime() < start + ms) {}
}


//这里配置的是当表格被打开时执行以下代码，生成功能按钮。
function Workbook_Open() {
    //配置第一个功能区，在此功能区中可以配多个功能按钮
    let bar1 = Application.CommandBars.Add('我的功能区1')
    bar1.Visible = true
        //配置功能按钮1，为它绑定宏命令demo1
        let btn1 = bar1.Controls.Add(msoControlButton)
        btn1.Caption = '出库粘贴'        //这里配置Caption，也就是功能按钮的显示名称
        btn1.OnAction = 'demo1'        //为功能按钮绑定函数，这里绑定了demo1
        //配置功能按钮2，为它绑定宏命令demo2
        let btn2 = bar1.Controls.Add(msoControlButton)
        btn2.Caption = '入库粘贴'        
        btn2.OnAction = 'demo2'

    //配置第二个功能区，在此功能区中可以配多个功能按钮
    let bar2 = Application.CommandBars.Add('我的功能区2')
    bar2.Visible = true
        // 配置功能按钮2，为它绑定宏命令demo2
        let btn3 = bar2.Controls.Add(msoControlButton)
        btn3.Caption = '原料应当结余粘贴'        
        btn3.OnAction = 'demo3'
        // 配置功能按钮3，为它绑定宏命令demo3
        let btn4 = bar2.Controls.Add(msoControlButton)
        btn4.Caption = '物料应当结余粘贴'
        btn4.OnAction = 'demo4'
    //配置第二个功能区，在此功能区中可以配多个功能按钮
    let bar3 = Application.CommandBars.Add('我的功能区3')
    bar3.Visible = true
        // 配置功能按钮4，为它绑定宏命令demo3
        let btn5 = bar3.Controls.Add(msoControlButton)
        btn5.Caption = '日盘清空单元格'
        btn5.OnAction = 'demo5'
        let btn6 = bar3.Controls.Add(msoControlButton)
        btn6.Caption = '自动填充原物料公式'
        btn6.OnAction = 'demo6'
    //配置第二个功能区，在此功能区中可以配多个功能按钮
    let bar4 = Application.CommandBars.Add('我的功能区4')
    bar4.Visible = true
        // 配置功能按钮4，为它绑定宏命令demo3
        let btn7 = bar4.Controls.Add(msoControlButton)
        btn7.Caption = '日盘自动填充数量'
        btn7.OnAction = 'demo7'
        let btn8 = bar4.Controls.Add(msoControlButton)
        btn8.Caption = '出库自动填充数量'
        btn8.OnAction = 'demo8'
    let bar5 = Application.CommandBars.Add('我的功能区5')
    bar5.Visible = true
        let btn9 = bar5.Controls.Add(msoControlButton)
        btn9.Caption = '出库自动填充领用'
        btn9.OnAction = 'demo9'
    bar5.Visible = true
        let btn10 = bar5.Controls.Add(msoControlButton)
        btn10.Caption = '批量修改品名'
        btn10.OnAction = 'demo10'
    let bar6 = Application.CommandBars.Add('我的功能区6')
    bar6.Visible = true
        let btn11 = bar6.Controls.Add(msoControlButton)
        btn11.Caption = '入库自动填充数量'
        btn11.OnAction = 'demo11'
    bar6.Visible = true
        let btn12 = bar6.Controls.Add(msoControlButton)
        btn12.Caption = '入库自动填充领用'
        btn12.OnAction = 'demo12'
}