Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
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?

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>();

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

var cell = (Range)worksheet.Cells[2, 2];
cell.Validation.Delete();
XlDVType.xlValidateList,
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.

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.

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
{
{
InitializeComponent();
}

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

More...

27. September 2010 18:15

An interesting user interface issue came up in the project I am working on right now.  In a nutshell, the user needs to select an item from a list of existing items. This sounds straightforward, except that the list of items has hundreds of elements. Clearly, a combo box or a list box won’t do the job – we need a convenient way to reduce the choices and help the user find what he needs.

One suggestion I liked was to use something similar to an auto-complete: type in a few letters, and show only “relevant” items to chose from:

While digging into the question, I came into a .NET interface I didn’t know, ICollectionView. Essentially, it is a collection that is suitable for WPF databinding, and also has filtering capabilities. It stores a source collection, which contains all the items, but when a filter is applied to it, WPF will only “see” the filtered results.

## Displaying a standard list

Let’s first create a WPF application, and warm up by adding a simple class, Product:

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

Now let’s add a WPF UserControl, SearchAndSelectView.xaml. The control has a TextBlock, where we will display the name of the selected Product, a TextBox, where the user will type in the filtering text, and a ListBox, where we will display a selection of Products.

<UserControl x:Class="QuickSearchAndSelect.SearchAndSelectView"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
mc:Ignorable="d">
<StackPanel Width="150">
<TextBlock Text="{Binding Path=SelectedProduct.Name}"/>
<TextBox x:Name="Filter"/>
<ListBox Height="150" x:Name="Products"
ItemsSource="{Binding Path=Products}"
SelectedItem="{Binding Path=SelectedProduct}">
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel>
<TextBlock Text="{Binding Path=Name}"/>
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
</StackPanel>
</UserControl>

More...

8. March 2010 12:50

Previous episodes

The shell of our control is ready – today we will fill the TreeView with all the open workbooks and worksheets. We will use a common design pattern in WPF: we will create objects that act as intermediary between the user interface and the domain objects. This approach is know as MVVM (Model-View-ViewModel) in WPF, and is a variation on the classic Model-View-Presenter pattern – the main difference being that MVVM relies heavily on the data binding capabilities of WPF.

As usual, Josh Smith has some great material on how to use the WPF TreeView, which is highly recommended reading – and was a life-saver in figuring out how things work.

In a first step, we will fill in the TreeView with fake data, and once the UI “works”, we will hook up the objects to retrieve real data from Excel.

To quote Josh Smith, “the WinForms TreeView control is not really providing a “view” of a tree: it is the tree”, whereas “the TreeView in our WPF programs to literally provide a view of a tree”, to which we want to bind. In our case, the tree we want to represent is that Excel has a collection of Workbook objects, which each has a collection of Worksheet objects. Let’s build that structure.

More...

2. March 2010 07:21

Now that our Custom Task Pane is in place, and that we can drive its visibility with the Ribbon, it’s time to begin adding some real functionality to the add-in. In our next two installments, we will create a tree view in the task pane, which will display all the workbooks that are currently open, and the worksheets within each workbook. Later on, we will use that tree view to select the worksheet we want to compare the current active worksheet to.

I will use WPF to create our tree view, instead of a Windows Form user control. While WinForms is probably more familiar to most developers, I really wanted to use WPF in this example, because I love the flexibility it provides in user interface design, and because this is where the future of UI design is at. I can’t do a full tutorial on WPF here; I’ll try my best to explain what is going on and provide pointers, but if you haven’t seen xaml before, you will probably find some parts confusing – I hope the result will be interesting enough to motivate further exploration!

For the Windows Forms fans, Dennis Wallentin has an excellent tutorial on how to populate a WinForms tree view, for a very similar problem; I encourage you to check it out.

More...