1

I have a Vf page which displays list of Accounts and Contacts and a button called "Export All Accounts and Contacts". On click of button, an excel file gets generated with two worksheets(or tabs in excel) one each for Accounts and Contacts. The code is below:

Vf page 1:

    <apex:page controller="ExportToExcelMultipleSheets">
<script>
    var links = [

  'https://sfdcacct-dev-ed--c.ap5.visual.force.com//apex/exportAll',
  'https://s3.amazonaws.com/Minecraft.Download/launcher/Minecraft.jar',
];

function downloadAll(urls) {
    alert('hi');
  var link = document.createElement('a');

  link.setAttribute('download', null);
  link.style.display = 'none';

  document.body.appendChild(link);

  for (var i = 0; i < urls.length; i++) {
    link.setAttribute('href', urls[i]);
    link.click();
  }

  document.body.removeChild(link);
}
</script>
    <apex:form >
        <apex:pageBlock title="Accounts and Contacts">
        <apex:pageBlockButtons >
            <apex:commandbutton value="Export All Accounts and Contacts" onclick="downloadAll(window.links)"/>
        </apex:pageBlockButtons>
            <apex:pageBlockSection columns="2">
                <apex:pageBlockSectionItem >
                    <apex:pageBlockTable title="All Accounts" value="{!accountList}" var="account">
                        <apex:facet name="caption" ><b>All Accounts</b></apex:facet>
                        <apex:column value="{!account.name}"/>
                    </apex:pageBlockTable>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem >
                    <apex:pageBlockTable title="All Contacts" value="{!contactList}" var="contact">
                        <apex:facet name="caption" ><b>All Contacts</b></apex:facet>
                        <apex:column value="{!contact.name}"/>
                        <apex:column value="{!contact.email}"/>
                        <apex:column value="{!contact.account.name}"/>
                    </apex:pageBlockTable>
                </apex:pageBlockSectionItem>
            </apex:pageBlockSection>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Controller :

public with sharing class ExportToExcelMultipleFiles 
{
    public List<Account> accountList{get;set;}
    public List<Contact> contactList{get;set;}
    public String xlsHeader 
    {
        get {
            String strHeader = '';
            strHeader += '<?xml version="1.0"?>';
            strHeader += '<?mso-application progid="Excel.Sheet"?>';
            return strHeader;
        }
    }

    public ExportToExcelMultipleFiles()
    {
        accountList = [select id, name from Account LIMIT 50];
        contactList = [Select id, name, account.name, email from Contact LIMIT 50];

    }

    public Pagereference exportAll()
    {
        return new Pagereference('/apex/exportAll');          
    }
}

Second Vf(Name : exportAll):

<apex:page controller="ExportToExcelMultipleFiles" 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">
         <Styles>
             <Style ss:ID="s1">
             <Alignment/>
             <Borders/>
             <Font ss:Bold="1"/>
             <Interior/>
             <NumberFormat/>
             <Protection/>
             </Style>
         </Styles>
         <Worksheet ss:Name="Accounts">
             <Table x:FullColumns="1" x:FullRows="1">
                 <Column ss:Width="170"/>
                 <Row>
                     <Cell ss:StyleID="s1"><Data ss:Type="String" >Account Name</Data></Cell>
                 </Row>
                 <apex:repeat value="{!accountList}" var="account">
                 <Row>
                     <Cell><Data ss:Type="String">{!account.name}</Data></Cell>
                 </Row>
                 </apex:repeat>
             </Table>
         </Worksheet>
         <Worksheet ss:Name="Contacts">
             <Table x:FullColumns="1" x:FullRows="1">
                 <Column ss:Width="170"/>
                 <Column ss:Width="280"/>
                 <Column ss:Width="330"/>
                 <Row>
                 <Cell ss:StyleID="s1"><Data ss:Type="String" >Contact Name</Data></Cell>
                 <Cell ss:StyleID="s1"><Data ss:Type="String" >Email</Data></Cell>
                 <Cell ss:StyleID="s1"><Data ss:Type="String" >Account Name</Data></Cell>
                 </Row>
                 <apex:repeat value="{!contactList}" var="contact">
                 <Row>
                 <Cell><Data ss:Type="String">{!contact.name}</Data></Cell>
                 <Cell><Data ss:Type="String">{!contact.email}</Data></Cell>
                 <Cell><Data ss:Type="String">{!contact.account.name}</Data></Cell>
                 </Row>
                 </apex:repeat>
             </Table>
         </Worksheet>
    </Workbook>    
</apex:page>

Instead of generating one file containing two sheets(one each for Accounts & Contacts), what needs to be done if we want to generate separate excel file for Accounts & Contacts on click of the button? I tried including the Contacts worksheet inside a new workbook tag in the second Vf but it is not working. Please let me know if it is possible to generate two excels in this case?

Thanks.

1

Normally one click should result in only one download. The easy option would be to create two buttons. However, according to this link you can download multiple files with one click with the help of a bit of javascript:

https://stackoverflow.com/questions/2339440/download-multiple-files-with-a-single-action

var links = [
  'https://s3.amazonaws.com/Minecraft.Download/launcher/Minecraft.exe',
  'https://s3.amazonaws.com/Minecraft.Download/launcher/Minecraft.dmg',
  'https://s3.amazonaws.com/Minecraft.Download/launcher/Minecraft.jar'
];

function downloadAll(urls) {
  var link = document.createElement('a');

  link.setAttribute('download', null);
  link.style.display = 'none';

  document.body.appendChild(link);

  for (var i = 0; i < urls.length; i++) {
    link.setAttribute('href', urls[i]);
    link.click();
  }

  document.body.removeChild(link);
}

<button onclick="downloadAll(window.links)">Test me!</button>

Basically this would mean you create two visualforce pages. One for contacts and one for the accounts. You can then use the javascript to download the two files with only one click. I don't believe there is a way to do this with just visualforce.

6
  • I will try with the code sample you sent, thanks Kasper for the response. – Suha Mar 27 '18 at 16:38
  • You're welcome, i hope it will work for you. – Kasper Mar 27 '18 at 21:38
  • Hi Kasper, I included the code in my first Vf(Updated above), but it is not downloading files but simply refreshes page.. – Suha Mar 29 '18 at 12:54
  • This trick might not work in all browsers? What browser do you use? – Kasper Mar 29 '18 at 15:25
  • I use Chrome browser. But it seems to be not working in IE also. – Suha Mar 30 '18 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.