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
