5

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();
        }
CheapD AKA Ju
  • 713
  • 2
  • 7
  • 21
  • Look at [this](http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB) article – Izikon Oct 29 '13 at 15:46
  • 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 Oct 29 '13 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 Oct 29 '13 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 Oct 30 '13 at 08:06
  • you getting the same error using the class you downloaded? – Izikon Oct 30 '13 at 09:41
  • Idk , I had just no result, but it's ok , i solved my problem! look following! – CheapD AKA Ju Oct 30 '13 at 10:38

2 Answers2

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\"
CheapD AKA Ju
  • 713
  • 2
  • 7
  • 21
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';
Luigino
  • 11
  • 3