java把excel内容上传到mysql实例代码
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 block e.printStackTrace(); } finally { try { pstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBconnection.closeConnection(); } return count; } }
总结
以上就是本文关于java把excel内容上传到mysql实例代码的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题,如有不足之处,欢迎留言指出。感谢朋友们对本站的支持!