Excel Spreadsheet/ADO.net

[mike509123]

I wrote the following code (code below) which opens an excel spreadsheet named Book1.xls. Afterwards it writes the text 5 in cell A7 and the text "Cell G3" in cell B7. This works okey the only issue is that when I open the spreadsheet and place the curser in cell A7 the number 5 is formatted as text nstead of a number. (warning message: The number in this cell is formatted as text or proceeded by an apostrophe)

Thanks

OleDbConnection conOleDBConnection = new OleDbConnection();
OleDbCommand comOleDBCommand = new OleDbCommand();
conOleDBConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=Book1.xls;Extended Properties=\"Excel 8.0;HDR=NO;\"";
comOleDBCommand.Connection = conOleDBConnection;
conOleDBConnection.Open();


comOleDBCommand.CommandText = "UPDATE [Sheet1$A7:B7] SET F1 = 5, F2 = 'Cell G3'";
comOleDBCommand.ExecuteNonQuery();
conOleDBConnection.Close();

 

[jmeckley]

not 100% sure, but i thought DB connections to Excel only allowed CRD operations. not update. looks like you are trying to combine sql statements with Excel operations.

the db objects don't know the datasource is Excel. it may be assuming all fields are text.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mike509123]

Check out the secontion Updating Excel Using ADO.NET, they seem to do it there just not with numbers:

http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

 

[jmeckley]

good link. how do you format your excel columns? it could be the format is general. so ADO defaults to string. maybe if the column is formatted to number it will display as a number.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mike509123]

how do you format your excel columns?

I dont, its a blank spreadsheet.

 

[AlexCuse]

I think you will need to use excel's COM to do the formatting. Do you have excel 2003? If so, I may have some sample code for you around somewhere.

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[jmeckley]

by default all cells are general. if your using excel as a database then the sheets/columns should already be formatted. In the link above excel was a "database" the table (sheet) had the columns named. I assume those columns where formatted.

it's a long shot, but you could try a create table query to execute

create table mytable
(
   column1 int,
   column2 datetime,
   column3 string
)

it's a long shot but might work.

one other point. ADO is only worried about accessing data, not formatting data. the data structure should already exist. if your storing data in excel it doesn't really matter how it's stored because it's not meant to be viewed by end users (in the current scenario).

if you're trying to generate an excel doc for end users there are other libraries for that.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[dseaver]

I just had to create a Excel document with my program, I used the Excel Object Library. Its a decent amount of work to get all the formatting you want, but it isn't that had to do after you get the hang of it.