Apex Code Development You need to sign in to do that
Don't have an account?
Money Care 7 How to search and export that record into excel
Hi Guys
I have one requirment to search and export that record into excel.for example i have one function to search record based on date.After record search on a perticular date i want to one custom button which could export that record into excel...
This page for Export
I have one requirment to search and export that record into excel.for example i have one function to search record based on date.After record search on a perticular date i want to one custom button which could export that record into excel...
This page for Export
<apex:page controller="dateInPBTbaleControllernewOk " 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="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" >Mastr Code</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Master Name</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Credit Balance</Data></Cell>
<Cell ss:StyleID="s1"><Data ss:Type="String" >Debit Balance</Data></Cell>
</Row>
<apex:repeat value="{!listAccount}" var="contact">
<Row>
<Cell><Data ss:Type="String">{!contact.Master_Code__c}</Data></Cell>
<Cell><Data ss:Type="String">{!contact.Master_Name__c}</Data></Cell>
<Cell><Data ss:Type="String">{!contact.Credit_Balance__c}</Data></Cell>
<Cell><Data ss:Type="String">{!contact.Debit_Balance__c}</Data></Cell>
</Row>
</apex:repeat>
</Table>
</Worksheet>
</Workbook>
</apex:page>
This is for search
<apex:page standardController="MasterCopy__c" extensions="dateInPBTbaleControllernewOk" showHeader="false" sidebar="false" >
<apex:pageMessages ></apex:pageMessages>
<style type="text/css" media="print">
@media print
{
#non-printable { display: none; }
#printable {
display: block;
width: 100%;
height: 100%;
}
}
</style>
<style type = "text/css">
.colHeadr {text-align:center;}
.colHeadrRight{
text-align:right;
}
}
</style>
<apex:define name="body">
<div style="width:1500px;margin: 0px auto 10px auto;border: 1px solid black;">
<apex:form id="tableId">
<apex:pageBlock >
<b><apex:outputLabel value="Trial Balance Report On: " style="font-weight: bold;color:light blue;"/></b>
<apex:inputField value="{!account.DateUpdate__c}" style="font-weight: bold;color:black;" required="false"/>
<!--- Trial Balance Report On: <apex:input type="date" value="{!dat}"/>-->
<apex:commandButton value="Display" action="{!displaingTable}" reRender="tableId" style="font-weight: bold;color:black;" oncomplete="window.opener.location.refresh();" />
<apex:commandbutton value="Export" action="{!exportToExcel}"/>
<b><apex:commandLink id="printable" onclick="JavaScript:window.print();" value="Print"/></b> <br/><br/><br/>
<center> <b><h5>Trial Balance Report</h5></b></center>
<apex:outputPanel rendered="{!If(totalRevenue != totalRevenue1,true,false)}">
<apex:outputLabel style="font-weight: bold;color:red;font-size:15px;padding-left:49%"> Debit and Credit Balances do not match. </apex:outputLabel>
</apex:outputPanel>
<apex:outputPanel rendered="{!If(account.DateUpdate__c== null,true,false)}">
<apex:outputLabel style="font-weight: bold;color:blue;font-size:15px;padding-left:1%" > Please enter date to fetch accurate results. </apex:outputLabel>
</apex:outputPanel>
<apex:dataTable value="{!listAccount}" var="record" border="1" cellpadding="1" cellspacing="0" bgcolor="#DAFFFF" style="font-family:verdana;color:Black;font-size:12px;height=18px;width:100%;" >
<!----<apex:pageblockTable value="{!listAccount }" var="record">---->
<apex:column headerValue="Master Code" Value="{!record.Master_Name__c}" headerClass="colHeadr" style="font-family:Cambria;color:Black;font-size:18px;height=20px;width:20%;" >
<!---- <apex:column headerValue="Master Code">
<apex:outputPanel >
<apex:commandLink action="{!Go}">
{!record.Master_Name__c}<apex:param name="Id" value="{!record.id}"/>
</apex:commandLink>
</apex:outputPanel>
</apex:column>--->
</apex:column>
<apex:column value="{!record.Master_Code__c}" headerValue="Master Name" headerClass="colHeadr" style="font-family:Cambria;color:Black;font-size:18px;height=20px;width:100%;"/>
<apex:column value="{!record.Debit_Balance__c}" headerValue="Debit(+)" style="font-family:Cambria;color:Black;font-size:18px;height=20px;width:100%;" headerClass="colHeadrRight" dir="RTL">
<apex:facet name="footer" >
<!---- <apex:outputText value=" Rs.{!totalRevenue}<br/> Rs.{!totalRevenuee}<br/>-----------------<br/> Rs.{!totalRevenuee1}" style="font-weight: bold;color:green;font-size:12px;height=18px;" escape="false">
</apex:outputText>-->
<apex:outputText value="Rs.{0, number, ###,###,###,##0.00}" style="font-family:Cambria;color:Black;font-size:18px;height=20px;width:100%;float:right;font-weight: bold;" >
<apex:param value="{!totalRevenue}" />
</apex:outputText>
</apex:facet>
</apex:column>
<apex:column value="{!record.Credit_Balance__c}" headerValue="Credit(-)" style="font-family:Cambria;color:Black;font-size:18px;height=20px;width:100%;" headerClass="colHeadrRight" dir="RTL">
<apex:facet name="footer" >
<apex:outputText value="Rs.{0, number, ###,###,###,##0.00}" style="font-family:Cambria;color:Black;font-size:18px;height=20px;width:100%;float:right;font-weight:bold;" >
<apex:param value="{!totalRevenue1}" />
</apex:outputText>
</apex:facet>
</apex:column>
</apex:dataTable>
</apex:pageBlock>
</apex:form>
</div>
</apex:define>
</apex:page>
public with sharing class dateInPBTbaleControllernewOk {
public dateInPBTbaleControllernewOk() {
}
public MasterCopy__c account{get;set;}
public List<MasterCopy__c> listAccount {get;set;}
public Double totalRevenue {get;set;}
public Double totalRevenue1 {get;set;}
public Double totalRevenuee {get;set;}
public Double totalRevenuee1 {get;set;}
public String xlsHeader {
get {
String strHeader = '';
strHeader += '<?xml version="1.0"?>';
strHeader += '<?mso-application progid="Excel.Sheet"?>';
return strHeader;
}
}
public Date dat {get;set;}
public Map<Date,List<MasterCopy__c>> mapOfDateWithEntry;
public dateInPBTbaleControllernewOk(ApexPages.StandardController controller){
account = new MasterCopy__c();
totalRevenue = 0;
totalRevenue1 = 0;
totalRevenuee = 0;
totalRevenuee1 = 0;
}
public void setValues(){
listAccount = new List<MasterCopy__c>([SELECT id,Name, Closing_Balance__c,Credit__c,Date__c,DateUpdate__c,Debit__c,Debit_Balance__c,Credit_Balance__c,
Group__c,Master_Code__c,Master_Name__c,New_Debit__c,New_Credit__c
FROM MasterCopy__c
order by Master_Name__c ASC ]);
mapOfDateWithEntry = new Map<Date,List<MasterCopy__c>>();
List<MasterCopy__c> listOfEntryData;
for(MasterCopy__c entry : listAccount){
if(entry.DateUpdate__c != null){
if(mapOfDateWithEntry.containsKey(entry.DateUpdate__c)){
mapOfDateWithEntry.get(entry.DateUpdate__c).add(entry);
}
else{
listOfEntryData = new List<MasterCopy__c>();
listOfEntryData.add(entry);
mapOfDateWithEntry.put(entry.DateUpdate__c,listOfEntryData);
}
}
}
calculateTotalRevenue();
calculateTotalRevenue1();
calculateTotalRevenue2();
}
public void displaingTable(){
try{
setValues();
if(account.DateUpdate__c!= null){
totalRevenue = 0;
totalRevenue1 = 0;
totalRevenuee = 0;
totalRevenuee1 = 0;
system.debug('Map '+mapOfDateWithEntry);
Set<Date> allDateSet = mapOfDateWithEntry.keySet();
if(allDateSet.contains(account.DateUpdate__c)){
listAccount = mapOfDateWithEntry.get(account.DateUpdate__c);
}
else {
List<Date> sortedDateList = new List<Date>();
sortedDateList.addAll(allDateSet);
sortedDateList.sort();
Boolean isAnyPastDate = false;
Date requiredDate;
for(Date recordDate : sortedDateList){
if(account.DateUpdate__c > recordDate){
requiredDate = recordDate;
isAnyPastDate = true;
}
else{
break;
}
}
if(isAnyPastDate){
listAccount = mapOfDateWithEntry.get(requiredDate);
}
else{
listAccount = new List<MasterCopy__c>();
}
/*Date compareDate ;
Integer count = 0;
for(Date firstKeydate : allDateSet){
compareDate = firstKeydate;
break;
}
system.debug('Before Compare Date'+compareDate);
for(Date keyDate : allDateSet){
if(keydate < account.DateUpdate__c && keydate > compareDate){
compareDate = keyDate;
}
if(account.DateUpdate__c < keyDate){
count ++ ;
}
}
system.debug('After Compare Date'+compareDate);
Date requiredDate ;
if(allDateSet.size() == count){
requiredDate = [Select DateUpdate__c from MasterCopy__c Order by DateUpdate__c Desc LIMIT 1][0].DateUpdate__c;
}
else {
requiredDate = compareDate;
}
listAccount = mapOfDateWithEntry.get(requiredDate);*/
}
}
calculateTotalRevenue();
calculateTotalRevenue1();
calculateTotalRevenue2();
}
catch(Exception e)
{
//ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.ERROR,'Sorry...... You have entered DUPLICATE MASTER CODE' );
// ApexPages.addMessage(myMsg);
Apexpages.addMessage(new Apexpages.message(ApexPages.Severity.Error,' NO RECORD FOUND'));
}
}
public void calculateTotalRevenue() {
//for(MasterCopy__c acct2 : listAccount) {
// if(acct2.Closing_Balance__c!= null)
// totalRevenuee= acct2.Closing_Balance__c;
//}
for(MasterCopy__c acct : listAccount) {
if(acct.Debit_Balance__c!= null)
totalRevenue+= acct.Debit_Balance__c;
}
totalRevenuee1=totalRevenuee+totalRevenue;
}
public PageReference exportToExcel() {
return new Pagereference('/apex/ExportTrial');
}
public void calculateTotalRevenue2() {
for(MasterCopy__c acct2 : listAccount) {
if(acct2.Closing_Balance__c!= null)
totalRevenuee= acct2.Closing_Balance__c;
}
}
public void calculateTotalRevenue1() {
for(MasterCopy__c acct1 : listAccount) {
if(acct1.Credit_Balance__c!= null)
totalRevenue1+= acct1.Credit_Balance__c;
}
}
}
First make sure the Object(to which record belongs) is searchable in salesforce global search box. Then try installing this app: https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009vl1ZEAQ
Its easy to setup and works great!