DEV Community

IronSoftware
IronSoftware

Posted on • Originally published at ironsoftware.com

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#:

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();
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

VB:

'Load Excel file
Dim wb As WorkBook = WorkBook.Load("Path")
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

VB:

'specify WorkSheet
Dim ws As WorkSheet = Wb.GetWorkSheet("SheetName")
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

VB:

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

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.

Image 1

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();
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

This code will display the following output:

Image 2

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

Image 3


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;
Enter fullscreen mode Exit fullscreen mode

VB:

Dim Val As Boolean = ws("Cell Address").BoolValue
Enter fullscreen mode Exit fullscreen mode

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:

Image 4

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();
Enter fullscreen mode Exit fullscreen mode

VB:

Dim array = WorkSheet("From:To").ToArray()
Enter fullscreen mode Exit fullscreen mode

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

C#:

string item = array[ItemIndex].Value.ToString();
Enter fullscreen mode Exit fullscreen mode

VB:

Dim item As String = array(ItemIndex).Value.ToString()
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The above code displays the following output:

Image 5

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

Image 6

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();
Enter fullscreen mode Exit fullscreen mode

VB:

Dim dt As DataTable = WorkSheet.ToDataTable()
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

VB:

Dim dt As DataTable=WorkSheet.ToDataTable([True])
Enter fullscreen mode Exit fullscreen mode

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); 
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

VB:

Dim ds As DataSet = WorkBook.ToDataSet()
Enter fullscreen mode Exit fullscreen mode

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];
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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


Top comments (0)