• Devinsfdc
  • NEWBIE
  • 0 Points
  • Member since 2014

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
    Questions
  • 1
    Replies

I am working on putting together a VF page that will export a parent record to the first worksheet in an excel document and a related list of child records to the second tab.  Using this great post from Neeraj Gupta, http://neerajgupta999.blogspot.in/2012/06/multisheet-excel-from-apex.html, I'm able to create the worksheet, but unable to write data to the worksheets.  I'm not well versed in XML so am having trouble seeing where the issue is. The VF Code and Controller Code I"m using as a proof of concept is below.    

 

VF Code: 

<apex:page id="pg" standardStylesheets="false" standardController="Account" extensions="controllerClass" contenttype="application/vnd.ms-excel#MultiSheetExcel.xls">
{!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">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author></Author>
      <LastAuthor></LastAuthor>
      <Company></Company>
      <Version>11.6360</Version>
  </DocumentProperties>

  <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>
 <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Bottom"/>
            <Borders/>
            <Font/>
            <Interior/>
            <NumberFormat/>
            <Protection/>
        </Style>
        <Style ss:ID="s23">
          <Font x:Family="Swiss" ss:Bold="1"/>
        </Style>
        <Style ss:ID="s22">
          <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        </Style>
  </Styles>

  <Worksheet ss:Name="Account">
    <Table >
      
      
        
    <Row ss:StyleID="s23">
        <Cell><Data ss:Type="String">
                <apex:outputText escape="false" value="{!HTMLENCODE("Account Name")}"/>
              </Data></Cell>
    </Row>
 
 
 <!-- Repeat and create data -->
 <apex:repeat value="{!acct}" var="row">
      <Row>
        <Cell ss:StyleID="s22">
            <Data ss:Type="String">
                <apex:outputText escape="false" value="{!HTMLENCODE(row.Name)}"/>
            </Data>
        </Cell>
      </Row>
    </apex:repeat>
 
 
    </Table>
  </Worksheet>




  <Worksheet ss:Name="Contacts">
    <Table >
          
    <Row ss:StyleID="s23">
        <Cell><Data ss:Type="String"><apex:outputText escape="false" value="{!HTMLENCODE("First Name")}"/></Data></Cell> 
        <Cell><Data ss:Type="String"><apex:outputText escape="false" value="{!HTMLENCODE("Last Name")}"/></Data></Cell> 
    </Row>
 
 <!-- Repeat and create rows-->
    <!-- <apex:repeat value="{!cs}" var="2row">-->
    <Row>
     <Cell ss:StyleID="s22"><Data ss:Type="String"><apex:outputText escape="false" value="{!HTMLENCODE(2row.FirstName)}"/></Data></Cell>
     <Cell ss:StyleID="s22"><Data ss:Type="String"><apex:outputText escape="false" value="{!HTMLENCODE(2row.LastName)}"/></Data></Cell>
    </Row>
    <!--</apex:repeat>-->
   </Table>
  </Worksheet>
  </Workbook>
<apex:outputText value="{!endfile}" escape="false"/>
</apex:page>

  Controller: 

public without sharing class controllerClass{
  public string xmlheader {get;set;}
  public string endfile{get;set;}
  public Account acct{get;set;}
  public List<Contact> cs{get;set;}
 
  //-------------------------------------------------------------------------------------------------------------------------------
  //Constructor
  //-------------------------------------------------------------------------------------------------------------------------------
  public controllerClass() {
    //VF page gives some issues directly using the below code there, so wrap up in variable
    xmlheader ='<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
    endfile = '</Workbook>';
  }
  
     public controllerClass(ApexPages.StandardController stdController) {
        this.acct = (Account)stdController.getRecord();
    }
    
    public Account getAcct(){
        return acct;
    }
    
    public List<Contact> getCs(){
        cs = [SELECT id, FirstName, LastName, Email, AccountId FROM Contact WHERE AccountId = :acct.id];
        return cs;
    }
 
}