Export Visualforce page to Excel with rich text

Question

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:

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.

Answer 1

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

Answer 2

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.