利用 COM 互操作(Microsoft.Office.Interop.Excel)在 Excel 文檔中搜索文本,本文對比兩種遍歷方案:Range.Find() 高效便捷 vs 手動遍歷 UsedRange 靈活可控,並揭示 UsedRange 索引起始值隨單元格區域變化的特殊行為。
Application → Workbooks → Workbook → Worksheets → Worksheet → Range
Application:Excel 進程實例Workbooks.Open():打開文件返回 WorkbookWorksheets:工作表集合,**索引從 1 開始**UsedRange:已使用區域,動態變化object missing = Type.Missing;
string keyword = "搜索關鍵字";
Excel.Application app = new Excel.ApplicationClass();
Excel.Workbook wb = app.Workbooks.Open(filename, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
foreach (Excel.Worksheet ws in wb.Worksheets) {
Excel.Range found = ws.UsedRange.Find(
keyword, missing, missing, missing, missing,
Excel.XlSearchDirection.xlNext, missing, missing, missing);
if (found != null) {
MessageBox.Show($"找到於工作表 {ws.Name},地址 {found.Address}");
break;
}
}
wb.Close(false); app.Quit();
What:搜索值After:起始單元格(可選)LookIn:xlValues/xlFormulas/xlCommentsLookAt:xlWhole/xlPartSearchDirection:xlNext/xlPrevious(必須顯式指定,Missing.Value 會導致編譯錯誤)bool found = false;
foreach (Excel.Worksheet ws in wb.Worksheets) {
int rowCount = ws.UsedRange.Rows.Count;
int colCount = ws.UsedRange.Columns.Count;
int startRow = ws.UsedRange.Row; // 關鍵:可能不是 1
int startCol = ws.UsedRange.Column; // 關鍵:可能不是 1
for (int r = startRow; r < startRow + rowCount; r++) {
for (int c = startCol; c < startCol + colCount; c++) {
string cellText = ((Excel.Range)ws.Cells[r, c]).Text?.ToString() ?? "";
if (cellText.IndexOf(keyword, StringComparison.OrdinalIgnoreCase) >= 0) {
MessageBox.Show($"找到於 {ws.Name} R{r}C{c}");
found = true; break;
}
}
if (found) break;
}
if (found) break;
}
「當工作表使用區域為單行單列時,索引從 1 開始;多行多列時,索引從 0 開始」—— 這是原文觀察到的現象,實際上是對
UsedRange.Row/Column理解不準確。
UsedRange.Row 返回區域**首行號**(1-based),Column 返回**首列號**(1-based)。Row=1, Column=1, Rows.Count=1, Columns.Count=1 → 遍歷 r=1..1, c=1..1 ✓Row=2, Column=2, Rows.Count=4, Columns.Count=3 → 遍歷 r=2..5, c=2..4 ✓iBgnRow = (Row>1)? Row-1 : Row **錯誤地減 1**,導致多行區域從 Row-1 開始,漏掉首行或越界。
int firstRow = ws.UsedRange.Row;
int firstCol = ws.UsedRange.Column;
int lastRow = firstRow + ws.UsedRange.Rows.Count - 1;
int lastCol = firstCol + ws.UsedRange.Columns.Count - 1;
for (int r = firstRow; r <= lastRow; r++)
for (int c = firstCol; c <= lastCol; c++)
// 訪問 ws.Cells[r, c]
| 維度 | Range.Find() | 手動遍歷 |
|---|---|---|
| 速度 | 極快(內部優化) | 慢(跨進程 COM 調用每單元格) |
| 靈活性 | 固定匹配模式 | 任意邏輯(正則、模糊、上下文) |
| 格式感知 | 可選 Values/Formulas/Comments | 僅 .Text (顯示文本) 或 .Value2 |
| 適用場景 | 99% 搜索需求 | 極其複雜的自定義匹配 |