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
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
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")
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)
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
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()
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()
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
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
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
It will display the following result:
With the Excel file Sample.xlsx
:
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
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)
This code will display the following output:
And this is how the Excel file Sample.xlsx
will look:
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()
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)
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
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
The output of the above code will look like this:
And the Excel file Sample.xlsx
will look like this:
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!
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 IronXLFrequently 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.