关于 jsp servlet 将Excel表格内容写进数据库，将数据库导出到Excel中。（二）

从数据库导出EXCEL。同样添加poi.jar包。
jsp如下：

<a href="ReportServlet" onclick="return confirm('确认数据导出到E:/下?');">导出数据到Excel</a>

servlet如下：

package control;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

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.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import db.DB;

public class ReportServlet extends HttpServlet {


    /**
     *
     */
    private static final long serialVersionUID = 1L;
    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);
     Connection conn = null ;
      DB db = new DB() ;
      conn = db.getDB() ;
     String sql = "select * from test_table" ;

     ResultSet rs = null;
    try {
        rs = conn.createStatement().executeQuery(sql);
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
     // 获取总列数
     int CountColumnNum=0;
    try {
        CountColumnNum = rs.getMetaData().getColumnCount();
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
     int i = 1 ;
     // 创建Excel文档
      HSSFWorkbook wb = new HSSFWorkbook() ;
     // sheet 对应一个工作页
      HSSFSheet sheet = wb.createSheet("test_table表中的数据") ;
      HSSFRow firstrow = sheet.createRow(0); //下标为0的行开始
      HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
     String[] names = new String[CountColumnNum];
      names[0] = "ID";
      names[1] = "学号";
      names[2] = "姓名";

     for(int j= 0 ;j<CountColumnNum; j++){
      firstcell[j] = firstrow.createCell(j);
      firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
     }
     try {
        while(rs.next())
         {
         // 创建电子表格的一行
          HSSFRow row = sheet.createRow(i) ; // 下标为1的行开始
         for(int j=0;j<CountColumnNum;j++)
         {
         // 在一行内循环
          HSSFCell cell = row.createCell( j) ;
         // 设置表格的编码集，使支持中文
         //
         // 先判断数据库中的数据类型
         // 将结果集里的值放入电子表格中
         try {
            cell.setCellValue(new HSSFRichTextString(rs.getString(j+1))) ;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
         }
          i++ ;
         }
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
     // 创建文件输出流，准备输出电子表格
     OutputStream out = new FileOutputStream("E:\\test.xls") ;
      wb.write(out) ;
      out.close() ;
     System.out.println("数据库导出成功") ;
     try {
        rs.close() ;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
     try {
        conn.close() ;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    response.sendRedirect("test.jsp");
     }
     public static void main(String[] args)
     {
     try {
     @SuppressWarnings("unused")
     ReportServlet excel = new ReportServlet() ;
     } catch (Exception e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
     }
     }

}