欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

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

程序员文章站 2022-05-02 13:22:15
...
这篇blog是介绍java中的poi技术读取Excel数据，然后保存到MySQL数据中。

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

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

项目结构：

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

Excel中的测试数据：

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

数据库结构：

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

对应的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;

插入数据成功：

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

如果重复数据，则丢掉：

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

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

源码部分：

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

/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 readXls() throws IOException {27 InputStream is = new FileInputStream(Common.EXCEL_PATH);28 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);29 Student student = null;30 List list = new ArrayList();31 // 循环工作表Sheet32 for (int numSheet = 0; numSheet 
/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 list = xlsMain.readXls();25 26 for (int i = 0; i 
/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 }


源码下载：http://files.cnblogs.com/hongten/ExcelTest.zip


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


多读一些书，英语很重要。


More reading,and english is important.


I'm Hongten


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


========================================================
相关标签： Excel java blog 技术 项目

上一篇： 在Vuejs中如何实现搜索匹配功能方法（详细教程）

下一篇： 详解关于JavaScript中Object值合并方法

推荐阅读