Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem while opening excel sheet from jsp

 
priya pratheepp
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
priya pratheepp
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I should submit this work today itself. that is why....
 
P Lavti
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
priya pratheepp
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you ....

surely i will read HF

But still my program is not giving proper output
 
sankar anbazhagan
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
do call the flush() method of the out object at the end of the program.
 
Ulf Dittmer
Rancher
Pie
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64623
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
priya pratheepp
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ???
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic