JAVA从Excel中读取数据保存到数据库中

1.jar包

2.数据库信息

3.JDBC连接数据库工具类

package Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBhepler {
	String url ="jdbc:mysql://192.168.0.109:3306/javenforexcel?useUnicode=true&characterEncoding=utf-8";

	String user="root";
	String pwd="123456";
	Connection conn=null;
	ResultSet rs=null;
	
	public void DataBase() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection(url, user, pwd);
		} catch (ClassNotFoundException e) {
			System.out.println("装载JDBC 驱动程序失败");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("无法连接数据库");
			e.printStackTrace();
		}
	}
	
	//查询
	public ResultSet Search(String sql,String str[]) {
		DataBase();
		
		try {
			PreparedStatement pst=conn.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;
		DataBase();
		try {
			PreparedStatement pst=conn.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;
		
		
	}
	
}

4.读取Excel表的工具类

package service;

import java.io.File;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import Test.DBhepler;
import entity.StuEntity;
import jxl.Sheet;
import jxl.Workbook;

public class StuService {
	
	//查询指定目录中电子表格中所有的数据
	
	public static List<StuEntity> getAllByExcel(String file){
		List<StuEntity>list =new ArrayList<StuEntity>();
		
		try {
			Workbook rwb=Workbook.getWorkbook(new File(file));
			Sheet rs=rwb.getSheet(0);
			
			int clos=rs.getColumns();//得到所有的列
			int rows=rs.getRows();   //得到所有的行
			System.out.println("列数："+clos+"行数:"+rows);
			
			
			for (int i = 1; i < clos; i++) {
				for (int j = 0; j < rows; j++) {
					
					String id=rs.getCell(j++,i).getContents();
					String name=rs.getCell(j++,i).getContents();
					String sex=rs.getCell(j++,i).getContents();
					String num=rs.getCell(j++,i).getContents();
					
					System.out.println("id:"+id+"name:"+name+"sex:"+sex+"num:"+num);
					StuEntity entity=new StuEntity();
					entity.setId(Integer.parseInt(id));
					entity.setName(name);
					entity.setSex(sex);
					entity.setNum(Integer.parseInt(num));
					list.add(entity);
					
					
				}
				
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		return list;
		
		
	}
	
	//通过id判断是否存在
	
	public static boolean isExist(int id) {
		
		try {
			DBhepler db=new DBhepler();
			ResultSet rs=db.Search("select * from stu where id =? ", new String[] {id+""});
			if (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return false;
	}

}

5.将读取到的Excel的数据保存到数据库，相同的就更新，不同的就添加

package excel;

import java.util.List;

import Test.DBhepler;
import entity.StuEntity;
import service.StuService;

public class TestExcelToDb {
	public static void main(String[] args) {
		//得到表格中所有的数据
		List<StuEntity>listExcel=StuService.getAllByExcel("d:/book.xls");
		
		
		DBhepler db=new DBhepler();
		
		for (StuEntity stuEntity : listExcel) {
			int id=stuEntity.getId();
			if (!StuService.isExist(id)) {
				//不存在就添加
				String sql="insert into stu(name,sex,num)values(?,?,?)";
				String[] str=new String[] {stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};
				db.AddU(sql, str);
				
			}else {
				//存在就更新
				String sql="update stu set name=?,sex=?,num=? where id=?";
				String[] str=new String[] {stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""};
				db.AddU(sql, str);
			}
		}
		
	}
	
	

}