Read data from Excel file using Apex in Salesforce

Question

I have a requirement where we need to parse the excel file and insert the data in one of the object using apex. This excel file contains multiple sheets. We need to read the data from multiple sheets (as of now one sheet is also fine) and upload the data.

Please help me with a solution.

Answer 1

I would suggest to provide more details about where the question starts and ends since it implies you already have the data available in your apex class but also doesn't include any information about the type/format you have it loaded as etc.

Here is a concept of what you might do (using a visualforce page and an apex controller) in case you are trying to figure out where to start:

1) Use apex:inputFile to load up the file into your apex controller (https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_compref_inputFile.htm)

Note: You can only upload files <= 10Mb this way

2) I would then send the uploaded object from my apex controller back to visualforce

One way to do this is to have a public property in the apex (e.g. MyExcelFile), and have your form trigger an apex:actionFunction to rerender a hidden component whose content is {!MyExcelFile} + run your parsing logic (https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_quick_start_display_field_values.htm) (https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_compref_actionFunction.htm)

3) Then I could parse it using whatever javascript library I felt like in my visualforce page (How to parse Excel file in Javascript/HTML5)

4) From there it is just a matter of calling a function in your apex controller which does the insert/update/whatever operation(s) and passing the params (https://eltoro.secure.force.com/PassingParametersFromVisualforcePageToApexControllerViaApexParam)