Import Multiple Files With Multiple Sheets in BIRT

 

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:

Customer ID Customer Name Invoice Number Sale Amount Purchase Date
1234 John Smith 100-0002 $1,200.00 2013/1/1
2345 Mary Harrison 100-0003 $1,425.00 2013/1/6
3456 Lucy Gomez 100-0004 $1,390.00 2013/1/11
4567 Rupert Jones 100-0005 $1,257.00 2013/1/18
5678 Jenny Walters 100-0006 $1,725.00 2013/1/24
6789 Samantha Donaldson 100-0007 $1,995.00 2013/1/31

february_2013:

Customer ID Customer Name Invoice Number Sale Amount Purchase Date
9876 Daniel Farber 100-0008 $1,115.00 2013/2/2
8765 Laney Stone 100-0009 $1,367.00 2013/2/8
7654 Roger Lipney 100-0010 $2,135.00 2013/2/15
6543 Thomas Haines 100-0011 $1,346.00 2013/2/17
5432 Anushka Vaz 100-0012 $1,560.00 2013/2/21
4321 Harriet Cooper 100-0013 $1,852.00 2013/2/25

march_2013:

Customer ID Customer Name Invoice Number Sale Amount Purchase Date
6634 Poop Smith 100-0014 $1,350.00 2013/3/4
8765 Tony Song 100-0015 $1,167.00 2013/3/8
2345 Mary Harrison 100-0016 $1,789.00 2013/3/17
6543 Rachel Paz 100-0017 $2,042.00 2013/3/22
3456 Lucy Gomez 100-0018 $1,511.00 2013/3/28
4321 Susan Wallace 100-0019 $2,280.00 2013/3/30

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

Customer Name Sale Amount
John Smith 1200
Mary Harrison 1425
Lucy Gomez 1390
Rupert Jones 1257
Jenny Walters 1725
....... ........
Susan Wallace 2280

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:

A
1 =file("D:/sales_2013.xlsx").xlsopen()
2 =A1.conj(A1.xlsimport@t('Customer Name','Sale Amount';~.stname))
3 return A2

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:
001png

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:

A B
1 for directory@p("d:/excel/*.xlsx") =file(A1).xlsopen()
2 =B1.conj(B1.xlsimport@t('Customer Name','Sale Amount','Purchase Date';~.stname))
3 =@|B2
4 return B3

 

A4: Return the merging result :

Customer Name Sale Amount Purchase Date
John Smith 1200 2013-01-01
Mary Harrison 1425 2013-01-06
Lucy Gomez 1390 2013-01-11
Rupert Jones 1257 2013-01-18
...... ...... ......
Thomas Haines 1346 2013-02-17

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.