Data field format when writing to Excel with ADO.Net

[Richard Marsden]

I'm currently working on an application that reads/writes data from/to
Access and Excel. Other databases will probably be added in the future.
This is in C#, so I've been reading up on ADO.NET and using this to do
all the data I/O. So far this has been working well.

I have hit an "inconvenience" for Excel, though. Most of the output
fields are numeric, but the user can select text or number fields. For
the text field I simply convert the number to a string first before
writing it.

That works fine, except Excel is not as strongly typed as the usual
relational databases. Therefore a user might expect a column to be
numeric, but in reality Excel thinks the field is text.

Is there a way to write a number to Excel through ADO.Net that forces
the cell to be a number, and not text?

(I know a priori if the connection is an Excel one or not)


Richard


--

Richard Marsden
Winwaed Software Technology LLC
http://www.winwaed.com
Tools and Add-ins for MapPoint - http://www.mapping-tools.com

 

[Cor Ligthert[MVP]]

Richard,

In my idea is the OleDB provider for Excel not as strong as complete IO
access with Excel (it seems to be an old Jet provider).

Therefore as you want to do extended actions with Excel use the normal .Net
for Applications parts.
(I think that not much people often visiting this group will find Excell a
database. It comes probably from the time that Lotus was claiming with the
successor from Lotus 123 that it had database functions, so Microsoft needed
to have that too, but as you see Excel now, then it has strong parts build
in to access SQL server)

Cor

 

[Richard Marsden]

Richard,

In my idea is the OleDB provider for Excel not as strong as complete IO
access with Excel (it seems to be an old Jet provider).

Therefore as you want to do extended actions with Excel use the normal
.Net for Applications parts.
(I think that not much people often visiting this group will find Excell
a database. It comes probably from the time that Lotus was claiming with
the successor from Lotus 123 that it had database functions, so
Microsoft needed to have that too, but as you see Excel now, then it has
strong parts build in to access SQL server)

Cor

You're right - it isn't really a database, but my customers are MapPoint
people. Yes they could use SQL Server, ODBC, Access, etc but they more
often than not are using Excel as a simple table database. Not a serious
database but good for "quick and dirty" data manipulation!

I'm currently supporting a couple of Excel providers - Jet only for the
2003 and earlier workbooks.


Richard

--

Richard Marsden (Virtual Earth MVP)
Winwaed Software Technology LLC
http://www.winwaed.com
Tools and Add-ins for MapPoint - http://www.mapping-tools.com

 

[Cor Ligthert[MVP]]

Richard,

As I already wrote, I would in your case use the .Net for applications
parts, the way the Office people think about objects is in my idea slightly
different then in .Net, all was it alone that the Office management has to
keep in mind to keep development with Office in line with the first day MS
Word was introduced. (And in fact Multiplan what in my idea was the base
for Excel and MS Word).

However, as I already wrote in the text, just my idea.

Cor