12

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?

  • 3
    Integration like this is best done by a separate app. – Mike Chale Nov 29 '12 at 16:20
15

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.

  • 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 Nov 30 '12 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 Apr 6 '13 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 Apr 6 '13 at 8:59
5

Take a look at Andrew's impressive post here

enter image description here

  • 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 Apr 6 '13 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

  • 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 Apr 9 '13 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.