Export to Excel functionality using the Wizard technique


Recently, we were working on a requirement wherein the client wanted an “Export to Excel” button on a Visualforce page which displays the data in a tabular format. Clicking this button enables them to store the tabular data locally as an excel file. Admittedly, there are several ways to implement this functionality. We will walkthrough an implementation technique that I like to call the “Wizard technique”.

Let’s create a sample Visualforce page(AccountDataTable.page) with a list of Account records displayed in a <apex:pageBlockTable> component.

<apex:page controller="AccountDataController" tabStyle="Account">

  <apex:pageBlock title="Account Data Table">

    <apex:pageBlockTable value="{!accounts}" var="account">
      <apex:column value="{!account.id}"/>
      <apex:column value="{!account.name}"/>
      <apex:column value="{!account.description}"/>
      <apex:column value="{!account.SLA__c}"/>
      <apex:column value="{!account.Active__c}"/>
    </apex:pageBlockTable>

  </apex:pageBlock>
</apex:page>

Related controller code(AccountDataController.cls)

public with sharing class AccountDataController {

  //constructor
  public AccountDataController() {
  }

  //list of accounts
  public List<Account> accounts {
    get {
      if(accounts != null) {
        return accounts;
      } else {
        accounts = [Select Id, name, Active__c, SLA__c, Description from Account limit 10];
        return accounts;
      }
    }
    set;
  }
}
Account Data Table

Account Table

Now, let’s create *another* Visualforce page with the contentType attribute set to “application/vnd.ms-excel#AccountTable.xls”. This Visualforce page is linked to the AccountDataController.cls controller that we have used in the AccountDataTable.page.


<apex:page controller="AccountDataController" contentType="application/vnd.ms-excel#AccountTable.xls" showHeader="false" standardStylesheets="false">

  <apex:pageBlock >
    <apex:dataTable value="{!accounts}" var="account">
      <apex:column value="{!account.id}"/>
      <apex:column value="{!account.name}"/>
      <apex:column value="{!account.description}"/>
      <apex:column value="{!account.SLA__c}"/>
      <apex:column value="{!account.Active__c}"/>
   </apex:dataTable>
 </apex:pageBlock>

</apex:page>

There are 2 things to note in this Visualforce page. First, I’m using <apex:dataTable> instead of <apex:pageBlockTable>. The reason being that the generated excel file includes some javascript code at the top along with the data. Replacing <apex:pageBlockTable> with the <apex:dataTable> resolved this issue(not sure why), and removed the javascript code from the excel file. Second thing to note is the showHeader and standardStyleSheets attribute in the <apex:page> component is set to false. The contentType attribute is responsible for transforming this page into an excel file.

We are now ready to add the “Export to Excel” button to the AccountData.page as follows:

<apex:page controller="AccountDataController" tabStyle="Account">

  <apex:form >
    <apex:pageBlock title="Account Data Table">
      <apex:pageBlockButtons >
        <apex:commandButton action="{! exportToExcel }" value="Export To Excel"/>
      </apex:pageBlockButtons>
      <apex:pageBlockTable value="{!accounts}" var="account">
        <apex:column value="{!account.id}"/>
        <apex:column value="{!account.name}"/>
        <apex:column value="{!account.description}"/>
        <apex:column value="{!account.SLA__c}"/>
        <apex:column value="{!account.Active__c}"/>
      </apex:pageBlockTable>

    </apex:pageBlock>
  </apex:form>
</apex:page>

Add the following method to the controller.

//export to excel - returns a page reference to the AccountDataExcel page
 public PageReference exportToExcel() {
   return Page.AccountDataExcel;
 }

Export To Excel button

And voila! When you click the button, your browser will download the data in an excel file. I call this method the Wizard technique because it mirrors the pattern(more than one Visualforce page linked to the same controller) used to create a wizard based form on the Force.com platform.

– @anup

Tagged , , ,

14 thoughts on “Export to Excel functionality using the Wizard technique

  1. I’d be curious to know if you could handle this in a single page with Dynamic VF components and a merge field for contentType? ContentType may not allow a merge field, but I am pretty sure the main content could use a dynamic component. I am interested in starting to use those. Maybe this could be simplified?

    Good post! There are lots of cool things you can do like that to output to different contentTypes.

    • anupj says:

      Thanks Scott! 🙂

      I think you probably can do this with DVF components. I’m not a big fan of DVF components myself, but it’s doable. I can confirm that the contentType attribute definitely allows a merge field.

      – Anup

  2. Jiri says:

    Interesting stuff Anup. Thanks for sharing.

  3. Ron says:

    Nice post Anup! Very useful tip!

    – Ron

  4. calvinrn says:

    In this code:

    public PageReference exportToExcel() {
    return Page.AccountDataExcel;
    }

    You mean:
    return Page.AccountDataTable;

    correct? Since your page is called AccountDataTable.

  5. calvinrn says:

    //export to excel – returns a page reference to the AccountDataExcel page
    public PageReference exportToExcel() {
    return Page.AccountDataExcel;
    }

    Should be return Page.AccountDataTable since that is then ame of the page.

    Great work BTW.

    • anupj says:

      Hi Calvinrn,

      It’s probably not very clear in the post but I create another Visualforce page called “AccoutDataExcel” with the contentType attribute value set to “application/vnd.ms-excel#AccountTable.xls”. The button added to the first Visualforce page returns a page reference to this new page.

  6. Ashwin says:

    Hi, I have used this functionality. Its awesome. Thanks for this blog. But if records are exceeding 1000 then I am getting an error that ‘Collection size 1,007 exceeds maximum size of 1,000.’ So can you please guide me how to overcome this error.

  7. jjain says:

    you need to set readonly=true as the collection limit from 1000 would be changed to 2000

  8. Pratyush says:

    Hey, I’ve replaced the with the but the generated excel file still has that javascript code included in it. I would really appreciate any help on this. Thanks

    • Pratyush says:

      It seems that the comment box is not correctly showing tags. I meant that I’ve replaced the pageBlockTable with the dataTable but still not able remove the javascript code from the exported excel file

    • Pratyush says:

      Removing pageBlock from the VF Page fixes the issue 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: