mysql 表列名 num1,num2,num3,num4,num5,num6 表名Excle
上传的方法
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