Excel表中的数据导入mysql数据库

Excel表中的数据导入mysql数据库(整体思路：读出Excel中的数据，插入到mysql中)

1,拷贝jar包

Jxl.jar/mysql.jar 

2,添加数据库的操作类src/com.chen.toolsbean

//韩顺平老师的SqlHelper类

SqlHelper.java

package com.chen.toolsbean;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class SqlHelper {
// 定义变量
private static Connection ct = null;
// 大多数情况下用preparedstatement替代statement
private static PreparedStatement ps = null;
private static ResultSet rs = null;
 // 连接数据库的参数
privatestatic String url = "";private static String username = "";
private static String driver = "";
private static String passwd = "";
private static CallableStatement cs = null;
public static CallableStatement getCs() {
   return cs;
}
private static Properties pp = null;
private static InputStream fis = null;
// 加载驱动，只需要一次，用静态代码块
static {
   try {
      // 从dbinfo.properties
      pp = new Properties();
      fis = SqlHelper.class.getClassLoader().getResourceAsStream(
            "com/chen/toolsbean/dbinfo.properties");
      // fis = new FileInputStream();
      pp.load(fis);
      url = pp.getProperty("url");
      username = pp.getProperty("username");
      driver = pp.getProperty("driver");
      passwd = pp.getProperty("password");
      Class.forName(driver);
   } catch (Exception e) {
      e.printStackTrace();
   } finally {
      try {
         fis.close();
      } catch (IOException e) {
         e.printStackTrace();
      }
      fis = null;// 垃圾回收站上收拾
   }
}
// 得到连接

publicstatic Connection getConnection() {try {
      ct = DriverManager.getConnection(url, username, passwd);
   } catch (Exception e) {
      e.printStackTrace();
   }
   return ct;
}
 
// *************callPro1存储过程函数1*************
public static CallableStatement callPro1(String sql, String[] parameters) {
   try {
      ct = getConnection();
      cs = ct.prepareCall(sql);
      if (parameters != null) {
         for (int i = 0; i < parameters.length; i++) {
            cs.setObject(i + 1, parameters[i]);
         }
      }
      cs.execute();
   } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e.getMessage());
   } finally {
      close(rs, cs, ct);
   }
   return cs;
}
// *******************callpro2存储过程2************************
public static CallableStatement callPro2(String sql, String[] inparameters,
      Integer[] outparameters) {
   try {
      ct = getConnection();
      cs = ct.prepareCall(sql);
      if (inparameters != null) {
         for (int i = 0; i < inparameters.length; i++) {
            cs.setObject(i + 1, inparameters[i]);
         }
      }
      // cs.registerOutparameter(2,oracle.jdbc.OracleTypes.CURSOR);
      if (outparameters != null) {
         for (int i = 0; i < outparameters.length; i++) {
            cs.registerOutParameter(inparameters.length + 1 + i,
                 outparameters[i]);
         }
      }
      cs.execute();
   } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e.getMessage());
   } finally {
   }
   return cs;
}
 
