Excel互操作如何筛选两列后正确选中列含值单元格并设置黄色填充

原有代码核心问题 #

• 直接选中整列/依赖Select+Selection操作，会纳入大量已使用区域外的空白单元格，甚至触发工作表底部单元格着色的问题
• End[Excel.XlDirection.xlDown]的定位逻辑不可靠：列内有空白单元格时会提前中断定位，起点无数据时会直接跳到工作表最后一行
• 没有基于工作表已使用范围裁剪目标列的有效区域，无法精准锁定需要操作的单元格范围

优化后实现逻辑 #

1. 先基于工作表UsedRange裁剪出两列的有效操作区域，避免处理无效空白单元格
2. 放弃Select/Selection操作，直接操作Range对象，稳定性和性能更好
3. 筛选后仅选中可见的有效单元格进行着色，避免空区域报错

修正后代码 #

using System.Runtime.InteropServices;

static public void FilterFunction(Excel.Application Oxl, Excel.Worksheet PSheet, Excel.Range Rng, Excel.Range Find)
{
    // 先清除原有筛选，避免之前的筛选影响结果
    if (PSheet.AutoFilterMode)
    {
        PSheet.AutoFilterMode = false;
    }

    // 获取工作表已使用区域，确定有效数据的最大行号
    Excel.Range usedRange = PSheet.UsedRange;
    int maxUsedRow = usedRange.Row + usedRange.Rows.Count - 1;

    // 裁剪出两列的有效操作区域（从传入单元格所在行到已使用区域最后一行，避免整列选中）
    Excel.Range rngCol = PSheet.Range(PSheet.Cells(Rng.Row, Rng.Column), PSheet.Cells(maxUsedRow, Rng.Column));
    Excel.Range findCol = PSheet.Range(PSheet.Cells(Find.Row, Find.Column), PSheet.Cells(maxUsedRow, Find.Column));

    try
    {
        // 第一种差异场景：Rng列=Found，Find列=Missing
        rngCol.AutoFilter(1, "Found");
        findCol.AutoFilter(1, "Missing");
        // 仅处理可见的有效单元格，Count>1是跳过表头行，无表头可改成>0
        if (rngCol.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Count > 1)
        {
            rngCol.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Interior.Color = ColorTranslator.ToOle(Color.Yellow);
            findCol.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Interior.Color = ColorTranslator.ToOle(Color.Yellow);
        }
        PSheet.ShowAllData();

        // 第二种差异场景：Rng列=Missing，Find列=Found
        rngCol.AutoFilter(1, "Missing");
        findCol.AutoFilter(1, "Found");
        if (rngCol.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Count > 1)
        {
            rngCol.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Interior.Color = ColorTranslator.ToOle(Color.Yellow);
            findCol.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Interior.Color = ColorTranslator.ToOle(Color.Yellow);
        }
    }
    catch
    {
        // 无匹配可见单元格时会触发异常，可按需添加日志处理
    }
    finally
    {
        // 清除筛选，恢复工作表原始状态
        if (PSheet.AutoFilterMode)
        {
            PSheet.ShowAllData();
        }
        // 释放COM对象，避免Excel进程残留（可选但推荐）
        Marshal.ReleaseComObject(rngCol);
        Marshal.ReleaseComObject(findCol);
        Marshal.ReleaseComObject(usedRange);
    }
}

内容的提问来源于stack exchange，提问作者drewg