Generate xlsx file from Apex

Question

I've developed some csv reports in the past through Apex code. Now, I've a requirement to develop a report in xlsx format. For CSV, I used ' , ' as delimiter and appended '.csv' to the file name. For XLSX, I used '\t' as the delimiter and appended '.xlsx'. But, if I open the generated file it throws the following error:

Excel cannot open the file 'filename.xlsx' because the file format for the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

I figured out a work around, which is to append '.xls' instead of '.xlsx'. It works, but throws a warning message at the user every time they try to open the file which is something I don't want user to experience. The warning message is

The file format and extension of ".xls" don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?

After little research,

I figured out that the issue here is that there should be at least one sheet in the workbook

So xlsx file needs to have at least one sheet wheres ascsv doesn't. So csv reports I developed in the past worked.

Is there any way i can create a sheet in Apex code? I came across some C# and Java code to add sheets. Can we do the same in Apex?

My sample code for xlsx :

String fileContent = '';
for(Account a: someList){
    fileContent += a.Name+'\t'+a.Field_One__c+'\t'+a.Field_Two__c+'\t'+a.Field_Three__c+'\t'+a.Field_Four__c+'\t'+a.Field_Five__c+'\n';
}
Document doc = new Document();
doc.Name = MyTestFile.xlsx';
doc.FolderId = folderId;
doc.Body = fileContent;
insert doc;

Answer 1

You're not going to be able to use XLSX at all. It's based on the Office Open XML standards from Microsoft. Basically, it's a set of XML files contained in a ZIP file, of which we have no native means of creating in Apex Code. Of course, you could build the constituent parts and then ship the data off to Heroku, AWS, etc, and have it finish packing up the file for you, or even use jszip to render the file in the browser, but the point is, it won't be a pure native solution.

As for using the "XLS" format, what you basically want to do is create a large XML file that conforms to Microsoft Office XML formatting schemes. This is actually how Salesforce "tricks" Excel into reading report exports when you use the "Export to Excel" report feature. You'll probably want to read the XML Spreadsheet Reference on how to build a proper spreadsheet. Alternatively, download a report from Salesforce in Excel format, and open it up in Notepad to see how they do it; it's basically a bare-bones implementation that's just good enough for Excel to figure out what to do with it.

Answer 2

I know the question has already been answered but to anyone who stumbles upon here looking for other options, I found a solution that generates an actual .xlsx file using only Apex. Take note though that the process is complicated and would take a bit research and trial and error on the side of Office Open XML to pull off.

If you already read the answer that @sfdcfox gave, you know that the biggest hurdle for this to work is Apex having no native means of working with ZIP files. Luckily someone implemented classes that would make it possible. That project is called Zippex and its made by Pedro Dal Col and Pliny Smith.

With that issue out of the way, we are now able to open up a .xlsx file and extract its contents; edit the content and fill it up with data from our Salesforce org; and then repackaged it again as a .xlsx file. All of that using only Apex. And that's generally the steps needed to generate a .xlsx file using Apex for my approach. If you like a further detailed guide for this and a working example, here is a repository I created that will be able to help you: https://github.com/RaymarMonte/apex-create-xlsx