Visualforce to Excel
by Sati Hillyer on December 22, 2008 at 11:45 AM
You probably know that a Visualforce Page can be easily converted to a PDF (if not, check out Quote2PDF), but did know Visualforce can also generate a Microsoft Excel Worksheet?
By simply modifying the ContentType attribute on the <apex:page> tag, your Visualforce code will automatically generate an Excel document. For example, the following code will create a table of Contact data for a given Account:
<apex:pageBlock title="Hello {!$User.FirstName}!">
You are viewing the {!account.name} account.
</apex:pageBlock>
<apex:pageBlock title="Contacts">
<apex:pageBlockTable value="{!account.Contacts}" var="contact">
<apex:column value="{!contact.Name}"/>
<apex:column value="{!contact.Email}"/>
<apex:column value="{!contact.Phone}"/>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:page>
If an ID of a valid Account record is specified as a query parameter in the URL for the page, you will see the following table of data:
We can tell Visualforce to convert this page to an Excel doc by adding:
The next time the page is loaded, you will receive the following prompt:
This is a powerful and easy-to-use feature. You can learn more about ContentType in the documentation.
TrackBack
TrackBack URL for this entry: http://www.typepad.com/services/trackback/6a00d8341cded353ef010536938791970c
Listed below are links to weblogs that reference Visualforce to Excel:
All rights reserved Various trademarks held by their respective owners.
General Enquiries: 415-901-7000 | Fax: 415-901-7040 | Sales: 1-800-no-software
Comments
Posted by Reid Carlberg on December 22, 2008 07:09 PM:
Love it.
Posted by Val on December 23, 2008 02:58 AM:
And it can me XML too
contenttype="text/xml"...or indeed csv !
Posted by Marc on December 26, 2008 09:59 AM:
I KNOW I can find a use for this.
Thanks!
Posted by michaelforce on December 29, 2008 01:56 PM:
That's pretty slick! Thanks Sati
Posted by David on December 29, 2008 02:01 PM:
Is this only for Standard Object?
Posted by Sati Hillyer on December 29, 2008 02:40 PM:
Hi David,
This will work for both Standard and Custom objects. Thanks for asking!
Sati
Posted by David on December 29, 2008 03:30 PM:
I tried this in CS2 and kept getting error. Even for a blank pageblocktable for a custom object. Is this only in NS3?
Posted by David on December 29, 2008 03:53 PM:
Sati: Have you tried running this in Internet Explorer? I ran into a problem using IE. but it works fine in FireFox
Posted by Andrew Waite on December 29, 2008 03:59 PM:
This should work across all instances including sandbox (CS2). David, perhaps you can open a discussion with more detail about your error on the Visualforce discussion board.
Posted by Sati Hillyer on December 29, 2008 04:20 PM:
I just tried in IE6 and wasn't able to generate the excel doc. You may want to try against IE7. If it's still not working, please log a case here: www.appexchange.com/support
Posted by Ben Hizak on December 30, 2008 04:19 PM:
any way to control Cell's formatting? (font, size, color)
if yes,
any way to create conditional formatting?
Thanks in advance!
Posted by Doug Sharpe on January 2, 2009 07:02 PM:
I suspect your problem is the known bug when rendering VF as Excel under https. That has been an issue since Summer 08 - not sure why it hasn't been fixed. In the meantime, use http instead.
That means you need to assemble the full url which is tricky with sandbox vs production. You can do something like this in your controller:
PageReference excelPage = new PageReference('http://'+ApexPages.currentPage().getHeaders().get('Host')+'/apex/HousefileExcel');
Posted by Varun on January 15, 2009 03:37 AM:
Feature is great. but everytime the file is downloaded the Present page where export button is displayed Becomes BLANK. How can I resolve that issue?
Posted by Sumiran on January 28, 2009 01:14 AM:
I tried the same thing and is working fine.But when I do the exporting in MAC OS I get the list plus some gibberish code at the top of my excel document->
if(!window.sfdcPage){window.sfdcPage = new ApexPage();} UserContext.initialize({'isAccessibleMode':false,'ampm':['AM','PM'],'locale':'en_US','dateTimeFormat':'M/d/yyyy h:mm a','today':'1/16/2009 1:31 AM','dateFormat':'M/d/yyyy','language':'en_US','siteUrlPrefix':'','userPreferences':[{'value':false,'index':119,'name':'HideUserLayoutStdFieldInfo'} ,{'value':false,'index':87,'name':'HideInlineSchedulingSplash'} ,{'value':false,'index':116,'name':'HideRPPWarning'} ,{'value':false,'index':115,'name':'DefaultTaskSendNotification'} ,{'value':false,'index':114,'name':'OverrideTaskSendNotification'} ,{'value':false,'index':112,'name':'HideInlineEditSplash'} ],'startOfWeek':'1'} );
Posted by Igor Androsov on April 16, 2009 11:12 AM:
How can we pass query string parameters to control what data is being exported to Excel file?
It is easy to export data on generic query but I need to control export query parameters and finding that its not possible to do with this method of Excel export. Is there any way I can have a dynamic query data be exported and downloaded by users as Excel file?
Posted by kelly on July 13, 2009 11:18 PM:
but if datatable is active ,it is according to the selectlist , how should i can do to export to excel
Posted by Chang Shin on July 24, 2009 09:54 AM:
Exporting in excel is ok in Standard.
but with extension, the excel is blank data.
It is like this:
.... standardController=... extensions=... contenttype=..application/vnd.ms-excel..
Do you know how can get excel file with extension?
Chang Shin