Excel 自动化:范围查找
本文关键字:查找 范围 自动化 Excel | 更新日期: 2023-09-27 17:57:23
我想在我的 c# 程序中实现此方法。但是我在一行中填写适当的参数时遇到问题,例如
long FirstRow = myWorksheet.Cells.Find(
What:="*",
After:=Range("IV65536"),
LookIn:=xlValues,
LookAt:= xlPart,
SearchOrder:=xlByRows,
SearchDirection:=xlNext).Row
下面是 Range.Find 方法的文档。
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.范围范围;
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;
}
未经过测试,但这给了你一个大致的想法:
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
可能适用于缺少的参数,但我很确定Type.Missing
是正确的填充物。 除此之外,它只是像你期望的那样称呼它。
下面是一些完整的 C# 示例:
- 如何:在工作表区域中搜索文本
下一个问题是 LookIn、LookAt 和 SearchOrder 参数。它们不应该是一个字符串,而类似于 SearchDirection 参数:
object What = "*";
object After = xlWorkSheet.get_Range("A1", "IV65536");
object LookIn = Excel.XlFindLookIn.xlValues;
object LookAt = Excel.XlLookAt.xlPart;
object SearchOrder = Excel.XlSearchOrder.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
);