C # search text in Excel documents by programming

Source: eNet power in Silicon Valley

With the experience of programming and searching text in Word documents, it is not difficult to implement this function in Excel.

Open the Excel VBA help to view the Excel object model. It is easy to find several sets and objects required to complete this function: Application, Workbooks, Workbook, Worksheets, and Worksheet and Range. The Application creates an Excel Application, and Workbooks opens the Excel document. The Workbook obtains the Workbook of the Excel document, and the Worksheets operation Worksheet set. The Worksheet obtains a single Worksheet.

　　

The search idea corresponds to the above set and object, which can be expressed as follows: the text to be searched may exist on a worksheet in an Excel document. The search should traverse the valid areas in each worksheet of the target Excel file, if it is found, this search is exited. If it is not found, the search continues until the search is completed.

　　

Unlike the Word object model, the Excel object model does not provide the Find object, but it does not matter. It can be achieved through two methods. One is through the Find () of the Range object () method To achieve this. In addition, it is troublesome to retrieve the valid zone UsedRange of the Worksheet and traverse all the rows and columns in the Range object. In actual development, a special phenomenon is found when the second method is used, so the second method is also prepared to be described in detail.

　　

Step 1: Open the Excel document:

Object filename = "";

Object MissingValue = Type. Missing;

String strKeyWord = ""; // specify the text to be searched. If there are multiple texts, declare string []

Excel. Application ep = new Excel. ApplicationClass ();

Excel. Workbook ew = ep. Workbooks. Open (filename. ToString (), MissingValue,

MissingValue,

MissingValue,

MissingValue,

MissingValue,

MissingValue );

Then you want to traverse the Excel Worksheet:

Excel. Worksheet ews;

Int iEWSCnt = ew. Worksheets. Count;

Int I = 0, j = 0;

Excel. Range oRange;

Object oText = strKeyWord. Trim (). ToUpper ();

　　

For (I = 1; I <= iEWSCnt; I ++)

{

Ews = null;

Ews = (Excel. Worksheet) ew. Worksheets [I];

ORange = null;

(Excel. Range) oRange = (Excel. Range) ews. UsedRange). Find (

OText, MissingValue, MissingValue,

MissingValue, MissingValue, Excel. XlSearchDirection. xlNext,

MissingValue, MissingValue, MissingValue );

If (oRange! = Null & oRange. Cells. Rows. Count> = 1 & oRange. Cells. Columns. Count> = 1)

{

MessageBox. Show ("the document contains the specified keyword! "," Search result ", MessageBoxButtons. OK );

Break;

}

}

Here are two points worth attention. One is to traverse the index of the worksheet, not starting from 0, but starting from 1; the other is the sixth parameter SearchDirection of the Find method, specifying the search direction, this parameter is optional in the help document, but I use MissingValue to explain how to compile it. For some reason, I explicitly specify its default value xlNext.

 

The first method is implemented. Let's look at the second method. In addition to traversing the worksheet, This method also traverses the rows and columns of the worksheet using the area. The Code is as follows:

Bool blFlag = false;

Int iRowCnt = 0, iColCnt = 0, iBgnRow, iBgnCol;

For (m = 1; m <= iEWSCnt; m ++)

{

Ews = (Excel. Worksheet) ew. Worksheets [m];

IRowCnt = 0 + ews. UsedRange. Cells. Rows. Count;

IColCnt = 0 + ews. UsedRange. Cells. Columns. Count;

IBgnRow = (ews. UsedRange. Cells. Row> 1 )?

Ews. UsedRange. Cells. Row-1: ews. UsedRange. Cells. Row;

IBgnCol = (ews. UsedRange. Cells. Column> 1 )?

Ews. UsedRange. Cells. Column-1: ews. UsedRange. Cells. Column;

　　

For (I = iBgnRow; I
{

For (j = iBgnCol; j
{

StrText = (Excel. Range) ews. UsedRange. Cells [I, j]). Text. ToString ();

If (strText. ToUpper (). IndexOf (strKeyWord. ToUpper ()> = 0)

{

MessageBox. Show ("the document contains the specified keyword! "," Search result ", MessageBoxButtons. OK );

}

}

}

}

Obviously, this method is much more complicated than the first one, but here there is a very special thing about the index for Traversing cells. When UsedRange In the worksheet is a single row column, the starting index value for cell traversal in UsedRange is 1. When there are multiple rows and multiple columns, the starting index value is 0. I wonder what kind of consideration this is for Excel programmers?