0

How will I be able to generate a proper excel sheet with a proper table with data pulled from salesforce and applied in the excel sheet in a proper table with proper columns and rows. And also would like to know how to highlight a particular data in the excel sheet with the same apex code which generates the excel sheet. (APEX code should generate the excel sheet and also highlight certain data which is required to be highlighted)

My code FYR, but this doesn't gives me a proper excel sheet but gives a sheet just like a notepad version and see the image attached for reference this is how I get when I run the code below enter image description here

List<VanaHCM__Absence_Request__c> ARlist = [Select name, VanaHCM__Absence_Type_Request__r.name, VanaHCM__Dump_Start_Date__c, VanaHCM__Dump_End_Date__c, VanaHCM__Actual_Absence__c, VanaHCM__Approved__c
                                            From VanaHCM__Absence_Request__c 
                                            WHERE ((VanaHCM__Dump_Start_Date__c >= 2015-01-18 AND VanaHCM__Dump_Start_Date__c <= 2015-01-23) OR (VanaHCM__Dump_End_Date__c >= 2015-05-18 AND VanaHCM__Dump_End_Date__c <= 2015-05-23))
                                            AND (VanaHCM__Requested_By__r.Vanahcm__User__r.userrole.name LIKE '%xxx%' or VanaHCM__Requested_By__r.Vanahcm__User__r.userrole.name LIKE '%xxx%' or VanaHCM__Requested_By__r.Vanahcm__User__r.userrole.name LIKE 'xx%')
                                            order by VanaHCM__Dump_Start_Date__c Limit 100];
string header = 'Absence Request Id, Absence Type, Start Date, End Date, Total Absence Requested, Approved \t';
string finalstr = header ;
for (VanaHCM__Absence_Request__c AR :ARlist)
{
       string recordString = AR.Name+','+AR.VanaHCM__Absence_Type_Request__r.name+','+AR.VanaHCM__Dump_Start_Date__c+','+AR.VanaHCM__Dump_End_Date__c+','+AR.VanaHCM__Actual_Absence__c+','+AR.VanaHCM__Approved__c +'\t';
       finalstr = finalstr +recordString;
}
Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
blob xlsxBlob = Blob.valueOf(finalstr);
string xlsxname= 'Absence_Request.xlsx';
csvAttc.setFileName(xlsxname);
csvAttc.setBody(xlsxBlob);
Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
String[] toAddresses = new list<string> {'xxx@xxxx.com'};
String subject ='Absence_Request XLSX';
email.setSubject(subject);
email.setToAddresses( toAddresses );
email.setPlainTextBody('Absence_Request XLSX ');
email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttc});
Messaging.SendEmailResult [] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});

Thanks in advance

2

You need to set an attachment content type, for your computer to open the file in Excel. You'll also want to use a CSV extension, since this isn't a true .xslx. Try the following section to replace where you create the attachment in your code:

Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
blob xlsxBlob = Blob.valueOf(finalstr);
string xlsxname = 'Absence_Request.csv';
csvAttc.setFileName(xlsxname);
csvAttc.setContentType('text/csv');
csvAttc.setBody(xlsxBlob);
0

You can do that easily using VF pages. It is difficult to understand the code that you have added.

But for reference, try starting with something like this -

VF page

<apex:page controller="contactquery" contentType="application/vnd.ms-excel#SalesForceExport.xls" cache="true">
    <apex:pageBlock title="Export Results" >
        <apex:pageBlockTable value="{!cs}" var="contact">
            <apex:column value="{!contact.ID}"/>
            <apex:column value="{!contact.Name}"/>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

Controller

public class contactquery{
    public List<Contact> cs{get; set;}
    public contactquery()
    {
    cs = new List<Contact>();
       for (Contact c : [Select id, Name from Contact])
       {       
           cs.add(c);
       }
    }
}

You can add stylesheets to add colors in excel.

Do let me know if it helps..!!

Thanks, Ray

2
  • Thanks for the tip Ray, but I'm actually trying to send this as an email attachment to my client. Will there be any possibilities in VF page that it can display the list as well as send it as an excel sheet attachment via email with data being highlighted. If pls let me know. thank u once again.
    – Nemesis88
    Jul 27 '15 at 6:47
  • You will need to use Single Email message setFileAttachments() method for same. Download this excel and save it as a Salesforce attachment record. Then attach it to single email message record. Jul 27 '15 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.