This week's book giveaway is in the Raspberry Pi forum.
We're giving away four copies of Getting started with Java on the Raspberry Pi and have Frank DelPorte on-line!
See this thread for details.
Win a copy of Getting started with Java on the Raspberry Pi this week in the Raspberry Pi forum!
  • 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
  • Ron McLeod
  • Paul Clapham
  • Tim Cooke
  • Jeanne Boyarsky
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Liutauras Vilda
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Tim Moores
  • Mikalai Zaikin
Bartenders:
  • Piet Souris

Excel-format report

 
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello everybody,

I am doing a report using JSP, html and java Script as front end and DB2 as back end.
When the user gives the input, depending on that input, report is given.
Now, my problem is,
User wants the report to be in EXCEL format.
So, i am changing the content type to:
----------------------
response.setContentType("application/vnd.ms-excel");
----------------------

Yes, i am getting report in Excel format. But, It is taking 10-12min. time to download and display. Report retrieves the data from backend and displays it on excel. In the database, there are around 5000 records.
Sometimes, user wants to see all existing records on excel.
So, to display all 5000 records in Excel-format, is taking quite a long time. Users are getting frustrated to wait for such a long time for one single download.

So, is there any other way to get Excel-format report other than changing the content type? I also want to know how to decrease the downloading time?

Any help will be greatly appreciated.
Thanks in advance

Regards
Ash
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Which part is taking so long: the retrieving of the data from the DB, the processing on the server, or the generation of the Excel file?
 
Anu satya
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for your reply.

If i run the quires on the backend, it is taking 1 or 2 min. to download.
SO, it is not the problem with DB. IT is may be processing at server and Excel download.

I am not saving the excel file in server. I am just opening the excel sheet, with the required content. If user wants to save, they can save the sheet in their harddisk.....So, if they click on close, and if they want the excel sheet again, they have to give all required input and submit the view the report.

