How to create a chart using Open XML SDK in Word 2010

October 30, 2013

Creating a Chart in Open involves two steps, as the data is stored at two locations. 
  1. Chart Cache
  2. Embedded Package - This is nothing but an Excel Sheet
Thus, populating the data in Cache is not enough. We should also create corresponding Excel Sheet. Below is the sample code. The code is verbose as I was using Open XML productivity tool to arrive at this. Full source code can be found at https://github.com/pradeepkumargali/GenerateWord-OpenXML 

Main Class:

   ChartPart chartPart2 = mainDocumentPart1.AddNewPart<ChartPart>("rId7");  
       GenerateChartPart2Content(chartPart2);  
       //Used to generate a dummy excel sheet out of a predefined blob - Look at "private string embeddedPackagePart2Data"  
       EmbeddedPackagePart embeddedPackagePart2 = chartPart2.AddNewPart<EmbeddedPackagePart>("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "rId1");  
       GenerateEmbeddedPackagePart2Content(embeddedPackagePart2);  
       //Alter the default embeddedPackage(Excel Sheet)  
       using (Stream str = embeddedPackagePart2.GetStream())  
       using (MemoryStream ms = new MemoryStream())  
       {  
         CopyStream(str, ms);  
         using (SpreadsheetDocument spreadsheetDoc =  
           SpreadsheetDocument.Open(ms, true))  
         {  
           // Update data in spreadsheet  
           // Find first worksheet  
           ss.Sheet ws = (ss.Sheet)spreadsheetDoc.WorkbookPart  
             .Workbook.Sheets  
             .FirstOrDefault();  
           string sheetId = ws.Id;  
           WorksheetPart wsp = (WorksheetPart)spreadsheetDoc  
             .WorkbookPart  
             .Parts  
             .Where(pt => pt.RelationshipId == sheetId)  
             .FirstOrDefault()  
             .OpenXmlPart;  
           ss.SheetData sd = wsp  
             .Worksheet  
             .Elements<ss.SheetData>()  
             .FirstOrDefault();  
           foreach (ss.Row tsd in sd.Elements<ss.Row>())  
           {  
             if (tsd.Elements<ss.Cell>().Count()> 1)  
             {  
               ss.Cell cell1 = tsd.Elements<ss.Cell>()  
                 .ElementAt(1);  
               if (cell1 != null)  
               {  
                 ss.CellValue cell1value = cell1.Elements<ss.CellValue>().FirstOrDefault();  
                 System.Console.WriteLine(cell1value.InnerText+cell1value.Text);  
                 if (cell1value != null && !cell1value.InnerText.Equals("0"))  
                 {  
                   //Data Goes Here  
                   cell1value.Text = "25";  
                 }  
               }  
             }  
           }  
         }   
         // Write the modified memory stream back  
         // into the embedded package part.  
         System.Console.WriteLine(Getbase64String(ms));  
         if(!embeddedPackagePart2Data.Equals(Getbase64String(ms)))  
           embeddedPackagePart2Data = Getbase64String(ms);                   
         ms.Close();  
       }  
       //Regenerate EmbeddedPackage  
       GenerateEmbeddedPackagePart2Content(embeddedPackagePart2);  


Helper Functions:


 // Generates content of chartPart2.  
     private void GenerateChartPart2Content(ChartPart chartPart2)  
     {  
       C.ChartSpace chartSpace2 = new C.ChartSpace();  
       chartSpace2.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");  
       chartSpace2.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");  
       chartSpace2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");  
       C.Date1904 date19042 = new C.Date1904() { Val = false };  
       C.EditingLanguage editingLanguage2 = new C.EditingLanguage() { Val = "en-US" };  
       C.RoundedCorners roundedCorners2 = new C.RoundedCorners() { Val = false };  
       AlternateContent alternateContent2 = new AlternateContent();  
       alternateContent2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");  
       AlternateContentChoice alternateContentChoice2 = new AlternateContentChoice() { Requires = "c14" };  
       alternateContentChoice2.AddNamespaceDeclaration("c14", "http://schemas.microsoft.com/office/drawing/2007/8/2/chart");  
       C14.Style style3 = new C14.Style() { Val = 102 };  
       alternateContentChoice2.Append(style3);  
       AlternateContentFallback alternateContentFallback2 = new AlternateContentFallback();  
       C.Style style4 = new C.Style() { Val = 2 };  
       alternateContentFallback2.Append(style4);  
       alternateContent2.Append(alternateContentChoice2);  
       alternateContent2.Append(alternateContentFallback2);  
       C.Chart chart2 = new C.Chart();  
       C.Title title1 = new C.Title();  
       C.Overlay overlay2 = new C.Overlay() { Val = false };  
       title1.Append(overlay2);  
       C.AutoTitleDeleted autoTitleDeleted2 = new C.AutoTitleDeleted() { Val = false };  
       C.PlotArea plotArea2 = new C.PlotArea();  
       C.Layout layout2 = new C.Layout();  
       C.PieChart pieChart1 = new C.PieChart();  
       C.VaryColors varyColors2 = new C.VaryColors() { Val = true };  
       C.PieChartSeries pieChartSeries1 = new C.PieChartSeries();  
       C.Index index4 = new C.Index() { Val = (UInt32Value)0U };  
       C.Order order4 = new C.Order() { Val = (UInt32Value)0U };  
       C.SeriesText seriesText4 = new C.SeriesText();  
       C.StringReference stringReference7 = new C.StringReference();  
       C.Formula formula10 = new C.Formula();  
       formula10.Text = "Sheet1!$B$1";  
       C.StringCache stringCache7 = new C.StringCache();  
       C.PointCount pointCount10 = new C.PointCount() { Val = (UInt32Value)1U };  
       C.StringPoint stringPoint16 = new C.StringPoint() { Index = (UInt32Value)0U };  
       C.NumericValue numericValue28 = new C.NumericValue();  
       numericValue28.Text = "Cases By Aging";  
       stringPoint16.Append(numericValue28);  
       stringCache7.Append(pointCount10);  
       stringCache7.Append(stringPoint16);  
       stringReference7.Append(formula10);  
       stringReference7.Append(stringCache7);  
       seriesText4.Append(stringReference7);  
       C.CategoryAxisData categoryAxisData4 = new C.CategoryAxisData();  
       C.StringReference stringReference8 = new C.StringReference();  
       C.Formula formula11 = new C.Formula();  
       formula11.Text = "Sheet1!$A$2:$A$5";  
       C.StringCache stringCache8 = new C.StringCache();  
       C.PointCount pointCount11 = new C.PointCount() { Val = (UInt32Value)4U };  
       C.StringPoint stringPoint17 = new C.StringPoint() { Index = (UInt32Value)0U };  
       C.NumericValue numericValue29 = new C.NumericValue();  
       numericValue29.Text = "1st Qtr";  
       stringPoint17.Append(numericValue29);  
       C.StringPoint stringPoint18 = new C.StringPoint() { Index = (UInt32Value)1U };  
       C.NumericValue numericValue30 = new C.NumericValue();  
       numericValue30.Text = "2nd Qtr";  
       stringPoint18.Append(numericValue30);  
       C.StringPoint stringPoint19 = new C.StringPoint() { Index = (UInt32Value)2U };  
       C.NumericValue numericValue31 = new C.NumericValue();  
       numericValue31.Text = "3rd Qtr";  
       stringPoint19.Append(numericValue31);  
       C.StringPoint stringPoint20 = new C.StringPoint() { Index = (UInt32Value)3U };  
       C.NumericValue numericValue32 = new C.NumericValue();  
       numericValue32.Text = "4th Qtr";  
       stringPoint20.Append(numericValue32);  
       stringCache8.Append(pointCount11);  
       stringCache8.Append(stringPoint17);  
       stringCache8.Append(stringPoint18);  
       stringCache8.Append(stringPoint19);  
       stringCache8.Append(stringPoint20);  
       stringReference8.Append(formula11);  
       stringReference8.Append(stringCache8);  
       categoryAxisData4.Append(stringReference8);  
       C.Values values4 = new C.Values();  
       C.NumberReference numberReference4 = new C.NumberReference();  
       C.Formula formula12 = new C.Formula();  
       formula12.Text = "Sheet1!$B$2:$B$5";  
       C.NumberingCache numberingCache4 = new C.NumberingCache();  
       C.FormatCode formatCode4 = new C.FormatCode();  
       formatCode4.Text = "General";  
       C.PointCount pointCount12 = new C.PointCount() { Val = (UInt32Value)4U };  
       numberingCache4.Append(formatCode4);  
       numberingCache4.Append(pointCount12);  
       for (int i = 0; i <= 3; i++)  
       {  
         C.NumericPoint numericPoint13 = new C.NumericPoint() { Index = UInt32Value.FromUInt32((uint)i) };  
         C.NumericValue numericValue33 = new C.NumericValue();  
         //numericValue33.Text = (10*(i+1)).ToString();  
         //Data Goes Here  
         numericValue33.Text = "25";  
         numericPoint13.Append(numericValue33);  
         numberingCache4.Append(numericPoint13);  
       }  
       /* C.NumericPoint numericPoint13 = new C.NumericPoint() { Index = (UInt32Value)0U };  
       C.NumericValue numericValue33 = new C.NumericValue();  
       numericValue33.Text = "99";  
       numericPoint13.Append(numericValue33);  
       C.NumericPoint numericPoint14 = new C.NumericPoint() { Index = (UInt32Value)1U };  
       C.NumericValue numericValue34 = new C.NumericValue();  
       numericValue34.Text = "3.2";  
       numericPoint14.Append(numericValue34);  
       C.NumericPoint numericPoint15 = new C.NumericPoint() { Index = (UInt32Value)2U };  
       C.NumericValue numericValue35 = new C.NumericValue();  
       numericValue35.Text = "1.4";  
       numericPoint15.Append(numericValue35);  
       C.NumericPoint numericPoint16 = new C.NumericPoint() { Index = (UInt32Value)3U };  
       C.NumericValue numericValue36 = new C.NumericValue();  
       numericValue36.Text = "1.2";  
       numericPoint16.Append(numericValue36);  
       numberingCache4.Append(formatCode4);  
       numberingCache4.Append(pointCount12);  
       numberingCache4.Append(numericPoint13);  
       numberingCache4.Append(numericPoint14);  
       numberingCache4.Append(numericPoint15);  
       numberingCache4.Append(numericPoint16); */  
       numberReference4.Append(formula12);  
       numberReference4.Append(numberingCache4);  
       values4.Append(numberReference4);  
       pieChartSeries1.Append(index4);  
       pieChartSeries1.Append(order4);  
       pieChartSeries1.Append(seriesText4);  
       pieChartSeries1.Append(categoryAxisData4);  
       pieChartSeries1.Append(values4);  
       C.DataLabels dataLabels2 = new C.DataLabels();  
       C.ShowLegendKey showLegendKey2 = new C.ShowLegendKey() { Val = false };  
       C.ShowValue showValue2 = new C.ShowValue() { Val = false };  
       C.ShowCategoryName showCategoryName2 = new C.ShowCategoryName() { Val = false };  
       C.ShowSeriesName showSeriesName2 = new C.ShowSeriesName() { Val = false };  
       C.ShowPercent showPercent2 = new C.ShowPercent() { Val = false };  
       C.ShowBubbleSize showBubbleSize2 = new C.ShowBubbleSize() { Val = false };  
       C.ShowLeaderLines showLeaderLines1 = new C.ShowLeaderLines() { Val = true };  
       dataLabels2.Append(showLegendKey2);  
       dataLabels2.Append(showValue2);  
       dataLabels2.Append(showCategoryName2);  
       dataLabels2.Append(showSeriesName2);  
       dataLabels2.Append(showPercent2);  
       dataLabels2.Append(showBubbleSize2);  
       dataLabels2.Append(showLeaderLines1);  
       C.FirstSliceAngle firstSliceAngle1 = new C.FirstSliceAngle() { Val = (UInt16Value)0U };  
       pieChart1.Append(varyColors2);  
       pieChart1.Append(pieChartSeries1);  
       pieChart1.Append(dataLabels2);  
       pieChart1.Append(firstSliceAngle1);  
       plotArea2.Append(layout2);  
       plotArea2.Append(pieChart1);  
       C.Legend legend2 = new C.Legend();  
       C.LegendPosition legendPosition2 = new C.LegendPosition() { Val = C.LegendPositionValues.Right };  
       C.Overlay overlay3 = new C.Overlay() { Val = false };  
       legend2.Append(legendPosition2);  
       legend2.Append(overlay3);  
       C.PlotVisibleOnly plotVisibleOnly2 = new C.PlotVisibleOnly() { Val = true };  
       C.DisplayBlanksAs displayBlanksAs2 = new C.DisplayBlanksAs() { Val = C.DisplayBlanksAsValues.Gap };  
       C.ShowDataLabelsOverMaximum showDataLabelsOverMaximum2 = new C.ShowDataLabelsOverMaximum() { Val = false };  
       chart2.Append(title1);  
       chart2.Append(autoTitleDeleted2);  
       chart2.Append(plotArea2);  
       chart2.Append(legend2);  
       chart2.Append(plotVisibleOnly2);  
       chart2.Append(displayBlanksAs2);  
       chart2.Append(showDataLabelsOverMaximum2);  
       C.ExternalData externalData2 = new C.ExternalData() { Id = "rId1" };  
       C.AutoUpdate autoUpdate2 = new C.AutoUpdate() { Val = false };  
       externalData2.Append(autoUpdate2);  
       chartSpace2.Append(date19042);  
       chartSpace2.Append(editingLanguage2);  
       chartSpace2.Append(roundedCorners2);  
       chartSpace2.Append(alternateContent2);  
       chartSpace2.Append(chart2);  
       chartSpace2.Append(externalData2);  
       chartPart2.ChartSpace = chartSpace2;  
     }  
     // Generates content of embeddedPackagePart2.  
     private void GenerateEmbeddedPackagePart2Content(EmbeddedPackagePart embeddedPackagePart2)  
     {  
       System.IO.Stream data = GetBinaryDataStream(embeddedPackagePart2Data);  
       embeddedPackagePart2.FeedData(data);  
       data.Close();  
     }  

You Might Also Like

0 comments

Popular Posts

Twitter