How To Use Java to Make Excel Data Available to Data Services This page last changed on Mar 20, 2008. .stickable, h1, h2, h3, h4, h5 false true Show Sticky notes Hide Sticky notes Save Cancel edocs Home > BEA AquaLogic Data Services Platform 3.0 Documentation # How To Use Java to Make Excel Data Available to Data Services
Author | Mike Reiche
This topic describes the steps needed to create a Java project that can consume Excel data. Step 1 - Create a new workspace Step 2 - Create a Java project named excel_jcom Step 3 - Enable XMLBeans builder for excel_jcom Step 4 - Create a new package Step 5 - Import the package Step 6 - Run the com2java function Step 7 - Import the Excel schema file into the src directory Step 8 - Build your project Step 9 - Create an ALDSP dataspace project Step 10 - Export the excel_jcom project Step 11 - Create a folder to contain the Excel data Step 12 - Open Excel Notes
| Save the attachments to a temporary directory.
1. ## Step 1 - Create a new workspace Create a new server in the workspace. ## Step 2 - Create a Java project named excel_jcom On first page of wizard select:
       Create separate source and output folder
2. On second page of wizard select WebLogic92's `jcom.jar`. Use the Libraries tab > External Libraries > Select:
       weblogic92\server\lib\jcom.jar
Click Finish. ## Step 3 - Enable XMLBeans builder for excel_jcom Right-click on the excel_jcom project > Properties > XmlBeans > Enable XmlBeans Builder ## Step 4 - Create a new package Create a new package named:
    com.bea.dsp.wrappers.excel
## Step 5 - Import the package Import Excel_JCOM.java into the new package. (Ignore any import errors that may appear.)
| To test this example, you can download a ZIP file containing `excel.xsd`, `Excel_JCOM.java`, and `test.xml`.
## Step 6 - Run the com2java function Run weblogic92\server\bin\com2java with the following directives:
    -output location:

    <workspace>/excel_jcom/src/com/bea/dsp/wrappers/excel/jcom
- output to package:
    -output to package:

    com.bea.dsp.wrappers.excel.jcom
3. - input your Excel executable such as Start menu > Program Files > Microsoft Office > OFFICE11 > excel.exe ## Step 7 - Import the Excel schema file into the src directory ## Step 8 - Build your project Your project should build automatically, all errors should disappear. ## Step 9 - Create an ALDSP dataspace project Create an ALDSP dataspace project. You can use the menu option: Properties > Targets > <your aldsp server> ## Step 10 - Export the excel_jcom project Export your excel_jcom project to: <dataspace-project>/DSP-INF/lib/excel_jcom.jar Select: `src` `.xbean_src` `.xbean_bin` Select the first option: Export generated class files and resources. Ignore any warning messages. ## Step 11 - Create a folder to contain the Excel data In the dataspace project, create a folder named `physical`. In that folder, create a physical data service of type Java Browse to excel_jcom.jar, com.bea.dsp.wrappers.excel.Excel_JCOM. Ignore the warnings, select only getExcel() method. Give Excel for the name of the data service. ## Step 12 - Open Excel Open Excel.ds in Test view. Select the getExcel() function. Pass the following arguments:
   Argument | Setting
   --- | ---
   arg1 | `c:/path-to/test.xls`
   arg2 | Customers
- Execute the function. ## Notes The Excel_JCOM getExcel java method reads the entire Excel document and creates XML in two forms:
      (row\[\]col\[\] and cell\[\]
You probably only need one format, so comment out the one you don't need. When you are using getExcel() in an xquery, call it once and store the result in a variable rather than making multiple calls. Excel_JCOM uses a cached instance of Excel and leaves it running. Therefore you will need to manually kill the Excel processes. This may affect performance.

Document generated by Confluence on Nov 26, 2008 13:01