Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 20. February 2011 17:27

While browsing the ListObject documentation today, I realized that, while all the examples given focused on binding to a DataSet, it also supports databinding to “any component that implements the IList interface”. This is something I wasn’t aware of, so I figured I would give it a try.

I quickly created a Excel 2007 Template project in VS2010, and added a simple Product class as follow:

public class Product
{
   public string Name { get; set; }
   public double Price { get; set; }
}

I then added the following code behind Sheet1, creating a straightforward list of Product, as well as a ListObject, setting the DataSource to the list:

public partial class Sheet1
{
   private List<Product> products;
   private ListObject listObject;

   private void Sheet1_Startup(object sender, System.EventArgs e)
   {
      this.listObject = this.Controls.AddListObject(this.Range["B2"], "Products");

      this.products = new List<Product>();
      this.products.Add(new Product() { Name = "Alpha", Price = 10d });
      this.products.Add(new Product() { Name = "Bravo", Price = 20d });
      this.products.Add(new Product() { Name = "Charlie", Price = 30d });

      this.listObject.DataSource = products;
      this.listObject.AutoSetDataBoundColumnHeaders = true;
   }
   // auto-generated code omitted
}

Hit F5, and watch:

image

Out of the box, we get a nicely formatted list, with filters in the headers. If anything, that’s a convenient way to display a list of items on a Worksheet. I didn’t have time to dig deeper into it, but I am now very curious about how much more can be done with this mechanism. Can I add data validation? Can I control what properties to display?

by Mathias 23. January 2011 15:51

Excel Data Validation provides a nice mechanism to help users select from a set of acceptable choices, by adding a drop-down directly in a cell and displaying the list of options when the cell is selected. To do that within Excel, just go to the Data ribbon, and the Data Validation button displays a dialog like the one below. Selecting allow “List”, and typing in a few comma-separated values in the Source section will do the job. How would we go about to do the same thing from .NET?

DataValidationDialog

Turns out it’s not very complicated, as I just found out. Just create a Validation object, Add it to a Range, and you are good to go. Here is a code snippet to do just that, from a VSTO project:

var excel = Globals.ThisAddIn.Application;
var worksheet = (Worksheet)excel.ActiveSheet;
         
var list = new List<string>();
list.Add("Alpha");
list.Add("Bravo");
list.Add("Charlie");
list.Add("Delta");
list.Add("Echo");

var flatList = string.Join(",", list.ToArray());

var cell = (Range)worksheet.Cells[2, 2];
cell.Validation.Delete();
cell.Validation.Add(
   XlDVType.xlValidateList,
   XlDVAlertStyle.xlValidAlertInformation,
   XlFormatConditionOperator.xlBetween,
   flatList,
   Type.Missing);

cell.Validation.IgnoreBlank = true;
cell.Validation.InCellDropdown = true;

Nothing fancy, but as usual it took a bit of searching to figure out the right enumerations to use in the method call – hopefully it will be useful to someone else!

In the process, I found out two things. First, I wondered what would happen if I tried to set through code the contents of a cell to a value that isn’t valid. The answer is, Data Validation doesn’t validate anything in that case – it appears to be strictly a UI mechanism. Then, I realized that I had no clear idea what the 2nd and 3rd tab in the dialog do; turns out, these are potentially pretty cool. Input Message behaves like a ToolTip that shows up on cell selection, with a title and message, in a way similar to Comments, but not editable. Error Alert defines the message that should show up when an invalid value is entered – and allows to disable the Error Alert if need be. So if all you wanted was a DropDown with “suggested” choices, you could just disable the error alert, and you would have a cell with a DropDown, where users could still type any freeform text they please.

by Mathias 16. January 2011 13:08

One of my favorite features in VSTO is the custom task pane. It provides a very natural and unobtrusive mechanism to expose your add-in functionality, fully integrated into Office, and makes it possible to use WPF for user interface development.

First_matryoshka_museum_doll_openHowever, the Task Pane is not natively a WPF control. When you create your own Custom Task Pane, you pass it a good-old WinForms control, which will then be displayed in the Task Pane. You can then add two Russian dolls to the construction: an ElementHost control inside your WinForms control (found in the WPF Interoperability section of the ToolBox), and a WPF control inside the ElementHost. At that point, your TaskPane is WPF-ready, and you can happily begin adding shiny WPF controls to your Task Pane and forget about WinForms.

If you want your Task Pane to look seamless to your user, you will probably need to play a bit with Docking. If not, two specific issues could arise:

  • Your WPF control is fairly small, and doesn’t take all the surface of the Task Pane, leaving the original WinForms color background visible in the uncovered areas,
  • Your WPF control is too large for the Task Pane surface, leaving parts of the control invisible to the user, who cannot access them.

