Feb 04

Export to Excel with multiple worksheets in visualforce

Export to Excel with multiple worksheets in visualforce

We need to use XML tags to get the excel with multiple worksheets. We can use visualforce tags also with XML tags. We can use Styles like color , size , height for formatting the cells of excel sheet. For XML spreadsheet reference, go to
XML Spreadsheet Reference

Click for Demo

Below is the Visualforce page code that will list all Accounts and Contacts. There is a button that will export all these accounts and contacts to excel sheet with two worksheets.

<apex:page controller="ExportToExcelMultipleSheets">
<apex:form >
<apex:pageBlock title="Accounts and Contacts">

<apex:pageBlockButtons >
<apex:commandbutton value="Export All Accounts and Contacts" action="{!exportAll}"/>
</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>

Below is the Visualforce page code that will generate excel file.

<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">
 <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>

Below controller is used by both the visualforce pages: 


public with sharing class ExportToExcelMultipleSheets {
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 ExportToExcelMultipleSheets(){
    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');


}

}

Permanent link to this article: http://www.sfdcpoint.com/salesforce/export-excel-multiple-worksheets-visualforce/

  • MyCloud Project

    I am getting both accounts and contacts in one sheet only. i copied the same code. Please help me.!

    • Hello

      Can you please try running ‘Click for demo’ link on this page and check if this code is generating Account and Contacts in one sheet or multiple sheets?

      • MyCloud Project

        Thanks for ur reply,!

        I’ve checked ‘Click for demo’ link and the link you posted above both are working fine. But i dont know why it is not working for me. Here i am posting the link please check it once.

        http://myprivate-developer-edition.ap1.force.com/

        • Thần Phong

          strHeader += ”;
          strHeader += ”;

          Remove comment in above to the following and it’s work fine.

          strHeader += ”;
          strHeader += ”;

      • MyCloud Project

        Please check the below code :

        Visual force page to display Accounts and Contacts :

        All Accounts

        All Contacts

        Visual force page to download Accounts and Contacts :

        Account Name

        {!account.name}

        Contact Name

        Email

        Account Name

        {!contact.name}

        {!contact.email}

        {!contact.account.name}

        Apex Class :

        public with sharing class ExportToExcelMultipleSheets {

        public List accountList{get;set;}

        public List contactList{get;set;}

        public String xlsHeader {

        get {

        String strHeader = ”;

        strHeader += ‘‘;

        strHeader += ‘‘;

        return strHeader;

        }

        }

        public ExportToExcelMultipleSheets() {

        accountList = [select Name,Id from Account LIMIT 10];

        contactList = [SELECT id,Name, LastName, Account.Name, Email FROM Contact WHERE Email != ” AND Account.Name != ” LIMIT 5];

        }

        public Pagereference exportAll(){

        return new Pagereference(‘/apex/exportAll’);

        }

        }

        Thanks!

    • Nitish Singhal

      Hello,

      Can you please try copy-paste our given code as it is because there seems some difference in our code and yours.

      Below is your chunk of code where it seems the difference:

      I guess , xmlns:v=”urn:schemas-microsoft-com:vml” and part is causing the issue, as you have also used the same reference that is in other parts of the code. So please try copy paste the same provided code and let us know, if it still doesn’t work for you.

      Thanks

      • MyCloud Project

        Hi, Now i just copied the above code provided in this site and pasted as it is. Now i am getting like this. http://myprivate-developer-edition.ap1.force.com/ .

        Thanks !

      • MyCloud Project

        Now i got it ! There was a small mistake i didn’t notice. The xml code is in comments.

        strHeader += ‘‘;
        strHeader += ‘‘;

        I removed the code in comments.

        ”;

        ”;

        Thanks !

        • hafeez

          Can u please say what exactly u missed out !!

  • కృష్ణ ప్రసాద్

    can u post test class ..i cant cover.

    .
    public String xlsHeader {
    get {
    String strHeader = ”;
    strHeader += ‘‘;
    strHeader += ‘‘;
    return strHeader;
    }
    }

    • Gyanender Singh Chauhan

      public static testmethod void ExportToExcelMultipleSheets(){

      ExportToExcelMultipleSheets EE = new ExportToExcelMultipleSheets();

      EE.exportAll();

      system.debug(‘*****EE.xlsHeader **********’+EE.xlsHeader);

      }

  • hafeez

    Hello
    I am using the same code ! But it is not coming in 2 excel sheets .

  • Deepak

    Hi Ankush,

    I am trying to create a excel file using your code above. When i check the demo link it is working fine and excel is coming in two worksheets, however when i am generating the excel by the code it is giving a excel with a single worksheet and two rows with all Accounts and Contacts values.

    I think there is something different in the exportAll page just the way it is in the controller where you have just written List not List or List.

    Can you or any one who has figure this out please help me and other people here.

    Thanks!

  • Kumaresan M

    Can anyone please post the right code to this functionality nothing is working for me. Please post the actual content present in that “Demo Link” shared. The code pasted here here is not working at all.

    Thanks,
    Kumaresan M

  • Maryem Bourhi

    Hello,

    i’ve tried the same code but its not working for me, i’ve tried to compare text content of the demo file and my file i found this part is present in mine and absent in the demo file :

    SfdcApp.Visualforce.viewstate.ViewstateSender.sendViewstate(‘https://eu5.salesforce.com’, ‘VF004_ExportCSVFile’);

    Does anyone have the same issue ? how can i remove that part before the file generation ?

    Thank you all

    • Jacob Gmerek

      I am having that same issue. I can not figure out how it is working in the Demo.

  • Jyothylakshmy P U

    I got error when i tryed to open the downloaded file of open the file generated as resul : “General Error.
    General input/output error.” for libre office . need any other configuration ?

  • Vaibhav

    Hey Can you tell me if we can freeze the headers as well

  • Vaibhav

    That is really important for me

  • Pingback: Salesforce Export to Excel Programatically | Pearltrees()

  • Pingback: Salesforce Export to Excel Programatically | Pearltrees()