im no getting

Printable View

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....

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>

February 6th, 2010, 06:37 AM
jassi

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.

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
Code :

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

February 6th, 2010, 06:59 AM
jassi

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.

Re: im no getting

thanks for replying me here is the total code..please solve it

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>
 
<%
 
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....

February 7th, 2010, 01:10 AM
dupra

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
February 7th, 2010, 11:47 PM
jassi

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
February 8th, 2010, 12:18 AM
dupra

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.
February 8th, 2010, 12:22 AM
dupra

1 Attachment(s)

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.
February 8th, 2010, 12:23 AM
jassi

Re: im no getting

ok..give it then
February 8th, 2010, 12:23 AM
jassi

Re: im no getting

yeah i got
February 8th, 2010, 01:20 AM
jassi

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