0

I am trying to format excel file that is exported for VisualForce page. I am able to export the data in a table with cell width that I need. For printing, I want to set the page orientation:Landscape and width of the page to fit one page. I am unable to do that. I have two VF pages, both are for the same table but different approach. Any solution in either of the approaches will be helpful.

I have tried: https://www.appshark.com/how-to-export-data-in-excel-using-visualforce/

https://www.soliantconsulting.com/blog/stylish-spreadsheets-from-a-visualforce-page

http://m8y.org/Microsoft_Office_2003_XML_Reference_Schemas/Help/html/spreadsheetml_HV01151864.htm

Is there a way to dynamically fill excel sheets from apex?

https://developer.salesforce.com/forums/?id=906F000000092vQIAQ

How to generate multiple excel files on click of a button in Vf page

VF 1

<apex:page controller="accMgrCtrl" id="pg" standardStylesheets="false" contentType="application/vnd.ms-excel#DM-Reached-Last90Days-{!TODAY()}.xls" cache="true" language="en-US">

   <head>
    <style>
        @page {
            size:landscape;
        }
    </style>
</head>  
    <table style="border:1px solid; font-size:16px;">

        <tr style="text-align:center; border:1px solid; ">
       <th>Name</th>
       <th>Next Follow-Up Date</th>
       <th>Next Follow-Up Action</th>
        <th>Primary POC</th>
       <th>Last DM Reached</th>
     </tr> 

             <apex:repeat value="{!accList}" var="a">
      <tr style="text-align:center; border:1px solid; width:auto; word-wrap: break-word;">
      <td><apex:outputLink value="/{!a.id}">{!a.Name}</apex:outputLink></td>
          <td data-cell-type="date"><apex:outputField value="{!a.NEXT_FOLLOW_UP_DATE__c}"/></td>
      <td style=" word-wrap: break-word; "><apex:outputField value="{!a.Next_Action_Follow_Up__c}"/></td>
      <td><apex:outputField value="{!a.Primary_POC__c}"/></td>
        <td data-cell-type="date">  <apex:repeat value="{!a.Prospecting_Activities__r}" var="t">
           <apex:outputField value="{!t.Activity_Date__c }"/>
          </apex:repeat>
          </td>
      </tr>
    </apex:repeat> 

    </table>

</apex:page>

VF 2

<apex:page controller="accMgrCtrl" contentType="application/vnd.ms-excel#DM-Reached-Last90Days-{!TODAY()}.xls" standardStylesheets="false" cache="true" language="en-US">

    <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="Accounts">
             <Table x:FullColumns="5" x:FullRows="1">
                 <Column ss:Width="170"/>
                 <Column ss:Width="280"/>
                 <Column ss:Width="330"/>
                 <Column ss:Width="330"/>
                 <Column ss:Width="330"/>
                 <Row>
                 <Cell ss:StyleID="s1"><Data ss:Type="String" >Account Name</Data></Cell>
                 <Cell ss:StyleID="s1"><Data ss:Type="String" >Next Follow-Up Date</Data></Cell>
                 <Cell ss:StyleID="s1"><Data ss:Type="String" >Next Follow-Up Action</Data></Cell>
                 <Cell ss:StyleID="s1"><Data ss:Type="String" >Primary POC</Data></Cell>
                 <Cell ss:StyleID="s1"><Data ss:Type="String" >Last DM Reached</Data></Cell>
                 </Row>
                 <apex:repeat value="{!accList}" var="a">
                 <Row>
                 <Cell><Data ss:Type="String">{!a.name}</Data></Cell>
                 <Cell><Data ss:Type="Date">{!a.NEXT_FOLLOW_UP_DATE__c}</Data></Cell>
                 <Cell><Data ss:Type="String">{!a.Next_Action_Follow_Up__c}</Data></Cell>
                 <Cell><Data ss:Type="String">{!a.Primary_POC__c}</Data></Cell>
                   <apex:repeat value="{!a.Prospecting_Activities__r}" var="t">
                 <Cell><Data ss:Type="Date">{!t.Activity_Date__c}</Data></Cell>
                     </apex:repeat>  
                 </Row>
                 </apex:repeat>
             </Table>
         </Worksheet>
    </Workbook>    
</apex:page>

Controller

public with sharing class accMgrCtrl {

    public String xmlheader {get;set;}
    public String endfile{get;set;}
    public List<Account> accList {get; set;}
    public List<Account> thirtyList {get; set;}
     public Integer oppSize {get;set;}

    private String sortDirection = 'ASC';
   private String sortExp = 'name';

    public String xlsHeader 
    {
        get {
            String strHeader = '';
            strHeader += '<?xml version="1.0"?>';
            strHeader += '<?mso-application progid="Excel.Sheet"?>';
            return strHeader;
        }
    }

    public accMgrCtrl(){

        lastmonth();
        lastThirty();
        oppSize = accList.size();
    }
2
  • 1
    I assume you have tried (not with VF) to create an Excel .xml file using the Excel UI that is landscape and then inspected the resulting xml? Whatever that looks like would be the key to figuring out how to inject those bits using VF
    – cropredy
    Apr 16 '19 at 18:52
  • Yes, VF 2 is my attempt using xml to create .xls file. VF is used to enter data in that file. With VF 1, it create the file and i can do certain type of formatting e.g. creating grid lines, adding accent to the background etc. I have not been able to do that on the page level e.g. changing the orientation, printing specs etc. Apr 17 '19 at 13:27

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.