mysql 表列名 num1,num2,num3,num4,num5,num6 表名Excle
上传的方法
java" id="highlighter_162276">
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
package com.web.connection;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class TestExcel { //记录类的输出信息 static Log log = LogFactory.getLog(TestExcel.class); //获取Excel文档的路径 //.xlsx文件用XSSFWorkbook .xlx 用HSSFWorkbook public static String filePath = "D://demoExcel.xlsx"; public static void main(String[] args) { try { // 创建对Excel工作簿文件的引用 XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath)); // 在Excel文档中，第一张工作表的缺省索引是0 // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0); XSSFSheet sheet = wookbook.getSheet("Sheet1"); //获取到Excel文件中的所有行数 int rows = sheet.getPhysicalNumberOfRows(); //遍历行 for (int i = 0; i < rows; i++) { // 读取左上端单元格 XSSFRow row = sheet.getRow(i); // 行不为空 if (row != null) { //获取到Excel文件中的所有的列 int cells = row.getPhysicalNumberOfCells(); String value = ""; //遍历列 for (int j = 0; j < cells; j++) { //获取到列的值 XSSFCell cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_NUMERIC: value += cell.getNumericCellValue() + ","; break; case HSSFCell.CELL_TYPE_STRING: value += cell.getStringCellValue() + ","; break; default: value += "0"; break; } } } // 将数据插入到mysql数据库中 String[] val = value.split(","); TestEntity entity = new TestEntity(); entity.setNum1(val[0]); entity.setNum2(val[1]); entity.setNum3(val[2]); entity.setNum4(val[3]); entity.setNum5(val[4]); entity.setNum6(val[5]); TestMethod method = new TestMethod(); int a=method.add(entity); if(a>0){ System.out.println("插入成功"); } else{ System.out.println("插入失败"); } } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }} |
其中 TestEntity 为用存放从excel表中查询到的数据的实体类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
package com.web.connection;public class TestEntity { private String num1; private String num2; private String num3; private String num4; private String num5; private String num6; public TestEntity(){ } public String getNum1() { return num1; } public void setNum1(String num1) { this.num1 = num1; } public String getNum2() { return num2; } public void setNum2(String num2) { this.num2 = num2; } public String getNum3() { return num3; } public void setNum3(String num3) { this.num3 = num3; } public String getNum4() { return num4; } public void setNum4(String num4) { this.num4 = num4; } public String getNum5() { return num5; } public void setNum5(String num5) { this.num5 = num5; } public String getNum6() { return num6; } public void setNum6(String num6) { this.num6 = num6; }} |
TestMethod 为往mysql表中插入数据 的sql语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
package com.web.connection;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class TestMethod { public int add(TestEntity te){ Connection con = DBconnection.getConnection(); PreparedStatement pstmt = null; int count = 0; String sql = " insert into Excle(num1,num2,num3,num4,num5,num6) values(?,?,?,?,?,?)"; try { pstmt = con.prepareStatement(sql); pstmt.setString(1, te.getNum1()); pstmt.setString(2, te.getNum2()); pstmt.setString(3, te.getNum3()); pstmt.setString(4, te.getNum4()); pstmt.setString(5, te.getNum5()); pstmt.setString(6, te.getNum6()); count = pstmt.executeUpdate(); /** if(count==0){ throw new DataAlreadyExistException(); }*/ } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { pstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBconnection.closeConnection(); } return count; }} |
总结
以上就是本文关于java把excel内容上传到mysql实例代码的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！
原文链接：http://www.cnblogs.com/leirenyuan/p/6008143.html
