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

Comments

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

George

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,

var chart=(Chart)charts.Add();

Any idea  on a solution?

George United Kingdom | Reply

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

trackback

Plot functions from F# to Excel

Plot functions from F# to Excel

Clear Lines Blog | Reply

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

gopro hero 4 release date 2014

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

gopro hero 4 release date 2014 United States | Reply

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

pingback

Pingback from earthweek1.xyz

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

earthweek1.xyz | Reply

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

pingback

Pingback from accelerando3.accelerando.xyz

How To Create A Regression Line In Excel 2008 | Accelerando3

accelerando3.accelerando.xyz | Reply

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

pingback

Pingback from steroidsforsale.biz

anabolics online

steroidsforsale.biz | Reply

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

pingback

Pingback from cathobbyist.com

chaco 3d simulation on apple – Chaco Canyon Facts

cathobbyist.com | Reply

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

pingback

Pingback from cathobbyist.com

chaco 3d simulation for apple – Chaco Canyon Facts

cathobbyist.com | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS