• Post Reply Bookmark Topic Watch Topic
  • New Topic

Export ResultSet to Excel  RSS feed

 
Chris Stewart
Ranch Hand
Posts: 184
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm displaying a resultset in a webpage for users. I'd like to offer a link where they can download/export this resultset shown on the screen to Excel (new document). Has anyone ever gone through this? Thanks for any replies.
 
Maulin Vasavada
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi James,
do u want something like "Printer Friendly Version"? OR u r expecting some processing on the data once u export to excel?
i have observed that if we have HTML Tablular display then we can select all the text and paste it in excel. excel puts data automatically in columns....
regards,
maulin
 
Philip Shanks
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jim,
I have done what you describe. While I do not know of a Java class that allows you to construct an Excel format file, a CSV (or comma separated values) file will suffice.
I use two JSP "views" (in the MVC sense) for my data... one is a normal HTML table, and one is a CSV file. The trick is to get the web browser client to recognize it as something that Exel can open.
Here is the JSP code for that:

The ReportAttributesBean contains what is essentially metadata. this includes things like the reference that allows retrieval of the ResultSet from the session. The custom tags render the data. I think that the important part for your purposes is in the first few lines of the page.
PCS
 
Chris Stewart
Ranch Hand
Posts: 184
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So when the user visits this page, do they get an option to "download" the CSV file? I'd like for them to click a link on the page where the resultSet is displayed (many rows of it) and it asks if they'd like to download the file. Is that what the response.setHeader does?
 
Chris Stewart
Ranch Hand
Posts: 184
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, I wrote a simple page using your example. The problem is, when you open the CSV file, it contains the html tags and everything. Is there anyway to load the ResultSet object itself in some sort of Stream and write an ouput (excel) file?
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I use the PrintWriter to create a *.csv file to write each row of data to a line in the file. Each column value is read in as a string and delimited by a comma on a line. The default to open a *.csv file is MSExcel, so it should automatically load into Excel when opened.
There are other alternatives that are much more involved. You can use POI ( open source Jakarta project ) to do this. Search the Java in general and maybe JDBC forums for information on POI ( Dirk Schreckmann recently made a post with the link ).
Jamie
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!