How to read an Excel file in C#



We often come across requirements especially developing desktop applications in .NET, where we need to do lot of report generation in Microsoft Excel. Over the next few tutorials on Excel, we will see how to read, write and perform Excel operations in C#. In this tutorial though, we will see how to read an Excel file in C#.

At the end of this tutorial, we will see how to open an Microsoft Excel file in C#, read few columns from the Excel file and save it as a list in C# and close the Excel file.

Excel

The figure shows the Excel file used in our example. We consider a very simple Excel file comprising of 2 columns of data Name and Age. So, our objective is to read the 10 rows of Column A and Column B and save it as a list. You can use this List to display the data in the Table format using DatagridView control. This is out of purview of our discussion for this topic.

There are a number of 3rd party libraries and techniques discussed in the internet. Here we use COM library which is available in your system if you have Microsoft office installed. Let us understand the steps involved.

Add a reference to the Excel object Library

Step 1 : Create a new C# Windows Forms Project. Open the Solution Explorer pane, Right click on the References and click on Add Reference. This will open a new window “Add Reference”. Click on the COM tab, and select “Microsoft Excel Object Library” (It is assumed that you have the licensed version of Microsoft Office installed in your system).

Add-Reference-1

Add-Reference-2


Step 2 :
You need to add the reference in the code, in order to use all the methods of Excel object library. This can be done using the following :

 

Step 3 : Instantiate an Excel object

 

Step 4 : Sometimes, Excel display some warning messages if there any compatibility issues. You can disable all the warning messages using the following code.

 

Set the Visibility

Step 5 : By default, the visibility of the Excel application is set to FALSE. At least during the development stages, lot of developers would prefer to debug the code and while seeing the Excel file. You can set the visibility of the Excel file using the code :

 

When you are debugging, the visibility is set to true, so you can see the Excel file when the application is running. When in release mode, the visibility is set to false.

Open the Excel File

Step 6 : In order to open the Excel file, you can use the following code:

 

Open the Excel Sheet

Now this is important, lot of developers make this mistake. When you open the Excel file, you might have noticed, there are more than 1 Excel sheet. So, the application would some Excel sheet. So you need to first get the reference to the concerned Excel sheet.

For our experiment, let us say the Excel sheet name is “ExperimentSheet”. You can get the reference to the sheet using the code:

 

However, you can also you use the following code. However, the best practice is to obtain the reference using the sheet name as mentioned in the above step.

Step 7 : In Step 6, we just obtained the reference to the sheet. However, this sheet might not be open(as we can have multiple sheets in Excel). In order to open the Excel sheet, using the following code

 

Its all about Address range

Step 8 : Whether you are reading or writing to an Microsoft Excel, the principle remains the same. First specify the address range of the cells. Read the values from this address range.

In our example, we need to read the cells from A2 to A11.

 

To read the second column, just change the address from B2 to B11.

 

You can access all the data in values object using a simple for loop.

 

Step 9 : Sometimes, you donot know the complete address range . In such cases, you can obtain the address till the last used row and last used column using the following code:

 

And the rest of the steps remain same as mentioned in Step 8.

Column Number to Column Name conversion

Step 10 : As you know in Microsoft Excel, the cells are indexed by their cell index or address. The columns are indexed in alphabet starting from A, B… Z, AA, AB and so on.

In the example we have considered in this tutorial, we have always assumed to the column address as “A”. In order obtain the column name, sometimes we need to first convert the integer into an Column Name

The following function does just that. I found this beautiful piece of code somewhere in the internet, donot know from where.(If you are reading this, thanks for sharing). It converts the integer into equivalent Column name.

 

So, columnNumberToName(1) would return “A”

columnNumberToName(15) would return “O”

columnNumberToName(27) would return “AA” and so on.

Close the Excel Application

Step 11 : The last step is to close the Excel file and the Excel application. As we have not made any changes in the Excel file, we donot have to save the file. The following code closes the Excel file and releases all the memory associated with the Excel application.

 

Download

You can download the complete source code from here and and the Excel file from here.

I hope, you find this tutorial on reading data from Microsoft excel in C# useful. Please leave a comment if you have any doubts or contact me with your comments and feedback. I like reading your feedback.


Kiran Chandrashekhar

Hey, Thanks for dropping by. My name is Kiran Chandrashekhar. I am a full-time software freelancer. I love Maths and Mathematical Shortcuts. Numbers fascinate me. I will be posting articles on Mathematical Shortcuts, Software Tips, Programming Tips in this website. I love teaching students preparing for various competitive examinations. Read my complete story.

4 Comments

  1. There is a C#/.NET library known as Aspose.Cells for .NET which allows users to create, write, open, read, apply many features on the excel file and also to convert excel file to many other formats. Try it, you can find code samples for many features that you can apply on your excel file.

  2. Therefore, I’ll coach you on getting reduce acne with organic acne treatment dishes residence and ways from bursting out in the foreseeable future to reduce acne.

Leave a Reply

Your email address will not be published. Required fields are marked *

Protected by WP Anti Spam