• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem while opening excel sheet from jsp

 
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I should submit this work today itself. that is why....
 
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank you ....

surely i will read HF

But still my program is not giving proper output
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
do call the flush() method of the out object at the end of the program.
 
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
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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 67746
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

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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ???
 
Slime does not pay. Always keep your tiny ad dry.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic