Working with Jasper Reports - single and multiple excel sheets and PDF Format

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
   

How to create Auto Generated Transaction ID in java ( Unique key )

Note : This auto generated Transaction unique key is in this format : eg : ( DASARI_1212121212  )
Note : where DASARI IS PREFIX AND 1212121212 IS DateTime in Milliseconds.

Instance Variables :

   private static long m_nMaxTID = 1000;
    private static long m_nMinTID = 0;
    private static long m_iCount = 0;
    private static String m_strTmp = "000";
    private static int m_nTmpLen = 3;





public static synchronized String generateTID(String strPrefix) {
        strPrefix = strPrefix + "_" + (new java.util.Date()).getTime();
        System.out.println("");
        String strSuf = m_strTmp + m_iCount;
        m_iCount++;
        if (m_iCount >= m_nMaxTID) {
            m_iCount = m_nMinTID;
        }
        return strPrefix + strSuf.substring(strSuf.length() - m_nTmpLen);
    }

Note : simply call String transactionID=generateTID("DASARI");

Cheers
Dasari

How to Write and Call Store Procedures in java using MYSQL Database

Scenarios :

Note : for one table only..............

1 @ Scenario :  Updating Specific Rows based on Where Condition and several fields.

'update tablename(dasari) set columnname= ? where column_name in (?)  and column_name in (?) and column_name =curdate() and column_name is null  limit ? ';

2 @ Scenario :  For Retrieving all the Rows Based on Specific Column_name :

' select * from tablename(dasari)  where column_name=?';

Below is the Example Program  for Employee Information : Passing only IN Parameters

Step -1 : Create Table Name with following fields.

                CREATE TABLE `alerts` (      
                       `id` bigint(20) NOT NULL auto_increment,
                       `inserted_on` date default NULL,        
                       `msisdn` varchar(50) default NULL,      
                       `op_id` smallint(6) default NULL,        
                       `spid` int(11) default NULL,            
                       `flag` bigint(10) unsigned default NULL,
                       PRIMARY KEY  (`id`),                    
                       KEY `idx_insertedon` (`inserted_on`),    
                       KEY `idx_spid` (`op_id`)                
                     ) ENGINE=InnoDB DEFAULT CHARSET=latin1    

Step -2   Create a procedure


DELIMITER $$

DROP PROCEDURE IF EXISTS `databasename`.`dasari_procedure`$$

CREATE DEFINER=`username`@`localhost` PROCEDURE `dasari_procedure`(in strSpids varchar(512),in strOpid varchar(512),
 in nLimit int, in nFlag bigint)
BEGIN
  declare nLimit1 int(10);
  declare nFlag1 bigint;
  declare strOpid1 varchar(512);
  declare strSpids1 varchar(512);
  if (nLimit <= 0) then
  set @nLimit1 = 100;
  else
  set @nLimit1 = nLimit;
  end if;
  set @nFlag1 = nFlag;
  set @strOpid1 = strOpid;
  set @strSpids1 = strSpids;
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
  start transaction;
 
  prepare stmt from 'update alerts set flag = ? where op_id in (?) and spid in (?) and inserted_on = curdate() and flag is null limit ?';
  execute stmt using @nFlag1,@strOpid1,@strSpids1,@nLimit1;
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  deallocate prepare stmt;
  commit;
  prepare stmt from 'select * from alerts where flag = ?';
  execute stmt using @nFlag1;
  deallocate prepare stmt;
     END$$

DELIMITER ;


