[SalesForce] Generate xlsx file from Apex

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;

Best Answer

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.

Related Topic