5

I have a Visual page which offers the capability of exporting a data table to Excel.

There is a text field called Account number. Some account numbers are more than 12 digits long. When such numbers are exported to Excel, they are shown by default in the scientific format.

12345678912345 becomes 1.23457E+13

Currently my code for this column is:

  <apex:column headerValue="Account Number">                   
            <apex:outputText value="{!mt.Account_Number__c}"/>                                     
   </apex:column>

I tried using different formatting options but they didn't work. Also tried using Apex:OutputField without success.

I tried some solutions like adding css but that did not help either.

https://stackoverflow.com/questions/4619909/format-html-table-cell-so-that-excel-formats-as-text

I know this is Excel's default behavior with numbers with 12 digits or more. Any ideas as to how I can force it to display them as Strings in my Visual force page?

Thanks.

2
  • Are you sure the field is text and not a number?
    – Mike Chale
    Feb 21, 2013 at 17:30
  • Yes the field type is text as some account numbers can be numeric or alphanumeric.
    – Richard N
    Feb 21, 2013 at 17:31

2 Answers 2

10

Try to insert html blanks to the start and end of the string:

<apex:column headerValue="Account Number">                   
    <apex:outputText value="&nbsp;{!mt.Account_Number__c}&nbsp;" escape="false"/>
</apex:column>
1
  • 1
    Superb...I did try blank spaces but not HTML blanks..This worked perfectly my friend...thanks!
    – Richard N
    Feb 21, 2013 at 17:57
5

If you want more fine-grained control over the number formats when they are exported to Excel, you can use a combination of a dynamic style parameter in the VForce page, and associated property in your controller.

eg. in this case I'm formatting a dynamic length text field (of numbers) as a number in Excel using 0's as the format type (ie no scientific display).

In the VForce page:

<td style="width:160px; line-height:13px;{!GTINCodeFormat[p]}" class="ean">{!productListByParentProductName[parentProd][p].Product__r.GTIN_pfi__c}</td>

(the style class is using embedded controller property based on current list element)

In controller:

public Map<String, String> GTINCodeFormat {get; protected set;}

if (p.Product__r.GTIN_pfi__c != null) {
s = 'vnd.ms-excel.numberformat:';
codeLen = p.Product__r.GTIN_pfi__c.length();
for (Integer i=1;i<=codeLen;i++) {
    s += '0';
}
GTINCodeFormat.put(p.Product__r.Oracle_Item_Code_pfi__c, s);
}  else {
GTINCodeFormat.put(p.Product__r.Oracle_Item_Code_pfi__c, '@');
}
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .