poi 实现excle 数据转入数据库 （已实现） 支持2007

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 blocke.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 blocke.printStackTrace();}if(stmt!=null)try {stmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}if(rs!=null)try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 blocke.printStackTrace();}if(stmt!=null)try {stmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}if(rs!=null)try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}