C# Read XLSX File

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:

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-load-workbook.cs
// Load the workbook
WorkBook workBook = WorkBook.Load("sample.xlsx"); // Excel file path
' Load the workbook
Dim workBook As WorkBook = WorkBook.Load("sample.xlsx") ' Excel file path
$vbLabelText   $csharpLabel

In the above code, the WorkBook.Load() function loads sample.xlsx into workBook. Any type of function can be performed on workBook 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:

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-get-worksheet.cs
// Access sheet by name
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");
' Access sheet by name
Dim workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")
$vbLabelText   $csharpLabel

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

OR

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-worksheet-index.cs
// Access sheet by index
WorkSheet workSheet = workBook.WorkSheets[0];
' Access sheet by index
Dim workSheet As WorkSheet = workBook.WorkSheets(0)
$vbLabelText   $csharpLabel

OR

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-default-worksheet.cs
// Access the default worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;
' Access the default worksheet
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet
$vbLabelText   $csharpLabel

OR

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-first-worksheet.cs
// Access the first worksheet
WorkSheet workSheet = workBook.WorkSheets.First();
' Access the first worksheet
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
$vbLabelText   $csharpLabel

OR

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-first-or-default-worksheet.cs
// Access the first or default worksheet
WorkSheet workSheet = workBook.WorkSheets.FirstOrDefault();
' Access the first or default worksheet
Dim workSheet As WorkSheet = workBook.WorkSheets.FirstOrDefault()
$vbLabelText   $csharpLabel

After getting ExcelSheet workSheet, 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 workSheet with this process:

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-get-data.cs
// Accessing data as a string
string dataString = workSheet["A1"].ToString();

// Accessing data as an integer
int dataInt = workSheet["B1"].Int32Value;
' Accessing data as a string
Dim dataString As String = workSheet("A1").ToString()

' Accessing data as an integer
Dim dataInt As Integer = workSheet("B1").Int32Value
$vbLabelText   $csharpLabel

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

foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Value is: {0}", cell.Text);
}
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Value is: {0}", cell.Text);
}
For Each cell In workSheet("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.

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-log-data.cs
using IronXL;

// Load an Excel file
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");

// Specify the range 
foreach (var cell in workSheet["B2:B10"])
{
    Console.WriteLine("Value is: {0}", cell.Text);
}
Imports IronXL

' Load an Excel file
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")

' Specify the range 
For Each cell In workSheet("B2:B10")
	Console.WriteLine("Value is: {0}", cell.Text)
Next cell
$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:

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-aggregate-function.cs
// Apply aggregate functions
decimal sum = workSheet["G2:G10"].Sum(); // Sum of cells from G2 to G10
decimal min = workSheet["G2:G10"].Min(); // Minimum value in cells from G2 to G10
decimal max = workSheet["G2:G10"].Max(); // Maximum value in cells from G2 to G10
' Apply aggregate functions
Dim sum As Decimal = workSheet("G2:G10").Sum() ' Sum of cells from G2 to G10
Dim min As Decimal = workSheet("G2:G10").Min() ' Minimum value in cells from G2 to G10
Dim max As Decimal = workSheet("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.

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-min-max.cs
using IronXL;

// Load the Excel workbook
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Get the specified WorkSheet
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");

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

// Output results
Console.WriteLine("Sum is: {0}", sum);
Console.WriteLine("Min is: {0}", min);
Console.WriteLine("Max is: {0}", max);
Imports IronXL

' Load the Excel workbook
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")

' Get the specified WorkSheet
Private workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")

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

' Output results
Console.WriteLine("Sum is: {0}", sum)
Console.WriteLine("Min is: {0}", min)
Console.WriteLine("Max is: {0}", max)
$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.

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-datatable.cs
// Convert worksheet to DataTable
DataTable dt = workSheet.ToDataTable();
' Convert worksheet to DataTable
Dim dt As DataTable = workSheet.ToDataTable()
$vbLabelText   $csharpLabel

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

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-datatable-header.cs
// Convert worksheet to DataTable with the first row as column names
DataTable dt = workSheet.ToDataTable(true);
' Convert worksheet to DataTable with the first row as column names
Dim dt As DataTable = workSheet.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.

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-print-datatable.cs
using IronXL;
using System.Data;

// Load the Excel workbook
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Get the specified WorkSheet
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");

// Convert WorkSheet to DataTable
DataTable dt = workSheet.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();
}
Imports IronXL
Imports System.Data

' Load the Excel workbook
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")

' Get the specified WorkSheet
Private workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")

' Convert WorkSheet to DataTable
Private dt As DataTable = workSheet.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
$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. Use the ToDataSet method to turn the whole workbook into DataSet. In this example, we will see how to use the Excel file as a DataSet.

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-file-excel-to-dataset.cs
using IronXL;
using System.Data;

// Load the Excel workbook
WorkBook workBook = WorkBook.Load("sample.xlsx");

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

// Iterate through tables in the DataSet and display table names
foreach (DataTable dt in ds.Tables)
{
    Console.WriteLine(dt.TableName);
}
Imports IronXL
Imports System.Data

' Load the Excel workbook
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")

' Convert the WorkBook to a DataSet
Private ds As DataSet = workBook.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
$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.

:path=/static-assets/excel/content-code-examples/how-to/c-sharp-read-xlsx-all-excel-sheets.cs
// THIS CODE SNIPPET IS NOT AVAILABLE!
' THIS CODE SNIPPET IS NOT AVAILABLE!
$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
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.