how to generate an excel sheet using apex code such that the excel sheet has proper table and data in order

Question

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

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> {'[email protected]'};
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

Answer 1

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);

Answer 2

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