using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published February 27, 2023

How to Fetch Data From Excel Sheet in C#

Excel is a popular software application used for data analysis, reporting, and storage. It is essential to be able to extract data from Excel sheets for further analysis in various applications and using C#. IronXL is a feature-rich API that provides various functionalities to work with Excel files in C#. In this tutorial, we 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, we need to follow these steps:

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

Install the IronXL Library into a Visual Studio Project using NuGet Package Manager

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

IronXL provides the following features

Dedicated support from .NET engineers
Simple installation via Microsoft Visual Studio
30-day free trial for development, with licenses starting at $499
Easy integration into C# or VB.NET projects to read Excel files
Sample codes containing three Excel sheets to help you get started quickly

IronXL can create, read, and edit Excel spreadsheets

Step 1: Import the Required Libraries

First, we need to import the required libraries to our project. 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

VB C#

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

To fetch data from an Excel file, we first need 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")

VB C#

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)

VB C#

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, we need to retrieve the Excel sheet from which we want to fetch data. A workbook can contain multiple worksheets, so we need to specify the name of the worksheet we want to work with.

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

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

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

VB C#

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)

VB C#

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

Step 4: Retrieve Data from Excel Sheet

Now that we have retrieved the Excel sheet, we can retrieve data from it. IronXL provides various methods to retrieve data from an Excel sheet. Here are some of the most commonly used methods:

GetCellValue: 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.GetCellValue("A1");

object value = worksheet.GetCellValue("A1");

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

VB C#

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)

VB C#

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)

VB C#

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")

VB C#

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

Step 5: Extract Data from Excel Sheet

Now that we have retrieved the required data, we can extract it and use it in our application. IronXL provides various data types to work with the extracted data, including strings, integers, doubles, and booleans.

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

For example, to extract an integer value from cell A1, we can use the following code:

int value = (int)worksheet.GetCellValue("A1");

int value = (int)worksheet.GetCellValue("A1");

Imports System

Dim value As Integer = CInt(Math.Truncate(worksheet.GetCellValue("A1")))

VB C#

This code retrieves the value of cell A1 and casts it to an integer.

To extract a row of data, we can use the following code:

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

VB C#

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

To extract a column of data, we can use the following code:

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

VB C#

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

To extract a range of cells, we can use the following code:

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

foreach (var row in values)
{
    foreach (var value in row)
    {
        int intValue = (int)value;
        // 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)
    {
        int intValue = (int)value;
        // 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
		Dim intValue As Integer = CInt(value)
		' Do something with the extracted value
	Next value
Next row

VB C#

This code extracts the range of cells from A1 to B2 and iterates through the values in each cell, casting each value to an integer.

Step 6: Import Data to a Database

Once we have extracted the required data from the Excel sheet, we can import it to a database or any other application that requires the data. Here, we will show how to import the extracted data to a database using C#.

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path/to/excel/file.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
string sheetName = "Sheet1";
string query = "SELECT * FROM [" + sheetName + "$]";

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    using (OleDbCommand command = new OleDbCommand(query, connection))
    {
        connection.Open();
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
        {
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            // Do something with the imported data
        }
    }
}

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path/to/excel/file.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
string sheetName = "Sheet1";
string query = "SELECT * FROM [" + sheetName + "$]";

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    using (OleDbCommand command = new OleDbCommand(query, connection))
    {
        connection.Open();
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
        {
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            // Do something with the imported data
        }
    }
}

Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path/to/excel/file.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'"
Dim sheetName As String = "Sheet1"
Dim query As String = "SELECT * FROM [" & sheetName & "$]"

Using connection As New OleDbConnection(connectionString)
	Using command As New OleDbCommand(query, connection)
		connection.Open()
		Using adapter As New OleDbDataAdapter(command)
			Dim dataTable As New DataTable()
			adapter.Fill(dataTable)
			' Do something with the imported data
		End Using
	End Using
End Using

VB C#

This code imports the data from Sheet 1 of the specified Excel file into a DataTable object, which can then be used to further manipulate the data.

Licensing

IronXL is available for free during the development phase, with a watermark included in the free version. You can also use the 30-day free trial for production without any payment. If you decide to purchase IronXL, the pricing plans are as follows:

Lite Edition ($499): This option is suitable for individual developers who want to use IronXL in their projects.
IronXL Lite Licensing Features
Professional Edition ($999): The Professional Edition is ideal for companies with multiple branches across the world, supporting up to 10 locations, 10 developers, and 10 projects simultaneously.
IronXL Professional Licensing Features
Unlimited Edition ($2999): The Unlimited Edition is the best option for those who prefer not to worry about using IronXL in a large number of projects, supporting unlimited developers, locations, and projects with a lifetime license.
IronXL Unlimited Licensing Features

IronXL also offers royalty distribution, product support, and updates.

IronXL SaaS and OEM Distribution Licensing

Conclusion

In this tutorial, we explored how to fetch data from Excel sheets in C# using IronXL. We covered how to 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.

IronXL is a more user-friendly and convenient option, with small and easily memorable functions. It allows you to perform various editing and formatting tasks with just a single line of code. Although the code flow may appear complex, beginners can easily understand it. You don't require any prior knowledge or understanding of IronXL, and there is no need to install separate libraries for different platforms. Install it once, and you can use it for all your actions without any further installations.

IronXL is built on the .NET framework and provides a simple and intuitive object model to work with Excel files. We can use IronXL to work with both XLS and XLSX files, and it supports all versions of Microsoft Excel. In this tutorial, we covered the basic steps to fetch data from an Excel sheet using IronXL. We loaded an Excel file, retrieved the required worksheet, retrieved data from the worksheet, extracted data, and imported data to a database. We also provided sample codes in C# for each step.

IronXL is a powerful tool for working with Excel files in C#. It provides a rich set of features, including support for multiple worksheets, cell ranges, and unmanaged memory. Additionally, it is easy to use and integrates well with Microsoft Visual Studio. Note that this tutorial only scratched the surface of what IronXL can do. IronXL provides a wide range of additional features, such as cell formatting, chart creation, and pivot tables, to name a few.

If you need to work with Excel files in your C# applications, IronXL is definitely worth checking out.