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/

23 comments

2 pings

Skip to comment form

    • MyCloud Project on April 3, 2014 at 5:37 pm
    • Reply

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

    1. 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 on April 4, 2014 at 1:02 pm
        • Reply

        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 on February 9, 2015 at 3:47 pm
          • Reply

          strHeader += ”;
          strHeader += ”;

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

          strHeader += ”;
          strHeader += ”;

        • MyCloud Project on April 4, 2014 at 1:10 pm
        • Reply

        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!

        • MyCloud Project on April 4, 2014 at 1:18 pm
        • Reply

        Both the link are working fine, but mine is not working. Please check it once http://myprivate-developer-edition.ap1.force.com/

        Thanks!

      • Nitish Singhal on April 5, 2014 at 12:50 am
      • Reply

      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 on April 7, 2014 at 10:15 am
        • Reply

        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 on April 8, 2014 at 10:54 am
        • Reply

        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 on December 23, 2014 at 1:59 am
          • Reply

          Can u please say what exactly u missed out !!

    • కృష్ణ ప్రసాద్ on July 8, 2014 at 3:47 pm
    • Reply

    can u post test class ..i cant cover.

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

      • Gyanender Singh Chauhan on May 27, 2015 at 5:32 pm
      • Reply

      public static testmethod void ExportToExcelMultipleSheets(){

      ExportToExcelMultipleSheets EE = new ExportToExcelMultipleSheets();

      EE.exportAll();

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

      }

    • hafeez on December 23, 2014 at 1:55 am
    • Reply

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

    • Deepak on January 14, 2015 at 2:19 pm
    • Reply

    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 on March 2, 2015 at 2:37 pm
    • Reply

    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 on April 14, 2015 at 2:03 pm
    • Reply

    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 on May 8, 2015 at 10:36 pm
      • Reply

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

    • Jyothylakshmy P U on April 21, 2015 at 3:01 pm
    • Reply

    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 on July 16, 2015 at 2:18 pm
    • Reply

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

    1. Approved

    • Vaibhav on July 16, 2015 at 2:19 pm
    • Reply

    That is really important for me

    1. Approved

  1. […] to Excel with multiple worksheets in visualforce – Salesforce Tips & Tricks. Export to Excel with multiple worksheets in visualforce – Salesforce Tips & Tricks. Visualforce – How to ensure that all of the cell content shows in an Excel contenttype page. I am […]

  2. […] Export to Excel with multiple worksheets in visualforce – Salesforce Tips & Tricks. Export to Excel with multiple worksheets in visualforce – Salesforce Tips & Tricks. Visualforce – How to ensure that all of the cell content shows in an Excel contenttype page. I am exporting a page as Excel page as described here (using contenttype). I am unsure how to ensure that the contents of the cells are showing. After opening the file, I could change it manually, by setting the cell height to Auto-fit and setting the cell contents to wrap, but I would like to do this automatically. I am guessing there must be a property that can be set in style which is explicitly for Excel which will define these two properties. […]

Leave a Reply

Your email address will not be published.