locked
getting data from an excel chart RRS feed

  • Question

  • I am trying to get some form of data from a chart (to redraw it using another program*) - i.e. the source data or the plotted points on the chart.


    so far I have

    Excel.SeriesCollection sc= (Excel.SeriesCollection) ExcelApp.ActiveChart.SeriesCollection(Type.Missing);

    for (int i=1; i<= sc.Count; i++)                
    {
          Excel.Series series = (Excel.Series) sc.Item(i);
           //what on earth do I do here?
    }

     


    I just can't find any documentation or examples involving the series collection and c#.  Plenty of VBA code (that uses a sc.Item(i).Points.count attribute [inaccessible in c# as Points is a method]) so I am stumped.

    Like I said, I am just trying to read off some source data for a chart.

    Can anyone help?!
    Tuesday, November 1, 2005 6:54 PM

Answers

  • This issue is specific to Excel charts and not to VSTO. That is, you'd be faced with the same restrictions if you were using a C++ Winforms app to automate Excel charts, or using VBA.

    As you've discovered, the Excel Chart OM exposes a SeriesCollection which is a collection of Series objects. The Series object does expose Values and XValues properties, but both of these are variant arrays, and there is no direct way to get an Excel Range object from a Series object.

    The sensible approach to getting at chart data is to get to the original source of the data that the chart itself is consuming. If you can't do that, then an alternative approach is to parse the SERIES formula. Each data series in a chart has a SERIES formula that looks something like this:

    =SERIES([Foo.xls]Sheet1!$A$3,[Foo.xls]Sheet1!$B$1:$E$2,[Foo.xls]Sheet1!$B$3:$E$3

    It's a bit of a hack, but you could parse this string to extract the relevant cell ranges as strings. Then uses those range strings to get Excel Range objects. From there you can get the Range values, like so:


    Excel.SeriesCollection sc = (Excel.SeriesCollection)this.Chart_1.SeriesCollection(missing);

    StringBuilder builder = new StringBuilder();

    for (int i = 1; i <= sc.Count; i++)

    {

    string seriesFormula = sc.Item(i).Formula;

    int firstComma = seriesFormula.IndexOf(',');

    int secondComma = seriesFormula.IndexOf(',', firstComma + 1);

    int thirdComma = seriesFormula.IndexOf(',', secondComma + 1);

    string yValues = seriesFormula.Substring(secondComma + 1, thirdComma - secondComma - 1);

    Excel.Range yRange = this.Range[yValues, missing];

    for (int j = 1; j <= yRange.Cells.Count; j++)

    {

    Excel.Range cell = (Excel.Range)yRange.Cells[j, missing];

    builder.Append(cell.Value2.ToString());

    if (j < yRange.Cells.Count)

    {

    builder.Append(',');

    }

    }

    if (i < sc.Count)

    {

    builder.Append(Environment.NewLine);

    }

    }

    MessageBox.Show(builder.ToString());

    Thursday, November 24, 2005 9:42 PM