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实例代码的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！