CODEWITHSUNDEEP
c#excel
Ammar Bayg
Ammar Bayg

Reputation: 105

Writing Excel Worksheets from C#

This code is only manipulating data if I create excel spreadsheet/file Worksheet using C#. If I provide it already created excel file for writing or manipulation, it throws an exception:

"The Microsoft Access database engine could not find the object 'Sheet1'(Worksheet in excel file which is referred in Data Source). Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1' is not a local object, check your network connection or contact the server administrator."

This is code I am trying:

string con = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\Temp\table1.xlsx;" + @"Extended Properties= 'Excel 12.0 Xml; HDR=YES;'

OleDbConnection connection = new OleDbConnection(con);
connection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = connection;


cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";
cmd.ExecuteNonQuery();

cmd.CommandText = "UPDATE Book1 SET Name = 'Current Tag' WHERE id = 3;";
cmd.ExecuteNonQuery();

connection.Close();

Upvotes: 0

Views: 268

Answers (1)

Tarrokk
Tarrokk

Reputation: 98

Is there any reason you have to use Access database commands to edit your Excel file? Otherwise you could have a look at the

Microsoft.Office.Interop

assembly, which provides direct access to Excel files: https://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx

Upvotes: 1

Related Questions