Exporting To Excel With Multiple Worksheets In Visualforce

To create an Excel file with multiple worksheets, XML tags are required. These XML tags can be combined with Visualforce tags. Utilizing styles such as color, size, and height allows for formatting the cells within the Excel sheet. For detailed reference on XML spreadsheets, please refer to the documentation.

XML Spreadsheet Reference

Click for Demo

Here’s the Visualforce page code that displays a list of all Accounts and Contacts. It includes a button that exports these records to an Excel sheet featuring two worksheets.

<apex:page controller="ExportToExcelMultipleSheets">
<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" ><b>All Accounts</b></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" ><b>All Contacts</b></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>
</apex:page>

Here’s the Visualforce page code that generates an Excel file.

<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>
</apex:page>

Both Visualforce pages utilize the following controller:

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 id, name from Account LIMIT 50];
    contactList = [Select id, name, account.name, email from Contact LIMIT 50];
 
}
 
public Pagereference exportAll(){
    return new Pagereference('/apex/exportAll');
 
 
}
 
}