I am putting together a demo VSTO add-in for my talk at the Excel Developer Conference. I wanted to play with charts a bit, and given that I am working off a .NET model, I figured it would be interesting to produce charts directly from the data, bypassing the step of putting data in a worksheet altogether.

In order to do that, we simply need to create a Chart in a workbook, add a Series to the SeriesCollection of the chart, and directly set the Series Values and XValues as an array, along these lines:

var excel = this.Application;
var workbook = excel.ActiveWorkbook;
var charts = workbook.Charts;
var chart = (Chart)charts.Add();

chart.ChartType = Excel.XlChartType.xlLine;
chart.Location(XlChartLocation.xlLocationAsNewSheet, "Tab Name");

var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.NewSeries();

series.Values = new double[] {1d, 3d, 2d, 5d};
series.XValues = new string[] {"A", "B", "C", "D"};
series.Name = "Series Name";

This will create a simple Line chart in its own sheet – without any reference to a worksheet data range.

Now why would I be interested in this approach, when it’s so convenient to create a chart from data that is already in Excel?

Suppose for a moment that you are dealing with the market activity on a stock, which you can retrieve from an external data source as a collection of StockActivity .NET objects:

public class StockActivity
{
   public DateTime Day { get; set; }
   public decimal Open { get; set; }
   public decimal Close { get; set; }
}

In this case, extracting the array for the X and Y values would be a trivial matter, making it very easy to produce a chart of, say, the Close values over time:

// Create a few fake datapoints
var day1 = new StockActivity()
              {
                 Day = new DateTime(2010, 1, 1), 
                 Open = 100m, 
                 Close = 110m
              };
var day2 = new StockActivity()
              {
                 Day = new DateTime(2010, 1, 2), 
                 Open = 110m, 
                 Close = 130m
              };
var day3 = new StockActivity()
              {
                 Day = new DateTime(2010, 1, 3), 
                 Open = 130m, 
                 Close = 105m
              };
var history = new List<StockActivity>() { day1, day2, day3 };

var excel = this.Application;
var workbook = excel.ActiveWorkbook;
var charts = workbook.Charts;
var chart = (Chart)charts.Add();

chart.ChartType = Excel.XlChartType.xlLine;
chart.Location(XlChartLocation.xlLocationAsNewSheet, "Stock Chart);

var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.NewSeries();

series.Values = history.Select(it => (double)it.Close).ToArray();
series.XValues = history.Select(it => it.Day).ToArray();
series.Name = "Stock";

Using LINQ, we Select from the list the values we are interested in, and pass them into an array, ready for consumption into a chart, and boom! We are done.

If what you need to do is explore data and produce charts to figure out potentially interesting relationships, this type of approach isn’t very useful. On the other hand, if your problem is to produce on a regular basis the same set of charts, using data coming from an external data source, this is a very interesting option!

Earlier this month, on Simon Murphy’s blog, the project to organize a UK Excel Developer conference 2012 in London quickly took shape. As of now, the planned date is in the January 24 to 26 range, with a pretty cool agenda, covering most of the recent developments Excel developers should know about. I am looking forward to it!

I’ll be talking for about an hour on VSTO (Visual Studio Tools for Office). VSTO can be both awesome and painful: I plan on demoing building an Excel add-in from the ground up, illustrating some of the benefits and drawbacks/pitfalls of that approach, so that you know when it’s the right time to go that route.

If there are any topics or questions you are specifically interested in, please let me know in the comments – I’ll be happy to take requests.

As an aside, I usually talk at .NET developers events, where I need to convince the audience that developing for Excel (or Office) isn’t a terrible idea. This will be my first time with an Excel developers audience, and I expect the opposite challenge, namely why bother with C#/VB.NET and Visual Studio when VBA is free and works just fine?

Check out the Excel Conference page and Simon’s blog for updates.

Picture from the amazing series “The Kitten Covers”

When working with Excel, it is common to use small optimization tricks, like setting Excel.ScreenUpdating to false to avoid un-necessary screen refreshes, during lengthy operations – something along these lines:

public void DoJob(Worksheet  worksheet)
{
   var excel = worksheet.Application;

   var initialScreenUpdating = excel.ScreenUpdating;
   var initialCursor = excel.Cursor;

   excel.ScreenUpdating = false;
   excel.Cursor = XlMousePointer.xlWait;

   // do stuff

   excel.ScreenUpdating = initialScreenUpdating;
   excel.Cursor = initialCursor;
}

This is a good outline of what we would like to happen, but as is, this code has limitations. We would like to be certain that whenever we capture the state, the final two lines, which reset the state to what it was originally, are executed.

More...

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:

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?

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