package page;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import whr.domain.*;
import whr.util.*;
import whr.dao.*;
public class TestExcel {
//记录类的输出信息
static Log log = LogFactory.getLog(TestExcel.class);
//获取Excel文档的路径
public static String filePath = "C:/Users/Administrator/Desktop/123.xlsx";
public static void main(String[] args) {
try {
// 创建对Excel工作簿文件的引用
XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
// 在Excel文档中,第一张工作表的缺省索引是0,
// 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
XSSFSheet sheet = wookbook.getSheet("Sheet1");
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//遍历行
for (int i = 0; i < rows; i++) {
// 读取左上端单元格
XSSFRow row = sheet.getRow(i);
// 行不为空
if (row != null) {
//获取到Excel文件中的所有的列
int cells = row.getPhysicalNumberOfCells();
String value = "";
//遍历列
for (int j = 0; j < cells; j++) {
//获取到列的值
XSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
}
// 将数据插入到mysql数据库中
String[] val = value.split(",");
Student entity = new Student();
entity.setId(val[0]);
entity.setName(val[1]);
entity.setPassword(val[2]);
entity.setSex(val[3]);
Studentdao method = new Studentdao();
method.insert(entity);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
*
*
*
package whr.domain;
public class Student {
private String id;
private String name;
private String password;
private String sex;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
*
package whr.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import whr.domain.*;
import whr.util.*;
public class Studentdao {
// public static List<Student> findall() throws SQLException{
// List<Student> tlist= new ArrayList<Student>();
// Connection conn=null;
// Statement stmt=null;
// ResultSet rs= null;
// conn = DBconnection.getConnection();
// try {
// String sql = "select *from teacherinfo";
// stmt = conn.createStatement();
// rs = stmt.executeQuery(sql);
// while(rs.next()){
// int id = rs.getInt("id");
// String name = rs.getString("name");
// String password = rs.getString("password");
// Student t = new Student(id,name,password);
// tlist.add(t);
// }
// } catch (SQLException e) {
// e.printStackTrace();
// tlist.clear();
// tlist = null;
// }finally{
// DBconnection.free (conn, stmt, rs );
// }
// return tlist;
// }
// public static Student find(int id) throws SQLException{
// Student t = null;
// Connection conn=null;
// Statement stmt=null;
// ResultSet rs= null;
// try {
// conn = DBconnection.getConnection();
// if(conn == null) return null;
// String sql ="select *form teacherinfo where id = '"+id+"'";
// stmt = conn.createStatement();
// rs = stmt.executeQuery(sql);
// while(rs.next()){
// String name = rs.getString("name");
// String password = rs.getString("password");
// t = new Student(id,name,password);
// }
// } catch (SQLException e) {
// e.printStackTrace();
// }finally{
// DBconnection.free (conn, stmt, rs );
// }
// return t;
// }
public static boolean insert(Student s){
boolean flag =false;
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs= null;
int count1=0;
try {
conn = DBconnection.getConnection();
String sql = "insert into student values(?,?,?,?)";
stmt = conn.prepareStatement(sql);
stmt.setString(1, s.getId());
stmt.setString(2,s.getName());
stmt.setString(3,s.getPassword());
stmt.setString(4,s.getSex());
count1 =stmt.executeUpdate();
if(count1>0)
flag=true;
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBconnection.free (conn, stmt, rs );
}
return flag;
}
// public static void delete (int id){
// try {
// Connection conn = DBconnection.getConnection();
// String sql = "delete from teacherinfo where id=?";
// PreparedStatement stmt = conn.prepareStatement(sql);
// stmt.setInt(1,id);
// stmt.executeUpdate();
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
//
// }
// public static Student searchTeacher(int id){
// final String sql="select *from teacherinfo where id="+id;
// ResultSet rs=null;
// Statement st=null;
// Connection conn=null;
// Student t=null;
// try {
// conn=DBconnection.getConnection();
// if(conn==null) return null;
// st=conn.createStatement();
// rs=st.executeQuery(sql);
// while(rs.next())
// {
// String name;
// String password;
// name=rs.getString("name");
// id=id;
// password=rs.getString("password");
// t=new Student(id,name,password);
// }
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// DBconnection.free(conn, st, rs);
// return t;
// }
// public static boolean setTeacher(Student t){
// int result =0;
// Connection conn=null;
// Statement st=null;
// ResultSet rs=null;
// try {
// int id=t.getId();
// conn=DBconnection.getConnection();
// st=conn.createStatement();
// String sql="update teacherinfo set password="+t.getPassword()+" where id='"+id+"'";
// result=st.executeUpdate(sql);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// DBconnection.free(conn, st, rs);
//
// if(result==0) return false;
// else return true;
// }
}
*
package whr.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBconnection {
private static String url = "jdbc:mysql://localhost:3308/teacherlist";
private static String user = "root";
private static String password = "8858991";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, user, password);
}
public static void free (Connection conn,Statement stmt, ResultSet rs ){
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(stmt!=null)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.whr.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Dbconnection {
private static String url = "jdbc:mysql://localhost:3308/teacherlist";
private static String user = "root";
private static String password = "8858991";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, user, password);
}
public static void free (Connection conn,Statement stmt, ResultSet rs ){
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(stmt!=null)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}