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#:
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();
}
VB:
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
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.
PM > 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:
C#:
//Load Excel file
WorkBook wb = WorkBook.Load("Path");
VB:
'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:
C#:
//specify WorkSheet
WorkSheet ws = Wb.GetWorkSheet("SheetName");
VB:
'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:
C#:
//Access the Data by Cell Addressing
string val = ws["Cell Address"].ToString();
VB:
'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#.
C#:
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();
}
VB:
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:
C#:
bool Val = ws["Cell Address"].BoolValue;
VB:
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 types 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:
C#:
var array = WorkSheet["From:To"].ToArray();
VB:
Dim array = WorkSheet("From:To").ToArray()
If we want to access a specific item from the array, we use:
C#:
string item = array[ItemIndex].Value.ToString();
VB:
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.
C#:
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();
}
VB:
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:
C#:
DataTable dt = WorkSheet.ToDataTable();
VB:
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:
C#:
DataTable dt=WorkSheet.ToDataTable(True);
VB:
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:
C#:
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);
}
VB:
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:
C#:
DataSet ds = WorkBook.ToDataSet();
VB:
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:
C#:
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];
}
VB:
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.
Top comments (0)