Discussion

Changing the format of excel from General to Text

Hi We have a requirement in our application regarding exporting to excel where in we need to preserve the values of numeric coloumns starting with zero .As the type of the Excel is general any numeric value which begins with a zero gets truncated.Please suggest an approachs to solve the issue. Regards, Azi.

***Updated by moderator: Marissa to close post***
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Comments

Keep up to date on this post and subscribe to comments

January 21, 2009 - 7:58am

Hi Aman,

Thanks for the respond. I have tried to implement your suggessted method by appending "\'" to the property that contains leading zero in property set. Its something like this "\'"+EMFCODE. However, still while exporting to excel, the data in the excel will show ' at the beginning of the property value. Do you have any idea how to display the original value (leading zero value) without '(apostrophe)at the beginning of the value.

Thanks a lot,
Azi

December 17, 2008 - 5:17pm

Azi,

ExportToExcel activity takes pyFileData as a parameter, and that holds the String that is Exported to Excel. In your case you will have to prefix a "\'" (a back-slash followed by an apostrophe) to all the numeric values you want to be interpreted as text in excel. The apostrophe tells Excel to interpret the Number as a String, and the back-slash is to escape the special character behavior of apostrophe in the String.

Hope it helps,
Aman

September 25, 2012 - 9:49am

Add this code in header:

TestPage+"<html><head><META HTTP-EQUIV='Content-Type' CONTENT='CHARSET=UTF-8'><style>.excel-hide-row{display:none;}.excelText{vnd.ms-excel.numberformat:@;}.excel-datetime{vnd.ms-excel.numberformat:m/d/yyyy h:mm AM/PM}.excel-date{vnd.ms-excel.numberformat:m/d/yyyy}.excel-timeofday{vnd.ms-excel.numberformat:hh:mm}.excel-number{vnd.ms-excel.numberformat:General;}</style></head><TABLE cellspacing='0' cellpadding='1' width='100%' border='1' style='border-collapse: collapse;'>"

add this in body or result:

<td class='excelText'>"+number+"</td>