package khservlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.khdb.DBManager;

public class UploadOutputExcelServlet extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-Disposition",
"attachment; filename=\"kh.xls\"");

Connection conn = null;
try {

conn = DBManager.getDBManager().getConnection();
PreparedStatement pst = conn
.prepareStatement("select kid,kname,jmgh,zbkh,address,sname,aztime,lxr,phon,xtbh from khtype");
ResultSet rs = pst.executeQuery();

// 创建出 Excel文档
HSSFWorkbook students = new HSSFWorkbook();
HSSFSheet sheet = students.createSheet("客户信息资料");

HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("客户名称");
row.createCell(2).setCellValue("加密狗号");
row.createCell(3).setCellValue("正版卡号");
row.createCell(4).setCellValue("客户地址");
row.createCell(5).setCellValue("所在地区");
row.createCell(6).setCellValue("安装时间");
row.createCell(7).setCellValue("联系人");
row.createCell(8).setCellValue("电话");
row.createCell(9).setCellValue("XT800编号");
int index = 1;

while (rs.next()) {

row = sheet.createRow(index++);

row.createCell(0).setCellValue(rs.getString("kid"));
row.createCell(1).setCellValue(rs.getString("kname"));
row.createCell(2).setCellValue(rs.getString("jmgh"));
row.createCell(3).setCellValue(rs.getString("zbkh"));
row.createCell(4).setCellValue(rs.getString("address"));
row.createCell(5).setCellValue(rs.getString("sname"));
row.createCell(6).setCellValue(rs.getString("aztime"));
row.createCell(7).setCellValue(rs.getString("lxr"));
row.createCell(8).setCellValue(rs.getString("phon"));
row.createCell(9).setCellValue(rs.getString("xtbh"));

}
students.write(response.getOutputStream());

} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (Exception e) {
}
}

}

}

package khservlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.khdb.DBManager;

public class UploadOutputExcelServlet extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-Disposition",
"attachment; filename=\"kh.xls\"");

Connection conn = null;
try {

conn = DBManager.getDBManager().getConnection();
PreparedStatement pst = conn
.prepareStatement("select kid,kname,jmgh,zbkh,address,sname,aztime,lxr,phon,xtbh from khtype");
ResultSet rs = pst.executeQuery();

// 创建出 Excel文档
HSSFWorkbook students = new HSSFWorkbook();
HSSFSheet sheet = students.createSheet("客户信息资料");

HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("客户名称");
row.createCell(2).setCellValue("加密狗号");
row.createCell(3).setCellValue("正版卡号");
row.createCell(4).setCellValue("客户地址");
row.createCell(5).setCellValue("所在地区");
row.createCell(6).setCellValue("安装时间");
row.createCell(7).setCellValue("联系人");
row.createCell(8).setCellValue("电话");
row.createCell(9).setCellValue("XT800编号");
int index = 1;

while (rs.next()) {

row = sheet.createRow(index++);

row.createCell(0).setCellValue(rs.getString("kid"));
row.createCell(1).setCellValue(rs.getString("kname"));
row.createCell(2).setCellValue(rs.getString("jmgh"));
row.createCell(3).setCellValue(rs.getString("zbkh"));
row.createCell(4).setCellValue(rs.getString("address"));
row.createCell(5).setCellValue(rs.getString("sname"));
row.createCell(6).setCellValue(rs.getString("aztime"));
row.createCell(7).setCellValue(rs.getString("lxr"));
row.createCell(8).setCellValue(rs.getString("phon"));
row.createCell(9).setCellValue(rs.getString("xtbh"));

}
students.write(response.getOutputStream());

} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (Exception e) {
}
}

}

}

servlet导出Excel