3

I have gone through this page: https://help.salesforce.com/apex/HTViewSolution?id=000003176 and successfully exported my data into excel sheet with .xls format. However, I kind of want to achieve a bit fancier effect in Excel. Something like this:

enter image description here

As you can see, the columns support filtering and sorting and there are colors in the spreadsheet. Is there anyway I can achieve this - even if Visualforce page with contenttype is not an option. The link to any related API/documents would also be helpful. And hopefully I don't need to go into Encoding.

4

Funny you should ask this - I just submitted a blog yesterday that deals with this. Unfortunately, the blog has not been approved for publishing yet. The short version of the blog is

  • Format a spreadsheet in Excel (frozen rows, named ranges, styles etc).
  • Save as an Excel XML 2004 document.
  • Open in a text editor and add your visualforce bindings.
  • Place the raw xml inside a newly created visualforce page that looks like this:

<apex:page id="pg" standardStylesheets="false" controller="ExcelExportController" 
contenttype="application/vnd.ms-excel#TestExport_{!TODAY()}.xls">
    {!xmlheader}
    <!-- your excel output goes here -->
    <apex:outputText value="{!endfile}" escape="false"/>
</apex:page>

Your controller will look something like this:

public with sharing class ExcelExportController {

    public Opportunity[] opportunities {get;set;}
    public Integer oppSize {get;set;}

    //xls variables:
    public String xmlheader {get;set;}
    public String endfile{get;set;}

    public ExcelExportController() {
        xmlheader ='<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
        endfile = '</Workbook>';
        opportunities = [SELECT Id, Name, StageName, Amount FROM Opportunity LIMIT 10];
        oppSize = opportunities.size();
    }
}

The reason you should do this in Excel is that the styles syntax that Excel generates is, to put it bluntly, horrible. Here is the sort of thing you get:

<Style ss:ID="s67">
  <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
  </Borders>
  <Font ss:Size="14"/>
  <Interior ss:Color="#FFF58C" ss:Pattern="Solid"/>
 </Style>
 <Style ss:ID="s68">
   <Alignment ss:Vertical="Bottom"/>
   <Font ss:Size="12" ss:Bold="1"/>
   <Interior ss:Color="#FFCC99" ss:Pattern="Solid"/>
 </Style>
 <Style ss:ID="s70">
   <Borders>
     <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"/>
     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="14"/>
   <Interior ss:Color="#FFF58C" ss:Pattern="Solid"/>
   <NumberFormat ss:Format="Percent"/>
  </Style>

...and on and on. No style names. repetition. Yuk.

Any formats you save in the spreadsheet will be maintained on the next export. Obviously you are going to have to add repeats and other apex data.

For my working code page, take a look here

3
  • Brilliant! I guess I understand how to do it now. – Lance Shi Jan 28 '16 at 4:00
  • Just one question. In your VF page end, there is already </Workbook>. Why do you still want to have a apex:outputText value="{!endfile}"? – Lance Shi Jan 28 '16 at 4:30
  • Hmm. I wonder if I've managed to combine two of my old outputs together? I'll have a look and get back to you> – Caspar Harmer Jan 28 '16 at 5:46
1

For whatever reason, when this page is downloaded, Salesforce seems to strip out the closing </Workbook> tag so we need to add one </Workbook> tag from our side so things fit as intended.

1
  • Based on our investigation, it really depends on different orgs though. Thank you for your answer anyway. – Lance Shi Dec 28 '16 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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