Scheduling a visualforce page as CSV

Question

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 :

when I use plain HTML + inside the component body and use I get the csv as

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.

Answer 1

(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".