im no getting

**dupra** · February 6th, 2010, 01:44 AM

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>

**jassi** · February 6th, 2010, 07:37 AM

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.

**dupra** · February 6th, 2010, 07:55 AM

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

**jassi** · February 6th, 2010, 07:59 AM

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.

**dupra** · February 6th, 2010, 08:05 AM

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

**dupra** · February 7th, 2010, 02:10 AM

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

**jassi** · February 8th, 2010, 12:47 AM

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

**dupra** · February 8th, 2010, 01:18 AM

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.

**dupra** · February 8th, 2010, 01:22 AM

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.

**jassi** · February 8th, 2010, 01:23 AM

ok..give it then

**jassi** · February 8th, 2010, 01:23 AM

yeah i got

**jassi** · February 8th, 2010, 02:20 AM

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

Thread: im no getting

LinkBack

Thread Tools

Display

im no getting

Re: im no getting

Re: im not getting data in all sheets

Re: im no getting

Re: im no getting

im waitng for ur reply

Re: im no getting

Re: im not getting

Re: im no getting

Re: im no getting

Re: im no getting

Re: im no getting