OLEDB with updating excel cells

Question

I wrote this method to update an excel cell:

public void update(string fileName, string sheetName)
{
   string connString = connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(fileName) + ";Extended Properties=Excel 12.0";

try
{
   OleDbConnection oledbConn = new OleDbConnection(connString);

   oledbConn.Open();

  OleDbCommand cmd = new OleDbCommand("UPDATE ["+sheetName+"$B5:B5] SET F1=17", oledbConn);

  cmd.ExecuteNonQuery();

  oledbConn.Close();
}
catch(Exception ex)
{
  Debug.Write("Error: " + ex.Message);
}
}

I called it like this:

update("test.xls", "test");

The B5 cell is available in "test" sheet, but the value never gets updated.

I even tried with this one:

UPDATE ["+sheetName+"$B5:B5] SET F1='17'

and I always got this exception: No value given for one or more required parameters.

Any idea?

Thanks in advance.

Fionnuala · Accepted Answer · 2012-02-06T14:59:08.573

5

EDIT I notice you have missed HDR=No.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + 
";Extended Properties=""Excel 12.0;HDR=No"""

EDIT Tested in C# Express

Either:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=z:\\docs\\myspreadsheet.xls;Extended Properties='Excel 12.0 xml;HDR=No'"

Note xml

Or

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=z:\\docs\\myspreadsheet.xls;Extended Properties='Excel 8.0;HDR=No'"

For *.xls

edited Feb 06 '12 at 14:59

answered Feb 06 '12 at 13:12

Fionnuala

90,370
7
114
152

You are right regarding read only, it will work without that, but HDR=No seems necessary. – Fionnuala Feb 06 '12 at 13:36
1

after adding HDR=No, I got another error: {"Could not find installable ISAM."} – olidev Feb 06 '12 at 13:37
do you know this problem: after updating the excel file and read it again, the cell values have been been updated unless I manually open it and after pressing on "x" to close it, there is a prompt pop up and I need to save it anyway in order for the new cell values saved? – olidev Feb 07 '12 at 00:00
I am not quite sure what you are saying, but are you sure you have released all objects? It might be worth asking a new question, with some code samples. – Fionnuala Feb 07 '12 at 00:03
I used UPDATE command and it seems other excel cells were not updated automatically. For example, F2 is dependent on F1 and if I used UPDATE command to update F1 then F2 will not get updated. I have manually open the excel file and save it in order to get F2 updated. – olidev Feb 07 '12 at 00:09
I posted this issue in other post: http://stackoverflow.com/questions/9169315/other-excel-cells-dependent-are-not-updated-automatically could you please help me? – olidev Feb 07 '12 at 00:17
I do not believe you can force calculations without opening the workbook. This suggests that what you need is interop, not ado. – Fionnuala Feb 07 '12 at 00:21

OLEDB with updating excel cells

1 Answers1

Linked