Professional VSTO 2005 - Visual Studio 2005 Tools For Office (2006) [eng]

Chapter 2

CalculateTotal()

If MessageBox.Show(“Are you sure you want to submit ?”, “Time sheet submission”, MessageBoxButtons.YesNo) = DialogResult.Yes Then

‘email the document to management Me.Application.ActiveWorkbook.SendMail(“[e-mail]”,

DateTime.Now.ToString(“hh/MM/yyyy”)) End If

End Sub

Private Sub Sheet1_Shutdown1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

‘The user is through so email the document to recipient EmailDocument()

End Sub

C#

using System; using System.Data;

using System.Drawing; using System.Windows.Forms;

using Microsoft.VisualStudio.Tools.Applications.Runtime; using Excel = Microsoft.Office.Interop.Excel;

using Office = Microsoft.Office.Core;

namespace BareBonesTM
{
public partial class Sheet1
{
/// <summary>
/// This routine formats the worksheet
/// </summary>
private void FormatSheet()
{
//take care of some aesthetic issues
Application.DisplayFormulaBar	= false;
Application.DisplayFunctionToolTips = false;
Application.DisplayScrollBars	= false;
Application.DisplayStatusBar	= false;

//make some customizations Application.Rows.Worksheet.SetBackgroundPicture(@”C:\Documents and

Settings\All Users\Documents\My Pictures\Sample Pictures\blue hills.jpg”);

//remove worksheet 2 and worksheet 3 Application.DisplayAlerts = false;

((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[2]).Delete(); ((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[2]).Delete(); ((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[1]).Name = “Bare Bones Time”;

Application.DisplayAlerts = true;

//hide column and row headers Application.ActiveWindow.DisplayGridlines = false;

48

Excel Automation

Application.ActiveWindow.DisplayHeadings = false;

}

///<summary>

///This routine customizes the worksheet with prefetched data

///</summary>

private void CustomizedData()

{

//set a namedrange Microsoft.Office.Tools.Excel.NamedRange formattedRange =

this.Controls.AddNamedRange(this.Range[“A1”, “D10”], “formattedRange”);

//note range names Microsoft.Office.Tools.Excel.NamedRange preFilledRange =

this.Controls.AddNamedRange(this.Range[“A2”, “A9”], “PreFilledRange”);

//formattedRange.ShrinkToFit = true; formattedRange.ShowErrors();

//auto fill days of the week Microsoft.Office.Tools.Excel.NamedRange firstCell = this.Controls.AddNamedRange(this.Range[“A2”, missing], “FirstCell”);

//note must seed the value firstCell.Select(); firstCell.Value2 = “Monday”;

//note must use the firstcell range that points to A1 for the autofill

to work

firstCell.AutoFill(Application.get_Range(“A2:A6”, missing), Excel.XlAutoFillType.xlFillWeekdays);

preFilledRange.BorderAround(missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

preFilledRange.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormat3DEffects1, true, false, true, false, true, true);

//get a reference to the header cell Microsoft.Office.Tools.Excel.NamedRange MergeRange =

this.Controls.AddNamedRange(this.Range[“A1”, “D1”], “MergeRange”);

//format the header cell MergeRange.EntireRow.Font.Bold = true; MergeRange.Value2 = “Time Sheet [Week - “ +

DateTime.Now.ToString(“hh/MM/yyyy”) + “]”; MergeRange.EntireRow.Font.Background =

Excel.XlBackground.xlBackgroundTransparent;

//turn off merged prompt dialog and then merge Application.DisplayAlerts = false; MergeRange.Merge(true); Application.DisplayAlerts = true;

//setup the range for data entry Microsoft.Office.Tools.Excel.NamedRange valueRange = this.Controls.AddNamedRange(this.Range[“B2”, “B6”], “ValueRange”);

49

Chapter 2

valueRange.NumberFormat = “#,###.00”; valueRange.Font.Bold = true;

valueRange.BorderAround(missing, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic, missing);

valueRange.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatColor2, true, false, true, false, true, true);

Microsoft.Office.Tools.Excel.NamedRange commentRange = this.Controls.AddNamedRange(this.Range[“B2”, missing],”CommentRange”);

//add the comment

commentRange.AddComment(“Enter your hours worked here.”);

}

private void Sheet1_Startup(object sender, System.EventArgs e)

{

//customize the worksheet during start up FormatSheet();

//add the customized data CustomizedData();

}

private void CalculateTotal()

{

Microsoft.Office.Tools.Excel.NamedRange totalRange = this.Controls.AddNamedRange(this.Range[“B2”, “B6”], “TotalRange”);

int[] fields = new int[] { 1, 2, 3, 4, 5 };

totalRange.Subtotal(1, Excel.XlConsolidationFunction.xlSum, fields, missing, missing, Excel.XlSummaryRow.xlSummaryBelow);

}

private void EmailDocument()

{

//calculate the hours worked CalculateTotal();

if (MessageBox.Show(“Are you sure you want to submit ?”, “Time sheet submission”, MessageBoxButtons.YesNo) == DialogResult.Yes)

{

//email the document to management this.Application.ActiveWorkbook.SendMail(“[e-mail]”,

DateTime.Now.ToString(“hh/MM/yyyy”), missing);

}

}

private void Sheet1_Shutdown(object sender, System.EventArgs e)

{

//The user is through so email the document to recipient EmailDocument();

}

#region VSTO Designer generated code

///<summary>

///Required method for Designer support - do not modify

///the contents of this method with the code editor.

50

Excel Automation

/// </summary>

private void InternalStartup()

{

this.Startup += new System.EventHandler(Sheet1_Startup); this.Shutdown += new System.EventHandler(Sheet1_Shutdown);

}

#endregion

}

}

Listing 2-24 Case study application

The code assumes that there is a legitimate file called blue hills.jpg given by the above path. If the VSTO runtime cannot find this file, an exception will be thrown.

Aside from the aesthetics, the core code used here is the typical format of an Excel-type application. Some code is used to initialize the spreadsheet and format according to user requirements and some cleanup code is necessary. The bulk of the work is mostly done through end-user interaction with the spreadsheet. Because Excel has most of the functionality already built in, it saves an enormous amount of development time. This is one true benefit of a VSTO-based application.

private void Sheet1_Startup(object sender, System.EventArgs e)

{

//customize the worksheet during start up FormatSheet();

//add the customized data CustomizedData();

}

The FormatSheet method’s main purpose is to disguise the appearance of the Excel spreadsheet so that the final application looks more like a windows application than a hybrid spreadsheet. The code uses a default background, but it is easy to pretend that the background is some fancy watermark image drawn up by the company’s elite graphics division.

Consider this piece of code:

//remove worksheet 2 and worksheet 3 ((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[2]).Delete(); ((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[2]).Delete();

((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[1]).Name = “Bare Bones

Time”;

The code deletes the second sheet twice. The reason for this is that the default collection contains three worksheets. The first line of code removes worksheet 2. The collection now contains only two worksheets. This is the reason for the second delete routine on worksheet 2. An exception would be thrown at this point if the code attempted to delete worksheet 3.

51

Chapter 2

Admittedly, the hard-coded approach is an eyesore, but it works. A more elegant approach would be to first find the count of the spreadsheet and then remove one less than the count. An example implementation is trivial and best left as an exercise to the reader.

The customizeddata routine handles a large part of the workload. A few points are worth noting. The code makes exclusive use of the Range object and named ranges. Named ranges are simply easier to maintain because the code is more readable especially by someone who is not familiar with the code. The code is also easier to update if the range changes, since the change only needs to occur in one place.

The next snippet of code makes use of the AutoFill routine.

//note must seed the value firstCell.Select(); firstCell.Value2 = “Monday”;

firstCell.AutoFill(Application.get_Range(“A2:A6”, missing), Excel.XlAutoFillType.xlFillWeekdays);

Excel supports a few predefined AutoFill patterns that come in handy. Notice that the range must be seeded with the first value “Monday” in order for AutoFill to work correctly. If there is no value, AutoFill functionality is simply ignored.

Finally, the range is merged and formatted for consistency and a comment is added to the range so that the user knows what to do. As previously discussed, the code turns off the confirmation prompts when the spreadsheet is modified in the merging process. Merging isn’t strictly necessary. However, it is a convenience we can afford, since it allows the code to treat a range as a single cell. Finally, the code presents an option to email the document.

Notice that the code is missing some rather basic functionality, but it is sufficient to illustrate the idea behind VSTO application development. In particular, exception handling is notably absent. For instance, the file load method call assumes that the file is present on disk and the executing application has sufficient permissions to secure the load. In the real world, these assumptions are not valid. You are certainly at liberty to expand and improve upon this implementation as needed. It merely provides a good beginning point for application functionality based on the concepts presented earlier.

Summar y

This chapter focused on the basic functionality of the Excel spreadsheet. The basic functionality revolves around data. The code showed how to load data from a variety of different data sources. Excel’s flexible data loads stem from the need to manipulate data in different environments.

Excel also contains internal support for XML file formats. The importance of XML as a cross-platform data source cannot be overstated: it is the standard for data exchange among corporate entities. However, the formatting features that form part of the payload are resource intensive during the render phase. Large XML files can take a considerable amount of time to be loaded into the spreadsheet.

52

Excel Automation

Next, we examined the code to manipulate the data. Data loaded into the Excel spreadsheet is available for use in a number of containers. The most widely used container is the Excel Range object. The Range object contains significant formatting capabilities, and the code presented a few notable examples. Other containers such as the cell object allow fine-grained customization of data.

Finally, we examined a complete coded example that showed how to build bare-bones time management software based on Microsoft Excel. The code showed that the Excel user interface does not necessarily have to look like an Excel spreadsheet. In fact, by applying just the right amount of formatting, the application can approach the aesthetic appeal of a third-party product.

53

Advanced Excel Automation

The Excel machinery has had a few years to evolve. Today, Microsoft Excel contains a robust calculation engine that is well suited for crunching numbers. In addition, the Excel user interface is a powerful yet familiar front end that has grown into a standard piece of furniture on corporate desktops; about 80% of corporations use Microsoft Excel.

Excel can cater to a variety of environments and can derive its data from different formats across different platforms. However, the security aspects of Excel have always been worrying. Recall from Chapter 2 that macros can easily compromise legacy Office applications. A large portion of this chapter examines the security approach that Visual Studio Tools for Office has adopted to mitigate security threats. Once the security aspects are understood, the remainder of the chapter examines the new VSTO controls in depth. Finally, time is spent examining the more advanced side of Excel programming to include addins VSTO menus, server automation, and integration of VSTO with .NET web services.

VSTO Security

Security is an important concern today. However, failure to understand application development from a security perspective coupled with a lack of language support conspire to derail the security initiative. In the end, a compromise leads to second-best security practices, which leaves the application vulnerable to hackers.

VSTO provides two levels of security for application development. One level resides in the .NET Framework and the other forms part of the user interface. We consider the latter first, followed by the former. Together, the two-tiered security fortress significantly reduces the surface area available for attack.

Workbook Password Protection

VSTO offers several ways to protect the contents of data. One approach uses password authentication to restrict access to data in the workbooks. Be aware though, that VSTO does not provide any

Chapter 3

functionality to test the strength of the password associated with the workbook. It simply acts as a storage vessel for the password. If you require such functionality, you must implement it yourself.

Security initiatives should be designed and planned for. It should not be an afterthought. Security is also especially important since Excel allows up to 256 users to view a single workbook.

Consider a password implementation example, shown in Listing 3-1. For this example, the code assumes that the password property has been set previously and is available for use by the calling code.

Visual Basic

Public Overrides Property Password() As String Get

Return MyBase.Password End Get

Set(ByVal value As String)

If Not String.IsNullOrEmpty(value) Then MyBase.Password = value

Else

Throw New ArgumentException(“Password Cannot Be Blank”, “Password”) End If

End Set End Property

Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

‘Protect The Workbook

Dim password As String = Me.Password Me.Protect(password)

‘Unprotect The Workbook

Me.Unprotect(password) End Sub

C#

string passwordData = string.Empty; public string Password

{

get { return passwordData; } set

{

if (!String.IsNullOrEmpty(value passwordData = value;

else

throw new ArgumentNullException(“Password Cannot Be Blank”,

“Password”);

}

}

private void Sheet1_Startup(object sender, System.EventArgs e)

{

//Protect The Workbook

56

Advanced Excel Automation

string password = this.Password;

this.Protect(password, missing, missing,missing, missing, missing,missing, missing, missing, missing,missing, missing, missing,missing,missing,missing);

//Unprotect The Workbook this.Unprotect(password);

}

Listing 3-1: Excel’s internal password support

In Listing 3-1, a property Password is used to set or retrieve the password attached to the workbook resource in Microsoft Excel. Then, workbook protection is applied and removed. For illustrative purposes, a property was used to manage the password retrieval process. Since properties ultimately map to method calls deep inside the .NET Framework, choosing a property implementation over a method implementation for the password management process is purely aesthetic, with no significant performance or readability benefit.

Assuming that you have negotiated your way around this issue, a more sinister villain is lurking. Although passwords are encrypted, Excel passwords are notably easy to crack. In fact, there are a number of free software applications available for that express purpose. There is also no limit imposed on the number of failed attempts as is common in most security-conscious applications. For instance, the Excel application does not lock the user out after three failed attempts. Password-cracking applications can usually brute-force their way into sensitive data.

If you choose to implement secure passwords, at the very minimum, you should keep track of the failed attempts. One good approach is to keep a counter variable that is associated with the logged-on user’s credentials. The counter variable represents the number of failed attempts, and the user’s credentials indicate the user who is making the failed attempts. Once the maximum number of failed attempts is reached, no more attempts are allowed by that user based on his or her login credentials for a set period of time — 24 hours for instance. You may use the Datetime object in .NET to keep track of the elapsed time during the failed attempts for a specific user.

Worksheet Security

Data protection may also be enforced at the worksheet level. This approach provides more fine-grained control over data access when compared to protection at the workbook level. Worksheets may have protection applied to them through the Protection object. Consider the example in Listing 3-2.

Visual Basic

Dim userPassword As String = Me.Password

Globals.Sheet1.Protect(userPassword)

C#

String userPassword = Password;

Globals.Sheet1.Protect(userPassword, missing, missing, missing, missing, missing,

missing, missing, missing, missing, missing, missing, missing, true, missing, missing);

Listing 3-2: Password protecting worksheets

57