Article By - Sheela E.N, Sonata Software Limited

This article explains the process of changing type of chart in a SpreadsheetML document programmatically using Open XML SDK API.

In this article, we will be replacing a bar chart embedded in the excel work sheet to pie chart. We will be loading the pre-existing pie chart XML file instead of generating the same programmatically.

1.      Create a C# project; add a reference to openxml dll

·         In the Add Reference dialog box, click the .NET tab.

·         Scroll to the Microsoft.Office.DocumentFormat.OpenXml option, select it, and then click OK.

2.      Import the namespace to refer to the dll

using Microsoft.Office.DocumentFormat.OpenXml.Packaging;

3.      Declare necessary namespaces

string strChrtNS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart";

4.      Open the Excel document.

SpreadsheetDocument xlDoc = SpreadsheetDocument.Open("../../BarChart.xlsx", true);

5.      Get the handler for each worksheet of a workbook

foreach (WorksheetPart wrkSh in xlDoc.WorkbookPart.WorksheetParts)

6.      For a given worksheet, search for chart object

foreach (IdPartPair var2 in part.Parts)

if (var2.OpenXmlPart.RelationshipType.ToString() == strChrtNS)

7.      If chart object is found, load the xml file having pie chart into the openxml part and save

ChartPart chartType = (ChartPart)var2.OpenXmlPart;

XmlDocument xmlDoc = new XmlDocument();

xmlDoc.Load("../../Piechart1.xml");

xmlDoc.Save(chartType.GetStream(FileMode.Create, FileAccess.Write));

8.      Close the excel handler

xlDoc.Close();