The first situation is mostly aesthetics (it just looks ugly), but the second case is a bit problematic, as it could make your Task Pane virtually unusable.

To illustrate the issue, let’s create an Excel 2007 Add-In project “AddInLab” in Visual Studio, add a WinForms control “TaskPaneWpfHostControl”, drop an ElementHost control in there, which we rename to wpfElementHost, instead of elementHost1, and set its Dock property to Fill so that it takes up the entire surface of the control. We’ll edit the code-behind, to provide access to the ElementHost via a public property:

namespace AddInLab
{
   using System.Windows.Forms;
   using System.Windows.Forms.Integration;

   public partial class TaskPaneWpfControlHost : UserControl
   {
      public TaskPaneWpfControlHost()
      {
         InitializeComponent();
      }

      public ElementHost WpfElementHost
      {
         get
         {
            return this.wpfElementHost;
         }
      }
   }
}

More...

by Mathias 28. November 2010 16:22

Sometimes, you need to know when your user decided to move to another Worksheet in Excel. Fortunately, Excel exposes some events for this. At the workbook level, Workbook.SheetActivate and Workbook.SheetDeactivate are fired when the user activates or deactivates a sheet in the Workbook, and at the application level, Application.WorkbookActivate and Application.WorkbookDeactivate are triggered when the user changes Workbooks.

This looks all nice and simple, except that there is a small catch, which caused me a bit of grief on my current project. I naively thought that when a user activated a new Workbook, it would fire WorkbookActivate, and SheetActivate. Wrong – when you activate a new Workbook, only WorkbookActivate is triggered.

The following VSTO code illustrates the point: Excel traps when a new Workbook is added, and begins tracking the Sheet activation/deactivation for that new Workbook.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
   var excel = this.Application;
   WorkbookAdded(excel.ActiveWorkbook);
   ((Excel.AppEvents_Event)this.Application).NewWorkbook += WorkbookAdded;
   excel.WorkbookOpen += WorkbookAdded;
   excel.WorkbookActivate += WorkbookActivated;
   excel.WorkbookDeactivate += WorkbookDeactivated;
}

private void WorkbookAdded(Excel.Workbook workbook)
{
   workbook.SheetActivate += SheetActivated;
   workbook.SheetDeactivate += SheetDeactivated;
}

private void WorkbookActivated(Excel.Workbook workbook)
{
   MessageBox.Show("Workbook activated.");
}

private void WorkbookDeactivated(Excel.Workbook workbook)
{
   MessageBox.Show("Workbook deactivated.");
}

private void SheetActivated(object sheet)
{
   MessageBox.Show("Sheet activated.");
}

private void SheetDeactivated(object sheet)
{
   MessageBox.Show("Sheet deactivated.");
}

If you run this code, you will note that when you change sheets within a Workbook, the Message Box “Sheet deactivated” pops up, followed by “Sheet activated”. However, if you add multiple workbooks, and start changing workbooks, only “Workbook activated” / “Workbook deactivated” shows up.

The morale of the story is that if you are interested in tracking when a user changed the active worksheet across workbooks, you can’t simply rely on SheetActivated: you will need to look out for Workbook level events, and when these occur, figure out through the Workbook active worksheet which sheet has been activated or deactivated.

I think the reason this caught me off-guard is that I had this Worksheet-centric mental image of Excel: when I am changing workbooks, my goal is to select a Worksheet in that Workbook, the Workbook is simply a means to an end – and I expected the events to reflect that. However, if you consider the Workbook as its own isolated entity, it makes sense: when I leave a Workbook, it simply becomes invisible, but otherwise nothing changed: the Worksheet that is active remains active, and will still be active when I come back later.

The other interesting pitfall is that when you start Excel, there is a Workbook active – but because it is created before you can begin trapping events, you have to register it manually if you want to track its behavior as well.

by Mathias 22. November 2010 17:54

One of the reasons I like .NET extension methods is that they provide a nice way to work with existing libraries, and tweak the public API to create custom methods and extend existing objects without modifying them. For instance, I regularly end up creating a few when working with the Office interop. Imagine for instance that you had an Excel project where you wanted to apply a consistent format to some ranges; you could write an extension method like this one:

public static void ApplyStandardFormat(this Range range)
{
   range.Font.Bold = true;
   range.Font.Color = ColorTranslator.ToOle(System.Drawing.Color.White);

   range.Interior.Pattern = XlPattern.xlPatternSolid;
   range.Interior.Color = ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
}

The nice thing here is that because of the addition of the this keyword in the signature “this Range range”, you can now use this method as if it was naturally exposed by the Range object, like this:

myRange.ApplyStandardFormat();

Arguable, this isn’t the greatest example, and doesn’t necessarily warrant an extension, but you get the idea.

More...

Comments

Comment RSS