13

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?

Improve this question
1
  • 3
    Integration like this is best done by a separate app.
    – Mike Chale
    Commented Nov 29, 2012 at 16:20

3 Answers 3

Reset to default
17

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.

Improve this answer
4
  • oh thanks for the information @Keith as you said even i was pretty skeptical about the script statements part, as we would hit the limits even if its a csv. Actually I should allow users to do some bulk edit and once they change some expirationdate field of all records i should update them in SF backend. So was expecting something of this kind so that users can do it easily through excel. any other ideas of how it can be achieved?
    – Sathya
    Commented Nov 30, 2012 at 8:24
  • 1
    "But there is no Apex zip/unzip API today.", this is correct in pure Apex. However it is possible to combine the use of the JSZip library with Apex and Visualforce. As per SKALIDHASAN's answer I have a blog post and github repo with code to do this against Open Office Files (which are essentially Office files ending in X).
    – Andrew Fawcett
    Commented Apr 6, 2013 at 8:45
  • 1
    Keith, your point about CSV is also a good one, as is the point about it not always being that trivial when considering quotation and other details of the CSV standard. We have found an excellent Apex CSV library and wrapped it in Batch Apex to give maximum scalability, there is a blog post here if you wish to reference it in your answer feel free. developer.financialforce.com/customizations/…
    – Andrew Fawcett
    Commented Apr 6, 2013 at 8:59
  • 1
    Zip and unzip in apex now possible (Developer Preview) help.salesforce.com/s/…
    – compski
    Commented May 8 at 9:29
6

Take a look at Andrew's impressive post here

enter image description here

Improve this answer
1
  • 1
    The XML data for the spreadsheet can be found in /xl/worksheets/sheet1.xml for example. The demo in the post shows unzipping to custom object and storing the spreadsheet data via Attachments. However by implementing the callbacks from the components used you can do whatever you like.
    – Andrew Fawcett
    Commented Apr 6, 2013 at 11:04
4

Continuing my research got another impressive Library (XLSX.js, JSzip.js) which does read XLSX files and converts them into .CSV/JSON ... you just need to drag and drop the file into the Text Area.

Download the Zip Files, Open index.html and see the magic!!!

Click Here For Files Index Page

Improve this answer
1
  • JSZip is a great library, very solid, it is used internally in the VF components described in my blog post you referenced on your other answer. I've not seen the XLSX JS wrapper around it, will take a look! Thanks!
    – Andrew Fawcett
    Commented Apr 9, 2013 at 14:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .