Exporting results into excel using JSP

Hi
currently, the webpage returns all the results against my SQL query into a single page.Now I would like to export/download  these results into an excel sheet.Can you please give me some code reference of how to do it in JSP.
kalyanmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sbrown2kCommented:
Yes.
0
sbrown2kCommented:
:)

Use POI.

OS, available at http://jakarta.apache.org/poi/.

0
sbrown2kCommented:
Specifically, use the HSSF portion of POI.  Its a java library for reading/writing MS Excel files.

http://jakarta.apache.org/poi/hssf/index.html

If I gave you a sample it would just be to copy code from:
http://jakarta.apache.org/poi/hssf/how-to.html

You would probably want to create a wrapper class that takes your jdbc record set
and builds the excel file.  Make sure you can pass WHERE you want the file, and
make sure that whereever you create the file you have mapped within your webserver
(tomcat/apache most likely?).  You may also want to setup some sort of daemon to
clean out the cache directory, since you actually need to generate the file to disk and
then redirect the webbrowser to that disk.

If you are doing CHARTS, i would suggest creating the chart in excel before hand
and creating a second worksheet you can fill out from java to populate the chart.

I hope this is enough to get you started, if you have specific questions I will do my
best to answer them.  

- sbrown2k
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kalyanmAuthor Commented:
Hi
Thanks very much for solution.unfortunately it look bit complicated from my point of view.I will explain you the scenario.perhaps you can help me better!!

I have a page which just fetches results from database and displays them in a JSP page.Now I would like to give the users and option to view/download these results into an excel sheet.Fo this I would like to provide a link called 'download to excel' in the same page clicking on which we should get an option to either save or view these results in excel.Can you please let me know if we can write a code within JSP page (as this is the requirement).

Thanks
0
sbrown2kCommented:
Well, I dont know of any way to create an in-memory copy of an excel file and stream it to your client easily.
An alternate solution would be to create a comma delimited file (.csv) and send that.  It can excel can open it and display it as a worksheet. Given that its raw text it should be easy to create from jsp.  Creating an excel file you need to use a library as its a binary format that is too complicated to just write from jsp (POI is the "right" solution, IMHO for creating an excel file, but inorder to get it to the client you have to cache it to disk). The .csv is easier.

- sbrown2k
0
kalyanmAuthor Commented:
Sbrown
Thanks for all your efforts but I was looking at some sample code which can help me download the data displayed into an excel sheet.Can you please let me kow how to download  it into a csv(code prefereable)
 .I have tried contentType="application/vnd.ms-excel"  but it doesn't work.
0
lilian-arnaudCommented:
Excel converts automaticly html pages into Excel documents.

You just need to change mime type of your response :

<%@ page language="java"
                 session="false"
                 contentType="application/excel"
%>

or you can try to set directly in your JSP (warning, you cannot set the contentType in page directive and in your code at the same time) :

<%
response.setContentType("application/excel");
%>

But it's better to set the content type in your servlet (or Struts action) before the display of your JSP.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kalyanmAuthor Commented:
Hi All
Thanx for your suggestions.i could get the answer after some research!!...
I would suggest this can be query can be closed now...
Thanks once again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.