• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Export data from Jsp to Excel taking more load time

 
Swathi Sree
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Swathi Sree
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Daniel Rhoades
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 697
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
niganola shanthi
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic