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.
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.
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?
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
Joined: Aug 27, 2005
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?
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.
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.
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...