+ Start a Discussion
Vaib ParaVaib Para 

Freeze headers when Export excel from Visualforce page?

Hi All,

Can anyone please help me with a code that freezes my header row when I export an excel file from the Visualforce page?. I am able to add autofilters though but I have not found a way to freeze headers. 

Thank you all in advance.
NagaNaga (Salesforce Developers) 
Hi Vaib,

Pleasee see the link below

http://stackoverflow.com/questions/9185928/how-can-i-create-a-freeze-pane-in-excel-when-exporting-via-asp-net-using-xml

I know you can do it inside visual force using Jquery but that is then without the exporting to excel.

http://brentmuir.com/projects/freezeheader/

Best Regards
Naga Kiran
Vaib ParaVaib Para
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');
 
 
}
 
}


 
NK@BITNK@BIT
You can try this blog..

https://nitinkhunalsalesforce.wordpress.com/2016/10/27/visualforce-export-excel-report-using-remote-action-and-alasql/

You can export excel using javascript remoting, For this you don't need to create additional visualforce page to renderAs excel.
Kt YadavKt Yadav
<apex:page id="pg" standardStylesheets="false" controller="xyzController" contenttype="application/vnd.ms-excel#MultiSheetExcel.xls">
{!xmlheader}
<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">

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>8535</WindowHeight>
    <WindowWidth>12345</WindowWidth>
    <WindowTopX>480</WindowTopX>
    <WindowTopY>90</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
   
</DocumentProperties>

<Styles>
    <Style ss:ID="Default" ss:Name="Normal">
        <Alignment ss:Vertical="Bottom"/>
        <Borders/>
        <Font ss:bgcolor="#FF0000"/>
        <Interior ss:bgcolor="#FF0000"/>
        <NumberFormat/>
        <Protection/>
    </Style>
    <Style ss:ID="s16">
        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1" ss:Italic="1"/>
        <Interior ss:Color="#92D050" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s23">
        <Font x:Family="Swiss" ss:Bold="0" ss:bgcolor="#FF0000"/>
    </Style>
    <Style ss:ID="s66">
        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="16" ss:Color="#000000" ss:Bold="1" ss:Italic="1"/>
    </Style>
</Styles>
<!--------------First Sheet ------------->
<Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="100">
        <Column ss:Width="180"/>
        <Row>
            <Cell ss:StyleID="s66">
                <Data ss:Type="String">
                    <img src="https://univar--d01sprt01--c.cs14.content.force.com/servlet/servlet.ImageServer?id=xxx&oid=xxx"/>
                 
                <!-- <apex:image value="{!$Resource.SPIPharama}" />
                 </Data> -->
             </Cell>
        </Row>
        <Row>
            <Cell ss:StyleID="s16" ><Data ss:Type="String">Field1</Data></Cell>
            <Cell ss:StyleID="s16"><Data ss:Type="String">Field2</Data></Cell>
        </Row>
    <apex:repeat value="{!sample}" var="row">
        <Row>
            <Cell ss:StyleID="s23"><Data ss:Type="String">{!row.id}</Data></Cell>
            <Cell ss:StyleID="s23"><Data ss:Type="String">{!row.Name}</Data></Cell>
       </Row>
    </apex:repeat>
   </Table>
</Worksheet>

</Workbook>
</apex:page>