C # programming to implement search text in Excel documents

excel| programming

With the experience of programming the search text in a Word document, it is not difficult to implement this feature in Excel.

　　

By opening VBA Help for Excel and looking at the object model for Excel, it is easy to find several collections and objects needed to complete this feature: application, workbooks, workbook, worksheets, and worksheet and range. Application Create an Excel application, workbooks open an Excel document, workbook get an Excel document workbook, worksheets an action sheet collection, and worksheet get a single worksheet.

　　

The search for ideas corresponds to the above set and objects, you can say this: The text you are searching for may exist on a worksheet in an Excel document, search for a valid area in each worksheet where you want to traverse the target Excel file, or if you find one, exit this search, and if not, continue searching until you complete this search.

　　

Unlike the Word object model, the Excel object model does not provide a Find object, but it does not have a relationship, it can be done in two ways, one through the Find () method of the Range object, and the other a bit cumbersome, After you get the valid area UsedRange of the worksheet worksheet, iterate through all the rows in the Range object. In actual development, a special phenomenon is found in the second method, so the second method is also prepared to describe it in detail.

　　

The first step is to open the Excel document:

Object Filename= "";

Object missingvalue=type.missing;

String Strkeyword= ""; Specifies the text to search for, and if there are more than one, declare string[]

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

Excel.Workbook Ew=ep. Workbooks.Open (filename. ToString (), Missingvalue,

Missingvalue,missingvalue,missingvalue,

Missingvalue,missingvalue,missingvalue,

Missingvalue,missingvalue,missingvalue,

Missingvalue,missingvalue,missingvalue,

Missingvalue);

Then prepare 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 results ", messageboxbuttons.ok);

Break

}

}

Here are two noteworthy places to look at. One is to traverse the index of the worksheet, not starting at 0, but starting at 1; the other is the sixth parameter searchdirection of the Find method, which specifies the direction of the search, which is said to be optional in the Help document, but I use missingvalue as a way to compile it, For some reason, you explicitly specify its default value Xlnext.

　　

The first method is implemented, and the second method is looked at. In addition to traversing the worksheet, this method iterates through the rows and columns of the area used by the worksheet. As with other, only traversal instructions, 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 results ", messageboxbuttons.ok);

}

}

}

}

Obviously this method is much more cumbersome than the first, but here's a special place for traversing the index of a cell, when the use area of the worksheet is usedrange as a single row, the start index value for the cell in UsedRange is 1, for multiple rows and columns, the starting index value is 0, I do not know this is the Excel program designer for what kind of consideration?