Write Data to Excel using Oledb

Question

Is it possible to write data using Oledb into a common excel ?

There are no table structure or anything, it's a user document.

When I tried, i had always an OleDbException

"INSERT" query reply : Operation must use an application that can be updated.
"UPDATE" query reply : No value given for one or more required parameters.

My code:

  using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"C:\Users\[...]\Classeur.xls" + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;READONLY=FALSE\""))
        {
            connection.Open();
            OleDbCommand commande = new OleDbCommand(
              "INSERT INTO [Feuil1$](F1,F2,F3) VALUES ('A3','B3','C3');", connection);
            commande.ExecuteNonQuery();

            connection.Close();
            connection.Dispose();
        }

New test (without sucess !) :

       using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\Users\[...]\Classeur.xls" + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;READONLY=FALSE\""))
        {
            string column = "A";
            string row = "1";
            string worksheetName = "Feuil1";
            string data = "TEST";
            connection.Open();
            string commandString = String.Format("UPDATE [{0}${1}{2}:{1}{2}] SET F1='{3}'", worksheetName, column, row, data);
            OleDbCommand commande = new OleDbCommand(
                commandString, connection);
            connection.Close();
            connection.Dispose();
        }

I've downloaded this project ( with functionalities who do not work for me precisely the cell edition), and in his code he use an Update like me .. I'm on FrameWork 3.5, maybe it's a reason ? — CheapD AKA Ju
– CheapD AKA Ju, Commented Oct 29, 2013 at 16:01
I don't think the reason is the FW version. the provider is different from the code in the article to your code — Izikon
– Izikon, Commented Oct 29, 2013 at 16:11
He use : "Jet OLEDB 4.0" (for Excel 8) And "Ace OLEDB 12.0" (for Excel 12) I've try to change provider, version, excel file's version (cross test) and every time the same error ! And , in my query, F1 is the Column but, i never specify the row. What would explain the error, but I didn't see any example where it's specify . — CheapD AKA Ju
– CheapD AKA Ju, Commented Oct 30, 2013 at 8:06

CheapD AKA Ju · Accepted Answer · 2013-10-30 08:31:47Z

8

I finally found ! Simple question of IMEX ( So many hours lost for that !)

So if anyone have the same issue :

 //for reading data 
 Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;READONLY=FALSE\"

 //for writing data 
 Extended Properties=\"Excel 8.0;HDR=NO;IMEX=3;READONLY=FALSE\"

answered Oct 30, 2013 at 8:31

CheapD AKA Ju

7212 gold badges7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Luigino · Accepted Answer · 2020-12-30 01:09:03Z

0

This IMEX situation for Writing Data was driving me crazy for months, I had to remove it to make it work. I just found [CheapD] answer and it works flawless, Thank you Cheap.

I would suggest to add the MODE parameter:

Extended Properties='Excel 12.0; HDR=Yes; IMEX=3; MODE=Share; READONLY=False';

answered Dec 30, 2020 at 1:09

Luigino

113 bronze badges

Collectives™ on Stack Overflow

Write Data to Excel using Oledb

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related