aspose file tools*
The moose likes Java in General and the fly likes Generate CSV from Resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Generate CSV from Resultset" Watch "Generate CSV from Resultset" New topic
Author

Generate CSV from Resultset

Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi All.
I want to generate reports. I am using struts. I want that I store result in CSV file and return url of csv so that when user clicks on url if he has excel report will open in excel otherwise in notepad. I dont want to generate report in jsp. So when user selects criteria and clicks on submit it will return url of csv and user can click on that url to open report in excel. I have resultset with me.
Can anyone guide me please

Thanks
Lalit
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Hi, what part has you stuck? Query the database, generate CSV, save CSV someplace user can get it later, other?

You can work through any resultset pretty easily. I made a "reader" with logic like this. ResultSetHandler is an interface that I made up ...

I could implement ResultSetHandler to write CSV to a stream and write to a file or a ByteArray or whatever.

BTW: I actually call the ResultSetHandler with the metadata for each column before the loop shown above. The handler can capture datatypes and sizes and such from the metadata, set up headers and prepare formatters for each column.

Any of that answer the right question?


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi James, I am not getting exactly what you are trying to suggest. I have resultset with me through query. Now i want that when user selects criteria from index.jsp like reportname and date and clicks on submit button. It should return URL like report1.csv and when user click on it it should open in excel or notepad. Now i am not getting what to do once i have result set from database. I am developing struts application. I am looking for that code. After i have result in resultset once i fire query how to return this URL and how to write result in result set

If you can guide me i will be really thankful. I am not moving forward once i have data in resultset.

Thanks again

Lalit
karthikeyan Chockalingam
Ranch Hand

Joined: Sep 06, 2003
Posts: 259
1. After getting the result set write the contents into CSV file. The location of the CSV file on the server shud be identified using a certain logic ..say
/your_app_context_root/csv/DDMMYY.csv
2. Set the location of the report in HttpServletRequest using setAttribute method.
3. In the JSP using struts tag or using scriplet display the url using <a href="/your_app_context_root/csv/DDMMYY.csv">Report </a> (the location was stored in step 2)
4. When the user clicks on the link, the browser will download the file and provide the user with the options "save" or "Open".
[ July 06, 2006: Message edited by: karthi keyan ]

http://www.skillassert.com


Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42598
    
  65
For generating CSV files you could use the Ostermiller CSV Utils.


Ping & DNS - my free Android networking tools app
Sean Sullivan
Ranch Hand

Joined: Sep 09, 2001
Posts: 427
http://opencsv.sourceforge.net/

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Generate CSV from Resultset