Open and read XML SpreadsheetML 2003 files from your C#/VB.NET applications

The following example shows how you can read XML (XML Spreadsheet 2003, also known as SpreadsheetML) and other Excel files by using only the GemBox.Spreadsheet .NET component.

GemBox.Spreadsheet can open many Excel file formats (including XLSX, XLS, XLSB, ODS, CSV, XML and HTML) in the same manner. Once you load your file from a disk, stream, database or some other source, you can read it, import its content, or modify it.

Steps for reading XML files

You can read XML files with just a few lines of code. You only need an IDE like Visual Studio or JetBrains Rider and .NET Framework, .NET Core, or any other platform that implements .NET Standard.

Follow these steps to load a file from a disk and read its content:

Create or use an existing C# or VB.NET project.
Download and install GemBox.Spreadsheet Setup.
Add a reference to GemBox.Spreadsheet.dll within your C# or VB.NET project.
Optionally, you can add GemBox.Spreadsheet Nuget package to your C# or VB.NET project.
Execute the C#/VB.NET code below.

Try reading XML files online

You can test reading the content of your XML files with the interactive example below. Just upload your file and click on Run Example. After clicking on Run example, the C#/VB.NET code will be compiled, and the content of your file will be read with only .NET framework and the GemBox.Spreadsheet component.

The following example loads the Excel file, iterates through its cells, and appends the content of the cells to StringBuilder.

C#
VB.NET

using System;
using System.Text;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If you are using the Professional version, enter your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = ExcelFile.Load("%InputFileName%");

        var sb = new StringBuilder();

        // Iterate through all worksheets in an Excel workbook.
        foreach (var worksheet in workbook.Worksheets)
        {
            sb.AppendLine();
            sb.AppendFormat("{0} {1} {0}", new string('-', 25), worksheet.Name);

            // Iterate through all rows in an Excel worksheet.
            foreach (var row in worksheet.Rows)
            {
                sb.AppendLine();

                // Iterate through all allocated cells in an Excel row.
                foreach (var cell in row.AllocatedCells)
                    if (cell.ValueType != CellValueType.Null)
                        sb.Append(string.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25));
                    else
                        sb.Append(new string(' ', 25));
            }
        }

        Console.WriteLine(sb.ToString());
    }
}

Imports System
Imports System.Text
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If you are using the Professional version, enter your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = ExcelFile.Load("%InputFileName%")

        Dim sb = New StringBuilder()

        ' Iterate through all worksheets in an Excel workbook.
        For Each worksheet In workbook.Worksheets

            sb.AppendLine()
            sb.AppendFormat("{0} {1} {0}", New String("-"c, 25), worksheet.Name)

            ' Iterate through all rows in an Excel worksheet.
            For Each row In worksheet.Rows

                sb.AppendLine()

                ' Iterate through all allocated cells in an Excel row.
                For Each cell In row.AllocatedCells
                    If cell.ValueType <> CellValueType.Null Then
                        sb.Append(String.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25))
                    Else
                        sb.Append(New String(" "c, 25))
                    End If
                Next
            Next
        Next

        Console.WriteLine(sb.ToString())
    End Sub
End Module

Sheet values read with GemBox.Spreadsheet — Screenshot of sheet values read with GemBox.Spreadsheet

Import data from XML to DataTable

With GemBox.Spreadsheet you can also import data from any sheet to a DataTable. GemBox.Spreadsheet offers lots of options, such as the possibility to detect the columns data type, that can simplify your code.

The following example shows how you can load XML or any other Excel file and create a DataTable from a specific cell range in the Excel worksheet in C# and VB.NET.

C#
VB.NET

using System;
using System.Data;
using System.Text;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If you are using the Professional version, enter your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = ExcelFile.Load("%InputFileName%");

        // Select the first worksheet from the file.
        var worksheet = workbook.Worksheets[0];

        // Create DataTable from an Excel worksheet.
        var dataTable = worksheet.CreateDataTable(new CreateDataTableOptions()
        {
            ColumnHeaders = true,
            StartRow = 1,
            NumberOfColumns = 5,
            NumberOfRows = worksheet.Rows.Count - 1,
            Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
        });

        // Write DataTable content
        var sb = new StringBuilder();
        sb.AppendLine("DataTable content:");
        foreach (DataRow row in dataTable.Rows)
        {
            sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}", row[0], row[1], row[2], row[3], row[4]);
            sb.AppendLine();
        }

        Console.WriteLine(sb.ToString());
    }
}

Imports System
Imports System.Data
Imports System.Text
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If you are using the Professional version, enter your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = ExcelFile.Load("%InputFileName%")

        ' Select the first worksheet from the file.
        Dim worksheet = workbook.Worksheets(0)

        ' Create DataTable from an Excel worksheet.
        Dim dataTable = worksheet.CreateDataTable(New CreateDataTableOptions() With
        {
            .ColumnHeaders = True,
            .StartRow = 1,
            .NumberOfColumns = 5,
            .NumberOfRows = worksheet.Rows.Count - 1,
            .Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
        })

        ' Write DataTable content
        Dim sb = New StringBuilder()
        sb.AppendLine("DataTable content:")
        For Each row As DataRow In dataTable.Rows

            sb.AppendFormat("{0}" & vbTab & "{1}" & vbTab & "{2}" & vbTab & "{3}" & vbTab & "{4}", row(0), row(1), row(2), row(3), row(4))
            sb.AppendLine()
        Next

        Console.WriteLine(sb.ToString())
    End Sub
End Module

XML Spreadsheet 2003 file format

XML Spreadsheet 2003, also known as SpreadsheetML, is a XML-based file format for spreadsheets created by Microsoft that was first introduced with Microsoft Office XP.

GemBox.Spreadsheet makes working with XML Spreadsheet files a lot easier. It offers a high level model, and lots of utility functions like calculation engine and auto fit. Besides the XML file format, you can also read the following file formats: XLSX, XLS, XLSB, ODS, CSV, TXT, XLSM, XLTX, XLTM, HTML, MHTML.

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy