How to Fetch Data From Excel Sheet in C#

IronXL is a feature-rich API that provides various functionalities to work with Excel files in C#. This tutorial will explore how to fetch data from Excel sheets in C# using IronXL.

To follow this tutorial, you need to have the following:

• Microsoft Visual Studio installed on your machine
• Basic knowledge of C# programming
• IronXL library installed in your project

You can download IronXL from the official website or through the NuGet Package Manager in Visual Studio.

Installing IronXL in Visual Studio

To install IronXL on a C# project, please follow these steps:

1. Open Microsoft Visual Studio.
2. Create a new project or open an existing project.
3. Right-click on the project in the Solution Explorer and select "Manage NuGet Packages".
4. In the "NuGet Package Manager" window, select the "Browse" tab.
5. In the search box, type "IronXL" and press Enter.
6. Select "IronXL.Excel" and click the "Install" button.
7. Click the "I Accept" button to accept the license agreement.
8. Wait for the installation to complete.

Installing the IronXL Library in Visual Studio Project

Once these steps are taken, IronXL should be installed and ready to use in your C# project.

Step 1: Import the Required Libraries

Add the following sample code to import the IronXL library and other necessary libraries.

using IronXL;
using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

using IronXL;
using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

Imports IronXL
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms

Using the IronXL namespace provides all the necessary classes and functions to work with Excel files using the IronXL library.

Step 2: Load the Excel File

The first step is to load the file. IronXL provides a WorkBook class to load an Excel file. We can use the WorkBooks class to open an existing Excel file or create a new workbook.

To load an existing Excel file, use the following code:

WorkBook workbook = WorkBook.Load("path/to/excel/file.xlsx");

WorkBook workbook = WorkBook.Load("path/to/excel/file.xlsx");

Dim workbook As WorkBook = WorkBook.Load("path/to/excel/file.xlsx")

This code loads the specified Excel file into a WorkBook object.

To create a new WorkBook, use the following sample code:

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

This code creates a new workbook in the specified file format (here, XLSX).

IronXL can load and create Excel Spreadsheets in the .XLS and the .XLSX file formats

Step 3: Retrieve the Excel Sheet

After loading the Excel file, it is necessary to indicate the Excel sheet from which to fetch data. A WorkBook can contain multiple WorkSheet objects. Each WorkSheet needs to specify the name of the worksheet before accessing data.

WorkSheet worksheet = workbook.WorkSheets ["Sheet1"];

WorkSheet worksheet = workbook.WorkSheets ["Sheet1"];

Dim worksheet As WorkSheet = workbook.WorkSheets ("Sheet1")

This code retrieves the first worksheet (with worksheet name Sheet1) from the workbook. If you want to retrieve a worksheet by index, you can use the following code:

WorkSheet worksheet = workbook.WorkSheets [0];

WorkSheet worksheet = workbook.WorkSheets [0];

Dim worksheet As WorkSheet = workbook.WorkSheets (0)

This code retrieves the first worksheet from the workbook, assuming that it exists.

Step 4: Retrieve Data from Excel Sheet

From the retrieved Excel sheet, accessing data becomes possible. IronXL provides various methods to retrieve data from an Excel sheet. Here are some of the most commonly used methods:

• Indexer operator []: Retrieves the value of a single cell.
• GetRow: Retrieves a single row of data.
• GetColumn: Retrieves a single column of data.
• GetRange: Retrieves a range of cells.

To retrieve a single cell's value, use the following code:

object value = worksheet ["A1"].Value;

object value = worksheet ["A1"].Value;

Dim value As Object = worksheet ("A1").Value

This code retrieves the value of cell A1 in the worksheet.

To retrieve a single row of data, use the following code:

var dataRow = worksheet.GetRow(1);

var dataRow = worksheet.GetRow(1);

Dim dataRow = worksheet.GetRow(1)

This code retrieves the first row of data in the worksheet.

