Access to add and change pages is restricted. See: https://cwiki.apache.org/confluence/display/OFBIZ/Wiki+access

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

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.

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

Here are the steps that can be used to import data in Product & InventoryItem entity from the attached spreadsheet attached with this document. 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 extension file(for which conversion is to be made) 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. Here put following entry in Import Text :­ 
    <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 have planned to schedule the job which will be executed in certain duration of time.
  5. Click on Import Text you will Get the result as "Got 3 entities to write to the datasource." This will schedule "productImportFromSpreadsheet" service.
  6. Now restart your server and you will find that the spreadsheet data has been converted in database data as well as 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 are done with the first step mentioned in the above space before performing this approach.

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.
  • No labels