How to Import Excel Files in C#

1. Import Data with the IronXL Library

Import data using the functions provided by the IronXL Excel library, which we'll be using in this tutorial. The software is available free for development.

Install into your C# Project via DLL Download or navigate using the NuGet package.

Install-Package IronXL.Excel

How to Tutorial

2. Access WorkSheet for Project

For our project needs today, we will be importing Excel data into our C# application, using the IronXL software installed in step 1.

For step 2, we will load our Excel WorkBook in our CSharp project by using the WorkBook.Load() function of IronXL. We pass the path of the Excel WorkBook as a string parameter in this function, like this:

// Load Excel file
WorkBook wb = WorkBook.Load("Path");

// Load Excel file
WorkBook wb = WorkBook.Load("Path");

' Load Excel file
Dim wb As WorkBook = WorkBook.Load("Path")

The Excel file at the specified path will be loaded into wb.

Next, we need to access a specific WorkSheet of the Excel file whose data will be imported into the project. For this purpose, we can use the GetWorkSheet() function of IronXL, passing the sheet name as a string parameter to specify which sheet of the WorkBook to import.

// Specify sheet name of Excel WorkBook
WorkSheet ws = wb.GetWorkSheet("SheetName");

// Specify sheet name of Excel WorkBook
WorkSheet ws = wb.GetWorkSheet("SheetName");

' Specify sheet name of Excel WorkBook
Dim ws As WorkSheet = wb.GetWorkSheet("SheetName")

The WorkSheet will be imported as ws, and wb is the WorkBook which we have defined in the above code sample.

There are also the following alternative ways to import an Excel WorkSheet into the project.

// Import WorkSheet by various methods

// by sheet indexing
WorkSheet mySheet = wb.WorkSheets[SheetIndex];

// get default WorkSheet
WorkSheet defaultSheet = wb.DefaultWorkSheet;

// get first WorkSheet
WorkSheet firstSheet = wb.WorkSheets.First();

// for the first or default sheet
WorkSheet firstOrDefaultSheet = wb.WorkSheets.FirstOrDefault();

// Import WorkSheet by various methods

// by sheet indexing
WorkSheet mySheet = wb.WorkSheets[SheetIndex];

// get default WorkSheet
WorkSheet defaultSheet = wb.DefaultWorkSheet;

// get first WorkSheet
WorkSheet firstSheet = wb.WorkSheets.First();

// for the first or default sheet
WorkSheet firstOrDefaultSheet = wb.WorkSheets.FirstOrDefault();

' Import WorkSheet by various methods

' by sheet indexing
Dim mySheet As WorkSheet = wb.WorkSheets(SheetIndex)

' get default WorkSheet
Dim defaultSheet As WorkSheet = wb.DefaultWorkSheet

' get first WorkSheet
Dim firstSheet As WorkSheet = wb.WorkSheets.First()

' for the first or default sheet
Dim firstOrDefaultSheet As WorkSheet = wb.WorkSheets.FirstOrDefault()

Now, we can easily import any type of data from the specified Excel files. Let's see all the possible aspects which we use to import Excel file data in our project.

3. Import Excel Data in C#

This is the basic aspect of importing Excel file data into our project.

For this purpose, we can use a cell addressing system to specify which cell data we need to import. It will return the value of a specific cell address from the Excel file.

var cellValue = ws["Cell Address"];

var cellValue = ws["Cell Address"];

Dim cellValue = ws("Cell Address")

We can also import cell data from Excel files by using row and column indexes. This line of code will return the value of the specified row and column index.

var cellValueByIndex = ws.Rows[RowIndex].Columns[ColumnIndex];

var cellValueByIndex = ws.Rows[RowIndex].Columns[ColumnIndex];

Dim cellValueByIndex = ws.Rows(RowIndex).Columns(ColumnIndex)

If we want to assign imported cell values to variables, we can use this code.

// Import Data by Cell Address
// by cell addressing
string val = ws["Cell Address"].ToString();

// by row and column indexing
string valWithIndexing = ws.Rows[RowIndex].Columns[ColumnIndex].Value.ToString();

// Import Data by Cell Address
// by cell addressing
string val = ws["Cell Address"].ToString();

// by row and column indexing
string valWithIndexing = ws.Rows[RowIndex].Columns[ColumnIndex].Value.ToString();

' Import Data by Cell Address
' by cell addressing
Dim val As String = ws("Cell Address").ToString()

' by row and column indexing
Dim valWithIndexing As String = ws.Rows(RowIndex).Columns(ColumnIndex).Value.ToString()

In the above examples, the row and column index starts at 0.

4. Import Excel Data of Specific Range

If we want to import data in a specific range from an Excel WorkBook, it can easily be done by using the range function. To define the range, we need to describe the starting and ending cell addresses. This way, it will return all the cell values within the specified range.

var rangeData = ws["Starting Cell Address:Ending Cell Address"];

var rangeData = ws["Starting Cell Address:Ending Cell Address"];

Dim rangeData = ws("Starting Cell Address:Ending Cell Address")

For more information about working with range in Excel files, check out the provided code examples.

:path=/static-assets/excel/content-code-examples/how-to/csharp-import-excel-import.cs

using IronXL;
using System;

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

// Specify WorkSheet
WorkSheet ws = wb.GetWorkSheet("Sheet1");

// Import data of specific cell
string val = ws["A4"].Value.ToString();
Console.WriteLine("Import Value of A4 Cell address: {0}", val);

Console.WriteLine("import Values in Range From B3 To B9 :\n");

// Import data in specific range
foreach (var item in ws["B3:B9"])
{
    Console.WriteLine(item.Value.ToString());
}

Console.ReadKey();

IRON VB CONVERTER ERROR developers@ironsoftware.com

The above code displays the following output:

With the values of Excel file sample.xlsx as:

5. Import Excel Data by Aggregate Functions

We can also apply aggregate functions to Excel files and import the resulting data from these aggregate functions. Here are some examples of the different functions and how to use them.

• Sum()

  // To find the sum of a specific cell range
  var sum = ws["Starting Cell Address:Ending Cell Address"].Sum();

  // To find the sum of a specific cell range
  var sum = ws["Starting Cell Address:Ending Cell Address"].Sum();

  ' To find the sum of a specific cell range
  Dim sum = ws("Starting Cell Address:Ending Cell Address").Sum()

  $vbLabelText   $csharpLabel

• Average()

  // To find the average of a specific cell range
  var average = ws["Starting Cell Address:Ending Cell Address"].Avg();

  // To find the average of a specific cell range
  var average = ws["Starting Cell Address:Ending Cell Address"].Avg();

  ' To find the average of a specific cell range
  Dim average = ws("Starting Cell Address:Ending Cell Address").Avg()

  $vbLabelText   $csharpLabel

• Min()

  // To find the minimum in a specific cell range
  var minimum = ws["Starting Cell Address:Ending Cell Address"].Min();

  // To find the minimum in a specific cell range
  var minimum = ws["Starting Cell Address:Ending Cell Address"].Min();

  ' To find the minimum in a specific cell range
  Dim minimum = ws("Starting Cell Address:Ending Cell Address").Min()

  $vbLabelText   $csharpLabel

• Max()

  // To find the maximum in a specific cell range
  var maximum = ws["Starting Cell Address:Ending Cell Address"].Max();

  // To find the maximum in a specific cell range
  var maximum = ws["Starting Cell Address:Ending Cell Address"].Max();

  ' To find the maximum in a specific cell range
  Dim maximum = ws("Starting Cell Address:Ending Cell Address").Max()

  $vbLabelText   $csharpLabel

You can read more about working with aggregate functions in Excel for C# and learn more about pulling data in different methods.

Let's see an example of how to import Excel file data by applying these functions.

:path=/static-assets/excel/content-code-examples/how-to/csharp-import-excel-math-functions.cs

using IronXL;
using System;

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

// Specify WorkSheet
WorkSheet ws = wb.GetWorkSheet("Sheet1");

// Import Excel file data by applying aggregate functions
decimal sum = ws["D2:D9"].Sum();
decimal avg = ws["D2:D9"].Avg();
decimal min = ws["D2:D9"].Min();
decimal max = ws["D2:D9"].Max();

Console.WriteLine("Sum From D2 To D9: {0}", sum);
Console.WriteLine("Avg From D2 To D9: {0}", avg);
Console.WriteLine("Min From D2 To D9: {0}", min);
Console.WriteLine("Max From D2 To D9: {0}", max);

Console.ReadKey();

IRON VB CONVERTER ERROR developers@ironsoftware.com

The above code gives us this output:

And our file sample.xlsx will have these values:

6. Import Complete Excel File Data

If we want to import complete Excel file data into our C# project, we can first parse our loaded WorkBook into a DataSet. In this way, the complete Excel data would be imported into the DataSet, and WorkSheets on Excel files become DataTables within that DataSet. Here it is in action:

// Import WorkBook into DataSet
DataSet ds = wb.ToDataSet();

// Import WorkBook into DataSet
DataSet ds = wb.ToDataSet();

' Import WorkBook into DataSet
Dim ds As DataSet = wb.ToDataSet()

In this way, our specified WorkSheet will be imported into a DataSet that we can use according to our requirements.

Often, the first column of an Excel file is used as ColumnName. In this case, we need to make the first column a DataTable ColumnName. To do this, we set the boolean parameter of ToDataSet() function of IronXL as follows:

// Import WorkBook into DataSet with first row as ColumnNames
DataSet ds = wb.ToDataSet(true);

// Import WorkBook into DataSet with first row as ColumnNames
DataSet ds = wb.ToDataSet(true);

' Import WorkBook into DataSet with first row as ColumnNames
Dim ds As DataSet = wb.ToDataSet(True)

This will make the first column of the Excel file as a DataTable ColumnName.

Let's see a complete example of how to import Excel data into a DataSet and use the first column of an Excel WorkSheet as a DataTable ColumnName:

:path=/static-assets/excel/content-code-examples/how-to/csharp-import-excel-dataset.cs

using IronXL;
using System;
using System.Data;

WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");

// Import Excel data into a DataSet
DataSet ds = wb.ToDataSet(true);

Console.WriteLine("Excel file data imported to dataset successfully.");
Console.ReadKey();

IRON VB CONVERTER ERROR developers@ironsoftware.com

Working with Excel Dataset and Datatable functions can be complicated, but we have more examples available for incorporating file data into your C# project.

Library Quick Access

Explore the IronXL Reference

Learn more about pulling Excel data via cells, range, datasets and datatables in our full documentation API Reference for IronXl.

Explore the IronXL Reference