📄 inputexcel.jsp

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



<%
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>
  
  <body>
 <%
 				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("E://j041.xls");
                        // 得到 workbook
                        Workbook wb = Workbook.getWorkbook(fs);

                         sh = wb.getSheet(0);
                        // 利用 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);

                        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());
                                }
                                sql = "INSERT INTO "+sh.getName()+"(学号,班级,姓名,性别,出生年月日,宿舍号,手机,QQ,电子邮箱)"
                                                + " VALUES(" + insertDate + ");";
                                smt.execute(sql);

                                insertDate = "";
                        }
                        con.commit();
                        con.close();//关闭数据库连接
                        wb.close();//关闭打开的文件，切记。切记
                        out.println("OK!");
                        // System.out.println(insertDate);
                        // start sql;

                } catch (IOException ex) {
                       ex.printStackTrace();
                } catch (BiffException ex) {
                        ex.printStackTrace();
                }

                catch (ClassNotFoundException ex) {
                       out.println("class not found");
                } catch (SQLException ex) {
                        ex.printStackTrace();
                }
        
  %>
  <center>已将E://j041.xls表导入到lsd数据库中，表名为<%=sh.getName() %></center>
  </body>
</html>
⌨️ 快捷键说明

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