1. IronXL
  2. How-Tos
  3. C Sharp Read XLSX File

C# Read XLSX File

Chaknith Bin
Chaknith Bin
Updated:

When working with various Excel formats, it often requires reading the data and manipulating it programmatically. In the following tutorial, we will learn how to read data from an Excel spreadsheet in C# using a convenient tool, IronXL.

Read .XLSX Files C#

  • Get IronXL for your project
  • Load a WorkBook
  • Access data from a WorkSheet
  • Apply functions like Sum, Min, & Max
  • Read a WorkSheet as a DataTable, DataSet, and more
How To Work related to C# Read XLSX File

Step 1

1. Get IronXL for Your Project

Use IronXL in your project for a simple way to work with Excel file formats in C#. You can either install IronXL via direct download or alternatively you can use NuGet Install for Visual Studio. The software is free for development.

Install-Package IronXL.Excel

How to Tutorial

2. Load WorkBook

WorkBook is the class of IronXL whose object provides full access to the Excel file and its whole functions. For example, if we want to access an Excel file, we'd use the code:

// Load Workbook
// anchor-load-workbook
WorkBook wb = WorkBook.Load("sample.xlsx"); // Excel file path
// Load Workbook
// anchor-load-workbook
WorkBook wb = WorkBook.Load("sample.xlsx"); // Excel file path
' Load Workbook
' anchor-load-workbook
Dim wb As WorkBook = WorkBook.Load("sample.xlsx") ' Excel file path
$vbLabelText   $csharpLabel

In the above code, the WorkBook.Load() function loads sample.xlsx into wb. Any type of function can be performed on wb by accessing the specific WorkSheet of an Excel file.

3. Access Specific WorkSheet

To access a specific WorkSheet of an Excel file, IronXL provides the WorkSheet class. It can be used in a few different ways:

// Access Sheet by Name
// anchor-access-specific-worksheet
WorkSheet ws = wb.GetWorkSheet("Sheet1"); // access by sheet name
// Access Sheet by Name
// anchor-access-specific-worksheet
WorkSheet ws = wb.GetWorkSheet("Sheet1"); // access by sheet name
' Access Sheet by Name
' anchor-access-specific-worksheet
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1") ' access by sheet name
$vbLabelText   $csharpLabel

wb is the WorkBook that is declared in the above portion.

OR

// Access Sheet by Index
// anchor-access-specific-worksheet
WorkSheet ws = wb.WorkSheets[0]; // access by sheet index
// Access Sheet by Index
// anchor-access-specific-worksheet
WorkSheet ws = wb.WorkSheets[0]; // access by sheet index
' Access Sheet by Index
' anchor-access-specific-worksheet
Dim ws As WorkSheet = wb.WorkSheets(0) ' access by sheet index
$vbLabelText   $csharpLabel

OR

// Access the default WorkSheet
WorkSheet ws = wb.DefaultWorkSheet;
// Access the default WorkSheet
WorkSheet ws = wb.DefaultWorkSheet;
' Access the default WorkSheet
Dim ws As WorkSheet = wb.DefaultWorkSheet
$vbLabelText   $csharpLabel

OR

// Access the first WorkSheet
WorkSheet ws = wb.WorkSheets.First();
// Access the first WorkSheet
WorkSheet ws = wb.WorkSheets.First();
' Access the first WorkSheet
Dim ws As WorkSheet = wb.WorkSheets.First()
$vbLabelText   $csharpLabel

OR

// Access the first or default WorkSheet
WorkSheet ws = wb.WorkSheets.FirstOrDefault();
// Access the first or default WorkSheet
WorkSheet ws = wb.WorkSheets.FirstOrDefault();
' Access the first or default WorkSheet
Dim ws As WorkSheet = wb.WorkSheets.FirstOrDefault()
$vbLabelText   $csharpLabel

After getting ExcelSheet ws, you can get any type of data from it and perform all Excel functions on it.

4. Access Data from WorkSheet

Data can be accessed from ExcelSheet ws with this process:

