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?

I'm not an expert, but when I import data from another source, I
sometimes have a similar problem. Something with the data being
delimited.
Selecting the column with the data you want to format, then pressing
Data--Text to Columns and selecting 'numbers' normally does the trick
for me.

Tony

On Apr 12, 6:18 am, DeveloperSQL
wrote:
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?

Try selecting the column, then pressing Data -- Text to Columns, and
select General

Tony

On Apr 12, 6:18 am, DeveloperSQL
wrote:
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?

Thanks.
I really need to be able to load the data programmatically preserving the
existing formatting and so that I do not have to manually modify the cells
afterwards.
I do not have the option of manual edits for the project that I'm working on.


"bony_tony" wrote:

Try selecting the column, then pressing Data -- Text to Columns, and
select General

Tony

On Apr 12, 6:18 am, DeveloperSQL
wrote:
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?