通过POI读取Excel

于 2011-06-10 16:16:00 发布
阅读量325 收藏
点赞数
文章标签： string scroll import sql sqlserver jdbc
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ahwhbc/article/details/6536743
版权

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>


<html>
  <head>
   <meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
   <%
  if(session.getAttribute("logname").equals("null") || session.getAttribute("logout").equals("true")){
  response.sendRedirect("error.html");
  }
   %>

    <title></title>
   
 

  </head>
 <jsp:useBean id="admin" class="com.ebor.system.AdminManage" scope="session"></jsp:useBean>
  <body>
 
  <%
    
    String rev = request.getParameter("type");
     String[] sourceStrArray=rev.split("-");
    // for(int i=0;i<sourceStrArray.length;i++)
    //{
      //out.println(sourceStrArray[i]);
    //}
 
 
 
   %>
 
    <%
    // response.setContentType("application/vnd.ms-excel");
    // response.setHeader("Content-disposition","attachment;filename=Web_Transact_Report.xls");
 
 // response.setContentType("application/vnd.ms-excel");
  //response.setHeader("Content-Disposition", "attachment; filename=" + new String(name.getBytes("gbk"), "iso8859-1"));


 HSSFWorkbook book = new HSSFWorkbook();
 HSSFSheet sheet = book.createSheet("sheet1");
// HSSFRow row = sheet.createRow((short)0);
 
 
  String url = "jdbc:mysql://localhost:3306/logistics?user=root&password=123&useUnicode=true&characterEncoding=GB2312";
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  Connection conn= DriverManager.getConnection(url);
  Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
 
      // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
       //Connection conn= DriverManager.getConnection ("jdbc:sqlserver://localhost:1433;DatabaseName=DBTest;user=baochen;password=123");
       //Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    //String url = "jdbc:mysql://localhost:3306/logistics?user=root&password=123&useUnicode=true&characterEncoding=GB2312";
       //Class.forName("com.mysql.jdbc.Driver").newInstance();
      // Connection conn= DriverManager.getConnection(url);
      // Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

    // Connection conn = indexBeanId.getConnection();
  //      Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  //Statement stmt=conn.createStatement((ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);


String sql="select * from tbuser,electricity where tbuser.tcjobid=electricity.tcjobid   and tbuser.state2='扣款' "+"and electricity.years=" + sourceStrArray[0] +" and electricity.months =" + sourceStrArray[1] + " order by tbuser.num ";
 //String sql = "SELECT * FROM content ";
//int id = 1;
//String pass = "555";
//String sql = "delete from log where id ='"+id+"'";
//String sql = "update log set password ='"+pass+"' where id='"+id"'";
//String sql = "update log set password = '222' where id=" +id;
//String condition="delete from message where id="+id;
//out.print(sql);

//ResultSet rs = admin.getRs(sql);
//modify -0601 ResultSet rs=stmt.executeQuery(sql);

ResultSet rs=stmt.executeQuery(sql);
//int i = 0;
//int j = 1;
int rownum = 1;
String s0 = null;
String s1 = null;
String s2 = null;
String s3 = null;
String s4 = null;
String s5 = null;
String s6 = null;
String s7 = null;
String s8 = null;
String s9 = null;
String s10 = null;
String s11 = null;
String s12 = null;


 HSSFRow rowfirst = sheet.createRow((short)0);
 HSSFCell cellfirst = rowfirst.createCell((short)0);
 HSSFCell cellfirst1 = rowfirst.createCell((short)1);
    HSSFCell cellfirst2 = rowfirst.createCell((short)2);
 HSSFCell cellfirst3 = rowfirst.createCell((short)3);
    HSSFCell cellfirst4 = rowfirst.createCell((short)4);
 HSSFCell cellfirst5 = rowfirst.createCell((short)5);
 HSSFCell cellfirst6 = rowfirst.createCell((short)6);
 HSSFCell cellfirst7 = rowfirst.createCell((short)7);
 HSSFCell cellfirst8 = rowfirst.createCell((short)8);
 HSSFCell cellfirst9 = rowfirst.createCell((short)9);
 HSSFCell cellfirst10 = rowfirst.createCell((short)10);
 HSSFCell cellfirst11 = rowfirst.createCell((short)11);
 HSSFCell cellfirst12 = rowfirst.createCell((short)12);
 
    cellfirst.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst.setCellValue("序号");
 
 
    cellfirst1.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst1.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst1.setCellValue("状态1");
 
 cellfirst2.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst2.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst2.setCellValue("状态2");
 
    cellfirst3.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst3.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst3.setCellValue("年");
 
 cellfirst4.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst4.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst4.setCellValue("月");

    cellfirst5.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst5.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst5.setCellValue("楼");
 
 cellfirst6.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst6.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst6.setCellValue("单元");
 
    cellfirst7.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst7.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst7.setCellValue("房号");
 
 cellfirst8.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst8.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst8.setCellValue("工号");
 
    cellfirst9.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst9.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst9.setCellValue("部门");
 
 cellfirst10.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst10.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst10.setCellValue("姓名");
 
 cellfirst11.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst11.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst11.setCellValue("使用度数");
 
    cellfirst12.setCellType(HSSFCell.CELL_TYPE_STRING);
 cellfirst12.setEncoding(HSSFCell.ENCODING_UTF_16);
 cellfirst12.setCellValue("实扣");