// Accessing data as a string
string dataString = ws["A1"].ToString(); // Access a specific cell as a string
// Accessing data as an integer
int dataInt = ws["B1"].Int32Value; // Access a specific cell as an integer
// Accessing data as a string
string dataString = ws["A1"].ToString(); // Access a specific cell as a string
// Accessing data as an integer
int dataInt = ws["B1"].Int32Value; // Access a specific cell as an integer
' Accessing data as a string
Dim dataString As String = ws("A1").ToString() ' Access a specific cell as a string
' Accessing data as an integer
Dim dataInt As Integer = ws("B1").Int32Value ' Access a specific cell as an integer
$vbLabelText   $csharpLabel

It is also possible to get data from multiple cells of a specific column.

foreach (var cell in ws["A2:A10"])
{
    Console.WriteLine("Value is: {0}", cell.Text);
}
foreach (var cell in ws["A2:A10"])
{
    Console.WriteLine("Value is: {0}", cell.Text);
}
For Each cell In ws("A2:A10")
	Console.WriteLine("Value is: {0}", cell.Text)
Next cell
$vbLabelText   $csharpLabel

This will display the values from cell A2 to A10.

A complete code example of the specifics above is provided here.

// Access WorkSheet Data
// anchor-access-data-from-worksheet
using IronXL;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Get the specified WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");

    // Iterate through a range of cells and display their text
    foreach (var cell in ws["A2:A10"])
    {
        Console.WriteLine("Value is: {0}", cell.Text);
    }

    Console.ReadKey();
}
// Access WorkSheet Data
// anchor-access-data-from-worksheet
using IronXL;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Get the specified WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");

    // Iterate through a range of cells and display their text
    foreach (var cell in ws["A2:A10"])
    {
        Console.WriteLine("Value is: {0}", cell.Text);
    }

    Console.ReadKey();
}
' Access WorkSheet Data
' anchor-access-data-from-worksheet
Imports IronXL

Shared Sub Main(ByVal args() As String)
	' Load the Excel workbook
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")

	' Get the specified WorkSheet
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")

	' Iterate through a range of cells and display their text
	For Each cell In ws("A2:A10")
		Console.WriteLine("Value is: {0}", cell.Text)
	Next cell

	Console.ReadKey()
End Sub
$vbLabelText   $csharpLabel

It will display the following result:

Doc3 Input1 related to 4. Access Data from WorkSheet

With the Excel file Sample.xlsx :

Doc3 1 related to 4. Access Data from WorkSheet

We can see how effortless it is to use Excel file data in your project using these methodologies.

5. Perform Functions on Data

It is very straightforward to access filtered data from an Excel WorkSheet by applying aggregate functions like Sum, Min, or Max using the following code:

// Apply aggregate functions
decimal sum = ws["G2:G10"].Sum(); // Sum of cells from G2 to G10
decimal min = ws["G2:G10"].Min(); // Minimum value in cells from G2 to G10
decimal max = ws["G2:G10"].Max(); // Maximum value in cells from G2 to G10
// Apply aggregate functions
decimal sum = ws["G2:G10"].Sum(); // Sum of cells from G2 to G10
decimal min = ws["G2:G10"].Min(); // Minimum value in cells from G2 to G10
decimal max = ws["G2:G10"].Max(); // Maximum value in cells from G2 to G10
' Apply aggregate functions
Dim sum As Decimal = ws("G2:G10").Sum() ' Sum of cells from G2 to G10
Dim min As Decimal = ws("G2:G10").Min() ' Minimum value in cells from G2 to G10
Dim max As Decimal = ws("G2:G10").Max() ' Maximum value in cells from G2 to G10
$vbLabelText   $csharpLabel

If you want more details, check out our in-depth tutorial on How to Write C# Excel Files with specifics on aggregate functions.

