Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 12 of 12

Thread: im no getting

  1. #1
    Junior Member
    Join Date
    Feb 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default im no getting

    i wrote a program to retrieve data from DB to many excel sheets.but it shows one sheet only remaining excel sheets r created but it doent show..but db retrieved

    im getting db from different servers..
    im using jsp and mysql..HSSWorkBook

    can any one help me its very urgent..

    dis is my code....

    <%@ page import="java.util.*" %>
    <%@ page import="java.sql.*" %>
    <%@ page import="java.text.*" %>
    <%@ page import="java.io.*"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
    <%@ page import="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
    <html>
    <body>
    <table border=1>
    <tr><td>sno</td><td>mobileno</td><td>digit entered</td><td>time of call</td><td>app name</td></tr>
     
    <%
     
    try
    {
    	ResultSet rs2=null,rs1=null;
    	Connection con;
    	Statement stmt,st;
    	Format  f,f1;
    	String s="";
    	String ckt[]={"ahm","jai","ban","chen","del","ludh","lnow","gwa","del2","kol","ran"};
    	int i=0;int p=0,in=3,hy=0;									
    	java.util.Date date = new java.util.Date();
    	String d=(date.getDate())+"-"+(date.getMonth()+1)+"-"+(date.getYear()+1900);
    	out.println(d);
    	f = new SimpleDateFormat("d-M-yyyy");
    				HSSFWorkbook hyd=new HSSFWorkbook();
    				Class.forName("org.gjt.mm.mysql.Driver");
    			String ip[]={" urls here"};
     
    	for(int ii=0;ii<10;ii++)
    		{
     
    								HSSFSheet sd=hyd.createSheet(ckt[ii]);
    								HSSFRow hr =sd.createRow((short)2);
    								hr.createCell((short) 0).setCellValue("SNo");
    								hr.createCell((short) 1).setCellValue("MobileNo");
    								hr.createCell((short) 2).setCellValue("DigitEntered");
    								hr.createCell((short) 3).setCellValue("Time Of Call");
    								hr.createCell((short) 4).setCellValue("Appname");
     
    					String url="jdbc:mysql://"+ip[ii]+"/vas1";
     
    				    con=DriverManager.getConnection(url,"telecanor","outbound");
     
    				    st=con.createStatement();
     
    					stmt=con.createStatement();
    					rs1=stmt.executeQuery("select distinct toCall,rtime,datagot,xml  from obcallss where datagot<>'' order by rtime");
     
     
    		while(rs1.next())
    		{
     
    	   	                                long ss=rs1.getLong(2);
    										java.util.Date dat = new java.util.Date(ss);
    										s=f.format(dat);
    										f1 = new SimpleDateFormat("dd-M-yyyy hh:mm::ss");
     
     
    		  if(s.equals(d))
    			{						
    									HSSFRow row =sd.createRow((short)in);
    									row.createCell((short) 0).setCellValue(hy++);
    									row.createCell((short) 1).setCellValue(rs1.getString(1));
    									row.createCell((short) 2).setCellValue(sd1);
    									row.createCell((short) 3).setCellValue(rs1.getString(3));
    									row.createCell((short)4).setCellValue(rs1.getString(4));					
     
     
    		}
    in++;
     
    		}
     
     
    FileOutputStream fileOut = new FileOutputStream("d:\\prasad\\rep.xls");
     							hyd.write(fileOut);
    							fileOut.close();
    	rs1.close();
    con.close();						
    	}
    	out.println("report generated...");
    }
    catch(Exception e)
    {
    out.println(e);
    }
    %>
    </table>
    </body>
    </html>
    Last edited by helloworld922; February 7th, 2010 at 07:57 PM. Reason: Please use [code] tags!

  2. #2
    Member
    Join Date
    Feb 2010
    Location
    Dehradun, India
    Posts
    37
    Thanks
    1
    Thanked 7 Times in 6 Posts

    Default Re: im no getting

    Hi dear..

    You are creating different sheets from different IPs. As i can see your code..

    I debugged your code and created one sheet. I can access database and can create excel sheet from it.

    Please make it clear how you want it to create your sheets.

  3. #3
    Junior Member
    Join Date
    Feb 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Re: im not getting data in all sheets

    im happy to reply me.
    my problem is to generate automatic report of different states...
    every time i execute my code one rep.xls file is generated in a specified path...with 10 sheets.
    i got data in one sheet (state) only ...remaining sheets r created but data is not displayed..
    i have a problem in these area


    code is
    HSSFSheet sd=hyd.createSheet(ckt[ii]);//generate excel sheet every time loop executed
     
     
     
     
                                                                           HSSFRow row =sd.createRow((short)in);
    									row.createCell((short) 0).setCellValue(hy++);
    									row.createCell((short) 1).setCellValue(rs1.getString(1));
    									row.createCell((short) 2).setCellValue(sd1);
    									row.createCell((short) 3).setCellValue(rs1.getString(3));
    									row.createCell((short) 4).setCellValue(rs1.getString(4));
    with regards
    durgaprasad
    Last edited by helloworld922; February 7th, 2010 at 07:58 PM. Reason: Please use [code] tags!

  4. #4
    Member
    Join Date
    Feb 2010
    Location
    Dehradun, India
    Posts
    37
    Thanks
    1
    Thanked 7 Times in 6 Posts

    Default Re: im no getting

    Hi durgaprasad..

    One thing i debugged is that there is no "sd1" object in there. There is one sd object which is used but it is not declared any where. Please check it.

  5. #5
    Junior Member
    Join Date
    Feb 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: im no getting

    thanks for replying me here is the total code..please solve it
    <%@ page import="java.util.*" %>
    <%@ page import="java.sql.*" %>
    <%@ page import="java.text.*" %>
    <%@ page import="java.io.*"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
    <%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
    <%@ page import="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
    <html>
    <body>
     
    <%
     
    try
    {
    	ResultSet rs2=null,rs1=null;
    	Connection con;
    	Statement stmt,st;
    	Format  f,f1;
    	String s="";
    	String ckt[]={"ahm","jai","ban","chen","del","ludh","lnow","gwa","del2","kol","ran"};
    	int i=0;int p=0,in=3,hy=0;									
    	java.util.Date date = new java.util.Date();
    	String d=(date.getDate())+"-"+(date.getMonth()+1)+"-"+(date.getYear()+1900);
    	out.println(d);
    	f = new SimpleDateFormat("d-M-yyyy");
    				HSSFWorkbook hyd=new HSSFWorkbook();
    				Class.forName("org.gjt.mm.mysql.Driver");
    			String ip[]={"urls here"};
     
    	for(int ii=0;ii<10;ii++)
    		{
     
    							HSSFSheet sd=hyd.createSheet(ckt[ii]);
    								HSSFRow hr =sd.createRow((short)2);
    //header of every excel sheet					
    			hr.createCell((short) 0).setCellValue("SNo");
    								hr.createCell((short) 1).setCellValue("MobileNo");
    								hr.createCell((short) 2).setCellValue("DigitEntered");
    								hr.createCell((short) 3).setCellValue("Time Of Call");
    								hr.createCell((short) 4).setCellValue("Appname");
     
    					String url="jdbc:mysql://"+ip[ii]+"/vas1";
     
    				    con=DriverManager.getConnection(url,"telecanor","outbound");
    				    st=con.createStatement();
     
    					stmt=con.createStatement();
    					rs1=stmt.executeQuery("select distinct toCall,rtime,datagot,xml  from obcallss where datagot<>'' order by rtime");
     
     
     
    		while(rs1.next())
    		{
     
    	   	                                long ss=rs1.getLong(2);
    										java.util.Date dat = new java.util.Date(ss);
    										s=f.format(dat);
    										f1 = new SimpleDateFormat("dd-M-yyyy hh:mm::ss");
    				String sd1=f1.format(dat),mno=rs1.getString(1),de=rs1.getString(3),apname=rs1.getString(4);
     
    		  if(s.equals(d))
    			{						
    									HSSFRow row =sd.createRow((short)in);
    									row.createCell((short) 0).setCellValue(hy++);
    									row.createCell((short) 1).setCellValue(rs1.getString(1));
    									row.createCell((short) 2).setCellValue(sd1);
    									row.createCell((short) 3).setCellValue(rs1.getString(3));
    									row.createCell((short) 4).setCellValue(rs1.getString(4));					
     
    		}
    in++;
     
    		}
     
     
    FileOutputStream fileOut = new FileOutputStream("d:\\prasad\\rep.xls");
     							hyd.write(fileOut);
    							fileOut.close();
    	rs1.close();
    con.close();						
    	}
    	out.println("report generated...");
    }
    catch(Exception e)
    {
    out.println(e);
    }
    %>
    </table>
    </body>
    finally im getting a rep.xls file with one excel sheet contains data remaing all r nothing but i got header mentioned above....
    Last edited by helloworld922; February 7th, 2010 at 07:58 PM. Reason: please use [code] tags!

  6. #6
    Junior Member
    Join Date
    Feb 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default im waitng for ur reply

    its very urgent..
    one more question is how to execute a program automatically..
    i want a progrm to send a mail after 8pm .with attachment. i know how to do but the problem is how to execute my program after 8'0 clock....
    with regards
    durga prasad

  7. #7
    Member
    Join Date
    Feb 2010
    Location
    Dehradun, India
    Posts
    37
    Thanks
    1
    Thanked 7 Times in 6 Posts

    Default Re: im no getting

    Hi durga prasad..Please give the ips which you r using as I am a little busy today..So I wont be able to write everything. I can just debug it.

    You also give the .sql script and also some data to insert in it..It will make work faster

    Thanks and Regards,

    Jassi

  8. #8
    Junior Member
    Join Date
    Feb 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: im not getting

    ok
    But i'll give data of one circle only in the format of text file..i dont give ips of circles its a security issue...

    i want a solution to print data on all circles in different....excel sheets..
    im getting data from db from all circles ,but i cant display it on all sheets....

    note:your changing text file into sql file.

  9. #9
    Junior Member
    Join Date
    Feb 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: im no getting

    ok
    But i'll give data of one circle only in the format of text file..i dont give ips of circles its a security issue...

    i want a solution to print data on all circles in different....excel sheets..
    im getting data from db from all circles ,but i cant display it on all sheets....

    note:your changing text file into sql file.
    Attached Files Attached Files

  10. #10
    Member
    Join Date
    Feb 2010
    Location
    Dehradun, India
    Posts
    37
    Thanks
    1
    Thanked 7 Times in 6 Posts

    Default Re: im no getting

    ok..give it then

  11. #11
    Member
    Join Date
    Feb 2010
    Location
    Dehradun, India
    Posts
    37
    Thanks
    1
    Thanked 7 Times in 6 Posts

    Default Re: im no getting

    yeah i got

  12. #12
    Member
    Join Date
    Feb 2010
    Location
    Dehradun, India
    Posts
    37
    Thanks
    1
    Thanked 7 Times in 6 Posts

    Default Re: im no getting

    Hi Durga Prasad.

    Your problem is solved.

    The bug i found in your code is that please make "in=3 " in your for loop again.

    Actually what was happenning is that the data is being written in your xls sheets but it is a bit downwards. as the "in"variable was incrementing and some rows is being written in first sheet. Then "in" variable is not initialized again and the data is written from the current value of "in".

    Make your changes and let me know wether it is working now.

    Enjoy....

    Thanks and Regards,
    Jassi and Shrikant