I am following the example at "http://support.microsoft.com/kb/306023" to
populate a sheet within an excel workbook with numbers using ADO.Net.
My problem is that the numbers are being inserted as strings although the
sheet has cell formatting for numbers. As a result I have to manually
reformat the sheet to number formatting. The ADO.Net example claims that
cell formatting is preserved, but this is not happening. How can I correct
this?

My insert statements look like this:

insert into mytable values ('InsertTest',1234.55,12345.67,123456.78);

The table 'mytable' is defined in Excel with range $A$1 : $D$1. (It's
basically an adaptation of the example.)

I've tried formatting just $B$1 : $D$1 in the proper number format (with 2
decimal places) and I've tried formatting all 100 cells ($B$1 : $D$100) that
would be populated as number format with 2 decimal places. These don't seem
to work.

I've also tried setting the 'mytable' to be the size of all 100 cells that
will be populated, but this causes the data to be inserted after the last row
of the table - although it is inserted with the correct formatting. So at
$A$101 : $D$101 I'd get the correct formatting with the first row of data
followed by the other 99 rows.

How could this be?