Understand the Excel object model from the perspective of .NET developers

xiaoxiao2021-03-06 97

Summary: Some objects provided by Microsoft Office Excel 2003 are discussed, and how to create a managed code solution by using the Microsoft Visual Studio tools for Microsoft Office systems. The focus is mainly Application, Workbook, Worksheet, and Range objects. Visual Basic .NET and Visual C # code examples demonstrate some properties, methods, and events of each object.

Download Excelobj.exe from the Microsoft Download Center.

This page

Introduction Application Objects Workbook class Worksheet class Range Objects Next During content

Introduction

For those who intend to use the Microsoft Office system for Microsoft Office systems and those who want to use COM automation to control Microsoft Office Excel 2003 applications, they need to interact with the objects provided by the Excel object model. Excel provides hundreds of objects you might want to interact with, but you can get a good start to get an object model by focusing on a very small subset of these available objects. These objects include:

• Application • Workbook • Worksheet • Range

Although it is impossible to specifically quotation, most of the work will be core in these four classes and their members. In this document, you will learn how to use each of these classes, in addition, we will introduce some properties, methods, and events of each class. You will also see some examples that you can try, these examples demonstrate some of the features of each object.

Tip Total, developers using Microsoft Visual Basic .NET When using the Microsoft Office object, it is much easier than developers using Microsoft Visual C #, an important reason is: Visual Basic for Applications. The VBA) method often contains an optional parameter, and Visual Basic .NET supports an optional parameter. C # Developers will find that they must provide a value for each optional method parameters, and Visual Basic .NET developers can simply use the named parameters only provide the value they need. In addition, C # does not support attributes with parameters (except for the indexer), however, many Excel attributes accept parameters. You will find that for C # developers, some properties (for example, Application.Range properties available for VBA and Visual Basic .NET) require a separate accessor method (Get_Range method replaces the range property). In this article, please note the difference between such a language.

In most cases, you will find that the Excel object model directly simulates its user interface. It is not difficult to guess, the Application object provides packaging of the encapsulation, and each Workbook object contains a collection of Worksheet objects. Among them, the main abstraction of the cell is a RANGE object, which allows you to use a single cell or unit group.

Each of the following will describe a primary Excel object, select a specific member of the object to perform a demonstration. Since there are hundreds of objects available for research, it is impossible to discuss all objects here: you will get enough knowledge of object model to start your work, and you can use Excel online help to get More detailed information.

