cool hit counter java's poi technique to read Excel data to MySQL_Intefrankly

java's poi technique to read Excel data to MySQL


This blog is about poi technique in java to read Excel data and then save it to MySQL data.

You can also find it at. java's poi technology to read and import Excel Get information on how to write to Excel

The JXL technology can be used in. java's jxl technique to import Excel

Project structure.

Test data in Excel.

Database structure.

Corresponding 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;

Successful insertion of data.

In case of duplicate data, discard.

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

Source code section.

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

/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         // recurring worksheetSheet
32         for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
33             HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
34             if (hssfSheet == null) {
35                 continue;
36             }
37             //  circulating lineRow
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                 //  Returns a value of type Boolean
61                 return String.valueOf(hssfCell.getBooleanCellValue());
62             } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
63                 //  Returns a value of type numeric
64                 return String.valueOf(hssfCell.getNumericCellValue());
65             } else {
66                 //  Returns a value of type string
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      *  student number
19      */
20     private String no;
21     /**
22      *  name and surname
23      */
24     private String name;
25     /**
26      *  academy
27      */
28     private String age;
29     /**
30      *  performance records
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 }

Source Code Download.http://files.cnblogs.com/hongten/ExcelTest.zip

E | hongtenzone@foxmail.com B |http://www.cnblogs.com/hongten


Recommended>>
1、Chapter 2 Everything is an object of Java Programming Ideas mind map
2、Gitlocated atXcode Summary of frequently asked questions about the configuration and use of the
3、The next big breakthrough in deep learning Machine Reading
4、SpringSecuritynotebooksRememberMe Next automatic login
5、OC语言Block

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号