How To Export Data in Excel Using Visualforce

When it comes to handling, segregating and systematically arranging data, nobody does it better than Salesforce! Sometimes though, the standard reporting interface of the Salesforce poses a challenge and may not suit the way you want the information to be represented.  Also, this is when you wish that good old Microsoft Excel was available to represent the data across various divisions in a systematic manner.

Did you know this is possible through a simple Visualforce code? As the second in our series on "Apex Programming Tips and Tricks", we present you a simple solution to generate lists of Salesforce data outside of the standard reporting interface.

We need to use XML tags to get Microsoft Excel with multiple worksheets and use Visualforce tags as well. Also, we can modulate the various parameters like color, size and height for the cells of excel sheet through this Visualforce code.

Here’s a sample code used to generate excel with multiple sheets –

---------Page------------

<apex:page controller="Test class" contentType=" application/vnd.ms-excel#AccountExcel.xls " >

<Workbook>

<Styles>

<Style ss_ID="s1">

<Alignment/>

<Borders/>

<Font ss_Bold="1"/>

<Interior/>

<NumberFormat/>

<Protection/>

</Style>

</Styles>

<Worksheet ss_Name="Accounts">

<Table x_FullColumns="1" x_FullRows="1">

<Column ss_Width="170"/>

<Row>

<Cell ss_StyleID="s1"><Data ss_Type="String" >Account Name</Data></Cell>

</Row>

<apex:repeat value="{!accountList}" var="account">

<Row>

<Cell><Data ss_Type="String">{!account.name}</Data></Cell>

</Row>

</apex:repeat>

</Table>

</Worksheet>

<Worksheet ss_Name="Contacts">

<Table x_FullColumns="1" x_FullRows="1">

<Column ss_Width="170"/>

<Column ss_Width="280"/>

<Column ss_Width="330"/>

<Row>

<Cell ss_StyleID="s1"><Data ss_Type="String" >Contact Name</Data></Cell>

<Cell ss_StyleID="s1"><Data ss_Type="String" >Email</Data></Cell>

<Cell ss_StyleID="s1"><Data ss_Type="String" >Account Name</Data></Cell>

</Row>

<apex:repeat value="{!contactList}" var="contact">

<Row>

<Cell><Data ss_Type="String">{!contact.name}</Data></Cell>

<Cell><Data ss_Type="String">{!contact.email}</Data></Cell>

<Cell><Data ss_Type="String">{!contact.account.name}</Data></Cell>

</Row>

</apex:repeat>

</Table>

</Worksheet>

</Workbook>

</apex:page>

---------class----------

public with sharing class TestController {

public Account objAccount {get;set;}

public List<account> accountList{get;set;}

public List<contact> contactList{get;set;}

public TestController (){

accountList = [select Name,Id,AccountNumber,Industry,Phone from Account LIMIT 10];

contactList = [SELECT id,Name, LastName, Account.Name, Email FROM Contact WHERE Email != '' AND Account.Name != '' LIMIT 5];

}

public Pagereference exportAll(){

return new Pagereference('/apex/ExportAll');

}

}

A quick glossary on the terms used in the Visualforce code:

<Workbook> tag is used to define a workbook that will contain one or more Worksheet elements. This is the top-most element of an XML Spreadsheet document and is required.

<Worksheet> tag is used todefine a worksheet within the current workbook. At least one instance of this element is required within a valid XML Spreadsheet document.

<Table> tag is used to define the cell table within the current worksheet.

<Cell> tag is used to define a single cell within the contained row.

<Data> tag is used to specify the value of the cell. The value should be specified in the appropriate format based on the value of the cell's Type attribute.

Here’s how you manage the width and height of cells in the Excel sheet:

Syntax:

<Columnss:Width="170"/>

<ss:Row ss_Height="3">

<ss:Cell ss_Width=”100” />

How to name the Excel

As discussed above to export Visualforce page as excel, we use <apex:page> tag and should use contentType=" application/vnd.ms-excel#AccountExcel.xls "

In this ContentType attribute, we can give the name for excel as required and can use merge fields for the same.

<apex:page controller= “Test controller” contentType=" application/vnd.ms-excel#Account-{!objAccount.Name}.xls " >

This is how it will appear to be:

This is how the final Excel will look like along with extra worksheets:

Managing data has never been easier before – Make your Salesforce activities simpler, neater and organized with this piece of code! Happy Coding!