/// <summary>
/// 範囲から値を検索する.
/// </summary>
/// <param name="range">検索範囲</param>
/// <param name="targetStr">検索文字列</param>
/// <param name="foundRow">見つかった行番号</param>
/// <param name="FoundColumn">見つかった列番号</param>
/// <param name="isPartialMatch">部分一致検索かどうか</param>
/// <returns></returns>
public bool Find(CellsRange searchRange, string keyword, out int foundRow, out int foundColumn, bool isPartialMatch = false)
{
Range xlSearchRange = null;
Range xlFoundRange = null;
try
{
xlSearchRange = xlSheet.Range[searchRange.A1Address];
XlLookAt xlLookAt = isPartialMatch ? XlLookAt.xlPart : XlLookAt.xlWhole;
xlFoundRange = xlSearchRange.Find(What: keyword, LookAt: xlLookAt);
if (xlFoundRange == null)
{
foundRow = 0;
foundColumn = 0;
return(false);
}
foundRow = xlFoundRange.Row;
foundColumn = xlFoundRange.Column;
return(true);
}
finally
{
if (xlSearchRange != null)
{
Marshal.ReleaseComObject(xlSearchRange);
xlSearchRange = null;
}
if (xlFoundRange != null)
{
Marshal.ReleaseComObject(xlFoundRange);
xlFoundRange = null;
}
}
}
private static Range Find(string content, Range lastCell, XlLookAt match)
{
if (_currentWorksheet == null)
{
return(null);
}
return(_currentWorksheet.Cells.Find(content, After: lastCell, LookAt: match) as Range);
}
public async Task Replace(int sheet, string range, string what, string replacement, XlLookAt lookAt = XlLookAt.xlPart, XlSearchOrder searchOrder = XlSearchOrder.xlByRows,
bool matchCase = false, bool matchByte = false)
{
await Task.Run(new System.Action(() =>
{
sheets = xlWorkbook.Sheets;
setValueSheet = sheets.get_Item(sheet);
setValueRange = setValueSheet.Range[range];
setValueRange.Replace(what, replacement, lookAt, searchOrder, matchCase, matchByte);
}
));
}
/// <summary>返回 Boolean，它表示指定区域内单元格中的字符。使用此方法并不会更改选定区域或活动单元格。
/// </summary>
/// <param name="What">Microsoft Excel 要搜索的字符串。</param>
/// <param name="Replacement">替换字符串。</param>
/// <param name="LookAt">可为以下 XlLookAt 常量之一：xlWhole 或 xlPart。</param>
/// <param name="SearchOrder">可为以下 XlSearchOrder 常量之一：xlByRows 或 xlByColumns。</param>
/// <param name="MatchCase">如果为 True，则搜索区分大小写。</param>
/// <param name="MatchByte">只有在 Microsoft Excel 中选择或安装了双字节语言时，才能使用此参数。如果为 True，则双字节字符只与双字节字符匹配。如果为 False，则双字节字符可与其对等的单字节字符匹配。</param>
/// <param name="SearchFormat">该方法的搜索格式。</param>
/// <param name="ReplaceFormat">该方法的替换格式。</param>
public bool Replace(string What, string Replacement, XlLookAt? LookAt = null, XlSearchOrder? SearchOrder = null, bool? MatchCase = null, bool? MatchByte = null, string SearchFormat = null, string ReplaceFormat = null)
{
_objaParameters = new object[8] {
What,
Replacement,
LookAt == null ? System.Type.Missing : LookAt,
SearchOrder == null ? System.Type.Missing : SearchOrder,
MatchCase == null ? System.Type.Missing : MatchCase,
MatchByte == null ? System.Type.Missing : MatchByte,
SearchFormat == null ? System.Type.Missing : SearchFormat,
ReplaceFormat == null ? System.Type.Missing : ReplaceFormat
};
return (bool)_objRange.GetType().InvokeMember("Replace", BindingFlags.InvokeMethod, null, _objRange, _objaParameters);
}
/// <summary>在区域中查找特定信息。
/// </summary>
/// <param name="What">要搜索的数据。可为字符串或任意 Microsoft Excel 数据类型。</param>
/// <param name="After">表示搜索过程将从其之后开始进行的单元格。此单元格对应于从用户界面搜索时的活动单元格的位置。请注意：After 必须是区域中的单个单元格。要记住搜索是从该单元格之后开始的；直到此方法绕回到此单元格时，才对其进行搜索。如果不指定该参数，搜索将从区域的左上角的单元格之后开始。</param>
/// <param name="LookIn">信息类型。</param>
/// <param name="LookAt">可为以下 XlLookAt 常量之一：xlWhole 或 xlPart。</param>
/// <param name="SearchOrder">可为以下 XlSearchOrder 常量之一：xlByRows 或 xlByColumns。</param>
/// <param name="SearchDirection">搜索的方向。</param>
/// <param name="MatchCase">如果为 True，则搜索区分大小写。默认值为 False。</param>
/// <param name="MatchByte">只在已经选择或安装了双字节语言支持时适用。如果为 True，则双字节字符只与双字节字符匹配。如果为 False，则双字节字符可与其对等的单字节字符匹配。</param>
/// <param name="SearchFormat">搜索的格式。</param>
public Range Find(object What, Range After = null, object LookIn = null, XlLookAt? LookAt = null, XlSearchOrder? SearchOrder = null, XlSearchDirection SearchDirection = XlSearchDirection.xlNext, bool? MatchCase = null, bool? MatchByte = null, string SearchFormat = null)
{
_objaParameters = new object[9] {
What,
After == null ? System.Type.Missing : After._objRange,
LookIn == null ? System.Type.Missing : LookIn,
LookAt == null ? System.Type.Missing : LookAt,
SearchOrder == null ? System.Type.Missing : SearchOrder,
SearchDirection,
MatchCase == null ? System.Type.Missing : MatchCase,
MatchByte == null ? System.Type.Missing : MatchByte,
SearchFormat == null ? System.Type.Missing : SearchFormat
};
return new Range(_objRange.GetType().InvokeMember("Find", BindingFlags.InvokeMethod, null, _objRange, _objaParameters));
}
