Export to Excel with multiple worksheets in visualforce

Export to Excel with multiple worksheets in visualforce

We need to use XML tags to get the excel with multiple worksheets. We can use visualforce tags also with XML tags. We can use Styles like color , size , height for formatting the cells of excel sheet. For XML spreadsheet reference, go to
XML Spreadsheet Reference

Click for Demo

Below is the Visualforce page code that will list all Accounts and Contacts. There is a button that will export all these accounts and contacts to excel sheet with 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>

Below is the Visualforce page code that will generate 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>

Below controller is used by both the visualforce pages: 

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');


}

}