Published December 10, 2021

Reading CSV Files in C#: a Tutorial

Working with various Excel formats often requires reading data and then reconfiguring it programmatically. In this article, we will learn how to read a CSV file and parse data from an Excel spreadsheet in C# using IronXL, the perfect tool for the job.

What is CSV?

CSVs (Comma Separated Values) are very popular import and export data formats used in spreadsheets and databases. Usually, data are stored on one line and separated by commas. It is necessary to use our own CSV Help package for our projects. CSV is a simple data format but there can be many differences. These may include different delimiters, new lines, or quotes. It is possible to Read and Write CSV Data with the help of the CSV Help library.

1. How to read a CSV File in C#

Before you can make use of IronXL to read CSV files in MVC or ASP or .NET core, you need to install it. Here is a quick walk-through.

In Visual Studio, select the Project menu
Manage NuGet Packages
Search for IronXL.Excel
Install

When you need to read CSV files in C#, IronXL is the perfect tool. You can read a CSV file with commas, or any other delimiter, as seen in the code segments below.

 WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
    WorkSheet ws = workbook.DefaultWorkSheet;
    workbook.SaveAs("Csv_To_Excel.xlsx");

 WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
    WorkSheet ws = workbook.DefaultWorkSheet;
    workbook.SaveAs("Csv_To_Excel.xlsx");

Dim workbook As WorkBook = WorkBook.LoadCSV("Weather.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
	Dim ws As WorkSheet = workbook.DefaultWorkSheet
	workbook.SaveAs("Csv_To_Excel.xlsx")

VB C#

Output:

Code Explanation:

A Workbook object is created. The LoadCSV method for the Workbook object is then used to specify the name of the CSV, its format, which delimiters are used in the CSV file being read. In this case, commas are used as delimiters.

A Worksheet object is then created. This is where the contents of the CSV file will be placed. Then the file is saved under a new name and format.

2. IronXL for Excel Files

Use IronXL for your project as a streamlined 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.

PM > Install-Package IronXL.Excel

3. Load Workbook and Access Worksheet

WorkBook is the class of IronXL whose object provides full access to the Excel file and all of its functions. For example, if we want to access an Excel file, we'd use the code:

WorkBook wb = WorkBook.Load("sample.xlsx");//Excel file path

WorkBook wb = WorkBook.Load("sample.xlsx");//Excel file path

Dim wb As WorkBook = WorkBook.Load("sample.xlsx") 'Excel file path

VB C#

To access the specific WorkSheet of an Excel file, IronXL provides the WorkSheet class.

WorkSheet ws = wb.GetWorkSheet("Sheet1"); //by sheet name

WorkSheet ws = wb.GetWorkSheet("Sheet1"); //by sheet name

Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1") 'by sheet name

VB C#

Once you have obtained the ExcelSheet ws, you can extract any type of data from it and perform all Excel functions on it. Data can be accessed from the ExcelSheet ws in this process:

using IronXL;
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   foreach (var cell in ws["A2:A10"])
   {
       Console.WriteLine("value is: {0}", cell.Text);
   }
   Console.ReadKey();
}

using IronXL;
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   foreach (var cell in ws["A2:A10"])
   {
       Console.WriteLine("value is: {0}", cell.Text);
   }
   Console.ReadKey();
}

Imports IronXL
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
   Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
   For Each cell In ws("A2:A10")
	   Console.WriteLine("value is: {0}", cell.Text)
   Next cell
   Console.ReadKey()
End Sub

VB C#

4. Reading an Excel WorkSheet as a DataTable

Using IronXL, it is very easy to operate with an Excel WorkSheet as a DataTable.

DataTable dt=WorkSheet.ToDataTable();

DataTable dt=WorkSheet.ToDataTable();

Dim dt As DataTable=WorkSheet.ToDataTable()

VB C#

Use the following namespace

using IronXL;
using System.Data;

using IronXL;
using System.Data;

Imports IronXL
Imports System.Data

VB C#

Write the following code:

static void Main(string[] args)
{
   WorkBook wb = WorkBook.Load("Weather.xlsx"); // Your Excel file Name
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   DataTable dt = ws.ToDataTable(true);//parse sheet1 of sample.xlsx file into datatable
   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();
       }
    }

