用servlet把数据库表导出到excel中

//下面是用来生成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;
 }

}

评论

2 楼 ypf3027 2009-03-14  

liucl_tiger 写道

学习了，不错！ for (int i = 1; i &lt;= colNum; i++) { &nbsp;&nbsp;&nbsp; sbt.append(rsmd.getColumnName(i) + "\t"); &nbsp;&nbsp; } &nbsp;&nbsp; sos.println(sbt.toString());你在这里输入出了，为什么在下面还要输出呢？ &nbsp;&nbsp; // 输出信息体，总共有多少条数据 &nbsp;&nbsp; while (rs.next()) { &nbsp;&nbsp;&nbsp; StringBuffer sb = new StringBuffer(); &nbsp;&nbsp;&nbsp; for (int i = 1; i &lt;= colNum; i++) { &nbsp;&nbsp;&nbsp;&nbsp; sb.append(rs.getString(rsmd.getColumnName(i)) + "\t"); &nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; sos.println(sb.toString()); &nbsp;&nbsp; }

1，上面是输出表头信息，下面是用这个表头的名字去取对应行的数据，不是输出重复！

1 楼 liucl_tiger 2009-03-14  

学习了，不错！
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());
   }