+ Start a Discussion
Abhi_TripathiAbhi_Tripathi 

Freeze header pane in excel while Importing

Hi Everyone,

I am trying to free header pane in excel, while am exporting it from Salesforce.

There is a method in Java

sheet1.createFreezepane(0,1);

Is there anything using javascript or something else in Apex class or page so that we can freeze the header in excel.

Regards,
Abhi Tripathi
Salesforce Certified Developer
Sonam_SFDCSonam_SFDC
Hi Abhi,

There is something similar in jquery which might of use to you : http://brentmuir.com/projects/freezeheader/
worth a read!
Abhi_TripathiAbhi_Tripathi
Hi Sonam, 

thanks for the reply, but I have tried this on, not working, as salesforce made changes in the EXCEL file by itself. can't find exact place to modify my Excel on page.

Regards,
Abhi
Vaib ParaVaib Para
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');
 
 
}
 
}