Import Data Using Apache POI api

Introduction

Apache POI is a well known API to read data from the Spreadsheet files. In this area some work has been done in JIRA issue https://issues.apache.org/jira/browse/OFBIZ-1810. Also pdf document is provided in that document. Instead of keeping document it is good to maintain that document on the OFBiz wiki.

Here are the steps that can be used to import data in Product & InventoryItem entity from the attached spreadsheet. Although this is sample example but with little effort it can be customized as per client's requirements.

1. Create a directory with the name "spreadsheet" inside [Ofbiz_Home] and put your xls file with the data to be loaded in this directory. This sample spreadsheet file is available with this doc only.
2.  Now go in the webtools and click on XML Data Import.

3. Put following entry in Import Text dialogue box:­

   <entity­engine­xml>
   <RecurrenceRule recurrenceRuleId="500" untilDateTime="" frequency="MINUTELY" intervalNumber="5" countNumber="­1"/>
   <RecurrenceInfo recurrenceInfoId="500" startDateTime="2008­05­29 01:10:00.000" recurrenceRuleId="500" recurrenceCount="0"/>
   <JobSandbox jobId="9000" jobName="Import Excel Sheet Minutely" runTime="2008­05­29 01:10:00.000" serviceName="productImportFromSpreadsheet" poolId="pool" runAsUser="admin" recurrenceInfoId="500"/>
   </entity­engine­xml>
   

4. The above entry should only be done if you want to schedule the job to be executed repeatedly with a certain frequency (5 minutes in this case).
5. Click on Import Text you will Get the result as "Got 3 entities to write to the datasource." This will schedule the "productImportFromSpreadsheet" service.
6. Now restart your server and you will find that the spreadsheet data has been loaded into the database, In addition this service will run in every 5 minutes.

Alternate Approach

This approach can be used to test the code present in OFBiz.

• If you want to test the code then you need not to schedule the service. Instead of scheduling the service you can run the service "productImportFromSpreadsheet" from Webtools -> Service Reference -> Select the service -> Run Service (Link present at the right side). For successful result you may need to run the first step mentioned in the above space.

or

• To run the service go to the run service screen directly by https://localhost:8443/webtools/control/runService and provide service name "productImportFromSpreadsheet". Please make sure that you have completed the creation of the data  mentioned in step 1 before performing starting the service.

Technical Details

• The source code is present inside product/src/org/ofbiz/product/spreadsheetimport/*.java.
• The service definition is present inside product/servicedef/services.xml file. Find the service by name "productImportFromSpreadsheet". 

Note: 

• This is a sample code which can be changed or reused for any customized need.
• The Job Scheduler mechanism of Ofbiz is used for reading the .xls file from Spreadsheet folder. Although we can create the GUI for the same and the same service can be used for doing it.

Acknowledgements

Initial contribution by Pranay Pandey & Chirag Manocha.
Minor changes in the code to make it functional and improved the return message in the code (success or error). by Ashish Vijaywargiya