DEV Community

IronSoftware
IronSoftware

Posted on • Originally published at ironsoftware.com

C# Import Excel

As developers, we often need to import data from Excel files and use it to fulfill our application and data management requirements. Without requiring many lines of code, IronXL gives us an easy way to import exactly the data we need directly into a C# project and then manipulate it programmatically.

C#:

//by sheet indexing
WorkBook.WorkSheets[SheetIndex];
//get default  WorkSheet
WorkBook.DefaultWorkSheet;
//get first WorkSheet
WorkBook.WorkSheets.First();
//for the first or default sheet:
WorkBook.WorkSheets.FirstOrDefault();
Enter fullscreen mode Exit fullscreen mode

VB:

'by sheet indexing
WorkBook.WorkSheets(SheetIndex)
'get default  WorkSheet
WorkBook.DefaultWorkSheet
'get first WorkSheet
WorkBook.WorkSheets.First()
'for the first or default sheet:
WorkBook.WorkSheets.FirstOrDefault()
Enter fullscreen mode Exit fullscreen mode

Step 1

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.

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

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 of the specified path will be loaded in 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. We will pass sheet name as a string parameter in this function to specify which sheet of WorkBook to be imported.

C#:

//specify sheet name of Excel WorkBook
WorkSheet ws = wb.GetWorkSheet("SheetName");
Enter fullscreen mode Exit fullscreen mode

VB:

'specify sheet name of Excel WorkBook
Dim ws As WorkSheet = wb.GetWorkSheet("SheetName")
Enter fullscreen mode Exit fullscreen mode

The WorkSheet will import 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.

C#:

//by sheet indexing
WorkBook.WorkSheets[SheetIndex];
//get default  WorkSheet
WorkBook.DefaultWorkSheet;
//get first WorkSheet
WorkBook.WorkSheets.First();
//for the first or default sheet:
WorkBook.WorkSheets.FirstOrDefault();
Enter fullscreen mode Exit fullscreen mode

VB:

'by sheet indexing
WorkBook.WorkSheets(SheetIndex)
'get default  WorkSheet
WorkBook.DefaultWorkSheet
'get first WorkSheet
WorkBook.WorkSheets.First()
'for the first or default sheet:
WorkBook.WorkSheets.FirstOrDefault()
Enter fullscreen mode Exit fullscreen mode

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 in 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 of the Excel file.

C#:

WorkSheet["Cell Address"];
Enter fullscreen mode Exit fullscreen mode

VB:

WorkSheet("Cell Address")
Enter fullscreen mode Exit fullscreen mode

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

C#:

WorkSheet.Rows[RowIndex].Columns[ColumnIndex]
Enter fullscreen mode Exit fullscreen mode

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

C#:

//by cell addressing
string val = WorkSheet["Cell Address"].ToString();
//by row and column indexing
string val = WorkSheet.Rows[RowIndex].Columns[ColumnIndex].Value.ToString();
Enter fullscreen mode Exit fullscreen mode

VB:

'by cell addressing
Dim val As String = WorkSheet("Cell Address").ToString()
'by row and column indexing
Dim val As String = WorkSheet.Rows(RowIndex).Columns(ColumnIndex).Value.ToString()
Enter fullscreen mode Exit fullscreen mode

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 cell and ending cell address. In this way, it will return all the cell values which lie in the specified range.

C#:

WorkSheet["starting Cell Address : Ending Cell Address"];
Enter fullscreen mode Exit fullscreen mode

VB:

WorkSheet("starting Cell Address : Ending Cell Address")
Enter fullscreen mode Exit fullscreen mode

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

C#:

using IronXL;
static void Main(string[] args)
{
    //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();
}
Enter fullscreen mode Exit fullscreen mode

VB:

Imports Microsoft.VisualBasic
Imports IronXL
Shared Sub Main(ByVal args() As String)
    'import Excel WorkBook
    Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
    'specify WorkSheet
    Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
    'import data of specific cell
    Dim val As String = ws("A4").Value.ToString()
    Console.WriteLine("Import Value of A4 Cell address: {0}",val)
    Console.WriteLine("import Values in Range From B3 To B9 :" & vbLf)
    'import data in specific range
    For Each item In ws("B3:B9")
        Console.WriteLine(item.Value.ToString())
    Next item
    Console.ReadKey()
End Sub
Enter fullscreen mode Exit fullscreen mode

The above code displays the following output:

Image 1

With the values of Excel file sample.xlsx as:

Image 2


5. Import Excel Data by Aggregate Functions

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

  • Sum()

C#:

//to find the sum of specific cell range 
WorkSheet["Starting Cell Address : Ending Cell Address"].Sum();
Enter fullscreen mode Exit fullscreen mode
  • Average()

C#:

//to find the average of specific cell range 
WorkSheet["Starting Cell Address : Ending Cell Address"].Avg()
Enter fullscreen mode Exit fullscreen mode
  • Min()

C#:

//to find the Min In specific cell range 
WorkSheet["Starting Cell Address : Ending Cell Address"].Min()
Enter fullscreen mode Exit fullscreen mode
  • Max()

C#

//to find the Max in specific cell range 
WorkSheet["Starting Cell Address : Ending Cell Address"].Max()
Enter fullscreen mode Exit fullscreen mode

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.

C#:

using IronXL;
static void Main(string[] args)
{
    //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();
}
Enter fullscreen mode Exit fullscreen mode

VB:

Imports IronXL
Shared Sub Main(ByVal args() As String)
    'Import Excel file
    Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
    'Specify WorkSheet
    Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
    'Import Excel file data by applying aggregate functions
    Dim Sum As Decimal = ws("D2:D9").Sum()
    Dim Avg As Decimal = ws("D2:D9").Avg()
    Dim Min As Decimal = ws("D2:D9").Min()
    Dim Max As Decimal = 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()
End Sub
Enter fullscreen mode Exit fullscreen mode

The above code gives us this output:

Image 3

And our file sample.xlsx will have these values:

Image 4


6. Import Complete Excel File Data

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

C#:

//import WorkBook into dataset
DataSet ds = WorkBook.ToDataSet();
Enter fullscreen mode Exit fullscreen mode

VB:

'import WorkBook into dataset
Dim ds As DataSet = WorkBook.ToDataSet()
Enter fullscreen mode Exit fullscreen mode

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 as DataTable ColumnName. To do this, we set the boolean parameter of ToDataSet() function of IronXL as follows:

C#:

ToDataSet(true);
Enter fullscreen mode Exit fullscreen mode

VB:

ToDataSet(True)
Enter fullscreen mode Exit fullscreen mode

This will make the first column of 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 WorkSheets as DataTable ColumnName:

C#:

using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    DataSet ds = new DataSet();
    ds = wb.ToDataSet(true);
    Console.WriteLine("Excel file data imported to dataset successfully.");
    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 ds As New DataSet()
    ds = wb.ToDataSet(True)
    Console.WriteLine("Excel file data imported to dataset successfully.")
    Console.ReadKey()
End Sub
Enter fullscreen mode Exit fullscreen mode

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


Top comments (0)