mysql 表列名num1,num2,num3,num4,num5,num6 表名Excle
上傳的方法
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表中查詢到的數據的實體類
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語句
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 blocke.printStackTrace();}finally {try {pstmt.close();}catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}DBconnection.closeConnection();}return count;}}總結
以上就是本文關於java把excel內容上傳到mysql實例代碼的全部內容，希望對大家有所幫助。感興趣的朋友可以繼續參閱本站其他相關專題，如有不足之處，歡迎留言指出。感謝朋友們對本站的支持！