Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
14. January 2012 14:16

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!

3/20/2012 8:53:20 AM #

I'm new to VSTO and am trying to understand how to add a chart to a workbook, using visual studio 2010 and Excel 2007.
If I add the first section of code which create a chart I get a error "Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Tools.Excel.Chart'." when reaching the line,

Any idea  on a solution?

2/6/2013 3:07:36 PM #

Plot functions from F# to Excel

Plot functions from F# to Excel

11/19/2014 9:14:08 AM #

All the different styles of Chaco adventure sandals, flips, shoes and boots will give you miles and miles of comfort. The main market for the Go Pro is of course the extreme sport set but using it as a kind of regular video camera I have found that it makes a solid device for everyday use. It\'s just like to carry water with a jar or a cup.

my webpage  gopro hero 4 release date 2014 - www.twinklestarperformers.Com.au/...mRetrieve.aspx

5/11/2016 3:58:06 AM #

Pingback from earthweek1.xyz

How To Create A 3d Line Chart In Excel 2010 | Earthweek1

9/9/2016 11:27:08 PM #

Pingback from accelerando3.accelerando.xyz

How To Create A Regression Line In Excel 2008 | Accelerando3

12/19/2016 10:23:40 PM #

Pingback from steroidsforsale.biz

anabolics online

12/8/2017 6:00:39 AM #

Pingback from cathobbyist.com

chaco 3d simulation on apple – Chaco Canyon Facts

12/16/2017 7:55:17 AM #

Pingback from cathobbyist.com

chaco 3d simulation for apple – Chaco Canyon Facts

• Comment
• Preview

#### Need help with F#?

The premier team for
F# training & consulting.