[SalesForce] need to process an excel file with apex

I have a need to process an excel file with apex.

Once a user uploads an excel.

Once they click the upload button it should store probably in contentversion (libraries) or documents and once it is created, probably it should have a trigger
(guess contentversion doesnt have a trigger. is it possible to have a trigger?) to invoke a batch apex.

And on start method of it am planning to read the uploaded excel and process the records in batch.

Any idea if this excel file reading/processing can be done in apex?

Best Answer

A .xls file is in a proprietary binary format that takes a mountain of detailed code to process. For example in Java there is POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files. But aside from the complexity, Apex has no mechanisms to manipulate binary data.

A .xlsx is a bunch of XML files (that could be processed in Apex) but the files are zipped into the single .xlsx file. But there is no Apex zip/unzip API today.

If you insist on people exporting from Excel to .csv first, then you can process the files quite easily in Apex if the .csv content is simple by just splitting on the commas. But if you want to support embedded quotation marks and line feeds the code gets much more complicated.

Any of these 3 would also suffer from the problem that the 200,000 script statement governor limit gets used up very quickly when dealing with many cells or characters. Though batch Apex could help there.

So my answer to "Any idea if this excel file reading/processing can be done in apex?" is no. Or is it? See below.

PS

I came across this recently Generate an excel file dynamically from the salesforce record detail page. I don't get how the zip gets created unless it is courtesy of the:

contenttype="application/vnd.ms-excel#MultiSheetExcel.xls"

Certainly worth trying out. Official docs Using a Custom ContentType.

Related Topic