wood burning stoves 2.0*
The moose likes JSP and the fly likes Problem while opening excel sheet from jsp Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "Problem while opening excel sheet from jsp" Watch "Problem while opening excel sheet from jsp" New topic
Author

Problem while opening excel sheet from jsp

priya pratheepp
Ranch Hand

Joined: Apr 02, 2008
Posts: 59
Hi friends

When i try to open excel sheet from my program I am getting two problems

1. first row of excel sheet is empty

2. while opening I am getting error message of "the file is not in a recognizable format"

Please help me..It is an urgent work
my code

<%@page language="java" import="java.io.*,java.text.*,java.sql.*,java.util.*"%>
<%

Class.forName("org.gjt.mm.mysql.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://256.136.119.86/mydb","zzz","zzz");
Statement st = conn.createStatement();

StringBuffer sb = new StringBuffer();

sb.append("SAP#" + "\t");
sb.append("x-plant status" + "\t");
sb.append("Total Amount" +"\t");
sb.append(">90 days" + "\t");

sb.append("\n");
try
{
String query="select * from temp_Xplant";
ResultSet rs = st.executeQuery(query);
while(rs.next()){
sb.append(rs.getString("sapNo") + "\t");
sb.append(rs.getString("status")+ "\t");
sb.append(rs.getString("amt") + "\t");
sb.append(rs.getString("days") + "\t");
sb.append("\n");

}

conn.close();
st.close();

}

catch (Exception e)
{
out.println("error");
conn.close();
st.close();
}


response.setContentType("application/vnd.ms-excel");
// response.setHeader("Content-Disposition", "attachment; filename=\"test.xls\"");
String testt = "tests.xls";
response.setHeader("Content-Disposition", "attachment; filename="+testt);


out.println(sb.toString());
out.close();

%>




thanks in advance
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41592
    
  55
Because you're not creating an XLS file. You're creating a tab-delimited file, but you're telling the browser that it should save (or open) it as an XLS file. That's bound not to work.

I'd create a comma-separated file instead, and tell the browser that the extension is ".csv". That should work much better.

As an aside, it's considered bad design to perform database tasks in JSPs, for multiple reasons. I'd move that into a backing bean or a servlet instead.


Ping & DNS - my free Android networking tools app
priya pratheepp
Ranch Hand

Joined: Apr 02, 2008
Posts: 59
Frist of all thanks a lot for your valuable suggestion.

This is my new work without using eclipse and work is in linux area...so don't know much to handle web.xml .because of that reason i am using like this jsp for everything
priya pratheepp
Ranch Hand

Joined: Apr 02, 2008
Posts: 59
I saved in .csv format.But again i am getting the same problem.Please can you help me remove first empty row in the excel sheet

thanks
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41592
    
  55
JSPs are not meant to stream files. There will always be whitespace and newlines inserted at various odd places throughout the output. That's why you should use servlets for this. Better to learn how to do that now, before bad habits get ingrained that are hard to get rid of later.
priya pratheepp
Ranch Hand

Joined: Apr 02, 2008
Posts: 59
I should submit this work today itself. that is why....
P Lavti
Ranch Hand

Joined: Mar 27, 2007
Posts: 65
You should always call setContentType() firs, Before you call the method that gives you your output stream i.e. PrintWriter or ServletOutputStream.

In case of JSP you need to set the the content type as below.

<%@ page language="java"
contentType="application/vnd.ms-excel;charset=WINDOWS-1252"%>

Before starting coding, go thru the basics of servlets. I'll recommend you the book Head First Seltes and JSP. I am also reading the same book, and it'll tell you all these important things very clearly.


-P Lavti<br />SCJP 5.0 (88%)
priya pratheepp
Ranch Hand

Joined: Apr 02, 2008
Posts: 59
thank you ....

surely i will read HF

But still my program is not giving proper output
sankar anbazhagan
Greenhorn

Joined: Apr 21, 2008
Posts: 2
do call the flush() method of the out object at the end of the program.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41592
    
  55
This problem has nothing to do with setting the content type. JSPs simply are not suitable for this kind of code.

do call the flush() method of the out object at the end of the program.

That, too, will not help.
[ April 21, 2008: Message edited by: Ulf Dittmer ]
P Lavti
Ranch Hand

Joined: Mar 27, 2007
Posts: 65
I agree that servlets are the best way to do this. But I think there should be a way to achieve the same by JSP's.

Try setting the header in your JSP

response.setHeader( "Content-Disposition",
"attachment; filename=filename.csv;" );
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41592
    
  55
But I think there should be a way to achieve the same by JSP's.

Why? JSPs are for meant for text, not files. They are usually compiled into servlets; that means servlets can do everything JSPs can. The reverse is not true, and this is an example of that.

The issue has nothing to do with the Content-Disposition header (or any other headers), either.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61084
    
  66

Originally posted by P Lavti:
I agree that servlets are the best way to do this. But I think there should be a way to achieve the same by JSP's.

That's like saying "I agree that a saw is the best tool to cut a board, but I think that there should be a way to do it with this hammer"

Use the right tool for the job.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
priya pratheepp
Ranch Hand

Joined: Apr 02, 2008
Posts: 59
thank your friends for your replies.

My prob is solved.It look like very funny.If i store the name of jsp file with excel word i am not getting error.

I don't know how.May be some shell script already running ???
 
wood burning stoves
 
subject: Problem while opening excel sheet from jsp