rstoexcel.java

留学生信息管理系统 我的工程实习的项目

package Xl;

import java.io.*;
import java.sql.*;

import news.sql_data;

import javax.servlet.*;
import javax.servlet.http.*;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
* resultset to excel
*         
*/
public class RsToExcel extends  HttpServlet{
        public void init(ServletConfig config)
                throws ServletException
        {
                super.init(config);
        }
        public void doPost(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException
        {        
                
                String sheetname = "StrName";                              // sheet名
                String excelsql = "select * from news ";              // sql语句  以ora中emp表为例
                int col_num = 0;                                           // 列数
                request.setCharacterEncoding("GB2312");
                response.setContentType("application/vnd.ms-excel");  //设置响应类型
                
                Statement stm = null;
                ResultSet rs = null;
                Connection conn = null;
                
                try  {
                        OutputStream os = response.getOutputStream();
                        //        将 WritableWorkbook 写入到输出流
                        WritableWorkbook wwb = Workbook.createWorkbook(os);
                        // 创建Excel工作表
                        WritableSheet ws = wwb.createSheet(sheetname,0);

                        //数据库连接
                     
                      String sDBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
                      Class.forName(sDBDriver);
                      String url="jdbc:odbc:lsd";
                      conn = DriverManager.getConnection(url);
                     
                        stm = conn.createStatement();
                        //得到结果
                        rs = stm.executeQuery(excelsql);

                        // 由 ResultSetMetaData 得到列数和字段名
                        ResultSetMetaData rsmd = rs.getMetaData();
                        col_num = rsmd.getColumnCount();  //NOTE 1
                        // 往工作表中添加数据**
                        
                        // 设置表头 即第一行 （i，1，列名）
                        for(int i=0;i<col_num;i++){
                                Label label_head = new Label(i,0,rsmd.getColumnName(i+1));
                                ws.addCell(label_head);
                        }
                        
                        // 结果集中的数据添加到excel中
                        for(int t=1;rs.next();t++){   // 要取出每一行的所有列 rs才下移next()
//                                        for(int w=0;w<col_num;w++){  //  从（列，行，xx） （0，1，xx）开始         
                                        
                                                //添加Label对象 （区分2种类型）                                        
//                                                if(rsmd.getColumnType(w+1)==2){        //NOTE 2 、3  ==8  
//                                                        Number label_num = new Number(w,t,rs.getDouble(w+1));
//                                                        ws.addCell(label_num);
//                                                }else{
//                                                        Label label_str = new Label(w,t,rs.getString(w+1));
//                                                        ws.addCell(label_str);
                        	//                                                }
                                        
//                                 }
                        	Number label_num = null;
                        	Label label_str =null;
                        	
                           label_num = new Number(0,t,rs.getInt(1));
                          ws.addCell(label_num);
                         label_str = new Label(1,t,rs.getString(2));
                          ws.addCell(label_str);
                          label_str = new Label(2,t,rs.getString(3));
                          ws.addCell(label_str);
                          label_num = new Number(3,t,rs.getInt(4));
                          ws.addCell(label_num);
                        }
                        
                        wwb.write(); //写入
                        wwb.close(); //关闭
                        os.flush();
                        os.close();        
                                
                }catch(SQLException sqle)  {         
                                System.out.print("sql error！");
            }
                catch(Exception e)  {        
                                e.printStackTrace();
                }
                finally{                        
                                if(stm != null)
                                try{
                                        stm.close();
                                }catch(Exception fe) { }
                                if(conn != null)
                                try{
                                        conn.close();
                                }
                                catch(Exception fe2) { }
                        }
                
        }

        public void destroy()
        {
                super.destroy();
        }

        public void doGet(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException
        {
                doPost(request, response);
        }
        
//        public static void main(String[] args){
//        	
//        	RsToExcel r=new RsToExcel();
//        	r.doPost( "e://r.xml");
//        }
}