Hi Friends !
Requirement :
1) How to export data in to excel using jasper reports in java
2) How to create multiple sheets in one excel report in java using jasper reports
3) How to export data in to pdf using jasper reports in java.
Note : Required Libraries
commons-beanutils-1.5.jar
commons-collections-2.1.jar
commons-digester-1.7.jar
commons-logging-1.0.2.jar
itext-1.3.1.jar
jasperreports-3.0.0.jar
mysql-connector-java-5.0.4-bin.jar
poi-3.0.1-FINAL-20070705.jar
Note : Before Working with reports , it is mandatory that we need to design the report using tool called
I-Report in our customized format.
1) With this I-Report tool we can generate a report with extension ( .jrxml )
Note :
( a ) How to compile .jrxml file and convert to .jasper
Note : with the help of .jasper file extension, we can pass this file in our program to generate the reports.
might be you will confuse little bit , by looking at the below example everything will be clear.
Example 1 : Exporting data in to Single Excel Sheet
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
public class SingleExcelSheet {
private static Connection con=null;
private static Statement stmt=null;
private static ResultSet rs=null;
public static void main(String[]a)
{
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/DBName","username","pwd");
if(con!=null)
{
System.out.println("connection successful.."+con);
}
String query="select name as person_name,street as person_street,designation as person_designation from person";
stmt = con.createStatement();
rs = stmt.executeQuery(query);
JRResultSetDataSource datasource= new JRResultSetDataSource(rs);
/** Note : .jasper file should be specified in some directory for eg: c:\\abc.jasper or d:\\xyz.jasper
JasperPrint jp = JasperFillManager.fillReport("c:\\filename.jasper", new HashMap(), datasource);
/** Another way : we can provide .jasper file in the root directory of our java application
/** for eg: JaseperReportApplication : we can copy .jasper file in this application root
//JasperPrint jp1 = JasperFillManager.fillReport("filename.jasper", new HashMap(), datasource);
//** Code snippet for Exporting single sheet in Excel
JRXlsExporter exporter = new JRXlsExporter();
exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,Boolean.TRUE);
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jp); //Note : jp is JasperPrint Object
/**Note : provide the path where the file should be generated
exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,"c://FolderName//Filename.xlsx");
Date d=new Date();
String s="summary"; //Note : by using this date and string we can display sheet name in our customized format
exporter.setParameter(JRXlsExporterParameter.SHEET_NAMES, new String[]{s+d.toLocaleString()});
exporter.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS,Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.OFFSET_X, 0);
exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, Boolean.FALSE);
exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, true);
exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, false);
exporter.exportReport();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Example 2 : Exporting data in to PDF
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperExportManager;
public class SingleExcelSheet {
private static Connection con=null;
private static Statement stmt=null;
private static ResultSet rs=null;
public static void main(String[]a)
{
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sdmg","sdmg","sdmg");
if(con!=null)
{
System.out.println("connection successful.."+con);
}
String query="select name as person_name,street as person_street,designation as person_designation from person";
stmt = con.createStatement();
rs = stmt.executeQuery(query);
JRResultSetDataSource datasource= new JRResultSetDataSource(rs);
/** Note : .jasper file should be specified in some directory for eg: c:\\abc.jasper or d:\\xyz.jasper
JasperPrint jp = JasperFillManager.fillReport("c:\\filename.jasper", new HashMap(), datasource);
JasperExportManager.exportReportToPdfFile(jp1,"c:/output/parameterReport.pdf");
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Example 3 : Exporting Multiple sheets in to the Excel
package in.dasari;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JRPrintPage;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
public class ExportDemoExcel {
/**
* @param args
*/
public static void main(String[] args)throws Exception {
// TODO Auto-generated method stub
Connection con = null;
Statement stmt = null;
Statement stmt1 = null;
Statement stmt2 = null;
Statement stmt3=null;
ResultSet rset = null;
ResultSet rset1 = null;
ResultSet rset2 = null;
ResultSet rset3 = null;
try {
//** Using DataSource */
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","admin");
if(con!=null)
{
System.out.println("connection successful.."+con);
}
String queryString = "select personName, country, state, city "
+ "FROM person ";
stmt = con.createStatement();
rset = stmt.executeQuery(queryString);
String queryString1 = "select personName, country, state, city "
+ "FROM person ";
stmt1 = con.createStatement();
rset1 = stmt1.executeQuery(queryString1);
String queryString2 = "select personName, country, state, city "
+ "FROM person ";
stmt2 = con.createStatement();
rset2 = stmt2.executeQuery(queryString2);
String queryString3="select stockroom_id,stockroom_name,stockroom_desc from stockroom";
stmt3 = con.createStatement();
rset3 = stmt3.executeQuery(queryString3);
JRResultSetDataSource jasperReports1 = new JRResultSetDataSource(rset);
JRResultSetDataSource jasperReports2 = new JRResultSetDataSource(rset1);
JRResultSetDataSource jasperReports3 = new JRResultSetDataSource(rset2);
JRResultSetDataSource jasperReports4 = new JRResultSetDataSource(rset3);
JasperPrint jp1 = JasperFillManager.fillReport("c:\\First.jasper", new HashMap(), jasperReports1);
JasperPrint jp2 = JasperFillManager.fillReport("c:\\Second.jasper", new HashMap(), jasperReports2);
JasperPrint jp3 = JasperFillManager.fillReport("c:\\Third.jasper", new HashMap(), jasperReports3);
JasperPrint jp4 = JasperFillManager.fillReport("c:\\Stock.jasper", new HashMap(), jasperReports4);
List<JRPrintPage> pages2 = new ArrayList<JRPrintPage>(jp4.getPages());
int j=1;
for(int count=0;count<pages2.size();count++){
jp1.addPage(j, (JRPrintPage)pages2.get(count));
j++;
}
List<JRPrintPage> pages1 = new ArrayList<JRPrintPage>(jp3.getPages());
int k=1;
for(int count=0;count<pages1.size();count++){
jp1.addPage(k, (JRPrintPage)pages1.get(count));
k++;
}
List<JRPrintPage> pages = new ArrayList<JRPrintPage>(jp2.getPages());
int i=1;
for(int count=0;count<pages.size();count++){
jp1.addPage(i, (JRPrintPage)pages.get(count));
i++;
}
JRXlsExporter exporter = new JRXlsExporter();
exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,Boolean.TRUE);
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jp1);
exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,"c://output//demo1.xls");
exporter.setParameter(JRXlsExporterParameter.SHEET_NAMES, new String[]{"Personal Information", "Skills", "Testing","Stocks" });
exporter.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS,Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.OFFSET_X, 0);
exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, Boolean.FALSE);
exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, true);
exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, false);
exporter.exportReport();
} catch (JRException e) {
StringWriter stringWriter = new StringWriter();
PrintWriter printWriter = new PrintWriter(stringWriter);
System.out.println(printWriter);
e.printStackTrace();
}
}
}
Example : 4 compiling .jrmxl and converting in to .jasper
import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.HashMap;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
public class CompileReport {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
String reportFile1 = "c:\\First.jrxml";
try {
System.out.println("In Try block compiling .... !!");
JasperCompileManager.compileReportToFile(reportFile1);
System.out.println(".jasper created..");
}
catch (JRException e) {
System.out.println("got exception .... !!");
e.printStackTrace();
}
System.out.println("Output Generated !!");
}
}
Cheers
Dasari
Requirement :
1) How to export data in to excel using jasper reports in java
2) How to create multiple sheets in one excel report in java using jasper reports
3) How to export data in to pdf using jasper reports in java.
Note : Required Libraries
commons-beanutils-1.5.jar
commons-collections-2.1.jar
commons-digester-1.7.jar
commons-logging-1.0.2.jar
itext-1.3.1.jar
jasperreports-3.0.0.jar
mysql-connector-java-5.0.4-bin.jar
poi-3.0.1-FINAL-20070705.jar
Note : Before Working with reports , it is mandatory that we need to design the report using tool called
I-Report in our customized format.
1) With this I-Report tool we can generate a report with extension ( .jrxml )
Note :
( a ) How to compile .jrxml file and convert to .jasper
Note : with the help of .jasper file extension, we can pass this file in our program to generate the reports.
might be you will confuse little bit , by looking at the below example everything will be clear.
Example 1 : Exporting data in to Single Excel Sheet
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
public class SingleExcelSheet {
private static Connection con=null;
private static Statement stmt=null;
private static ResultSet rs=null;
public static void main(String[]a)
{
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/DBName","username","pwd");
if(con!=null)
{
System.out.println("connection successful.."+con);
}
String query="select name as person_name,street as person_street,designation as person_designation from person";
stmt = con.createStatement();
rs = stmt.executeQuery(query);
JRResultSetDataSource datasource= new JRResultSetDataSource(rs);
/** Note : .jasper file should be specified in some directory for eg: c:\\abc.jasper or d:\\xyz.jasper
JasperPrint jp = JasperFillManager.fillReport("c:\\filename.jasper", new HashMap(), datasource);
/** Another way : we can provide .jasper file in the root directory of our java application
/** for eg: JaseperReportApplication : we can copy .jasper file in this application root
//JasperPrint jp1 = JasperFillManager.fillReport("filename.jasper", new HashMap(), datasource);
//** Code snippet for Exporting single sheet in Excel
JRXlsExporter exporter = new JRXlsExporter();
exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,Boolean.TRUE);
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jp); //Note : jp is JasperPrint Object
/**Note : provide the path where the file should be generated
exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,"c://FolderName//Filename.xlsx");
Date d=new Date();
String s="summary"; //Note : by using this date and string we can display sheet name in our customized format
exporter.setParameter(JRXlsExporterParameter.SHEET_NAMES, new String[]{s+d.toLocaleString()});
exporter.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS,Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.OFFSET_X, 0);
exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, Boolean.FALSE);
exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, true);
exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, false);
exporter.exportReport();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Example 2 : Exporting data in to PDF
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperExportManager;
public class SingleExcelSheet {
private static Connection con=null;
private static Statement stmt=null;
private static ResultSet rs=null;
public static void main(String[]a)
{
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sdmg","sdmg","sdmg");
if(con!=null)
{
System.out.println("connection successful.."+con);
}
String query="select name as person_name,street as person_street,designation as person_designation from person";
stmt = con.createStatement();
rs = stmt.executeQuery(query);
JRResultSetDataSource datasource= new JRResultSetDataSource(rs);
/** Note : .jasper file should be specified in some directory for eg: c:\\abc.jasper or d:\\xyz.jasper
JasperPrint jp = JasperFillManager.fillReport("c:\\filename.jasper", new HashMap(), datasource);
JasperExportManager.exportReportToPdfFile(jp1,"c:/output/parameterReport.pdf");
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Example 3 : Exporting Multiple sheets in to the Excel
package in.dasari;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JRPrintPage;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
public class ExportDemoExcel {
/**
* @param args
*/
public static void main(String[] args)throws Exception {
// TODO Auto-generated method stub
Connection con = null;
Statement stmt = null;
Statement stmt1 = null;
Statement stmt2 = null;
Statement stmt3=null;
ResultSet rset = null;
ResultSet rset1 = null;
ResultSet rset2 = null;
ResultSet rset3 = null;
try {
//** Using DataSource */
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","admin");
if(con!=null)
{
System.out.println("connection successful.."+con);
}
String queryString = "select personName, country, state, city "
+ "FROM person ";
stmt = con.createStatement();
rset = stmt.executeQuery(queryString);
String queryString1 = "select personName, country, state, city "
+ "FROM person ";
stmt1 = con.createStatement();
rset1 = stmt1.executeQuery(queryString1);
String queryString2 = "select personName, country, state, city "
+ "FROM person ";
stmt2 = con.createStatement();
rset2 = stmt2.executeQuery(queryString2);
String queryString3="select stockroom_id,stockroom_name,stockroom_desc from stockroom";
stmt3 = con.createStatement();
rset3 = stmt3.executeQuery(queryString3);
JRResultSetDataSource jasperReports1 = new JRResultSetDataSource(rset);
JRResultSetDataSource jasperReports2 = new JRResultSetDataSource(rset1);
JRResultSetDataSource jasperReports3 = new JRResultSetDataSource(rset2);
JRResultSetDataSource jasperReports4 = new JRResultSetDataSource(rset3);
JasperPrint jp1 = JasperFillManager.fillReport("c:\\First.jasper", new HashMap(), jasperReports1);
JasperPrint jp2 = JasperFillManager.fillReport("c:\\Second.jasper", new HashMap(), jasperReports2);
JasperPrint jp3 = JasperFillManager.fillReport("c:\\Third.jasper", new HashMap(), jasperReports3);
JasperPrint jp4 = JasperFillManager.fillReport("c:\\Stock.jasper", new HashMap(), jasperReports4);
List<JRPrintPage> pages2 = new ArrayList<JRPrintPage>(jp4.getPages());
int j=1;
for(int count=0;count<pages2.size();count++){
jp1.addPage(j, (JRPrintPage)pages2.get(count));
j++;
}
List<JRPrintPage> pages1 = new ArrayList<JRPrintPage>(jp3.getPages());
int k=1;
for(int count=0;count<pages1.size();count++){
jp1.addPage(k, (JRPrintPage)pages1.get(count));
k++;
}
List<JRPrintPage> pages = new ArrayList<JRPrintPage>(jp2.getPages());
int i=1;
for(int count=0;count<pages.size();count++){
jp1.addPage(i, (JRPrintPage)pages.get(count));
i++;
}
JRXlsExporter exporter = new JRXlsExporter();
exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,Boolean.TRUE);
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jp1);
exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,"c://output//demo1.xls");
exporter.setParameter(JRXlsExporterParameter.SHEET_NAMES, new String[]{"Personal Information", "Skills", "Testing","Stocks" });
exporter.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS,Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.OFFSET_X, 0);
exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, Boolean.FALSE);
exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, true);
exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, false);
exporter.exportReport();
} catch (JRException e) {
StringWriter stringWriter = new StringWriter();
PrintWriter printWriter = new PrintWriter(stringWriter);
System.out.println(printWriter);
e.printStackTrace();
}
}
}
Example : 4 compiling .jrmxl and converting in to .jasper
import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.HashMap;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
public class CompileReport {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
String reportFile1 = "c:\\First.jrxml";
try {
System.out.println("In Try block compiling .... !!");
JasperCompileManager.compileReportToFile(reportFile1);
System.out.println(".jasper created..");
}
catch (JRException e) {
System.out.println("got exception .... !!");
e.printStackTrace();
}
System.out.println("Output Generated !!");
}
}
Cheers
Dasari
No comments:
Post a Comment