while(rs.next()) {
 
    int s0_id = rs.getInt("num");
 s0 = String.valueOf(s0_id);
 s1 = rs.getString("state1");
 s2 = rs.getString("state2");
 int s3_id = rs.getInt("years");
 s3 = String.valueOf(s3_id);
 int s4_id = rs.getInt("months");
 s4 = String.valueOf(s4_id);
 s5 = rs.getString("floor");
 s6 = rs.getString("unit");
 s7 = rs.getString("doornum");
 s8 = rs.getString("tcjobid");
 s9 = rs.getString("depart");
 s10 = rs.getString("name");
 int s11_id = rs.getInt("usedegree");
 s11 = String.valueOf(s11_id);
    int s12_id = rs.getInt("fee");
 s12 = String.valueOf(s12_id);
 
 

 HSSFRow row = sheet.createRow((short)rownum);
 HSSFCell cell = row.createCell((short)0);
 HSSFCell cell1 = row.createCell((short)1);
    HSSFCell cell2 = row.createCell((short)2);
 HSSFCell cell3 = row.createCell((short)3);
    HSSFCell cell4 = row.createCell((short)4);
 HSSFCell cell5 = row.createCell((short)5);
 HSSFCell cell6 = row.createCell((short)6);
 HSSFCell cell7 = row.createCell((short)7);
 HSSFCell cell8 = row.createCell((short)8);
 HSSFCell cell9 = row.createCell((short)9);
 HSSFCell cell10 = row.createCell((short)10);
 HSSFCell cell11 = row.createCell((short)11);
 HSSFCell cell12 = row.createCell((short)12);
 
 cell.setCellValue(s0);

 cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
 cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
 cell1.setCellValue(s1);
 
 cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
 cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
 cell2.setCellValue(s2);
 
 cell3.setCellValue(s3);
 
 cell4.setCellValue(s4);
 
    cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
 cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
 cell5.setCellValue(s5);
 
 cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
 cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
 cell6.setCellValue(s6);
 
    cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
 cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
 cell7.setCellValue(s7);
 
 cell8.setCellType(HSSFCell.CELL_TYPE_STRING);
 cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
 cell8.setCellValue(s8);
 
    cell9.setCellType(HSSFCell.CELL_TYPE_STRING);
 cell9.setEncoding(HSSFCell.ENCODING_UTF_16);
 cell9.setCellValue(s9);
 
 cell10.setCellType(HSSFCell.CELL_TYPE_STRING);
 cell10.setEncoding(HSSFCell.ENCODING_UTF_16);
 cell10.setCellValue(s10);
 
 cell11.setCellValue(s11);

    cell12.setCellValue(s12);
   
 rownum++;
   
} 
 //out.print(s1);

   // HSSFRow row = sheet.createRow((short)rownum);
 //HSSFCell cell = row.createCell((short)0);
 //cell.setCellValue(s1);
   //while( s1 != null)
   //{
     //int  k = 10;
 
  //String xlsName = "C://Program Files//Apache Software Foundation//Tomcat 5.5//webapps//sdcx//temp.xls";
 String xlsName ="//disk2//sudytech//apache-tomcat-6.0.13//webapps//sdcx//temp.xls";
 // String dir = request.getRealPath();
  //fileName  = "temp182.xls";
 
   FileOutputStream file = new FileOutputStream(xlsName);
   book.write(file);
   file.close();
   response.sendRedirect("temp.xls");
  // k++;
   //}
%>


<%


rs.close();

//admin.closeItem();

 

//stmt.close();

//conn.close();

%>
  </body>
</html>

ahwhbc
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值