Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
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 9. January 2011 17:11

As 2010 just closed, It’s time for a quick retrospective, looking at what went according to plan, and what could be improved upon in the year ahead.

  • The boat is still afloat, but, not surprisingly, this year has been a bit rougher than the previous ones financially. There isn’t much I can do regarding the economic downturn, but this was a reminder that diversification, aka “don’t put all your eggs in one basket”, is always a good idea. I haven’t quite gotten to my 2010 resolution of getting into products – in 2011, I will aim modestly for a revenue of at least $1 coming from product sales. It’s the first step that is difficult.
  • I spent some time this year learning F#; after 6 years or so writing exclusively C# code, this has been a great experience, and I am now sold on the Pragmatic Programmer advice to learn one language a year (still need to read that book). My F# skills are still mediocre at best, but it was a great introduction to functional concepts, and if anything, it had a direct impact in the quality of my C# code. Next in line: Python. Dynamic languages are another terra incognita for me, it’s time to give it a shot. The .NET community seems to be raving about Ruby these days, so why Python? Probably my contrarian streak – and also the fact that the Ruby community seems very focused on web development, which I am only moderately into, whereas Python and math often appear together.
  • I committed to write a post every week for 2010, and pretty much did it. While this isn’t always easy, I do enjoy the exercise of clarifying my thoughts and writing them down, and then think about the follow-up comments and questions. Keep them coming, you guys are great! Besides, I have a terrible memory, and this turns out to be a very useful repository of tricks and code snippets I can go back to later. Regularity definitely paid off in terms of traffic: my blog receives now over 2000 visits a week, from about 500 early in the beginning of the year. For the first time, it even brought me a client – it’s nice when a labor of love also pays off economically. Another lesson learnt was that website downtime is a killer: I have run into some issues with my web host during the last quarter, and at the same time, the nice pattern of weekly visits started to behave erratically, trending downwards. It could be that my blog content dried up, but I can’t help thinking that my hosting issues are to blame. Sorry for anyone who has been inconvenienced, and I’ll definitely keep a closer look in 2011!
  • I wrote quite a bit on VSTO, and became a Microsoft MVP for VSTO – definitely a highpoint of the year. I was honestly a bit surprised at the traffic I received around VSTO; nothing earth shattering, but it seems there is a demand for guidance on the general topic of Office automation and .NET. I’ll keep at it this year – and if you have specific requests, let me know in the comments and I’ll do my best to deliver.
  • I wanted to improve my system architecture skills in 2010, and while I have learnt quite a bit, I am still nowhere close to where I want to be, so that will remain an area of focus for 2011. The details remain fuzzy (one thing I still enjoy about .NET is that there is always something new to learn), but I still want to dig into Azure, and get better with data.
  • It’s been a very fun year with the San Francisco .NET user group. I really wanted to keep quality speakers coming, and at the same time make it a more friendly place of discussion and exchange. Lighting talks, Nerd Dinners, drinks – we’ll have more on these in 2011. It takes a surprising amount of work to keep things running, but it’s been all worth it. Special thanks to Bruno Terkaly, Microsoft Evangelist extraordinaire, who has been incredibly supportive of the user group, and to all the volunteers and members who stepped up to help all year long (you know who you are!). I am really looking forward to 2011.
  • I have been obsessing quite a bit about how to have the best of both worlds – a team of people with whom to discuss, from whom you expect ideas that are different from yours which are worth listening to, and independence. I still haven’t figured that one out; so far the only ways I see are getting a partner in Clear Lines, and/or setting up a collective/Guild of independent developers, something I like a lot on paper, but which would take a great amount of energy.

That’s it - I wish you all an amazing year 2011, packed with fun, success, and dreams fulfilled!

Comments

Comment RSS