Understanding the Excel Object Model from a. Net developer's Perspective
Figure 6. With the PrecisionAsDisplayed property set to True, Excel uses only the visible decimals to perform its calculations
Yüklə 0,58 Mb.
|
Figure 6. With the PrecisionAsDisplayed property set to True, Excel uses only the visible decimals to perform its calculations.
Working with Document Properties Just like the other Office applications, Excel allows you to store document properties along with the workbook. Excel provides a number of built-in properties, and you can add your own, as well. Selecting File|Properties displays the dialog box shown in Figure 7, and you can also select the Custom tab to create and modify custom properties. 
Figure 7. Use this dialog box to set document properties. Use the Workbook class BuiltInDocumentProperties property to work with built-in properties, and the CustomDocumentProperties property to work with custom properties. Each of these properties returns a DocumentProperties object, which is a collection of DocumentProperty objects. You can use the Item property of the collection to retrieve a particular property, either by name, or by index within the collection. The full list of property names is available in the Excel documentation, but there's an easy way to retrieve the list: the following procedure runs when you click the Document Properties link on the sample workbook (see Figure 8). This procedure calls the DumpPropertyCollection method to list all the built-in properties and their current values, and then repeats the process for the custom properties. In addition, the procedure modifies the Revision Number property individually, and creates a new custom property: ' 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 ex As Exception MessageBox.Show(ex.Message) End Try ' Work with custom properties: Try prps = DirectCast( _ ThisWorkbook.CustomDocumentProperties, _ Office.DocumentProperties) DumpPropertyCollection(prps, rng, i) Catch ex As 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 ex As 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 Next
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 {
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 properties: 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 property. prp = prps.Add("Project Name", false, Office.MsoDocProperties.msoPropertyTypeString, "White Papers", Type.Missing); } catch (Exception ex) { MessageBox.Show(ex.Message, ThisApplication.Name); } }
{ ThisApplication.ScreenUpdating = true; } }
Office.DocumentProperties prps, Excel.Range rng, int i) { foreach (Office.DocumentProperty prp in prps) { rng.get_Offset(i, 0).Value2 = prp.Name; try {
{ rng.get_Offset(i, 1).Value2 = prp.Value.ToString(); } } catch { // Do nothing at all. } i += 1;
} return i; }
Working with Styles Much like a Word document, Excel workbooks allow you to apply named styles to regions within the workbook, and Excel supplies a number of predefined (although not terribly interesting) styles. You can use the Format|Styles menu item to display a dialog box that allows you to interactively modify styles, as shown in Figure 9.
If you click Modify on the Style dialog box, you'll display the Format Cells dialog box shown in Figure 10. 
Figure 10. Use the Format Cells dialog box to modify styles. The Format Cells dialog box displays all the options you can use when formatting cells, and each of the options available in this dialog box is available from within your code. You can use the Styles property of a Workbook object to interact with and apply styles to ranges within the workbook. You can create, delete, and modify styles, using the Styles property of a Workbook object. Clicking Apply Style on the sample workbook runs the following procedure, which creates a new style (or uses an existing one, if you've run the code already), sets up the various aspects of the style, and applies it to a region: ' 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 the range filled in by the document properties. This procedure uses the Range.End method to find the end of the range filled with document properties, and then creates a new range based on the upper-left corner of the range and the bottom-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); }
Once you've run the code, the region containing document properties on the sample workbook changes shading and font, as shown in Figure 11. 
Figure 11. After applying a custom style Clicking Clear Style runs the following procedure, which clears the style for the same region: ' Visual Basic Private Sub ClearStyle() ' Get the range containing all the document properties, and ' clear the style. GetDocPropRange().Style = "Normal" End Sub
// C# private void ClearStyle() { // Get the range containing all the document properties, and // clear the style. GetDocPropRange().Style = "Normal"; } Working with Sheets The Workbook class provides a Sheets property that returns a Sheets object. This object contains a collection of Sheet objects, each of which can be either a Worksheet or a Chart object. Clicking List Sheets on the sample workbook runs the following procedure and lists all the existing sheets 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.Sheets rng.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 sh in ThisWorkbook.Sheets) { rng.get_Offset(i, 0).Value2 = sh.Name; i = i + 1; }
You may also find the following members of the Sheets class to be useful.
Tip If you have some reason to want to sort the list of sheets in a workbook, you can make use of the Move method to perform an inefficient bubble sort. Of course, because you're unlikely to have very many sheets, the speed of the sort won't be an issue.
Tip Many of the methods listed in this section apply to other classes. For example, the PrintOut method is provided by the Chart, Charts, Range, Sheets, Window, Workbook, Worksheet, and Worksheets classes. The methods work the same in each case; they just act on a different object. The Select method applies to just about every selectable object (and there are many of them). Methods of the Workbook Class The Workbook class provides a huge number of methods, many handling very specific situations. Rather than focus on the details, this section discusses some of the methods you're likely to use in every application, leaving the esoteric methods for later study. The following list describes some of the methods you're most likely to need:
Yüklə 0,58 Mb. Dostları ilə paylaş:
|
