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

-
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();  
     }

Comments

Post a Comment

Feedback - positive or negative is welcome.

Popular posts from this blog

How to prepare your LOB app for Intune?

-
Image
One of the aspects I struggled with Intune is to wrap an LOB app. In the first cut, it feels like just wrapping the app is enough. But, you need to understand some subtle architectural aspects to get your wrapping right.  App features To get started, let's start with app features.  App uses Azure AD authentication App is a hybrid and uses Cordova (Supports iOS and Android) App communicates with Sharpoint online (via REST) and set up as a Azure AD native application. Thus, conditional access is involved here.  "Send logs via email" - this feature is important as wrapping the application will restrict the email/data sharing capabilities based on the policy  Intune Way You can wrap the application in two ways Using Intune Wrapping Tool Use the Cordova plugin Please visit the link for more details  https://docs.microsoft.com/en-us/intune/deploy-use/decide-how-to-prepare-apps-for-mobile-application-management-with-microsoft-intune Intune wrappi
Read more

Information Architecture - Setup your term store to scale

-
In this post I am going to put together a list of best practices to consider when setting up a term store in Sharepoint Online. As you may already know term store is collection of term groups. And each term group consists of number term sets. Each term set contains a list/hierarchy of terms. Team group gives the flexibility in terms of access. You can assign permissions to users on their term group, rather than giving them access to the entire store.  Always maintain a corporate/general term group which contains common term sets across organization.  Every group/business unit should have a term group. Pin the terms from corporate term group as required into Business unit term groups.  For example, you plan to maintain list of document types in common term group. Thus, there is a term set created. Marketing department want to use this term set. But, they want to add a few more terms. In this case, you should create another term set in Marketing department term group and pin the te
Read more

Generate token signing .CER from ADFS Federation Metadata XML

-
Image
While workging on Force.com SSO, our ADFS team has provided me with federation metadata xml only. As per this link , you also need a token-signing certificate from provider to complete the setup and provide the Force.com XML file to ADFS. Now, the question is how to generate .pem/.cer file out of FederationMetadata.xml file.  Edit FederationMetadata.xml file, and search for  <KeyDescriptor use="signing">. You should find more than one entry. Pick any one of them.      2.  Pick the X500Certificate value and save the text as .der file     3.  openssl x509 -in <(base64 --decode FILE_FROM_STEP2.der) -inform DER -out OUTPUT.pem Use <OUTPUT>.per as Identity Provider Certificate.  Reference:  https://ask.auth0.com/t/how-to-convert-saml-federationmetadata-xml-key-to-pem-or-cer/24
Read more