DEV Community

Allen Yang
Allen Yang

Posted on

How to Convert XML Data to an Excel Spreadsheet Using C#

#csharp #dotnet #xml #excel

Write XML Data into Excel Files with C#

In today's data-driven landscape, the ability to seamlessly move information between different formats is crucial. While XML (Extensible Markup Language) excels at structuring and transmitting data between systems, its hierarchical nature can be less intuitive for human analysis or reporting compared to the familiar tabular layout of an Excel spreadsheet. Non-technical users often prefer Excel for its immediate readability, filtering capabilities, and widespread accessibility.

This common requirement—transforming structured XML data into a user-friendly Excel format—presents a perfect opportunity for automation. Manually parsing XML and painstakingly entering data into Excel is not only tedious but also prone to errors. Fortunately, C# provides powerful tools to programmatically bridge this gap, allowing developers to create robust solutions for data conversion. This tutorial will guide you through the process of writing XML data to an Excel spreadsheet using C#, enabling efficient data workflows and empowering your applications with robust export capabilities.

Understanding the Need: Bridging XML and Spreadsheets

XML's strength lies in its self-descriptive, tree-like structure, making it ideal for configuration files, web services, and data exchange between applications. However, this very structure can be a hurdle when the data needs to be consumed by a wider audience. Imagine a business analyst trying to make sense of a complex XML file detailing product inventory or customer orders; they would likely prefer a spreadsheet where each row represents an item and columns represent its attributes.

Programmatic conversion offers significant advantages over manual methods:

  • Efficiency: Automate the export of large datasets, saving countless hours.
  • Accuracy: Eliminate human error associated with manual data entry.
  • Consistency: Ensure data is always presented in a standardized format.
  • Integration: Embed data export capabilities directly into your applications.

By leveraging C#, we can parse the XML, extract the relevant information, and then structure it perfectly for an Excel spreadsheet, making the data accessible and actionable for everyone.

Setting Up Your C# Environment for Excel Export

To begin, you'll need a C# development environment. Visual Studio is highly recommended. We'll start by creating a new console application project, which is simple and perfect for demonstrating this functionality.

Create a New Project: Open Visual Studio, choose "Create a new project," select "Console Application" (for .NET Core or .NET Framework, either will work), give it a name (e.g., XmlToExcelExporter), and click "Create."

Install the Excel Library: For this tutorial, we will use Spire.XLS for .NET, a robust and efficient library for working with Excel files programmatically. To add it to your project, use the NuGet Package Manager:

  • Via NuGet Package Manager Console: Go to Tools > NuGet Package Manager > Package Manager Console. In the console, type the following command and press Enter: Install-Package Spire.XLS.
  • Via NuGet Package Manager UI: Right-click on your project in the Solution Explorer, select "Manage NuGet Packages...", go to the "Browse" tab, search for Spire.XLS, and click "Install."

Once installed, your project is ready to start parsing XML and generating Excel files.

Parsing XML Data in C

Before we can write data to Excel, we need to extract it from our XML source. Let's consider a simple XML structure representing a list of products.

Example XML Data (products.xml):

<Products>
  <Product id="101">
    <Name>Laptop</Name>
    <Category>Electronics</Category>
    <Price>1200.00</Price>
    <Availability>In Stock</Availability>
  </Product>
  <Product id="102">
    <Name>Mouse</Name>
    <Category>Electronics</Category>
    <Price>25.50</Price>
    <Availability>Out of Stock</Availability>
  </Product>
  <Product id="103">
    <Name>Keyboard</Name>
    <Category>Peripherals</Category>
    <Price>75.00</Price>
    <Availability>In Stock</Availability>
  </Product>
</Products>
Enter fullscreen mode Exit fullscreen mode

We'll use XDocument from the System.Xml.Linq namespace to parse this data. It provides a more modern and LINQ-friendly way to work with XML. First, let's define a simple class to hold our product data:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public decimal Price { get; set; }
    public string Availability { get; set; }
}

public class XmlParser
{
    public static List<Product> ParseProductsXml(string xmlContent)
    {
        List<Product> products = new List<Product>();
        try
        {
            XDocument doc = XDocument.Parse(xmlContent);
            products = doc.Descendants("Product")
                          .Select(p => new Product
                          {
                              Id = (int)p.Attribute("id"),
                              Name = (string)p.Element("Name"),
                              Category = (string)p.Element("Category"),
                              Price = (decimal)p.Element("Price"),
                              Availability = (string)p.Element("Availability")
                          }).ToList();
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error parsing XML: {ex.Message}");
        }
        return products;
    }
}
Enter fullscreen mode Exit fullscreen mode

In your Program.cs or main class, you would load the XML content (either from a string or a file) and parse it:

// In your Main method or another appropriate place
string xmlData = @"
<Products>
  <Product id=""101"">
    <Name>Laptop</Name>
    <Category>Electronics</Category>
    <Price>1200.00</Price>
    <Availability>In Stock</Availability>
  </Product>
  <Product id=""102"">
    <Name>Mouse</Name>
    <Category>Electronics</Category>
    <Price>25.50</Price>
    <Availability>Out of Stock</Availability>
  </Product>
  <Product id=""103"">
    <Name>Keyboard</Name>
    <Category>Peripherals</Category>
    <Price>75.00</Price>
    <Availability>In Stock</Availability>
  </Product>