// Sum Min Max Functions
// anchor-perform-functions-on-data
using IronXL;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Get the specified WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");

    // Calculate sum, minimum, and maximum for a range of cells
    decimal sum = ws["G2:G10"].Sum();
    decimal min = ws["G2:G10"].Min();
    decimal max = ws["G2:G10"].Max();

    // Output results
    Console.WriteLine("Sum is: {0}", sum);
    Console.WriteLine("Min is: {0}", min);
    Console.WriteLine("Max is: {0}", max);
    Console.ReadKey();
}
// Sum Min Max Functions
// anchor-perform-functions-on-data
using IronXL;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Get the specified WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");

    // Calculate sum, minimum, and maximum for a range of cells
    decimal sum = ws["G2:G10"].Sum();
    decimal min = ws["G2:G10"].Min();
    decimal max = ws["G2:G10"].Max();

    // Output results
    Console.WriteLine("Sum is: {0}", sum);
    Console.WriteLine("Min is: {0}", min);
    Console.WriteLine("Max is: {0}", max);
    Console.ReadKey();
}
' Sum Min Max Functions
' anchor-perform-functions-on-data
Imports IronXL

Shared Sub Main(ByVal args() As String)
	' Load the Excel workbook
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")

	' Get the specified WorkSheet
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")

	' Calculate sum, minimum, and maximum for a range of cells
	Dim sum As Decimal = ws("G2:G10").Sum()
	Dim min As Decimal = ws("G2:G10").Min()
	Dim max As Decimal = ws("G2:G10").Max()

	' Output results
	Console.WriteLine("Sum is: {0}", sum)
	Console.WriteLine("Min is: {0}", min)
	Console.WriteLine("Max is: {0}", max)
	Console.ReadKey()
End Sub
$vbLabelText   $csharpLabel

This code will display the following output:

Doc3 Output2 related to 5. Perform Functions on Data

And this is how the Excel file Sample.xlsx will look:

Doc3 2 related to 5. Perform Functions on Data

6. Read Excel WorkSheet as DataTable

Using IronXL, it is very easy to operate with an Excel WorkSheet as a DataTable.

// Convert WorkSheet to DataTable
DataTable dt = ws.ToDataTable();
// Convert WorkSheet to DataTable
DataTable dt = ws.ToDataTable();
' Convert WorkSheet to DataTable
Dim dt As DataTable = ws.ToDataTable()
$vbLabelText   $csharpLabel

If we want to use the first row of ExcelSheet as DataTable ColumnName then:

// Convert WorkSheet to DataTable with the first row as column names
DataTable dt = ws.ToDataTable(true);
// Convert WorkSheet to DataTable with the first row as column names
DataTable dt = ws.ToDataTable(true);
' Convert WorkSheet to DataTable with the first row as column names
Dim dt As DataTable = ws.ToDataTable(True)
$vbLabelText   $csharpLabel

Thus, the Boolean parameter of ToDataTable() sets the first row as the column names of your DataTable. By default, its value is False.

// WorkSheet as DataTable
// anchor-read-excel-worksheet-as-datatable
using IronXL;
using System.Data;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Get the specified WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");

    // Convert WorkSheet to DataTable
    DataTable dt = ws.ToDataTable(true); // Use first row as column names

    // Iterate through rows and columns and display data
    foreach (DataRow row in dt.Rows) // Access rows
    {
        for (int i = 0; i < dt.Columns.Count; i++) // Access columns of corresponding row
        {
            Console.Write(row[i] + " ");
        }
        Console.WriteLine();
    }
}
// WorkSheet as DataTable
// anchor-read-excel-worksheet-as-datatable
using IronXL;
using System.Data;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Get the specified WorkSheet
    WorkSheet ws = wb.GetWorkSheet("Sheet1");

    // Convert WorkSheet to DataTable
    DataTable dt = ws.ToDataTable(true); // Use first row as column names

    // Iterate through rows and columns and display data
    foreach (DataRow row in dt.Rows) // Access rows
    {
        for (int i = 0; i < dt.Columns.Count; i++) // Access columns of corresponding row
        {
            Console.Write(row[i] + " ");
        }
        Console.WriteLine();
    }
}
' WorkSheet as DataTable
' anchor-read-excel-worksheet-as-datatable
Imports IronXL
Imports System.Data

