• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Export ResultSet to Excel

 
Ranch Hand
Posts: 184
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Men call me Jim. Women look past me to this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic