jsjexcel.java

JSP开发的学生信息管理系统

package excel;
import java.io.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
import mypackage.DataBaseConnection;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class JsjExcel implements Serializable
{
	private Connection con=null;
	Statement stmt=null;
	ResultSet rst=null;
	String sql="";
	String str="";
	Cell cell=null;
	public JsjExcel()
	{
		try
		{
			this.con=DataBaseConnection.getConnection();
			stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		}
		catch(SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
	}
		
	//导入Excell
	public int addExcel(String path)
	{
		try
		{
			File f=new File(path);
			InputStream fs=new FileInputStream(f);
			Workbook wb=Workbook.getWorkbook(fs);
			Sheet sh=wb.getSheet(0);
			if(sh.getColumns()>5)
				{
					System.out.print("Excel中列数超过了数据库字段值,操作已中止!");
					return 2;
				}
				if(sh.getColumns()<5)
				{
					System.out.print("Excel中列数少于数据库字段值,操作已中止!");
					return 3;
				}
			for(int i=0;i<sh.getRows();i++)
			{
				if(Exist(sh.getCell(0,i).getContents())!=1)
					{
						return 4;
					}//判断是否存在此学生
				for(int j=0;j<sh.getColumns();j++)
				{
					cell=sh.getCell(j,i);
					if(j!=sh.getColumns()-1)
					{
						str+="'"+cell.getContents()+"',";//excel中列
					}
					else
					{
						str+="'"+cell.getContents()+"'";
					}
				}
				sql="insert into jsjDengji values("+str+")";
				stmt.executeUpdate(sql);
				str="";
			}
			try{	
  	    		 stmt.close();
  	    		 con.close();
  	    		 wb.close();
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    		
		}catch (IOException e)
		 {
            e.printStackTrace();
            return 0;
         }
         catch(BiffException ex)
         {
            ex.printStackTrace();
            return 0;
         }
         catch(SQLException sqlE)
         {
         	sqlE.printStackTrace();
         	return 10;
         }      
		return 1;
	}
	public int Exist(String studentId)
	{
		try{
			rst=stmt.executeQuery("select * from xuesheng where studentId='"+studentId+"'");
			if(rst.next())
			{
				return 1;
			}
			}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		return 0;
  	    	}
  	    	try{
  	    		rst.close();
  	    	}
  	    	catch(SQLException sqlE)
         	{
         		sqlE.printStackTrace();
         		return 0;
        	 } 
  	    	return 0;
	}
}