function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ram123ram123 

Creating Excel File in Apex code

I'm wondering is it possible to create Excel or CSV file in apex code (as attachment) is it possible ? currently i only see it works with VF page, but i'm looking to do it in apex code not using vf page, I don't see any options.

 

Any help is appreciated.

 

Thanks

Ram

 

Best Answer chosen by Admin (Salesforce Developers) 
Andy BoettcherAndy Boettcher

If you can render a VF page the way you want it to look (grid-style), you can change the content type in the apex:page tab to be contenttype="application/vnd.ms-excel" to get it into Excel format.

 

Once you do that - you can wrap it up in an Http object method in an APEX class.  Call the VF page within APEX, parse the response into a blob variable, then you can slide that blob right into an Attachment record.  I just did this for a customer of mine who wanted to take a VF page rendering as PDF with a single button press generate and attach it to the parent record.  Works slick...the user just clicks the button and is immediately returned back to the originating page with the attachment created.

 

If you're looking to systematize it more - you can replace "button click" with "another APEX method" to do the same thing.

 

If that's the approach you want to take, let me know and I can pass along some code structure to get you started.  =)

 

-Andy

All Answers

Starz26Starz26

Good question...

 

I know it is possible, just not how it is done. We currently have an app exchange app (cogna composer) i believe, that creates a powerpoint file and attaches it to the Opportunity. I do not think it is native functionality and I would love to know how it is done, especially PDF's etc.

 

I have a requirement coming in the future to created PDF's or word documents from data in SFDC. The google graphs do not work in VF pdf rendering but do work when you print to pdf so I am curious as to the answer to your question as well.....
Andy BoettcherAndy Boettcher

If you can render a VF page the way you want it to look (grid-style), you can change the content type in the apex:page tab to be contenttype="application/vnd.ms-excel" to get it into Excel format.

 

Once you do that - you can wrap it up in an Http object method in an APEX class.  Call the VF page within APEX, parse the response into a blob variable, then you can slide that blob right into an Attachment record.  I just did this for a customer of mine who wanted to take a VF page rendering as PDF with a single button press generate and attach it to the parent record.  Works slick...the user just clicks the button and is immediately returned back to the originating page with the attachment created.

 

If you're looking to systematize it more - you can replace "button click" with "another APEX method" to do the same thing.

 

If that's the approach you want to take, let me know and I can pass along some code structure to get you started.  =)

 

-Andy

This was selected as the best answer
sfdcfoxsfdcfox

A CSV file is easy... You can create the CSV file in memory as a long string (remember the CRLFs!), then use the Blob class to convert the string to a Blob, which can then be sent as an Attachment (i.e. EmailAttachment). This would be a pure Apex Code method that doesn't rely on calling Visualforce at all. Of course, if you're trying to render anything more complex, like a XLS file (using the same trick that Force.com uses by tricking the system into reading an XML file...), Visualforce would probably offer superior performance than a pure Apex Code method of the same type. I've actually done this for a project that involved scheduled email reports, so I know it's exceptionally trivial. You just have to remember to liberally use quotation marks so Excel won't be upset with you.
nasknask

just run the below code if it helps  :)

This is a small poc. If its your solution the please mark as soultion .

 

List<Account > acclist = [Select id,name , CreatedDate , lastModifiedDate from Account limit 10];
string header = 'Record Id, Name , Created Date, Modified Date \n';
string finalstr = header ;
for(Account a: acclist)
{

       string recordString = a.id+','+a.Name+','+a.CreatedDate+','+a.LastModifiedDate +'\n';

       finalstr = finalstr +recordString;

}

Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
blob csvBlob = Blob.valueOf(finalstr);
string csvname= 'Account.xls';
csvAttc.setFileName(csvname);
csvAttc.setBody(csvBlob);
Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
String[] toAddresses = new list<string> {'test@test.com};
String subject ='Account CSV';
email.setSubject(subject);
email.setToAddresses( toAddresses );
email.setPlainTextBody('Account CSV ');
email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttc});
Messaging.SendEmailResult [] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});
nasknask

Make the extension as csv. then it works absolutely fine.
ram123ram123

this idea is kind of work around, but it will work for my situation I think or I have to make the design to work with VF pages.

 

Thanks to all the other folks for good ideas, esp writing native solution using blob in sfdc which might a project to do in side in free time.

 

Ram

 
nasknask

USE'\t' instead of , in my code it should work as desired without changing to csv.
sdusdu

Andy, could you provide some sample code? Also would this work in Apex batch?

 
Andy BoettcherAndy Boettcher

Batch = yes.

 

What part of the code would you like some assistance with?

 

-Andy
JitheshJithesh

Hello Andy!

 

I am facing similar situation where i need to create an excel file  from the  list of child records and attach it to the parent record. Can you please help to figure out  how to go about it.

 

Thanks,

Jithesh
ram123ram123

I'm interested in knowing if anyone tried doing encoding in apex for pdf.

 

Thanks

Ram

 
Deepak SaxenaDeepak Saxena

Hi,

 

Can you please send me the basic code structure for this.

 

Thanks in advance...

 
padma_2700padma_2700

Hi ,

 

My requirement is to send a report as an excel attachment to a particular user,for that I have created a batch class where I am able to create an excel and place information in it and send it to the required user.

 

Problem is I am unable to format an excel (like cell background color, setting heading ) in the batch apex.

 

Is it possible in apex? If possible please help me out.

 

Thanks in advance.
Manoj Parida 2Manoj Parida 2
Hi Andy,

Could you share some piece of code for this? Calling a vf from Apex and getting the blob. We have a similar requirement in the project.

Thanks,
rajesh k 10rajesh k 10
Urgent:How to import multiple sheets(means multiple objects with single excel with different sheets)?
Hi,
I have single Excel sheet but this single excel sheet having multiple sheets(means multiple objects each sheet).How to import multiple sheets(means multiple objects with single excel with different sheets).

Using below code i was done import for single excel sheet for single object with but how to do import multiple objects(means i have one excel but this excel having multiple sheets(means multiple objects))

thanks
 
theseptemberguytheseptemberguy
Hi Andy,

Thanks a ton for the information. Very useful.

I have a visualforce page that has a button called "export to excel". When the user clicks that button whatever this page has i export that to an excel. This is done by calling a new visualforce with contect type as excel. Now i want to automate this job so that everyweek the excel is generated and stored in a particular shared folder.

Going by your answer looks like this is possible. Can you please confirm? I will try it today and if need help will post it here. 
poonam wadhwani 13poonam wadhwani 13
Hi theseptemberguy,

Did you get some way to solve this . My requirement is similar to yours where I need to generate a csv on daily basis and save it in a public folder in Salesforce.

Any help would be appreciated.

Thanks.
Rakesh SRakesh S
Hi Andy,

can you please provide some code snippet to understand. it more helpfull to us. 

I appriciate your response.


Rakesh S.
Kt YadavKt Yadav
Hi Rakesh,

Please find below link to create excel dynamically from visualforce page.
https://sfdceinstien.wordpress.com/2017/09/09/generate-excel-file-dynamically-from-the-salesforce-record-detail-page/

If it helps, you can close this question by marking it solved.

Regards,
Keerti Yadav
 
Inder KanojiaInder Kanojia
Hi rajesh k 10,

Did you found solution of your problem?
I have the same task where I want to generate excel with multiple sheets and sent it as attachment.
Anvesh SinghAnvesh Singh
@nask has provided a good solution just replace ',' with '\t' and have contentType ="application/vnd.ms-excel#{!fileName}.xls"
ChrisYazChrisYaz
Sure do wish @Andy Boettcher shared some code structure before going off grid. I am very close but I can't figure out how to grab the body of the excel via http. Every time I try and debug the body I did grab, it looks like javascript as if I'm only saving the code for the "Save As" dialog that pops up when you navigate to the VF page. How do we grab the content of the excel file using apex?
ChrisYazChrisYaz
I was able to get my code working. I used this answer to first generate the excel file using visualforce: https://salesforce.stackexchange.com/questions/235368/is-there-a-way-to-dynamically-fill-excel-sheets-from-apex/235380#235380

I then used the below code to take that visualforce page, and save is as an excel file attached to the original record:
//Set up the page
PageReference pgRef= Page.*YourVFPageName*;

//Create the File
ContentVersion cv = new ContentVersion();
cv.VersionData = pgRef.getContent();
cv.Title = '*YourFileName*.xls';
cv.PathOnClient = '*YourFileName*.xls';
insert cv;

//Link the File to Parent Record
ContentDocumentLink cdl = new ContentDocumentLink();
cdl.ContentDocumentId = [SELECT Id, ContentDocumentId FROM ContentVersion WHERE Id =: cv.Id].ContentDocumentId;
cdl.LinkedEntityId = *YourRecordId*;
cdl.ShareType = 'V';
insert cdl;

Hope this helps someone