201906G110/POI/ExcelOutDB.java

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;

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;
 
public class ExcelOutDB {	
public static void main(String[] args) throws Exception {
    //文件流指向excel文件
    FileInputStream fin=new FileInputStream("C:\\Users\\ASUS\\Desktop\\stu.xls");
	try {
		HSSFWorkbook workbook = new HSSFWorkbook(fin);
		 HSSFSheet sheet=workbook.getSheetAt(0);   //得到工作表
		    HSSFRow row=null;                      //对应excel的行
		    HSSFCell cell=null;                    //对应excel的列
		    int totalRow=sheet.getLastRowNum();    //得到excel的总记录条数
		 
		    String user="";
		    String name="";
		    String password="";
		    String sex="";
		    String national="";
		    String telephone="";
		    String age="";
		    
		    Connection con = null;       //数据库连接
			 DBConnection db = new DBConnection();  
			 con = db.getDB();  
		    String sql="insert into student(user,name,password,sex,national,telephone,age) values(?,?,?,?,?,?,?)";
		    
		    //利用循环把数据导入数据库中
		    for(int i=1;i<=totalRow;i++){
		     row=sheet.getRow(i);
		     cell=row.getCell((short) 0);
		     user=cell.getRichStringCellValue().toString();
		     cell=row.getCell((short) 1);
		     name=cell.getRichStringCellValue().toString();
		     cell=row.getCell((short) 2);
		     password=cell.getRichStringCellValue().toString();
		     cell=row.getCell((short) 3);
		     sex=cell.getRichStringCellValue().toString();
		     cell=row.getCell((short) 4);
		     national=cell.getRichStringCellValue().toString();
		     cell=row.getCell((short) 5);
		     telephone=cell.getRichStringCellValue().toString();
		     cell=row.getCell((short) 6);
		     
		     
		     PreparedStatement pst=con.prepareStatement(sql);
		     pst.setString(1,user);
		     pst.setString(2,name);
		     pst.setString(3,password);
		     pst.setString(4,sex);
		     pst.setString(5,national);
		     pst.setString(6,telephone);
		     pst.setString(7,age);		     
		     pst.execute();		     
		    }
		    System.out.println("导入成功");
		       
	} catch (IOException e) {
		// TODO 自动生成的 catch 块
		e.printStackTrace();
	}
   
}    
   
}