c# - Excel自动化 : Range. 查找

Question

我想实现 this我的 C# 程序中的方法。但是我无法在一行中填写适当的参数，例如

long FirstRow = myWorksheet.Cells.Find(
  What:="*", 
  After:=Range("IV65536"), 
  LookIn:=xlValues,
  LookAt:= xlPart, 
  SearchOrder:=xlByRows,
  SearchDirection:=xlNext).Row

Here is the documentation for the Range.Find method.

Range Find(
    [In] object What, 
    [In, Optional] object After, 
    [In, Optional] object LookIn, 
    [In, Optional] object LookAt, 
    [In, Optional] object SearchOrder, 
    [In, Optional] XlSearchDirection SearchDirection, 
    [In, Optional] object MatchCase, 
    [In, Optional] object MatchByte, 
    [In, Optional] object SearchFormat
);

所以基本上我不知道如何制作合适的参数对象。

更新 Excel.Range范围；

        object What = "*";
        object After = xlWorkSheet.get_Range("A1", "IV65536");
        object LookIn = "xlValues";
        object LookAt = "xlPart";
        object SearchOrder = "xlByRows";
        Excel.XlSearchDirection SearchDirection = Excel.XlSearchDirection.xlNext;
        object MatchCase = System.Reflection.Missing.Value;
        object MatchByte = System.Reflection.Missing.Value;
        object SearchFormat = System.Reflection.Missing.Value;

        range = xlWorkSheet.Cells.Find(
            What,
            After,
            LookIn,
            LookAt,
            SearchOrder,
            SearchDirection,
            MatchCase,
            MatchByte,
            SearchFormat
            );

给出“COMException 未处理:类型不匹配。(HRESULT 异常:0x80020005 (DISP_E_TYPEMISMATCH))”

更新#2这是到目前为止的方法。唯一缺少的是设置和返回范围。

    public void RealUsedRange()
    {
        int FirstRow = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByRows,
            Excel.XlSearchDirection.xlNext,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Row;

        int FirstColumn = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByColumns,
            Excel.XlSearchDirection.xlNext,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Column;

        int LastRow = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByRows,
            Excel.XlSearchDirection.xlPrevious,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Row;

        int LastColumn = xlWorkSheet.Cells.Find(
            "*",
            xlWorkSheet.get_Range("IV65536", misValue),
            Excel.XlFindLookIn.xlValues,
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByColumns,
            Excel.XlSearchDirection.xlPrevious,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value,
            System.Reflection.Missing.Value
            ).Column;
    }

Answer 1

未经测试，但这给了你一般的想法:

long firstRow = myWorkSheet.Cells.Find(
    "*", /* What */
    Range("IV65536"), /* After */
    Excel.XlFindLookIn.xlValues, /* LookIn */
    Excel.XlLookAt.xlPart, /* LookAt */
    Excel.XlSearchOrder.xlByRows, /* SearchOrder */
    Excel.XlSearchDirection.xlNext, /* SearchDirection */
    Type.Missing, /* MatchCase */
    Type.Missing, /* MatchByte */
    Type.Missing /* SearchFormat */
    ).Row;

由于在没有 C# v4 的情况下无法使用 VB.NET 的可选参数语法，因此您需要按顺序提供所有参数。提供 null 可能适用于缺少的 args，但我很确定 Type.Missing 是正确的填充符。除此之外，它只是像您期望的那样调用它。

以下是一些完整的 C# 示例:

• How to: Search for Text in Worksheet Ranges