Understanding the Excel Object Model from a. Net developer's Perspective
Tip You may want to set the Application.DisplayAlerts
Yüklə 0,58 Mb.
|
Tip You may want to set the Application.DisplayAlerts property to False before calling the SaveAs method, because saving in some formats requires interaction. For example, when saving a worksheet to XML format, Excel reminds you that you won't be able to save the VBA project with the workbook. Setting the DisplayAlerts property to False suppresses this warning.
Warning Interactively canceling any of the methods that save or copy the workbook triggers a run-time error in your code. For example, if your procedure calls the SaveAs method but doesn't disable prompts from Excel, and your user clicks Cancel when prompted, Excel raises a run-time error back to your code. The Worksheet Class By the time you've gotten to this point in this document, you've been introduced to most of the concepts you need in order to work with an individual worksheet. Although the Worksheet class provides a large number of members, most of the properties, methods, and events are identical or similar to members provided by the Application and/or Workbook classes. This section focuses on important members and issues specific to the Worksheet class that you haven't already seen described elsewhere in this document. (You can find the examples for this section in the sample workbook on the Worksheet Object sheet.) There's No Sheet Class Although Excel provides a Sheets collection as a property of a Workbook object, you won't find a Sheet class in Excel. Instead, each member of the Sheets collection is either a Worksheet, or a Chart object. You can think of it this way: the Worksheet and Chart classes are each specialized instances of an internal Sheet class (and there's no way for anyone without access to the source code to know if this matches the actual implementation), but the Sheet class isn't publicly available. Working with Protection In general, the protection feature in Excel keeps users and/or code from modifying objects within a worksheet. Once you enable protection for a worksheet, unless you make provisions otherwise, users cannot edit or otherwise modify the sheet. Within the user interface, you can enable protection using the Tools|Protection|Protect Sheet menu item. Selecting this item displays the Protect Sheet dialog box shown in Figure 12. You can set a password here or can allow users to take specific actions. By default, all cells are locked once you turn on protection. In addition, you can allow users to edit specific ranges, using the Tools|Protection|Allow Users to Edit Ranges menu item, which displays the dialog box shown in Figure 13. Using a combination of these two dialog boxes, you can lock down the sheet and then allow users to edit specific features and ranges.
You can control the protection of your sheet programmatically using the Protect method of the worksheet. The syntax for the method looks like the following, in which each parameter is optional: ' Visual Basic WorksheetObject.Protect(Password, DrawingObjects, Contents, _ Scenarios, UserInterfaceOnly, AllowFormattingCells, _ AllowFormattingColumns, AllowFormattingRows, _ AllowInsertingColumns, AllowInsertingRows, _ AllowInsertingHyperlinks, AllowDeletingColumns, _ AllowDeletingRows, AllowSorting, AllowFiltering, _ AllowUsingPivotTables) // C# WorksheetObject.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables); The following list describes the parameters to the Protect method:
To protect a worksheet, call the sheet's Protect method, as in the following fragment, which sets the password and allows only 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, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing); Tip Clearly, hard-coding the password within your code isn't a great idea. Most likely, you'll need to request the password from the user, and apply it to the workbook without saving it. Generally, you won't find hard-coded passwords in source code. To unprotect a worksheet, you could use code like the following. This fragment assumes a procedure named GetPasswordFromUser, which would request the user to enter a password, and return the entered value: ' Visual Basic DirectCast(ThisApplication.Sheets(1), Excel.Worksheet). _ Unprotect(GetPasswordFromUser()) // C#
((Excel.Worksheet)ThisApplication.Sheets[1]). Unprotect(GetPasswordFromUser()); The Unprotect method removes protection from the worksheet, allowing you to supply an optional password. Excel also provides two other objects you'll find useful, when working with protection: the Protection and AllowEditRange objects. The Protection object encapsulates all the information you specify when you call the Protect method, as well as information about unprotected ranges. Calling the Protect method sets properties of the shared Protection object, which provides the following Boolean properties and which correspond to the parameters of the Protect method:
In addition, the Protection class provides the AllowEditRanges property, which allows you to specify the editable ranges on the worksheet, corresponding to the information specified in the dialog box shown in Figure 13. The AllowEditRanges property contains a collection of AllowEditRange objects, each of which provides a number of useful properties, including:
On the Worksheet Object sheet of the sample workbook (see Figure 14), you can experiment with programmatic protection. Click Protect to protect the sheet so that you can only edit within the shaded regions (the two ranges named Information and Date). Click Unprotect to unprotect the sheet. 
Figure 14. Test the protection capabilities of a worksheet. The links on the sample sheet run the following procedures: ' Visual Basic Private Sub ProtectSheet() Dim ws As Excel.Worksheet = _ DirectCast(ThisApplication.ActiveSheet, Excel.Worksheet) With ws.Protection.AllowEditRanges .Add("Information", ThisApplication.Range("Information")) .Add("Date", ThisApplication.Range("Date")) End With
ws.Protect() End Sub
Private Sub UnprotectSheet() 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 way. 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.AllowEditRanges; 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,Type.Missing,Type.Missing, Type.Missing,Type.Missing); } private void UnprotectSheet() { Excel.Worksheet ws = (Excel.Worksheet) ThisApplication.Sheets["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.AllowEditRanges; 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 introduce these objects and provide examples of working with them. Comments Using the Insert|Comment menu item, you can insert a text comment attached to a range on a worksheet (see Figure 15). You can accomplish the same goal in code using the AddComment method of the Range object. The following code deletes the comment associated with the range named Date if it exists, and then creates a new comment. Finally, the code displays all the comments on the sheet, 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. You can easily insert a new comment into a worksheet in the user interface. 
Figure 16. After showing all the comments on the sample sheet The Worksheet class provides its Comments property, which returns a Comments object. This collection of Comment objects allows you to iterate through all the Comment objects associated with the Worksheet. The Comment class doesn't provide many members. You're likely to use the Comment class's Visible property to show or hide a comment, or the Delete method to delete the comment. In addition, you may find the Text method useful: this method allows you to add text to the comment, either appending or overwriting existing text. Once you've added a comment, you might want to display the comments on a worksheet. The sample project includes a procedure, ShowOrHideComments, which shows or hides all the comments on the active sheet: ' 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 Next 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; } }
Outline Excel provides support for grouping rows of data using its outlining feature. You can take advantage of the same functionality from within your code, as well. For example, given the set of rows shown in Figure 17, you can add outlining (it's been done already in the figures) so you can collapse the rows as shown in Figure 18 and collapse the groups as shown in Figure 19. 
Figure 17. Create these groups 
Figure 18. Collapsed groups 
Figure 19. Completely collapsed groups The Worksheet class provides an Outline property, which is itself an Outline object. The Outline class doesn't provide many members, and the following list describes the ones you're likely to use:
The sample worksheet contains named ranges corresponding to the data for the years 2001 (Data2001) and 2002 (Data2002), and for the entire set of rows (AllData). These named ranges cover the entire width of the worksheet; in order for grouping to work, you must use ranges that consist of full rows. The data for 2003 doesn't have a named range associated with it so that the example code can demonstrate how you can work with entire rows as ranges. Creating groups is simple: you call the Group method of a range that corresponds to one or more full rows to create the group. (You can specify four optional grouping parameters, including the start and end values to be grouped, the group by value, and an optional array of Boolean values indicating grouping periods. The example uses none of these, because you rarely need any of these parameters.) Call the Ungroup method to remove the group. For example, clicking the Work with Groups link on the sample sheet runs the following code: ' Visual Basic Private Sub WorkWithGroups() Dim ws As Excel.Worksheet = _ DirectCast(ThisApplication.ActiveSheet, 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 these ' 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 that ' 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 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 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 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); } To group the three named ranges, the code simply calls the Group method of the range: ' Visual Basic ThisApplication.Range("Data2001").Group() // C# ThisApplication.get_Range("Data2001", Type.Missing). Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing); To group the unnamed range, the code uses the Rows property of the worksheet, given a range of rows. This property returns a range corresponding to the requested rows: ' 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); Clicking the Clear Groups link on the sample worksheet runs similar code to clear the groups: ' Visual Basic Private Sub ClearGroups() Dim ws As Excel.Worksheet = _ DirectCast(ThisApplication.ActiveSheet, 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(); } Using these techniques, you can create and remove groups, and control the grouping level displayed on your worksheets. The Range Object The Range object is the object you'll use most within your Excel applications; before you can manipulate any region within Excel, you'll need to express it as a Range object and work with methods and properties of that Range. The Range class is so important; just about every example you've seen so far in this article has used a Range object in some way or another. Basically, a Range object represents a cell, a row, a column, a selection of cells containing one or more blocks of cells (which may or may not be contiguous), or even a group of cells on multiple sheets. Because it would be impossible to discuss all the members of the huge Range class, this section focuses on three major issues:
In other words, because the Range object encompasses so many different uses in so many different situations, this section focuses on answering "How do I...?" questions, rather than providing a comprehensive list of members. Managing the Selection Although it's tempting to work with the current selection as a means to modifying the properties and behavior of a range, do your best to avoid it. Just like any other shared resource, the selection within Excel represents the user's selection. If you modify it in code, you've caused the user to lose control over the current selection. The rule of thumb is that you should call an object's Select method only if your intent is to change the user's selection. You should never call the Select method simply because it's convenient for you, as a developer. There are always alternatives, if your only goal is to set a property of a range. Avoiding the Select method will not only make your code run faster, in general, it will make your users happier. It's all too easy to write code like the following, to clear the region contiguous to the user's current cell: ' Visual Basic ThisApplication.ActiveCell.CurrentRegion.Select DirectCast(ThisApplication.Selection, Excel.Range).ClearContents // C#
ThisApplication.ActiveCell.CurrentRegion.Select(); ((Excel.Range)ThisApplication.Selection).ClearContents(); Doing this loses the user's selection. If only a single cell was originally selected, after running the previous code fragment the entire block of contiguous cells would be selected. Unless your goal was, in fact, to select the whole range of cells, a better solution would be to use code like the following: ' Visual Basic ThisApplication.ActiveCell.CurrentRegion.ClearContents // C#
ThisApplication.ActiveCell.CurrentRegion.ClearContents(); Why would anyone even think of using the first fragment? This kind of code happens because beginning Excel developers have a tendency to use the Excel macro recorder while attempting to discover how to use the various objects and their methods within Excel. This is a great idea, except that the macro recorder writes really terrible code. In general, the macro recorder uses the selection and modifies the selection when recording any task. Yüklə 0,58 Mb. Dostları ilə paylaş:
|
