After you compile, IE requires you to specify a file extension in the
request IF you want Excel to open in the browser. If you don't, the user
will be prompted if they want to open the file or save the file, which will
be named [ServletName] with no file extension. What I usually do is generate
a quazi-random file name in the requesting page or servlet and append that
to the query string of the calling page:
<a href="servlet/ExcelForNorthwind?name=[text]&generatedFileName.xls">Get
Excel</a>
If you want the data to be submitted via method=post in a form, do this:
<form action="servlet/ExcelForNorthwind/generatedFileName.xls">
<!-- fields -->
</form>
NOTE: This should also work for Netscape, but I have not tested THIS code
for Netscape yet.
NOTE: This example works for Excel 97 & 2000. If you have users with Excel
95 or other spread sheet sw, you need to format the output NOT in an HTML
table, but in a comma separated value format ( value1,value2,value3 + \n
-- \n is the row delimiter). You can give the user the option prior to
requesting the Servlet and conditionally process the output.
All the best,
--Bill
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
public class ExcelForNorthwind extends HttpServlet {
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException
{
doGet(req,res);
}
public void doGet(HttpServletRequest request, HttpServletResponse
response)
throws IOException, ServletException
{
// This is the main thing you have to do but
// you also have to format output in an HTML table.
// Alternatively you can delimit the query with commas and
{CR}{LF}
response.setContentType("application/ms-excel");
PrintWriter out = response.getWriter();
//We'll search by last name
String name = request.getParameter("name");
out.println( "You requested information on : <strong>" +
name + "</strong>" );
String tblStart = "<table width=550 border=1>";
tblStart += "<tr bgcolor=\"#000000\"><th><font
color=white>Last Name, First Name</font></th><th><font
color=white>Title</font></th><th><font color=white>City,
State</font></th></tr>";
String tblEnd = "</table>";
String bgColor;
Cookie myCookie;
String SQL = "SELECT * FROM Employees WHERE LastName Like
\'%" + name + "%\'" ;
int i = 1;
try{
Driver drv = (Driver)
Class.forName("JData1_2.sql.$Driver").newInstance();
Connection con =java.sql.DriverManager.getConnection(
"jdbc:JDataConnect://localhost/Northwind::");
PreparedStatement s = con.prepareStatement( SQL );
ResultSet rs = s.executeQuery();
out.println( tblStart );
while( rs.next()){
//Alternate row colors
if( i % 2 == 0 ) {
bgColor = "#ffffff";
}
else{
bgColor = "#00cccc";
}
out.print( "<tr bgcolor=\"" + bgColor + "\">" );
out.print( "<td>" + rs.getString("LastName") + ",
" + rs.getString("FirstName") + "</td>");
out.print( "<td>" + rs.getString("Title") +
"</td>");
out.print( "<td>" + rs.getString("City") + ", " +
rs.getString("Region") + "</td>" );
out.println( "</tr>" );
i++;
}
out.println( tblEnd );
s.close();
con.close();
}
catch( Exception e ){
out.println( "\n SQL Exception -- " + e.getMessage()
);
}
}
}
//end
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
------------------------------
Date: Tue, 27 Jun 2000 14:39:35 +0200
From: Giovanni Santini SZ3PGQ <[EMAIL PROTECTED]>
Subject: MS Excel
Hello World,
is there somebody who can give me an example of servlet to generate excel
file?
Thank you
------------------------------
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
