Read and Write Excel Documents Using OLEDB

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

• Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^] 
• How To Use ADO with Excel Data from Visual Basic or VBA 
• How to: Get Data from Multiple Workbooks using One OleDbConnection?
  - Compare data or join/read data from multiple Excel files using single OleDb connection.
• Microsoft Jet Database Engine Programmer's Guide - Introduction

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.