using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published January 28, 2024

How to Convert An Excel File to XML in C#

Introduction

Microsoft Excel is a widely used multifunctional spreadsheet application renowned for its effectiveness in organizing, analyzing, and visualizing data. It is a program that works similarly to a grid. Users may enter various types of input, such as text, numbers, dates, and formulas into individual cells, and the data is arranged into rows and columns for easy management. Its robust formula and function calculation capabilities enable users to perform a wide range of logical, statistical, and mathematical tasks.

Excel offers tools for managing and evaluating data as well as for producing graphs and charts that visually represent the data. It facilitates collaborative efforts by allowing several users to edit and share files simultaneously. It can be customized and automated with the use of macros, VBA, and add-ins due to its adaptability to a range of user demands across sectors. Excel Workbook class is used in a variety of fields, including banking, education, research, and business analytics. It is an essential tool for organizing, evaluating, and using data to inform choices. In this article, we are going to learn how to convert Excel to XML in C#.

Convert Excel Files (XLS) to XML Format in C#

Create a Console project in Visual Studio.
Install the IronXL library.
Initialize the necessary object of the IronXL.
Create a new Excel file (XLS or XLSX) or load the Excel file into the created object.
Use the SaveAsXml method within the XMLSaveOptions class to convert the loaded file into XML.
Dispose of the IronXL objects.

using IronXL;

class Program
{
    static void Main()
    {
        // Step 2: Install IronXL library

        // Step 3: Initialize IronXL object
        WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

        // Step 4: Create or load Excel file
        // Create a new Excel file
        WorkSheet worksheet = workbook.CreateWorkSheet("Sheet1");
        worksheet["A1"].Value = "Hello";
        worksheet["B1"].Value = "World";

        // Or load an existing Excel file
        workbook = WorkBook.Load("path_to_excel_file.xlsx");

        // Step 5: Convert Excel to XML
        XMLSaveOptions saveOptions = new XMLSaveOptions();
        workbook.SaveAsXml("path_to_output_xml_file.xml", saveOptions);

        // Step 6: Dispose of the IronXL objects
        worksheet.Dispose();
        workbook.Dispose();
    }
}

using IronXL;

class Program
{
    static void Main()
    {
        // Step 2: Install IronXL library

        // Step 3: Initialize IronXL object
        WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

        // Step 4: Create or load Excel file
        // Create a new Excel file
        WorkSheet worksheet = workbook.CreateWorkSheet("Sheet1");
        worksheet["A1"].Value = "Hello";
        worksheet["B1"].Value = "World";

        // Or load an existing Excel file
        workbook = WorkBook.Load("path_to_excel_file.xlsx");

        // Step 5: Convert Excel to XML
        XMLSaveOptions saveOptions = new XMLSaveOptions();
        workbook.SaveAsXml("path_to_output_xml_file.xml", saveOptions);

        // Step 6: Dispose of the IronXL objects
        worksheet.Dispose();
        workbook.Dispose();
    }
}

Imports IronXL

Friend Class Program
	Shared Sub Main()
		' Step 2: Install IronXL library

		' Step 3: Initialize IronXL object
		Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

		' Step 4: Create or load Excel file
		' Create a new Excel file
		Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Sheet1")
		worksheet("A1").Value = "Hello"
		worksheet("B1").Value = "World"

		' Or load an existing Excel file
		workbook = WorkBook.Load("path_to_excel_file.xlsx")

		' Step 5: Convert Excel to XML
		Dim saveOptions As New XMLSaveOptions()
		workbook.SaveAsXml("path_to_output_xml_file.xml", saveOptions)

		' Step 6: Dispose of the IronXL objects
		worksheet.Dispose()
		workbook.Dispose()
	End Sub
End Class

VB C#

In the above code, we first install the IronXL library. Then, we initialize the IronXL object and create or load the Excel file. Finally, we use the SaveAsXml method with the XMLSaveOptions class to convert the loaded file into XML. Don't forget to dispose of the IronXL objects after use.

What is IronXL

A powerful .NET framework Excel library called IronXL was created to facilitate dealing with Excel written in C#, VB.NET, and other .NET languages. It is compatible with the XLS and XLSX file formats. With the use of this library, developers may write, read, edit, and produce Excel spreadsheets more quickly and simply. A vast array of tools and functions are also available.

The key features and properties of IronXL include:

