java excel api实现输出EXCEL文件下载

<%@ page extends="tradeex.ec4.jsp.BaseJSPPage" import="java.util.*,java.text.*,java.sql.*,java.net.*" import="oracle.jdbc.driver.*,jxl.*,tradeex.ec4.util.*" contentType="text/html;charset=UTF8"%>
<%
String sorefitem=request.getParameter("SOREFITEM");
sorefitem = "24326";
String sheetname = "productsitems";
String excelsql = "select oi.sorefitem,po.psuppliercompany suppliername,oi.pname productname,oi.ivendpartnumber productcode,oi.oqty quantity,oi.osellprice price,oi.pricecurrefitem currency,po.ponumber "
                  +" from ecorders.orderitems oi,ecorders.purchaseorders po "
                  +" where (oi.ostatus>3 or oi.ostatus=3 and oi.osubstatus=4) AND oi.sorefitem="+sorefitem
                  +" AND oi.sorefitem=po.sorefitem and oi.osellerid=po.psupplierid" ;
int col_num = 0;

java.sql.Statement stm = null;
java.sql.ResultSet rs = null;
java.sql.Connection conn = null;try...{
        Class.forName("oracle.jdbc.driver.OracleDriver");    
        }catch (ClassNotFoundException e)
        ...{ System.out.println ("no exist driver");
          System.exit(0);
         }
       try
       ...{   
           
            response.reset();
           response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition" ,"attachment;filename="+new String("导出采购商明细报表Excel.xls".getBytes(),"iso-8859-1"));                   
            OutputStream os = response.getOutputStream();//将 WritableWorkbook 写入到输出流
            jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(os);    //创建Excel工作表   
            jxl.write.WritableSheet ws = wwb.createSheet("sheet_1",0);                      
            col_num = 7;           
            jxl.write.Label label_head;   
                           
            label_head = new jxl.write.Label(0,0,"请购单号");   
            ws.addCell(label_head);   
            label_head = new jxl.write.Label(1,0,"供应商名");   
            ws.addCell(label_head);   
            label_head = new jxl.write.Label(2,0,"产品规格型号");       
            ws.addCell(label_head);       
            label_head = new jxl.write.Label(3,0,"产品编码");       
            ws.addCell(label_head);       
            label_head = new jxl.write.Label(4,0,"数量");       
            ws.addCell(label_head);       
            label_head = new jxl.write.Label(5,0,"单价");   
            ws.addCell(label_head);       
            label_head = new jxl.write.Label(6,0,"定单号");
            ws.addCell(label_head);//结果集中的数据添加到excel中
            
            jxl.write.Number label_num;       
            jxl.write.Label label_str;       
            jxl.write.WritableCellFormat floatFormat;           
            int numrow = 1;
            System.out.println(System.currentTimeMillis()+" welcome to hehe!!!!!!!!!!!");
           
            conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.180:1521:DB","system","manager");
            stm = conn.createStatement();   
            rs = stm.executeQuery(excelsql);
           
            while(rs.next())       
            ...{   
                System.out.println(System.currentTimeMillis()+"   welcome to while!!!!!!!!!");          
                label_num = new jxl.write.Number(0,numrow,rs.getInt("sorefitem"));          
                ws.addCell(label_num);                       
                label_str = new jxl.write.Label(1,numrow,rs.getString("suppliername"));          
                ws.addCell(label_str);                     
                label_str = new jxl.write.Label(2,numrow,rs.getString("productname"));          
                ws.addCell(label_str);                     
                label_str = new jxl.write.Label(3,numrow,rs.getString("productcode"));          
                ws.addCell(label_str);                      
                floatFormat = new jxl.write.WritableCellFormat (jxl.write.NumberFormats.FLOAT);               
                label_num = new jxl.write.Number(4,numrow,rs.getDouble("quantity"),floatFormat);              
                ws.addCell(label_num);                 
                label_num = new jxl.write.Number(5,numrow,rs.getDouble("price"),floatFormat);         
                 ws.addCell(label_num);          
                 label_str = new jxl.write.Label(6,numrow,rs.getString("ponumber"));              
                 ws.addCell(label_str);                 
                 numrow ++;   
             }       
              wwb.write();
              wwb.close();
              os.flush();
              os.close();            
           }
           catch(java.sql.SQLException sqle)...{   
             System.out.println("sql error");            
             }       
           catch(Exception e)...{
              System.out.println("catch error ------");       
             e.printStackTrace();   
           }   
          finally...{
          System.out.println(System.currentTimeMillis()+ "welcome to finally!!!!!!!");                   
           if(stm != null)           
           try...{               
              stm.close();
           }catch(Exception fe) ...{ }           
           if(conn != null)           
           try...{               
           conn.close();       
           }           
           catch(Exception fe2)...{ }       
           }
%>
JXL包下载页面：http://sourceforge.net/project/showfiles.php?group_id=79926

如果要输出文件到服务器保存：
jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(new   File("D:/JSPweb/112WebModule1/download_excel/"+excelfilename+"交接箱统计故障数.xls")); //创建Excel工作表

评论

14 楼 binge520 2011-04-08  

       

13 楼 binge520 2011-04-08  

       

12 楼 binge520 2011-04-08  

       

11 楼 binge520 2011-04-08  

       

10 楼 binge520 2011-04-08  

       

9 楼 binge520 2011-04-08  

       

8 楼 binge520 2011-04-08  

       

7 楼 binge520 2011-04-08  

       

6 楼 binge520 2011-04-08  

       

5 楼 binge520 2011-04-08  

       

4 楼 binge520 2011-04-08  

       

3 楼 binge520 2011-04-08  

       

2 楼 binge520 2011-04-08  

  

1 楼 binge520 2011-04-08  

write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(new   File