Shared Sub Main(ByVal args() As String)
	' Load the Excel workbook
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")

	' Get the specified WorkSheet
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")

	' Convert WorkSheet to DataTable
	Dim dt As DataTable = ws.ToDataTable(True) ' Use first row as column names

	' Iterate through rows and columns and display data
	For Each row As DataRow In dt.Rows ' Access rows
		For i As Integer = 0 To dt.Columns.Count - 1 ' Access columns of corresponding row
			Console.Write(row(i) & " ")
		Next i
		Console.WriteLine()
	Next row
End Sub
$vbLabelText   $csharpLabel

Using the above code, every cell value of the WorkSheet can be accessed and used as required.

7. Read Excel File as DataSet

IronXL provides a very simple function to use a complete Excel file (WorkBook) as a DataSet.

// Convert WorkBook to DataSet
DataSet ds = wb.ToDataSet();
// Convert WorkBook to DataSet
DataSet ds = wb.ToDataSet();
' Convert WorkBook to DataSet
Dim ds As DataSet = wb.ToDataSet()
$vbLabelText   $csharpLabel

In this example, we will see how to use the Excel file as a DataSet.

// Excel File as DataSet
// anchor-read-excel-file-as-dataset
using IronXL;
using System.Data;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Convert the WorkBook to a DataSet
    DataSet ds = wb.ToDataSet();

    // Iterate through tables in the DataSet and display table names
    foreach (DataTable dt in ds.Tables)
    {
        Console.WriteLine(dt.TableName);
    }
}
// Excel File as DataSet
// anchor-read-excel-file-as-dataset
using IronXL;
using System.Data;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Convert the WorkBook to a DataSet
    DataSet ds = wb.ToDataSet();

    // Iterate through tables in the DataSet and display table names
    foreach (DataTable dt in ds.Tables)
    {
        Console.WriteLine(dt.TableName);
    }
}
' Excel File as DataSet
' anchor-read-excel-file-as-dataset
Imports IronXL
Imports System.Data

Shared Sub Main(ByVal args() As String)
	' Load the Excel workbook
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")

	' Convert the WorkBook to a DataSet
	Dim ds As DataSet = wb.ToDataSet()

	' Iterate through tables in the DataSet and display table names
	For Each dt As DataTable In ds.Tables
		Console.WriteLine(dt.TableName)
	Next dt
End Sub
$vbLabelText   $csharpLabel

The output of the above code will look like this:

Doc10 Output2 related to 7. Read Excel File as DataSet

And the Excel file Sample.xlsx will look like this:

Doc10 2 related to 7. Read Excel File as DataSet

In the above example, we see that we can easily parse an Excel file into a DataSet and employ it with every WorkSheet of an Excel file as a DataTable. Dive more into how to parse Excel as a DataSet here featuring code examples.

Let's see one more example of how to access each cell value of all ExcelSheets. Here, we can access each cell value of every WorkSheet of an Excel file.

// WorkSheet Cell Values
// anchor-read-excel-file-as-dataset
using IronXL;
using System.Data;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Convert WorkBook to DataSet
    DataSet ds = wb.ToDataSet(); // Treat the complete Excel file as DataSet

    // Iterate through each DataTable in the DataSet (Equivalent to each worksheet)
    foreach (DataTable dt in ds.Tables) // Treat each WorkSheet as DataTable
    {
        // Iterate through each DataRow in the DataTable
        foreach (DataRow row in dt.Rows) // Rows of corresponding worksheet
        {
            // Iterate through each column in the DataRow
            for (int i = 0; i < dt.Columns.Count; i++) // Columns of the corresponding row
            {
                Console.Write(row[i] + " ");
            }
            Console.WriteLine();
        }
    }
}
// WorkSheet Cell Values
// anchor-read-excel-file-as-dataset
using IronXL;
using System.Data;

