XML is often used for data interchange between different systems, while Excel is a widely recognized format for data analysis and reporting. By converting XML data to Excel, you can leverage Excel's powerful features to analyze and visualize the data more effectively. This conversion process is essential in various industries, including finance, healthcare, and e-commerce.
In this article, you will learn how to convert XML to Excel and PDF in C# using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Understanding XML Structure: Elements, Attributes, and Data
Before converting XML to Excel, it's crucial to understand the structure of XML files. XML is a markup language that uses tags to define elements, attributes, and data. Here’s a breakdown of these components:
- Elements: These are the building blocks of XML. They are defined by start and end tags and can contain data or other elements.
<person>
<name>John Doe</name>
<age>30</age>
</person>
- Attributes: These provide additional information about elements. They are specified within the start tag of an element.
<person id="1">
<name>John Doe</name>
<age>30</age>
</person>
- Data: This is the content enclosed within the start and end tags of an element.
Understanding these components will help you map XML data to Excel effectively.
Convert XML to Excel in C#
In .NET, you can use the System.Xml.Linq namespace, which provides classes for working with XML files. The primary class used is XDocument, which allows you to load, navigate, and manipulate XML documents effortlessly.
Here's an example:
- C#
using System;
using System.Xml.Linq;
class Program
{
static void Main()
{
// Load the XML file
XDocument doc = XDocument.Load("data.xml");
XElement root = doc.Root;
// Iterate through elements
foreach (XElement person in root.Elements("person"))
{
string name = person.Element("name")?.Value;
string age = person.Element("age")?.Value;
// Output the name and age
Console.WriteLine($"Name: {name}, Age: {age}");
}
}
}
After parsing the XML data, the next step is to map it to an Excel worksheet. You can use Spire.XLS for .NET to create a new workbook, input data into specific cells, and apply various styles and formatting options. These include auto-fitting column widths, adjusting text alignment, and making the header bold.
To convert XML to Excel in C#, follow these steps:
- Utilize the System.Xml.Linq library to extract data from the XML file.
- Create a Workbook object.
- Add a worksheet using the Workbook.Worksheets.Add() method.
- Write the extracted data into the worksheet cells using the Worksheet.SetValue() method.
- Apply styles and formatting to enhance the appearance of the worksheet.
- Save the workbook to an Excel file using the Workbook.SaveToFile() method.
The following code demonstrates an efficient and advanced method for reading data from XML and importing it into an Excel file.
- C#
using Spire.Xls;
using System.Xml.Linq;
namespace ConvertXmlToExcel
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Remove default worksheets
workbook.Worksheets.Clear();
// Add a worksheet and name it
Worksheet worksheet = workbook.Worksheets.Add("Books");
// Load an XML file
XDocument xmlDoc = XDocument.Load(@"C:\Users\Administrator\Desktop\Books.xml");
XElement xmlRoot = xmlDoc.Root;
// Get the first "book" element
XElement firstBook = xmlRoot.Element("book");
// Extract header information and convert it into a list
var headers = firstBook.Elements().ToList();
// Write header to Excel
for (int colIndex = 0; colIndex < headers.Count; colIndex++)
{
string headerText = headers[colIndex].Name.LocalName;
worksheet.SetValue(1, colIndex + 1, headerText);
}
// Write other data to Excel by iterating over each book element and each data node within it
int rowIndex = 2;
foreach (XElement book in xmlRoot.Elements("book"))
{
var dataNodes = book.Elements().ToList();
for (int colIndex = 0; colIndex < dataNodes.Count; colIndex++)
{
string value = dataNodes[colIndex].Value;
worksheet.SetValue(rowIndex, colIndex + 1, value);
}
rowIndex++;
}
// Set column width
worksheet.AllocatedRange.AutoFitColumns();
// Set alignment
worksheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Left;
// Set font style
worksheet.Range["A1:F1"].Style.Font.IsBold = true;
// Save the workbook to an Excel file
workbook.SaveToFile("output/XmlToExcel.xlsx");
// Dispose resources
workbook.Dispose();
}
}
}
Convert XML to PDF in C#
The previous example effectively imports data from an XML file into an Excel worksheet. This worksheet can subsequently be converted to a PDF file using the Worksheet.SaveToPdf() method. To ensure a well-structured PDF, you may want to adjust page layout settings, such as margins and the preservation of gridlines, during the conversion process.
Here are the steps to convert XML to PDF using C#:
- Use the System.Xml.Linq library to retrieve data from the XML file.
- Create a Workbook object.
- Add a worksheet with the Workbook.Worksheets.Add() method.
- Populate the worksheet cells with data extracted from the XML file using the Worksheet.SetValue() method.
- Apply styles and formatting to improve the worksheet's appearance.
- Configure page settings using properties from the PageSetup object, accessible via Worksheet.PageSetup.
- Save the worksheet as a PDF file using the Worksheet.SaveToPdf() method.
The following code snippet illustrates how to import data from XML into a worksheet and then save that worksheet as a PDF file.
- C#
using Spire.Xls;
using Spire.Xls.Core;
using System.Xml.Linq;
namespace ConvertXmlToPdf
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Remove default worksheets
workbook.Worksheets.Clear();
// Add a worksheet and name it
Worksheet worksheet = workbook.Worksheets.Add("Books");
// Load an XML file
XDocument xmlDoc = XDocument.Load(@"C:\Users\Administrator\Desktop\Books.xml");
XElement xmlRoot = xmlDoc.Root;
// Get the first "book" element
XElement firstBook = xmlRoot.Element("book");
// Extract header information and convert it into a list
var headers = firstBook.Elements().ToList();
// Write header to Excel
for (int colIndex = 0; colIndex < headers.Count; colIndex++)
{
string headerText = headers[colIndex].Name.LocalName;
worksheet.SetValue(1, colIndex + 1, headerText);
}
// Write other data to Excel by iterating over each book element and each data node within it
int rowIndex = 2;
foreach (XElement book in xmlRoot.Elements("book"))
{
var dataNodes = book.Elements().ToList();
for (int colIndex = 0; colIndex < dataNodes.Count; colIndex++)
{
string value = dataNodes[colIndex].Value;
worksheet.SetValue(rowIndex, colIndex + 1, value);
}
rowIndex++;
}
// Set column width
worksheet.AllocatedRange.AutoFitColumns();
// Set alignment
worksheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Left;
// Set font style
worksheet.Range["A1:F1"].Style.Font.IsBold = true;
// Fit worksheet on one page
workbook.ConverterSetting.SheetFitToPage = true;
// Get the PageSetup object
PageSetup pageSetup = worksheet.PageSetup;
// Set page margins
pageSetup.TopMargin = 0.3;
pageSetup.BottomMargin = 0.3;
pageSetup.LeftMargin = 0.3;
pageSetup.RightMargin = 0.3;
// Preserve gridlines
pageSetup.IsPrintGridlines = true;
// Save the worksheet to a PDF file
worksheet.SaveToPdf("output/XmlToPdf.pdf");
// Dispose resources
workbook.Dispose();
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
