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.
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).
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 :
|
1
|
using Excel = Microsoft.Office.Interop.Excel;
|
Step 3 : Instantiate an Excel object
|
1
|
Excel.Application oXL= new Excel.Application();
|
Step 4 : Sometimes, Excel display some warning messages if there any compatibility issues. You can disable all the warning messages using the following code.
|
1
|
oXL.DisplayAlerts = false;
|
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 :
|
1
2
3
4
5
|
#if DEBUG
oXL.Visible = true;
#else
oXL.Visible = false;
#endif
|
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:
|
1
2
3
|
//Open the Excel File
String inputFile = @"D:\Excel\Input.xlsx";
Excel.Workbook oWB = oXL.Workbooks.Open(inputFile);
|
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:
|
1
2
3
4
5
6
7
|
String SheetName = "ExperimentSheet";
Excel._Worksheet oSheet = oWB.Sheets[SheetName];
oSheet = oWB.Sheets[1]; // Gives the reference to the first sheet
oSheet = oWB.ActiveSheet; // Gives the reference to the current opened sheet
|
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
|
1
|
oSheet.Activate();
|
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.
|
1
2
3
4
5
6
|
//We already know the Address Range of the cells
String start_range = "A2";
String end_range = "A11";
Object[,] values = oSheet.get_Range(start_range, end_range).Value2;
|
To read the second column, just change the address from B2 to B11.
|
1
2
3
4
5
6
|
//We already know the Address Range of the cells
start_range = "B2";
end_range = "B11";
Object[,] values = oSheet.get_Range(start_range, end_range).Value2;
|
You can access all the data in values object using a simple for loop.
|
1
2
3
4
5
|
int t = values.GetLength(0);
for (int i = 1; i <= values.GetLength(0); i++)
{
String val = values[i, 1].ToString();
}
|
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:
|
1
2
3
4
5
6
7
8
9
|
Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
int lastRow = last.Row;
int lastColumn = last.Column;
start_range = "A2";
end_range = "A" + Convert.ToString(lastColumn);
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
// Return the column name for this column number.
private string ColumnNumberToName(int col_num)
{
// See if it's out of bounds.
if (col_num < 1) return "A";
// Calculate the letters.
string result = "";
while (col_num > 0)
{
// Get the least significant digit.
col_num -= 1;
int digit = col_num % 26;
// Convert the digit into a letter.
result = (char)((int)'A' + digit) + result;
col_num = (int)(col_num / 26);
}
return result;
}
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
oXL.Quit();
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
oSheet = null;
oWB = null;
oXL = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
|
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.
SapnaEdu is a portal to learn some really cool Mathematical Shortcuts. I hope, it will help students preparing for various competitive examinations. Some of these shortcuts are explained in Vedic Mathematics and rest of them are from my own experience of teaching students. Get to know about SapnaEdu 
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.
Thanks a lot, I will try.
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.