2

I am posting my approach, but still unable to send the VF page data as email attachment.

scheuler + batch :

global class Schedule_report implements Schedulable
{
    global void execute(SchedulableContext ctx)
    {
        list<EmailTemplate> lstEmailTemplates = [select Id,Name,DeveloperName from EmailTemplate where Name=: 'some_template'];
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
        if(lstEmailTemplates!=null && lstEmailTemplates.size()>0)
        mail.setTemplateId(lstEmailTemplates[0].Id);
        mail.setSaveAsActivity(false);
        mail.setToAddresses(new list<string>{'rao@gmail.com'});
        Messaging.SendEmailResult[] resultMail = Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

    }

    @isTest
    global static void testSchedule()
    {
        Test.startTest();
        Schedule_report  sched = new Schedule_report ();
        Id job_id = System.schedule('test', '0 0 0 30 12 ? 2099', sched);
        System.assertNotEquals(null, job_id);
        Test.stopTest();
    }
}

Email template :

<messaging:emailTemplate subject="test subjects" recipientType="User" relatedToType="Opportunity" >
<messaging:HTMLEmailBody >
<c:custom_component />
</messaging:HTMLEmailBody>
</messaging:emailTemplate>

Component :

<apex:component controller="excel_controller" access="global" >
<apex:datatable value="{!Report_for_template}" var="opp">
<apex:column headervalue="Opportunity Name">{!opp.name} </apex:column>
<apex:column headervalue="Opportunity owner">{!opp.owner.name}</apex:column>
<apex:column headerValue="Opportunity current Quarter">{!opp.Expected_Close_Quarter_1__r.name}</apex:column>
<apex:column headervalue="Opportunity Owner Emp ID" value="{!opp.owner.EmployeeNumber}"/>
<apex:column headervalue="Opportunity Region " value="{!opp.Region__c}" > </apex:column>
<apex:column headervalue="Account related to Oppty " value="{!opp.account.name}"></apex:column>
<apex:column headervalue="Account Owner" value="{!opp.account.owner.name}" > </apex:column>
<apex:column headervalue="Account Owner Emp ID" value="{!opp.Account.owner.EmployeeNumber}"/>
</apex:datatable> 
</apex:component>

The above approach / using native HTML with repeat does not help me out

when I change the Email template to :

<messaging:emailTemplate subject="test subject" recipientType="User" relatedToType="Opportunity" >
<messaging:HTMLEmailBody >
hello see the attachment 
</messaging:HTMLEmailBody>

<messaging:attachment>
<c:custom_component />
</messaging:attachment>

the contents inside the attachments can be rendered as PDF which seems to do the job but getting to make it show up as a xls/csv seems to be impossible :(

</messaging:emailTemplate>

when I use datatables inside the component and use I get the csv as :

enter image description here

when I use plain HTML + inside the component body and use I get the csv as enter image description here

the way salesforce uses the .csv is more like a drop down list that says

customer here is the info

name : name 1 
address : address 1

name : name 2
address : address 2 

When it comes to making the data fit in excel columns it seems impossible to map the data.( I was able to render this report as PDF, that is never an issue).

Questioning myself on how to map the data from datatables/table to an excel document :O. Is this even possible?

this is what I am looking for in an excel/csv format.

enter image description here

  • Have you tried the renderAs="text/csv" attribute on messaging:attachment? salesforce.com/us/developer/docs/pages/Content/…. – Andrew Fawcett May 8 '13 at 20:00
  • Also to get more control, I would just use apex:repeat (in your component) and emit the data literally e.g. {!opp.name},{!opp.owner.name},{!opp.Expected_Close_Quarter_1__r.name} ... etc – Andrew Fawcett May 8 '13 at 20:02
  • @AndrewFawcett Yes the second approach works but with the first approach thecsv produces a random html file inside the csv, I have to give this to eyescream since he helped me out with the answer in my previous thread :) – Rao May 8 '13 at 20:09
  • Yes just read his answer, looks good and indeed along the lines I was think, go for it! :) – Andrew Fawcett May 8 '13 at 20:11
  • To be honest it's still far off what I'd consider good answer, maybe some escaping of special characters (like the field separator), maybe wrapping in quotes... CSVs can be surprisingly tricky. And I'd be bit tempted to just concatenate strings in apex instead of whole VF email + component business :D – eyescream May 8 '13 at 20:13
3

(Both the question and my answer are moved from Scheduled reports as attachment as the topic is slightly different. Rao, pls wait a bit with accepting, there might be even better answers around :))

I think you just did something horribly wrong in <apex:repeat> in your code. Please check out if this is close to what you're after?

1. Controller

public with sharing class oppsreportctrl {
    public List<Opportunity> getOpps(){
        return [SELECT Name, Owner.Name, Amount, StageName FROM Opportunity LIMIT 10];
    }
}

2. Component

<apex:component controller="oppsreportctrl" access="global">{!$ObjectType.Opportunity.fields.Name.label};{!$ObjectType.User.fields.Name.label};{!$ObjectType.Opportunity.fields.Amount.label};{!$ObjectType.Opportunity.fields.StageName.label}
<apex:repeat value="{!opps}" var="o">
{!o.Name};{!o.Owner.Name};{!o.Amount};{!o.StageName}
</apex:repeat>
</apex:component>

3. Email template

<messaging:emailTemplate subject="test" recipientType="User" relatedToType="Opportunity">
<messaging:plainTextEmailBody >
Hi Salesforce StackExchange!
</messaging:plainTextEmailBody>
<messaging:attachment filename="out.csv">
<c:oppsreport />
</messaging:attachment>
</messaging:emailTemplate>

This renders for me as an email with attachment:

Name;Full Name;Amount;Stage
Email sending test;Herp Derpinson;9500.00;Negotiation/Review
test;Herp Derpinson;;Prospecting
Sample M$ Opp;Herp Derpinson;;Prospecting
test;Herp Derpinson;2859120.00;Prospecting
Grand Hotels Kitchen Generator;Herp Derpinson;15000.00;Id. Decision Makers
Burlington Textiles Weaving Plant Generator;Herp Derpinson;1234.00;Prospecting
Pyramid Emergency Generators;Herp Derpinson;100000.00;Prospecting
Dickenson Mobile Generators;Herp Derpinson;15000.00;Qualification
Grand Hotels Emergency Generators;Herp Derpinson;210000.00;Closed Won
United Oil Emergency Generators;Herp Derpinson;440000.00;Closed Won

Looks like pretty good CSV to me? I don't have Excel around at the moment (private laptop, never bothered to cough up the $) but it should open just fine.

If not - experiment with comma instead of semicolon? I think Excel's idea of Comma-Separated Values file is somewhat reta stupid and varies from locale to locale, including commas, semicolons or tabs. You could also try just saving your mock output as csv and peeking in Notepad++ or similar tool at what format does your Excel's locale "like".

  • Awesomeness is the only word. I was getting super bugged I was not able to connect the dot. The , made all the difference – Rao May 8 '13 at 20:10
  • 1
    I will wait for a bit and accept the answer. Would be worth a wait if someone comes out with a different approach. – Rao May 8 '13 at 20:22

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.