1. IronXL
2. How-Tos
3. C# Parse Excel File

C# Parse Excel File

When using Excel Spreadsheets for application builds, we often analyze the results based on data, and need to within C# parse Excel file data into the required format to get the right results. Parsing data into different formats is made easy in the C Sharp environment with the use of IronXL, and the steps below.

C# Open Excel Worksheets

• Download IronXL for Visual Studio
• C# parse Excel data numeric values
• Parse data into boolean values
• Parse files into C# Collections including array, datatables, and datasets

How to Parse Excel File in C#

1. Install the Excel library to process Excel files.
2. Add the Excel file by opening the Workbook object.
3. Choose the default Worksheet.
4. Read values from the Excel Workbook.
5. Process the value accurately and display it.

Step 1

1. Download IronXL for Visual Studio

Your first step is to Download IronXL for Visual Studio or install using NuGet, both free methods for development projects.

Install-Package IronXL.Excel

How to Tutorial

2. Load Excel File

Next, you'll want to load the Excel file into your C# project using the using the WorkBook.Load() function of IronXL. We pass one string parameter to specify the path of Excel file as follows:

//Load Excel file
WorkBook wb = WorkBook.Load("Path");

//Load Excel file
WorkBook wb = WorkBook.Load("Path");

'Load Excel file
Dim wb As WorkBook = WorkBook.Load("Path")

The Excel file will load in wb. Now we can open a specific WorkSheet.

3. Open the Excel WorkSheet

To open a WorkSheet of an Excel file, we use the WorkBook.GetWorkSheet() function. It requires one string parameter to specify the WorkSheet name to be opened. We can use it like this:

//specify WorkSheet
WorkSheet ws = Wb.GetWorkSheet("SheetName");

//specify WorkSheet
WorkSheet ws = Wb.GetWorkSheet("SheetName");

'specify WorkSheet
Dim ws As WorkSheet = Wb.GetWorkSheet("SheetName")

wb is the WorkBook which we defined in Step 1.

4. Get Data from Excel file

Now we can easily get any type of data, in many ways, from the open Excel WorkSheet. In the following example, we see how to access a specific cell value and parse it to string:

//Access the Data by Cell Addressing
string val = ws ["Cell Address"].ToString();

//Access the Data by Cell Addressing
string val = ws ["Cell Address"].ToString();

'Access the Data by Cell Addressing
Dim val As String = ws ("Cell Address").ToString()

In the line above ws is the WorkSheet, which was defined in Step 2. This is the simple approach, but you can read more and see different examples on how to access Excel file data.

5. Parse Excel Data Into Numeric Values

Now we move to how to parse Excel file data.

First off, we look at how to deal with a numeric type of Excel data, and parse it into our required format. Use the IronXL list of parser functions below to use what's right for your project.

Here's an example of how to use some of these functions to parse Excel file data in C#.

/**
Parse into Numeric Values
anchor-parse-excel-data-into-numeric-values
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    //Parse Excel cell value into string
    string str_val = ws ["B3"].Value.ToString();
    //Parse Excel cell value into int32
    Int32 int32_val = ws ["G3"].Int32Value;
    //Parse Excel cell value into Decimal
    decimal decimal_val = ws ["E5"].DecimalValue;

    Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val);
    Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val);
    Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val);
    Console.ReadKey();
}

/**
Parse into Numeric Values
anchor-parse-excel-data-into-numeric-values
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    //Parse Excel cell value into string
    string str_val = ws ["B3"].Value.ToString();
    //Parse Excel cell value into int32
    Int32 int32_val = ws ["G3"].Int32Value;
    //Parse Excel cell value into Decimal
    decimal decimal_val = ws ["E5"].DecimalValue;

    Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val);
    Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val);
    Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val);
    Console.ReadKey();
}

'''
'''Parse into Numeric Values
'''anchor-parse-excel-data-into-numeric-values
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	'Parse Excel cell value into string
	Dim str_val As String = ws ("B3").Value.ToString()
	'Parse Excel cell value into int32
	Dim int32_val As Int32 = ws ("G3").Int32Value
	'Parse Excel cell value into Decimal
	Dim decimal_val As Decimal = ws ("E5").DecimalValue

	Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val)
	Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val)
	Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val)
	Console.ReadKey()
End Sub

This code will display the following output:

And we can see the values of the Excel file sample.xlsx here:

6. Parse Excel Data into Boolean Values

To parse Excel file data into Boolean data type, IronXL provides the BoolValue function. It can be used as follows:

/**
Parse into Boolean Values
anchor-parse-excel-data-into-boolean-values
**/
bool Val = ws ["Cell Address"].BoolValue;

/**
Parse into Boolean Values
anchor-parse-excel-data-into-boolean-values
**/
bool Val = ws ["Cell Address"].BoolValue;

'''
'''Parse into Boolean Values
'''anchor-parse-excel-data-into-boolean-values
'''*
Dim Val As Boolean = ws ("Cell Address").BoolValue

ws is the WorkSheet as described in the above example. Above function will return the value as True or False.

Note: If you want to parse cell value into boolean data type, make sure that your Excel file values are in (0 , 1) or (True , False) format.

7. Parse Excel File into C# Collections

By using IronXL, we can parse Excel file data into following type of CSharp collections:

Let's see one by one each example of how to parse Excel file data into these collections types.

7.1. Parse Excel Data Into Array

IronXL provides a simple way to parse Excel file data of a specified range into an array. For this purpose, we specify From to To cell addresses, whose data will be converted into array. It can be done as follows:

var array = WorkSheet ["From:To"].ToArray();

