Export VisualForce Data into Excel Sheet in 3 Easiest Steps

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 following code 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 and you also need to mention the file extension like .xls.

Here is the sample code:

Step 1:

To do this, lets 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.

Its very simple code here. You have to keep only 1 things in your mind while Export VisualForce Data into Excel Sheet.
Here in query, I have added LIMIT statement in the controller to limit the Contact returned to 10. If your Salesforce org have 10000 contacts then you will face issue like, "Collection size xxxx exceeds 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 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