Office 2007 SP2 has added a major inclusion to the Object Models of Word and PowerPoint, i.e., Object Model for Charts. This new object model (OM) provides office application programmers flexibility to manipulate charts programmatically in Word and PowerPoint.
The new OM in Word and PowerPoint is similar to the one that existed for Excel prior to Office SP2. Chart objects can be accessed using the Shape.Chart property. Every Chart object contains a ChartData property which refers to the Excel worksheet which holds the data for the chart. Once you get hold of the worksheet which holds a chart’s data, you can use Excel’s object model to manipulate that data.
In this post, we will try to add charts to Word document using this newly added object model. But, before we dive into the coding part, make sure that Service Pack 2 for Microsoft Office 2007 is installed on your machine. Also, I will be using Visual Studio 2008; however, the steps mentioned here should also work for Visual Studio 2005. In case you face any difficulty, feel free to ask me.
First of all create a new Windows Forms application in your favorite .Net programming language (i.e. C# or VB.Net). Add a new button to the form, and double click it to add the code in the Click event handler’s for it.
Next step is to add references to the required assemblies for enabling .Net code automate Word and PowerPoint. For this, right click on the References folder in your Solution Explorer (if you have created a VB.Net project, you may have to click Show All Files for making the References folder visible). Now select the COM tab in the Add reference dialog box that got opened, and search for following entries:
- Microsoft Excel 12.0 Object Library
- Microsoft PowerPoint 12.0 Object Library
- Microsoft Word 12.0 Object Library
Now, you’ll be asking why we need to select these entries from the COM tab rather than the .Net tab, as we have the Primary Interop Assemblies installed on our machines, and why in the world, do we need Excel object library for?
Well for your first question, as you know Visual Studio 2008 uses its own set of Office Primary Interop Assemblies (PIAs) when you compile and link your projects and not the ones that are installed in your system’s Global Assembly Cache (GAC), selecting assemblies from the COM tab (rather than the .Net tab) ensures that Visual studio will not compile/link your project with its own set of PIAs, but will generate ones from the corresponding COM type libraries. If we do not follow this step, Visual Studio will try to link our code with its own copy of PIAs, which do not contain declarations of the new Office SP2 object model.
And, for your second question, the Chart object model in SP2 has added a new property to the Chart object called ChartData, which (as you might have guessed) represents the data of a chart. This ChartData has a property named Workbook which corresponds to the Excel Workbook containing the data of your chart. Therefore, we need the Excel object library to be able to interact with this workbook programmatically.
Now, let’s first write the code require to insert a chart, set its data, and set some of its visible properties. We will discuss about the new classes/properties of the new object model later.
Below is an example, which shows how to insert a chart to a Word document, accesses chart’s underlying data’s workbook, modify that data and finally sets some of its other properties. (Line numbers mentioned are just for indicative purpose.)
1: Word.Application word = null;
2: word = new Word.Application();
3: word.Visible = true;
4: Word.Document doc = word.Documents.Add(ref missing, ref missing, ref missing, ref missing);
5: Word.Chart wdChart = doc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn , ref missing).Chart;
6: Word.ChartData chartData = wdChart.ChartData;
7: Excel.Workbook dataWorkbook = (Excel.Workbook)chartData.Workbook;
8: Excel.Worksheet dataSheet = (Excel.Worksheet)dataWorkbook.Worksheets;
9: Excel.Range tRange = dataSheet.Cells.get_Range(“A1”, “B5”);
10: Excel.ListObject tbl1 = dataSheet.ListObjects[“Table1”];
12: ((Excel.Range)dataSheet.Cells.get_Range(“A2”, missing)).FormulaR1C1 = “Bikes”;
13: ((Excel.Range)dataSheet.Cells.get_Range(“A3”, missing)).FormulaR1C1 = “Accessories”;
14: ((Excel.Range)dataSheet.Cells.get_Range(“A4”, missing)).FormulaR1C1 = “Repairs”;
15: ((Excel.Range)dataSheet.Cells.get_Range(“A5”, missing)).FormulaR1C1 = “Clothing”;
16: ((Excel.Range)dataSheet.Cells.get_Range(“B2”, missing)).FormulaR1C1 = “1000”;
17: ((Excel.Range)dataSheet.Cells.get_Range(“B3”, missing)).FormulaR1C1 = “2500”;
18: ((Excel.Range)dataSheet.Cells.get_Range(“B4”, missing)).FormulaR1C1 = “4000”;
19: ((Excel.Range)dataSheet.Cells.get_Range(“B5”, missing)).FormulaR1C1 = “3000”;
20: wdChart.ChartTitle.Font.Italic = true;
21: wdChart.ChartTitle.Font.Size = 18;
22: wdChart.ChartTitle.Font.Color = Color.Black.ToArgb();
23: wdChart.ChartTitle.Text = “2007 Sales”;
24: wdChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
25: wdChart.ChartTitle.Format.Line.ForeColor.RGB = Color.Black.ToArgb();
26: wdChart.ApplyDataLabels(Word.XlDataLabelsType.xlDataLabelsShowLabel, missing, missing, missing, missing, missing, missing, missing, missing, missing);
As you might have guessed, the main class here is the Chart class. We can get an object of Chart class by accessing the Shape.Chart property. The call to Document.InlineShapes.AddChart() method returns a Shape object and we have accessed its Chart object in line no 5.
The next object in question is the ChartData object. We require this object to access the underlying workbook containing all the data of our chart, and we have accessed it using ChartData.Workbook property.
After getting the Workbook object of the ChartData, the next task is just using object model of Excel to insert/update data in its cells (lines 12 to 19).
Once we are finished with filling the data in the Excel workbook, we set some of the visible properties of chart like, Font, Text and Style of its Title; enable display of labels on the Chart (which are the bars in our example). After doing all this, the last task is to quit the Excel application (which automatically got created when we accessed the ChartData.Workbook object).
Here is how the final chart looks like after running the above code: