Hi,

Here are 4 lines of code which will read excel file .xlsx extension. Read excel workbook using C#. Fix Numeric alpha numeric issues.

If you found your code is not able to read alphanumeric fields then add registery provided at the end of this code.

For connection String please refer  http://www.connectionstrings.com/ .

You can refer the connectionstring there and read older versions of excel.

If you have any query comment it, I will try to answer. or use http://forums.asp.net.

you can also read csv file or flat file by firing query like select * from employee

refer: http://www.revenmerchantservices.com/post/2010/07/02/C-read-csv-file.aspx

Code snippet

  string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\MyExcelFile.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";

//code to read the content of format file

OleDbConnection con = new OleDbConnection(Connection);

OleDbCommand command = new OleDbCommand();

DataTable dt = new DataTable();OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", con);

myCommand.Fill(dt);

// Let's bind this excel data in Data table dt to GridView

GridView1.DataSource = dt;
GridView1.DataBind();

VB.Net Code

Dim Connection As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\MyExcelFile.xlsx;Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
'code to read the content of format file

Dim con As New OleDbConnection(Connection)
Dim command As New OleDbCommand()

Dim dt As New DataTable()
Dim myCommand As New OleDbDataAdapter("select * from [Sheet1$]", con)
myCommand.Fill(dt) 

Thats it you now have this data in dt table.


Windows Registry Editor Version 5.00

 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel]
"DisabledExtensions"="!xls"
"ImportMixedTypes"="Text"
"FirstRowHasNames"=hex:01
"AppendBlankRows"=dword:00000001
"TypeGuessRows"=dword:00003a98
"win32"="C:\\PROGRA~1\\COMMON~1\\MICROS~1\\OFFICE12\\ACEEXCL.DLL"

For more information about how to troubleshoot excel reading issue 

visit http://www.codeproject.com/KB/miscctrl/Excel_data_access.aspx 



Why to use this registery http://www.codeproject.com/KB/miscctrl/Excel_data_access.aspx

1. HDR : It represents Header of the fields in the excel table. Default is YES. If you dont have fieldnames in the header of your worksheet, you can specify HDR=NO which will take the columns of the tables that it finds as f1,f2 etc.
   
2. ReadOnly : You can also open excel workbook in readonly mode by specifying ReadOnly=true; By Default Readonly attribute is false, so you can modify data within your workbook.
   
3. FirstRowHasNames : It is the same as HDR, it is always set to 1 ( which means true) you can specify it as false if you dont have your header row. If HDR is YES provider disregards this property. You can change the default behaviour of your environment by changing the Registry Value [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\FirstRowHasNames] to 00 (which is false)  
   
4. MaxScanRows : Excel does not provide the detailed schema defination of the tables it finds. It need to scan the rows before deciding the data types of the fields. MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column. By default the value of this is 8. You can specify any value from 1 - 16 for 1 to 16 rows. You can also make the value to 0 so that it searches all existing rows before deciding the data type. You can change the default behaviour of this property by changing the value of [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default.  Currently MaxScanRows is ignored, so you need only to depend on TypeGuessRows Registry value. Hope Microsoft fixes this issue to its later versions.
   
5. IMEX : (A Caution) As mentioned above excel have to guess a number or rows to select the most appropriate data type of the column, a serious problem may occur of you have mixed data in one column. Say you have data of both integer and text on a single column, in that case excel will choose its data type based on majority of the data. Thus it selects the data for the majority data type that is selected, and returns NULL for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.
   For example,  In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.   
   To work around this problem for data, set "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting.  You can change the enforcement of type by changing [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes] to numeric as well.