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();
        }

Ishmul

Look at this article – Triennium

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 ? – Ishmul

I don't think the reason is the FW version. the provider is different from the code in the article to your code – Triennium

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 . – Ishmul

you getting the same error using the class you downloaded? – Triennium

Idk , I had just no result, but it's ok , i solved my problem! look following! – Ishmul

Answer 1

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\"

Ishmul

Answer 2

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';

Underexpose