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.
- 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.
- Now go in the webtools and click on XML Data Import.
Put following entry in Import Text dialogue box:
<entityenginexml> <RecurrenceRule recurrenceRuleId="500" untilDateTime="" frequency="MINUTELY" intervalNumber="5" countNumber="1"/> <RecurrenceInfo recurrenceInfoId="500" startDateTime="20080529 01:10:00.000" recurrenceRuleId="500" recurrenceCount="0"/> <JobSandbox jobId="9000" jobName="Import Excel Sheet Minutely" runTime="20080529 01:10:00.000" serviceName="productImportFromSpreadsheet" poolId="pool" runAsUser="admin" recurrenceInfoId="500"/> </entityenginexml>
- 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).
- Click on Import Text you will Get the result as "Got 3 entities to write to the datasource." This will schedule the "productImportFromSpreadsheet" service.
- 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
3 Comments
Ron Wheeler
Can this be removed?
" In this area some work has been done in JIRA issue . Also pdf document is provided in that document. Instead of keeping document it is good to maintain that document on the OFBiz wiki."
Ron Wheeler
Can this be removed or moved to an Acknowledgement section at the bottom of the page so that it is less confusing. It is a spot that should have a description of the process.
"Initial contribution is done by Pranay Pandey & Chirag Manocha.
I have done minor changes in the code to make it functional and improved the return message in the code (success or error). -- Ashish Vijaywargiya"
Ron Wheeler
"attached spreadsheet attached with this document" aside from the redundancy in the sentence , there is no attached file.
This should be replaced by a description of the Excel file. Column names at a minimum or a reference link to where the column names can be found.