Sridhar's Blog

Embed a dynamic excel spreadsheet and chart object into web part

A customer of mine called in with this requirement of embedding a dynamic excel spreadsheet and chart object into a SharePoint (both V2 and V3) web part.  After few hours of battle, we came up with the following code sample that works like charm.

 

Following is the code that'll allow us to render a dynamic spreadsheet and chart object within a web part.

 

using System;

using System.Runtime.InteropServices;

using System.Web.UI;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Serialization;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

using Microsoft.Office.Excel.WebUI;

using Microsoft.Office.Interop.Owc11;

 

namespace BugPart

{

    [Guid("651cba9f-9c6b-46af-8beb-79104ffee875")]

    public class BugPart : System.Web.UI.WebControls.WebParts.WebPart

    {

        System.Text.StringBuilder scripter = new System.Text.StringBuilder();

        protected override void Render(HtmlTextWriter writer)

        {  

            writer.Write(spreadsheet);

            writer.Write(chart);

            writer.Write(LoadData("'1,2,3,4,5," + System.Environment.NewLine + "6,7,8,9'"));

            writer.Write(System.Environment.NewLine + "<script language='vbscript'>" + System.Environment.NewLine + "call loadData()" + System.Environment.NewLine + "</script>");

            ChartSpace objCSpace = new ChartSpaceClass();

            ChChart objChart = objCSpace.Charts.Add(0);

            objChart.HasTitle = true;

            objChart.Title.Caption = "Advanced SharePoint Charts";

            objChart.Type = ChartChartTypeEnum.chChartTypeArea3D;

            objCSpace.Charts[0].HasLegend = true;

            ChSeries series = objCSpace.Charts[0].SeriesCollection.Add(0);

            series.SetData(ChartDimensionsEnum.chDimCategories, (int)ChartSpecialDataSourcesEnum.chDataLiteral,

             "Week 1,Week 2,Week 3,Week 4,Week 5,Week 6");

            series.SetData(ChartDimensionsEnum.chDimValues, (int)ChartSpecialDataSourcesEnum.chDataLiteral, "11,25,16,14,8,20");

            writer.Write((byte[])objCSpace.GetPicture("jpg", 500, 500));

        }

 

        string spreadsheet = "<object classid='clsid:0002E559-0000-0000-C000-000000000046' id='mytest_sp' ></object>";

        string chart = "<object classid='clsid:0002E55D-0000-0000-C000-000000000046' id='mytest_cs' ></object>";

 

       public string CustomizeSpreadSheet()

