How to export data in Excel using Visualforce

Posted by Madhusudhana Reddy Guda | Posted On February 6th, 2015|Salesforce

When it comes to handling, segregating and systematically arranging data, sorting 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 xmlns=”urn:schemas-microsoft-com:office:spreadsheet”

xmlns:o=”urn:schemas-microsoft-com:office:office”

xmlns:x=”urn:schemas-microsoft-com:office:excel”

xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”

xmlns:html=”http://www.w3.org/TR/REC-html40″>

<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:                      

image 1

 

 

 

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

Image 3

 

 

 

 

 

 

Image 3

 

 

 

 

 

 

 

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

 

Categories: Salesforce

Madhusudhana

Madhusudhana Guda has been a part of the AppShark family since the last 6 years. He is currently a Senior Software Engineer in AppShark who specializes across Salesforce technologies. In his free time, he enjoys watching cricket and plays the game well too!

Leave a Reply