8

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

2 Answers 2

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

8
  • 1
    Can't open the generated file in OpenOffice unfortunately. May 2, 2014 at 15:51
  • I checked and it opened in MS excel.
    – MnZ
    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
    – Mukul Goel
    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
    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/…
    – Mukul Goel
    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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.