How to Render data into MS-Execl using Visualforce?
Here i am sending particular account related contact detail to MS-Execl, for this you need mention content-type attribute in <apex:page> tag.
Here is the Example.
<apex:page standardController="Account" sidebar="false" contentType="application/vnd.ms-excel#sfdcsrini_Excel.xls" cache="true">
<apex:pageBlock title="Hello {!$User.FirstName}!">
You are viewing the {!account.name} account.
</apex:pageBlock>
<apex:pageBlock title="Contacts">
<apex:pageBlockTable value="{!account.Contacts}" var="contact">
<apex:column value="{!contact.Name}"/>
<apex:column value="{!contact.Email}"/>
<apex:column value="{!contact.Phone}"/>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:page>
in content-type attribute i have mentioned the format as MS-Excel and name of the file as "sfdcsrini_Excel". You have to set cache="true" for IE support.
Finally you need to pass the particular account id in to url
like
https://c.ap1.visual.force.com/apex/msexcel?id=0019000000RMAaF
------------------------------------------------------------
Example:
Export Page:
Here i am sending particular account related contact detail to MS-Execl, for this you need mention content-type attribute in <apex:page> tag.
Here is the Example.
<apex:page standardController="Account" sidebar="false" contentType="application/vnd.ms-excel#sfdcsrini_Excel.xls" cache="true">
<apex:pageBlock title="Hello {!$User.FirstName}!">
You are viewing the {!account.name} account.
</apex:pageBlock>
<apex:pageBlock title="Contacts">
<apex:pageBlockTable value="{!account.Contacts}" var="contact">
<apex:column value="{!contact.Name}"/>
<apex:column value="{!contact.Email}"/>
<apex:column value="{!contact.Phone}"/>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:page>
in content-type attribute i have mentioned the format as MS-Excel and name of the file as "sfdcsrini_Excel". You have to set cache="true" for IE support.
Finally you need to pass the particular account id in to url
like
https://c.ap1.visual.force.com/apex/msexcel?id=0019000000RMAaF
------------------------------------------------------------
Example:
<apex:page controller="AssetsTreeController1" docType="html-5.0" sidebar="false" readonly="true">
<apex:form id="frm">
<apex:actionstatus id="Statusimage">
<apex:facet name="start">
<div class="waitingSearchDiv" id="el_loading" style="background-color: #fbfbfb; height:100%;opacity:0.65;width:100%;">
<div class="waitingHolder" style="top: 100px; width: 91px;">
<img class="waitingImage" src="/img/loading.gif" title="Please Wait..." />
<span class="waitingDescription">Loading...</span>
</div>
</div>
</apex:facet>
</apex:actionstatus>
<apex:outputPanel >
<b> Account: </b>
<apex:inputfield value="{!Gl.Accountid}" id="prdname" ></apex:inputfield>
<apex:commandbutton value="Search" action="{!search}" status="Statusimage" rerender="pg" />
<apex:commandButton value="<<" rerender="frm,pg" action="{!beginning}" disabled="{!prev}" status="Statusimage"/>
<apex:commandButton value="<" rerender="frm,pg" action="{!previous}" disabled="{!prev}" status="Statusimage"/>
<apex:commandButton value=">" rerender="frm,pg" action="{!next}" disabled="{!nxt}" status="Statusimage"/>
<apex:commandButton value=">>" rerender="frm,pg" action="{!end}" disabled="{!nxt}" status="Statusimage"/>
<apex:commandButton value="Export to Excel" action="{!exportAll}"/>
</apex:outputPanel>
<apex:pageBlock id="pg">
<apex:pageBlockTable value="{!assetLst}" var="a">
<apex:column headerValue="System type">
<apex:outputField value="{!a.System_Type__c}"/>
</apex:column>
<apex:column headerValue="System">
<apex:outputField value="{!a.System_Number__c}"/>
</apex:column>
<apex:column headerValue="Product">
<apex:outputField value="{!a.product2.name}"/>
</apex:column>
<apex:column headerValue="Serial Number">
<apex:outputField value="{!a.serialnumber}"/>
</apex:column>
<apex:column headerValue="Description">
<apex:outputField value="{!a.product2.description}"/>
</apex:column>
<apex:column headerValue="Account Name">
<apex:outputField value="{!a.Account.name}"/>
</apex:column>
<apex:column headerValue="End Customer">
<apex:outputField value="{!a.End_Customer__r.name}"/>
</apex:column>
<apex:column headerValue="Installed At Location">
<apex:outputField value="{!a.Installed_At_Description__c}"/>
</apex:column>
<apex:column headerValue="Purchase Order">
<apex:outputField value="{!a.Purchase_Order__c}"/>
</apex:column>
<apex:column headerValue="Sales Order">
<apex:outputField value="{!a.Sales_Order__c}"/>
</apex:column>
<apex:column headerValue="Ship Date">
<apex:outputField value="{!a.PurchaseDate}"/>
</apex:column>
<apex:column headerValue="Contract#">
<apex:outputField value="{!a.Contract_Number__c}"/>
</apex:column>
<apex:column headerValue="Contract Business Partner">
<apex:outputField value="{!a.Contract_Partner__c}"/>
</apex:column>
<!---- <apex:outputpanel rendered="{!a.SCID__c != null}">{!a.Contract_Status__c}</apex:outputpanel><apex:outputpanel rendered="{!a.SCID__c == null}">Not Entitled</apex:outputpanel>---->
<apex:column headerValue="Contract Status">
<apex:outputpanel rendered="{!a.SCID__c != null}">{!a.Contract_Status__c}</apex:outputpanel><apex:outputpanel rendered="{!a.SCID__c == null}">Not Entitled</apex:outputpanel>
</apex:column>
<!---- <apex:column headerValue="Contract Status">
<apex:outputField value="{!a.Contract_Status__c}"/>
</apex:column>---->
<apex:column headerValue="Contract StrtDate">
<apex:outputField value="{!a.Line_StartDate__c}"/>
</apex:column>
<apex:column headerValue="Contract EndDate">
<apex:outputField value="{!a.Line_EndDate__c}"/>
</apex:column>
<apex:column headerValue="Asset Status">
<apex:outputField value="{!a.Status}"/>
</apex:column>
<apex:column headerValue="Sonus Ref #">
<apex:outputField value="{!a.Instance_ID__c}"/>
</apex:column>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>
</apex:page>
Controller:
public class AssetsTreeController1{
public list<asset> assetLst {get;set;}
//public Generic_Lookup__c Gl {get;set;}
public asset Gl {get;set;}
public set<string> SystemIds{get;set;}
public string systid{get;set;}
public set<id> QassetsIds {get;set;}
public string AccountID ;
public string Asset_sort_field{get;set;}
public string Asset_sort_Order{get;set;}
private integer index = 0;
private integer blockSize = 50;
public integer totalRecs {get;set;}
public String alldata ;
public boolean nxt {get;set;}
public boolean prev {get;set;}
public string maintennce{get;set;}
public AssetsTreeController1(){
//Gl= new Generic_Lookup__c();
// totalRecs =[select count() from asset ];
Gl= new asset ();
Asset_sort_field = 'ID';
Asset_sort_Order = 'Desc';
QassetsIds = new set<id>();
if(SystemIds== null || SystemIds.size() <= 0)
{
SystemIds = new set<string>();
if(systid != null && systid != '')
{
SystemIds.add(systid);
}
}
systid = ApexPages.currentPage().getParameters().get('systemid');
alldata = ApexPages.currentPage().getParameters().get('alldata');
search() ;
}
public void search(){
system.debug('--------------->');
AccountID= Gl.accountid;
assetLst = new list<asset>();
// if(SystemIds.size() > 0 || QassetsIds.size()>0){
system.debug('--------------->');
string query = 'SELECT id , System_Type__c ,ContractLineItem__r.servicecontract.Partner_Account_Name__c,Contract_Partner__c,Contract_Business_partner__c, SCID__c,ContractLineItem__r.ServiceContract.Name ,Contract_number__c ,Contract_Status__c,Line_EndDate__c,Line_StartDate__c, Installed_At_Description__c, name,Sales_Order_Type__c , InstallDate ,account.name,Quantity,Status,Instance_ID__c,End_Customer__r.name,purchasedate, Description,SerialNumber,System_Number__c,product2.description,product2.name,Purchase_Order__c , Sales_Order__c ,parent_asset__c,Location_Latitude__c,Location_Longitude__c ';
query = query+ ' FROM Asset where Model__c = null and Parent_Asset__c = null and Instance_ID__c != null and Product2id != null and Sales_Order_Type__c !=\''+ 'SERVICE RMA' +'\'';
if(SystemIds.size() > 0 && QassetsIds.size()>0 )
query = query + ' and ( System_Number__c in: SystemIds OR id in: QassetsIds )';
if(SystemIds.size() > 0 && QassetsIds.size()<=0 )
query = query + ' and System_Number__c in: SystemIds ';
if(SystemIds.size() <= 0 && QassetsIds.size() > 0 )
query = query + ' and id in: QassetsIds ';
if(maintennce != '--None--' )
{
system.debug('!!!!!!!Maint '+maintennce );
if(maintennce =='Yes' )
{
query = query +' and Contract_Status__c =\''+'Active'+'\'';
}
if(maintennce =='No' )
{
query = query +' and Contract_Status__c !=\''+'Active'+'\'';
}
}
query = query + ' AND ( End_Customer__c =: AccountID OR accountid =: AccountID OR Contract_Business_partner__c =:AccountID )';
query = query + ' Order by ';
query = query + Asset_sort_field +' '+Asset_sort_Order ;
List<asset> Totalrecslst = Database.query(query +' limit 2000');
if(Totalrecslst.size()> 50 && alldata == null && alldata != '1' )
{
query = query + ' LIMIT ' + blockSize + ' OFFSET ' + index;
if(AccountID!= null)
assetLst = database.query(query);
}
else
{
assetLst = Totalrecslst ;
}
totalRecs = Totalrecslst.size();
if((index + blockSize) >= totalRecs) nxt = true;
else nxt = false;
if(index == 0) prev = true;
else prev = false;
// }
}
public Pagereference exportAll(){
Pagereference P = new pagereference('/apex/AssetsTreeExportpage?alldata=1');
alldata = '1';
search();
alldata = null;
P.setRedirect(false);
return P;
}
public void beginning()
{
index = 0;
Search();
}
public void previous()
{
index = index - blockSize;
if (index <= 0 ) index = 0;
Search();
}
public void next()
{
index = index + blockSize;
Search();
}
public void end()
{
index = totalrecs - math.mod(totalRecs,blockSize);
if (index > 2000) index = 1999;
Search();
}
}
----------------------------------Export Page:
<apex:page controller="AssetsTreeController1" contentType="application/vnd.ms-excel#AssetsList.xls" sidebar="false" showHeader="false" readOnly="true" cache="true">
<apex:form >
<apex:pageBlock >
<div class="bPageBlock brandSecondaryBrd apexDefaultPageBlock secondaryPalette">
<div class="pbBody">
<apex:outputPanel id="OP1" >
<apex:pageMessages id="msg" escape="false"></apex:pageMessages>
<table class="list" id="Custtable" >
<b> <tr >
<th>System Type</th>
<th>System</th>
<th>Product</th>
<th>Serial Number</th>
<th>Description</th>
<th>Account Name</th>
<th> End Customer</th>
<th> Installed At Location</th>
<th>Purchase Order</th>
<th>Sales Order</th>
<th>Ship Date</th>
<th>Contract#</th>
<th >Contract Business Partner</th>
<th>Contract Status</th>
<th>Contract StrtDate</th>
<th>Contract EndDate</th>
<th>Asset Status</th>
<th>Sonus Ref #</th>
</tr></b>
<apex:repeat value="{!assetLst}" var="L1">
<tr id="L1{!L1.id}" class="dataRow" >
<td>{!L1.System_Type__c}</td>
<td> {!L1.System_Number__c} </td>
<td>{!L1.product2.name}</td>
<td>{!L1.serialnumber}</td>
<td>{!L1.product2.description}</td>
<td>{!L1.Account.name}</td>
<td>{!L1.End_Customer__r.name}</td>
<td>{!L1.Installed_At_Description__c}</td>
<td>{!L1.Purchase_Order__c }</td>
<td>{!L1.Sales_Order__c}</td>
<td><apex:outputText value="{0,date,dd'-'MMM'-'yyyy}">
<apex:param value="{!L1.PurchaseDate}" />
</apex:outputText></td>
<td>{!L1.Contract_Number__c}</td>
<td>{!L1.Contract_Partner__c}</td>
<td>
{!L1.Contract_Status__c}
</td>
<td>
<apex:outputText value="{0,date,dd'-'MMM'-'yyyy}">
<apex:param value="{!L1.Line_StartDate__c}" />
</apex:outputText>
</td>
<td>
<apex:outputText value="{0,date,dd'-'MMM'-'yyyy}">
<apex:param value="{!L1.Line_EndDate__c}" />
</apex:outputText>
</td>
<td>{!L1.Status}</td>
<td>{!L1.Instance_ID__c}</td>
</tr>
</apex:repeat>
</table>
</apex:outputPanel>
</div>
</div>
</apex:pageBlock>
</apex:form>
</apex:page>
Controller same as used the above
No comments:
Post a Comment