static void Main(string[] args)
{
    // Load the Excel workbook
    WorkBook wb = WorkBook.Load("sample.xlsx");

    // Convert WorkBook to DataSet
    DataSet ds = wb.ToDataSet(); // Treat the complete Excel file as DataSet

    // Iterate through each DataTable in the DataSet (Equivalent to each worksheet)
    foreach (DataTable dt in ds.Tables) // Treat each WorkSheet as DataTable
    {
        // Iterate through each DataRow in the DataTable
        foreach (DataRow row in dt.Rows) // Rows of corresponding worksheet
        {
            // Iterate through each column in the DataRow
            for (int i = 0; i < dt.Columns.Count; i++) // Columns of the corresponding row
            {
                Console.Write(row[i] + " ");
            }
            Console.WriteLine();
        }
    }
}
' WorkSheet Cell Values
' anchor-read-excel-file-as-dataset
Imports IronXL
Imports System.Data

Shared Sub Main(ByVal args() As String)
	' Load the Excel workbook
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")

	' Convert WorkBook to DataSet
	Dim ds As DataSet = wb.ToDataSet() ' Treat the complete Excel file as DataSet

	' Iterate through each DataTable in the DataSet (Equivalent to each worksheet)
	For Each dt As DataTable In ds.Tables ' Treat each WorkSheet as DataTable
		' Iterate through each DataRow in the DataTable
		For Each row As DataRow In dt.Rows ' Rows of corresponding worksheet
			' Iterate through each column in the DataRow
			For i As Integer = 0 To dt.Columns.Count - 1 ' Columns of the corresponding row
				Console.Write(row(i) & " ")
			Next i
			Console.WriteLine()
		Next row
	Next dt
End Sub
$vbLabelText   $csharpLabel

Using the above example, it is very convenient to access each cell value of every WorkSheet of an Excel file.

For more on how to Read Excel Files Without Interop check out the code here.

Tutorial Quick Access

API Reference for IronXL

Read more about IronXL's features, classes, method fields, namespaces, and enums in the documentation.

API Reference for IronXL
Documentation related to Tutorial Quick Access

Frequently Asked Questions

How can I install a library for my project?

You can install IronXL by direct download or using the NuGet Package Manager in Visual Studio. Use the command 'Install-Package IronXL.Excel' in the NuGet Package Manager console.

How do I load an Excel Workbook using a library?

To load an Excel Workbook, use the WorkBook.Load() method. For example: WorkBook wb = WorkBook.Load('sample.xlsx');

How can I access a specific worksheet in an Excel file?

You can access a specific worksheet using the GetWorkSheet method by name or index, or by using properties like DefaultWorkSheet or First.

How do I access data from a worksheet using a library?

Data can be accessed by referencing specific cells. For example, ws['A1'].ToString() accesses a cell as a string, while ws['B1'].Int32Value accesses it as an integer.

How do I apply aggregate functions like Sum, Min, and Max using a library?

You can apply aggregate functions such as Sum, Min, and Max using methods on a range of cells, e.g., decimal sum = ws['G2:G10'].Sum().

Can I convert a worksheet to a DataTable using a library?

Yes, you can convert a worksheet to a DataTable using the ToDataTable() method. If you want the first row as column names, use ToDataTable(true).

How can I use an Excel workbook as a DataSet?

You can use the ToDataSet() method on a WorkBook object to treat the entire Excel file as a DataSet.

Is it possible to read Excel files without using Interop?

Yes, IronXL enables you to read Excel files in C# without using Interop, providing a simpler and more efficient approach.

What are some benefits of using a library for Excel file manipulation?

IronXL allows for easy reading and manipulation of Excel files in C#, supports various Excel formats, and offers features such as accessing data, performing functions, and converting worksheets to DataTables or DataSets.

Where can I find more documentation on a library?

You can find more documentation on IronXL's features, classes, and methods on the IronXL API Reference page.

Chaknith Bin
Chaknith Bin
  Chat with engineering team now
Software Engineer
Chaknith works on IronXL and IronBarcode. He has deep expertise in C# and .NET, helping improve the software and support customers. His insights from user interactions contribute to better products, documentation, and overall experience.
Talk to an Expert Five Star Trust Score Rating

Ready to Get Started?

Nuget Passed
Free NuGet Download Total downloads: 1,487,525
View Licenses