Click here to Skip to main content
12,354,086 members (71,619 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


21 bookmarked

Read and Write Excel Documents Using OLEDB

, 3 Jan 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Sample code for reading and writing Excel documents using OLEDB.

Below are some of the simplified codes, for more detail and deeper explanation, please read:


using System.Data.OleDb;
using System.Data;

Get Connection String

private string GetConnectionString()
    Dictionary<string, string> props = new Dictionary<string, string>();

    // XLSX - Excel 2007, 2010, 2012, 2013
    props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
    props["Extended Properties"] = "Excel 12.0 XML";
    props["Data Source"] = "C:\\MyExcel.xlsx";

    // XLS - Excel 2003 and Older
    //props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
    //props["Extended Properties"] = "Excel 8.0";
    //props["Data Source"] = "C:\\MyExcel.xls";

    StringBuilder sb = new StringBuilder();

    foreach (KeyValuePair<string, string> prop in props)

    return sb.ToString();


private void WriteExcelFile()
    string connectionString = GetConnectionString();

    using (OleDbConnection conn = new OleDbConnection(connectionString))
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;

        cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";

        cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";

        cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";

        cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";

        cmd.CommandText = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;";


Note: DELETE is not supported.


private DataSet ReadExcelFile()
    DataSet ds = new DataSet();

    string connectionString = GetConnectionString();

    using (OleDbConnection conn = new OleDbConnection(connectionString))
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;

        // Get all Sheets in Excel File
        DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        // Loop through all Sheets to get data
        foreach (DataRow dr in dtSheet.Rows)
            string sheetName = dr["TABLE_NAME"].ToString();

            if (!sheetName.EndsWith("$"))

            // Get all rows from the Sheet
            cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

            DataTable dt = new DataTable();
            dt.TableName = sheetName;

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);


        cmd = null;

    return ds;

Error Message

If you see this error: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Try one of these:

  • If you running 64 bit of Windows, change the target platform of build to x86, or 
  • The Engine should be installed by default, but in case it is not, manually download Microsoft Access Database Engine 2010 Redistributable, choose the correct build (x86 or x64) and install it. 


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


About the Author

Software Developer
Malaysia Malaysia
Writing programs is an art.

You may also be interested in...

Comments and Discussions

QuestionSheetnames only once? Pin
DBLWizard6-Jun-16 3:50
memberDBLWizard6-Jun-16 3:50 
QuestionDate "conversion" issue Pin
Member 1192075919-Aug-15 5:15
memberMember 1192075919-Aug-15 5:15 
AnswerRe: Date "conversion" issue Pin
adriancs12-Sep-15 16:38
mvpadriancs12-Sep-15 16:38 
QuestionCan u provide me JAVA code for Extracting Excel data and store it in SQL server using OLEDB 12.0 Pin
Member 1178959929-Jul-15 21:08
memberMember 1178959929-Jul-15 21:08 
QuestionRead-only database error Pin
Member 1102735019-Feb-15 5:12
memberMember 1102735019-Feb-15 5:12 
AnswerRe: Read-only database error Pin
adriancs12-Sep-15 16:39
mvpadriancs12-Sep-15 16:39 
QuestionNice! Pin
Volynsky Alex4-Jan-14 2:40
memberVolynsky Alex4-Jan-14 2:40 
GeneralThoughts Pin
PIEBALDconsult3-Jan-14 5:00
professionalPIEBALDconsult3-Jan-14 5:00 
GeneralRe: Thoughts Pin
adriancs3-Jan-14 14:09
memberadriancs3-Jan-14 14:09 
QuestionNeeds some work Pin
Richard MacCutchan2-Jan-14 22:32
mvpRichard MacCutchan2-Jan-14 22:32 
AnswerRe: Needs some work Pin
adriancs2-Jan-14 23:05
memberadriancs2-Jan-14 23:05 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160621.1 | Last Updated 3 Jan 2014
Article Copyright 2014 by adriancs
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid