window7 下使用java从excel中读数据导入到sqlserver 2005

import java.lang.reflect.*;
import java.util.List;
import java.util.Map;
import java.util.regex.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import java.sql.*;


public class Main {
	 private static Connection conn;
	 private static Statement stmt;
	 private static String url = "jdbc:sqlserver://192.168.0.99:1433;DatabaseName=dormitoryManagementSys";
	 private static String classforname = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	 private static String uid = "dmsys";
	 private static String pwd = "000000";
	 
	private static void insert(String sql){
		try{
			stmt.executeQuery(sql);
		}catch(SQLException ex)
		{			
			System.out.println(sql + "insert execute error!");
		}
	}
	private static void insertStudent(){
		try{
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:/work/dms/11.xls"));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);
			
			HSSFRow row = null;
			String sql;
			for(int i=1;i<=sheet.getLastRowNum();i++){
				
				row = sheet.getRow(i);
				HSSFCell cell = row.getCell(2);			 
				cell.setCellType(cell.CELL_TYPE_STRING);
				HSSFRichTextString richStr = cell.getRichStringCellValue();
				String name = richStr.toString();
				
				cell = row.getCell(12);
				cell.setCellType(cell.CELL_TYPE_STRING);
				richStr = cell.getRichStringCellValue();
				Integer departmentId = Integer.parseInt(richStr.toString());
				
				cell = row.getCell(0);
				cell.setCellType(cell.CELL_TYPE_STRING);
				richStr = cell.getRichStringCellValue();
				String examId = richStr.toString();
				
				cell = row.getCell(1);
				cell.setCellType(cell.CELL_TYPE_STRING);
				richStr = cell.getRichStringCellValue();
				String identyId = richStr.toString();
				
				cell = row.getCell(8);
				cell.setCellType(cell.CELL_TYPE_STRING);
				richStr = cell.getRichStringCellValue();
				String sex = "m";
				if(richStr.toString() == "2")
					sex = "w";
				
				sql = "insert into student(exam_num,identy_num,name,department_id,sex) values('"+ examId +"','" + identyId + "','"+ name + "'," + departmentId + ",'" + sex + "')";
				insert(sql);
			}
		}catch(IOException e){
			
		}
	}
	private static void insertDepartment(){
		try{
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:/work/dms/yxdm.xls"));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);
			
			HSSFRow row = null;
			String sql;
			for(int i=1;i<=sheet.getLastRowNum();i++){
				
				row = sheet.getRow(i);
				HSSFCell cell = row.getCell(0);			 
				cell.setCellType(cell.CELL_TYPE_STRING);
				HSSFRichTextString richStr = cell.getRichStringCellValue();
				String name = richStr.toString();
				
				cell = row.getCell(1);
				cell.setCellType(cell.CELL_TYPE_STRING);
				richStr = cell.getRichStringCellValue();
				Integer departmentId = Integer.parseInt(richStr.toString());			
				sql = "insert into department(name,id) values('"+ name + "'," + departmentId + ")";
				insert(sql);
			}
		}catch(IOException e){
			
		}
	}
	public static void main(String []args){		
		System.out.println("test");
		try{
		 Class.forName(classforname);
		}catch(ClassNotFoundException  ex){			
		}
		try{
			conn = DriverManager.getConnection( url, uid, pwd);
			stmt = conn.createStatement();
		}catch(SQLException ex){
			 System.out.println("connecttion error!");
			 ex.printStackTrace();
			return;
		}	
		insertStudent();
	}
}

 

首先：安装 jdbc驱动 可以去微软上下载，具体的版本要跟操作体统匹配（window 7）下要使用3.0版

其次，解压下载的驱动到\Program Files (x86)目录

再次，在eclipse中添加这个jar

 

注 :1.因为SQLexpress服务器默认是禁用的并且端口号没有配置,所以要进行重新设置
2.如果你以前用java连接sql server 2000的话就要注意了:
在sql server 2000 中加载驱动和URL路径的语句是
String driverName = "com.microsoft.jdbc .sqlserver .SQLServerDriver";
String dbURL = "jdbc :microsoft :sqlserver ://localhost:1433; DatabaseName=rs";
而sql server 2005 中加载驱动和url的语句则为
String driverName = "com.microsoft.sqlserver .jdbc .SQLServerDriver";
String dbURL = "jdbc:sqlserver ://localhost:1433; DatabaseName=rs";
如果写法错误将会找不到驱动.

sqlserver数据库：java连接sqlserver2005数据库心得体会

首先得下载驱动程序到微软网站下载Microsoft SQL Server 2005 JDBC Driver 1.2　　解压Microsoft SQL Server 2005 jdbc driver1.2.exe