MS Graph Datasheet, Get Columns, Rows, NumberFormat, Values in VSTO… But How ??

Recently in a project I stumbled upon the same question as to how do I obtain the above information in an MS Graph format chart, whose data behind is kept in a datasheet (which not as pretty looking, as excel is these days…)

blog10_image1.bmp

And to add flavor to the same, I had to perform the same operation in C# code which is not so friendly with the syntax associated with the COM’s (for ex. Parametric Properties).
So, to cut the long story short the code below illustrates the methodology to read the above information off the MS Graph datasheet for your required purposes.

 private void btnTest_Click(object sender, RibbonControlEventArgs e)
        {
            PowerPoint.Application pptApp = Globals.ThisAddIn.Application;
            Graph.Chart msChart = pptApp.ActiveWindow.Selection.ShapeRange[1].OLEFormat.Object as Graph.Chart;
            Graph.DataSheet dataSheet = msChart.Application.DataSheet;

            //Datasheet
            //Get the column information (like Include)
            //The defination of Columns  is only aviable as "{Get}"
            //Check wether Column 2 is included in the MS Graph or not as Series. 
            Graph.Range columnRange = dataSheet.get_Range(dataSheet.Cells[1, 2], dataSheet.Cells[1, 2]).Columns;
            System.Diagnostics.Debug.WriteLine("Column 2 of the datasheet (Include): " + columnRange.Include);

            //Get the row information (like Include) 
            Graph.Range rowRange = dataSheet.get_Range(dataSheet.Cells[10, 1], dataSheet.Cells[10, 1]).Rows;
            System.Diagnostics.Debug.WriteLine("Row 10 of the datasheet (Include): " + columnRange.Include);

            //Get the number format of the cell
            Graph.Range cellRange = dataSheet.Cells[4, 2] as Graph.Range;
            System.Diagnostics.Debug.WriteLine("Cell 4(row),2(column) of the datasheet number format: " + cellRange.NumberFormat);

            //Get the value of the cell
            System.Diagnostics.Debug.WriteLine("Cell 4(row),2(column) of the datasheet value: " + cellRange.get_Value(Type.Missing));
        }

Downloads:
Download

Leave a Reply

Your email address will not be published. Required fields are marked *

*