jxl.jar是通过java操作excel表格的工具类库,是由java语言开发而成的。
链接:https://pan.baidu.com/s/1KMh_VnYxYg3UYK4gozBQ0w https://pan.baidu.com/s/1KMh_VnYxYg3UYK4gozBQ0w
提取码:65v0
然后新建一个java类
package Second;
import java.io.File;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import jxl.Cell;
import sample.dao.DBhepler;
import sample.character.user1;
import jxl.Sheet;
import jxl.Workbook;
public class UserService {
//查询指定目录中电子表格中所有的数据
public static List<user1> getAllByExcel(String file) throws Exception{
List<user1> list = new ArrayList<user1>();
//获取excel的文件
Workbook rwb = Workbook.getWorkbook(new File(file));
Sheet rs = rwb.getSheet("Sheet1");
int clos = rs.getColumns();//得到所有的列
int rows = rs.getRows(); //得到所有的行
//打印出一共有几行几列
System.out.println("列数:" + clos + "行数:" + rows);
//循环读取数据
for (int i = 1; i < rows; i++) {
for (int j = 0; j < clos; j++) {
Cell cell = rs.getCell(j, i);
System.out.println(cell.getContents() + "\t");
String uNo = rs.getCell(j++, i).getContents();
String uName = rs.getCell(j++, i).getContents();
String uSex = rs.getCell(j++, i).getContents();
String uPhone = rs.getCell(j++, i).getContents();
String uPassword = rs.getCell(j++,i).getContents();
String dname = rs.getCell(j++, i).getContents();
String dno = rs.getCell(j++, i).getContents();
System.out.println("uNo:" + uNo + "uName:" + uName + "uSex:" + uSex +
"uPhoneN:" + uPhone + "uPassword:" + uPassword + "dname:" + dname + "dno:" + dno);
user1 user1 = new user1();
user1.setUNo(uNo);
user1.setUName(uName);
user1.setUSex(uSex);
user1.setUPhoneN(uPhone);
user1.setUPassword(uPassword);
user1.setDname(dname);
user1.setDno(dno);
list.add(user1);
}
}
return list;
}
//通过id判断是否存在
public static boolean isExist(String id) {
try {
//DBhepler新建的一个数据库工具类
DBhepler db=new DBhepler();
ResultSet rs=db.Search("select * from user1 where uNo =? ", new String[] {id+""});
if (rs.next()) {
return true;
}
} catch (Exception e) {
// TODO: handle exception
}
return false;
}
}
关于DBhepler类,为执行数据库实现对excel表格的更新的工具类
package sample.dao;
import sample.util.DbUtil;
import java.sql.*;
/*
* excel表导入
* */
public class DBhepler {
Connection con;
ResultSet rs = null;
DbUtil dbUtil=new DbUtil();
//查询
public ResultSet Search(String sql,String str[]) {
try {
try {
con=dbUtil.getCon();
} catch (Exception e) {
e.printStackTrace();
}
PreparedStatement pst=con.prepareStatement(sql);
if (str!=null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i+1, str[i]);
}
}
rs=pst.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
//增删修改
public int AddU(String sql,String str[]) {
int a=0;
try { con=dbUtil.getCon();
PreparedStatement pst=con.prepareStatement(sql);
if (str!=null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i+1, str[i]);
}
}
a=pst.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
}
return a;
}
}
如果数据库中存在与excel相同的学号,则更新,如果没有存在的学号就添加 相关的学生信息