//下面是用来生成Excel数据的Servlet,在web.xml中映射成 /data.xls
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.carlease.persist.ExportDataDao;
public class ExportDataServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
//获得要生成Excel数据的数据库中的表名称
String tableName = request.getParameter("tableName");
// 禁止数据缓存。
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
// 设置响应的类型格式为电子表格格式
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// 将数据输出到Servlet输出流中。
ServletOutputStream sos = response.getOutputStream();
//这是一个用来处理从数据库中读取数据的Dao操作类
ExportDataDao edd = new ExportDataDao();
edd.outDB(tableName, sos);
sos.flush();
sos.close();
}
}
//操作读取数据库的Dao类
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletOutputStream;
import com.carlease.common.DBManager;
public class ExportDataDao {
public ExportDataDao(){
}
public void outDB(String tableName, ServletOutputStream sos) {
DBManager dbm = new DBManager();
Connection con = dbm.getCon();
ResultSetMetaData rsmd = null;
Statement smt = null;
ResultSet rs = null;
String sql = "select * from " + tableName;
try {
smt = con.createStatement();
rs = smt.executeQuery(sql);
rsmd = rs.getMetaData();
// 输出表头信息一共有多少列
int colNum = rsmd.getColumnCount();
StringBuffer sbt = new StringBuffer();
for (int i = 1; i <= colNum; i++) {
sbt.append(rsmd.getColumnName(i) + "\t");
}
sos.println(sbt.toString());
// 输出信息体,总共有多少条数据
while (rs.next()) {
StringBuffer sb = new StringBuffer();
for (int i = 1; i <= colNum; i++) {
sb.append(rs.getString(rsmd.getColumnName(i)) + "\t");
}
sos.println(sb.toString());
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获得当前用户下的所有表名称
*
* @return 装有所有表名称的List集合
*/
public List<String> getAllTable(){
List<String> tableList = new ArrayList<String>();
DBManager dbm = new DBManager();
Connection con = dbm.getCon();
Statement smt = null;
ResultSet rs = null;
try {
//获取Oracle数据库当前用户下的所有表名的特定语句
String sql = "select table_name from user_tables";
smt = con.createStatement();
rs = smt.executeQuery(sql);
while(rs.next()){
tableList.add(rs.getString("table_name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
dbm.close(rs, smt, con);
}
return tableList;
}
}