2

I have a visualforce page that generates an excel file with the table contained in the page, but when i open the file it is stripping the 0 on the number fields. These fields are actually text so i would think its excel doing the stripping.

How can I prevent this?

visualforce page:

    <apex:page controller="fundingUI_Excel_Controller" contentType="application/vnd.ms-excel#PPAY.xls" cache="true">
<apex:pageBlock>
    <apex:pageBlockTable var="dt" value="{!dtxns}">
        <apex:column headerValue="DR ACLR Code"/>
        <apex:column headerValue="CR ACLR Code"/>
        <apex:column headerValue="DR BSB" >
            <apex:outputText value="{!invBSB}"/>
        </apex:column>
        <apex:column headerValue="DR Account" value="{!invBankAccNum}"/>
        <apex:column headerValue="Payee Name" value="{!dt.Account_Full_legal_Name__c}"/>
        <apex:column headerValue="Payee Amount" value="{!dt.Disbursal_Transaction_Amount__c}"/>
        <apex:column headerValue="CR BSB" value="{!dt.Borrower_Bank_BSB__c}"/>
        <apex:column headerValue="CR Account" value="{!dt.Borrower_Bank_Account_Number__c}"/>
        <apex:column headerValue="Payment Type" value="{!paymType}"/>
        <apex:column headerValue="Narration">
            <apex:outputText>Banjo Ref {!dt.Banjo_Loan_No__c}</apex:outputText>
        </apex:column>
        <apex:column headerValue="Payment Date" >
            <apex:outputText value="{0, date, dd'/'MM'/'YYYY}">
                <apex:param value="{!TODAY()}" /> 
            </apex:outputText>
        </apex:column>
        <apex:column headerValue="Trade Date" >
            <apex:outputText value="{0, date, dd'/'MM'/'YYYY}">
                <apex:param value="{!TODAY()}" /> 
            </apex:outputText>
        </apex:column>
        <apex:column headerValue="Settlement Date"  >
            <apex:outputText value="{0, date, dd'/'MM'/'YYYY}">
                <apex:param value="{!TODAY()}" /> 
            </apex:outputText>
        </apex:column>
        <apex:column headerValue="Asset Code"/>
        <apex:column headerValue="Units"/>
        <apex:column headerValue="Brokerage"/>
        <apex:column headerValue="Broker"/>
        <apex:column headerValue="Contract Note"/>
        <apex:column headerValue="Expense Code">
            <apex:outputText value="TRANSFER"/>
        </apex:column>
        <apex:column headerValue="Transaction type">
            <apex:outputText value="FO"/>
        </apex:column>
    </apex:pageBlockTable>
</apex:pageBlock>
</apex:page>

DR BSB DR Account 12345 123123123 BSB should contain a 0 before the 12345 but its been stripped by excel.

1
  • Can you add quotes when entering the value in the string variable? This might work. Jun 26, 2018 at 4:45

3 Answers 3

1

Salesforce isn't stripping them in most cases; Excel is. Once it determines that a column is "number-like" (most rows have only numbers in them), Excel automatically assumes you want the cells to be in number format. The quick fix is to have a prefixed ":

<apex:column headerValue="DR Account" value="&quot;{!invBankAccNum}"/>

If that doesn't work, you might need to actually expand the column like this:

<apex:column headerValue="DR Account">
 "{!invBankAccNum}
</apex:column>

As an aside, using apex:pageBlock in Excel might produce other annoying artifacts because of the HTML structure of those; I'd recommend removing it and using a simple apex:dataTable instead. The former is really intended to render Salesforce-looking pages, while the latter makes more sense when you're just exporting the data.

4
  • hello thanks for your suggestion, i implemented the datatable and added the &quot; but it now actually adds that character so ends up like this "123456 and this file is then sent to an automated system which rejects the file because of the illegal character. How can i do to not have the quote appear, also tried with single quote (') but didnt work either Jun 26, 2018 at 6:51
  • You can't have it both ways, I don't think. If it's an Excel file, you need the quote, if you're importing into another system, you don't.
    – sfdcfox
    Jun 26, 2018 at 6:53
  • is there a way to specify the column format for the excel file in visualforce, for example by setting to text instead of General? Jun 26, 2018 at 7:21
  • @Mauricio.Ramos1979 I really don't think you can do this with apex:pageBlockTable, but apex:dataTable should work just fine. See this question for the appropriate CSS you should use.
    – sfdcfox
    Jun 26, 2018 at 7:23
0

Another way is to use contact function and join the number value with empty sting.

Here is sample code that worked for me:

<apex:page standardController="Account" recordSetVar="accounts"  contentType="application/vnd.ms-excel#PPAY.xls" >  
    <table>
        <tr>
            <td>Account Name </td>
            <td>Account Number </td>
        </tr>        
        <apex:repeat value="{!accounts}" var="a">
            <tr>
                <td>{!a.name}</td>
                <td>=CONCATENATE("","{!a.AccountNumber}")</td>
            </tr>
        </apex:repeat>    
    </table>    
</apex:page>

PS: Note that user/you will need to click "Enable Editing" button to see the result.

0

SO finally was able to solve this problem, I managed to fix it by using css style inline in the column tag as follows:

    <apex:column headerValue="DR BSB" value="{!invBSB}" style="mso-number-format:\@;"/>

The mso-number-format: \@ forces the column to be a text one instead of General.

Thank you all for your replies, i do hope this solution ends up helping someone else.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.