Working around VSTO SE's lack of document-customization for Excel 2007

I've been doing some research on Visual Studio Tools for Office (VSTO) and Excel 2007 recently and found out that some very interesting features available in previous versions of VSTO for Excel 2003 are not available for Excel 2007. Here's a summary of the current situation and the workarounds I figured out.

State of the Art

VSTO has basically two kinds of Excel add-ins: document-level customization an application-level add-ins.

The document-level customization lets you customize an Excel workbook and also reference the Application to add buttons to the command bar and so forth. The resulting add-in will be only available to the customized workbook. The great benefit of document-level customization is that you can add controls to the spreadsheets within Visual Studio, being these controls part of the VSTO object model. The VSTO objects are wrappers around the Interop ones and they add very interesting functionality, like events and databinding. The VSTO controls are in the Microsoft.Office.Tools.Excel namespace whereas the Interop ones are in Microsoft.Office.Interop.Excel.

Document-level customization project template

Document-level customization project

On the other and, an application-level add-in is, as its name implies, always visible for the host Application regardless of the opened workbook. The only class added by Visual Studio is the TheAddin one, no workbook or worksheet editors are provided. The VSTO controls are not available neither. All Excel objects that we can get access through TheAddin class are from the Microsoft.Office.Interop.Excel API.

Application-level add-in project template

Application-level add-in project

The problem is that, whereas document-level customization is available for Excel2003, is not yet available for Excel 2007. So, the spreadsheet editor is not available within Visual Studio and the VSTO extended controls cannot be used. The table below shows which kinds of add-in are available for each product combination (see Features available by product combination for the complete table listing all Office applications).

	VSTO 2005 or Visual Studio Team System	VSTO 2005 SE installed with VSTO 2005 or Visual Studio Team System	VSTO 2005 SE installed with Visual Studio 2005 Professional Edition
Document-level customizations	Excel 2003	Excel 2003	Not available
Application-level add-ins	Not available	Excel 2003 Excel 2007	Excel 2003 Excel 2007

Workarounds

In order to make a document-level customization (or at least something similar) to target Excel 2007 I found out that some of the following things can be done:

1- Develop for Excel 2003 and deploy in Excel 2007

It is possible to develop a document-level customization in Visual Studio for Excel 2003 using VSTO 2005. The resulting customized document can then be opened in Excel 2007 in compatibility mode with the same functionality. The drawbacks of this approach is that Excel 2003 needs to be installed in the developer's machine and that you won't have Office 2007 features like custom panes and ribbon extensibility available.

2- Develop for Excel 2007 with VSTO SE and try to emulate the document-level customization.

In order to target Excel 2007, VSTO SE is needed and the only option by the moment is to create an application level add-in. For emulating the document customization you can create an Excel's template file within Excel application, add the desired named ranges, tables, graphics, etc...and then access these controls trough the add-in's code to make custom automation. The drawback of this approach is that the extended functionality of VSTO's controls is not available. For example, you won't be able to use the ListObject databinding support. In order to access the workbook controls, the Interop API must be used. Another drawback, is that the Interop API is not as well documented as the VSTO's.

3- Develop an application-level add-in for Excel 2007 and create the VSTO's objects from code.

Actually I'm not sure wheter this is really a choice, since I couldn't make it work. What I tried here was to create a new instance of a VSTO worksheet to wrap an Interop worksheet. In ThisAddin class I have the following code:

using Excel = Microsoft.Office.Interop.Excel;

using Vsto = Microsoft.Office.Tools.Excel;

namespace ExcelAddIn1

{

public partial class ThisAddIn

{

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

{

#region VSTO generated code

this.Application = (Excel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(Excel.Application), this.Application);

#endregion

try

{

Excel.Worksheet newSheet = (Excel.Worksheet)this.Application.ActiveWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Microsoft.Office.Tools.Excel.Worksheet extendedSheet = GetExtendedWorksheet(newSheet);

extendedSheet.Controls.AddNamedRange(extendedSheet.Range["A1","C2"], "NamedRange");

}

catch (Exception ex) {

MessageBox.Show(ex.ToString());

}

}

private Vsto.Worksheet GetExtendedWorksheet(Excel.Worksheet nativeWorksheet)

{

//Get the IHostItemProvider instance.

Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider hostItemProvider = (Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)

RuntimeCallback.GetService(typeof(Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider));

//Create the new worksheet and return it to calling function.

return new Vsto.Worksheet(hostItemProvider, RuntimeCallback, nativeWorksheet.CodeName,

null, nativeWorksheet.Name);

}

But, when trying to add the NamedRange to the extended worksheet this Exception is thrown:

This document might not function as expected because the following control is missing: Sheet4. Data that relies on this control will not be automatically displayed or updated, and other custom functionality will not be available. Contact your administrator or the author of this document for further assistance.
at Microsoft.Office.Tools.Excel.Worksheet.GetObjects()
at Microsoft.Office.Tools.Excel.Worksheet.GetPrimaryControl()
at Microsoft.Office.Tools.Excel.Worksheet.get_Range()
at ExcelAddIn1.ThisAddIn.ThisAddIn_Startup(Object sender, EventArgs e) in C:\...\Projects\ExcelAddIn1\ExcelAddIn1\ThisAddIn.cs:line 26

Conclusion

Although VSTO's document-level customization is not available for Excel 2007 yet, there still are some choices for developing add-ins targeting this application version. Besides, the good news is that this lack will be supplied soon. VSTO "Orcas" will provide document-level customization for Excel 2007 within its new features. So, anyway, it's just a matter of time...

Developing a Custom Property Comparison Validator using Entlib's VAP

 Here's a sample scenario where, in an application using Entlib's Validation Application Block (VAP), the validation of an entity's member depends on the value of  another property. In this case the entity I want to validate has two properties. Using the attributes approach to support validation, the entity's code looks like this:

public class MyEntity

{

    private string myVar1;

 

    [NotNullValidator()]

    [StringLengthValidator(0, 20, MessageTemplate = "MyProperty1 must be less than 20 characters length")]

    public string MyProperty1

    {

        get { return myVar1; }

        set { myVar1 = value; }

    }

 

    private string myVar2;

 

    [MyProp2Validator()]

    public string MyProperty2

