0

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. enter image description here

7
  • the best way to fix this is to reverse engineer it, create an excel having all the formatting that you expect (top row freezed, tab color etc.) and then save it as xml. try and match the xml generated by your code with that xml and you will know whats missing. Apr 15, 2015 at 19:42
  • Ok I can now see how to get what I need to put into my visualforce page, however it keeps telling me i have a corrupt file. Is there a way to debug that statement to figure out what is missing? Apr 15, 2015 at 21:57
  • 1
    yes, when it throws that error it also creates an error log and shows the path, do you see that path in the error message? Can you please share the snapshot of the error? Apr 16, 2015 at 3:09
  • I have added the picture to show the error message, but I don't see an error log anywhere..... Apr 16, 2015 at 13:45
  • 1
    Comparing the code you have vs. the working code in the example, the route I'd take is to strip it down to basics first: - Remove all in-place references to the data you're pulling in (may be something throwing off the format xml nodes) - Remove your apex:repeat for the time being (again, get your data out of there) Once you can get it to generate a functional XLS file as basic as possible, then start introducing your data. May 10, 2015 at 13:34

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.