This is how it is done.


Hi, This is the code which has freeze panes. I found out a way to do that. Headers will be fixed when the visualforce page is downloaded as excel.

My next requirement is to have conditional coloring.?? Any idea on how to do that?

<apex:page controller="ExportToExcelMultipleSheets" contentType="application/vnd.ms-excel#Candidate.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 ss:Vertical="Top"/>
 <Borders/>
<Font ss:Bold="1"/>
 <Interior ss:Color="#800080" ss:Pattern="Solid" />
 <NumberFormat/>
 <Protection/>
 </Style>
 </Styles>
 
 <Worksheet ss:Name="Contacts">
 <Table  x:FullColumns="1" x:FullRows="1">

 <Column  ss:Width="100" />
 <Column  ss:Width="100" />
 <Column  ss:Width="100"/>
 <Row ss:Height="25">
 <Cell ss:StyleID="s1"><Data ss:Type="String" >Contact Name</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
 <Cell ss:StyleID="s1"><Data ss:Type="String" >Email</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
 <Cell ss:StyleID="s1"><Data ss:Type="String" >Account Name</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
 <Cell ss:StyleID="s1"><Data ss:Type="String" >Birth Date</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
 <Cell  ss:StyleID="s1"><Data ss:Type="String" >Mobile</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
 </Row>
 <apex:repeat value="{!contactList}" var="contact">
 <Row>
 <Cell><Data ss:Type="String"><apex:outputText style="color:#000;" value="{!contact.name}"/></Data></Cell>
 <Cell><Data ss:Type="String">{!contact.email}</Data></Cell>
 <Cell><Data ss:Type="String">{!contact.account.name}</Data></Cell>
 <Cell><Data ss:Type="String"><apex:outputText value="{0,date,MM/dd/yy}" ><apex:param value="{!contact.Birthdate}" /></apex:outputText></Data></Cell>
 <Cell><Data ss:Type="String">{!contact.MobilePhone}</Data></Cell>
 </Row>
 </apex:repeat>
 <Row ss:StyleID="s1">
 <Cell><Data ss:Type="String">Planning</Data></Cell>
 </Row>
 <Row>
 <Cell><Data ss:Type="String">test</Data></Cell>
 </Row>
 </Table>
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <FilterOn/>
   <FreezePanes/>
   <FrozenNoSplit/>
   <SplitHorizontal>1</SplitHorizontal>
   <TopRowBottomPane>1</TopRowBottomPane>
   <SplitVertical>1</SplitVertical>
   <LeftColumnRightPane>1</LeftColumnRightPane>
   <ActivePane>0</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
    </Pane>
    <Pane>
     <Number>1</Number>
    </Pane>
    <Pane>
     <Number>2</Number>
    </Pane>
    <Pane>
     <Number>3</Number>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
  </Workbook>

</apex:page>


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,MobilePhone,Birthdate, email from Contact LIMIT 50];
 
}
 
public Pagereference exportAll(){
    return new Pagereference('/apex/exportAll');
 
 
}
 
}