    {

        get { return myVar2; }

        set { myVar2 = value; }

    }

}

The validators for Property1 are built in validators, no big deal with that. The thing here is that I want the second property to be validated against the value of the first one. Let’s say, for example, that Property2 is valid if it begins with the value of Property1.

Although the VAP ships with a PropertyComparisonValidator, it is limited to comparisons of type =, !=, >, >=, <>. So it seems that a custom validator has to be made. But how to obtain the actual value of Property1 from the Property2's custom validator?

One approach is to use reflection. Using reflection the DoValidate method of MyProperty2Validator would look something like this:

protected override void DoValidate(string objectToValidate, object currentTarget, string key, ValidationResults validationResults){

    //obtain the other property value through reflection

    PropertyInfo prop = currentTarget.GetType().GetProperty("MyProperty1");

    string myProp1Value = prop.GetValue(currentTarget, null) as string;

    //some custom logic here

    if (! objectToValidate.StartsWith( myProp1Value ){

        LogValidationResult(validationResults,"msg",currentTarget,key);

    }

}

But the dropdown of using reflection is that it wouldn't work with UI integration.

What do work with integration is what the built-in PropertyComparisonValidator does, and that is using the ValueAccess class. The ValueAccess class allow us to find other members in an integration friendly way throuhg its GetValue method. See how this is done in our Property2 valdiator:

[ConfigurationElementType(typeof(MyProp2ValidatorData))]

class MyProp2Validator : Validator<string> {

    private ValueAccess valueAccess;

    internal const string OtherPropName = "MyProperty1";

 

    public MyProp2Validator(ValueAccess valueAccess):base (Resources.MyProp2ValidatorMessageTemplate,null){

        this.valueAccess = valueAccess;

    }

 

    protected override void DoValidate(string objectToValidate, object currentTarget, string key, ValidationResults validationResults){

        object myProp1;

        string valueAccessFailureMessage;

        //try to obtain the value of property 1.

        if (!this.valueAccess.GetValue(currentTarget, out myProp1, out valueAccessFailureMessage)) {

            base.LogValidationResult(validationResults, string.Format(CultureInfo.CurrentUICulture, Resources.MyProp2ValidatorFailureToRetrieveProp1, new object[] { this.valueAccess.Key, valueAccessFailureMessage }), currentTarget, key);

        }

        else {

            //custom validation logic between prop1 and prop2

            if (!objectToValidate.StartsWith((string)myProp1)) {

                base.LogValidationResult(validationResults, string.Format(CultureInfo.CurrentUICulture, this.MessageTemplate, new object[] { objectToValidate, myProp1 }), currentTarget, key);

            }

        }

    }

 

    protected override string DefaultMessageTemplate {

        get { return Resources.MyProp2ValidatorDefaultMessageTemplate; }

    }

}

What’s left is to provide the ValueAccess to the validator’s constructor. This is done in the MyProp2ValidatorAttribute class, if using attributes to place validators, or in the MyProp2ValidatorData if using configuration files. MyProp2ValidatorAttribute would look like this:

public class MyProp2ValidatorAttribute : ValueValidatorAttribute

{

    protected override Validator DoCreateValidator(Type targetType)

    {

        throw new InvalidOperationException("A member value access builder is needed.");

    }

 

    protected override Validator DoCreateValidator(Type targetType, Type ownerType, MemberValueAccessBuilder memberValueAccessBuilder)

    {

        PropertyInfo propertyInfo = ownerType.GetProperty(MyProp2Validator.OtherPropName);

        if (propertyInfo == null)

        {

            throw new InvalidOperationException(String.Format(Resources.MyProp2ValidatorAttributeCouldNotFindProperty, new string[] { ownerType.Name, MyProp2Validator.OtherPropName }));

        }

        return new MyProp2Validator(memberValueAccessBuilder.GetPropertyValueAccess(propertyInfo));

    }

}

Finally, you can test the validation logic using the following code:

private void buttonValidate_Click(object sender, EventArgs e)

{

    MyEntity ent = new MyEntity();

    ent.MyProperty1 = textBoxProp1.Text;

    ent.MyProperty2 = textBoxProp2.Text;

    ValidationResults r = Validation.Validate<MyEntity>(ent);       

    if (!r.IsValid)

    {

        DisplayValidationResults(r);

    }

    else {

        MessageBox.Show("ok");

    }

}

It's only left to provide the UI integration for the custom validator to assure averything's working right. I hope to be posting this in a second part soon.

Workflow Foundation conference's material

The material (powerpoint presentation and code samples) of the Introduction To Workflow Foundation conference is now available for download.

Sign up in the WF's conference @ MS Argentina

On Thursday the 12^th (from 9 am to 1 pm) I will take part in the “Introduction to Windows Workflow Foundation” conference given at Microsoft Argentina, along with Diego Gonzalez (MVP) and Jorge Fioranelli.

Here is the conference abstract:

“Between the new technologies included in the recently released .Net Framework 3.0, we found Windows Workflow Foundation, which consists on the most innovating tool set included in this new framework’s version. The possibility of extracting the workflow concept from the applications as a reusable and extensible element is an extremely innovating aspect in terms of application design. Tools are also included that let you visually edit workflows, enabling analysts and users without programming knowledge to extend applications with a minimum impact in the whole application.

The conference will consist on an introduction to the technology, showing its main characteristics, a demonstration of its more relevant advanced aspects, and will end with two examples of real projects where Windows Workflow Foundation has been used in combination with other technologies to solve common issues.”

You can see the details and sign up here.

See you there!