8

Is anyone aware of generating excel from Visual force with multiple sheets in a workbook?

Improve this question

2 Answers 2

Reset to default
9 
public with sharing class ExportToExcelMultipleSheets {

public List<account> accountList{get;set;}
public List<contact> contactList{get;set;}
public String xlsHeader{
    get{
        String strHeader = '';
        strHeader += '<?xml version="1.0"?>';
        strHeader += '<?mso-application progid="Excel.Sheet"?>';
        return strHeader;
    }
}
public ExportToExcelMultipleSheets(){
    accountList = [select Name,Id 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');
    }
   }

VF Page 1

<apex:page controller="ExportToExcelMultipleSheets" sidebar="false">
<apex:form >
 <apex:pageblock title="Accounts and Contacts">
   <apex:pageblockbuttons >
     <apex:commandbutton value="Export All Accounts and Contacts" action="{!exportAll}"/>
  </apex:pageblockbuttons>
<apex:pageblocksection columns="2">
<apex:pageblocksectionitem >
   <apex:pageblocktable title="All Accounts" value="{!accountList}" var="account">
   <apex:facet name="caption">All Accounts</apex:facet>
    <apex:column value="{!account.name}"/>
   </apex:pageblocktable>
  </apex:pageblocksectionitem>
  <apex:pageblocksectionitem >

  <apex:pageblocktable title="All Contacts" value="{!contactList}" var="contact">
     <apex:facet name="caption">All Contacts</apex:facet>
      <apex:column value="{!contact.name}"/>
      <apex:column value="{!contact.email}"/>
      <apex:column value="{!contact.account.name}"/>
   </apex:pageblocktable>
  </apex:pageblocksectionitem>
  </apex:pageblocksection>
 </apex:pageblock>
 </apex:form>

VF page 2

  <apex:page controller="ExportToExcelMultipleSheets" contentType="txt/xml#myTest.xls" cache="true">
    <apex:outputText value="{!xlsHeader}"/>
    <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>

Above is the code for doing the same. Please refer to the link below for the demo

demo link

Improve this answer
8
  • 1
    Can't open the generated file in OpenOffice unfortunately.
    – David Gillen
    Commented May 2, 2014 at 15:51
  • I checked and it opened in MS excel.
    – MnZ
    Commented May 2, 2014 at 15:54
  • This is specific to ms office .. we need to import the libraries for xml generation if need to be opened in open office
    – user3170
    Commented May 2, 2014 at 16:11
  • Thanks for this answer @MukulGoel. I was wondering where you found the documentation for this? I have been having trouble with exporting for excel in terms of the lack of documentation. Perhaps you just found a blog post on this, but if you have the location of the "official" doco, it would be interesting to see.
    – lindon fox
    Commented May 7, 2014 at 3:29
  • 1
    @lindonfox I have referred to Microsoft documentation for the above solution. This is like generating a xml and then converting it to excel. The link for the official documentation from microsoft is msdn.microsoft.com/en-us/library/office/…
    – user3170
    Commented May 7, 2014 at 5:28
0

i have faced this problem many times, when it works on demo but not when i try it, the solution is you should not try to run it using preview (it generates an extra script which blocks you excel file from displaying) trying using a button.

Improve this answer

You must log in to answer this question.