1. Import a Files With Multiple Sheets.

Below is an Excel file containing sales data. There are 3 worksheets of the same structure in the file.

january_2013:

february_2013:

march_2013:

The merging requires getting two fields, Customer Name & Sales Amount, from each worksheet. Below is the final merging result:

Solution:

1). Using Excel Datasource in BIRT.

In the Edit Data Set dialog, select Property Binding in the left pane. Then in the Worksheet(s): text box, you can enter (for example if your parameter name was theSheet):
params[“theSheet”].value

2). Using esProc Datasource in BIRT.

esProc SPL script:

2. Import Multiple Files With Multiple Sheets

Here are several Excel files in which every worksheet containing a year’s sales data has the same structure as the worksheets in the previous instance:

Solution:

1). Using Apache POI in a scripted data source.

You can use Apache POI to dynamically read-in the worksheet names from the XLSX workbook. And populate a scripted data source/data set using these values.

You need to specify all of the JARs(Apache POI version 3.9 in a BIRT 4.5.0 report) for Apache POI in the report design (Resources).

Here is the .rptdesign XML of that definition:

<list-property name="scriptLibs"> 
  <structure> 
    <property name="name">stax-api-1.0.1.jar</property> 
  </structure>  
  <structure> 
    <property name="name">xmlbeans-2.3.0.jar</property> 
  </structure>  
  <structure> 
    <property name="name">dom4j-1.6.1.jar</property> 
  </structure>  
  <structure> 
    <property name="name">junit-3.8.1.jar</property> 
  </structure>  
  <structure> 
    <property name="name">log4j-1.2.13.jar</property> 
  </structure>  
  <structure> 
    <property name="name">poi-ooxml-3.9-20121203.jar</property> 
  </structure>  
  <structure> 
    <property name="name">poi-ooxml-schemas-3.9-20121203.jar</property> 
  </structure>  
  <structure> 
    <property name="name">commons-codec-1.5.jar</property> 
  </structure>  
  <structure> 
    <property name="name">commons-logging-1.1.jar</property> 
  </structure>  
  <structure> 
    <property name="name">poi-3.9-20121203.jar</property> 
  </structure> 
</list-property>

2). Using esProc Datasource in BIRT.

esProc SPL script:

A4: Return the merging result :

The report can be designed in the same way as you would if you were retrieving the data from a database. For detail SPL integration with BIRT, see How to Call an SPL Script in BIRT. If you have any questions or comments please leave them below.