How to Read Excel File in C#

Reading an Excel file is something which Programmer is going to face in any case in their life. This is the era of data, and People usually store their data in the Excel file due to its user friendly behavior. Programmer must knows about the reading of Excel files in their respective programming language.

If you are a .Net Programmer and struggling with the reading excel file, than Don't worry. You are at the right place. I will show you step by step method about how to read an Excel file using C#.

You might be thinking that, reading Excel file would be the difficult task and might need high level expertise, but that not the case. I will show you that we have to right just 2-3 lines of code for reading the excel files with the help of IronXL.

We will be covering following topics in this article.

1. Introduction to IronXL. a. Creating an Example Project for demonstration
2. Add IronXL Nuget Package to to our Project. a. Install IronXL using Package Manager Console b. Install IronXL using Manage Package Manager c. Directly download it and add reference to the project
3. Read the Excel file in C#.
4. Design the Form
5. Code to Read the data from Excel
6. Code to Read data from Particular range
7. Code to Read XLs and XLSX File
8. Code to Read csv file
9. Summary

For complete Tutorial please visit this link

Introduction to IronXL:

IronXL is .Net Library develop and Provided by Iron Software. This Library provides us wonderful functions and APIs by using which we can read, create and Update/ Edit our Excel file and spreadsheet. IronXL does not require Excel to be installed on your server or Interop. IronXL provides a faster and more intuitive API than Microsoft Office Interop Excel.

IronXL works on .Net Core 2, Framework 4.5, Azure, Mono and, Mobile and Xamarin.

Create an Example Project for Demonstration:

I will be using windows Form App as an Example, you can use any such as Asp.Net Web App, .Net core MVC or .Net Core web api as per your choice and requirements.

For creating new Project; Open Visual Studio, Click on Create new Project => Select Template ; I am using Windows Form App; you can use any. => Click on Next Button => Named your Project; I have named it as "C Sharp Read Excel File". => Click on Next = > Select your Target Framework, I am selecting .Net core 3.1; you can use any. => Finally, click on create Project; A project would be created and opened for you as shown below.

Our Next Task is to Install Nuget Package for the IronXL.

Add IronXL Nuget Package:

We can add IronXL Package by using three ways, But you have to choose any one, which you fine easy or comfortable.

Install IronXL using Package Manager Console:
Open Package Manager console in your Project and use following command.

Go to Tools => NuGet Package Manager => Package Manager Console.

This will open Package Manager console for you. Now; Write following command in the Package Manager console.

PM > Install-Package IronXL.Excel

Install IronXL using Nuget Package Manager:

This is another way to install Nuget Package Manager, If you have already installed by using previous way, than you don't need to use this way.

Go to Tools = > NuGet Package Manager => Manage NuGet Packages for Solution, and click on it.

This will open Nuget-Solution for you, Click on Browse and Search IronXL.Excel in Search Bar

Click on Install Button, It will install IronXL for you. After installing IronXL, Go to your form, and start designing it.

Download the IronXL:

Another way of using IronXL is to download it directly from this link. After downloading just add the reference of the file in your project and start using it.

Design the Form:

Now, we have to design the form as per the requirement. I will use the minimum design for the demonstration. you can use any as per your requirement.

Go to the ToolBox=> Select Label (for name our example app), select Panel and Data grid view. Put Data grid view inside the panel. Our design will look like as following.

Our form has been designed, Our next task is to actually write the code for read an excel file or excel sheet in c#.

Write code to Read the Excel file in C#:

We want to our app to read the given excel file on button click. so; for that double click on Read Excel file button, following code will appear:

private void button1_Click(object sender, EventArgs e)
        {
        }

Now, Add following Namespace in this file.

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

using IronXL namespace is necessary for using the functionalities of IronXL. However; you can use other namespace as per the requirements of your project.

WorkBook workbook = WorkBook.Load("Weather.xlsx");
            WorkSheet sheet = workbook.GetWorkSheet("Sheet1");
            DataTable dt = sheet.ToDataTable(true);
            dataGridView1.DataSource = dt;

First Statement will load the Excel workbook "Weather.xlsx" for us. I have choose xlsx file format as an example. You can choose any file of your choice.

After loading the excel workbook, we need to select excel worksheet which we want to read. After that I have convert my worksheet into data table, so that we can further load it into our Data grid View.

This will read the file for us, and will display all its data into the data grid view.

Output for Read the Data from Excel File using C#:

Let's open our "Weather.xlsx" file in Microsoft Excel and compare our output. xlsx file is the extension for Microsoft Excel file.

Now, our basic operation such as read excel file using C# has been completed. Let's explore some advance version.

Let's suppose that; you want to read the data from excel file for particular cell range. Let's see how we can do that.

Read Data from Excel in Particular Cell Range:

For reading the data from particular cell range, write following code behind the button.

WorkBook workbook = WorkBook.Load("Weather.xlsx");
            WorkSheet sheet = workbook.GetWorkSheet("Sheet1");
            var range = sheet["A1:C6"];
            DataTable dt = range.ToDataTable(true);
            dataGridView1.DataSource = dt;

First two lines are same in which we have just load the workbook and select the worksheet which we want to read. In the third line, we have select the cell rang for which we want to read the data only. I have select the range A1:C6 that means that, It will read first three columns such as column A, Column B, and Column C and First 6 rows, as shown below.

Output for Read Data for Particular Range:

Read XLS or XLSX File:

We can read excel files no matter whether its file format is xls or xlsx. In previous example, we have seen the example of reading the data from Xslx file. Now, Let's try by saving same file as .xls format and than reading it. Code will remain the same, we just have to change the file name to "Wather.xls" as shown below:

WorkBook workbook = WorkBook.Load("Weather.xls");

Output for Reading XLS File:

Read CSV File in C#:

Suppose that, you encounter the case, in which you have csv fie instead of excel. In that case, you have to make slightly changes to the code.

Let's see this example.

I have same Weather file saved as "Weather.csv", so I would be using same file for all the demonstration. you can use any according to your requirement.

WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
            WorkSheet sheet = workbook.DefaultWorkSheet;
            DataTable dt = sheet.ToDataTable(true);
            dataGridView1.DataSource = dt;

First line will load the csv file for you, define the fie format. Second line will select worksheet, other two code lines are same as previous.

Output for Reading CSV File:

Summary:

In this tutorial we have learned about how to read data using excel in c#, by selecting particular cells and range., or different file format. This all was done with just few lines code.

You can download the Project file from here.

If you want to explore more about Iron Software Products Please click on this link. If you will buy complete package which includes 5 wonderful .Net Libraries, you will get that in just price of two. For more information. Please click here.

I hope that you have found this guide easy and helpful, if you need any further assistance, feel free to comment.