8

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

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 | |
  • 1
    Can't open the generated file in OpenOffice unfortunately. – David Gillen May 2 '14 at 15:51
  • I checked and it opened in MS excel. – MnZ May 2 '14 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 '14 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 '14 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 '14 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 | |

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.