OLEDB with updating excel cells
11,565
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
Author by
olidev
Updated on August 22, 2022Comments
-
olidev over 1 year
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 over 12 yearsYou are right regarding read only, it will work without that, but HDR=No seems necessary.
-
olidev over 12 yearsafter adding HDR=No, I got another error: {"Could not find installable ISAM."}
-
olidev over 12 yearsdo 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?
-
Fionnuala over 12 yearsI 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.
-
olidev over 12 yearsI 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 over 12 yearsI posted this issue in other post: stackoverflow.com/questions/9169315/… could you please help me?
-
Fionnuala over 12 yearsI do not believe you can force calculations without opening the workbook. This suggests that what you need is interop, not ado.