Excel Datasources

By Tim Dexter

We have been getting requests asking if we can handle an Excel file as a datasource for a report. I guess the business case is that users maintain and enter new data using Excel spreadsheets and need the entered to support a report for management.

With the BIP Server you could have done it with the JDBC-ODBC bridge or maybe a conversion to a CSV format and then load to a db ... both work but are fiddly to setup and maintain. With the 10.1.3.2, R12 and 5.6.3 for 11i releases there are a set of java APIs that can be used to access and read binary Excel files generating XML data. I have to admit these are somewhat hidden APIs; they will be the basis of our Excel template strategy when they arrive later this year. For now this entry will serve as the documentation for the APIs. ... apologies.

In this article I'll cover the use of the Excel2Data API, this is used to read data from the Excel file and generate XML output. I have mounted the javadoc here. The simplest implementation would be:

public class Excel2XML {
    public Excel2XML() {
       String inExcel = "d://temp//excel//1.xls";
       String outFile = "d://temp//excel//1.xml";
        Properties prop = new Properties();
        prop.setProperty("system-temp-dir", "d://temp//excel//tmp");
        
        Excel2Data xls2Data = new Excel2Data();
        xls2Data.setOutputType(Excel2Data.OUTPUT_TYPE_XML);
        xls2Data.setConfig(prop);
         try 
         {
            xls2Data.loadExcel(inExcel);
            xls2Data.generate(outFile);
         }
         catch (IOException exc)
         {
            exc.printStackTrace();
         }
         catch (XDOException e)
         {
            e.printStackTrace();      
         }
        }

    public static void main(String[] args) {
        Excel2XML excel2XML = new Excel2XML();
    }
}

inExcel - can be an InputStream or a File location
outFile - can be an OutputStream or File location
system-temp-dir - this is a temporary working directory that the API needs to work in and must be set.
output format - the only other unknown for you should be the output type ie Excel2Data.XXXXX. This can take one of three values, two are XML formats and the third a CSV format:

OUTPUT_TYPE_XML  - generates the following format of XML. This format will work with any Excel format. Its a standard XML format i.e. no matter what Excel you use the XML format will remain the same.

<workbook>
  <sheet>
    <name>Sheet1</name>
    <row number='0'>
      <column number='0'>Employee Listing</column>
    </row>
    <row number='2'>
      <column number='0'>Name</column>
      <column number='1'>Title</column>
      <column number='2'>Salary</column>
    </row>
    <row number='3'>
      <column number='0'>Jones</column>
      <column number='1'>Managing Director</column>
      <column number='2'>60000.0</column>
    </row>
</workbook>

OUTPUT_TYPE_XDOXML  - this format can only be generated if you use named cells. This is the basis of the coming Excel templates. Its structure is completely dependent on the data.

<XDOROOT>
  <EMPLOYEE>
    <NAME>Jones</NAME>
    <TITLE>Managing Director</TITLE>
    <SALARY>60000.0</SALARY>
  </EMPLOYEE>
</XDOROOT>

OUTPUT_TYPE_CSV - this, if you ever need it generates a comma delimited file output.

Sheet1
"Employee Listing"
"Name","Title","Salary"
"Jones","Managing Director",60000

Great so now you can get data XML from a binary Excel file. You can get the sample file and java class here. How can I use the API in a real scenario I hear you ask?

Well, you now know how to use the API so you could create a java class to do the conversion and then feed that to the formatting API, FOProcessor. What would be way more cool and useful would be to allow the BIP Server to fetch the data from the Excel at runtime, so you could set up Excel datasources so that report consumers could get a snap shot of data from the Excel files at will.
Tune in tomorrow to see how that can be done ... sorry gotta dangle that carrot so you come back for more.