asked on

Preformatting when creating an Excel spreadsheet from ASP.Net with XML/XSL

I am creating an Excel spreadsheet from and ASP.Net web page query. One of my fields is for a social security number, datatype varchar. The problem is that when Excel reads in the data it defaults the social security number field to numeric which causes leading zeros to be truncated. I need a way to preformat the Excel column to text to preserve the data.

I have tried to pass the data to Excel via xml and an xsl stylesheet but can't seem to get the stylesheet to work correctly.

Does anyone have any experience with this or know of another way to preformat Excel?

ASKER CERTIFIED SOLUTION

CarlosMu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brsltd

Along the same lines as Carlos, I think Excel uses the character ' to indicate left-aligned text.......as in it won't display it but will tell Excel you are giving it a string and not numbers.

mwoertz

ASKER

Sorry for the delay in returning a response. I tried your suggestion but it didn't seem to work. Could be that although I was appending the space to the front of the social security number ASP.Net was truncating it during transfer to excel, I don't know. However, you did give me another idea. I simply inserted the normal dashes into the social security number. That's an accepted format and doesn't interfere with sorting in excel.

Harland Gomez

it also happened to me when i was creating a report in excel but what i did was i copy the data onto clipboard first and store it in a variable..

Harland Gomez

it also happened to me when i was creating a report in excel but what i did was i copy the data onto clipboard first and store it in a variable..

Harland Gomez

it also happened to me when i was creating a report in excel but what i did was i copy the data onto clipboard first and store it in a variable..