Custom XML Support in Syncfusion Excel Library

14 Oct 20245 minutes to read

When you embed XML data in a document, the data is named as custom XML part, which is used to store arbitrary XML data in the workbook.

Essential XlsIO supports the following functionalities with Custom XML:

• Adding CustomXmlPart to workbook
• Reading CustomXmlPart from workbook

Add Custom XML

Adding Custom XML part to workbook is achieved by using the Add method of ICustomXmlPartCollection interface.

The following code snippet illustrates on how to add a Custom XML part.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Adding CustomXmlData to Workbook
	ICustomXmlPart customXmlPart = workbook.CustomXmlparts.Add("SD10003"); 

	//Add XmlData to CustomXmlPart
	byte[] xmlData = File.ReadAllBytes(Path.GetFullPath(@"Data/InputTemplate.xml"));
	customXmlPart.Data = xmlData;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/CreateCustomXML.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();

	//Open default JSON
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding CustomXmlData to Workbook
  ICustomXmlPart customXmlPart = workbook.CustomXmlparts.Add("SD10003");

  //Add XmlData to CustomXmlPart
  byte[] xmlData = File.ReadAllBytes("Test.xml");
  customXmlPart.Data = xmlData;

  workbook.SaveAs("CustomXml.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Adding CustomXmlData to Workbook
  Dim customXmlPart As ICustomXmlPart = workbook.CustomXmlparts.Add("SD10003")

  'Add XmlData to CustomXmlPart
  Dim xmlData() As Byte = File.ReadAllBytes("Test.xml")
  customXmlPart.Data = xmlData

  workbook.SaveAs("CustomXml.xlsx")
End Using

A complete working example to add custom XML in C# is present on this GitHub page.

Read Custom XML

Reading Custom XML part from workbook is achieved by using the GetById method of ICustomXmlPartCollection interface. The following code snippet illustrates on how to read Custom XML parts from workbook.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
	IWorksheet sheet = workbook.Worksheets[0];

	//Access CustomXmlPart from Workbook
	ICustomXmlPart customXmlPart = workbook.CustomXmlparts.GetById("SD10003");

	//Access XmlData from CustomXmlPart
	byte[] xmlData = customXmlPart.Data;

	System.Text.Encoding.Default.GetString(xmlData);

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/ReadXml.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Open("CustomXml.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Access CustomXmlPart from Workbook
  ICustomXmlPart customXmlPart = workbook.CustomXmlparts.GetById("SD10003");

  //Access XmlData from CustomXmlPart
  byte[] xmlData = customXmlPart.Data;
  System.Text.Encoding.Default.GetString(xmlData);

  workbook.SaveAs("CustomXml.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Open("CustomXml.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Access CustomXmlPart from Workbook
  Dim customXmlPart As ICustomXmlPart = workbook.CustomXmlparts.GetById("SD10003")

  'Access XmlData from CustomXmlPart
  Dim xmlData As Byte() = customXmlPart.Data
  System.Text.Encoding.Default.GetString(xmlData)

  workbook.SaveAs("CustomXml.xlsx")
End Using

A complete working example to read custom XML in C# is present on this GitHub page.

NOTE

Custom XML cannot be modified when the file is saved in Excel 97-2003 (*.xls) format.
Custom XML can be created and modified when the file is saved in Excel 2007 and later versions (*.xlsx).