java的poi技术读取Excel数据到MySQL

这篇blog是介绍java中的poi技术读取Excel数据，然后保存到MySQL数据中。

你也可以在 ： java的poi技术读取和导入Excel 了解到写入Excel的方法信息

使用JXL技术可以在 ： java的jxl技术导入Excel 

项目结构：

Excel中的测试数据：

数据库结构：

对应的SQL：

1 CREATE TABLE `student_info` (
2   `id` int(11) NOT NULL AUTO_INCREMENT, 3 `no` varchar(20) DEFAULT NULL, 4 `name` varchar(20) DEFAULT NULL, 5 `age` varchar(10) DEFAULT NULL, 6 `score` float DEFAULT '0', 7 PRIMARY KEY (`id`) 8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据成功：

如果重复数据，则丢掉：

=============================================

源码部分：

=============================================

/ExcelTest/src/com/b510/client/Client.java

 1 /**
 2  * 
 3  */  4 package com.b510.client;  5  6 import java.io.IOException;  7 import java.sql.SQLException;  8  9 import com.b510.excel.SaveData2DB; 10 11 /** 12  * @author Hongten 13  * @created 2014-5-18 14 */ 15 public class Client { 16 17 public static void main(String[] args) throws IOException, SQLException { 18 SaveData2DB saveData2DB = new SaveData2DB(); 19  saveData2DB.save(); 20 System.out.println("end"); 21  } 22 }

/ExcelTest/src/com/b510/common/Common.java

 1 /**
 2  * 
 3  */  4 package com.b510.common;  5  6 /**  7  * @author Hongten  8  * @created 2014-5-18  9 */ 10 public class Common { 11 12 // connect the database 13 public static final String DRIVER = "com.mysql.jdbc.Driver"; 14 public static final String DB_NAME = "test"; 15 public static final String USERNAME = "root"; 16 public static final String PASSWORD = "root"; 17 public static final String IP = "192.168.1.103"; 18 public static final String PORT = "3306"; 19 public static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DB_NAME; 20 21 // common 22 public static final String EXCEL_PATH = "lib/student_info.xls"; 23 24 // sql 25 public static final String INSERT_STUDENT_SQL = "insert into student_info(no, name, age, score) values(?, ?, ?, ?)"; 26 public static final String UPDATE_STUDENT_SQL = "update student_info set no = ?, name = ?, age= ?, score = ? where id = ? "; 27 public static final String SELECT_STUDENT_ALL_SQL = "select id,no,name,age,score from student_info"; 28 public static final String SELECT_STUDENT_SQL = "select * from student_info where name like "; 29 }

/ExcelTest/src/com/b510/excel/ReadExcel.java

 1 /**
 2  * 
 3  */  4 package com.b510.excel;  5  6 import java.io.FileInputStream;  7 import java.io.IOException;  8 import java.io.InputStream;  9 import java.util.ArrayList; 10 import java.util.List; 11 12 import org.apache.poi.hssf.usermodel.HSSFCell; 13 import org.apache.poi.hssf.usermodel.HSSFRow; 14 import org.apache.poi.hssf.usermodel.HSSFSheet; 15 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 16 17 import com.b510.common.Common; 18 import com.b510.excel.vo.Student; 19 20 /** 21  * @author Hongten 22  * @created 2014-5-18 23 */ 24 public class ReadExcel { 25 26 public List<Student> readXls() throws IOException { 27 InputStream is = new FileInputStream(Common.EXCEL_PATH); 28 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); 29 Student student = null; 30 List<Student> list = new ArrayList<Student>(); 31 // 循环工作表Sheet 32 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { 33 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); 34 if (hssfSheet == null) { 35 continue; 36  } 37 // 循环行Row 38 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 39 HSSFRow hssfRow = hssfSheet.getRow(rowNum); 40 if (hssfRow != null) { 41 student = new Student(); 42 HSSFCell no = hssfRow.getCell(0); 43 HSSFCell name = hssfRow.getCell(1); 44 HSSFCell age = hssfRow.getCell(2); 45 HSSFCell score = hssfRow.getCell(3); 46  student.setNo(getValue(no)); 47  student.setName(getValue(name)); 48  student.setAge(getValue(age)); 49  student.setScore(Float.valueOf(getValue(score))); 50  list.add(student); 51  } 52  } 53  } 54 return list; 55  } 56 57 @SuppressWarnings("static-access") 58 private String getValue(HSSFCell hssfCell) { 59 if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { 60 // 返回布尔类型的值 61 return String.valueOf(hssfCell.getBooleanCellValue()); 62 } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { 63 // 返回数值类型的值 64 return String.valueOf(hssfCell.getNumericCellValue()); 65 } else { 66 // 返回字符串类型的值 67 return String.valueOf(hssfCell.getStringCellValue()); 68  } 69  } 70 }

/ExcelTest/src/com/b510/excel/SaveData2DB.java

 1 /**
 2  * 
 3  */  4 package com.b510.excel;  5  6 import java.io.IOException;  7 import java.sql.SQLException;  8 import java.util.List;  9 10 import com.b510.common.Common; 11 import com.b510.excel.util.DbUtil; 12 import com.b510.excel.vo.Student; 13 14 /** 15  * @author Hongten 16  * @created 2014-5-18 17 */ 18 public class SaveData2DB { 19 20 @SuppressWarnings({ "rawtypes" }) 21 public void save() throws IOException, SQLException { 22 ReadExcel xlsMain = new ReadExcel(); 23 Student student = null; 24 List<Student> list = xlsMain.readXls(); 25 26 for (int i = 0; i < list.size(); i++) { 27 student = list.get(i); 28 List l = DbUtil.selectOne(Common.SELECT_STUDENT_SQL + "'%" + student.getName() + "%'", student); 29 if (!l.contains(1)) { 30  DbUtil.insert(Common.INSERT_STUDENT_SQL, student); 31 } else { 32 System.out.println("The Record was Exist : No. = " + student.getNo() + " , Name = " + student.getName() + ", Age = " + student.getAge() + ", and has been throw away!"); 33  } 34  } 35  } 36 }

/ExcelTest/src/com/b510/excel/util/DbUtil.java

  1 /**
  2  * 
  3  */  4 package com.b510.excel.util;  5  6 import java.sql.Connection;  7 import java.sql.DriverManager;  8 import java.sql.PreparedStatement;  9 import java.sql.ResultSet;  10 import java.sql.SQLException;  11 import java.util.ArrayList;  12 import java.util.List;  13  14 import com.b510.common.Common;  15 import com.b510.excel.vo.Student;  16  17 /**  18  * @author Hongten  19  * @created 2014-5-18  20 */  21 public class DbUtil {  22  23 /**  24  * @param sql  25 */  26 public static void insert(String sql, Student student) throws SQLException {  27 Connection conn = null;  28 PreparedStatement ps = null;  29 try {  30  Class.forName(Common.DRIVER);  31 conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);  32 ps = conn.prepareStatement(sql);  33 ps.setString(1, student.getNo());  34 ps.setString(2, student.getName());  35 ps.setString(3, student.getAge());  36 ps.setString(4, String.valueOf(student.getScore()));  37 boolean flag = ps.execute();  38 if(!flag){  39 System.out.println("Save data : No. = " + student.getNo() + " , Name = " + student.getName() + ", Age = " + student.getAge() + " succeed!");  40  }  41 } catch (Exception e) {  42  e.printStackTrace();  43 } finally {  44 if (ps != null) {  45  ps.close();  46  }  47 if (conn != null) {  48  conn.close();  49  }  50  }  51  }  52  53 @SuppressWarnings({ "unchecked", "rawtypes" })  54 public static List selectOne(String sql, Student student) throws SQLException {  55 Connection conn = null;  56 PreparedStatement ps = null;  57 ResultSet rs = null;  58 List list = new ArrayList();  59 try {  60  Class.forName(Common.DRIVER);  61 conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);  62 ps = conn.prepareStatement(sql);  63 rs = ps.executeQuery();  64 while(rs.next()){  65 if(rs.getString("no").equals(student.getNo()) || rs.getString("name").equals(student.getName())|| rs.getString("age").equals(student.getAge())){  66 list.add(1);  67 }else{  68 list.add(0);  69  }  70  }  71 } catch (Exception e) {  72  e.printStackTrace();  73 } finally {  74 if (rs != null) {  75  rs.close();  76  }  77 if (ps != null) {  78 ps.close(); 79 } 80 if (conn != null) { 81 conn.close(); 82 } 83 } 84 return list; 85 } 86 87 88 public static ResultSet selectAll(String sql) throws SQLException { 89 Connection conn = null; 90 PreparedStatement ps = null; 91 ResultSet rs = null; 92 try { 93 Class.forName(Common.DRIVER); 94 conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD); 95 ps = conn.prepareStatement(sql); 96 rs = ps.executeQuery(); 97 } catch (Exception e) { 98 e.printStackTrace(); 99 } finally { 100 if (rs != null) { 101 rs.close(); 102 } 103 if (ps != null) { 104 ps.close(); 105 } 106 if (conn != null) { 107 conn.close(); 108 } 109 } 110 return rs; 111 } 112 113 }

/ExcelTest/src/com/b510/excel/vo/Student.java

 1 /**
 2  * 
 3  */  4 package com.b510.excel.vo;  5  6 /**  7  * Student  8  *  9  * @author Hongten 10  * @created 2014-5-18 11 */ 12 public class Student { 13 /** 14  * id 15 */ 16 private Integer id; 17 /** 18  * 学号 19 */ 20 private String no; 21 /** 22  * 姓名 23 */ 24 private String name; 25 /** 26  * 学院 27 */ 28 private String age; 29 /** 30  * 成绩 31 */ 32 private float score; 33 34 public Integer getId() { 35 return id; 36  } 37 38 public void setId(Integer id) { 39 this.id = id; 40  } 41 42 public String getNo() { 43 return no; 44  } 45 46 public void setNo(String no) { 47 this.no = no; 48  } 49 50 public String getName() { 51 return name; 52  } 53 54 public void setName(String name) { 55 this.name = name; 56  } 57 58 public String getAge() { 59 return age; 60  } 61 62 public void setAge(String age) { 63 this.age = age; 64  } 65 66 public float getScore() { 67 return score; 68  } 69 70 public void setScore(float score) { 71 this.score = score; 72  } 73 74 }

　　

转载：http://www.cnblogs.com/hongten/p/java_poi_excel.html

转载于:https://www.cnblogs.com/zhoushihui/p/5711774.html