Calvince Moth for Syncfusion, Inc.

Posted on Apr 22 • Originally published at syncfusion.com on Apr 21

How to Export XML to Excel in 3 Easy Steps using C#?

#excel #net #csharp #datavisualization

TL;DR: Are you tired of manually converting XML to Excel? Save time and streamline your workflow with our .NET Excel Library (XlsIO)! In just 3 simple steps, you’ll learn how to export XML data to an Excel sheet using C#, apply XML mappings for structured data control, and export beautifully formatted Excel files—all with minimal code.

Working with XML data can be tedious, especially when you need to convert it into readable, shareable Excel spreadsheets. Manually copying data? Not scalable. Third-party tools? Often, they lack flexibility and control.

That’s where the Syncfusion^® .NET Excel Library (XlsIO) comes in. It lets you seamlessly export XML files into Excel with complete control over structure, formatting, and even mapping. Whether you’re building reports or transforming large datasets, XlsIO makes it easy.

The .NET Excel Library, also known as Essential XlsIO, is a robust tool that facilitates the smooth creation, reading, and editing of Excel documents using C#. It supports the creation of Excel documents from scratch, modification of existing Excel documents, data import and export, Excel formulas, conditional formats, data validations, charts, sparklines, tables, pivot tables, pivot charts, template markers, and much more.

This blog will explore the steps to export XML data to an Excel document using our .NET Excel Library and C#.

Step 1: Create a .NET Core Console app

First, create a .NET Core Console application in Visual Studio, as shown in the following image.

Step 2: Install the Syncfusion.XlsIO.NET.Core NuGet package

Then, install the latest Syncfusion.XlsIO.NET.Core NuGet package in your app.

Step 3: Export XML data to an Excel document

Now, add the following code to Export XML data to an Excel document.

using Syncfusion.XlsIO;

namespace XmlToExcel
{
    class Program
    {
        public static void Main(string[] args)
        {
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Xlsx;

                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];

                // Import XML data into the worksheet
                FileStream inputStream = new FileStream(
                    "../../../Data/XmlFile.xml", FileMode.Open, FileAccess.Read);
                worksheet.ImportXml(inputStream, 1, 1);

                worksheet.UsedRange.AutofitColumns();

                // Saving the workbook as a stream
                FileStream outputStream = new FileStream(
                    "../../../Output/Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
                workbook.SaveAs(outputStream);

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

Refer to the following images.

Input XML data

Exporting XML data to an Excel document

XML maps to Excel

We can also add XML mapping to an Excel document using our .NET Excel Library. XML mapping enables us to define which parts of the XML data should be imported into Excel and which sections can be exported back as XML, offering complete control over your data transformation process.

To do so, please add the following code and run the app.

using Syncfusion.XlsIO;

namespace XmlMapToExcel
{
    class Program
    {
        public static void Main(string[] args)
        {
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Xlsx;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];

                // Import XML data into the worksheet
                FileStream inputStream = new FileStream("../../../Data/XmlFile.xml", FileMode.Open, FileAccess.Read);

                // Import XML mapping to Excel
                workbook.XmlMaps.Add(inputStream);

                // Saving the workbook as a stream
                FileStream outputStream = new FileStream("../../../Output/XmlMapOutput.xlsx", FileMode.Create, FileAccess.ReadWrite);
                workbook.SaveAs(outputStream);

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

Refer to the following image.

The screenshot above shows that only the XML mapping is added to the Excel document. With XML maps, we can easily select and control the specific data to be imported or exported between the XML file and the Excel sheet.

References

For more details, refer to the Exporting XML to Excel using C# documentation and GitHub demo.

Conclusion

Thanks for reading! In this blog, we’ve explored how to export XML data to an Excel document using C# and Syncfusion .NET Excel Library** (XlsIO)**. It also allows you to export Excel data to images, data tables, CSV, HTML, collections of objects, ODS, JSON, and other file formats. Feel free to try out this versatile .NET Excel Library and share your feedback in the comments section of this blog post!

For current customers, our products are available on the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to try them out.

Please let us know in the comments section below if you have any queries or require clarification. You can also contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!

DEV Community