Data handling: IronXL makes it easy to read, write, and manipulate data in Excel spreadsheets. A two-dimensional array may be used to obtain cell values, and formulas, computations, and data formatting are all possible.
Excel File Creation and Modification: Developers may add, remove, and manage worksheets in addition to creating new Excel files and modifying ones that already exist. Additionally, they can communicate with a variety of Excel components using DLL files.
IronXL may be utilized in a range of application scenarios because of its cross-platform interoperability and can be integrated with multiple .NET platforms, including Xamarin, .NET Core, and .NET Framework.
Versatility and Compatibility: It supports both the older XLS Excel format and the more modern XLSX Excel format, and it is compatible with several Excel versions.
Support for Both Legacy and Modern Excel Formats: It can support more recent XML-based forms (XLSX, which dates to Excel 2007) as well as older Excel file formats (XLS, which dates back to Excel 97–2003).
Usefulness: By offering a straightforward API with easily understood attributes and functions, the library increases the accessibility of Excel-related activities for developers with varying degrees of experience.
Data Extraction and Export: IronXL facilitates the extraction of data from Excel files and the export of Excel data to several formats such as XML, new DataTable, and plain text, making it simple to interface with databases and other systems.
Support and Documentation: IronXL provides an abundance of tutorials, documentation, and support to assist developers in using its library for Excel-based activities.
Automation and Efficiency: By automating Excel procedures, IronXL enables users to build data-driven, efficient apps, increase productivity, and spend less time on manual labor.
Integration and Customization: It makes it easier to create personalized reports and data-driven solutions by exporting Excel data choices into a variety of formats. It also works well with databases and other systems.

Finance, data analysis, reporting, business intelligence, and software development are just a few of the several sectors that utilize IronXL. Combining data manipulation with Excel integration enables programmers to work with Excel files and produce reliable solutions. To learn more, go to this link.

Create a New Visual Studio Project

Setting up a Visual Studio Console project is easy. Follow these steps to create a Console application:

Launch Visual Studio. Ensure that Visual Studio is installed on your computer.
Create a new project: Select File > New > Project.
Choose your preferred programming language (e.g., C#) from the left side of the "Create a new project" box.
From the list of available project templates, select the "Console App" or "Console App (.NET Core)" template.
Provide a name for your project in the "Name" field.
Choose the location to store your project.
Click "Create" to start working on your new Console application project.

Installing IronXL Library

To install the IronXL library, follow these steps:

Install the IronXL library as it is required for the next steps. Use the following command in the NuGet Package Manager Console:
```
Install-Package IronXL
```
Another approach is to use the NuGet Package Manager to search for the package "IronXL". Choose the appropriate NuGet package for IronXL from the search results.

Convert Excel File to XML in C#

You can save data from an Excel file into an XML file format using IronXL's SaveAsXml function.

Here's an example of how to use the SaveAsXml function in C# with IronXL:

using IronXL;

class Program
{
    static void Main(string[] args)
    {
        string excelFilePath = "Excel file here";
        string xmlFilePath = "XML file here";

        // Load Excel file using IronXL
        WorkBook workbook = WorkBook.Load(excelFilePath);

        // Save Excel data as XML
        workbook.SaveAsXml(xmlFilePath);

        Console.WriteLine("Excel data saved as XML successfully at: " + xmlFilePath);
    }
}

using IronXL;

class Program
{
    static void Main(string[] args)
    {
        string excelFilePath = "Excel file here";
        string xmlFilePath = "XML file here";

        // Load Excel file using IronXL
        WorkBook workbook = WorkBook.Load(excelFilePath);

        // Save Excel data as XML
        workbook.SaveAsXml(xmlFilePath);

        Console.WriteLine("Excel data saved as XML successfully at: " + xmlFilePath);
    }
}

Imports IronXL

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Dim excelFilePath As String = "Excel file here"
		Dim xmlFilePath As String = "XML file here"

		' Load Excel file using IronXL
		Dim workbook As WorkBook = WorkBook.Load(excelFilePath)

		' Save Excel data as XML
		workbook.SaveAsXml(xmlFilePath)

		Console.WriteLine("Excel data saved as XML successfully at: " & xmlFilePath)
	End Sub
End Class

VB C#

To save the resulting XML file, replace "XML file here" with the appropriate location and "Excel file here" with the path to your actual Excel file.

This code sample imports an Excel file and uses the SaveAsXml function provided by IronXL to convert the Excel file data into XML format without manually looping through rows and columns to structure the XML. This method internally converts the Excel data to XML format and then publishes it to the specified XML file.

Make sure you have the necessary permissions to write to the directory when saving the XML file. The content and layout of the original Excel file may influence the type and structure of the resulting XML.

Input file:

How to Convert An Excel File to XML in C#: Figure 6 - Excel Input

Result:

How to Convert An Excel File to XML in C#: Figure 7 - XML Output

To learn more about the code, refer to the code example here.

Conclusion

An easy and efficient way to export data from an Excel file to XML format is by using IronXL's SaveAsXml function. This method eliminates the need for manual iteration through rows and cells when converting Excel data into XML format. IronXL is a .NET library that provides this functionality.

By utilizing IronXL's SaveAsXml function in C#, users can easily and quickly convert Excel data to XML format. IronXL offers a free Community Edition with limitations for non-commercial use. Paid versions of IronXL are available through subscription or perpetual-based licensing models, starting at $599. These paid versions offer enhanced features, support, and complete functionality. For the most up-to-date information on licensing, please refer to the IronXL website. To learn more about Iron Software products, visit this page.