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

mwoertz
mwoertz used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
The solution doesnt have to do with XML, or ASP, but with EXCEL.  Try entering the number with a space prefixed.  If you put a space to the left of a number in Excel the program will treate it as text.

Commented:
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.

Author

Commented:
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.  
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Harland GomezSoftware Engineer II

Commented:
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 GomezSoftware Engineer II

Commented:
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 GomezSoftware Engineer II

Commented:
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..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial