📄 m_inputexcel2.jsp

📁 留学生信息管理系统我的工程实习的项目
💻 JSP
字号:
<%@ page language="java" import="java.sql.*" import="jxl.*" import="java.io.*" pageEncoding="utf-8"%>
<%@ page contentType="text/html; charset=gb2312"%>
<jsp:directive.page import="util.SplitStr"/>




<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'test_excel.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
<jsp:useBean id="split" scope="session" class="util.SplitStr"/>
  <link rel="stylesheet" href="m_style.css" type="text/css">
  <body>
  
   <%!
  	public String Bytes(String str)
  	{
  		try{
  			String str_1=str;
  			byte[] str_2=str_1.getBytes("ISO8859-1");
  			String bytes=new String(str_2);
  			return bytes;
  		}catch(Exception ex){
  			System.out.println("中文乱码解决问题出错"+ex.getMessage());
  			
  		}
  		return null;
  	}
   %>
 <%

 			String x=Bytes(request.getParameter("path"));
 			out.println(x);
 
 
 				Cell cell = null;
 				Sheet sh=null;
                String sql = null;
                String insertDate = "";
                try {
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                		String url="jdbc:odbc:lsd";
                		Connection con=DriverManager.getConnection(url);
                        // 使用事务。不用也无所谓
                        con.setAutoCommit(false);
                        Statement smt = con.createStatement();
                        // if (smt != null) System.out.println("smt is ok!");
                        // 加载excel文件
                        InputStream fs = new FileInputStream(x);//x是要导入的excel的路径，前面已经获取
                    
                        // 得到 workbook
                        Workbook wb = Workbook.getWorkbook(fs);

                         sh = wb.getSheet(0);
                         //验证数据库里是否已经存在sh.getName()表
                        DatabaseMetaData dbmd=con.getMetaData(); 
						ResultSet rs=dbmd.getTables(null,null,sh.getName(),null); 
						if(!rs.next()){
						//String tablename=rs.getString(3);
						//if(tablename.equals(sh.getName())){
                        // 利用 sheet 的名字做表名创建数据库表。你可以自己规定
                        sql = "CREATE TABLE "+sh.getName()
                                        
                                        + " ("
                                        + "学号 varchar(10),班级 varchar(10),姓名 varchar(5),性别 varchar(5),出生年月日 varchar(10)," +
                                        		"宿舍号 varchar(10),手机 varchar(15),QQ varchar(20),电子邮箱 varchar(50));";
                       
						 
						 smt.execute(sql);
						 //	}
						
						}
							 rs.close();
							
                        for (int i = 1; i < sh.getRows(); i++) {
                                for (int j = 0; j < sh.getColumns(); j++) {
                                        cell = sh.getCell(j, i);
                                        if (j != sh.getColumns() - 1)
                                                // System.out.print(cell.getContents() + ",");
                                                insertDate += "'" + cell.getContents() + "',";
                                        else
                                                insertDate += "'" + cell.getContents() + "'";
                                        // System.out.print(cell.getContents());
                                }
                                String[] temp=split.Split(insertDate,",");
 								String sql2="select * from "+sh.getName()+" where 学号= "+temp[0];
 								//out.println("select * from "+sh.getName()+" where 学号= "+temp[0]);
 								//out.print(temp[0]);
 								rs=smt.executeQuery(sql2);
 								if(!rs.next()){
                                sql = "INSERT INTO "+sh.getName()+"(学号,班级,姓名,性别,出生年月日,宿舍号,手机,QQ,电子邮箱)"
                                                + " VALUES(" + insertDate + ");";
                                smt.executeUpdate(sql);
								}
								
                                insertDate = "";
                                 rs.close();
                        }
                       
                        con.commit();
                        con.close();//关闭数据库连接
                        wb.close();//关闭打开的文件，切记。切记
                        out.println("OK!");
                        %>
                        
                        <center>已将E://j041.xls表导入到lsd数据库中，表名为<%=sh.getName() %></center>
                        <%
                        // System.out.println(insertDate);
                        // start sql;

                } catch (Exception ex) {
                	out.println("<center>ex.getMessage():"+ex.getMessage()
                	+"<input type='button' name='ret' value='返回' onclick='history.go(-1)'> "+"</center>");
                      
                } 
        
  %>
  
  </body>
</html>
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -