All programmers need to learn how to read excel files. This is the information age, and many people store their data in excel files because they are user-friendly. Programmers must also know all about reading excel files in their respective programming language.
If you are a .Net Programmer and struggling with reading excel files, then don't worry — you are in the right place. I will show you a step-by-step for reading excel files using C#.
You might be thinking that reading excel files would be difficult and require high-level expertise, but this is not the case. I will show you that it only takes 2-3 lines of code with IronXL to be able to read excel files.
We will be covering the following topics in this article:
- Introduction to IronXL
- Creating an Example Project for our Demonstration
-
Adding IronXL Nuget Package to our Project
a. Installing IronXL using the Package Manager Console
b. Installing IronXL using the Manage Package Manager
c. Directly download it and add a reference to the project -
Read the Excel file in C#
a. Designing the Form
b. Code to Read the data from Excel
c. Code to Read data within a specific range
d. Code to Read XLs and XLSX File
e. Code to Read CSV files Summary
Introduction to IronXL:
IronXL is a .Net Library development brought to you by Iron Software. This library provides excellent functions and APIs to help us read, create and update/edit our excel files and spreadsheets. IronXL does not require Excel to be installed on your server or Interop. Moreover, 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.
Creating an Example Project for our Demonstration
I will be using Windows Form App as an example, but you can use one of your choice, such as Asp.Net Web App, .Net core MVC or .Net Core web api.
To create a new project, open Visual Studio, click on "Create New Project" => Select Template; I am using the Windows Form App, you can use any of your choosing. => Click on Next Button => Name Your Project; I have named mine as "C Sharp Read Excel File". => Click on Next = > Select your Target Framework; I am selecting .Net core 3.1, but again, you can choose your own. => Finally, click on "Create Project", and a project will be created and opened for you as shown below:
Our next task is to Install the Nuget Package for IronXL.
(adsbygoogle = window.adsbygoogle || []).push({});Adding the IronXL Nuget Package
We can add the IronXL Package in one of any three ways, so you can choose the method that suits you best.
Installing IronXL using the Package Manager Console
Open the Package Manager console in your Project and use the following command:
Go to Tools => NuGet Package Manager => Package Manager Console.
This will open the Package Manager console for you. Next, write the following command in the Package Manager console:
PM > Install-Package IronXL.Excel
Installing IronXL using the Nuget Package Manager
This is another way to install the Nuget Package Manager. If you have already completed installation using the previous method, than you won't need to use this one.
Go to: Tools = > NuGet Package Manager => Manage NuGet Packages for Solution, and click on it.
This will open the Nuget-Solution for you; click on "Browse" and search for IronXL.Excel in the search bar:
Click on the "Install" button and it will install IronXL for you. After installing IronXL, go to your form and start designing it.
Downloading IronXL
Another way of using IronXL is to download it directly from this link. Once downloaded, just add the reference of the file in your project and start using it.
Design the Form
Now, we have to design the form to suit our requirements. I will use the minimum design for the demonstration. You can use any design depending on your needs.
Go to the ToolBox=> Select Label (for name our example app), and select the "Panel and Data" grid view. Put the "Data" grid view inside the panel. Our design will look like the following:
Our form has now been designed. Our next task is to actually write the code for reading an excel file or excel sheet in C#.
Write Code for Reading Excel Files in C
We want our app to read any given excel file at the click of a button, so for this, double-click on the "Read Excel File" button, and the following code will appear:
private void button1_Click(object sender, EventArgs e)
{
}
Next, add the following namespace in this file:
using System;
using System.Windows.Forms;
using IronXL;
using System.Data;
Using the IronXL namespace is necessary to access the IronXL functions. However, you can use other namespaces as per the requirements of your own projects.
WorkBook workbook = WorkBook.Load("Weather.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Sheet1");
DataTable dt = sheet.ToDataTable(true);
dataGridView1.DataSource = dt;
The first statement will load the excel workbook "Weather.xlsx" for us. I have chosen the xlsx file format as an example. You can choose any file of your choice.
After loading the excel workbook, we need to select the excel worksheet we want to read. After that, we need to convert our worksheet into a data table so that we can further load it into our Data Grid View.
This will read the file for us and display all its data in the data grid view.
Output for Reading 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 a Microsoft Excel file.
Excel File:
Now that we can read excel files using C#, let's explore some advanced options.
Let's suppose that you want to read the data from an excel file for a specific cell range. Let's look at how to do that.
Read Data from Excel within a Specific Cell Range:
To read the data in a specific cell range, write the 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;
The first two lines are the same as those for loading the workbook and selecting the worksheet we want to read. On the third line, we have selected the cell range within which we want to read the data. I have selected the range A1:C6 which means that it will read the first three columns: Column A, Column B, and Column C, as well as the first six rows, as shown below.
Output for Read Data within a Specific Cell Range:
Read XLS or XLSX File:
We can read excel files no matter whether the file format is xls or xlsx. In the previous example, we looked at the example of reading the data from an Xslx file. Next, let's look at saving the same file in .xls format and then reading it. The 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#:
If you have csv files instead of an excel file, you have to make slight changes to the code.
Let's look at the following example.
I have the same Weather file saved as "Weather.csv", so I am using the same file for all the examples. Again, you can use any example according to your requirements.
WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet sheet = workbook.DefaultWorkSheet;
DataTable dt = sheet.ToDataTable(true);
dataGridView1.DataSource = dt;
On the first line will load the csv file for you and define the file format. The second line will select the worksheet, while the other two code lines are the same as those in the previous examples.
Output for Reading CSV File
Summary:
In this tutorial we have learned how to read data using excel in C# by selecting specific cells and ranges, or different file formats. This all was done with just a few lines of code.
If you want to learn more about Iron Software products, please click on this link. If you decide that you’d like to try it out for yourself, then go ahead and take advantage of the Iron Software 30-day Free-Trial . You may also want to take a look at the Iron Suite, this complete package includes five .Net Libraries, this special offer means you can get all five products for the price of only two. For more information, please click here.
I hope that you have found this guide helpful and easy to follow. If you need any further assistance, please feel free to post a comment.
Oldest comments (3)
Good and easy example using Ironsoft
But Iron Software products are paid, we can read excel in C# using Epplus or MS Interop or OLEDB for free.
Thanks
MS Interop requires a licensed copy of Office to be installed on the server. This is never a good idea, and instantly disqualifies this as an option. Microsoft themselves advise against it.
EPPlus seems to be a good alternative with EPPlus 4 is free under LGPL, but it’s obsolete and no longer maintained. Any server admins will tell you that obsolete and unmaintained software is a massive security risk.
EPPlus 5 requires a license for commercial use and their prices rise sharply for more than a few developers. If you don’t renew the license, they cut off support, and you’re left on your own.
The IronXL team are available to help, during POC->development->testing>deployment.
Yes, there is another option of using Oledb, IronXL is costly option, would not recommend it to anyone.