static void Main(string[] args)
{
   WorkBook wb = WorkBook.Load("Weather.xlsx"); // Your Excel file Name
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   DataTable dt = ws.ToDataTable(true);//parse sheet1 of sample.xlsx file into datatable
   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();
       }
    }

Shared Sub Main(ByVal args() As String)
   Dim wb As WorkBook = WorkBook.Load("Weather.xlsx") ' Your Excel file Name
   Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
   Dim dt As DataTable = ws.ToDataTable(True) 'parse sheet1 of sample.xlsx file into datatable
   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
End Sub

VB C#

In this example, we will look at how to use an Excel file as a DataSet.

static void Main(string[] args)
{          
   WorkBook wb = WorkBook.Load("sample.xlsx");
   DataSet ds = wb.ToDataSet(); //Parse WorkBook wb into DataSet
   foreach (DataTable dt in ds.Tables)
   {
       Console.WriteLine(dt.TableName);
   }
}

static void Main(string[] args)
{          
   WorkBook wb = WorkBook.Load("sample.xlsx");
   DataSet ds = wb.ToDataSet(); //Parse WorkBook wb into DataSet
   foreach (DataTable dt in ds.Tables)
   {
       Console.WriteLine(dt.TableName);
   }
}

Shared Sub Main(ByVal args() As String)
   Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
   Dim ds As DataSet = wb.ToDataSet() 'Parse WorkBook wb into DataSet
   For Each dt As DataTable In ds.Tables
	   Console.WriteLine(dt.TableName)
   Next dt
End Sub

VB C#

Let's look at another example of how to access each cell value across all ExcelSheets. Here, we can access each cell value of every WorkSheet in an Excel file.

static void Main(string[] args)
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("Weather.xlsx");
DataSet ds = wb.ToDataSet();//behave complete Excel file as DataSet
foreach (DataTable dt in ds.Tables)//behave Excel WorkSheet as DataTable.
{
   foreach (DataRow row in dt.Rows)//corresponding Sheet's Rows
   {
       for (int i = 0; i < dt.Columns.Count; i++)//Sheet columns of corresponding row
       {
           Console.Write(row[i] + "  ");
       }
Console.WriteLine();
   }
}

static void Main(string[] args)
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("Weather.xlsx");
DataSet ds = wb.ToDataSet();//behave complete Excel file as DataSet
foreach (DataTable dt in ds.Tables)//behave Excel WorkSheet as DataTable.
{
   foreach (DataRow row in dt.Rows)//corresponding Sheet's Rows
   {
       for (int i = 0; i < dt.Columns.Count; i++)//Sheet columns of corresponding row
       {
           Console.Write(row[i] + "  ");
       }
Console.WriteLine();
   }
}

Shared void Main(String() args) static Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("Weather.xlsx")
Dim ds As DataSet = wb.ToDataSet() 'behave complete Excel file as DataSet
For Each dt As DataTable In ds.Tables 'behave Excel WorkSheet as DataTable.
   For Each row As DataRow In dt.Rows 'corresponding Sheet's Rows
	   For i As Integer = 0 To dt.Columns.Count - 1 'Sheet columns of corresponding row
		   Console.Write(row(i) & "  ")
	   Next i
Console.WriteLine()
   Next row
Next dt

VB C#

5. CSV parsing in C# .NET:

CSV's have an abundance of issues with how line breaks are handled in fields, or how fields can be contained in quotes that completely block a simple string split approach. I have recently discovered the following options when converting CSV's to C#. NET: It's the first time I have ever used just one string. Split (‘) instead of a simple. Strings. Split() to separate the values in a comma. In this article, we will look at the best ways in which C++ has a CSV parsing feature in C.XcPC.NET.

6. Reading CSV Data in C# Records

This process advances the reader through the next file. We read the CSV field files in trygetfield. We use the read function on field fields of the CSV files as record fields.

WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
            WorkSheet ws = workbook.DefaultWorkSheet;
    DataTable dt = ws.ToDataTable(true);//parse sheet1 of sample.xlsx file into datatable
            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();
            }

WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
            WorkSheet ws = workbook.DefaultWorkSheet;
    DataTable dt = ws.ToDataTable(true);//parse sheet1 of sample.xlsx file into datatable
            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();
            }

Dim workbook As WorkBook = WorkBook.LoadCSV("Weather.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
			Dim ws As WorkSheet = workbook.DefaultWorkSheet
	Dim dt As DataTable = ws.ToDataTable(True) 'parse sheet1 of sample.xlsx file into datatable
			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

VB C#

7. Getting Data from Excel Files

Now we can easily get any type of data, using a variety of methods, from the open Excel WorkSheet. In the following example, we can 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()

VB C#

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.

8. How to Parse Excel Files in C#

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

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();
}

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

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();
}

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

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

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'@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

VB C#

9. How to Parse Excel Data Into Numeric & Boolean Values

Now we move to how to parse Excel file data. First, we look at how to deal with numeric Excel data, and then how to parse it into our required format.

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();
}

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();
}

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

VB C#

This code will display the following output:

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

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

bool Val = ws["Cell Address"].BoolValue;

bool Val = ws["Cell Address"].BoolValue;

Dim Val As Boolean = ws("Cell Address").BoolValue

VB C#

10. How to Parse Excel Files into C# Collections

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();
}

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();
}

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

VB C#

10.1 How to Parse an Excel WorkSheet into a DataTable:

One excellent feature of IronXL is 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:

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);
}

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);
}

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

VB C#

10.2 How to Parse an Excel File into a DataSet:

If we want to parse a complete Excel file into a DataSet, then for this purpose we can use the .ToDataSet() function in IronXL.

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];
}

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];
}

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

VB C#

10.3 Reading Excel Data within a Specific Range:

IronXL provides an intelligent method for reading Excel file data within a specific range. The range can be applied to both rows and columns.

static void Main(string[] args)
{
   WorkBook wb = WorkBook.Load("sample.xlsx");
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   //get specified range values by loop
   foreach (var item in ws["B3:B8"])
   {
       Console.WriteLine("Value is: {0}", item);
   }
   Console.ReadKey();
}

static void Main(string[] args)
{
   WorkBook wb = WorkBook.Load("sample.xlsx");
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   //get specified range values by loop
   foreach (var item in ws["B3:B8"])
   {
       Console.WriteLine("Value is: {0}", item);
   }
   Console.ReadKey();
}

Shared Sub Main(ByVal args() As String)
   Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
   Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
   'get specified range values by loop
   For Each item In ws("B3:B8")
	   Console.WriteLine("Value is: {0}", item)
   Next item
   Console.ReadKey()
End Sub

VB C#

The above code displays the following output:

And produces the Excel file sample.xlsx values:

11. How to Read Boolean Data in an Excel File

In application development, we need to make decisions based on the Boolean data type in Excel files.

static void Main(string[] args)
{
   WorkBook wb = WorkBook.Load("sample.xlsx");
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   foreach (var item in ws["G1:G10"])
   {
   Console.WriteLine("  Condition is: {0}", item.BoolValue);
   }
   Console.ReadKey();
}

static void Main(string[] args)
{
   WorkBook wb = WorkBook.Load("sample.xlsx");
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   foreach (var item in ws["G1:G10"])
   {
   Console.WriteLine("  Condition is: {0}", item.BoolValue);
   }
   Console.ReadKey();
}

Shared Sub Main(ByVal args() As String)
   Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
   Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
   For Each item In ws("G1:G10")
   Console.WriteLine("  Condition is: {0}", item.BoolValue)
   Next item
   Console.ReadKey()
End Sub

VB C#

From this we get the output:

And the Excel file sample.xlsx with values from C1 to C10:

12. How to Read a Complete Excel WorkSheet

It is simple to read a complete Excel WorkSheet by using rows and columns indexes. For this purpose, we use two loops: one for traversing all rows, and the second for traversing all columns of a specific row. Then we can easily obtain all the cell values within the entire Excel WorkSheet.

static void Main(string[] args)
{
   WorkBook wb = WorkBook.Load("Weather.xlsx"); // your Excel File Name
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   //Traverse all rows of Excel WorkSheet
for (int i = 0;  i< ws.Rows.Count(); i++)
   {
       //Traverse all columns of specific Row
       for (int j = 0; j < ws.Columns.Count(); j++)
       {
           //Get the values
           string val = ws.Rows[i].Columns[j].Value.ToString();
           Console.WriteLine("Value of Row {0} and Column {1} is: {2}", i, j,val);
       }
   }
   Console.ReadKey();
}

static void Main(string[] args)
{
   WorkBook wb = WorkBook.Load("Weather.xlsx"); // your Excel File Name
   WorkSheet ws = wb.GetWorkSheet("Sheet1");
   //Traverse all rows of Excel WorkSheet
for (int i = 0;  i< ws.Rows.Count(); i++)
   {
       //Traverse all columns of specific Row
       for (int j = 0; j < ws.Columns.Count(); j++)
       {
           //Get the values
           string val = ws.Rows[i].Columns[j].Value.ToString();
           Console.WriteLine("Value of Row {0} and Column {1} is: {2}", i, j,val);
       }
   }
   Console.ReadKey();
}

Shared Sub Main(ByVal args() As String)
   Dim wb As WorkBook = WorkBook.Load("Weather.xlsx") ' your Excel File Name
   Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
   'Traverse all rows of Excel WorkSheet
For i As Integer = 0 To ws.Rows.Count() - 1
	   'Traverse all columns of specific Row
	   For j As Integer = 0 To ws.Columns.Count() - 1
		   'Get the values
		   Dim val As String = ws.Rows(i).Columns(j).Value.ToString()
		   Console.WriteLine("Value of Row {0} and Column {1} is: {2}", i, j,val)
	   Next j
Next i
   Console.ReadKey()
End Sub

VB C#

13. How to Read Excel Files without Interop

IronXL is an Excel Library for C# and .NET which allows developers to read and edit Excel data from XLS and XLSX Documents without using Microsoft.Office.Interop.Excel.

The API allows us to create, read, manipulate, save and export Excel files intuitively for:

.NET Framework 4.5+
.NET Core 2+
.NET Standard
Xamarin
Windows Mobile
Mono
& Azure Cloud hosting

Add the following name space:

using IronXL;
using System;
using System.Linq;

using IronXL;
using System;
using System.Linq;

Imports IronXL
Imports System
Imports System.Linq

VB C#

Now write the following code inside the main function.

WorkBook workbook = WorkBook.Load("Weather.xlsx");
            WorkSheet sheet = workbook.WorkSheets.First();

            //Select cells easily in Excel notation and return the calculated value
            int cellValue = sheet["A2"].IntValue;
            // Read from Ranges of cells elegantly.
            foreach (var cell in sheet["A2:A10"])
            {
                Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
            }

WorkBook workbook = WorkBook.Load("Weather.xlsx");
            WorkSheet sheet = workbook.WorkSheets.First();

            //Select cells easily in Excel notation and return the calculated value
            int cellValue = sheet["A2"].IntValue;
            // Read from Ranges of cells elegantly.
            foreach (var cell in sheet["A2:A10"])
            {
                Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
            }

Dim workbook As WorkBook = WorkBook.Load("Weather.xlsx")
			Dim sheet As WorkSheet = workbook.WorkSheets.First()

			'Select cells easily in Excel notation and return the calculated value
			Dim cellValue As Integer = sheet("A2").IntValue
			' Read from Ranges of cells elegantly.
			For Each cell In sheet("A2:A10")
				Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
			Next cell

VB C#

IronXL also fully supports ASP.NET, MVC, Windows, macOS, Linux, iOS, Android and Windows Mobile application development.

14. Conclusion and Iron XL Special Offer

In addition to CSV parsing in C#, IronXL converts CSVs to Excel with just two lines of code!

Using C# or VB.NET, it is very easy to use IronXL’s Excel API without the need for Interop. You can read, edit, and create Excel spreadsheets or work with other Excel formats such as XLS/XLSX/CSV/TSV. With the support of multiple frameworks, you can buy 5 products for the price of two. Click here for further information.

You can download the software product from this link.