XML Functionality with Visualforce?

The code below has been modified from this solution: http://salesforce.stackexchange.com/questions/34261/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>

April 17, 2015
·
Answer
·
Like
0
·
Follow
1

tggagne
Can you post the actual result XML?

April 17, 2015
·
Like
0
·
Dislike
0

dbalke1.3880741030651309E12
When I have this code in visualforce and I try to get the excel spreadsheet, all I get is an error message that states "The File is corrupt and cannot be opened". The actual XML that I am looking for is below

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Balke, Dawn</Author>
<LastAuthor>Windows User</LastAuthor>
<Created>2015-04-15T22:17:05Z</Created>
<LastSaved>2015-04-16T21:49:36Z</LastSaved>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>9315</WindowHeight>
<WindowWidth>9705</WindowWidth>
<WindowTopX>270</WindowTopX>
<WindowTopY>675</WindowTopY>
<ActiveSheet>3</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s18" ss:Name="Currency">
<NumberFormat
ss:Format="_("$"* #,##0.00_);_("$"* $#,##0.00$;_("$"* "-"??_);_(@_)"/>
</Style>
<Style ss:ID="s62">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" x:Family="Swiss" ss:Bold="1"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s64">
<NumberFormat ss:Format=""$"#,##0.00"/>
</Style>
<Style ss:ID="s67" ss:Parent="s18">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" x:Family="Swiss" ss:Bold="1"/>
<Interior/>
<NumberFormat
ss:Format="_([$$-409]* #,##0.00_);_([$$-409]* $#,##0.00$;_([$$-409]* "-"??_);_(@_)"/>
<Protection/>
</Style>
<Style ss:ID="s68" ss:Parent="s18">
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<NumberFormat
ss:Format="_([$$-409]* #,##0.00_);_([$$-409]* $#,##0.00$;_([$$-409]* "-"??_);_(@_)"/>
</Style>
</Styles>
<Worksheet ss:Name="Standard Price Book">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="5" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Column ss:Width="80.25"/>
<Row>
<Cell ss:StyleID="s62"><Data ss:Type="String">Standard Price Book</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">AV-100</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">AV-200</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">AV-GPSII</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">AV-GPS1</Data></Cell>
</Row>
</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>
<Worksheet ss:Name="Test Price Book">
<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="6" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Column ss:Width="99.75"/>
<Column ss:Width="80.25" ss:Span="1"/>
<Row>
<Cell ss:StyleID="s62"><Data ss:Type="String">Product Code</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">Unit Price</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">H28718-58</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="Number">782.04399999999998</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">2-8-282HAN</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="Number">433</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">MF01-0069</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="Number">277.5</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">INS-1004</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="Number">1680</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">MF01-0075</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="Number">1616</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<TabColorIndex>13</TabColorIndex>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>0</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Novation Tier - 1">
<Names>
<NamedRange ss:Name="_FilterDatabase"
ss:RefersTo="='Novation Tier - 1'!R1C1:R375C2" ss:Hidden="1"/>
</Names>
<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="375" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Column ss:Width="99.75"/>
<Column ss:Width="80.25" ss:Span="1"/>
<Row>
<Cell ss:StyleID="s62"><Data ss:Type="String">Product Code</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">Unit Price</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">2-8-060HAN</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">511.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<TabColorIndex>10</TabColorIndex>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>6</ActiveRow>
<ActiveCol>5</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<AutoFilter x:Range="R1C1:R375C2"
xmlns="urn:schemas-microsoft-com:office:excel">
</AutoFilter>
</Worksheet>
<Worksheet ss:Name="MedAssets">
<Names>
<NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=MedAssets!R1C1:R382C2"
ss:Hidden="1"/>
</Names>
<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="382" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Column ss:Width="99.75"/>
<Column ss:StyleID="s68" ss:Width="80.25"/>
<Column ss:Width="80.25"/>
<Row>
<Cell ss:StyleID="s62"><Data ss:Type="String">Product Code</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell ss:StyleID="s67"><Data ss:Type="String">Unit Price</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">2-8-282HAN</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">343.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">2-8-280HAN</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">382.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">CER-1009</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">2404.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">CER-1008</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">2099.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">ADV-1062</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">2645.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">CLM-110-HU0141</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">298.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">ML02-0121</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">3.75</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">ADV-1007</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">48706.00</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">ADV-1008</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">46746.00</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">MF01-0071</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">118.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">MF01-0070</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">24.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">2-8-160HAN</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">450.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">2-8-060HAN</Data><NamedCell
ss:Name="_FilterDatabase"/></Cell>
<Cell><Data ss:Type="String">456.00</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<TabColorIndex>40</TabColorIndex>
<Selected/>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>12</ActiveRow>
<ActiveCol>4</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<AutoFilter x:Range="R1C1:R382C2"
xmlns="urn:schemas-microsoft-com:office:excel">
</AutoFilter>
</Worksheet>
</Workbook>

April 20, 2015
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

XML Functionality with Visualforce?

You need to sign in to do that.