The code below has been modified from this solution: How to generate excel files with the multiple sheets in VisualForce which does work but I was looking to add extra functionality such as freezing the top row pane, changing tab color, and filtering.....Can anyone point me in the right direction on how I can do this? I don't really know much about XML. I have tried this to freeze the panes but it keeps saying the excel file is corrupt and won't open
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<SplitVertical>1</SplitVertical>
<LeftColumnRightPane>1</LeftColumnRightPane>
<ActiveRow>2</ActiveRow>
<Panes>
<Pane>
<Number>1</Number>
</Pane>
<Pane>
<Number>2</Number>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
Actual Code
<apex:page controller="CustomPBListController" 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="Standard Price Book" >
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="80"/>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Standard Price Book</Data> </Cell>
</Row>
<apex:repeat value="{!standard}" var="stpb">
<Row>
<Cell><Data ss:Type="String">{!stpb.ProductCode}</Data></Cell>
</Row>
</apex:repeat>
</Table>
</Worksheet>
<Worksheet ss:Name="Test Price Book">
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="100"/>
<Column ss:Width="80"/>
<Column ss:Width="80"/>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Product Code</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Unit Price</Data></Cell>
</Row>
<apex:repeat value="{!pb3}" var="test">
<Row>
<Cell><Data ss:Type="String">{!test.ProductCode}</Data></Cell>
<Cell><Data ss:Type="String">{!test.UnitPrice}</Data></Cell>
</Row>
</apex:repeat>
</Table>
</Worksheet>
<Worksheet ss:Name="Novation Tier - 1">
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="100"/>
<Column ss:Width="80"/>
<Column ss:Width="80"/>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Product Code</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Unit Price</Data></Cell>
</Row>
<apex:repeat value="{!pb4}" var="novation1">
<Row>
<Cell><Data ss:Type="String">{!novation1.ProductCode}</Data></Cell>
<Cell><Data ss:Type="String">{!novation1.UnitPrice}</Data></Cell>
</Row>
</apex:repeat>
</Table>
</Worksheet>
<Worksheet ss:Name="MedAssets">
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="100"/>
<Column ss:Width="80"/>
<Column ss:Width="80"/>
<Row>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Product Code</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Unit Price</Data></Cell>
</Row>
<apex:repeat value="{!pb5}" var="medassets">
<Row>
<Cell><Data ss:Type="String">{!medassets.ProductCode}</Data></Cell>
<Cell><Data ss:Type="String">{!medassets.UnitPrice}</Data></Cell>
</Row>
</apex:repeat>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<TabColorIndex>51</TabColorIndex>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>27</ActiveRow>
<ActiveCol>3</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</apex:page>
Update to show Corrupt file error message. I also added the section to the bottom of the apex page that when put into the code it throws the error. Once I take it out, the file is fine.