You need to sign in to do that
Don't have an account?
Vaib 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.
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.
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
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');
}
}
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.
{!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>