C # programming to implement search text in Excel documents

Source: Internet
Author: User
Tags contains count implement object model range tostring valid
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?



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.