How to read Excel files in CSharp

We are going to be reading data that is stored in Excel files using C#. Writing code to read Excel sheets in C# is really easy! You can read from xlsx, xlsm and xls formats in the same way! We will go over how to do that step by step! Follow along and you will be able to get values out of your sheets with ease!

First, I’ve created a video guide that is embedded here that will show you the entire process! If you would like to learn how to read from excel from a text-based format though, read on!

The first step is to create a new C# Application in Visual Studio, I create a new WinForms application in the above example and use a simple button. You can create a Console application or WinForms application to do the following task.

I would also like to add that this code can be easily converted to work with VB.Net! Most of the code I write to read excel documents is actually written in VB.Net. If you have any questions about how to read from Excel files in CSharp or VB.Net, leave a comment below or on the Youtube video and I will help as soon as I see it!

This is the simple Windows Forms application that I created to read from an excel file. It just contains a simple button.

Next, you need to double-click on the button to handle the click event. This will get us into the code behind with an event handler already written! That should look like the screenshot below!

After that, we need to include the Excel Object Library. To do this, you need to right-click on References, then click add a reference. Next, you click on COM on the left and search for “Excel”. This will bring up “Microsoft Excel 16.0 Object Library”. This can have different version numbers if you have different versions of Excel installed.

You will have to have a version of Microsoft Office installed for this to work. What the application does, in the end, is create an instance of Excel that we use to open and read the file. So if you don’t have Excel installed, the application can’t create an instance of it. You will know this by not being able to find the Object Library. So if you can’t find the excel reference, it’s most likely because Excel isn’t installed on the box and needs to be installed.

Once you have the reference added, the next step is to use that reference in the heading of your code. The line of code for this is below. I’ve included the other two lines that will be there already, just so you know where to put the using Excel line.

using System;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

Alright, now we have our references all set up and it’s time to start writing our code to read from the excel document! I created a new function called readExcel, which gets called from the new button click event handler. I’ll show what that looks like in the picture below.

Doing it this way just keeps the excel code out of the designer code. In a perfect world, you would put this function in another class file and not in the same file as the designer code (event handler in this case).

The following code is used to open the workbook

private void readExcel() {
     string filePath = "c:\path\to\file.xls";
     Microsoft.Office.Interop.Excel.Application excel = new Excel.Application();
     Workbook wb;
     Worksheet ws;
     wb = excel.Workbooks.Open(filePath);
     ws = wb.Worksheets[1];

}

Obviously, you will need to change the filePath to your desired file path. I also recommend using a file browser dialog or something to that nature, if the path changes frequently.

So to work through this code, we declare the file path, then we create a new excel application, next we create an object to hold an excel workbook, then we create an object to hold an excel worksheet. After that, we open the file located at the path and it is stored in the wb Workbook variable. Now that we have the book open, we can then get the first sheet and store that in ws, our worksheet variable.

Now that we have everything set up it is time to start reading values from cells!

The simple way to read a value from the excel file would be to just message box out the results using the line of code below.

private void readExcel() {
     string filePath = "c:\path\to\file.xls";
     Microsoft.Office.Interop.Excel.Application excel = new Excel.Application();
     Workbook wb;
     Worksheet ws;
     wb = excel.Workbooks.Open(filePath);
     ws = wb.Worksheets[1];

     MessageBox.Show(Convert.ToString(ws.Cells[1, 1].Value));

}

In my excel file, the cell at 1,1 has the word Apple and the resulting message box looked like this:

Below I have broken that line down into parts that make it easier to understand:

private void readExcel() {
     string filePath = "c:\path\to\file.xls";
     Microsoft.Office.Interop.Excel.Application excel = new Excel.Application();
     Workbook wb;
     Worksheet ws;
     wb = excel.Workbooks.Open(filePath);
     ws = wb.Worksheets[1];

     Range cell = ws.Cells[1, 2];
     string CellValue = cell.Value;
     MessageBox.Show(CellValue);

}

The last 3 lines are the important bit. The first line creates a Range object called cell and stores the Range located at position 1,2 in the Excel file in it. Next we create a string variable called CellValue and pull the Value out of the cell variable and store it in the CellValue string. Lastly, we message box out that value. The resulting message box in my example said “Wine”

Another method you can do is to use excel’s own naming convention to read cells and ranges from the sheet. What I mean by that is being able to say “A1” or “A1:B1” to read from the sheet. This will make it easier for you to know where in the document you are reading at a glance. The code for that is below.

Range cell = ws.Range["A1"];

If you attempt to read a range using the above code, you are going to get Object[] as the result. This is because the value is an array instead of a single value. To read in a range of cell values, you will need to loop through the array to display each value. The code for this is located below.

private void readExcel() {
     string filePath = "c:\path\to\file.xls";
     Microsoft.Office.Interop.Excel.Application excel = new Excel.Application();
     Workbook wb;
     Worksheet ws;
     wb = excel.Workbooks.Open(filePath);
     ws = wb.Worksheets[1];

     Range cell = ws.Cells["A1:A2"];
     foreach (string Result in cell.Value)
     {
           MessageBox.Show(Result);
     }
}

The result of this will be a message box for each cell in the range which will contain the value of each cell. In my case, the first message box said Apple, and the second said Banana.

If you are reading a lot of values out of an Excel sheet, you are going to want to read the data in as a range and store that in an array. At that point, you can manipulate the array in code really quickly. If you read the data in one cell at a time, instead of as a range, it will be much slower with large datasets.

One last thing to note is that you will want to use .close() and .dispose() on your workbook after you are done reading values. This way your excel sheet isn’t continually locked out!

I hope this was helpful and if you have any questions, feel free to leave a comment!