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

寒星2009

浏览: 201038 次
性别:
来自: 苏州

最近访客更多访客>>

Ethan_Fu

billylee2008

icesongs

sens88

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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

博客分类：

其他

Excel Java Oracle SQL OS

<%@ 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工作表

分享到：