Last night I had an issue with exporting the asp.net dataset/datatable to excel. Requirement is to export the data into an Microsoft excel sheet. There are about 13 columns and few columns have to be in specific format. The problem was with formatting.
Couple of columns have to be in dd/MM/yyyy 00:00:00 format, so for example when the date is June 08, 2010, then the date should look like 08/06/2010 00:00:00. But for me it was displaying like 8/6/2010 which is not suppose to be. I googled for almost 2 hours for the solution, but nothing worked. Finally today I got an idea to use the formatting option available in excel sheet. When the data is passed from c# code, it can be concatenated with default excel function available.
I need to use the TEXT function in excel sheet so that it displays exactly what I send. I found the formatting options here http://www.techonthenet.com/excel/formulas/text.php
Used the following code for formatting the date as text:
Similarly, we can use the above code for formatting decimal numbers as well. Check the given link for decimal conversion as well.
Use the below code for decimal conversion. Eg: 123.44, 880.08, 334.30, 0.00
No comments:
Post a Comment