Saturday, July 14, 2012

Export to excel in salesforce using HTML Table & jQuery


In the last post I mentioned export to excel in salesforce using another plugin called downlaodify. Many people did not like it using a flash plugin for export. So decided to research export in another way.
In my research I found excel and open office recognizes HTML Table and displays in excel format. My colleague Murali has an interesting idea the we can send variable in salesforce from one page to another page. By using the same controller for two pages we easily access variable in another page.Export can be acheived using two visualforce pages and same controllerfor both of them, by sending a hidden variable from one page to another page in the html table format.
Apex Page refference methods from apex can used to redirect from one page to another page.
Consider the governer limits while using this code. This do not work when you cross beyond the view state.
I tested for 1000- 5000 records with 12- 15 columns it works fine for me(without text area).

You can see live demo  @  http://ugeshcrm-developer-edition.ap1.force.com/ExportToExcelDisplay

Apex Class:
public with sharing class ExportToExcel{
 
     public String exportDataString {get; set;}  

     public ExportToExcel() {}

   //Apex Page refference methods from apex can used to redirect from one page to another page.
     public ApexPages.PageReference goToExportPage() {
        return Page.ExportToExcel;
     }  
}

Visualforce page: Display
<apex:page controller="ExportToExcel" sidebar="false" title="Export">
     <!-- Static Resource Includes: Jquery and css files  -->
    <apex:includeScript value="{!JSENCODE(URLFOR($Resource.JQuery,'js/jquery-1.5.2.min.js'))}"/>
    <apex:includeScript value="{!JSENCODE(URLFOR($Resource.JQuery,'js/jquery-ui-1.8.13.custom.min.js'))}"/>
    <apex:stylesheet value="{!JSENCODE(URLFOR($Resource.JQuery, 'css/redmond/jquery-ui-1.8.11.custom.css'))}"/>

    <style>
        #newspaper-b {
            font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
            font-size: 12px;
            margin: 45px;
            width: 480px;
            text-align: left;
            border-collapse: collapse;
            border: 1px solid #69c;
        }
        #newspaper-b th  {
            padding: 15px 10px 10px 10px;
            font-weight: normal;
            font-size: 14px;
            color: #039;
        }
        #newspaper-b tbody {
            background: #e8edff;
        }
        #newspaper-b td {
            padding: 10px;
            color: #669;
            border-top: 1px dashed #fff;
        }
        #newspaper-b tbody tr:hover td {
            color: #339;
            background: #d0dafd;
        }

    </style>
     <apex:sectionHeader title="Export Table" />

     <script type='text/javascript' language='javascript'>
        var j$ = jQuery.noConflict();
        function exportTableData(){
            j$(".exampleDataToExport").val(j$("#containerTable").html());
            goToExportPage();
        }
     </script>
    <apex:form >
        <a style="text-decoration: underline;cursor: pointer;margin-left:480px;font-weight:bold;font-size: 15px;" class="dummyCustomExport"  onclick="exportTableData()" title="export"><b>Export</b></a>

        <div id="containerTable">
            <table id="newspaper-b" class="dummyExportTableClass" summary="2007 Major IT Companies' Profit">
                <thead>
                    <tr>
                      <th scope="col">Company</th>
                      <th scope="col">Q1</th>
                      <th scope="col">Q2</th>
                      <th scope="col">Q3</th>
                      <th scope="col">Q4</th>
                    </tr>
                </thead>
                <tbody>
                  <tr>
                      <td>Microsoft</td>
                      <td>20.3</td>
                      <td>30.5</td>
                      <td>23.5</td>
                      <td>40.3</td>
                  </tr>
                  <tr>
                      <td>Google</td>
                      <td>50.2</td>
                      <td>40.63</td>
                      <td>45.23</td>
                      <td>39.3</td>
                  </tr>
                  <tr>
                      <td>Apple</td>
                      <td>25.4</td>
                      <td>30.2</td>
                      <td>33.3</td>
                      <td>36.7</td>
                  </tr>
                  <tr>
                      <td>IBM</td>
                      <td>20.4</td>
                      <td>15.6</td>
                      <td>22.3</td>
                      <td>29.3</td>
                  </tr>

                </tbody>
            </table>
        </div>
        //action function redirects to another page.
        <apex:actionFunction name="goToExportPage" action="{!goToExportPage}"/>
        <apex:inputTextarea id="ExportDataString" value="{!exportDataString}" styleClass="exampleDataToExport" style="display:none"/>
    </apex:form>
</apex:page>

Visualforce page: Export page
<apex:page controller="ExportToExcel" contentType="application/vnd.ms-excel#export.xls"  title="Export To Excel" cache="true">
  {!(exportDataString)}
</apex:page>

Please feel free to comment if you have any better ideas andto imporove code and any other export methods.

1 comment:

  1. Hi,

    Thanks for sharing this cool way.
    Can you please help in to have Data validation list or drop-down or select option list as well in the exported excel file?
    This is very urgent for me.

    Thanks in advance

    ReplyDelete