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

来源：互联网发布：数据结构设计编辑：程序博客网时间：2024/05/17 02:10

这篇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 Hongten13  * @created 2014-5-1814  */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 database13     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     // common22     public static final String EXCEL_PATH = "lib/student_info.xls";23     24     // sql25     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 Hongten22  * @created 2014-5-1823  */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         // 循环工作表Sheet32         for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {33             HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);34             if (hssfSheet == null) {35                 continue;36             }37             // 循环行Row38             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 Hongten16  * @created 2014-5-1817  */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 Hongten10  * @created 2014-5-1811  */12 public class Student {13     /**14      * id15      */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 }

0 0