Sunday, August 17, 2008

How to Read an Excel in C#?

This article will help you understand reading an excel file in C#. This is often required when developing applications. Create an excel file named "Test.xls" in the C Drive. The sample excel file will look like:
For interacting with an excel file you will have to include the following COM assemblies:

  • Microsoft Excel 12.0 Object Library
  • Microsoft Office 12.0 Object Library

Following code will be used to read the excel file and display the values in a Console application:




// Add Reference
using System;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;

namespace ReadExcel
{
/// <summary>
/// This class will be used to read the excel and
/// display it in a console.
/// </summary>
class ReadExcelApplication
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
// Path for the test excel application
string Path = @"c:\test.xls";
// Initialize the Excel Application class
Excel.ApplicationClass app = new ApplicationClass();
// Create the workbook object by opening the excel file.
Excel.Workbook workBook = app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,
Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
// Get the active worksheet using sheet name or active sheet
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;

// This row,column index should be changed as per your need.
// i.e. which cell in the excel you are interesting to read.
int index = 1;
object rowIndex = 1;
object colIndex1 = 1;
object colIndex2 = 2;

try
{
while (((Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
{
// Read the Cells to get the required value.
string firstName = ((Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
string lastName = ((Excel.Range)workSheet.Cells[rowIndex, colIndex2]).Value2.ToString();
Console.WriteLine("Name : {0},{1} ", firstName, lastName);
index++;
rowIndex = index;
}
}
catch (Exception ex)
{
// Log the exception and quit...
app.Quit();
Console.WriteLine(ex.Message);
}
}

}
}

Hope this helps! Your comments are always welcome!

25 comments:

Anonymous said...

Nice, but beware of COM automation drawbacks.
If you have small workbooks you can use our GemBox.Spreadsheet Free
Excel component for XLS/CSV/XLSX reading/writing/reporting
.

Anonymous said...

Excellent. Thanks

Anonymous said...

thanks for this, helped get me started

Anonymous said...

{System.Runtime.InteropServices.COMException (0x80028018): Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD)) ocurrs when i open the excel. Can anyone help me? Thanks

Sandeep Aparajit said...

Here is a complete KB article provided by Microsoft for resolution of this bug.
http://support.microsoft.com/kb/320369

Anonymous said...

find some othe from the following link

http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

Anonymous said...

Very Nice
how can i convert usedrange of excel sheet to a table in excel sheet and giving column names

Anonymous said...

Thanks,

do not forget to close it => app.Workbooks.Close() otherwise you will end up with a lot of Excel instances running on background.

Sandeep Aparajit said...

Thanks, good catch!

-Sandeep

Anonymous said...

i got an error when i used the code. It was a compile time error that said "Interop type XXXX cannot be embedded. Use the applicable interface instead"

The error pointed to the ApplicationClass (in place of the XXXX). To fix it, all I did was change "ApplicationClass" to "Application" and it worked.

Thank you very much for the code.

Anonymous said...

how to read entire excel file and keep it into array , then use the data as parameter to send at method

Anonymous said...

Many thanks, this just saved me a LOT of time.

Jinto said...

we are trying to retrieve a calculated value from a cell which has add-In formulas in it. The sample add-in "myUtilityl.xla" is working properly in excel. It retrieves value for the addin function =ISOWEEKNUM(F9). But we are unable to retrieve the value programatically using C# & Microsoft Object Library. The add-In "myUtilityl.xla" is attached to Excel. Environment is VS2010

I am providing the sample code here.

string path = @"C:\Test.xls";
Workbook theWorkbook;
Worksheet theWorksheet;
Range readRange;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
theWorkbook = app.Workbooks.Open(path);
Sheets theSheets = (Sheets)theWorkbook.Worksheets;
theWorksheet = (Worksheet)theWorkbook.Worksheets.get_Item("Sheet1");
readRange = theWorksheet.get_Range("B1");
MessageBox.Show(Convert.ToString(readRange.Value));
//theWorkbook.Save();
app.Workbooks.Close();

Anonymous said...

wonderful.........................

Unknown said...

good post Sandeep

Anonymous said...

thank you for posting this

Anonymous said...

This is not good solution I think require Excel installation or these interop. :(

Sriram Parthasarathy said...

Hi ,

I got this error ,
Object reference not set to an instance of the object .

try
{
for (int i = 2; i < 12; i++)
{
string y = ((Excel.Range)xlWorkSheet.Cells[2, i]).Value2.ToString();
MessageBox.Show(y);
}


}
catch (Exception e)
{
MessageBox.Show("Catch " + e.Message);
}
}

Sriram Parthasarathy said...

try
{
for (int i = 2; i < 12; i++)
{
string y = ((Excel.Range)xlWorkSheet.Cells[2, i]).Value2.ToString();
MessageBox.Show(y);
}


}
catch (Exception e)
{
MessageBox.Show("Catch " + e.Message);
}
}

Error : Object Reference Not set to an instance of the object . Please help .

Thanks .

John said...

Nice!
C# Read Excel and Show in WPF DataGrid: http://www.codearsenal.net/2012/06/c-sharp-read-excel-and-show-in-wpf.html

Anonymous said...

thanx Sandeep:)

Anonymous said...

My Program gives error for calling Excel and XML, please help;

Could you please tell me what namespaces and references should be used to avoid this error for a C# console application VS 2008.

Regards

KenanUsta said...

Thank you Very Much. This really is what I need.

Pranav Singh said...

This is really a nice article have a look of this article.
http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=381&title=How-to-Read-Excel-File-in-C#-Windows-Application-and-Show-Excel-Data-Into-DataGridView-Controls

Feliny said...

I use this C# Excel Library for creating and reading my excel files. Try it our i hope you will also find it useful.

Post a Comment