This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes JSP and the fly likes Export data from Jsp to Excel taking more load time Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "Export data from Jsp to Excel taking more load time" Watch "Export data from Jsp to Excel taking more load time" New topic
Author

Export data from Jsp to Excel taking more load time

Swathi Sree
Greenhorn

Joined: Aug 27, 2005
Posts: 19
Hi All,

My requirement is to get the data from database and display in excel sheet. User can either save or open in the browser. I am using response.setContentType("application/vnd.ms-excel"); for this purpose.

My problem is, to load the browser it is taking lot of time(10 to 15min).
Number of records will be around 60000.

Anybody please tell me how to reduce the load time.

Thanks in Advance,
Swathi.
Daniel Rhoades
Ranch Hand

Joined: Jun 30, 2004
Posts: 186
Do some logging in your app and see what stage is taking the most time, if the only bottleneck is sending response data to the browser then consider compressing the output stream.

Lots a posts in these forums for Excel problems - I'd do a search if I were you


Drinking more tea is the key...
Swathi Sree
Greenhorn

Joined: Aug 27, 2005
Posts: 19
Hi Daniel,

First of all Thank you for the reply. The loading time is more if I am trying to show the data in excel. If it is jsp, it is taking normal time.

Any idea of how would it effect if the browser shows data in excel? any information regarding comparision of load time for jsp to excel?

Thanks & Regards,
Swathi.
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Changing the content-type tag should have no effect on how long it takes to get the data from the server to the client. Regardless of what you set the content type to, the server is doing the same amount of work.

Perhaps the difference you're seeing is due to the time that it takes to instanciate Excel (not a small app) on your client's machine?

Any time you have to send large amounts of text over the internet, the obvious first choice for speeding things up is compression.
http://www.onjava.com/pub/a/onjava/2003/11/19/filters.html


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Daniel Rhoades
Ranch Hand

Joined: Jun 30, 2004
Posts: 186
If you simply try saving the reponse to a file rather than opening it in Excel , is the problem still there? If not then try changing the content type to csv
Swathi Sree
Greenhorn

Joined: Aug 27, 2005
Posts: 19
Hi,

Compressing the data is great idea. Thankyou for guiding me in that direction. I have tried by compressing the data while sending through network, I could not find the difference in time of loading after compressing.

If I save the file from the JSP, it is saving correctly but opening the file again is becoming problem. I tried saving as csv but it didn't work for me. Some junk data is coming in to the file.

One thing I noticed is, saved xls file is taking 85MB space, which is 8MB if I copy the data and save it as xls file manually. As I am using response.setContentType("application/vnd.ms-excel"); the excel is opening in webpage and is being saved as web page xls.

I do not understand why this is taking 10times more memory than that of normal excel file.

Is there any way to open the excel as only excel file not as excel in webpage?

Thanks & Regards,
Swathi.
Paul Michael
Ranch Hand

Joined: Jul 02, 2001
Posts: 697
Hi Swathi. I also just started investigating on this issue.

We were able to reduce the file size by using a workaround but we still have problems with load time especially when dealing with large data such as 80,000+ records with 30+ columns each.

The workaround: Instead of clicking the "Save" button (which we think saves the excel file as embedded html), click the "Open" button and then save it from there. At one instance, we were able to reduce the file size from 93,052KB to 2,780KB.

Hope this helps! Let me know also if you ever find a solution to the load time problem.


SCJP 1.2 (89%), SCWCD 1.3 (94%), IBM 486 (90%), SCJA Beta (96%), SCEA (91% / 77%), SCEA 5 P1 (77%), SCBCD 5 (85%)
niganola shanthi
Greenhorn

Joined: Mar 15, 2006
Posts: 7
Hi Swathi Sree,


I am also getting same problem while save data from jsp to excel its saving but junk data is saving. Did you solved this problem. While opening excel sheet its giving error message. Please give need the information if you solved.



Thanks & Regards

Shanthi
Shreenivaas Reddy
Greenhorn

Joined: Jul 04, 2006
Posts: 7
Hi Swathi,

I hope this works fine for your question...'Is there any way to open the excel as only excel file not as excel in webpage?'
Just download JDIC(Java Desktop Integration Componenents) API and try to use the Desktop.open(...) method to open the Excel file in Excel window.
I have used JDIC for 'FileSearch and Opening the files'(like Windows FileSearch)application.
Hope it works for you...

regrads
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Export data from Jsp to Excel taking more load time
 
Similar Threads
browser time out problem.
Important jsp
HTML Table to an Excel Spreadsheet
why does My JSPs are taking very long time to load
Excel-format report