Export data in Excel using Visualforce

Requirement: Generate an excel report of salesforce data using visualforce 

Sometimes there may be a requirement to generate a excel report of salesforce data .

To complete this requirement  we must follow the following steps :

1. ) First we create a visualforce page. And put a button named generate excel report on that  visuaforce page.
2. ) Then  we create another visualforce page .This page  is converted to excel  When user press  generate excel report button . When user click on generate report  button. We call second visualforce  page and convert this visualforce page to excel report .

1.  Create  visualforce page with a button : Here we are creating a visualforce page  having generate excel report  button  and agentlistcontroller. When user click generate excel report button. A  excel  report of agents  generate.

<apex:page controller="AgentListcontroller" sidebar="false" id="pid" >
    <script type="text/javascript">
    function callexcel(){
      window.open("/apex/AgentReportToExcelPage", '_blank');
    }
    </script>

    <apex:form>
        <apex:pageBlock> 
            <apex:pageBlockButtons location="bottom" id="pbbid" style="margin-left:170px !important;">
             // onbutton click we are calling javascript callexcel method 
            <apex:commandButton id="excelbutton" value="Generate Excel Spreadsheets" onclick="callexcel();" />
            </apex:pageBlockButtons>
        </apex:pageBlock>
    </apex:form>
</apex:page>

2) Create another visualforce page that is converted into excel report : This visualforce page is open when user click on generate excel report . This page is rendered into excel report . we  take content type of visualforce page as “application/vnd.ms-excel#Yourfilename.xls” .And  shows the data in tabular format . In this visualforce page datalist is the list of data we  need to show in our excel report . so here we are showing datalist data in tabular format using repeat.

<apex:page controller="AgentListcontroller" sidebar="false" action="{!loadDataExcel}" contentType="application/vnd.ms-excel#Agentlist.xls">
   <table style="width:100%;border:1px solid;">
     <tr style="text-align:center; border:1px solid;">
       <th>Name</th>
       <th>phone</th>
       <th>Email</th>
     </tr>
    <apex:repeat value="{!dataList}" var="a">
      <tr style="text-align:center; border:1px solid;">
      <td>{!a.Name}</td>
      <td>{!a.phone}</td>
      <td>{!a.Agency_Email__c}</td>
      </tr>
    </apex:repeat>
    </table>
</apex:page>

3) Controller For fetching data from salesforce object :  Below is the controller for pages.

In this class we  are fetching  account information.  and assigning values to datalist  we use  data list in visualforce page

global with sharing class AgentListcontroller 
{

  public string xmlheader{get;set;}
  public string endfile {get;set;}
  public list<account> dataList{get;set;}

  public void loadDataExcel()
  {
    xmlheader ='<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
    endfile = '</Workbook>';

    list<Account>usr = new list<Account>();
    usr = [SELECT id, name,Agency_Email__c,phone  FROM Account 
        ORDER BY Name ASC];
    datalist= usr;

  }

}

we build a functionality of generating excel report of data . when user click on generate excel report button an excel report of data is generated .

Hits: 3311