package com.zhiyou100.video.utils;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
/**
* 作者:于留俊
时间:20180713
qq:1099887856@qq.com
*
**/
public class TestExcelToDb1 {
public static void main(String[] args) throws Exception {
/**
* String DRIVER = "com.mysql.jdbc.Driver";
String URL = "jdbc:mysql://localhost:3307/zy_video";//根据自己的数据库设置路径和用户名密码
String USERNAME = "bin";
String USERPASSWORD = "123456";
String sql = "select * from u where id =?"; // 根据自己的要求书写sql语句
// 连接数据库
ResultSet rs;
try {
Class.forName(DRIVER);
Connection conn;
conn = DriverManager.getConnection(URL,USERNAME,USERPASSWORD);
PreparedStatement ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//得到表格中所有的数据
List<U> listExcel=StuService.getAllByExcel("D:/book.xls");
if(listExcel!=null) {
System.out.println("存在数据");
}else{
System.out.println("不存在数据表");
}
//得到数据库表中所有的数据
List<StuEntity> listDb=StuService.getAllByDb();
DBUtil db=new DBUtil();
for (U stuEntity : listExcel) {
System.out.println(stuEntity.toString()+"每个数据");
int id=stuEntity.getId();
if (!StuService.isExist(id)) {
//不存在就添加
String sql1="insert into u (um,sex) values(?,?)";
String[] str=new String[]{stuEntity.getUm(),stuEntity.getSex()+""};
if(str!=null) {
db.AddU(sql, str);
System.out.println("添加成功!");
}
}else {
//存在就更新
String sql2="update u set um=?,sex=? where id=?";
String[] str=new String[]{stuEntity.getUm(),stuEntity.getSex(),id+""};
if(str!=null) {
db.AddU(sql, str);
System.out.println("更新成功");
}
}
}
}**/
String DRIVER = "com.mysql.jdbc.Driver";
String URL = "jdbc:mysql://localhost:3307/zy_video";//根据自己的数据库设置路径和用户名密码
String USERNAME = "bin";
String USERPASSWORD = "123456";
int i;
Sheet sheet;
Workbook book;
@SuppressWarnings("unused")
Cell cell1,cell2,cell3,cell4,cell5,cell6,cell7,cell8,cell9,cell10,cell11,cell12,cell13,cell14,cell15,cell16;
try {
//hello.xls为要读取的excel文件名
book= Workbook.getWorkbook(new File("D:/存档考生信息表.xls"));
//获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
sheet=book.getSheet(0);
//获取左上角的单元格
cell1=sheet.getCell(0,0);
System.out.println("存档考生信息:"+cell1.getContents());
i=1;
while(true)
{
//获取每一行的单元格
cell1=sheet.getCell(0,i);//(列,行)
cell2=sheet.getCell(1,i);
cell3=sheet.getCell(2,i);
cell4=sheet.getCell(3,i);//(列,行)
cell5=sheet.getCell(4,i);
cell6=sheet.getCell(5,i);
cell7=sheet.getCell(6,i);//(列,行)
cell8=sheet.getCell(7,i);
cell9=sheet.getCell(8,i);
cell10=sheet.getCell(9,i);//(列,行)
cell11=sheet.getCell(10,i);
cell12=sheet.getCell(11,i);
cell13=sheet.getCell(12,i);//(列,行)
cell14=sheet.getCell(13,i);
cell15=sheet.getCell(14,i);
cell16=sheet.getCell(15,i);//(列,行)
if("".equals(cell1.getContents())==true) //如果读取的数据为空
break;
System.out.println(cell1.getContents()+"\t"+cell2.getContents()+"\t"+cell3.getContents()+"\t");
try {
// 连接数据库
@SuppressWarnings("unused")
boolean rs;
Class.forName(DRIVER);
Connection conn;
conn = (Connection) DriverManager.getConnection(URL,USERNAME,USERPASSWORD);
String sql = "insert into stu (身份证号码,准考证号,姓名,性别,出生日期,文化程度,职业(工种)名称,鉴定级别,理论成绩,实操成绩,外语成绩,综合成绩,评定成绩,证书编号,颁证日期,鉴定机构名称) values('"+cell1.getContents()+"','"+cell2.getContents()+"','"+cell3.getContents()+"','"+cell4.getContents()+"','"+cell5.getContents()+"','"+cell6.getContents()+"','"+cell7.getContents()+"','"+cell8.getContents()+"','"+cell9.getContents()+"','"+cell10.getContents()+"','"+cell11.getContents()+"','"+cell12.getContents()+"','"+cell13.getContents()+"','"+cell14.getContents()+"','"+cell15.getContents()+"','"+cell16.getContents()+"')"; // 根据业务需求组织sql语句
PreparedStatement ps = conn.prepareStatement(sql);
rs = ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
i++;
}
book.close();
}
catch(Exception e) { }
}
}