[SalesForce] Export to excel file problem with displaying information

Following is my page

<apex:page controller="contactquery" contentType="application/vnd.ms-excel#SalesForceExport.xls" cache="true">
<apex:pageBlock title="Export Results" >
    <apex:pageBlockTable value="{!cs}" var="contact">
        <apex:column value="{!contact.ID}"/>
        <apex:column value="{!contact.Name}"/>
        <apex:column value="{!contact.phone}"/>   
    </apex:pageBlockTable>    
    <apex:pageBlockTable value="{!acc}" var="account">                    
        <apex:column value="{!account.id}"/>              
        <apex:column value="{!account.name}"/>            
    </apex:pageBlockTable>
</apex:pageBlock>
</apex:page>

And following is my controller

public class contactquery{
public List<Contact> cs{get; set;}
public List<Account> acc{get; set;}    
public contactquery()
{
cs = new List<Contact>();
acc = new List<Account>();     
   for (Account a : [Select id, Name,(Select id, Name from Contacts) from Account])   
   {
     acc.add(a);
   } 
 }
 }

I get an excel sheet for accounts only.

PROBLEM: Unable to display contacts related to every account in a separate column. How can I achieve this? Thanks in advance.

UPDATE: Following is the required sample excel(without borders). Sample screenshot

Best Answer

Try this,

<apex:dataTable value="{!acc}" var="a">
        <apex:column value="{!a.ID}"/>
        <apex:column value="{!a.Name}"/>
        <apex:column>
         <apex:dataTable value="{!a.contacts}" var="c">
            <apex:column value="{!c.id}"/>                          
            <apex:column value="{!c.name}"/>              
            <apex:column value="{!c.phone}"/> 
         </apex:dataTable>
        </apex:column>
</apex:dataTable>

Related Solutions

[SalesForce] Export Visualforce page to Excel with rich text

Funny you should ask this - I just submitted a blog yesterday that deals with this. Unfortunately, the blog has not been approved for publishing yet. The short version of the blog is

Format a spreadsheet in Excel (frozen rows, named ranges, styles etc).
Save as an Excel XML 2004 document.
Open in a text editor and add your visualforce bindings.
Place the raw xml inside a newly created visualforce page that looks like this:

<apex:page id="pg" standardStylesheets="false" controller="ExcelExportController" 
contenttype="application/vnd.ms-excel#TestExport_{!TODAY()}.xls">
    {!xmlheader}
    <!-- your excel output goes here -->
    <apex:outputText value="{!endfile}" escape="false"/>
</apex:page>

Your controller will look something like this:

public with sharing class ExcelExportController {

    public Opportunity[] opportunities {get;set;}
    public Integer oppSize {get;set;}

    //xls variables:
    public String xmlheader {get;set;}
    public String endfile{get;set;}

    public ExcelExportController() {
        xmlheader ='<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
        endfile = '</Workbook>';
        opportunities = [SELECT Id, Name, StageName, Amount FROM Opportunity LIMIT 10];
        oppSize = opportunities.size();
    }
}

The reason you should do this in Excel is that the styles syntax that Excel generates is, to put it bluntly, horrible. Here is the sort of thing you get:

<Style ss:ID="s67">
  <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
  </Borders>
  <Font ss:Size="14"/>
  <Interior ss:Color="#FFF58C" ss:Pattern="Solid"/>
 </Style>
 <Style ss:ID="s68">
   <Alignment ss:Vertical="Bottom"/>
   <Font ss:Size="12" ss:Bold="1"/>
   <Interior ss:Color="#FFCC99" ss:Pattern="Solid"/>
 </Style>
 <Style ss:ID="s70">
   <Borders>
     <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"/>
     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="14"/>
   <Interior ss:Color="#FFF58C" ss:Pattern="Solid"/>
   <NumberFormat ss:Format="Percent"/>
  </Style>

...and on and on. No style names. repetition. Yuk.

Any formats you save in the spreadsheet will be maintained on the next export. Obviously you are going to have to add repeats and other apex data.

For my working code page, take a look here

[SalesForce] Export to Excel with Command Button

The options I can think of offhand:

Conga Composer offers the ability to merge salesforce data into templates, which can be excel files. You can read more on their website.
Linking to an existing report using the record id as a filter (Related Question describing how to link to a report export)
Building a .csv file yourself (Related Question creating a .csv blob)

Related Topic