+ Start a Discussion
dbalke1.3880741030651309E12dbalke1.3880741030651309E12 

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>
tggagnetggagne
Can you post the actual result XML?
dbalke1.3880741030651309E12dbalke1.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="_(&quot;$&quot;* #,##0.00_);_(&quot;$&quot;* \(#,##0.00\);_(&quot;$&quot;* &quot;-&quot;??_);_(@_)"/>
  </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="&quot;$&quot;#,##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]* &quot;-&quot;??_);_(@_)"/>
   <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]* &quot;-&quot;??_);_(@_)"/>
  </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>