Step -3  Calling Stored Procdeure

   String sqlQuery ="{call dasari_procedure(?,?,?,?)}";
   CallableStatment cstmt=con.prepareCall(sqlQuery);
   cstmt.setString(1,1);
   cstmt.setString(1,1);
   cstmt.setInt(3,100);
   cstmt.setInt(4,12121212);
   cstmt.executeUpdate();  // flag will be update instead of null replace with 12121212 based on where flag is     null in stored procedure.

 // Retrieval of Resultset in same Stored Procedure.. after updating the records

   ResultSet rs=cstmt.getResultSet();

 while(rs.next())
{
   String name=rs.getString("column_name');
  -------------------------------------------  etc,,,,,,,,,

}

Happy Learning !!!

Cheers
Dasari

How to Create .bat file in java

@Title OBDMonitorDishnet [ %date% %time% ]

@set path=%path%;C:\Program Files\Java\jdk1.6.0_19;

@set classpath=%classpath%;C:\filename.jar;D:\filename.jar;E:\filename.jar;F:\filename.jar;

java -Duser.timezone="Asia/Calcutta" -Xmx1024M  [Your Class Name ] [C://filename.properties]

pause


#Step-1 : Provide Title as your wish eg: your application name. you can provide date and time also .
#Step-2 : set java path
#Step-3 : Here we will Store all our libaries under classpath
#Step-4 : This is final Step repalce [Your Class Name eg: DasariClass ] with your Application Class Name
#Step-5 : Save this Text as filename.bat and run ...enjoy
#Note: [c://filename.properties] u can pass your properties at command line level.

File Upload Workaround Solution in Java Web Application :

1) Example program for How to upload .csv file or .txt or .jpeg......etc.......




Here one thing we need to observee carefully , when we want to upload a specific file in web application.


 we should know about compatibility of browsers.


 1 ) IE 7 and IE 8 :
 Note : if we upload a file in web using below html tag
 syn : <input type="file" name="fileupload" accept=""/>


for example : the file location is at D:/Folder1/Folder2/filename.csv


so when u upload a file from this location , IE browser will take the path as : D:\Folder1\Folder2\filename.csv


observe carefully , if we test with the file upload functionality in  below  browsers


  2) firefox , opera , chrome , Netscape


  here , these browsers will take the path as     :  filename.csv


 these above browsers have not take  complete path when we upload a file from specific directory.




conclusion :
IE : will take full path eg: D:\folder1\folder2\filename.csv
firefox,chrome..etc : the take only specific file name eg : filename.csv


so,  if u are completely following IE compatibility web browser , i suggest everyone please


use java replace() method , for replacing the backword (\) slash with forward slash (/). otherwise java will not accept backword (\) charcter for reading the file .....


eg:       String filename="D:\filename.csv ": we cant read this file in java.


            correct way :      filename=filename.replace("\\","/");


          then the path will convert in to this format : D:/filename.csv , so we can easily read the file ..


for other browsers except IE,
                     we can  confiure the path in static way .


for example :       String dir="D:/";


                      path : "filename.csv";


                 String filename=dir+path;




Hope u may get some idea, how to make workaround solution for this..


it would  be very grateful for your feedback, if u find any errors........


u can test sample  example from below code snippet :

Step 1:  please paste this code in html or jsp ..
<form action="servletClassName" method=post/ enctype="multipart/form-data>

 <input type="hidden" name="_paramArgument"  vaule="fileupload"/>

 <input type="submit" value="upload" />  

 <input type="file"  name="_PARAM_FILEUPLOAD" id="sno" size="20"  accept="text/csv" />

</form>
Step 2: 

Business logic: Here u can use servlet or jsp ?copy the below code either in servlet or jsp.

  public class UploadServlet extends HttpServlet

   { 

     String param=request.gerParameter("_paramArgument");

        dospost(){  //dopost()method of servlet 

            try{

                   if(param.equals("fileupload"))

{

String data=request.getParameter("_PARAM_FILEUPLOAD");  

 data = data.replace("\\", "/");

if (data.endsWith(".csv") && data.contains("/")) {

File f = new File(data);

                      

    boolean path = f.exists();  // if path exits then true otherwise false

                        if(path)

                       { 

   //your Database logic to insert the file.csv in to database.....upload success 

  uploadCSVFile(data);  // this method will insert the csv data in to database....

                        }

else {invalid upload}

    }

                                            

            }

catch(Exception e)

{}

}}    

step 3:

       

    DAO Logic :

      public int uploadCSVFile(String path) {

      

      int status = 0;

    

         try {

            String query = "LOAD DATA INFILE '" + path + "' IGNORE INTO    TABLE tbl_voicetocrbt_content FIELDS  TERMINATED BY   

          ',' LINES TERMINATED BY '\n'";

            PreparedStatement pst = getJDBCConnection().prepareStatement(query);

           status= pst.executeUpdate();

            

          } catch (Exception e) {

            e.printStackTrace();

          }

         return status;

        }

 :

Cheers and Fun
Dasari...