Click here to Skip to main content
14,449,458 members

Read and Write Excel Documents Using OLEDB

Rate this:
4.57 (10 votes)
Please Sign up or sign in to vote.
4.57 (10 votes)
3 Jan 2014CPOL
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
Programming is an art.

Comments and Discussions

 
QuestionIs there a way to read worksheet based on position Pin
Yadav Vinay12-Dec-17 9:24
MemberYadav Vinay12-Dec-17 9:24 
QuestionSheetnames only once? Pin
DBLWizard6-Jun-16 4:50
MemberDBLWizard6-Jun-16 4:50 
QuestionDate "conversion" issue Pin
Stephen Grey19-Aug-15 6:15
MemberStephen Grey19-Aug-15 6:15 
AnswerRe: Date "conversion" issue Pin
adriancs12-Sep-15 17:38
professionaladriancs12-Sep-15 17: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 22:08
MemberMember 1178959929-Jul-15 22:08 
QuestionRead-only database error Pin
Member 1102735019-Feb-15 6:12
MemberMember 1102735019-Feb-15 6:12 
AnswerRe: Read-only database error Pin
adriancs12-Sep-15 17:39
professionaladriancs12-Sep-15 17:39 
QuestionNice! Pin
Volynsky Alex4-Jan-14 3:40
professionalVolynsky 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
professionaladriancs3-Jan-14 15:09 
QuestionNeeds some work Pin
Richard MacCutchan2-Jan-14 23:32
communityengineerRichard MacCutchan2-Jan-14 23:32 
AnswerRe: Needs some work Pin
adriancs3-Jan-14 0:05
professionaladriancs3-Jan-14 0: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.

Tip/Trick
Posted 2 Jan 2014

Tagged as

Stats

230.1K views
28 bookmarked