To retrieve a single column of data, use the following code:

var dataColumn = worksheet.GetColumn(1);

var dataColumn = worksheet.GetColumn(1);

Dim dataColumn = worksheet.GetColumn(1)

This code retrieves the first column of data in the worksheet.

To retrieve a range of cells, use the following code:

var cellRange = worksheet.GetRange("A1:B2");

var cellRange = worksheet.GetRange("A1:B2");

Dim cellRange = worksheet.GetRange("A1:B2")

This code retrieves the range of cells from A1 to B2 in the worksheet.

Step 5: Extract Data from Excel Sheet

To extract a single value from the retrieved data, it needs to be cast to the appropriate data type.

The following code shows how to extract an integer value from cell A1:

int value = worksheet ["A1"].IntValue;

int value = worksheet ["A1"].IntValue;

Dim value As Integer = worksheet ("A1").IntValue

The following code can be used to extract a row of data:

var dataRow = worksheet.GetRow(1);
var values = dataRow.Values;

foreach (var value in values)
{
    int intValue = (int)value;
    // Do something with the extracted value
}

var dataRow = worksheet.GetRow(1);
var values = dataRow.Values;

foreach (var value in values)
{
    int intValue = (int)value;
    // Do something with the extracted value
}

Dim dataRow = worksheet.GetRow(1)
Dim values = dataRow.Values

For Each value In values
	Dim intValue As Integer = CInt(value)
	' Do something with the extracted value
Next value

This code extracts the first row of data and iterates through the values in the row, casting each value to an integer.

Next, the following code shows how to extract a column of data:

var dataColumn = worksheet.GetColumn(1);
var values = dataColumn.Values;

foreach (var value in values)
{
    string strValue = (string)value;
    // Do something with the extracted value
}

var dataColumn = worksheet.GetColumn(1);
var values = dataColumn.Values;

foreach (var value in values)
{
    string strValue = (string)value;
    // Do something with the extracted value
}

Dim dataColumn = worksheet.GetColumn(1)
Dim values = dataColumn.Values

For Each value In values
	Dim strValue As String = CStr(value)
	' Do something with the extracted value
Next value

This code extracts the first column of data and iterates through the values in the column, casting each value to a string.

The next example demonstrates how to extract a range of cells:

var cellRange = worksheet.GetRange("A1:B2");
var values = cellRange.Values;

foreach (var row in values)
{
    foreach (var value in row)
    {
        // Do something with the extracted value
    }
}

var cellRange = worksheet.GetRange("A1:B2");
var values = cellRange.Values;

foreach (var row in values)
{
    foreach (var value in row)
    {
        // Do something with the extracted value
    }
}

Dim cellRange = worksheet.GetRange("A1:B2")
Dim values = cellRange.Values

For Each row In values
	For Each value In row
		' Do something with the extracted value
	Next value
Next row

This code extracts the range of cells from A1 to B2 and iterates through the values in each cell.

Conclusion

This tutorial explored how to fetch data from Excel sheets in C# using IronXL as well as load an Excel file, retrieve the required worksheet, retrieve data from the worksheet, extract data, and import data to a database. IronXL provides a feature-rich API to work with Excel files, making it easy to integrate Excel data into C# applications. With IronXL, we can read and write Excel files and interact with their data programmatically. Additionally, we can use IronXL to automate Excel tasks, such as generating reports, creating charts, and formatting cells.

More about IronXL

IronXL is a user-friendly and convenient library for reading and writing to Excel & CSV files, with small and easily memorable functions.

This tutorial only scratched the surface of what IronXL can do. IronXL provides a wide range of additional features, such as cell formatting, math funtions, and conditional formatting to name a few.

Notable Benefits of using IronXL

• Dedicated support from .NET engineers, cross-platform supports, Azure, AWS, and Docker.
• Simple installation via Microsoft Visual Studio
• free trial for development, with licenses starting at $749
• Easy integration into C# or VB.NET projects