Export VisualForce Data into Excel Sheet in 3 Easiest Steps

One of the most common Business requirements is to export a VisualForce page with data into an Excel sheet.

If you want to import CSV file In Salesforce using Apex then check Import CSV file In Salesforce using Apex in 3 Easiest Steps

Here in this blog, I am exporting contact data into AllContacts.xls sheet using Visualforce page.

To export data from a Visualforce page, the only real change that you have to make to the page is to change the content type of the page to “application/vnd.ms-excel” and also set a file name. 
<apex:page contentType=”application/vnd.ms-excel#AllContacts.xls”> </apex:page>

By simply modifying the ContentType attribute on the <apex:page> tag, your Visualforce page code will automatically generate an Excel document and download it automatically. 

For example, the below code snippets will create a table of Contact data for a given Account. After # in the contentType attribute value, it is the name of the excel file that will be generated after opening the vf page and you also need to mention the file extension like .xls.

Here is the sample code:

Step 1:

To do this, let's start off with a super simple controller. Create Apex Class ExportAllContactsHandler

public class ExportAllContactsHandler{

    public List<Contact> lstContact {set;get;}

   

    public ExportAllContactsHandler(){

        // Here I added limit 10, you can add filter criteria which you want

        lstContact = [Select Id, Name, Email, Phone From Contact Limit 10];   

    }

}

Step 2: 

Create Visualforce Page with name Export_All_Contacts:

<apex:page controller="ExportAllContactsHandler" contentType="application/vnd.ms-excel#AllContacts.xls" cache="true">

    <apex:pageBlock title="Export All Contacts">

        <apex:pageBlockTable value="{!lstContact}" var="objContact">

            <apex:column value="{!objContact.Name}"/>

            <apex:column value="{!objContact.Email}"/>

            <apex:column value="{!objContact.Phone}"/>

        </apex:pageBlockTable>

    </apex:pageBlock>

</apex:page>

Step 3:

You can click on Preview button which is on Visualforce page 
OR 
You can Go to the URL and type (Change the yoursalesforceinstance with your Salesforce org URL)

https://yoursalesforceinstance.com/apex/Export_All_Contacts

AllContact.xls downloaded automatically into your local system.

When you have created this Visualforce page and you go to view the page, your browser should automatically download the .xls file. When you try to open it, you will most likely be presented with an error!
Click OK and open the file.

It's very simple code here. You have to keep only 1 things in your mind while Export VisualForce Data into Excel Sheet.
Here in the query, I have added the LIMIT statement in the controller to limit the Contact returned to 10. If your Salesforce org have 10000 contacts then you will face an issue like, "Collection size xxxx exceeds the maximum size of 1000".
We should always limit the query here so as not to reach the View State limit, but in this case, it is done to keep the file returned nice and simple for later processing. 
What are the Best practices to optimize view state, then check this : An Introduction to Visualforce View State in Salesforce

Enjoy! If you have any questions, comments etc. please feel free to let me know. As always, please feel free to get in touch me as I would be more than happy to assist you with any of your Salesforce development needs.

Next post: 7 Easy Steps to Generate Apex Class From WSDL In Salesforce