To reduce the downloading time,
1)I removed all <b><i> and other alignment tags from excel report.
2) I am not assigning the output values to any variable. I directly display it using (<td><%=rs.getString(1)%></td>


Please let me know, if there any other method to improve this downloading time?

Regards,
Ash
 
Sheriff
Posts: 13411
Firefox Browser VI Editor Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't think that changing the content type to Excel is what's causing the slowdown. To find out, try changing it to text/plain and see if it comes down any faster.

I have no doubt that the network (internet) is the bottleneck.
One thing that might speed things up is adding a gzip filter if your container doesn't already have one (Tomcat does).
 
Anu satya
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Ben Souther:
I don't think that changing the content type to Excel is what's causing the slowdown. To find out, try changing it to text/plain and see if it comes down any faster.

I have no doubt that the network (internet) is the bottleneck.
One thing that might speed things up is adding a gzip filter if your container doesn't already have one (Tomcat does).



Thank you Ben,

I have no idea what is gzip filter is all about! Can u please let me know Where can i find complete information about it?

Regards,
ASh
 
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Ashwini,

There is one another solution for your problem that
use the Apaches POI package which creates the Excel File
using beans yes it might be time consuming for developement
but it is fast to make excel file.
POI can get on Apache web site.

Thanx & Regards
Nitin.
 
Sheriff
Posts: 67732
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"Ashvini .S",

There aren't many rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't think using POI will make this any faster; in fact, I think it may be slower than generating HTML.

But more to the point: How is it an Excel file when you're generating HTML tags? I assumed you're talking about a CSV file? Have you measured how long the processing of one record takes? Maybe that can be speeded up. And how big is the complete file? It might take a browser quite a while to lay out a multi-MB-sized file.
 
Bear Bibeault
Sheriff
Posts: 67732
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

How is it an Excel file when you're generating HTML tags?



Excel will accept an HTML-formatted table as input.
 
Anu satya
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you Nitin Jawarkar,

But, how to fetch data from database and display the contents in Excel sheet?
It is like, i should display each field in seperate cell/column and each record in seperate row. Is it possible to use POI code in JSP?

Thanking you,

Regards
Ashvini
 
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Ashvini,

i also faced the same type of problem couple of months back.
I was able to reduce the download time to some extent as bcoz fi the records are more the time taken would definately be high.

my observation about the problem was

first check the time taken in the db class for retrieval of values
try to optimize the query to get the results quicker.

secondly see the display logic of the results in the jsp page how ur iterating or do some optimization there by reducing the loops taken.

as i worked in above two step fashion i was able to reduce the time taken for displaying in my case it was around 50,000 records it took me average of 2 min to display the results.

Now u tell me ur display logic and other things i may help u to the maximum extent possible.

Regarding POI the learning curve would we be very high and it takes some understanding to go with POI implementation.

 
Bear Bibeault
Sheriff
Posts: 67732
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
kumar satish, JavaRanch is a community of people from all over the world, many of who are not native English speakers. While using abbreviations like "u" instead of spelling out "you" is convenient when text messaging your friends on a cell phone or in a chat room, it presents an extra challenge to those that are already struggling with English. Additionally, such shortcuts may confound automated translation tools that patrons of the Ranch may be making use of.

I would like to ask for your help in making the content of JavaRanch a little easier to read for everybody that visits here by not using such abbreviations.

Please read this for more information.

thanks,
bear
Forum Bartender
 
Anu satya
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you Sathis Kumar.

It will be of great help, If you guide me to tackle this problem.
Even though i have only 4000 to 5000 records to display, it is taking 10-15 min. time.

I will give you my code sample.

code
---------------------------
<%@ page language="java" import="java.text.*,java.sql.*,java.text.NumberFormat,java.util.*,javax.servlet.http.*,java.util.Date" %>

<%
String connect=String.valueOf(session.getAttribute("connect"));
String uid=String.valueOf(session.getAttribute("uid"));
String pwd=String.valueOf(session.getAttribute("pwd"));

if(connect.equals("true"))
{
String stkno=new String();
stkno=request.getParameter("stkno").trim().toUpperCase();
%>
<html>
<head>
<title>Excel Report for Stock Items</title>
</head>
<table border="1" width="80%" align="center">
<tr bgcolor="#D2D3FF">
<td colspan="16" height="20" align="center">
<div align="center"><fontface="Verdana, Arial, Helvetica, sans-serif" size="3"><B><B>EXCEL   REPORT </B></B> for 
<B><B>STOCK   ITEMS - 
</B></b></font></div>
</td>
</tr>
</table>
<br><br>
<TABLE border=1 width="100%">

<TR>
<Th>Stock Code</Th>
<Th>Description</Th>
<Th>UOM</Th>
<Th>Forecast 1</Th>
<Th>Customer Orders</Th>
<Th>Forecast Accuracy 1 %</Th>
<Th>Good Forecast %</Th>
<Th>Forecast 1 Value</Th>
<Th>Customer Orders Value</Th>
<Th>Forecast Accuracy diff Value 1</Th>
<Th>Forecast 3</Th>
<Th>Customer orders</Th>
<Th>Forecast Accuracy 3 %</Th>
<Th>Forecast 3 Value</Th>
<Th>Forecast Accuracy diff Value 3</Th>
</TR>
<%
try
{
Connection con=null;
response.setContentType("application/vnd.ms-excel");
CallableStatement cs1=null;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc dbc:testing",uid,pwd);
Statement s=con.createStatement();
ResultSet rs = s.executeQuery("SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15 from stock_master WHERE stockno='"+stkno+"'");

String clas=new String();
double first=0;
double fcst1=0;
double ord1=0;
double fcstp1=0;
double cntrto=0;
double fcstv1=0;
double ordv1=0;
double diff1=0;
double fcst3=0;
double fcstp3=0;
double fcstv3=0;
double diff3=0;
double diff4=0;
double diff5=0;
double diff6=0;


while (rs1.next())
{
first=rs1.getDouble(1);
fcst1=rs1.getDouble(2);
ord1=rs1.getDouble(3);
fcstp1=rs1.getDouble(4);
cntrto=rs1.getDouble(5);
fcstv1=rs1.getDouble(6);
ordv1=rs1.getDouble(7);
diff1=rs1.getDouble(8);
fcst3=rs1.getDouble(9);
fcstp3=rs1.getDouble(10);
fcstv3=rs1.getDouble(11);
diff3=rs1.getDouble(12);
diff4=rs1.getDouble(13);
diff5=rs1.getDouble(14);
diff6=rs1.getDouble(15);
%>
<tr bgcolor="#FFFFD7">
<!-- I am using number Format to display the contents or numbers along with cama and without that exponential format-->
<td Align = "CENTER" Valign = "Bottom" CLASS="Heading" colspan=2><B>Summary</B></td>
<TD align="center"><%=(NumberFormat.getInstance().format(fcst1))%></TD>
<TD><%=(NumberFormat.getInstance().format(fcst1))%></td>
<TD><%=(NumberFormat.getInstance().format(ord1))%></td>
<TD><%=(NumberFormat.getInstance().format(fcstp1))%></td>
<TD><%=(NumberFormat.getInstance().format(cntrto))%></td>
<TD><%=(NumberFormat.getInstance().format(fcstv1))%></td>
<TD><%=(NumberFormat.getInstance().format(ordv1))%></td>
<TD><%=(NumberFormat.getInstance().format(diff1))%></td>
<TD><%=(NumberFormat.getInstance().format(fcst3))%></td>
<TD><%=(NumberFormat.getInstance().format(ord1))%></td>
<TD><%=(NumberFormat.getInstance().format(fcstp3))%></td>
<TD><%=(NumberFormat.getInstance().format(fcstv3))%></td>
<TD><%=(NumberFormat.getInstance().format(diff3))%></td>
</tr>
<TR>
<TD colspan=14 align=center><a href="farip.jsp"><font SIZE=4 color="#6600FF">Back</font></a></TD>
</TR>
<%
}//end od while
rs.close();
}catch(Exception e)
{
out.println(e);
}
}//end of if connect
else
{
response.sendRedirect("login1.jsp");
}
%>
---------------------------

Similar to this while loop above, i have two more loops to fetch data from backend. I can't avoid those loops. Those are must.

Please note:
Instead of using select statements, i created views at the backend, I invoked these views with the help of CallableStatements. But still download time is more.....10-15 min.

So, can you please guide me !

Thanks in advance.
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser VI Editor Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you looked into gziping your output?
http://www.google.com/search?hl=en&lr=&q=GZIP+FILTER&btnG=Search

You didn't say what container you're using.
Tomcat has this capability built in.
It's possible that your container does too.
 
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I am also facing the same problem of taking more time to load(10-15 misn) while exporting data from jsp to excel. I am using response.setContentType("application/vnd.ms-excel"); to display in Excel.

I am using for loop to iterate through the data. The data is retrieved from database, kept in arraylist and that arraylist is iterated to display in browser. Records will be around 60000.

please help me to reduce the load time.

Any help is greatly appreciated.

Thanks in Advance,
Swathi.
 
Satish SN
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ashvini,

I feel that u are embedding Database code into the JSP which takes lots of time to generate the data.

my suggestion would be to move the DB related code to the helper class which will return the values in the collection format may be arraylist
and i feel this would certainly reduce the display time for the excel

try this suggestion and come back to me for further doubts

--->
 
Anu satya
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you Ben for your reply,

Since i am new to Java/JSP programming, i don't know what is "Container" is all about.

Yes, i went through the link you have sent. But, couldn't understand how to implement filter in my program.(i am not yet reached that level).

Any Guidance will be appreciated.
 
Anu satya
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you Sathish for your reply.

Could i be cheeky enough to ask for a sample code ?! :roll:

sample code for returning the values to arraylist and then use this arraly list to generate-Excel report?

Thankyou in advance !
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser VI Editor Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by kumar satish:
Hi Ashvini,

I feel that u are embedding Database code into the JSP which takes lots of time to generate the data.

my suggestion would be to move the DB related code to the helper class which will return the values in the collection format may be arraylist
and i feel this would certainly reduce the display time for the excel

try this suggestion and come back to me for further doubts

--->



Your suggestion to move the database code to helper classes is certainly a good idea but not for performance reasons.

I don't think the original poster would see any gain in performance by moving the code from one place on the server to another.
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser VI Editor Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Ashvini Devi:
Thank you Ben for your reply,

Since i am new to Java/JSP programming, i don't know what is "Container" is all about.

Yes, i went through the link you have sent. But, couldn't understand how to implement filter in my program.(i am not yet reached that level).

Any Guidance will be appreciated.



Container is your application server.
Which are you using?
Some, (such as Tomcat) have compression built in and only need some configuration to get it working.
 
Anu satya
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you Ben.

I am Using Tomcat 4.0 as TEST-server. As soon as i start Tomcat, i get following display in console:
----------------------------------------
Starting service Tomcat-Standalone
Apache Tomcat/4.0
Starting service Tomcat-Apache
Apache Tomcat/4.0
-----------------------------------------

Actual Server is there in Singapore and It is running on AS400 OS and websphere. I don't have any control on it.

 
Tell me how it all turns out. Here is a tiny ad:
Low Tech Laboratory
https://www.kickstarter.com/projects/paulwheaton/low-tech-0
reply
    Bookmark Topic Watch Topic
  • New Topic