Click here to Skip to main content
11,428,221 members (60,800 online)
Click here to Skip to main content

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

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)
    {
        sb.Append(prop.Key);
        sb.Append('=');
        sb.Append(prop.Value);
        sb.Append(';');
    }

    return sb.ToString();
}

Write

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

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

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

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

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

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

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

        conn.Close();
    }
}

Note: DELETE is not supported.

Read

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

    string connectionString = GetConnectionString();

    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        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("$"))
                continue;

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

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

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);

            ds.Tables.Add(dt);
        }

        cmd = null;
        conn.Close();
    }

    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. 

License

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

Share

About the Author

adriancs
Software Developer
Malaysia Malaysia
Writing programs is an art.

Comments and Discussions

 
QuestionRead-only database error Pin
Member 1102735019-Feb-15 6:12
memberMember 1102735019-Feb-15 6:12 
QuestionNice! Pin
Volynsky Alex4-Jan-14 3:40
memberVolynsky Alex4-Jan-14 3:40 
GeneralThoughts Pin
PIEBALDconsult3-Jan-14 6:00
professionalPIEBALDconsult3-Jan-14 6:00 
GeneralRe: Thoughts Pin
adriancs3-Jan-14 15:09
memberadriancs3-Jan-14 15:09 
QuestionNeeds some work Pin
Richard MacCutchan2-Jan-14 23:32
mvpRichard MacCutchan2-Jan-14 23:32 
AnswerRe: Needs some work Pin
adriancs3-Jan-14 0:05
memberadriancs3-Jan-14 0: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.150428.2 | Last Updated 3 Jan 2014
Article Copyright 2014 by adriancs
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid