Click here to Skip to main content
11,710,472 members (81,801 online)
Click here to Skip to main content

Read and Write Excel Documents Using OLEDB

, 3 Jan 2014 CPOL 54.9K 14
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

QuestionDate "conversion" issue Pin
Member 1192075919-Aug-15 5:15
memberMember 1192075919-Aug-15 5:15 
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 
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    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
Web01 | 2.8.150819.1 | Last Updated 3 Jan 2014
Article Copyright 2014 by adriancs
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid