Updating data in to a Excel sheet using c#

Question

I am trying to update some data in an Excel sheet of the format "xlsx" using OLEDB connection, but I am unable to make out the connection establishment.

Here is my code:

        String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + "D:\abc1.xlsx" + "';Extended Properties='Excel 8.0;HDR=Yes'";
        OleDbConnection con = new OleDbConnection(sConnectionString);

        con.Open();
        OleDbCommand com = new OleDbCommand("select * from Sheet1",con);
        OleDbDataReader reader = null;

        reader = com.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(reader[0]);
        }
        con.Open();

        Console.ReadLine();
    }

When I run the code, I'm facing the following exception:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Any idea how to recover from this exception or any other suggestions from which i can update my data in excel is advisable.

"when i run the code i am facing the following exception.. " So what is this exception? — Plue, Jan 13 '14 at 09:31
What kind of exception? What's the message of the exception? — Andi Krusch, Jan 13 '14 at 09:31
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. — Naveen Billure, Jan 13 '14 at 09:37
most likely the issue is with the bit-ness of your application. Try building for x86 and see if the application works. — NoviceProgrammer, Jan 13 '14 at 09:44
You need to install it, have a look here [Microsft.ACE.OLEDB.12.0 provider not registered on the local machine][1] [1]: http://stackoverflow.com/questions/6649363/microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine — sagar, Jan 13 '14 at 09:46

Sudhakar Tillapudi · Answer 1 · 2014-01-13T09:46:07.683

0

Change your PlatformTarget Type from AnyCPU to X86.

Steps:

Goto Project Properties.
Select Build tab.
Select X86 from PlatformTarget options.

edited Jan 13 '14 at 09:46

answered Jan 13 '14 at 09:32

Sudhakar Tillapudi

25,935
5
37
67

JohnyHarkness · Accepted Answer · 2014-01-13T09:52:01.663

0

This could be the provider you have stated try changing it to the one which matches the Excel version on your machine

Try

Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\abc1.xlsx';Extended Properties="Excel 12.0 Xml;HDR=YES";

Instead

Could also be that excel isnt installed

Also check that you have referenced the OLEDB library for your project

edited Jan 13 '14 at 09:52

answered Jan 13 '14 at 09:46

JohnyHarkness

88
7

score 0 · Answer 3 · answered Jan 13 '14 at 10:02

It is possible that there are multiple reasons for this exception.

1) You could use the OleDbEnumerator class to find out what providers are available. Accordingly you set your connection string.

2) Before that just try out below connection string. String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + "D:\abc1.xlsx" + "';Extended Properties='Excel 8.0;HDR=Yes'";

3) If you have a 64 bit OS, there is no 64-bit version of the JET provider available and there's no alternative. As long as you want to support JET, you'll need to set the build target to x86.

pratik patel · Answer 4 · 2014-01-13T10:56:42.477

first save as your excel workbook as Excel 97-2003 Workbook It will work in my project...

string filepath = Server.MapPath("~/ImportData/") + fileUpload.FileName;
 OleDbConnection oconn = new OleDbConnection
 (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";  
        Extended Properties=Excel 8.0");`


    oconn.Open();
    OleDbDataAdapter adp = new OleDbDataAdapter("select * from Sheet1", oconn);
    DataSet ds = new DataSet();

    adp.Fill(ds);
    oconn.Close();`

Updating data in to a Excel sheet using c#

4 Answers4