+ Start a Discussion
iKnowSFDCiKnowSFDC 

Exporting to multiple excel worksheets

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;
    }
 
}

 

DevinsfdcDevinsfdc
Hi

I hope you solved this.

If not you are using two constructors in the controller class in which only one class is called initially when the page is loaded.

so you have to remove one of the constructor. 

it looks like this 

public controllerClass1(ApexPages.StandardController controller) {
       
        xmlheader ='<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
        endfile = '</Workbook>';
         this.acct = (Account)controller.getRecord();
        System.Debug('AccoundId----->'+acct);
        acct = [select Name,Id from Account where Id =:acct.Id];
        if(acct.Id!=NULL)
        cs = [SELECT id, FirstName, LastName,Name, Email, AccountId FROM Contact WHERE AccountId = :acct.id];
       
    }

let me know if you didn't solve this