java导入excel数据到mysql_java的poi技术读取Excel数据到MySQL

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

你也可以在 ：

项目结构：

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 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 // 循环工作表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 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 }

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

源码部分：