var array = WorkSheet ["From:To"].ToArray();

Dim array = WorkSheet ("From:To").ToArray()

If we want to access a specific item from the array, we use:

string item = array [ItemIndex].Value.ToString();

string item = array [ItemIndex].Value.ToString();

Dim item As String = array (ItemIndex).Value.ToString()

It is clear that we can easily access items by indexing. Let's see the example of how to parse Excel file data into an array and select a specific item from it.

/**
Parse into Array
anchor-parse-excel-data-into-array
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    var array = ws ["B6:F6"].ToArray();
    int item = array.Count();
    string total_items = array [0].Value.ToString();
    Console.WriteLine("First item in the array: {0}", item);
    Console.WriteLine("Total items from B6 to F6: {0}",total_items);
    Console.ReadKey();
}

/**
Parse into Array
anchor-parse-excel-data-into-array
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    var array = ws ["B6:F6"].ToArray();
    int item = array.Count();
    string total_items = array [0].Value.ToString();
    Console.WriteLine("First item in the array: {0}", item);
    Console.WriteLine("Total items from B6 to F6: {0}",total_items);
    Console.ReadKey();
}

'''
'''Parse into Array
'''anchor-parse-excel-data-into-array
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	Dim array = ws ("B6:F6").ToArray()
	Dim item As Integer = array.Count()
	Dim total_items As String = array (0).Value.ToString()
	Console.WriteLine("First item in the array: {0}", item)
	Console.WriteLine("Total items from B6 to F6: {0}",total_items)
	Console.ReadKey()
End Sub

The above code displays the following output:

The range values of the Excel file, sample.xlsx, from B6 to F6, look like this:

7.2. Parse Excel WorkSheet Into Datatable

It is the beauty of IronXL that we can easily convert a specific Excel WorkSheet into a DataTable. For this purpose, we can use the .ToDataTable() function of IronXL as follows:

DataTable dt = WorkSheet.ToDataTable();

DataTable dt = WorkSheet.ToDataTable();

Dim dt As DataTable = WorkSheet.ToDataTable()

It will parse the Excel WorkSheet into DataTable dt. In this case, if we want to use the first row of Excel file as the DataTable ColumnName then we can set it as such:

DataTable dt=WorkSheet.ToDataTable(True);

DataTable dt=WorkSheet.ToDataTable(True);

Dim dt As DataTable=WorkSheet.ToDataTable([True])

The boolean parameter of .ToDataTable() function will specify that the first row of Excel file becomes ColumnName of DataTable. You can dive deeper into working with ExcelWorksheet as DataTable in C#

And here is the example of how to parse Excel WorkSheet into DataTable:

/**
Parse into DataTable
anchor-parse-excel-worksheet-into-datatable
**/
using IronXL;
using System.Data; 
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    //parse sheet1 of sample.xlsx file into DataTable.
    //we set parameter true of ToDataTable() function,so first row of Excel file becomes columnname of DataTable
    DataTable dt = ws.ToDataTable(true); 
}

/**
Parse into DataTable
anchor-parse-excel-worksheet-into-datatable
**/
using IronXL;
using System.Data; 
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    //parse sheet1 of sample.xlsx file into DataTable.
    //we set parameter true of ToDataTable() function,so first row of Excel file becomes columnname of DataTable
    DataTable dt = ws.ToDataTable(true); 
}

'''
'''Parse into DataTable
'''anchor-parse-excel-worksheet-into-datatable
'''*
Imports IronXL
Imports System.Data
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	'parse sheet1 of sample.xlsx file into DataTable.
	'we set parameter true of ToDataTable() function,so first row of Excel file becomes columnname of DataTable
	Dim dt As DataTable = ws.ToDataTable(True)
End Sub

7.3. Parse Excel File into DataSet

If we want to parse a complete Excel file into a DataSet, then for this purpose we can use the .ToDataSet() function of IronXL. It can be used as follows:

DataSet ds = WorkBook.ToDataSet();

DataSet ds = WorkBook.ToDataSet();

Dim ds As DataSet = WorkBook.ToDataSet()

Note: If we are parsing an Excel file into a DataSet, then ALL of the WorkSheets of the Excel file become DataTables of this Dataset.

Let's see the example of how to parse Excel file into DataSet:

/**
Parse File into DataSet
anchor-parse-excel-file-into-dataset
**/
using IronXL;
using System.Data; 
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    //parse WorkBook wb into DataSet
    DataSet ds = wb.ToDataSet(); 
    //we also can get DataTable from ds which is actually WorkSheet as:
    DataTable dt=ds.Tables [0];
}

/**
Parse File into DataSet
anchor-parse-excel-file-into-dataset
**/
using IronXL;
using System.Data; 
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    //parse WorkBook wb into DataSet
    DataSet ds = wb.ToDataSet(); 
    //we also can get DataTable from ds which is actually WorkSheet as:
    DataTable dt=ds.Tables [0];
}

'''
'''Parse File into DataSet
'''anchor-parse-excel-file-into-dataset
'''*
Imports IronXL
Imports System.Data
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	'parse WorkBook wb into DataSet
	Dim ds As DataSet = wb.ToDataSet()
	'we also can get DataTable from ds which is actually WorkSheet as:
	Dim dt As DataTable=ds.Tables (0)
End Sub

You can read more about Excel SQL Dataset and working with these files.

Tutorial Quick Access

Documentation for Excel in C#

Use the IronXL documentation for working with Excel in C# to take use all the functionality, classes, namespaces, and more in your projects.

Documentation for Excel in C#