        {

            System.Text.StringBuilder script = new System.Text.StringBuilder();

            script.Append(System.Environment.NewLine + "<script type='text/vbscript' language='vbscript'>");

            script.Append(System.Environment.NewLine + "Sub loadData()");

            script.Append(System.Environment.NewLine + "Dim chConstants");

            script.Append(System.Environment.NewLine + "Dim Spreadsheet1");

            script.Append(System.Environment.NewLine + "set Spreadsheet1 = document.all(\"mytest_sp\").object");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(2, 1).Value = \"Mexico\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(3, 1).Value = \"Canada\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(4, 1).Value = \"America\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(1, 2).Value = \"Domestic\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(2, 2).Value = 0.02");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(3, 2).Value = 0.05");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(4, 2).Value = 0.10");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(1, 3).Value = \"Long-Distance\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(2, 3).Value = 0.09");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(3, 3).Value = 0.82");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(4, 3).Value = 0.28");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(1, 4).Value = \"International\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(2, 4).Value = 0.42");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(3, 4).Value = 0.12");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(4, 4).Value = 0.55");

            script.Append(System.Environment.NewLine + "set ChartSpace1 = document.all(\"mytest_cs\").object");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts.Add");

            script.Append(System.Environment.NewLine + "Set chConstants = ChartSpace1.Constants");

            script.Append(System.Environment.NewLine + "ChartSpace1.DataSource = Spreadsheet1");

            script.Append(System.Environment.NewLine + "Set oSeries1 = ChartSpace1.Charts(0).SeriesCollection.Add");

            script.Append(System.Environment.NewLine + "oSeries1.Type = chConstants.chChartTypeLine3D");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection.Add");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection.Add");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, \"B1\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimCategories, chConstants.chDataBound, \"A2:A5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimValues, chConstants.chDataBound, \"B2:B5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, \"C1\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimCategories, chConstants.chDataBound, \"A2:A5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimValues, chConstants.chDataBound, \"C2:C5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, \"D1\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimCategories, chConstants.chDataBound, \"A2:A5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimValues, chConstants.chDataBound, \"D2:D5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).HasLegend = True");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).TitleHasLegend");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).Axes(chConstants.chAxisPositionLeft).NumberFormat = \"0%\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).Axes(chConstants.chAxisPositionLeft).MajorUnit = 0.2");

            script.Append(System.Environment.NewLine + "End Sub");

            script.Append(System.Environment.NewLine + System.Environment.NewLine + System.Environment.NewLine + "</script>");

            return scripter.ToString();

        }

 

        public string BindChartToSpreadSheet()

        {

            scripter.Append(System.Environment.NewLine + "<script language='javascript'>");

            scripter.Append(System.Environment.NewLine + "if(document.all.ch != null){");

            scripter.Append(System.Environment.NewLine + "document.all.ch.DataSource = document.all.sp");

            scripter.Append(System.Environment.NewLine + "document.all.ch.Charts.Add()");

            scripter.Append(System.Environment.NewLine + "document.all.ch.Charts[1].SetSpreadsheetData('Datasource!A1:E1',true)");

            scripter.Append(System.Environment.NewLine + "}" + System.Environment.NewLine + "</script>");

            return scripter.ToString();

        }

 

        public string LoadData(string payload)

        {

            System.Text.StringBuilder script = new System.Text.StringBuilder();

            script.Append(System.Environment.NewLine + "<script type='text/vbscript' language='vbscript'>Sub loadData()");

            script.Append(System.Environment.NewLine + "Dim chConstants");

            script.Append(System.Environment.NewLine + "Dim Spreadsheet1");

            script.Append(System.Environment.NewLine + "set Spreadsheet1 = document.all(\"mytest_sp\").object");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(2, 1).Value = \"Mexico\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(3, 1).Value = \"Canada\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(4, 1).Value = \"America\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(1, 2).Value = \"Domestic\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(2, 2).Value = 0.02");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(3, 2).Value = 0.05");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(4, 2).Value = 0.10");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(1, 3).Value = \"Long-Distance\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(2, 3).Value = 0.09");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(3, 3).Value = 0.82");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(4, 3).Value = 0.28");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(1, 4).Value = \"International\"");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(2, 4).Value = 0.42");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(3, 4).Value = 0.12");

            script.Append(System.Environment.NewLine + "Spreadsheet1.ActiveSheet.Cells(4, 4).Value = 0.55");

            script.Append(System.Environment.NewLine + "Dim ChartSpace1");

            script.Append(System.Environment.NewLine + "set ChartSpace1 = document.all(\"mytest_cs\").object");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts.Add");

            script.Append(System.Environment.NewLine + "Set chConstants = ChartSpace1.Constants");

            script.Append(System.Environment.NewLine + "ChartSpace1.DataSource = Spreadsheet1");

            script.Append(System.Environment.NewLine + "Set oSeries1 = ChartSpace1.Charts(0).SeriesCollection.Add");

            script.Append(System.Environment.NewLine + "oSeries1.Type = chConstants.chChartTypeLine3D");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection.Add");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection.Add");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, \"B1\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimCategories, chConstants.chDataBound, \"A2:A5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimValues, chConstants.chDataBound, \"B2:B5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, \"C1\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimCategories, chConstants.chDataBound, \"A2:A5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimValues, chConstants.chDataBound, \"C2:C5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, \"D1\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimCategories, chConstants.chDataBound, \"A2:A5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimValues, chConstants.chDataBound, \"D2:D5\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).HasLegend = True");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).Axes(chConstants.chAxisPositionLeft).NumberFormat = \"0%\"");

            script.Append(System.Environment.NewLine + "ChartSpace1.Charts(0).Axes(chConstants.chAxisPositionLeft).MajorUnit = 0.2");

            script.Append(System.Environment.NewLine + "End Sub");

            script.Append(System.Environment.NewLine + System.Environment.NewLine + System.Environment.NewLine + "</script>");

            return script.ToString();

        }

    }

}

 

As you can see, it uses the Office Interop to embed excel and the chart objects.  The messiest thing is the scripting part.  However, it pays-off in terms of quick response in the web interface.