Excel Generation using Dynamic Jasper-3.0

Introduction

Dynamic Jasper (DJ) is an open source free library that hides the complexity of Jasper Reports, it helps developers to save time when designing simple/medium complexity reports generating the layout of the report elements automatically.

DJ creates reports dynamically, defining at runtime the columns, column width groups, variables, fonts, charts, crosstabs, sub reports page size and everything else can be defined at design time.

More about Dynamic Jasper

Most of the features are provided directly by Jasper Reports

Dynamic column report: Columns can be defined at runtime, which means you also control (at runtime) the column positioning, width, title, etc.

Clean Excel export: One of the most valuable features that DJ provides is exporting plain reports to excel, with no data formatting, no page break, etc. This is very valuable for end users that use this report to create dynamic tables in Excel, creating these reports just with Jasper Reports can demand a lot of design time.

Auto text: Add auto text in page header and footer such as “Page 1 of 10”, “Generated on Oct. 10th 2005” or a custom text.

Styles: Each column can have its own style for its title and detail data (defining border, border color, font size, type and color, background color, etc.).

Calculation Variables: Repeating groups can have variables that hold the result of an operation on a given field (column). With DJ adding variables is a 1 line of code task.

Charts: Easy to add simple charts.

Barcode columns: As simple as adding a regular column.

Add labels in group header and footer: Dynamic Jasper enables to have labels in group header and footer, especially for each variable (on top of it) or a general label for all variables at the left.

Sub reports are supported; they can also be dynamically created.

Concatenating many reports in a single one

EXCEL GENERATION USING DYNAMIC JASPER

I. Generating excel using dynamic Jasper is the easy Process. Below code shows how to create a simple report.

• For Column definitions, we use a new ColumnBuilder instance for each Column. The ColumnBuilder.getInstance() method returns a new instance of the builder.
• The DynamicReportBuilder provides the methods to pass the basic parameters to the report: They are,

· addColumn( <title>, <field/property to show>, <class name of the property to show>, <width of the column> )

· addGroups (<number of columns to group by>)

· setTitle(...)

· setSubtitle( ...)

The code snippet:

 

DynamicReportBuilder drb = new DynamicReportBuilder();

AbstractColumn firstName = ColumnBuilder.getInstance()
.setColumnProperty("firstName", String.class.getName())
.setTitle("First Name")
.setWidth(15);

AbstractColumn lastName = ColumnBuilder.getInstance()
.setColumnProperty("lastName", String.class.getName())
.setTitle("Last Name")
.setWidth(15);

AbstractColumn Age = ColumnBuilder.getInstance()
.setColumnProperty("lastName", Integer.class.getName())
.setTitle("Last Name")
.setWidth(15);

drb.addColumn(firstName);
drb.addColumn(lastName);
drb.addColumn(Age);
drb.setUseFullPageWidth(true);

Snippet brief:

•    setColumnProperty:

        Defines the field of the data source that the column will show, also its type (Integer, Float, BigDecimal, Double, Date, etc)

•    setTitle:

        Set the title for the column

•    setWidth

        Set the width of the column

•    addColumn

        Add the columns to the report (through the builder) in the order we want them to appear

•    setUseFullPageWidth(<Boolean>)

         If true, the columns width will be resized proportionally until meet the page width

II. Moving the column into Excel sheet

Ø The DynamicReportBuilder builds DynamicReport objects, this object contains the main options of the report (title, subtitle, styles, page size, orientation and a list of columns and groups)

DynamicReport dynamicReport = drb.build();

// Create a JRDataSource and dataList is the list from DB

JRDataSource  dataSource = new JRBeanCollectionDataSource(dataList);

// Creates the JasperPrint object, pass as a dynamicReport and DataSource
as the Parameter

JasperPrint jasperPrint =  DynamicJasperHelper
.generateJasperPrint(dynamicReport,
new ClassicLayoutManager(), dataSource);

// Initializing the finalbyteArray(byte array) and  ServletOutputStream

byte[] finalbyteArray = null;
ServletOutputStream sout = null;

ByteArrayOutputStream outputStream = new ByteArrayOutputStream();


// Using JRXlsExporter to exports a JasperReports document to XLS format.


JRXlsExporter exporterXLS = new JRXlsExporter();


//  Excel Spacification Parameter


exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM,    outputStream);
exporterXLS.exportReport();
finalbyteArray = outputStream.toByteArray();


// This will give you the popup window with the Option(Open,Save  or Cancel)

response.setContentType("application/xsl");
response.setContentLength(finalbyteArray.length);

// filename,which is used to save the file

response.setHeader("Content-Disposition", "attachment; filename="excel.xls");
sout = response.getOutputStream();


//Writing the final byteArray into ServletOutputStream
sout.write(finalbyteArray);
sout.flush();
DynamicReport dynamicReport = drb.build();

// Create a JRDataSource and dataList is the list from DB

JRDataSource  dataSource = new JRBeanCollectionDataSource(dataList);

// Creates the JasperPrint object, pass as a dynamicReport and DataSource
as the Parameter

JasperPrint jasperPrint =  DynamicJasperHelper
.generateJasperPrint(dynamicReport,
new ClassicLayoutManager(), dataSource);

ADDING GROUPS

//define the criteria column to group by (some column: for eg., state)
// GroupBuilder  is used to group the columns
    
GroupBuilder gb1 = new GroupBuilder();
          DJGroup dgGroup = gb1.setCriteriaColumn((PropertyColumn) state)
                  .setGroupLayout(GroupLayout.DEFAULT).build();

//add the group to the DynamicReportBuilder
            
drb.addGroup(dgGroup);

ADDING STYLE

Ø To add style to column header or tittle, use Style class

Style headerStyle = new Style();

Ø To set column header Font Face,textcolor,borderColor and Transparency

headerStyle.setFont(Font.ARIAL_SMALL_BOLD);
headerStyle.setTextColor(Color.BLACK);
headerStyle.setTransparency(Transparency.OPAQUE);
headerStyle.setBorderColor(Color.BLACK);

Ø To set report title Font Face,textcolor,borderColor and Alignment

Style titleStyle = new Style();
titleStyle.setFont(Font.ARIAL_MEDIUM);
titleStyle.setBorder(Border.THIN);
titleStyle.setHorizontalAlign(HorizontalAlign.CENTER);
titleStyle.setVerticalAlign(VerticalAlign.MIDDLE);
titleStyle.setBorderColor(Color.LIGHT_GRAY);

ADDING HEADER AND/OR FOOTER TO A REPORT

Ø To add Header or Footer to the report

drb.addAutoText(AutoText.AUTOTEXT_PAGE_X_SLASH_Y,AutoText.POSITION_FOOTER, AutoText.ALIGMENT_RIGHT, 30, 30);
drb.addAutoText(AutoText.AUTOTEXT_CREATED_ON, AutoText.POSITION_FOOTER,
AutoText.ALIGMENT_LEFT, AutoText.PATTERN_DATE_DATE_TIME,285,285);

drb.addAutoText(AutoText.AUTOTEXT_CREATED_ON, AutoText.POSITION_HEADER
AutoText.ALIGMENT_LEFT, AutoText.PATTERN_DATE_DATE_TIME,285,285);

TWO TABLES IN EXCEL

Ø Creating multiple Table in Excel is same as creating the Single Table, but instead of Passing single DataSource and Dynamic Report, you need to pass multiple Datasource and Dynamic Report.

// Creating two DynamicReportBuilder  instant
DynamicReportBuilder firstdrb = new DynamicReportBuilder();
DynamicReportBuilder Seconddrb = new DynamicReportBuilder();

// First Table will contain two Columns (firstName(<String>) and LastName(<String>))
AbstractColumn firstName = ColumnBuilder.getInstance()
.setColumnProperty("firstName", String.class.getName())
.setTitle("First Name")
.setWidth(15);

AbstractColumn lastName = ColumnBuilder.getInstance()
.setColumnProperty("lastName", String.class.getName())
.setTitle("Last Name")
.setWidth(15);

// Second Table will contain two Columns (Age(<Integer>) and State<String>)
AbstractColumn Age = ColumnBuilder.getInstance()
.setColumnProperty("lastName", Integer.class.getName())
.setTitle("Last Name")
.setWidth(15);

AbstractColumn State  = ColumnBuilder.getInstance()
.setColumnProperty("lastName", String.class.getName())
.setTitle("Last Name")
.setWidth(15);

//  Adding  firstName and lastName in   firstdrb (DynamicReportBuilder)
firstdrb.addColumn(firstName);
firstdrb.addColumn(lastName);
firstdrb.setUseFullPageWidth(true);

//  Adding  Age and state in   firstdrb (DynamicReportBuilder
Seconddrb.addColumn(Age);
Seconddrb.addColumn(state);
Seconddrb.setUseFullPageWidth(true);


//Initializing JRDataSource
JRDataSource firstjrDataSource    = null;
JRDataSource secondjrDataSource1 = null;

//Initializing DynamicReport
DynamicReport firstdReport               = null;
DynamicReport seconddReport        = null;

//Initializing JasperPrint
JasperPrint jPrint                   = null;
JasperPrint jp1                              = null;


// Building both the report!
if(firstdrb!= null){
firstdReport = firstdrb.build();
}
if(Seconddrb!= null){
seconddReport = Seconddrb.build();
}

// Passing  firstList to firstjrDataSource
if(firstList!= null && firstList.size() > 0){
firstjrDataSource = new JRBeanCollectionDataSource(firstList);
}


// Passing  secondList to secondjrDataSource1(
if(secondList!= null && secondList.size() > 0){
secondjrDataSource1 = new JRBeanCollectionDataSource(secondList);
}

// Creating two JasperPrint(jPrint,jp1)  instance and both the List to JasperPrint
if(dReport != null && jrDataSource != null){
jPrint = DynamicJasperHelper.generateJasperPrint(firstdReport,
new  ClassicLayoutManager(),firstjrDataSource);
}

if(dReport1 != null && jrDataSource1 != null){
jp1 = DynamicJasperHelper.generateJasperPrint(seconddReport,
new ClassicLayoutManager(),  secondjrDataSource1);
}

//  Iterating both the JasperPrint(jPrint ,jp1), getting  the tables as separate pages and finally adding the pages to Single Jasperprint.
JasperPrint printAll = new JasperPrint();
if(jPrint != null){
for(int i = 0; i < jPrint.getPages().size(); i++){
printAll.addPage((JRPrintPage)jPrint.getPages().get(i));
}
}

if(jp1 != null){
for(int j = 0; j < jp1.getPages().size(); j++){
printAll.addPage((JRPrintPage)jp1.getPages().get(j));
}
}

//  Setting the Width and Height For the table
printAll.setPageHeight(842);
printAll.setPageWidth(595);

SNAPSHOT

USED JARS

• 
  DynamicJasper-3.0.6.jar
  



• 
  jasperreports-2.0.2.jar
  



• 
  itext-1.3.jar
  



• 
  poi-3.0.1.jar
  

Website for Reference

• 
  http://sourceforge.net/projects/dynamicjasper/
  



• 
  http://dynamicjasper.sourceforge.net/docs/getting_started.html