Visual Force Code and Controller for Excel export with multiple workbooks.

Hello, I am fairly new to this and need help with this Visualforce Code and Controller for excel export for multiple workbooks. I was able to find this online and made a few tweaks to make it work. With the code I am able to populate columns and rows fine and even the workbook, but what I am wanting is to be able to filter each workbook separately. Example, for "Canada", I want to use Region__c = "Canada". Please Help.

Vfpage

<apex:page controller="ExportToExcelMultipleSheets" 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&#8221">;
                
   <Styles>

   <Style ss:ID="s1">

   <Alignment/>

   <Borders/>

   <Font ss:Bold="1"/>

   <Interior/>

    <NumberFormat/>

    <Protection/>

     </Style>

   </Styles>

  <Worksheet ss:Name="Canada">

  <Table x:FullColumns="1" x:FullRows="1">

  <Column ss:Width="170"/>

  <Row>

<Cell ss:StyleID="s1"><Data ss:Type="String">Region</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Terminal Location</Data></Cell>      

<Cell ss:StyleID="s1"><Data ss:Type="String">Agreement Name</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Legacy Agreement Name</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Status Category</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Status</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">NuCM Number</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Agreement Type</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Start Date</Data></Cell>

<Cell ss:StyleID="s1"><Data ss:Type="String">End Date</Data></Cell>

<Cell ss:StyleID="s1"><Data ss:Type="String">Days Remaining on Contract</Data></Cell>      

<Cell ss:StyleID="s1"><Data ss:Type="String">Renewal Notice</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Renewal Term</Data></Cell>      

<Cell ss:StyleID="s1"><Data ss:Type="String">Description of Services</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Products</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Tank #</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Tank Cap.</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Combined Space</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Monthly Base or Minimum Thruput</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Full Record Id</Data></Cell>

<Cell ss:StyleID="s1"><Data ss:Type="String">Link to Salesforce</Data></Cell>
     
  </Row>

  <apex:repeat value="{!AgreementList}" var="account">

  <Row>

<Cell><Data ss:Type="String">{!account.Region__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Terminal_Location__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Name}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Legacy_Agreement_Number__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Apttus__Status_Category__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Apttus__Status__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Apttus__Agreement_Number__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Agreement_Type__c}</Data></Cell> 
      
<Cell><Data ss:Type="String">{!account.Apttus__Contract_Start_Date__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Apttus__Contract_End_Date__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Days_Remaining_on_Contract__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Apttus__Renewal_Notice_Days__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Apttus__Renewal_Notice_Date__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Apttus__Auto_Renew_Term_Months__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Description_of_Services__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Products__c}</Data></Cell>       

<Cell><Data ss:Type="String">{!account.Tank_s_if_segregated_storage__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Tank_Capacity__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Combined_Space__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Monthly_Base_or_Minimum_Thruput_Revenue__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Full_Record_Id__c}</Data></Cell>    
      
</Row>

</apex:repeat>

</Table>

</Worksheet>
        
<Worksheet ss:Name="North East">

  <Table x:FullColumns="1" x:FullRows="1">

  <Column ss:Width="170"/>

  <Row>

<Cell ss:StyleID="s1"><Data ss:Type="String">Region</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Terminal Location</Data></Cell>      

<Cell ss:StyleID="s1"><Data ss:Type="String">Agreement Name</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Legacy Agreement Name</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Status Category</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Status</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">NuCM Number</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Agreement Type</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Start Date</Data></Cell>

<Cell ss:StyleID="s1"><Data ss:Type="String">End Date</Data></Cell>

<Cell ss:StyleID="s1"><Data ss:Type="String">Days Remaining on Contract</Data></Cell>      

<Cell ss:StyleID="s1"><Data ss:Type="String">Renewal Notice</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Renewal Term</Data></Cell>      

<Cell ss:StyleID="s1"><Data ss:Type="String">Description of Services</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Products</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Tank #</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Tank Cap.</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Combined Space</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Monthly Base or Minimum Thruput</Data></Cell>
      
<Cell ss:StyleID="s1"><Data ss:Type="String">Full Record Id</Data></Cell>

<Cell ss:StyleID="s1"><Data ss:Type="String">Link to Salesforce</Data></Cell>
     
  </Row>

  <apex:repeat value="{!AgreementList}" var="account">

  <Row>

<Cell><Data ss:Type="String">{!account.Region__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Terminal_Location__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Name}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Legacy_Agreement_Number__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Apttus__Status_Category__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Apttus__Status__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Apttus__Agreement_Number__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Agreement_Type__c}</Data></Cell> 
      
<Cell><Data ss:Type="String">{!account.Apttus__Contract_Start_Date__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Apttus__Contract_End_Date__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Days_Remaining_on_Contract__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Apttus__Renewal_Notice_Days__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Apttus__Renewal_Notice_Date__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Apttus__Auto_Renew_Term_Months__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Description_of_Services__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Products__c}</Data></Cell>       

<Cell><Data ss:Type="String">{!account.Tank_s_if_segregated_storage__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Tank_Capacity__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Combined_Space__c}</Data></Cell>

<Cell><Data ss:Type="String">{!account.Monthly_Base_or_Minimum_Thruput_Revenue__c}</Data></Cell>
      
<Cell><Data ss:Type="String">{!account.Full_Record_Id__c}</Data></Cell>    
      
</Row>

</apex:repeat>

</Table>

</Worksheet>        

</Workbook>    

</apex:page>

Apex Class

public class ExportToExcelMultipleSheets {

public List<Apttus__APTS_Agreement__c> AgreementList{get;set;}

public String xlsHeader{
    
    
    get{

        String strHeader;

        strHeader += '<?xml version="1.0"?>';

        strHeader += '<?mso-application progid=”Excel.Sheet”?>';

        return strHeader;
        
           }

}

public ExportToExcelMultipleSheets(){

    AgreementList = [select Name,Region__c,Terminal_Location__c,Legacy_Agreement_Number__c,Apttus__Status_Category__c,Apttus__Status__c,Apttus__Agreement_Number__c,Agreement_Type__c,Apttus__Contract_Start_Date__c,Apttus__Contract_End_Date__c,Days_Remaining_on_Contract__c,Apttus__Renewal_Notice_Days__c,Apttus__Renewal_Notice_Date__c,Apttus__Auto_Renew_Term_Months__c,Description_of_Services__c,Products__c,Tank_s_if_segregated_storage__c,Tank_Capacity__c,Combined_Space__c,Monthly_Base_or_Minimum_Thruput_Revenue__c,Full_Record_Id__c,Id from Apttus__APTS_Agreement__c LIMIT 500];
  

}
}

May 5, 2020
·
Answer
·
Like
0
·
Follow
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

Visual Force Code and Controller for Excel export with multiple workbooks.

You need to sign in to do that.