Click here to Skip to main content
14,698,390 members
Articles » Web Development » ASP.NET » General
Posted 30 Mar 2016


30 bookmarked

Read Write Excel file with OLEDB in C# (without Interop)

Rate me:
Please Sign up or sign in to vote.
4.96/5 (16 votes)
30 Mar 2016CPOL
Read write Excel file without interop using OLEDB in C#


Are you looking for a way to Read/Write Excel file without Interop com? Want to Read both XLS and XLSX format? Then read this article - it will really help you Read or Write Excel files using OLEDB.


In earlier days when I was new to programming, I used to read/write Excel file using Interop object, but it is unmanaged and heavy entity and due to its 'HELL' performance, I desperately needed some good alternative to Interop. I have gone through OLEDB, it performs very well for reading and writing Excel files.

Using the Code

Before start Reading/Writing from/in Excel file, we need to connect to OLEDB using connection string, here OLEDB will act as Bridge between your program and EXCEL.

Image 1

Rows and columns of Excel sheet can be directly imported to data-set using OLEDB, no need to open Excel file using INTROP EXCEL object.

Let's start with the code.

// Connect EXCEL sheet with OLEDB using connection string
// if the File extension is .XLS using below connection string
//In following sample 'szFilePath' is the variable for filePath
 szConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                       "Data Source='" + szFilePath + 
                       "';Extended Properties=\"Excel 8.0;HDR=YES;\"";
 // if the File extension is .XLSX using below connection string
 szConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
                      "Data Source='" + szFilePath + 
                      "';Extended Properties=\"Excel 12.0;HDR=YES;\"";

In the above connection string:

  • Provider is OLEDB provider for Excel file, e.g., Jet.OLEDB.4.0 is for XLS file and ACE.OLEDB.12.0 for XLSX file
  • Data Source is the file path of Excel file to be read
  • Connection string also contains 'Extended Properties' like Excel driver version, HDR Yes/No if source Excel file contains first row as header

After connection to EXCEL file, we need to fire Query to retrieve records from sheet1.

Accessing Excel Tables

There are a couple of ways to refer to an Excel table:

  1. Using sheet name: With the help of sheet name, you can refer to Excel data, you need to use '$' with sheet name, e.g. Select * from [Sheet1$]
  2. Using Range: We can use Range to read Excel tables. It should have specific address to read, e.g. Select * from [Sheet1$B1:D10]

** Here $ indicates the EXCEL table/sheet already exists in workbook, if you want to create a New workbook/sheet, then do not use $, look at the sample below:

// Connect EXCEL sheet with OLEDB using connection string
 using (OleDbConnection conn = new OleDbConnection(connectionString))
        OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
        ("select * from [Sheet1$]", conn);
        DataSet excelDataSet = new DataSet();
        dataGridView1.DataSource = excelDataSet.Tables[0];
	//In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
        // with the help of data adaptor we can load records in dataset		
	//write data in EXCEL sheet (Insert data)
 using (OleDbConnection conn = new OleDbConnection(connectionString))
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange) 
            VALUES ('DEC','40','60','80');";
        catch (Exception ex)
            //exception here
//update data in EXCEL sheet (update data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "UPDATE [Sheet1$] SET month = 'DEC' WHERE apple = 74;";
        catch (Exception ex)
            //exception here

**OLEDB does not support DELETE query.

Image 2

Exceptions May Be Faced

  1. The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

    Cause: The exception occurs when we run our code on 64Bit machine.

    How to Resolve: If your application is Desktop based, compile your EXE with x86 CPU. If your application is web based, then Enable '32-Bit Applications' in application pool.

  2. Deleting data in a linked table is not supported by this ISAM.

    Cause: As we have already discussed, OLEDB does not support DELETE operation. If you try to Delete rows from EXCEL sheet, it gives you such exception.

Advantage Against INTEROP/COM Object

Image 3

We know EXCEL Interop application can also be used to complete this task, but there are several advantages against INTEROP/COM object, see the below points:

  1. Interop objects are heavy and un-managed objects
  2. Special permissions are needed to launch component services if you run this code as Web application in IIS
  3. No Excel installation is needed when we need to Read/Write Excel using OLEDB. 4. OLEDB is faster in performance than Interop object, as No EXCEL object is created.


There are always two sides of the coin. With OLEDB, you cannot format data that you inserted/updated in EXCEL sheet but Interop can do it efficiently. You cannot perform any mathematical operation or working on graphs using OLEDB, but it is really a good way to insert/update data in EXCEL where no Excel application is installed.

Comments and suggestions are always welcome

Thank you!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Technical Lead
India India
Hi there, I am Prasad. Author, Blogger, contributor and passionate about Microsoft .NET technologies. I like to write an articles/blogs on different .NET aspects and like to help Developers, to resolve their issues and boost them on Microsoft Technologies.

Certifications: Microsoft Certified professional (MCP), Microsoft Certified technology specialist (MCTS), Agile-Scrum Master.

Awards: Microsoft Re-connect MVP (GSC Member), Most valuable member at dotnetspider, Most popular curator, Most active curator, featured curator at Microsoft Curah, Editor at dotnetspider.

Microsoft MVP 2014 [ASP.NET/IIS]
Click here for more .NET Tips
-After all Knowledge is an endless entity

Comments and Discussions

QuestionWhere we can view data of table in project ?? Pin
Member 1406357522-Nov-18 2:34
MemberMember 1406357522-Nov-18 2:34 
GeneralMy vote of 5 Pin
Cipherc6-Aug-18 23:53
MemberCipherc6-Aug-18 23:53 
QuestionGreat Post Pin
MotuDev6-Feb-18 6:00
MemberMotuDev6-Feb-18 6:00 
Questionto read an excel file using console application c# Pin
Member 1286342531-Dec-16 1:23
MemberMember 1286342531-Dec-16 1:23 
AnswerRe: to read an excel file using console application c# Pin
koolprasadd1-Jan-17 22:38
professionalkoolprasadd1-Jan-17 22:38 
QuestionStill need Data Connectivity Components Pin
overmachine12-Apr-16 10:05
Memberovermachine12-Apr-16 10:05 
AnswerNo Pin
koolprasadd12-Apr-16 19:13
professionalkoolprasadd12-Apr-16 19:13 
QuestionAnother way Pin
gautams30-Mar-16 23:36
Membergautams30-Mar-16 23:36 
AnswerRe: Another way Pin
koolprasadd31-Mar-16 19:43
professionalkoolprasadd31-Mar-16 19:43 
GeneralThoughts Pin
PIEBALDconsult30-Mar-16 5:02
professionalPIEBALDconsult30-Mar-16 5:02 
GeneralRe: Thoughts Pin
koolprasadd3-Apr-16 19:36
professionalkoolprasadd3-Apr-16 19:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.