aspose file tools*
The moose likes JSP and the fly likes Excel-format report Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "Excel-format report" Watch "Excel-format report" New topic
Author

Excel-format report

Anu satya
Ranch Hand

Joined: Mar 17, 2005
Posts: 146
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


With Regards,
Anu
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42276
    
  64
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?


Ping & DNS - my free Android networking tools app
Anu satya
Ranch Hand

Joined: Mar 17, 2005
Posts: 146
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
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

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).


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

Joined: Mar 17, 2005
Posts: 146
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
Nitin Jawarkar
Ranch Hand

Joined: Dec 18, 2004
Posts: 79
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.


Cheers<br />Nitin
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61420
    
  67

"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


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42276
    
  64
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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61420
    
  67

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

Joined: Mar 17, 2005
Posts: 146
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
Satish SN
Ranch Hand

Joined: Apr 19, 2005
Posts: 70
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.



Satish SN<br />SCJP 1.4 & SCWCD 1.4
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61420
    
  67

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

Joined: Mar 17, 2005
Posts: 146
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

Joined: Dec 11, 2004
Posts: 13410

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.
Swathi Sree
Greenhorn

Joined: Aug 27, 2005
Posts: 19
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

Joined: Apr 19, 2005
Posts: 70
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

Joined: Mar 17, 2005
Posts: 146
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

Joined: Mar 17, 2005
Posts: 146
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

Joined: Dec 11, 2004
Posts: 13410

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

Joined: Dec 11, 2004
Posts: 13410

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

Joined: Mar 17, 2005
Posts: 146
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.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Excel-format report