</Products>";

List<Product> products = XmlParser.ParseProductsXml(xmlData);

if (products.Any())
{
    Console.WriteLine($"Parsed {products.Count} products.");
    // Now 'products' list contains the data ready for Excel export.
}
Enter fullscreen mode Exit fullscreen mode

Writing Data to Excel with C

Now that we have our parsed XML data in a list of Product objects, we can proceed to write this data into an Excel spreadsheet using Spire.XLS for .NET.

using Spire.Xls;
using System.Collections.Generic;
using System.Linq;
using System;

public class ExcelExporter
{
    public static void ExportProductsToExcel(List<Product> products, string filePath)
    {
        // 1. Initializing the Workbook and Worksheet
        // Create a new workbook
        Workbook workbook = new Workbook();
        // Get the first worksheet (default is Sheet1)
        Worksheet sheet = workbook.Worksheets[0];

        // 2. Adding Headers
        // Define column headers
        string[] headers = { "Product ID", "Product Name", "Category", "Price", "Availability" };
        for (int i = 0; i < headers.Length; i++)
        {
            // Write header to the first row, starting from column 1 (A)
            sheet.Range[1, i + 1].Text = headers[i];
            // Optional: Make headers bold
            sheet.Range[1, i + 1].Style.Font.IsBold = true;
        }

        // 3. Populating Data Rows
        // Start writing data from the second row (after headers)
        int dataRow = 2;
        foreach (var product in products)
        {
            sheet.Range[dataRow, 1].Value = product.Id.ToString();
            sheet.Range[dataRow, 2].Value = product.Name;
            sheet.Range[dataRow, 3].Value = product.Category;
            sheet.Range[dataRow, 4].Value = product.Price.ToString(); // Spire.XLS can handle decimal/double directly
            sheet.Range[dataRow, 5].Value = product.Availability;
            dataRow++;
        }

        // 4. Basic Styling and Saving
        // Auto-fit column width for better readability
        sheet.AutoFitColumn(1);
        sheet.AutoFitColumn(2);
        sheet.AutoFitColumn(3);
        sheet.AutoFitColumn(4);
        sheet.AutoFitColumn(5);

        // Save the workbook to a file
        workbook.SaveToFile(filePath, ExcelVersion.Version2016); // Or ExcelVersion.Version2013, ExcelVersion.Version2010 etc.

        // Dispose of the workbook object to release resources
        workbook.Dispose();

        Console.WriteLine($"Excel file saved successfully to: {filePath}");
    }
}
Enter fullscreen mode Exit fullscreen mode

Now, integrate this into your Main method:

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO; // For Path.Combine

// Assuming Product, XmlParser, and ExcelExporter classes are defined as above

class Program
{
    static void Main(string[] args)
    {
        string xmlData = @"
<Products>
  <Product id=""101"">
    <Name>Laptop</Name>
    <Category>Electronics</Category>
    <Price>1200.00</Price>
    <Availability>In Stock</Availability>
  </Product>
  <Product id=""102"">
    <Name>Mouse</Name>
    <Category>Electronics</Category>
    <Price>25.50</Price>
    <Availability>Out of Stock</Availability>
  </Product>
  <Product id=""103"">
    <Name>Keyboard</Name>
    <Category>Peripherals</Category>
    <Price>75.00</Price>
    <Availability>In Stock</Availability>
  </Product>
</Products>";

        List<Product> products = XmlParser.ParseProductsXml(xmlData);

        if (products.Any())
        {
            string outputDirectory = AppDomain.CurrentDomain.BaseDirectory;
            string excelFilePath = Path.Combine(outputDirectory, "ProductsReport.xlsx");
            ExcelExporter.ExportProductsToExcel(products, excelFilePath);
        }
        else
        {
            Console.WriteLine("No product data parsed from XML.");
        }

        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }
}
Enter fullscreen mode Exit fullscreen mode

When you run this application, it will:

  1. Parse the provided XML string into a List<Product>.
  2. Create a new Excel workbook.
  3. Add column headers to the first row.
  4. Iterate through the products list, writing each product's details into a new row.
  5. Auto-fit the columns for better presentation.
  6. Save the resulting ProductsReport.xlsx file in the application's execution directory.

Excel Output Preview

Below is a preview of the generated Excel file:

Excel Output Preview

Conclusion

Exporting XML data to Excel using C# is a powerful technique for making structured data accessible and usable for a broader audience. This tutorial has walked you through the essential steps: setting up your C# project, parsing XML data into manageable C# objects, and finally, writing that data into a formatted Excel spreadsheet using the Spire.XLS for .NET library.

By automating this process, you gain significant advantages in efficiency, accuracy, and data consistency. This programmatic approach not only streamlines data workflows but also empowers your applications with robust data export capabilities, allowing for seamless integration and improved data analysis.

For further enhancements, consider adding more sophisticated error handling, implementing advanced Excel formatting (like conditional formatting or charts), or integrating this logic into a web application or desktop GUI for an even more user-friendly experience. The foundation laid here provides a solid starting point for a wide range of data transformation and reporting solutions.

Top comments (0)