wood burning stoves*
The moose likes Servlets and the fly likes Displaying the query results in Excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Displaying the query results in Excel" Watch "Displaying the query results in Excel" New topic
Author

Displaying the query results in Excel

aliya sharma
Ranch Hand

Joined: Aug 11, 2004
Posts: 48
I have a jsp page where user enters some parameter and then get the results. Now, i want to give him a choice of viewing this in MS-Excel.So, My controller would recieve the request, query the database and then send the results to MS-Excel. I know how to do the query but have no idea how i should send the results to excel. I know how to send the regular data to excel like:



Could anyone please guide me how to get the results from the database to show in Excel program. I would really appreciate it. Thanks a lot!

Aliya
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30123
    
150

Aliya,
To access the database use JDBC.

If you need to display anything more complex in Excel, you can use HSSF/POI an excellent framework for creating spreadsheets.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Aliya,

SInce you know how to query the database, as well as you can display excel with hardcoded values

then there should be no problem in displaying excel content.

I recommend you to use DTO's in your application is not using I am modifying your
code assuming same values are in database.


--------------------------------------------
Create DTO Class
--------------------------------------------
public class myExcelDTO Implements Serializable{

private String q1="";
private String q2="";
private String q3="";
private String q4="";
private String total="";


public String getQ1()
{
retrun this.q1;
}

public void setQ1(String q1)
{
this.q1 = q1;
}
.
.
.
...so on .......

}



-------------------------------------------------------------
At database end
-------------------------------------------------------------
Excecute your query get the resultset (rs) then

myExcelDTO myDTO = null;
ArrayList retList= new ArrayList();

while(rs.next())
{
myDTO = new myExcelDTO();

myDTO.setQ1(rs.getString("q1"));
myDTO.setQ2(rs.getString("q2"));
.
.
so on

retList.add(myDTO);
}


return retList;



--------------------------------------------------------------
At JSP
--------------------------------------------------------------


resp.setContentType("application/vnd.ms-excel");
PrintWriter out = resp.getWriter();

//-- call your method and get the ArrayList retList and then


out.println("\tQ1\tQ2\tQ3\tQ4\tTotal");
for(int i=0; i < retList.size();i++)
{
myExcelDTO jspDTO = (myExcelDTO) retList.get(i);
out.println(jspDTO.getq1()+ "\t" +jspDTO.getq2()+"\t"+jspDTO.getq3()+"\t"+jspDTO.getq4()+"\t"+jspDTO.getTotal());

}
[ October 16, 2004: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
aliya sharma
Ranch Hand

Joined: Aug 11, 2004
Posts: 48
Thanks a lot Shailesh. I tried it and worked like a charm!..Again, i really appreicate it.

Aliya
 
wood burning stoves
 
subject: Displaying the query results in Excel
 
Similar Threads
Create Excel Speedsheet on UNIX
write and read Excel File
JSP/Servlet With MS Excel?
JSP to Excel - beginner
Capturing result of SUM query