public static ResultSet executeQuery(String sql, String[] parameters) {
   try {
　　　ct = getConnection();
      ps = ct.prepareStatement(sql);
      if (parameters != null) {
         for (int i = 0; i < parameters.length; i++) {
            ps.setString(i + 1, parameters[i]);
         }
      }
      rs = ps.executeQuery();
   } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e.getMessage());
   } finally {
   }
   return rs;
} 
public static Connection getCt() {
   return ct;
}
public static PreparedStatement getPs() {
   return ps;
}
public static ResultSet getRs() {
   return rs;
}
public static void executeUpdate2(String[] sql, String[][] parameters) {
   try {
      ct = getConnection();
      ct.setAutoCommit(false);
      for (int i = 0; i < sql.length; i++) {
         if (null != parameters[i]) {
            ps = ct.prepareStatement(sql[i]);
            for (int j = 0; j < parameters[i].length; j++) {
              ps.setString(j + 1, parameters[i][j]);
            }
            ps.executeUpdate();
         }
      }
      ct.commit();
   } catch (Exception e) {
      e.printStackTrace();
      try {
         ct.rollback();
      } catch (SQLException e1) {
         e1.printStackTrace();
      }
      throw new RuntimeException(e.getMessage());
   } finally {
      close(rs, ps, ct);
   }
}
// 先写一个update、delete、insert
// sql格式：update 表名 set 字段名 =？where 字段=？
// parameter神应该是（”abc“,23）
public static void executeUpdate(String sql, String[] parameters) {
   try {
      ct = getConnection();
      ps = ct.prepareStatement(sql);
      if (parameters != null) {
         for (int i = 0; i < parameters.length; i++) {
            ps.setString(i + 1, parameters[i]);
         }
      }
      ps.executeUpdate();
   } catch (Exception e) {
      e.printStackTrace();// 开发阶段
      // 抛出异常
      // 可以处理，也可以不处理
      throw new RuntimeException(e.getMessage());
   } finally {
      close(rs, ps, ct);
   }
}
public static void close(ResultSet rs, Statement ps, Connection ct) {
   // 关闭资源(先开后关)
   if (rs != null) {
      try {
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      rs = null;
   }
   if (ps != null) {
      try {
         ps.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      ps = null;
   }
   if (null != ct) {
      try {
         ct.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      ct = null;
   }
}
}

同包内的配置文件

dbinfo.properties

# key

username=root

password=

driver=com.mysql.jdbc.Driver

url=jdbc\:mysql\://127.0.0.1\:3306/demo?useUnicode\=true&characterEncoding\=utf-8解决中文乱码

要转到数据的表信息

package com.chen.domain; 
public class Person {
private int id;
private String name;
private String sex;
private String jiguan;
private int age; 

public int getId() {
   return id;
}
public void setId(int id) {
   this.id = id;
}
public String getName() {
   return name;
}
public void setName(String name) {
   this.name = name;
}
public String getSex() {
   return sex;
}
public void setSex(String sex) {
   this.sex = sex;
} 
public String getJiguan() {
   return jiguan;
}
public void setJiguan(String jiguan) {
   this.jiguan = jiguan;
}public int getAge() {
   return age;
}
public void setAge(int age) {
   this.age = age;
}
}

读取Excel表的数据：

package com.chen.tomysql;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import com.chen.domain.Person;

public class Excel {
public List<Person> addCustomerAssign(File file) {
   List<Person> ls = new ArrayList<Person>();
   jxl.Workbook rwb = null;
   try {
      InputStream is = new FileInputStream(file);
      rwb = Workbook.getWorkbook(is);
      Sheet rs = rwb.getSheet(0);
      int rsRows = rs.getRows();
      for (int i = 1; i < rsRows; i++) {
         // 如第一行为属性项则从第二行开始取数据(int i=0 ;i<rsRows;i++)
         String cell1 = rs.getCell(0, i).getContents() + " ";
         String cell2 = rs.getCell(1, i).getContents() + " ";
         String cell3 = rs.getCell(2, i).getContents() + " ";
         String cell4 = rs.getCell(3, i).getContents() + " ";
         if (cell1 != null && !cell1.equals(" ") && cell2 != null
              && !cell3.equals(" ") && !cell4.equals(" ")) {
            Person person = new Person();
           person.setName(rs.getCell(0, i).getContents());
            person.setSex(rs.getCell(1, i).getContents());
            person.setJiguan(rs.getCell(2, i).getContents());
            person.setAge(Integer.parseInt(rs.getCell(3, i)
                 .getContents()));
            ls.add(person);
         }
      }
      System.out.println("取得信息！");
   } catch (Exception e) {
      e.printStackTrace();
   } finally {
      rwb.close();
   }
   return ls;
}
}

把Excel表中的数据写到Mysql中的类：

package com.chen.tomysql;
import com.chen.domain.Person;
import com.chen.toolsbean.SqlHelper;
public class ExcelToMysql {
public static Boolean insertMysql(Person person) {
   System.out.println("调取插入信息的方法！");
   String sql = "insert into Person(name,sex,jiguan,age) values(?,?,?,?)";
   String[] parameters = { person.getName(), person.getSex(),person.getJiguan(), person.getAge() + "" };
   boolean flag = false;
   try {
      SqlHelper.executeUpdate(sql, parameters);
      System.out.println("插入信息!");
      flag = true;
   } catch (Exception e) {
      e.printStackTrace();
   } finally {
   }
   return flag;
}
}

 

测试类：(其中J:\\Person.xls文件内容：

name	sex	jiguan	age
陈新卫	男	河南	10
chen	M	henan	20

Mysql数据库中Person表的字段

 

 

package com.chen.tomysql;
import java.io.File;
import java.util.Iterator;
import java.util.List;
import org.junit.Test;
import com.chen.domain.Person;
public class ExcelToMysqlTest {
@Test
public void test() throws Exception {
   Excel excel = new Excel();
   List<Person> list = excel.addCustomerAssign(new File("J:\\Person.xls"));
   Iterator<Person> iter = list.iterator();
   System.out.println(iter.hasNext());
   while (iter.hasNext()) {
      Person person = iter.next();
      if (ExcelToMysql.insertMysql(person)) {
         System.out.println("Success!");
      } else {
         System.out.println("Fail!");
      }
   }
}
}