Read excel file using C# and .NET
With C#, we can read excel file data and can store it into DataTable object easily. To do this, you may need to install and prepare development environment as well in the system wherever you want to deploy this application. This is one of the probable solution and is not limited to. Also, I have developed and tested this in Windows environment only, no idea about other OS. Development environment: OS Windows 7 32bit, VS 2012, Excel 2013.
Prerequisites:
1. Download and install "Data Connectivity Components".
2. Register msexcl40.dll located in C:\Windows\System32
3. Register oledb32.dll located in C:\Program Files\Common Files\System\Ole DB
Create an excel file. Here, I have created with name Test.xlsx as shown in figure below.
Lets move to C# code. To work with excel file, you need an OledbConnection object to communicate with the .xls/.xlsx file. OLEDB (Object Linking and Embedding, Database) is an API designed by Microsoft to access data with variety of sources like excel, access etc. in a uniform manner.
Another object we need is OleDbDataAdapter, to fill data into DataTable object. The method is as simple as communicating database.
using System.Data.OleDb;
OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='file_path';Extended Properties='Excel 12.0;HDR=Yes'");
// file_path should be complete file path. Example: E:\\TestData\\Test.xlsx
// HDR = Yes/No. If Ist row is column name, set HDR = Yes.
DataTable tbl = new DataTable("Particulars");
OleDbDataAdapter adp = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);
// Sheet1$ : The name of sheet/tab in excel file, you want to read.
adp.Fill(tbl);
And the object "tbl" output has shown in figure below.
Probable error(s) could be possible:
The above error(s) can be possible if:-
Hope this will help you and you enjoyed the codding.
Prerequisites:
1. Download and install "Data Connectivity Components".
2. Register msexcl40.dll located in C:\Windows\System32
3. Register oledb32.dll located in C:\Program Files\Common Files\System\Ole DB
Create an excel file. Here, I have created with name Test.xlsx as shown in figure below.
Lets move to C# code. To work with excel file, you need an OledbConnection object to communicate with the .xls/.xlsx file. OLEDB (Object Linking and Embedding, Database) is an API designed by Microsoft to access data with variety of sources like excel, access etc. in a uniform manner.
Another object we need is OleDbDataAdapter, to fill data into DataTable object. The method is as simple as communicating database.
using System.Data.OleDb;
OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='file_path';Extended Properties='Excel 12.0;HDR=Yes'");
// file_path should be complete file path. Example: E:\\TestData\\Test.xlsx
// HDR = Yes/No. If Ist row is column name, set HDR = Yes.
DataTable tbl = new DataTable("Particulars");
OleDbDataAdapter adp = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);
// Sheet1$ : The name of sheet/tab in excel file, you want to read.
adp.Fill(tbl);
And the object "tbl" output has shown in figure below.
Probable error(s) could be possible:
- 1. The 'Microsoft.Jet.OLEDB.12.0' provider is not registered on the local machine.
- 2. Could not find installable ISAM.
- 3. External table is not in the expected format.
The above error(s) can be possible if:-
- Connection string is incorrect or Microsoft.ACE.OLEDB.12.0 and Excel 12.0 are different version.
- msexcl40.dll and/or oledb32.dll are/is not registered properly.
- You are building your application with 64bit but application does not support it. If any of such issue(s) still persists, change platform target to 32bit. To do this, right click on project, click properties, navigate to Build tab, set Platform target to x86.
Hope this will help you and you enjoyed the codding.
Comments
Post a Comment