Tip In this article, you will see many of the DirectCast and CType methods. The reason is that the sample project has its own optionstrict settings - this means that Visual Basic .NET requires strict type conversion. Many Excel methods and properties return to the Object type or depend on the late binding: For example, the Application.ActiveSheet property returns Object, not the Worksheet you guess. Therefore, in order to perform strict type conversion as much as possible, an Option Strict is enabled, and each type conversion is explicitly handled. (If you don't use Option Strict in Visual Basic .NET, the code you have written may be compiled, but it will fail at runtime. This is the option of Option Strictness - it greatly reduces the likelihood that illegal conversion is abnormal at runtime). If you are a C # developer who is reading this document, you may appreciate this decision. This white paper references example project ExcelobjectModel.sln. This project contains an Excel workbook and related Visual Basic .NET code. It is not that each example shown in this article appears in this sample project, but any example of more than one line or two lines of code is placed in the workbook, and the hyperlink of calling code is set in the project.

Tip In this article, it is impossible to annotate each object or member. It is impossible to mention a small part of these classes. The best tool for studying any large object model is the Object Browser window, where you can find a list of each class, as well as members of the class. You will find that many members of many members discussed in this document apply to many other different classes: for example, the Printout method discussed in the context of the Sheets collection also applies to Chart, Worksheet, Range, and other objects. This document is designed to let you know what is available, and the rest of the things rely on your curiosity to dig.

Application object

The Excel Application object represents the Excel application itself. This may sound obvious, but the Application object discloses some options for the runtime application, applied to the instance, and information on the current user object opened in this instance. The Application object provides many members, where many members you have never need to study, but other members have the right to integrate the behavior of your application. You can divide these members into the following categories:

• Control status and display in Excel • Return to a member of an object • Members of the execution operation • Members of the file operation • Other members

The following sections describe each of these groups, as well as code examples to demonstrate some members.

Control status and display in Excel

Application object provides a large set of properties to control the general state of Excel. Table 1 lists a subset of the Application object properties related to the status.

Table 1. APPLICATION Property Types for Control Excel Property Type Description Cursor XLMousePointer (XLDefault, Xlibeam, XlnorthwestArrow, XLwait) Gets or sets the appearance of the mouse pointer. The EditDirectLyleNCell Boolean value directly acquires or sets the ability to edit cells. If you are false, you can only edit cells in the formula column. FixedDecimal Boolean value If you are true, all numeric values use the fixedDecimalplaces attribute to determine the fractional number; otherwise, the FixedDecimalPlaces property will be ignored (default false). FixedDecimalPlace LONG determines the number of digits used for numerical data (if the FixedDecimal property is true). Interactive Boolean gains or sets the ability to interact with the user through keyboard and mouse; if you set this property to false, be sure to reset it to true in an exception handler. Excel does not automatically reset it. The MoveAfterReturn Boolean value is if true, then when you press the Enter key, select it will move to the next cell; the default is true. MoveAfterReturndirection XLDirection (XLDown, XLTOLEFT, XLTORTIGHT, XLUP) Indicates the direction that moves after pressing the Enter (if the MoveAfterReturn property is true). The default is XLDown. ScreenupDating Boolean value If true, Excel updates its screen after each method call. In order to save time and make your application look more professional, you can turn off the display at the code. Once you are done, you must reset this property value again to true. Excel does not automatically reset it. Sheetsinnewwbook long gets or sets the number of worksheets that Excel automatically placed in a new workbook. The StandardFont string gets or sets the name of the default font in Excel; only after restarting Excel. StandardFontSize Long Gets or sets the size of the default fonts in Excel; only after restarting Excel. Startuppath (read-only) strings Returns the full path to the folder containing the Excel boot load. TemplatesPath (read-only) string returns the full path to the folder containing the template; this value represents a Windows special folder. In all properties listed in Table 1, an attribute you most use is the screenUpdating property. By using this attribute, you can not only make your Excel application look more professional, but also make them run faster - updated updates after each modification will seriously affect the running efficiency of the code, especially in a wide range When the programming method is filled. However, it is important to always set this property when you complete your work, because Excel does not reset it. Therefore, when using the ScreenupDating property, you will need to always use the following code snippet, and use the .NET exception handle to ensure the screen update recovery:

'Visual Basic

Try

Thisapplication.screenupdating = false

'Do Your Work That Updates The Screen.

Finally

Thisapplication.screenupdating = TrueEnd TRYEND TRYEND TRYEND

// C #

Try

{

Thisapplication.screenupdating = false;

// Do Your work That Updates the screen.

}

Finally

{

Thisapplication.screenupdating = true;

}

The Application object also provides a set of properties that control the displayed in Excel. You can modify any of these properties to change the content seen on the screen. Table 2 lists a subset of available display options.

Table 2 Application Property Property Types for Control Excel Property Type Description DisplayAlerts Boolean value If you are true (default), when you run the code, as long as it is necessary (for example, when you delete a worksheet), Excel displays a warning message. Set to False to ignore the warning. Excel's behavior seems to have a default value for each warning. DisplayformulaBar Boolean value if true (default), Excel displays the standard formula bar to edit cells; set to false to hide the formula bar. DisplayFullScreen Boolean Boolean, if you are True, Excel runs full screen mode (this mode is not the same as only maximizing the EXCEL window); its default value is false.

Tip is exactly the same as the ScreenupDating property, resetting the DisplayAlerts property is very important. Because Excel does not reset this property, and it is set to false, Excel does not prompt you to save them before you turn off the workbook; if you don't carefully reset the Displayalerts property, you may cause you if you are not careful. Lost data.

Returns a member of the object

Many Application objects return to other objects. Because the standard Microsoft Office project template provided by Visual Studio .NET contains only thisApplication and thisworkBook objects, you usually need to use object members of the Application class to reference other objects provided by Excel. You can use these members to retrieve references to a particular sub-object through attributes such as ActiveWindow, or retrieved references to an available object set through attributes such as Charts. Table 3 lists a subset of the attributes of the returning object of the Application object.

Table 3. A subset of the properties of the Application object to the object of the object. Property Type Description ActiveCell Ran Returns a reference to the current active cell in the active window (top window). If there is no active window, this property will generate an error. The ActiveChart chart returns a reference to the chart of the current activity. For an embedded chart, it can only be active only if this chart is selected or activated. ActiveSheet object Returns a reference to the active worksheet in the active workbook. The ActiveWindow window returns a reference to the active window (top window); if there is no active window, no results are returned. The Charts worksheet returns a collection of Sheet objects (the parent object of the Worksheet object), which contains references to each chart in the active workbook. The Selection object returns the object selected in the application. May be a Range, a Worksheet or any other object - equally applicable to the Window class, in which case is usually a RANGE object. If there is no object being selected, no results are returned. The Sheets worksheet returns a collection of Sheet objects that contain references to each worksheet in the active workbook. The WorkbookS Workbook returns a collection of Workbook objects that contain references to all open workbooks. You will most interact with the Workbook attribute of the Application class. This property allows you to loop access to the open workbook, open or create a new workbook. The following section describes the behavior of this attribute.

Workbook collection

Workbooks collection makes it possible to use all open workbooks to create a new workbook and import data into a new workbook. The following table lists the main purposes of the Workbooks collection you will find:

• Create a new workbook to use the following code (you can also specify a parameter for the name of a workbook template as an Add method): 'Visual Basic

DIM WB As Excel.Workbook = thisapplication.workbooks.add ()

// c #

Excel.Workbook WB = thisApplication.workbooks.add (type.missing);

• Turn off all open workbooks. Unlike most collections, this collection allows you to close all members at once. The following method calls close all open workbooks: 'Visual Basic

Thisapplication.workbooks.close ()

// c #

THISAPPLICATION.WORKBOOKS.CLOSE ();

• Open an existing workbook (using the Open method of the Workbooks collection). The simplest form is to use the OPEN method, as shown in the following code snippet. The Open method provides a lot of optional parameters that affect its behavior in a specific environment, however, you usually do not need to use these optional parameters: 'Visual Basic

DIM WB As Excel.Workbook = _

Thisapplication.Workbooks.open ("c: /yourpath/yourworkbook.xls")

// c #

Excel.Workbook WB = thisapplication.workbooks.open

"C: //yourpath//yourworkbook.xls",

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

TYPE.MISSING, TYPE.MISSING;

Tip If you are a C # developer, you will be accustomed to see the reference to the Type.Missing value in the method call. Because the Excel object model is written in VBA, many methods are available to accept optional parameters - sometimes reach 30 optional parameters. You will need many instances of type.missing values, or provide specific default values for each parameter. • Open a text file, database or XML file (using OpenText, OpenDatabase or OpenXML) in the form of a workbook. These methods provide great flexibility, but even more contexts to them, it will occupy more spaces more than here. In terms of now, you know that these methods are sufficient - if you need to load any of these items into Excel, you can study these items more carefully. You can use the following code to load a text file in the form of a workbook (using a comma as a separator, starting from the third line in the text file): 'Visual Basic

DIM WB As Excel.Workbook = _

Thisapplication.Workbooks.opentext ("c: /test.txt", Startrow: = 3, _

DataType: = Xldelimited, Comma: = true)

// c #

Excel.Workbook wb =

ThisApplication.workbooks.opentext ("c: //test.txt",

Type.Missing, 3, Excel.xltextParsingType.xldelimited,

Excel.xlTextQualifier.xltextqualifierDoublequote,

Type.Missing, Type.Missing, Type.Missing, True,

Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing;

• Reference a single workbook. You can use an integer (indicating the location in the collection) or workbook name as an index in the Workbooks collection. However, if you want to reference a workbook through the name, you will need to pay attention to the method of reference: You must use the name you see in the title bar, this name does not include ".xls" extension before saving the file. Name: 'Visual Basic

DIM WB As Excel.Workbook = thisapplication.Workbooks (1)

'Before Book1 IS Saved:

WB = thisapplication.workbooks ("book1")

'After book1 is saved:

WB = thisapplication.Workbooks ("Book1.xls")

// c #

Excel.Workbook wb = thisapplication.workbooks [1];

// before book1 is saved:

WB = thisapplication.Workbooks ["book1"];

// after book1 is saved: wb = thisappplandation.Workbooks ["book1.xls"];

Tip When you reference a specific workbook, you take advantage of the default indexer (Item property). In addition to the ITEM attribute, the Workbooks set is the same as all collection provided by Microsoft Office, including count properties, and count attributes return items in the collection (Workbooks in this example).

Members of the operation

Application objects provide a number of methods that allow you to perform operations (from recalculating current data to revocation to data). The following table enumerate some methods of the Application object, and describes each method using some small examples. The example of this part appears within the ApplicationObject worksheet in the sample workbook:

• Calculate: Forced recalculate all open workbooks, specific workbooks or specific ranges: 'Visual Basic

THisapplication.calculate

'OR ...

Thisworkbook.calculate

'OR ...

THISAPPLICATION.RANGE ("SomenamedRange"). Calculate

// c #

THISAPPLICATION.CALCULATE ();

// OR ...

THISWORKBOOK.CALCULATE ();

// OR ...

ThisApplication.get_Range ("A1", "B12"). Calculate ();

Note As shown in the sample code, the RANGE and WORKSHEET objects also provide a Calculate method. Use the method that can limit the calculation range within the minimum cell number of cells you want to recalculate. The recalculation engine in Excel is very fast, but if you can limit the number of cells involved, you can optimize this. Application.calculate is only used when you want to recalculate each of the open workbooks. Tips Visual Basic .NET and C # are not exactly the same when processing Excel members. For example, the RANGE attribute in Excel, VBA, and Visual Basic .NET can only be accessed through the GET_RANGE method in C #. In this article, some examples of this accessor member and other accessor members will be found. • Checkspelling: Returns a boolean to indicate whether the parameters provided are spelled correctly. You can choose to provide a name of a custom dictionary and a boolean to indicate if you want to ignore your capital. The following code snippet checks the spelling of the value you provide, and indicates its result on the worksheet: 'Visual Basic

Private sub testspelling ()

DIM RNG AS Excel.range = _

Thisapplication.range ("Checkspelling")

DIM STROUT AS STRING

IF thisapplication.checkspelling (_

RNG.Offset (0, 1) .value.tostring) THEN

Strout = "Spelled Correctly"

Else

Strout = "Spelled IncorRectly"

END IF

RNG.OFFSET (0, 2) .value = strout

End Sub

// c #

Private void testspelling ()

{

// if you specify Only a named Range in the call // TO GET_RANGE, Use Type.missing for the second parameter.

Excel.Range RNG = thisApplication.

GET_RANGE ("Checkspelling", Type.Missing;

// Note That C # Requires you to retrieve and set

// The value2 Property of the Range, Rather Than

// The value of Property, Because The Value Property

// is Parameterized, Making It Unavailable to C # code:

RNG.GET_OFFSET (0, 2) .value2 =

(Thisapplication.checkspelling

RNG.GET_OFFSET (0, 1) .value2.toString (),

Type.Missing, Type.Missing)

"Spelled Correctly"

: "Spelled IncorRectly");

}

Prompt the previous code snippet uses the OFFSET method of the Range object, and you have never encountered so far. These two will be discussed in the part of the Range object later. The use of the Range class is easy to understand: Range object represents a cell or a set of cells. In this example, the RANGE object references the named range Checkspelling. The Offset property returns a RANGE object from the specified row of the relevant Range's left corner and the number of columns, and allows you to process cells relative to a known location. • Evaluate: Convert the Excel name to the actual reference or value. This method allows you to create a reference in the form of a string, and convert it into an actual object reference in the form of a string, or the value of the expression. The following example allows you to enter the address of the cell into the sample worksheet, and the code puts the text in the cell you specify: 'Visual Basic

Private sub Testevaluate ()

DIM RNG AS Excel.range = _

ThisApplication.range ("evataate")

Try

Dim Rngnew as Excel.Range = _

Thisapplication.evaluate (_

Directcast (RNG.Offset (0, 1) .value), Excel.Range

RNGNEW.VALUE = "Hello, World!"

Catch exception

Messagebox.show (ex.Message, thisApplication.name)

END TRY

End Sub

// c #

Private void Testevaluate ()

{

Excel.Range RNG = thisApplication.

GET_RANGE ("Evaluate", Type.Missing;

Try

{

Excel.Range RNGNEW =

(Excel.Range) thisapplication.evaluate

RNG.GET_OFFSET (0, 1) .value2;

RNGNEW.VALUE2 = "Hello, World!";

}

Catch (Exception EX)

{

Messagebox.show (ex.Message, thisApplication.name);

}

• Mailsystem, MailSession, MailOgFf, Mailogon, and Sendmail: These members allow you to log in to your installed email system, send the current workbook as an attachment, and log out of the email system you installed. The MAILSystem property indicates that the installed email system is installed, and the Mailsession property returns a reference to the current email session (if there is an activity session, you don't need to log in). The following example sends an example workbook as an attachment to a simple email message: 'Visual Basic

Private sub testemail ()

IF thisapplication.mailsystem = Excel.xlmailsystem.xlmapi Then

IF thisapplication.mailsession is nothing then

DIM FRM AS New Sendmail

IF frm.showdialog = DialogResult.ok then

Thisapplication.maillogon (_

Frm.emailname, frm.emailpassword, frm.downloadnewmail

END IF

DIM stres for a string = _

Thisapplication.range ("sendmail"). Offset (0, 1). _

Value.tostring

Thisworkbook.sendmail (Stremail, "Sample Excel Email")

Thisapplication.maillogoff ()

Else

Messagebox.show (_

"This Demonstration Works Only IF MAPI is Installed.")

END IF

End Sub

// c #

Private void testemail ()

{

IF (thisApplication.mailsystem ==

Excel.xlmailsystem.xlmapi)

{

IF (thisApplication.mailsession == NULL)

{

Sendmail fm = new sendmail ();

IF (frm.showdialog () == DialogResult.ok)

{

Thisapplication.maillogon (frm.emailname,

Frm.EmailPassword, frm.downloadnewmail;

}

String stremail = trisapplication.

Get_Range ("Sendmail", Type.Missing).

Get_offset (0, 1) .value2.toString ();

Thisworkbook.sendmail (stremail,

"Sample Excel Email", Type.Missing;

Thisapplication.maillogoff ();

}

Else

{

MessageBox.show ("this Demonstration Works Only IF"

"MAPI IS INSTALLED.");

}

Note: The Workbook class provides Sendmail methods; this is very meaningful because the least particle size of the email is the workbook itself. You will notice that the sendmail method does not provide any way to attach the text to the message you sent, and not flexible enough to address. Obviously, it is easy to provide these members to make a workbook via email. If you want to get a more complete support, you need to study other methods with email interactions. Also, if you are currently not online, it is not connected to your email system, the above sample code will fail. If the mailsession property does not return any results, you can handle this failure and no longer try to send an email. • Quit: Allows you to exit Excel by programming. If you set the Displayalerts property to False, the system will not prompt you to save any unsaved data. Also, if you set the Workbook's Saved property to True, you will not prompt you to save it: 'Visual BasiceisApplication.quit

// c #

THISAPPLICATION.QUIT ();

• Undo: Cancel the last operation of the user in the user interface. This method does not affect the operation of the code and can only undo a single operation. This feature is not very powerful, but it really allows you to cancel the last operation of the user before performing your code: 'Visual Basic

Thisapplication.undo

// c #

THISPPLICATION.UNDO ();

Processing file operation

The Application object provides several members that interact with file systems in the context of the Excel application through these members. The following sections describe some members you might use. (The example described in this section is in the Application File Handling worksheet for the sample workbook.)

DefaultFilePath properties

This simple attribute gets or sets the path to the EXCEL to load and save the file:

'Visual Basic

'When the workbook Opens:

ThisApplication.range ("defaultfilepath"). Value = _

Thisapplication.defaultfilepath

'When You Save The DefaultFilePath Property:

Thisapplication.defaultfilepath = _

ThisApplication.Range ("defaultfilepath"). _

Value.tostring

// c #

// when the workbook Opens:

ThisApplication.get_Range ("defaultfilepath", type.missing).

Value2 = thisApplication.defaultfilepath;

// when you save the defaultfilepath property:

ThisApplication.defaultFilePath =

ThisApplication.get_Range ("defaultfilepath", type.missing).

Value2.toString ();

DefaultsaveFormat properties

This property gets or sets the default format saved for the workbook. Excel provides a lot of options for this property that is a member of the XLFileFormat enumeration. The sample workbook allows you to choose from an option, as shown in Figure 1. The following code snippet demonstrates how to load and save this property. In this example, the column E in the sample worksheet contains the name list of all possible values of the XLFileFormat enumeration (in the range of "XLFileFormat"), and column F contains the corresponding integer value. Figure 2 shows a subset of these two columns. The DEFAULTSAVEFORMAT naming range (in Figure 1) contains references to the XLFileFormat range, which allows you to choose from a list. Once you choose to save this value, the code must use the Range.Find method to find the string you have already selected, and then use the Range.Offset method to return a value with the specified offset you find the value. (For more information on the Range.Find method, see the part behind the title "Search within the range".) Finally, the code will re-saving the integer value (converted into an appropriate enumeration type) to the defaultsaveformat property.

Retrieving the current value of defaultsaveformat is very simple. The following code converts the value into text and displays it in the correct RANGE on the sample worksheet:

'Visual Basic

'When the workbook Opens, Convert the Enumerated Value

'Into A String:

ThisApplication.range ("defaultsaveformat"). Value = _

ThisApplication.defaultsaveformat.tostring

// c #

//Hen the workbook Opens, Convert the Enumerate Value

// INTO A STRING:

ThisApplication.get_Range ("defaultsaveformat", type.missing).

Value2 = thisApplication.defaultsaveformat.tostring ();

Re-assaying is difficult. This involves three steps. The code must handle the following tasks:

Retrieve the name of the save format from the DefaultsaveFormat range from the worksheet:

'Visual Basic

'Retrieve The Name of the New Save Format, as a string:

DIM strsaveformat as string = _

ThisApplication.Range ("defaultsaveformat"). _

Value.tostring ()

// c #

// Retrieve the name of the new save format,

// AS A String:

String strsaveformat = trisapplication.

GET_RANGE ("defaultsaveformat", type.missing).

Value2.toString ();

Find matching integer values (calling the range of the Range class) in the worksheet near the XLFileFormat range. Then, the code uses the Range.Offset property to retrieve the value of a column on the right:

'Visual Basic

DIM INTSAVEFORMAT AS INTEGER = _

CType ("XLFILEFORMAT"). _Find (strsaveformat) .offset (0, 1) .value, integer

// c #

Excel.Range RNG = thisApplication.

GET_RANGE ("XLFILEFORMAT", TYPE.MISSING;

Excel.Range Rngfind = RNG.Find (strsaveformat,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Excel.xlsearchDirection.xlnext, Type.Missing, Type.Missing,

TYPE.MISSING);

// in C #, Use the get_offset method instead of the offset printy:

INT INTSAVEFORMAT =

Convert.TOINT32 (RNGFind.get_offset (0, 1) .value2);

Re-assind the integer value to the DefaultSaveFormat property:

'Visual Basic

ThisApplication.defaultsaveformat = _

CType (INTSAVEFORMAT, Excel.xLfileFormat)

// c #

Thisapplication.defaultsaveformat =

Excel.xlfileFormat) INTSAVEFORMAT;

Figure 1. Select a file format from the list of available types.

Figure 2. A subset of the XLFileFormat range on the sample worksheet.

Recentfiles property

Recentfiles properties Returns a collection of strings that contains the names of all files in the most recently used file list that appears in the File menu. The length of this list will vary as the user has changed the number of files that has been retained. The sample workbook calls this process when it opens, and copies the nearest file list to the sample worksheet in the range of RecentFiles:

'Visual Basic

Private sub listRecentfiles ()

DIM I as integer

DIM RNG As Excel.Range = Directcast (_

ThisApplication.Range ("Recentfiles"). _

Cells (1, 1), Excel.Range)

For i = 1 to thisapplication.recentfiles.count

RNG.OFFSET (i - 1, 0) .value = _

Thisapplication.recentfiles (i) .name

End Sub

// c #

Private void listRecentFiles ()

{

Excel.Range RNG = (Excel.Range) thisApplication.

GET_RANGE ("Recentfiles", Type.Missing) .Cells [1, 1];

For (int i = 1; i <= thisapplication.recentfiles.count; i )

{

RNG.GET_OFFSET (i - 1, 0) .value2 =

Thisapplication.recentfiles [i] .name;

}

FileDialog attribute

The FileDialog property returns a FileDialog object, which handles four types of file operations. This FileDialog object returned by this property allows you: • Select a file and open it. • Select a file location and save the current workbook. • Select a folder. • Select a file name.

By using this dialog, you can use all file processing capabilities provided by Microsoft Office. The FileDialog property requires you to select a specific use of the dialog by passing a MsofileDialogType enumeration value (MsofilePicFolder, MsofileDialogfolderPicker, Msofiledialogopen, or MsofileDialogsaveas). You can then interact with the FileDialog object returned to this property.

Similar to many other objects, the FileDialog object is provided by Microsoft.Office.Core namespace. To avoid typing the full path to each Office object, the sample item uses the imports or using statement to import this namespace. The code snippet in this article also assumes that you have added the appropriate namespace reference to your file:

'Visual Basic

Imports office = Microsoft.Office.core

// c #

USING OFFICE = Microsoft.Office.core;

FileDialog object's show method Displays the dialog box and returns -1 (if you press OK) and 0 (if you press Cancel). If you have already used MsofileDialogopen or MsofileDialogsaves enumeration values, you can use this class's Execute method to actually open or save files. The SelectEMS property contains a collection of strings, each of which represents a selected file name.

For example, the code from the sample workbook is prompted to open a new workbook. This code snippet allows multiple selection to clear the list of available filters, add two new filters, then display the dialog, as shown in Figure 3. If you select a file or multiple files, the code calls the FileDialog object's Execute method to open the requested file:

'Visual Basic

With thisapplication.filedialog (_

Office.msofiledialogtype.msofiledialogtype.msofiledialogopen)

.Allowmultiselectr = true

.FILTERS.CLEAR

.Filters.add "excel files", "* .xls; *. Xlw"

.Filters.add "all files", "*. *"

IF .show <> 0 THEN

.Execute

END IF

End with

// c #

DLG = thisapplication.get_filedialog

Office.msofiledialogtype.msofiledialogtype.msofiledialogtype.ms

Dlg.filters.clear ();

Dlg.Filters.Add ("Excel Files", "* .xls; *. xlw", type.missing;

Dlg.Filters.Add ("all files", "*. *", type.missing;

IF (DLG.SHOW ()! = 0)

Dlg.execute ();

Figure 3. Using the FileDialog class to display the Standard File Open dialog. The code snippet from the example demonstrates how you can use the dialog box to select a folder:

'Visual Basic

With thisapplication.filedialog (_

Office.msofiledialogtype.msofiledialogfolderpicker)

IF .show <> 0 THEN

ThisApplication.Range ("FolderPickerResults"). _

Value = .selectedItems.Item (1)

END IF

End with

// c #

DLG = thisapplication.get_filedialog

Office.msofiledialogtype.msofiledialogfolderpicker;

IF (DLG.SHOW ()! = 0)

{

ThisApplication.get_Range ("FolderPickerResults", Type.Missing).

Value2 = dlg.selectedItems.Item (1);

}

Note Application object also provides a getOpenFileName and GetSaveASFileName method that allows you to select a file name of the file to open. Although you can use these methods, you will find the corresponding OpenFileDialog and SaveFileDialog controls provided by Microsoft .NET Framework more rich and easier to use.

Other useful members

The Application object provides some members that do not apply to other kinds of members, such as WorksheetFunction properties, Names collection, and Windows collection. These members will be described below.

Worksheetfunction class

The Application object contains an attribute WorksheetFunction, which returns an instance of the WorksheetFunction class. This class provides a number of shared / static methods, each of which is packaged in an Excel worksheet function. Each of these methods discloses one of many Excel spreadsheet computing functions, while VBAs do not provide these functions. And some of them have already provided in the operators and methods of Visual Basic .NET and C #, so you will not use these members (for example, and methods).

You will find a lot of interesting and useful functions in the method of the WorksheetFunction class, summarizing in the list below:

• Mathematics functions such as ACOS, ACOSH, Asin, Asinh, Cosh, Degrees, LN, LOG, Median, Max, Min, MODE, RADIANS, and more. • Domain functions allow you to perform operations, such as Daverage, Dcount, Dcounta, DGET, DMAX, DMIN, DPRODUCT, DSUM, and more. • Logic functions, such as ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISTEXT. • Statistical functions such as betadist, binomdist, chitest, chiinc, lognormd, negbinomdist, pearson, sumproduct, susq, tdist, ttest, var, varp, etc. • Spreadsheet functions, in .NET Framework, you will not use these functions, such as and, or, choose, and more. • Compliance with Thailand: You will find a large number of inexplicable functions, these functions are used to handle Thai numbers, calendars and currencies (rumored Excel teams have especially like Thai food, thus adding these functions to help calculate them local Thai restaurant meals, but now it seems that this rumor is not true), for example bahtText, IsThaiDigit, ThaiDayOfWeek, ThaiDigit, ThaiMonthOfYear, ThaiNumSound, ThaiNumString, ThaiStringLength, ThaiYear, RoundBahtDown and RoundBahtUp. In the Visual Studio .NET project, it is easy to use the WorksheetFunction class. Because the project template provides thisApplication object, you can simply reference the object's WorksheetFunction property. The sample application contains a worksheet called Other Application Members, as shown in Figure 4, which only tested several members of this class.

Note The WorksheetFunction class and its members provide a good example, explaining why the use of Excel objects from Visual Basic is much easier than using an equivalent code from C #. Many methods of the WorksheetFunction class require C # developers to pass 30 parameters, most of which are empty. Of course, the packaging of two required parameters, etc.) is undoubted by writing a variety of different ways of packaging (some necessary parameters, etc.). For the purpose of this article, the code calls "naked" method without using the packaging method. Of course, the C # code is difficult to see.

Click the DemonStrateWorksheetFunction link to run the following code (for more information on the Sort method, see "Singing the data within the range":

'Visual Basic

Private sub testworksheetfunction ()

DIM WS as Excel.Worksheet = _

Directcast (thisworkbook.activeesheet, excel.worksheet)

DIM RNG As Excel.Range = WS.RANGE ("RandomnumBers")

DIM RND As New System.random

DIM I as integer

For i = 1 to 20

WS.cells (i, 2) = rnd.next (100)

Next I

RNG.SORT (RNG, _

Orientation: = Excel.xlsortorientation.xlsortcolumns) with thisapplication.WorksheetFunction

WS.RANGE ("min"). Value = .min (RNG)

WS.RANGE ("max"). Value = .max (RNG)

WS.RANGE ("Median"). Value = .median (RNG)

WS.RANGE ("Average"). Value = .average (RNG)

WS.RANGE ("stdev"). Value = .stdev (RNG)

End with

End Sub

// c #

Private void testworksheetfunction ()

{

Excel.Worksheet WS = (Excel.Worksheet) thisworkbook.activesheet;

Excel.Range RNG = ws.get_range ("randomnumbers", type.missing;

System.random rnd = new system.random ();

For (INT i = 1; i <= 20; i )

Ws.cells [i, 2] = rnd.next (100);

RNG.SORT (RNG, Excel.xlsortorder.xlascending,

Type.Missing, Type.Missing, Excel.xlsortorder.xlascending,

Type.Missing, Excel.xlsortorder.xlascending,

Excel.xlyesnoguess.xlno, Type.Missing, Type.Missing,

Excel.xlsortorientation.xlsortcolumns,

Excel.xlsortmethod.xlpinyin,

Excel.xlsortDataOption.xlsortNormal,

Excel.xlsortDataOption.xlsortNormal);

Excel.WorksheetFunction WSF = thisapplication.WorksheetFunction;

WS.GET_RANGE ("min", type.missing) .value2 = wsf.min (RNG,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

TYPE.MISSING);

WS.GET_RANGE ("max", type.missing) .value2 = wsf.max (RNG,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

TYPE.MISSING);

WS.GET_RANGE ("Median", Type.Missing) .value2 = wsf.median (RNG,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

TYPE.MISSING);

WS.GET_RANGE ("Average", Type.Missing) .value2 = wsf.average (RNG,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

TYPE.MISSING);

WS.GET_RANGE ("stdev", type.missing) .value2 = wsf.stdev (RNG,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

TYPE.MISSING);

}

Figure 4. Select the WorksheetFunction workpiece to test the WorksheetFunction class and its useful way.

As you can see in the sample code, you can pass the RANGE object as a parameter to the WorksheetFunction method. In addition, you can also transfer single or values lists as parameters. These methods usually accept up to 32 parameters, so if you want to calculate the average of a fixed digital list, you can use the following code:

'Visual Basic

Dblaverage = thisapplication.WorksheetFunction.average (_

12, 14, 13, 19, 21)

// c #

// Note The Number of Type.Missing Values - The Method Accepts

// 30 parameters.

DBLAVERAGE = thisapplication.WorksheetFunction.average (

12, 14, 13, 19, 21,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

TYPE.MISSING);

Window class and windows collection

As you may expect, the Application object provides control of the window displayed in the Excel application, and you can open, close and arrange the Excel object window using the Windows property of the Application object.

The Windows property returns a collection of Window objects and you can call the Arrange method to arrange all open windows (or just visible windows). Specify an XlarRangeStyle enumeration value to indicate how you want to arrange the window, and you can also select some information about whether you just want to arrange the visible window, and how you want to synchronize the window scrolling. For example, to display the window in the Excel workspace, you can use the following code:

'Visual Basic

Thisapplication.windows.Arrange (_

Excel.xlarRangeStyle.xlarRangeStyletiled)

// c #

Thisapplication.windows.Arrange

Excel.xlarRangestyle.xlarRangeStyletiled,

Type.Missing, Type.Missing, Type.Missing;

If you want to create a new window by programming, you can call the workbook's newwindow method, for example:

'Visual Basic

Thisworkbook.newwindow () // c #

THISWORKBOOK.NEWWINDOW ();

Because the newWindow method returns a Window object, you can also write the following code, which set the title of the new window, then activate it:

'Visual Basic

With thisworkbook.newwindow ()

.Caption = "new window"

.Activate ()

End with

// c #

Excel.window wnd = thisworkbook.newwindow ();

Wnd.caption = "new window";

WND.Activate ();

The Windows class provides the properties and methods of the appearance and behavior of the control related window, including color, title, window characteristics, and scroll behavior. You can write the following code to use the properties of a specific window:

'Visual Basic

With thisapplication.windows (3)

.GridlineColor = colorTranslator.toole (color.red)

.Caption = "a new window"

.DisplayHeadings = false

.Displayformulas = false

.Displayworkbooktabs = false

.Splitcolumn = 1

End with

// c #

Wnd = thisapplication.windows [3];

Wnd.gridLineColor = ColorTranslator.Toole (Color.Red);

Wnd.caption = "a new window";

Wnd.displayHeadings = false;

WND.DISPLAYFORMULAS = FALSE;

WND.DISPLAYWORKBOOKTABS = FALSE;

WND.SPLITCOLUMN = 1;

Tip Although VBA and .NET use color through similar paradigm - each of the three sets of bytes, including the color, green and blue components of the color, the three low bytes encoded into 32-bit integers - but They have different ways to handle colors. You can use the System.drawing.Colortranslator.toole method to convert from .NET color to the OLE color required by VBA.

Click Work with Windows on the Other Application Members Worksheet to run the sample program TestWindows, which contains all the code provided in the form of a small range block in this section. Click RESET Windows in the same worksheet to run the following procedure, it will close all the windows other than the first window, then maximize the first window:

'Visual Basic

Private sub resetwindows ()

DIM I as integer

For i = thisapplication.windows.count to 2 step -1

Thisapplication.windows (i) .close ()

ThisApplication.windows (1) .windowstate = _

Excel.xlwindowState.xlmaximized

End Sub

// c #

Private void resetwindows ()

{

For (int i = thisapplication.windows.count; i> = 2; i -) thisapplication.windows [i] .close

False, Type.Missing, Type.Missing;

Thisapplication.windows [1] .windowstate =

Excel.xlwindowState.xlmaximized;

}

Name class and Names collection

The Application object provides its names property that returns a collection of Name objects. Each Name object corresponds to the name range in the Excel application. There are many ways to retrieve references to named scope - you can use the NAMES properties of the Workbook object, you can also use the NAMES properties of the Worksheet object.

To create a new naming range, you can use the Names collection add-on, as shown below the code snippet below. In addition to two required parameters, the Add method also accepts many optional parameters:

'Visual Basic

DIM NM as Excel.name

nm = thisapplication.names.add (_

"Newname", "= 'Other Application MEMBERS'! $ A $ 6")

// c #

Excel.name nm;

NM = thisapplication.names.add (

"Newname", @ "= 'Other Application MEMBERS'! $ A $ 6",

Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing;

Specify name and location (and other optional parameters), then you can reference this range in your code:

'Visual Basic

ThisApplication.Range ("newname"). Value = "Hello, World!"

// c #

THISAPPLICATION.GET_RANGE

"Newname", type.missing) .value2 = "Hello, World!";

In order to retrieve information about naming range, you can use the various properties of the Name class. The following list describes some of the most common members:

• Name returns a name assigned to the name. • ReferSTO returns a string containing the actual target address in standard format ("= SheetName! $ 25"). • ReferStor1c1 returns the target address in "R1C1" format ("= SheetName! R25C2"). • Value returns a reference to the naming range of the content parsing to the range.

Click the Work With the Work with Names link in the example to run the following code, populate a region of the worksheet for all naming ranges:

'Visual Basic

DIM NM as Excel.name

DIM RNG As Excel.Range = thisApplication.range ("names")

DIM I as integer

For i = 0 to thisapplication.names.count - 1

Nm = thisapplication.names.Item (i 1) RNG.Offset (i, 0) .value = nm.name

'Without the leading "'", The References

'get evaluated, Rather Than Displayed Directly.

RNG.OFFSET (i, 1) .value = "'" & nm.referSto.toString

RNG.Offset (I, 2) .value = "'" & nm.referStor1c1.toString

RNG.Offset (i, 3) .value = nm.value

Next I

// c #

Excel.Range RNG = thisApplication.get_range ("names", type.missing;

For (int i = 0; i <= thisapplication.names.count - 1; i )

{

Nm = thisapplication.names.Item (i 1,

TYPE.MISSING, TYPE.MISSING;

RNG.GET_OFFSET (I, 0) .value2 = nm.name;

// welHout the Leading "'", The References

// Get Evaluated, Rather Than Displayed Directly.

RNG.Get_offset (i, 1) .value2 = "'" nm.referSto.toString ();

RNG.GET_OFFSET (I, 2) .value2 = "'" nm.referStor1c1.toString ();

RNG.GET_OFFSET (I, 3) .value2 = nm.value;

}

Application event

In addition to all other methods provided by the Application class, you will also find a large group of events available. Although all events cannot be demonstrated in any consistent manner, it is clearly known to know their use according to the name. The following sections describe a subset of these events and discuss the most likely events that are most likely in your own application.

Tip Parameters of the event handler passing to the Office application will feel different from the parameters in this machine .NET event. Typically, the .NET event handler always receives the Object variable (object object objects) and the second parameter (this parameter is inherited from the Eventargs base class, including additional information about the event). There is no such definition of event design mode for Office applications, so each event handler accepts any number of parameters (defined by the initial developer).

Table behavior

The Application object provides a variety of events related to the table (including charts and worksheets). The following list contains information about many such events:

• When any of the table is activated, SheetAtAtiVate will happen. Excel passes an Object variable containing reference to the activated table to the event handler. Prompt as any of the cases of passing an Object of a reference table in Excel, before you can use this reference, you need to convert the reference forced into a correct type (Worksheet or Chart, depending on the specific situation). However, if you have disable Option Strict settings in Visual Basic .NET, you can use advanced binding. When you type, you will still not use IntelliSense, which makes the code be more difficult. All examples in this document are explicitly converted into the required specific types of tables (Worksheet or Chart) in the example of the SHETS collection. • SheetBeForedoubleClick will happen when any table is double-clicked before Excel. Excel passes the following parameters to the event handler: an Object variable containing the reference to the table, a RANGE object that contains the nearest cell that is close to the table, one allows you to cancel the Boolean value of the default event processing (default is false). (This event does not appear in the chart.) Tip All events that include the word "before" in their name allow you to cancel the default event processing. Parameters passing to your event handler are usually named CANCLE, with default value false. If this parameter is set to True, Excel will not perform the default processing of the event. • SHETBEFORHTCLICK will happen when any table is right-clicked in Excel. Exce delivers the following parameters to the Event Handle: A reference to the reference to the table, an RANGE object that contains a nearest cell that is close to the table, one allows you to cancel the Boolean value of the default event processing (default is false). (This event does not appear in the chart.) • Sheetcalculate will appear when any table is recalculated. Excel passes an Object that contains references to recalculate tables to the event handler. • When the cells in any work table changes (by the user or by running the code), sheetchange happens. Excel passes an Object variable (including reference to the table) and a range variable (scope of the reference change) to the event handler. • When any form is deactivated (ie when it no longer has a focus), sheetDeactivate will occur. This event handler runs only when the focus is transferred to another table in the same workbook. Excel passes an Object variable containing a reference to the already deactivated table to the event handler. • SheetFollowHyperLink will happen when you click any hyperlink in any workbook. Excel passes a reference to the Object variable containing this link and a HyperLink object that contains a reference to the link you click on the HyperLink object to the event handler. (The sample project uses this event to provide navigation in the example.) • SheetSelectionChange occurs when the selection changes on the worksheet changes (this event does not appear in the chart). Excel passes an Object variable of a reference to select the table that changes and a reference to the event handler to the event handler.

(Note that before the initial choice changes, Excel does not pass information about the original choice.) Note Each event in this section can also be used as an event provided by the Workbook class. If the event is provided by the Application object, it can be triggered by any of the currently opened in Excel. When it is provided by the Workbook object, the event only occurs when it affects one of the specific workbook. In addition, you will also find the same events provided by the Worksheet class. In this case, the event name does not contain the word "sheet" (for example, you will find FollowHyperLink instead of SheetFollowHyperLink, etc.), and the event handler does not pass the table's reference - this information is implied in receiving events Object. In addition, events and their usage methods and parameters are the same as those you see here. WINDOW behavior

Application objects (and the corresponding Workbook objects) provide events that process the behavior of the Window object. The following list describes these events:

• When any window is activated, WindowActivate will happen. Excel passes the following two parameters to the event handler: one is a Workbook object, this object references the workbook for the window; one is a Window object that references the selected window. Like other activation events, this event is also excited when the focus moves in Excel. Switch to another application, then return to Excel, this event will not be triggered. • WindowDeactivate will happen when any windows are disabled. For more information, see the WindowActivate event description. • When any workbook window is re-adjusted, WindowResize will happen. Excel passes a Workbook object that references a workbook that provides a window and a Window object that recommodes a reference size to the event handler. Note In an event provided by the Workbook class, the event handler does not receive reference to Workbook - this information is implied in an object that throws this event.

Workbook management

The Application object provides a variety of events that occur when you interact with any Workbook objects. Each of these events receives a Workbook variable that indicates a specific workbook participating in the event. The following list describes a subset of available events:

• NewWorkbook will happen when creating a new workbook. Excel passes an Workbook variable that references the new workbook to the event handler. (This event is only available by the Application class.) • WorkbookAction occurs when any workbook is activated. Excel passes an Workbook variable that references the activated workbook to the event handler. (Like other "activation" events, this event only occurs when you switch from a workbook to another.) • WorkbookBeforeClose will happen when an open workbook is just off before the default event is turned off. Excel passes a Boolean value that references the Workbook variable that will be closed and a Boolean value (that is, the Workbook opens) (ie, the Workbook opens open) is passed to the event handler. WARNING If you are placed to TRUE, do not consider any conditions, all workbooks will never be closed. • WorkbookBeforePrint will happen when printing in the workbook begins before the default event processing. Excel uses a Workbook variable that includes a workbook that contains the print content and a Boolean value that allows the event handler to cancel the default event (ie, the skip request) (the default is false) to the event handler. • WorkbookBeforesave will happen when you have saved your workbook before the default event process. Excel will pass a Workbook variable that stores a saved workbook and a Boolean value (ie, unsave) Boolean value (ie, canceling) to cancel the default event (ie, unforgettable). • WorkbookDeactivate will happen when any workbook is deactivated. Excel passes an Workbook variable that references the already deactivated workbook to the event handler. (Like other "activation" events, this event only happens when you switch from a workbook to another.) • Workbooknewsheet will happen when adding a new table to a workbook. Excel passes an Object variable of a reference workbook and an Object variable of a reference new table to an event handler. • Workbookopen will happen when a workbook is turned on. Excel passes an Workbook variable that references the newly opened workbook to the event handler. Note The Workbook class provides its own set of events, which is very similar to the events you see here. All events starting with "Workbook" appears in the event list of the Workbook class without the delegate ("Activate" instead of "WorkbookActivate", etc.). The Workbook class event handler does not receive the Workbook variable as a parameter; this information is implied in the object that triggers this event. In addition, the Workbook class also provides mirroring of other Application object events, but only captures them for a single workbook, which is compared to all workbooks. The remainder of this document will not discuss events because you have now known some events you most likely to use. Back to top

Workbook class

As you might imagine, the Workbook class represents a single workbook in the Excel application. In this section, you will understand some members of this class, including those that are most commonly used.

Prompt many members of the Application class also introduce as members of the Workbook class. In this case, its properties apply to a specific workbook without applicable to the active workbook. The members you want to discuss far more than the discussed part of the previous part, mainly because you have already understood many of the members mentioned. The properties of the Workbook class

The Workbook class provides a large number of properties (approximately 90), and there are many attributes to handle special circumstances that most developers will never take into account; for example, the autOUpdateFrequency property returns the number of automatic updates of the shared workbook; if the workbook uses 1904 Date System (a date order plan, which will be used as a date corresponding to the value 1 on 2, 1904, usually used in the Macintosh computer), the Date1904 property returns true value; PasswordEncryptionAlgorithm property allows you to set the password Exact algorithm, etc.

This part only introduces the most likely used Workbook object properties, rather than trying to fully introduce its numerous properties. The usual rule is: If you need a workbook for a workbook, others may have requested this behavior, which is actually the most likely to allow this behavior, which is usually provided by a method. Carefully check the documentation before you add your own code to a workbook.

The following list describes some of the most commonly used Workbook properties:

• Name, Fullname, Path: These properties return different versions of workbook names, respectively. FullName Returns the full path name, including a workbook file name. Name just returns the name part, and PATH only returns the path portion. Click the Name Information link in the sample workbook to run the following code and return information, as shown in Figure 5: 'Visual Basic

ThisApplication.Range ("WorkbookName"). Value = _

THISWORKBOOK.NAME

Thisapplication.range ("WorkbookPath"). Value = _

Thisworkbook.path

ThisApplication.range ("WorkbookFullname"). Value = _

Thisworkbook.fullname

// c #

ThisApplication.get_Range ("WorkbookName", Type.Missing).

Value2 = thisworkbook.name;

THISAPPLICATION.GET_RANGE ("WorkbookPath", Type.Missing).

Value2 = thisworkbook.path;

THISAPPLICATION.GET_RANGE ("WorkbookFullName", Type.Missing).

Value2 = thisworkbook.fullname;

Figure 5. Retrieving information about the name using the Workbook property.

• Password: Get or sets the password related to the workbook. If you specify a non-empty password, the Haspassword property of the workbook will return true. You can retrieve the Password property, but its value is always "********". Click the SET Password link in the sample workbook to run the following code, which can set or clear the workbook password according to your providing text or only the empty string. This example uses a form named Password in the sample project, which provides a text box and a password property: 'Visual BasicPrivate Sub setPassword ()

DIM FRM AS New Password

IF frm.showdialog = DialogResult.ok then

Thisworkbook.password = frm.password

END IF

frm.dispose ()

End Sub

// C #

Private void setpassword ()

{

Password fm = new password ();

IF (frm.showdialog () == DialogResult.ok)

Thisworkbook.password = fr.value;

frm.dispose ();

}

• PrecisionAsDisplayed: If true, Excel uses the number of decimal display. If false (default), Excel uses all available decimal numbers, even those that are not displayed at all. Figure 6 shows an example workbook that this property is set to TRUE. Each value in the column C column is a copy of the value in the column B column, but the digital format in the column C column has been set to only two decimal data. It should be noted that if the precisionasdisplayed attribute is set to true, it will be different, because the actual value will be different after the four rounds. . If you click PrecisionASdisPlayed = False link, ask for again. Click the following process to pass the TRUE or FALSE value (depending on the link you click): 'Visual Basic

Private Sub TestPrecisionaSdisplayed (_

BYVAL ISPRISIONASDISPLAYEDON AS BOOLEAN)

Thisworkbook.precisionaSdisplayed = IsPrecisionAsDisplayedon

End Sub

// C #

Private void testprecisionasdisplayed

Bool isprecisionasdisplayedon)

{

THISWORKBOOK.PRECISIONASDISPLAYED =

ISPRECISIONASDISPLAYEDON;

}

Figure 6. Set the precisionasdisplayed attribute to true, Excel only uses the displayed decimal number of decimers.

• Readonly (Boolean, read-only): If the workbook is opened in a read-only mode, this property returns the TRUE value. At this point, if you cannot save the data to the workbook, you can take other different operations in your application. • Saved: Used to get or set the save status of the workbook. If the user has modified the content or structure of the workbook, the Saved property is TRUE. If you try to close your workbook or exit Excel, an alert prompts you save a workbook (unless you have set the Application.Displayalerts property to false). If you set the saved property value to false in your code, Excel will think that your workbook has been saved and will not remind you to save it again. Use the Document property

As with other Office applications, Excel allows you to save document properties while saving a workbook. Excel provides many built-in properties and you can also add your own properties. Select File | Properties to display the dialog shown in Figure 7 and you can also select the Custom tab to create and modify custom properties.

Figure 7. Setting the document properties using this dialog.

Use the built-in properties through the Built IndocumentProperties property of the Workbook class and use the CustomDocumentProperties property to use custom properties. These attributes return a DocumentProperties object that is a collection of DocumentProperty objects. The specified properties can be retrieved using the names or indexes in the collection. There is a list of all attribute names in the Excel document, but there is a simple way to retrieve the list: When you click the Document Properties link in the sample workbook (see Figure 8). This process calls the DUMPPROPERTYCOLLECTION method lists all built-in properties and their current values and then repeats the custom attributes. In addition, the process also modifies the Revision Number property separately and creates a new custom attribute:

'Visual Basic

Private sub displayDocumentproperties ()

DIM PRP as office.documentproperty

DIM PRPS as office.documentproperties

DIM RNG AS Excel.range = _

Thisapplication.range ("DocumentProperties")

DIM I as integer

Try

Thisapplication.screenupdating = false

Try

PRPS = Directcast (_

Thisworkbook.builtindocumentproperties, _

Office.Documentproperties)

'Set the revision number property:

PRP = prps.Item ("Revision Number")

Prp.Value = CType (Prp.Value, Integer 1

'Dump Contents of The Collection:

DUMPPROPERTYCOLLECTION (PRPS, RNG, I)

Catch exception

Messagebox.show (ex.Message)

END TRY

'Work with custom property:

Try

PRPS = Directcast (_

Thisworkbook.customDocumentproperties, _

Office.Documentproperties)

DUMPPROPERTYCOLLECTION (PRPS, RNG, I)

Catch exception

Messagebox.show (ex.Message)

END TRY

'Add a Custom Property:

Try

'Delete the property, if it exists.

PRP = prps.Item ("Project Name")

Prp.delete ()

Catch

'Do nothing if you get an exception.

END TRY

Try

'Add a new property.

PRP = prps.add ("Project Name", False, _

Office.msodocproperties.msopropertytypeString, _

"White papers")

Catch exception

Messagebox.show (ex.Message)

END TRY

Finally

Thisapplication.screenupdating = TRUE

END TRY

End Sub

Private sub DumppropertyCollection (_

Byval prps as office.documentproperties, _

Byval RNG As Excel.Range, byref i as integer

DIM PRP as office.documentproperty

For Each Prp in PRPS

RNG.OFFSET (i, 0) .value = prp.name

Try

IF not prp.value is nothing then

RNG.OFFSET (i, 1) .value = _

Prp.Value.toString

END IF

Catch

'Do nothing at all.

END TRY

i = 1

End Sub

// C #

Private Void DisplayDocumentProperties ()

{

Office.documentproperty prp = null;

Office.Documentproperties prps =

(Office.DocumentProperties)

Thisworkbook.builtindocumentproperties;

Excel.Range RNG = thisApplication.

GET_RANGE ("DocumentProperties", Type.Missing;

INT i = 0;

Try

{

Thisapplication.screenupdating = false;

Try

{

// set the revision Number Property:

Prp = prPs ["revision number"];

Prp.Value = Convert.Toint32 (Prp.Value) 1;

// Dump Contents of the Collection:

I = DUMPPROPERTYCOLLECTION (PRPS, RNG, I);

}

Catch (Exception EX)

{

Messagebox.show (ex.Message, thisApplication.name);

// Work with custom property:

Try

{

PRPS = (Office.DocumentProperties)

THISWORKBOOK.CUSTOMDocumentProperties;

DUMPPROPERTYCOLLECTION (PRPS, RNG, I);

}

Catch (Exception EX)

{

Messagebox.show (ex.Message, thisApplication.name);

}

// Add a Custom Property:

Try

{

// delete the property, if it exists.

PRP = prPs ["Project Name"];

prp.delete ();

}

Catch

{

// do Nothing if you get an exception.

}

Try

{

// Add a new profment.

PRP = prps.add ("Project Name", False,

Office.msodocproperties.msopropertytypeString,

"White Papers", Type.Missing;

}

Catch (Exception EX)

{

Messagebox.show (ex.Message, thisApplication.name);

}

Finally

{

Thisapplication.screenupdating = true;

}

Private int dumppropertycollection

Office.DocumentProperties PRPS, Excel.Range RNG, INT i)

{

Foreach (Office.DocumentProperty Prp in PRPS)

{

RNG.Get_offset (i, 0) .value2 = prp.name;

Try

{

IF (Prp.Value! = null)

{

RNG.GET_OFFSET (i, 1) .value2 =

Prp.Value.toString ();

}

Catch

{

// do nothing at all.

}

i = 1;

}

Return I;

}

Tip The previous code example DisplayDocumentProperties uses several enumerations and types in the Microsoft.Office.Core program. The sample code contains an imports / using statement that sets text "Office" to this namespace abbreviation, just like setting "Excel" abbreviation. The project template will automatically set "Excel" abbreviation. And you need to add a "office" statement yourself.

Figure 8. Built-in document properties

Note Although you are using Excel and its objects, it is actually Office provides a list of available built-in document properties, and Excel does not need to implement all properties - if you try to access a value property that does not define an undefined property, it triggers An exception. The example process contains simple abnormal processing that cope with this situation (if it will appear).

Use style

And the Word document is very similar, the Excel workbook allows you to apply the specified style to the area within the workbook, and Excel provides many predefined (although not very eye-catching) styles. Using the Format | Style menu item, a dialog is displayed, which allows you to modify the style interactively, as shown in Figure 9.

Figure 9. Using this dialog to modify the style.

Click Modify in the Style dialog to display the "cell format" dialog as shown in Figure 10. Figure 10. Modify the style using the "cell format" dialog.

The "cell format" dialog displays all options you can use when formatting units, all options available in this dialog can also be used in your code. You can use the Styles property of the Workbook object to interact with the workbook and apply the style for the range within the workbook.

By using the Styles property of the Workbook object, you can create, delete, and modify the style. Click Apply Style in the sample workbook to run the following procedure, it creates a new style (if you have already running this code, use existing style), set all aspects of the style, and apply it To a zone:

'Visual Basic

Private sub ApplyStyle ()

Const style_name as string = "PropertyBorder"

DIM RNG AS Excel.Range

'Get The Range Containing All The Document Properties.

RNG = getDocproprange ()

DIM STY as Excel.Style

Try

STY = thisworkbook.styles (style_name)

Catch

STY = thisworkbook.styles.add (style_name)

END TRY

Sty.font.name = "verdana"

Sty.font.size = 12

STY.FONT.COLOR = ColorTranslator.Toole (color.blue)

STY. Interior.color = ColorTranslator.Toole (Color.lightgray)

Sty.interior.pattern = xlpattern.xlpatternsolid

RNG.Style = style_name

RNG.COLUMNS.AUTOFIT ()

End Sub

// C #

Private void ApplyStyle ()

{

Const string style_name = "PropertyBorder";

// Get The Range Containing All The Document Properties.

Excel.Range RNG = getDocproprange ();

Excel.Style Sty;

Try

{

STY = thisworkbook.styles [style_name];

}

Catch

{

STY = thisworkbook.styles.add (style_name, type.missing);

}

Sty.font.name = "verdana";

STY.FONT.SIZE = 12;

STY.FONT.COLOR = ColorTranslator.Toole (color.blue);

STY. Interior.Color = ColorTranslator.Toole (Color.lightgray);

STY. Interior.pattern = Excel.xlpattern.xlpatternsolid;

RNG.Style = style_name;

RNG.COLUMNS.AUTOFIT ();

}

The getDocproprange method returns a range that is filled by the document property. This process uses the range.end method to find the end of the scope of the document property, and create a new range based on the left corner of this range and the lower right corner: 'Visual Basic

Private function getDocproprange () AS Excel.Range

DIM RNG AS Excel.range = _

Thisapplication.range ("DocumentProperties")

Dim rngstart as excel.range = _

Directcast (RNG.Cells (1, 1), Excel.Range

DIM RNGEND AS Excel.Range = _

RNG.End (Excel.xLDirection.xLDown) .offset (0, 1)

Return thisApplication.range (RNGStart, RNGEND)

END FUNCTION

// c #

Private Excel.Range getDocproprange ()

{

Excel.Range RNG =

ThisApplication.get_Range ("DocumentProperties", Type.Missing;

Excel.Range RNGStart =

(Excel.Range) RNG.Cells [1, 1];

Excel.Range RnGend =

RNG.GET_END (Excel.xLDirection.xLdown) .get_offset (0, 1);

Return thisApplication.get_range (RNGStart, RNGEND);

}

Tip To know more about retrieving and using the RANGE object, see the chapter of "Using Range" later.

Once you have run this code, the area containing document properties in the sample workbook will change the shading and font, as shown in Figure 11.

Figure 11. Apply a custom style

Click Clear Style to run the following procedure, it clears the style of the same area:

'Visual Basic

Private sub coplearsTyle ()

'Get The Range Containing All The Document Properties, And

'CLEAR THELE.

GetDocproprange (). Style = "normal"

End Sub

// c #

Private void clearstyle ()

{

// Get The Range Containing All The Document Properties, And

// Clear the STYL.

GetDocproprange (). Style = "normal";

}

Use

The Workbook class provides a Sheets property that returns a Sheets object. This object contains a collection of the Sheet object, where each object can be both a Worksheet object or a Chart object. Click the list of the sample workbook to run the following process, which lists all existing tables in the workbook:

'Visual Basic

Private sub listsheets ()

DIM SH as Excel.Worksheet

DIM RNG AS Excel.Range

DIM I as integer

RNG = thisapplication.range ("sheets")

For each sh in thisworkbook.sheetsrng.offset (i, 0) .value = sh.name

i = i 1

Next sh

End Sub

// C #

Private void Listsheets ()

{

INT i = 0;

Excel.Range RNG =

ThisApplication.get_Range ("Sheets", Type.Missing;

Foreach (Excel.Worksheet Shin thisworkbook.sheets)

{

RNG.GET_OFFSET (I, 0) .value2 = sh.name;

i = i 1;

}

You may also find the following SHETS class membership very useful.

• The Visible property allows you to display or hide an existing table without deleting and rebuilding a table. The Visibility property can be set to a value in the XlsheetVisibility enumeration value (XlsheetVisible). Using Xlsheethidden allows users to hide the table through the Excel interface; use Xlsheetveryhidden, ask you to run code to cancel the hidden table: 'Visual Basic

Directcast (thisworkbook.sheets (1), Excel.Worksheet) .visible = _

Excel.xlsheetvisibility.xlsheetveryhidden

// C #

(Excel.Worksheet) thisworkbook.sheets [1]). Visible =

Excel.xlsheetvisibility.xlsheetveryhidden;

• Add method allows you to add a new table to the table collection in the workbook, and accept four optional parameters, which can indicate the location of the table, the number of points to add, and the type of table (worksheet, chart Wait): 'Visual Basic

DIM SH as Excel.sheet = thisworkbook.sheets.add ()

// C #

Excel.sheet sh = thisworkbook.sheets.add (

Type.Missing, Type.missing, Type.Missing, Type.Missing;

• Copy method Creates a copy of a table and inserts the table into the specified location. You can freely specify the new table into the front or back of the existing table. If you don't specify a location, Excel creates a new workbook to store this new table. The following code snippet copies the first table in the current workbook and puts a copy behind the third table: 'Visual Basic

Directcast (thisworkbook.sheets (1), excel.worksheet). _

Copy (after: = thisworkbook.sheets ((3)))

// C #

((Excel.Worksheet) thisworkbook.sheets [1]).

Copy (Type.Missing, thisworkbook.sheets [3]) ;.

• Delete method Deletes a specified table: 'Visual Basic

Directcast (thisworkbook.sheets (1), Excel.Worksheet). Delete

// C #

(Excel.Worksheet) thisworkbook.sheets [1]). Delete ();

• The FillacrossSheets method copies the data in the range of a table within the workbook to all other tables. You need to specify a range, and whether you want to copy data, format, or all, the rest of the work Excel will deal with it. The following code snippet is in the workbook, and the data named DATA and copy it to the same area of all tables, and formats these data: 'Visual BasiceSworkbook.sheets.fillacrosssheets (_

Thisapplication.range ("data"), excel.xlfillwith.xlfillwithall

// C #

Thisworkbook.sheets.fillacross Sheets (

THISAPPLICATION.GET_RANGE ("Data", Type.Missing,

Excel.xlfillwith.xlfillwithll;

• The MOVE method and the copy method are very similar, but the event you get is an instance of the table. You can specify where you put the table in front of your table or put it behind (but not specified at the same time). Similarly, if you do not specify a location for the moving table, Excel creates a new workbook to include it. The following code snippet moves the first workfinder to the last: 'Visual Basic

Dim SHTS as Excel.sheets = thisworkbook.sheets

Directcast (SHTS (1), Excel.Worksheet) .MOVE (after: = SHTS (SHTS.COUNT))

// C #

Excel.sheets shts = thisworkbook.sheet;

(Excel.Worksheet) SHTS [1]). Move (Type.Missing, SHTS [SHTS.COUNT]);

Tip If you want to sort a list of tables in your workbook for some reason, you can use the MOVE method to make a inefficient bubble sort. Of course, you can't have too many tables, so the sorting speed is not a problem. • The PrintOUT method allows you to print the selected object (this method is suitable for multiple different objects). You can specify a number of optional parameters, including: The number of pages to be printed (start page and termination page), copy quantity, preview before printing, whether the name of the printer is printed, is it possible Document printing and file name you want to print. The following example uses the default printer prints the specified table, only prints the second page, prints two copies, and preview the document before printing: 'Visual Basic

Directcast (thisworkbook.sheets (1), excel.worksheet). _

PrintOut (from: = 1, to: = 1, COPIES: = 2, preview: = TRUE)

// C #

((Excel.worksheet) thisapplication.sheets [1]).

PrintOut (1, 1, 2, true, type.missing, type.missing,

TYPE.MISSING, TYPE.MISSING;

• The PrintPreview method allows you to display the specified object in the PrintPreView window, and you can choose to change the page layout: 'Visual Basic

Directcast (thisworkbook.sheets (1), excel.worksheet). _

PrintPreview (False)

// C #

((Excel.worksheet) THISPPLICATION.SHEETS [1]). PrintPreview; • SELECT method Select the specified object and change the user's selection (you can use the Activate method to get the object to get the focus without changing the user's choice. You can selectively provide a reference to the object being replaced by the currently selected replacement. The following code snippet Select the first worksheet: 'Visual Basic

Activeworkbook.sheets (1) .select ()

// C #

(Excel.Worksheet) thisapplication.sheets [1]). Select (type.missing);

The many methods listed in this section also apply to other classes. For example, the Printout method is provided by: Chart, Charts, Range, Sheets, Window, Workbook, Worksheet, and Worksheets. The specific use of these methods is the same, but it is only on different objects. The SELECT method is almost suitable for any optional object (and there are many objects).

Method of Workbook class

The Workbook class provides a lot of ways, with many ways to handle some very special situations. This part explores some methods you might use in each application, rather than detailing various methods, and put some difficult solutions in the following chapters, the following list describes some of you most Methods possible:

• Activate method activates a workbook and selects the first worksheet in a workbook: 'Visual Basic

Thisapplication.Workbooks (1) .activate

// C #

Thisapplication.Workbooks [1] .actiVate;

• Close method Close a specified workbook and (optional) Specifies whether to save modifications. If the workbook has never been saved, you can specify a file name. Also, if you want to send a workbook to another user, you can specify whether you want to send a workbook to the next user. The following code snippet turns off the workbook and does not save the modification: 'Visual Basic

Thisapplication.Workbooks (1) .close (SaveChanges: = false)

// C #

Thisapplication.Workbooks (1) .close (False,

TYPE.MISSING, TYPE.MISSING;

• The Protect and UNPROTECT methods allow you to protect a workbook so that you cannot add or remove a worksheet, and cancel your workbook again. You can specify a password (optional) and indicate whether the structure of the workbook (so users cannot move the worksheet) and the window of the workbook (optional). Protecting workbook users can still edit cells. To protect data, you must protect the worksheet. Call the UNPROTECT method (if necessary, pass a password) can cancel the protective workbook. The following example assumes a process named getPasswordFromuser, which requires the user to enter a password and return the input value: 'Visual Basic

Thisapplication.Workbooks (1) .protect (getpasswordfromuser ())

// C #

Thisapplication.Workbooks [1] .protect

GetPasswordFromuser (), Type.Missing, Type.missing; • As you think, Save method saves a workbook. If you have not saved a workbook, you should call the SaveAs method so you can specify a path (if you haven't saved the workbook, Excel will save it in the current folder and give it when you create a workbook. Name named: 'Visual Basic

'Save All Open Workbooks.

DIM WB AS Excel.Workbook

For Each WB in thisapplication.Workbooks

Wb.save

Next WB

// C #

// Save all Open Workbooks.

Foreach (Excel.Workbook wb in thisapplication.workbooks)

{

Wb.save ();

}

• The SaveAs method is more complicated than the Save method. This method allows you to save the specified workbook, and specify names, file formats, passwords, access modes, and other more options (optional). Check online help to get all the list of options. The following code snippet saves the current workbook to a specified location and stores an XML format: 'Visual Basic

ThisApplication.Activeworkbook.saveas ("c: /myworkbook.xml", _

FileFormat: = Excel.xlFileFormat.xlxmlspreadsheet)

// C #

Thisapplication.Activeworkbook.saves ("c: //myworkbook.xml",

Excel.xlfileformat.xlxmlspreadsheet, Type.Missing,

Type.Missing, Type.Missing, Type.Missing,

Excel.xlsaveasaccessMode.xlnochange, Type.Missing,

Type.Missing, Type.missing, Type.Missing, Type.Missing;

Tip Due to some interactions in some formats, you may want to set the Application.Displayalerts property to false before calling the SaveAs method. For example, Excel will remind you to save VBA projects with your workbook. This warning will not occur if the Displayalerts property is set to FALSE. • SaveCopyas Method Saves a copy of the workbook into the file, but does not modify the workbook opened in memory. This method is useful when you want to create a workbook backup, this method is useful when you do not modify the workbook: 'Visual Basic

Thisapplication.ActiveWorkbook.savecopyas ("c: /test.xls")

// C #

ThisApplication.ActiveWorkbook.savecopyas ("C: //Test.xls");

WARNING Cancel any save or copy the workbook method will trigger a runtime exception in your code. For example, if your procedure calls the SaveAs method, but does not disable the Excel's prompt function, and your user clicks "Cancel" after the prompt, Excel returns the runtime error to the code.

Worksheet class

When you read this article, you have learned most of the concepts that need to be mastered using a separate worksheet. Although the Worksheet class provides a large number of members, most of their properties, methods, and events are identical or similar to those provided by Application and / or Workbook class. This section will focus on important members and specific issues of Worksheet classes, which are not touched by the other parts of this article. (You can see the example in this section in the Worksheet Object worksheet in the sample workbook.) Does not exist the Sheet class

Although Excel provides an attribute of a Sheets collection as a Workbook object, you can't find the Sheet class in Excel. Instead, each member of the Sheets collection is a Worksheet or a Chart object. You can consider it in this way: seeing the Worksheet and Chart class as a specific instance of the internal Sheet class (and people who cannot access the source code will not know if this view is consistent with the actual implementation), but Sheet The class is not available to the outside.

Use protection

Typically, the protection feature in Excel prevents users and (or) code to modify objects in the workpiece. Once you have enabled worksheet protection, users cannot edit or modify worksheets unless you pre-arrangement. In the user interface, you can use the Tools | Protection | Protect Sheet menu item to enable protection. The Protection Worksheet dialog box is displayed after selecting this item, as shown in Figure 12. You can set your password or allow the user to perform a specific operation. By default, once the protection feature is enabled, all cells are locked. In addition, by using Tools | Protection | Allow Users to Edit Ranges menu items (which displays the dialog shown in Figure 13), you can edit users to edit specific areas. Use these two dialogs to lock the worksheet, then allow users to edit specific features and areas.

Figure 12. In the user interface, use the control protection of this dialog.

Figure 13. Using this dialog, you can allow users to edit a particular area.

You can use the Protect method of the worksheet to control the protection of the worksheet by programming methods. The syntax of this method is as shown in the example below, each of which is optional:

'Visual Basic

WorksheetObject.Protect (Password, DrawingObjects, Contents, _

Scenarios, UserInterfaceonly, AllowFormattingcells, _

AllowFormattingColumn, AllowFormattingRows ,_

AllowInsertingColumn, AllowInsertingRows ,_

AllowInsertinghyperlinks, AllowDeletingColumns, _

AllowDeleTingRows, Allowsorting, ALLOWLTERING, _

AllowusingPivottables)

// C #

WorksheetObject.protect (Password, DrawingObjects, Contents,

Scenarios, UserInterfaceonly, AllowFormattingcells,

AllowFormattingColumn, AllowFormattingRows,

AllowInsertingColumn, AllowInsertingRows,

AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeleTingRows, Allowsorting, AllowFiltering,

AllowusingPivottables;

The following list describes the parameters of the Protect method:

• Set the Password parameter to specify a case-sensitive string, which is required to cancel the protection worksheet. If you do not specify this parameter, anyone can cancel the protection worksheet. • Set the DrawingObjects parameter to True to protect the shape of the worksheet. The default is false. • Set the Contents parameter to True to protect the contents of the worksheet (cell). The default is true, you may never change it. • Set the Scenarios parameter to True to protect the schemes in the worksheet. The default is TRUE. • Set the userinterfaceonly parameter to TRUE to allow via code modification, but is not allowed to modify via user interface. The default value is False, which means that the protected worksheet cannot be modified by code and user interface items. This property setting is only available for current sessions. If you want the code to operate a worksheet in any session, you need to add the code that sets this property when you open each workbook. • The remaining parameters shown in the fullyowformattingcells parameter, the completeowformattingcolumns parameter, the full list of the front method syntax allows specific formatting functions, corresponding to the options (as shown in Figure 12) in the dialog. By default, all these properties are false.

You can call the worksheet's Protect method to protect the worksheet, as shown in the following code snippet, this code sets the password and only allows sorting:

'Visual Basic

Directcast (thisapplication.sheets (1), excel.worksheet). _

Protect ("mypassword", allowsorting: = true)

// C #

(Excel.Worksheet) thisapplication.sheets [1]). Protect (

"Mypassword", Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, True, Type.Missing, Type.Missing;

Tip is obvious that hard coding password in your code is not a good idea. The most common situation is that you need to get your password from the user, then apply this password to a workbook, but not saved. Typically, you will not see hard-coded passwords in the source code.

In order to cancel the protection of the worksheet, you can use the following code. This code is assumed to have a process named getPasswordFromuser, which requires the user to enter a password and return the input password value:

'Visual Basic

Directcast (thisapplication.sheets (1), excel.worksheet). _

Unprotect (getPasswordFromuser ())

// C #

((Excel.worksheet) thisapplication.sheets [1]).

Unprotect (getPasswordFromuser ());

UNPROTECT methods will cancel the protection of worksheets and let you provide an optional password. Excel also provides two other objects that you will find that they are useful when using: Protection and AlloweditRange objects. The Protection object encapsulates all the information you specified when you call the Protect method, and information in the unprotected zone. By calling the Protect method to set the properties of the shared Protection object, these objects provide the Boolean property of the parameters corresponding to the Protect method:

• AllowDeletingColumns, AllowDeletingRows • AllowFiltering • AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows • AllowInsertingColumns, AllowInsertingHyperlinks, AllowInsertingRows • AllowSorting • AllowUsingPivotTables

In addition, the Protection class provides the AllowedItRanges property that allows you to specify the editable area on the worksheet, corresponding to the information specified in the dialog shown in Figure 13. The AlloweditRanges property contains an AllowediTRANGE object collection, each of which provides many useful properties, including:

• Range: Gets or sets the range of the editable area • Title: Gets or sets the title of the editable area (for display in the dialog shown in Figure 13). • Users: Get or sets the useerAccess object collection (for more information on the useerAccess object, please refer to the online document).

In the WorksheetObject worksheet (see Figure 14) on the sample workbook, you can test the protection functionality implemented by programming. Click the Protect Protection Worksheet so you can only edit the contents of the shadow area (the names INFORMATION and DATE). Click UNPROTECT to cancel the protection worksheet.

Figure 14. Protection of test worksheets.

The link in the sample worksheet will run the following process:

'Visual Basic

Private sub protectsheet ()

DIM WS as Excel.Worksheet = _

Directcast (thisapplication.activeesheet, excel.worksheet)

With ws.protection.alloweditRanges

.Add ("information", thisapplication.range ("information"))

.Add ("date", thisapplication.range ("date"))

End with

ws.protect ()

End Sub

Private sub unproductsheet ()

DIM WS as Excel.Worksheet = _

Directcast (thisapplication.sheets ("Worksheet Class"), _

Excel.Worksheet)

'Unprotect the sheet.

WS.Unprotect ()

'Delete All Protection Ranges, Just to Clean Up.

'You must loop through this using the index,' backwards. This Collection Doesn't Provide

'An Enumeration Method, And It Doesn't Handle

'Being Resized as you're looping in a nice.

DIM I as integer

With ws.protection.alloweditRanges

For i = .count to 1 step -1

.Item (i) .delete ()

Next i

End with

End Sub

// C #

Private void protectsheet ()

{

Excel.Worksheet WS =

(Excel.Worksheet) thisApplication.activesheet;

Excel.alloweditRanges Ranges = ws.protection.AlloweditRange;

Ranges.add ("Information",

Thisapplication.get_range ("Information", Type.Missing,

TYPE.MISSING);

Ranges.add ("Date",

ThisApplication.get_Range ("Date", Type.Missing), Type.Missing;

Ws.protect (Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing,

TYPE.MISSING, TYPE.MISSING;

}

Private void unproductsheet ()

{

Excel.Worksheet WS =

(Excel.Worksheet) THISPPLICATION.SHETS ["Worksheet Class"];

WS.Unprotect (Type.Missing);

// delete All Protection Ranges, Just to Clean Up.

// you must loop through this using the index,

// backwards. This Collection Doesn't Provide

// an enumeration method, and it doesn't handle

// being resized as you're looping in a nice way.

Excel.alloweditRanges Ranges = ws.protection.AlloweditRange;

For (int i = ranges.count; i> = 1; i -)

{

Ranges [i] .delete ();

}

Object properties

The Worksheet class provides several properties that return objects. The following sections will introduce these objects and provide examples of these objects.

annotation

Using the INSERT | Comment menu item, you can insert additional text annotations in a range of a worksheet (see Figure 15). The addcomment method using the RANGE object in the code can also achieve the same purpose. The following code deletes the annotation associated with the range named Date (if there is any annotation), then create a new annotation. Finally, the code displays all the annotations in the work table by calling the ShoworHideComments method described in the next code example (see Figure 16): 'Visual Basic

DIM RNG As Excel.Range = thisApplication.range ("date")

IF not rng.comment is nothing then

RNG.Comment.delete ()

END IF

RNG.ADDComment ("Comment Added" & DateTime.now)

'Display All the Comments:

ShoworHideComments (show: = true)

// C #

Excel.Range RNG = thisApplication.get_range ("Date", Type.Missing;

IF (RNG.Comment! = NULL)

{

RNG.Comment.delete ();

}

RNG.ADDComment ("Comment Added DateTime.now);

// Display All the Comments:

ShoworhideComments (TRUE);

Figure 15. In the user interface, you can easily insert a new annotation into the worksheet.

Figure 16. After the sample worksheet is displayed

The Worksheet class provides its Comments property, which returns a Comments object. This Comment object collection allows you to loop all Comment objects related to Worksheet. The Comment class does not provide many members. You can use the visible property of the Comment class to display or hide the annotation, or remove the annotation using the Delete method. In addition, you may find that the text method is useful: This method allows you to add text to the annotation, you can add it to the existing text, or overwrite the existing text.

After adding a comment, you may want to display annotations in the worksheet. The sample project contains a process showorHideComments, which displays or hides all the annotations in the active worksheet:

'Visual Basic

Private Sub ShoworHideComments (Byval Show as Boolean)

'Show or Hide All the Comments:

DIM WS as Excel.Worksheet = _

Directcast (thisapplication.sheets ("Worksheet Class"), _

Excel.Worksheet)

DIM I as integer

For i = 1 to ws.comments.count

Ws.comments (i) .visible = show

End Sub

// C #

Private void showorhidecomments (Bool Show)

{

// show or hide all the comments:

Excel.Worksheet WS =

(Excel.Worksheet) THISAPPLICATION.SHEETS ["Worksheet Class"]; for (int i = 1; i <= ws.comments.count; i )

{

Ws.comments [i] .visible = show;

}

Note Similar to many of the auxiliary set classes in Excel, the Comments collection does not provide a default enumerator. That is, you will not use a for Each loop to access all elements of this collection. For a collection of Comment collection, you must use an index loop to loop to this collection.

outline

EXCEL is packet unobstructed by using an outline function. You can also use the same function in your code. For example, given a set of rows as shown in Figure 17, you can add an outline function (added in the diagram shown) so you can fold these rows (as shown in Figure 18), folded group As shown in Figure 19.

Figure 17. Create these groups

Figure 18. Folding group

Figure 19. Completely folded group

The Worksheet class provides an Outline property that it is an Outline object. The Outline class does not provide too many members, the following list describes the members you might use:

• AutomaticStyles (Boolean) indicates whether the Outline is automatically styled to the Excel. • SummaryColumn (XlsumMaryColumn) Gets or sets the location of the summary column. XlsummaryColumn enumeration has two possible values: Xlsummaryonleft and XlsummaryonRight. • SummaryRow (xlsummaryrow) Gets or sets the location of the summary line. XlsummaryRow enumerates two possible values: XlsumMaryabove and XlsumMaryBelow. • Showlevels allows you to fold the bogroup or extend it to the row and / or column of the you want. You can send two parameters to this method, as shown in the following code: 'Visual Basic

DIM WS as Excel.Worksheet = _

Directcast (thisapplication.activeesheet, excel.worksheet)

'Specify Rowlevels and / or Columnlevels Parameters:

Ws.outline.showlevels (Rowlevels: = 3)

// C #

Excel.Worksheet WS =

(Excel.Worksheet) thisApplication.activesheet;

// Specify Rowlevels and / or Columnlevels Parameters:

Ws.outline.showlevels (3, Type.Missing);

The sample worksheet contains naming ranges corresponding to data from 2001 (DATA2001) and 2002 (DATA2001) and the entire rowset (allData). These named ranges cover the entire range of the worksheet; if you want to group, you must use the range of all rows. For the 2003 data, there is no one and its named range so that sample code demonstrates how all rows are used as a range.

The creation group is very simple: you can invoke a range of Group for one or more complete lines to create a group. (You can specify four optional packet parameters, including: Packet start and termination values, packets, and a boolean value to indicate packet cycles. This option is not used, because you rarely Use these parameters.) Call the UNGROUP method to cancel the packet. For example, click the WorkwithGroups link on the sample worksheet to run the following code: 'Visual Basic

Private sub WorkwithGroups ()

DIM WS as Excel.Worksheet = _

Directcast (thisapplication.activeesheet, excel.worksheet)

'Set worksheet-level features for the outline.

'In this Case, Summary Rows Are Below

'the data rows (So Excel Knows Where to Put

'the summary rows, and we don't want Excel

'TO FORMAT The Summary Rows - That's Already Been Done.

Ws.outline.summaryrow = Excel.xlsummaryrow.xlsummarybelow

ws.outline.automaticStyles = false

'Group The Two Named Ranges. Each of There

'Ranges Extends Across Entire Rows.

THISAPPLICATION.RANGE ("DATA2001"). Group ()

THISAPPLICATION.RANGE ("DATA2002"). Group ()

Thisapplication.range ("alldata"). Group ()

'The Range of Rows from 24 to 27 Doesn't Have

'a named Range, So you can work with this

'Range Directly.

DIM RNG AS Excel.range = _

Directcast (WS.ROWS ("24:27"), Excel.Range

RNG.Group ()

'Collapse to the second group level.

Ws.outline.showlevels (Rowlevels: = 2)

End Sub

// c #

Private void WorkwithGroups ()

{

Excel.Worksheet WS =

(Excel.Worksheet) thisApplication.activesheet;

// set Worksheet-level features for the outline.

// in this case, Summary Rows Are Below

// The data rows (so excline where to put

// the summary rows, and we don't want Excel

// TO Format The Summary Rows - That's Already Been Done.

Ws.outline.summaryrow = Excel.xlsummaryrow.xlsummarybelow;

Ws.outline.automaticStyles = false; // group the two named Ranges. Each of these

// Ranges Extends across Entire Rows.

ThisApplication.get_Range ("DATA2001", Type.Missing).

Group (Type.Missing, Type.missing, Type.Missing, Type.missing);

ThisApplication.get_Range ("DATA2002", Type.Missing).

Group (Type.Missing, Type.missing, Type.Missing, Type.missing);

ThisApplication.get_Range ("alldata", type.missing).

Group (Type.Missing, Type.missing, Type.Missing, Type.missing);

// the Range of Rows from 24 to 27 Doesn't Have

// a named Range, So you can work with what That

// Range Directly.

Excel.Range RNG = (Excel.Range) WS.ROWS ["24:27", Type.Missing];

RNG.Group (Type.Missing, Type.Missing, Type.missing,

TYPE.MISSING);

// collapse to the second group level.

Ws.outline.showlevels (2, Type.Missing);

}

In order to group three named ranges, the code is just a simple call method for calling the corresponding range:

'Visual Basic

THISAPPLICATION.RANGE ("DATA2001"). Group ()

// c #

ThisApplication.get_Range ("DATA2001", Type.Missing).

Group (Type.Missing, Type.missing, Type.Missing, Type.missing);

In order to group unnamed ranges, the code uses the ROWS attribute of the worksheet, the scope of the line. This attribute returns a range corresponding to the row to use:

'Visual Basic

DIM RNG AS Excel.range = _

Directcast (WS.ROWS ("24:27"), Excel.Range

RNG.Group ()

// c #

Excel.Range RNG = (Excel.Range) WS.ROWS ["24:27", Type.Missing];

RNG.Group (Type.Missing, Type.missing, Type.Missing, Type.Missing);

Click the Clear Groups link in the sample worksheet to run similar code, which can clear the group:

'Visual Basic

Private sub cleargroups ()

DIM WS as Excel.Worksheet = _

Directcast (thisapplication.activeesheet, excel.worksheet)

'Specify Rowlevels and / or Columnlevels Parameters:

Ws.outline.showlevels (Rowlevels: = 3)

DIM RNG As Excel.Range = _Directcast (ws.rows ("24:27"), Excel.Range

RNG.ungroup ()

THISAPPLICATION.RANGE ("DATA2001"). UNGROUP ()

THISAPPLICATION.RANGE ("DATA2002"). UNGROUP ()

ThisApplication.range ("alldata"). UNGROUP ()

End Sub

// C #

Private void cleargroups ()

{

Excel.Worksheet WS =

(Excel.Worksheet) thisworkbook.sheets ["Worksheet Class"];

// Specify Rowlevels and / or Columnlevels Parameters:

Ws.outline.showlevels (3, Type.Missing);

Excel.Range RNG = (Excel.Range) WS.ROWS ["24:27", Type.Missing];

RNG.UNGROUP ();

THISAPPLICATION.GET_RANGE ("DATA2001", TYPE.MISSING .ungroup ();

Thisapplication.get_range ("DATA2002", TYPE.MISSING .ungroup ();

Thisapplication.get_range ("alldata", type.missing) .ungroup ();

}

By using these methods, you can create and delete the group and control the level displayed in the worksheet.

Range object

The Range object is the most frequently used object in the Excel application; you need to represent it as a Range object before you can operate any area in Excel, and then use the method and properties of the RANGE object. The Range class is very important. So far, one RANGE object is used in each example in this article. Basically, a Range object represents a cell, one line, a column, contains one or more unit blocks (which can be a continuous cell, or a non-contained cell) selected cell, or even multiple A set of cells on the worksheet.

This part focuses on the three major issues because it is impossible to discuss all members of Range.

• Reference ranges in your code. • Operating range in your code. • Use the RANGE object to achieve a specific purpose.

In other words, since the Range object has many different applications in many different occasions, all this section will answer "How I ..." such as "I don't provide a list of all members.

Management selection area

Although it is very attractive to use the current selection area as a range of properties and behavior, you should avoid this. Just like any other shared resource, the selection area in Excel represents the user's choice. If you modify this selection area in your code, it will cause users to lose control on the current selection area. The experience is that the SELECT method of the object can only be called only when you want to change the user's selection area. As a developer, you can't just call the SELECT method for your convenience. If your purpose is just set up a range of properties, there will always be other alternatives. In short, avoiding using the Select method not only allows your code to run faster, you can also make your users from interference.

The following code clears the user's current cell adjacent area, and the code to write such a code is very simple:

'Visual Basice.Application.Activecell.currentregion.select

Directcast (thisapplication.selection, excel.range) .clearContents

// C #

ThisApplication.Activecell.currentregion.select ();

(Excel.Range) thisapplication.selection .clearContents ();

This will cancel the user's choice. If only one cell is originally selected, the entire large block near the cell will be selected when running the previous code snippet. In fact, unless your purpose is to select all cells, the code as shown below is a better solution:

'Visual Basic

ThisApplication.Activecell.currentregion.clearContents

// C #

ThisApplication.Activecell.currentregion.clearContents ();

Why will anyone think of using the first code snippet? The reason why this code is used because Excel developers attempt to find out how to use the various objects in Excel and the beginning of their methods will tend to use the Excel macro recorder. This kind of good idea, but the code written by the macro recorder is very bad. Typically, the macro recorder uses the selection area and modifies the selection area when recording any task.

Tip When using one or a set of cells, use as much as possible to describe the range of the cell you want to use, not the modified selection area. If your purpose is to change the user's selection area, use the Range.select method.

Quote Range in the code

The Range class is very flexible, you will find too much choice for programming when programming. Sometimes the Range object is a single object, and sometimes it represents a collection of objects. It has members of Item and Count, although the Range object refers to a single object, which makes sometimes how to accurately use the RANGE object to become a very difficult thing.

Tips The following examples get a range of Address properties. This attribute returns a string containing range coordinates, which represents one of the following formats, including: "$ a $ 1" (cell in position A1), "$ 1" (in the first line of worksheet) and " $ A $ 1: $ C $ 5 (ranges from all cells within the rectangle between A1 and C5). "$" Represents absolute coordinates (rather than relative coordinates). Using the Address property is the easiest way to find the exact location of the range you want to search. For more information on the various methods of the reference range, please refer to Excel online help.

With its simplest way, you can write the code shown in the following procedure to enable the RANGE object to reference a single cell or a set of cells. All examples are assumed to have the following settings:

'Visual Basic

DIM WS as Excel.Worksheet = _

Directcast (thisworkbook.worksheets (1), Excel.Worksheet)

DIM RNG, RNG1, RNG2 AS Excel.Range

// C #

Excel.Worksheet WS = (Excel.Worksheet) thisworkbook.worksheets [1];

Excel.Range RNG, RNG1, RNG2;

You can use any of the following methods to reference a specific range (there are several other ways to get the RANGE object reference):

• Reference ActiveCell property of the Application object: 'Visual Basicrng = thisApplication.activeCell

// C #

RNG = thisApplication.activeCell;

• Specify an area using the Range property of the object. Since the C # does not support parameterized non-index properties, you must call GET_RANGE methods, this method requires two parameters: 'Visual Basic

RNG = WS.RANGE ("a1")

RNG = ws.range ("A1: B12")

// C #

RNG = ws.get_range ("a1", type.missing;

RNG = ws.get_range ("a1: b12", type.missing);

• Specify a single line and column value using a worksheet: 'Visual Basic

'The Cells Collection Returns An Object -

'Convert It to a Range Object Explicitly:

RNG = Directcast (ws.cells (1, 1), Excel.range)

// C #

RNG = (Excel.Range) WS.cells [1, 1];

• Specify a range of "corners"; you can also directly reference the range of Cells, Rows, or Columns properties; each case, the property returns a range: 'Visual Basic

RNG = WS.RANGE ("A1", "C5")

RNG = WS.RANGE ("A1", "C5"). Cells

RNG = WS.RANGE ("a1", "c5"). ROWS

RNG = WS.RANGE ("A1", "C5"). Columns

// C #

RNG = ws.get_range ("a1", "c5");

RNG = ws.get_range ("a1", "c5"). Cells;

RNG = ws.get_range ("a1", "c5"). ROWS;

RNG = ws.get_range ("a1", "c5"). columns;

• Reference a naming range. You can see this technique in this article. Note: Because the C # get_range method requires two parameters, the range name only needs one of the parameters, so you must specify Type.Missing for the second parameter: 'Visual Basic

RNG = thisapplication.range ("somerangename")

// C #

RNG = thisApplication.range ("somerangename", type.missing;

• Reference Specific lines or specific columns or rows and columns; Note: Both the ROWS and Column attributes return an object if you set the option strict to ON, you need type conversion: 'Visual Basic

RNG = Directcast (ws.rows (1), Excel.Range) RNG = Directcast (ws.Rows ("1: 3"), Excel.Range

RNG = Directcast (ws.column (3), Excel.Range

// C #

RNG = (Excel.Range) WS.ROWS [1, Type.Missing];

RNG = (Excel.Range) WS.ROWS ["1: 3", Type.Missing];

RNG = (Excel.Range) WS.COLUMNS [3, TYPE.MISSING];

WARNING IntelliSense feature of the columns property is easy to misunderstand - it indicates that you must specify a row value and then specify the column value. In practical applications, the value of the columns attribute is inverted. For Rows and Columns properties, the second parameter is not used. • Returns the range corresponding to the selected cell using the application object; in the case shown in FIG. 20, the following code segment returns "$ c $ 3" (using "$" to indicate absolute coordinate): ' Visual Basic

Debug.writeLine (_

Directcast (thisapplication.selection, excel.range) .address

// C #

System.Diagnostics.debug.writeline

(Excel.Range) thisapplication.selection.

GET_ADDRESS (Type.Missing, Type.Missing,

Excel.xlreferenceing, Type.Missing);

Tip The Address property is another parameterization property that C # cannot directly process. Call the GET_ADDRESS method to acquire the address corresponding to the RANGE object. All parameters of the Address property are optional, but get_address methods to get 5 parameters - you may only need to care about the third parameter, which allows you to specify the address format. • Create a range that contains other two mergers (specified two ranges in quotation marks, separated by commas): 'Visual Basic

RNG = thisapplication.range ("A1: D4, F2: G5")

'You can also use the application object's union

'Method to Retrieve The Intersection of TW Ranges:

RNG1 = THISAPPLICATION.RANGE ("A1: D4")

RNG2 = thisApplication.range ("F2: G5")

RNG = thisApplication.union (RNG1, RNG2)

// C #

RNG = thisapplication.get_range ("A1: D4, F2: G5", TYPE.MISSING;

// you can also use the application object's union

//Method to Retrieve The InterSection of Two Ranges, But this

// is Far More Effort in C #:

RNG1 = thisApplication.get_range ("a1", "d4");

RNG2 = thisApplication.get_range ("f2", "g5"); // Note That The Union Method Requires You To Supply Thirty

// Parameters:

RNG = THISAPPLICATION.Union (RNG1, RNG2,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.missing, Type.Missing, Type.Missing;

• Create a range that references other two range overlapping parts (specified in quotation marks, do not use separator): 'Visual Basic

RNG = thisApplication.range ("A1: D16 B2: F14")

'You can also use the application object's intertect

'Method to Retrieve The Intersection of TW Ranges:

RNG1 = thisApplication.range ("A1: D16")

RNG2 = THISAPPLICATION.RANGE ("B2: F14")

RNG = thisApplication.INTERSECT (RNG1, RNG2)

// c #

RNG = thisapplication.get_range ("A1: D16 B2: F14", TYPE.MISSING);

// you can also use the application object's intertect

// Method to Retrieve The INTERSECTION OF TWO RANGES. Note

// That the intersect method required required by in to pass 30 parameters:

RNG1 = trisapplication.get_range ("a1", "D16");

RNG2 = thisApplication.get_range ("B2", "F14");

RNG = THISPPLICATION.INTERSECT (RNG1, RNG2,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.missing, Type.Missing, Type.Missing;

• Use the range of Offset attribute to get a range relative to the initial range; the following example adds content in the first line, the first column: 'Visual Basic

RNG = Directcast (ws.cells (1, 1), Excel.range)

DIM I as integer

FOR i = 1 to 5

RNG.Offset (i, 0) .value = i.toString

// c #

RNG = (Excel.Range) WS.cells [1, 1];

For (INT i = 1; i <= 5; i )

{

RNG.GET_OFFSET (i, 0) .value2 = i.toString ();

}

Tip The Range.Offset property is a parameterized property, so the C # code cannot obtain this attribute value directly. Alternatively, the C # developer must call the GET_OFFSET method. • Use the scope of the CurrentRegion property to obtain a range representing the current area, which is limited by the nearest air line and column; for example, in Figure 20, the following expressions are set to bold: 'Visual Basic

THISAPPLICATION.RANGE ("C3"). CurrentRegion.font.bold = true

// c #

ThisApplication.get_Range ("C3", Type.Missing).

CurrentRegion.font.bold = true;

Figure 20. Request the CURRENTREGON attribute of the C3 cell Returns a1: E5 range.

• The range collection is obtained using the range of AREAs attribute, where each range corresponds to a region of the range content. For example, the following code snippet shows the address of two regions in the range name Test, "$ 6: $ E $ 5" and "$ 7: $ G $ 11" (using "$" represents absolute coordinate), such as Figure 21 shows: 'Visual Basic

RNG = thisapplication.range ("test")

DIM I as integer

For i = 1 to rng.areas.count

Debug.writeLine (RNG.Areas (i) .address

// c #

RNG = thisApplication.get_range ("test", type.missing;

For (int i = 1; i <= rng.areas.count; i )

{

System.Diagnostics.debug.writeline

RNG.Areas [i] .get_address (Type.Missing, Type.Missing,

Excel.xlreferenceing, Type.Missing);

}

Figure 21. The range included in the range can be discontinuous, and these areas can be obtained separately.

• Use the END attribute and an XLup, XLtoright, XLTOLEFT, XLDOWN, to achieve a range representing the end cells of the area, as you press the key described in the enumeration value; use as shown in Figure 22 The selected cell, the following code segment gets four defined scope (as shown in the code): 'Visual BasicDim RNGLEFT, RNGRIGHT, RNGUP, RNGDOWN AS Excel.Range

RNG = DirectCast (thisapplication.selection, excel.range)

'E3

RNGRight = RNG.End (Excel.xLDirection.xlt (Excel.xLDirection.xLtoright)

'A3

RNGLEFT = RNG.End (Excel.xLDirection.xlt (Excel.xLDirection.xlt)

'C1

RNGUP = RNG.End (Excel.xLDirection.xlup)

'C5

RNGDOWN = RNG.End (Excel.xLDirection.xLDown)

// c #

Excel.Range RNGLEFT, RNGRIGHT, RNGUP, RNGDOWN

RNG = (Excel.Range) THISPPLICATION.SELECTION;

// Note That The Range.end Property Is Parameterized, SO

// c # developers Cannot Retrieve It. You Must Call The

// Get_END METHOD, INSTEAD:

// e3

RNGRIGHT = RNG.GET_END (Excel.xLDirection.xLtoright);

// a3

RNGLEFT = RNG.GET_END (Excel.xLDirection.xlt (Excel.xLDirection.xLTOLEFT);

// c1

RNGUP = RNG.GET_END (Excel.xLDirection.xLUP);

// c5

RNGDOWN = RNG.GET_DOWN (Excel.xLDirection.xLDown);

Figure 22. Returns the range corresponding to a range using the END attribute.

• Use the Entirerow or EntireColumn property to reference the included rows or columns. For example, the following code snippet is set to a bold: 'Visual Basic using the example shown in Figure 21.

RNG = thisapplication.range ("test")

RNG.areas (2) .ntreamow.font.bold = true

// c #

RNG = thisApplication.get_range ("test", type.missing;

RNG.Areas [2] .ntreamow.font.bold = true;

Use technology

Developers often require such capabilities: changing fonts containing the entire line of the selected cell, making the text into bold. There is no built-in function in Excel, but it is not very difficult to add it. The worksheet for the Range class in the sample workbook contains a range of special processes: When you select an entry, its row will become bold. Figure 23 shows this line.

Figure 23. Select an entry to make a whole line into bold.

The sample workbook contains the following procedure to process formatted:

'Visual Basic

Private sub boldcurrentrow (byval WS as Excel.Worksheet)

'Keep Track of The Previously Bolded Row.

Static Introw as INTEGER

'Work with the current active cell.

DIM RNGCELL AS Excel.Range = _

Thisapplication.activececell

'Bold the current row.

RNGCELL.ENTIREROW.FONT.BOLD = TRUE

'Make Sure INTROW ISN''' (Meaning That

'this is your first pass through here.

IF Introw <> 0 THEN

'If you're on a different

'Row Than the last time through here,

'make the old row not bold.

If RNGCELL.ROW <> Introw Then

DIM RNG AS Excel.range = _

Directcast (WS.ROWS (INTROW), Excel.Range

RNG.Entirerow.Font.Bold = false

END IF

'Store Away The New Row Number

'for next time.

InTrow = RNGCELL.ROW

End Sub

// c #

Private int lastboldedrow = 0;

Private void Boldcurrentrow (Excel.Worksheet WS)

{

// Keep Track of The Previously Bolded Row.

// Work with the current active cell.

Excel.Range RNGCELL = thisapplication.activecell;

// bold the current row.

RNGCELL.ENTIREROW.FONT.BOLD = TRUE;

// Make Sure Introw Isn't 0 (Meaning That

// this is your first pass through here).

IF (LastBoldedRow! = 0)

{

// if you're on a different at a DIFFERENT

// Row Than the last time through here,

// make the old row not bold.

IF (RNGCELL.ROW! = lastboldedrow)

{

Excel.Range RNG =

(Excel.Range) WS.ROWS [LastBoldedRow, Type.Missing];

RNG.FONT.BOLD = FALSE;

}

// store away the New Row Number

// for next time.

LastBoldedrow = RNGCELL.ROW;

}

This example uses the following steps to turn the current row into bold, and turn the row that turns back to the original in the previous time:

• Declare a variable (in Visual Basic, type static) is used to track the previously selected line: 'Visual Basic

Static Introw as INTEGER

// c #

Private int lastboldedrow = 0;

• Use the Application.Activecell property to get a reference to the current cell: 'Visual BasicPrivate Int LastBoldedRow = 0;

Dim RNGCELL AS Excel.Range = thisApplication.ActiveCell

// C #

Excel.Range RNGCELL = thisapplication.activecell;

• Use the Entirerow property of the active cell to make the current row into bold: 'Visual Basic

RNGCELL.ENTIREROW.FONT.BOLD = TRUE

// C #

RNGCELL.ENTIREROW.FONT.BOLD = TRUE;

• Make sure that the current value of INTROW is not 0, if it is 0, then this is the first time to run this code: 'Visual Basic

IF Introw <> 0 THEN

'Code Removed Here ...

END IF

// C #

IF (LastBoldedRow! = 0)

{

// Code removed here ...

}

• Make sure the current row and the front lines are different. If the current row is different from the front line, the code only needs to modify the state of the line. The ROW attribute returns an integer value to indicate the row corresponding to the range: 'Visual Basic

If RNGCELL.ROW <> Introw Then

'Code Removed Here ...

END IF

// C #

IF (RNGCELL.ROW! = lastboldedrow)

{

// Code removed here ...

}

• Retrieve a reference to the range of the previously selected row, and set the line to be bold: 'Visual Basic

DIM RNG AS Excel.range = _

Directcast (WS.ROWS (INTROW), Excel.Range

RNG.Font.Bold = false

// C #

Excel.Range RNG =

(Excel.Range) WS.ROWS [LastBoldedRow, Type.Missing];

RNG.FONT.BOLD = FALSE;

The sample workbook is called from its SheetSelectionChange event handler to call the boldcurrentrow process. During this process, the code verifies whether the new selection line is located in the correct range (using the INTERSECT method of the Application object), if so, call the boldcurrentrow process:

'Visual Basic

Private sub thisworkbook_sheetselectionchange (_

BYVAL SH As Object, Byval Target as Excel.Range_

Handles thisworkbook.sheetselectionchange

IF not thisapplication.intersect (Target, _

ThisApplication.range ("boldselectedrow")) is nothing then

'The selection is forning the range where you're making

'the selected row bold.

Boldcurrentrow (Directcast (SH, Excel.Worksheet))

END IF

End Sub

// C #

Protected void thisworkbook_sheetselectionchange (System.Object SH, Excel.Range Target)

{

// Don n't forget this the INTERSECT METHOD Requires

// thirty parameters.

IF (THISAPPLICATION.INTERSECT (Target,

ThisApplication.get_Range ("BoldSelectedRow", Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing

! = NULL)

{

// the selection is foring the range where you're making

// The successd rotted row bold.

Boldcurrentrow (Excel.Worksheet) SH);

}

Range

Once you get a reference to a range, can you use it? The use you can list is endless, as long as you can imagine it. This section focuses on techniques that use the RANGE object and provide a simple example for each technology. All examples in this section can be found in the Range Class worksheet of the sample workbook.

Automatic fill range

The AutoFill method of the Range class allows you to automatically populate a range. In most cases, the AutoFill method is used to store the incrementally or decreasing value into a range. You can specify the behavior of this method by providing an optional constant. This constant from XlAutoFillType enumeration (xlFillDays, xlFillFormats, xlFillSeries, xlFillWeekdays, xlGrowthTrend, xlFillCopy, xlFillDefault, xlFillMonths, xlFillValues, xlFillYears or xlLinearTrend). If you do not specify a fill type, Excel assumes that you use the default fill type (XLFILDEFAULT) and fill it thinks the appropriate specified range.

The example worksheet (as shown in Figure 24) contains four regions that will be automatically filled. Column B contains five working days; column C includes five months; column D contains a date increasing year by year; column E contains a series of numbers, each line increases. Figure 25 shows the same area after running an example code.

Figure 24. Calling the four sample scope prior to the AutoFill method.

Figure 25. After the automatic fill range.

Click the AutoFill link to run the following process:

'Visual Basic

Private su autofill ()

DIM RNG As Excel.Range = thisApplication.range ("b1")

RNG.Autofill (thisapplication.range ("b1: b5"), _excel.xlautofilltype.xlfillDays)

RNG = thisapplication.range ("c1")

RNG.Autofill (THISAPPLICATION.RANGE ("C1: C5"), _

Excel.xlautofillType.xlfillmonths)

RNG = THISAPPLICATION.RANGE ("D1")

RNG.Autofill (THISAPPLICATION.RANGE ("D1: D5"), _

Excel.xlautofillType.xlfillyears)

RNG = thisapplication.range ("E1: E2")

RNG.Autofill (THISAPPLICATION.RANGE ("E1: E5"), _

Excel.xlautofillType.xlfillSeries)

End Sub

// c #

Private void autofill ()

{

Excel.Range rng = thisapplication.get_range ("b1", type.missing;

RNG.Autofill (thisapplication.get_range ("b1: b5", type.missing,

Excel.xlautofillType.xlFillDays;

RNG = thisapplication.get_range ("c1", type.missing;

RNG.Autofill (thisApplication.get_Range ("C1: C5", Type.Missing,

Excel.xlautofilltype.xlfillmonths;

RNG = thisapplication.get_range ("D1", Type.Missing;

RNG.Autofill (thisApplication.get_Range ("D1: D5", Type.Missing,

Excel.xlautofillType.xlfillyEars;

RNG = thisapplication.get_range ("E1: E2", TYPE.MISSING);

RNG.Autofill (THISAPPLICATION.GET_RANGE ("E1: E5", TYPE.MISSING,

Excel.xlautofillType.xLfillSeries;

}

You must specify two scope each case:

• Call the range of the AutoFill method, which specifies the "starting point" fills. • The range to be filled, it is passed to the AutoFill method as a parameter; the purpose range must contain the source range.

The second parameter of the AutoFill method (XLAUTOFILLTYPE enumeration value) is optional. Typically, you need to provide this value to get the behavior you want. For example, try changing the following code:

'Visual Basic

RNG.Autofill (THISAPPLICATION.RANGE ("D1: D5"), _

Excel.xlautofillType.xlfillyears)

// c #

RNG.Autofill (thisApplication.get_Range ("D1: D5", Type.Missing,

Excel.xlautofillType.xlfillyEars;

It looks like this:

'Visual Basicrng.autofill (THISAPPLICATION.RANGE ("D1: D5"))

// c #

RNG.Autofill (thisApplication.get_Range ("D1: D5", Type.Missing,

Excel.xlautofillType.xlfillDefault);

After the code is modified, the date will be incremented by day, not increment in the year.

Find in the range

The Find method of the Range class allows you to search for text within the range. This flexible approach mimes the behavior of the lookup and replacement dialog in Excel, as shown in Figure 26 - In fact, this method is directly interacting with this dialog. That is, the Range.Find method or uses the parameters you pass to it to determine its search behavior, or if you do not pass the parameters, it uses its value in the lookup and replacement dialog box. Table 4 lists the parameters of the Range.Find method, except for the first parameters, all other parameters are optional.

Figure 26. Selection on this dialog affects the behavior of the Find method.

WARNING Because almost all parameters of Range.Find are optional, because users may change the value by the Find and Replace dialog, you must make sure that all values are transmitted to the Find method, unless you want to Choose also take into account. Of course, C # developers don't need to worry about this problem, because they must provide all parameters when they call each method.

Table 4. Parameter Parameter Type of Range.Find Method Description What (Required) Object To find data; it can be a string or any Excel data type. After Scope You want to start searching from this range (in search, excluding this cell); if you do not specify a cell, start search from the upper left corner of the range. Lookin XLFindlookin (XlValue, Xlcomments, XLFormula) To search the information type; you can't use the OR operator to query. Lookat Xllookat (XLWhole, XLPART) OK Search match all cells or partial cells. SearchORDER XLSEARCHORDER (XlbyRows, XlbyColumns) Decides to search the order; XLByRows (default) will search horizontally, then searches; XLBYCOLUMNS searches vertical and searches. SearchDirection XLSearchDirection (xlnext, xlprevious) determines the direction of the search; the default value is XLNext. The MatchCase Boolean value determines if the search is case sensitive. The MatchByte Boolean value determines if the double-byte character only matches the double-byte match (true) or can also match the single-byte character (false); only when you have installed double-byte support.

The following example comes from the sample workbook, which searches for a range (name "winds") and changing the font of a cell containing the word "apples" (Figure 27 shows the search results). This process also uses the FindNext method, which uses the previous search settings to repeat the search. (Range.FindPRevious method and the Range.FindNext method are almost the same, but this example is useless.) You want to specify which cell after search, and the remaining is processed by the FindNext method.

Figure 27. Search results for cells containing words "apples"

Prompt the FindNext (and FindPrevious) method Once searching the range is searched, it will be returned to the start position of the search range. To make sure the search will not become an infinite loop, never, you need to set it in the code. The example process demonstrates a method of processing this situation. If you want to completely avoid this unlimited loop, or you want to make a more complex search than the Find / FindNext / FindPrevious method, you can also use a for Each loop to loop all cells within a range. Click the Find link in the Range Class worksheet in the sample workbook to run the following process:

'Visual Basic

Private sub demofind ()

DIM RNG As Excel.Range = thisApplication.range ("fruits")

Dim RNGFOUND As Excel.Range

'Keep Track of The First Range you find.

Dim RNGFoundFirst as Excel.Range

'You Should Specify All these Parameters

'Every Time You Call this Method, Since They

'Can Be Overriden in the user interface.

Rngfound = rng.find (_ _ _

"apples", _

Excel.xlfindlookin.xlvalues, Excel.xllookat.xlpart, _

Excel.xlsearchOrder.xlbyrows, Excel.xlSearchDirection.xlnext,

False)

While Not RNGFOUND IS NOTHING

IF rngfoundfirst is nothing then

RNGFOUNDFIRST = RNGFound

Elseif Rngfound.address = RNGFOUNDFIRST.ADDRESS THEN

Exit while

END IF

With rngfound.font

.Color = colorranslator.toole (color.red)

.Bold = true

End with

Rngfound = rng.findNext (RNGFound)

End while

End Sub

// c #

Private void demofind ()

{

Excel.Range RNG = thisApplication.

Get_Range ("Fruits", Type.Missing;

Excel.range RNGFOUND;

// Keep TRACK OF THE FIRST RANGE you find.

Excel.Range rngfoundfirst = null;

// you shop specify all these parameters

// every time you call this method, Since they

// can be overriden in the user interface.

RNGFOUND = RNG.Find ("apples", type.missing,

Excel.xlfindlookin.xlvalues, Excel.xllookat.xlpart,

Excel.xlsearchOrder.xlbyrows, Excel.xlSearchDirection.xlnext,

False, Type.Missing, Type.Missing;

While (RNGFound! = NULL)

{

IF (RNGFOUNDFIRST == NULL)

{

RNGFOUNDFIRST = RNGFOUND;

}

Else IF (GetDress (RNGFound) == getaddress (RNGFoundFirst))

{

Break;

}

Rngfound.font.color = colorTranslator.Toole (Color.Red);

RNGFOUND.FONT.BOLD = TRUE;

Rngfound = rng.findnext (RNGFOUND);

}

This code takes these steps to achieve its purpose:

• Declare an Excel.Range variable to track the entire range, the first scope and the current discovered range: 'Visual Basic

DIM RNG As Excel.Range = thisApplication.range ("fruits")

Dim RNGFOUND As Excel.Range

Dim RNGFoundFirst as Excel.Range

// C #

Excel.Range RNG = thisApplication.

Get_Range ("Fruits", Type.Missing;

Excel.range RNGFOUND;

Excel.Range rngfoundfirst = null;

• Search the first match value, specify all parameters (except for cells to search later) - By default, search start from the upper left corner of the range - then search "Apples" in units, matching parts Value, search by line, and do not vase case: 'Visual Basic

Rngfound = rng.find (_ _ _

"apples", _

Excel.xlfindlookin.xlvalues, Excel.xllookat.xlpart, _

Excel.xlsearchOrder.xlbyrows, Excel.XLSearchDirection.xlnext, _

False)

// C #

RNGFOUND = RNG.Find ("apples", type.missing,

Excel.xlfindlookin.xlvalues, Excel.xllookat.xlpart,

Excel.xlsearchOrder.xlbyrows, Excel.xlSearchDirection.xlnext,

False, Type.missing, Type.Missing;

• Just find that the matching value search will continue: 'Visual Basic

While Not RNGFOUND IS NOTHING

'Code Removed Here ...

End while

// C #

While (RNGFound! = NULL)

{

// Code removed here ...

}

• Compare the first discovered range (RNGFOUNDFIRST) and Nothing, if the code only finds the first match value, RNGFoundFirst will be Nothing, and only in this case it will only be Nothing. In this case, the code will be saved by the range of the found; otherwise, if the range address is found and the first found range address, the code exits the loop. 'Visual Basic

IF rngfoundfirst is nothing then

Rngfoundfirst = rngfoundelseif rngfound.address = RNGFOUNDFIRST.ADDRESS THEN

Exit while

END IF

// c #

IF (RNGFOUNDFIRST == NULL)

{

RNGFOUNDFIRST = RNGFOUND;

}

Else IF (GetDress (RNGFound) == getaddress (RNGFoundFirst))

{

Break;

}

• Set the exterior of the range found: 'Visual Basic

With rngfound.font

.Color = colorranslator.toole (color.red)

.Bold = true

End with

// c #

Rngfound.font.color = colorTranslator.Toole (Color.Red);

RNGFOUND.FONT.BOLD = TRUE;

• Perform another search: 'Visual Basic

Rngfound = rng.findNext (RNGFound)

// c #

Rngfound = rng.findnext (RNGFOUND);

Click the RESET Find link of the sample worksheet to run this simple process and start the range when running:

'Visual Basic

Private sub resetfind ()

DIM RNG As Excel.Range = thisApplication.range ("fruits")

With RNG.Font

.Color = colorranslator.toole (color.black)

.Bold = false

End with

End Sub

// c #

Private void resetFind ()

{

Excel.Range RNG = thisApplication.

Get_Range ("Fruits", Type.Missing;

RNG.Font.Color = ColorTranslator.toole (color.black);

RNG.FONT.BOLD = FALSE;

}

Tip If you want to find and replace within a range, use the Range.Replace method. This method is similar to a Find method, but you can give you a value to be replaced. The REPLACE method returns a Boolean value indicating whether the replacement is performed. Even if only one value is replaced, it will return true.

Sort data in the range

Just sorted by the Excel user interface, you can use the Range.Sort method to sort the data using the RANGE.SORT method. You point out that the range to be sorted, you want to sort up to three rows or three columns (optional), and other optional parameters, the remaining is handled by Excel. Table 5 lists all parameters of the sort method. (Visual Basic .Net developers are likely to use only part of it, and C # developers must give values for each parameter.)

Table 5. Parameter Parameter Type of the SORT method Description Key1 Object (string or Range) The first sort field, can be a range name (String), or a RANGE object, determines the value to be sorted. Order1 Xlsortorder (XLascending, Xldescending) determines the value of the sorted order for the value specified in KEY1. Key2 Object (String or Range) The second sort field, cannot be used when sorting the perspective. Type Object When you sort a perspective, specify which elements are sorted; there is no impact on a normal range. ORDER2 XLSortorder determines the order order for the value specified in Key2. Key3 Object (String or Range) third sort field, cannot be used in the perspective. Order3 XlsortORDER determines the order sequence for the value specified in Key3. Header XlyeSnoguess (Xlguess, XLNO, XLYES) Specifies whether the first line contains header information, the default value is XLNO; if you want Excel to speculate, it is specified as Xlguess. ORDERCUSTOM INTEGER specifies a 1-based index for custom sorting sequence list; if this parameter is not specified, use the default sort order. Figure 28 shows a technique for creating a custom sort order. For this example, this parameter is specified as 6 will be sorted based on "FRUITS" custom order. MatchCase Boolean is set to true that the sorting of case-sensitive is set to FALSE, which is not case-sensitive; cannot be used in the perspective. Orientation Xlsortorientation (XlsortRows, XlsortColumns) Sorting Direction. SortMethod XlsortMethod (xlstroke, xlpinyin) Specifies a sorting method; you cannot apply to all languages (the current value is only sorted by Chinese characters without applicable to other languages). DataOption1 XlsortDataOption (XlsortTextAnsNumBers, XlsortNormal) Specifies how to sort the range specified in Key1; cannot be used for perspective sorting. DataOption2 XlsortDataOption Specifies how to sort the range specified in Key2; cannot be used in perspective sorting. DataOption3 XlsortDataOption Specifies how to sort the range specified in KEY3; cannot be used for perspective sorting. Tip When toned like this, Visual Basic .NET developers have obvious advantages over C # developers. Because you are unlikely to use all parameters, Visual Basic .NET developers can use named parameters, only specify the parameters they need. In order to accept the default behavior, C # developers must pass all non-use parameters.

Figure 28. You can create your own custom sort list and then reference these specific sort order in your code.

Click the Sort link in the Range Class sample worksheet to run the following procedure, which is first sorted according to the data in the first column, and then sort according to the data in the second column:

'Visual Basic

Private sub Demosort ()

DIM RNG As Excel.Range = thisapplication.range ("fruits") RNG.SORT (_

Key1: = rng.columns (1), ORDER1: = Excel.xlsortorder.xlascending, _

Key2: = rng.columns (2), ORDER2: = Excel.xlsortorder.xlascending, _

Orientation: = Excel.xlsortorientation.xlsortcolumns, _

Header: = Excel.xlySnoguess.xlno)

End Sub

// C #

Private void Demosort ()

{

Excel.Range RNG = thisApplication.

Get_Range ("Fruits", Type.Missing;

RNG.Sort (RNG.Columns [1, Type.Missing],

Excel.xlsortorder.xlascending,

RNG.COLUMNS [2, Type.Missing], Type.Missing,

Excel.xlsortorder.xlascending,

Type.Missing, Excel.xlsortorder.xlascending,

Excel.xlyesnoguess.xlno, Type.Missing, Type.Missing,

Excel.xlsortorientation.xlsortcolumns,

Excel.xlsortmethod.xlpinyin,

Excel.xlsortDataOption.xlsortNormal,

Excel.xlsortDataOption.xlsortNormal);

}

Click the RESET Sort link in the same worksheet to run the following procedure, which sorted the second column according to custom sorting methods, as shown in Figure 28:

'Visual Basic

Private sub repland ()

DIM RNG As Excel.Range = thisApplication.range ("fruits")

RNG.Sort (rng.columns (2), ORDERCUSTOM: = 6, _

Orientation: = Excel.xlsortorientation.xlsortcolumns, _

Header: = Excel.xlySnoguess.xlno)

End Sub

// C #

Private void recsort ()

{

Excel.Range RNG = thisApplication.

Get_Range ("Fruits", Type.Missing;

RNG.Sort (RNG.Columns [2, Type.Missing],

Excel.xlsortorder.xlascending,

Type.Missing, Type.Missing, Excel.xlsortorder.xlascending,

Type.Missing, Excel.xlsortorder.xlascending,

Excel.xlyesnoguess.xlno, 6, Type.missing,

Excel.xlsortorientation.xlsortcolumns,

Excel.xlsortmethod.xlpinyin,

Excel.xlsortDataOption.xlsortNormal,

Excel.xlsortDataOption.xlsortnormal;}

Under time

Although this article seems to be lengthy, it just involves the surface of a large amount of content provided by the Excel object model. This article describes the most important classes - Application, Workbook, Worksheet, and Range - But there is no introduction to other classes that may be useful to you. You may also need to study the second "layer" class provided by the Excel object model, for example, Pivottable and Camines. As long as you are willing to find the exact class you need, the integrity of the Excel object model makes you complete any of your required automation tasks. As long as you master this article, Object Browser and Excel VBA help, you should be able to compete for any task that can be imagined in Excel.

转载请注明原文地址:https://www.9cbs.com/read-100053.html

9cbs

New Post(0)