importexcelservlet.java

学生考勤管理系统————分为课程选择

package com.ccniit.kaoqin.servlet;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;


import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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 com.ccniit.kaoqin.db.classes.Classes;
import com.ccniit.kaoqin.db.classes.ClassesDAO;
import com.ccniit.kaoqin.db.data.DataSource;
import com.ccniit.kaoqin.db.student.Student;
import com.ccniit.kaoqin.db.student.StudentDAO;
import com.ccniit.kaoqin.db.teacher.Teacher;
import com.ccniit.kaoqin.db.teacher.TeacherDAO;

@SuppressWarnings("serial")
public class ImportExcelServlet extends HttpServlet {
	
	
/**
 * 根据路径得到班级的信息导入到数据库
 * @param filePath
 * @return
 */
	// 根据班级来解析
	@SuppressWarnings("deprecation")
	public boolean importClass(String filePath) {

		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		ClassesDAO classesDAO = null;
		Classes classes = null;
		try {
			fs = new POIFSFileSystem(new FileInputStream(filePath));
			wb = new HSSFWorkbook(fs);

			HSSFSheet sheet = wb.getSheetAt(0);
			HSSFRow row;
			HSSFCell cell;

			int rowNum;

			// 得到表中的列数

			// System.out.println(rowNum);
			classes = new Classes();
			classesDAO = new ClassesDAO();
			rowNum = sheet.getLastRowNum();
			//ArrayList classess=classesDAO.getAllClasses();
			for (int i = 1; i < rowNum + 1; i++) {

				row = sheet.getRow(i);
				cell = row.getCell((short) 0);
				classes.setClass_NO(cell.getStringCellValue());
				// System.out.println(cell.getStringCellValue());
				cell = row.getCell((short) 1);
				classes.setClass_name(cell.getStringCellValue());
				// System.out.println(cell.getStringCellValue());
				cell = row.getCell((short) 2);
				classes.setClass_grade(cell.getStringCellValue());
				// System.out.println(cell.getStringCellValue());
				
				
				
					classesDAO.addClassess(classes);
				
				

			}
		} catch (FileNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (classesDAO != null)
					classesDAO.release();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
				return false;
			}
		}

		return true;

	}
	
	/**
	 * 根据路径获得.XLS文件来解析导入学生
	 * @param filePath
	 * @return
	 */
@SuppressWarnings("deprecation")
public 	boolean importStudent(String filePath){
	
	POIFSFileSystem fs = null;
	HSSFWorkbook wb = null;
	Student student=null;
	StudentDAO studentDAO=null;
	
	try {
		fs = new POIFSFileSystem(new FileInputStream(filePath));
		wb = new HSSFWorkbook(fs);

		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row;
		HSSFCell cell;

		int rowNum;
		rowNum = sheet.getLastRowNum();
		student=new Student();
		studentDAO=new StudentDAO();
		for (int i = 1; i < rowNum + 1; i++) {

			row = sheet.getRow(i);
			cell = row.getCell((short) 0);
			
				student.setStudent_NO(cell.getStringCellValue());
				
			
			cell = row.getCell((short) 1);
			student.setStudent_name(cell.getStringCellValue());
			cell = row.getCell((short) 2);
			student.setStudent_sex(cell.getStringCellValue());
			cell = row.getCell((short) 3);
			student.setStudent_room(cell.getStringCellValue());
			cell = row.getCell((short) 4);
		    student.setStudent_phone(cell.getStringCellValue());
				
			cell = row.getCell((short) 5);
			student.setStudent_telPhone(cell.getStringCellValue());
			cell = row.getCell((short) 6);
			student.setStudent_email(cell.getStringCellValue());
			cell = row.getCell((short) 7);
			student.setStudent_password(cell.getStringCellValue());
			cell = row.getCell((short) 8);
			student.setClass_id(Integer.parseInt(cell.getStringCellValue()));
			
			studentDAO.addStudent(student);

		}
	} catch (NumberFormatException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	} catch (FileNotFoundException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	} catch (IOException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	} catch (ClassNotFoundException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	} catch (SQLException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	}finally{
	try {
		if(studentDAO!=null)
			studentDAO.release();
	} catch (SQLException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	}
		
	
	}
	
	
	return true;
	
}
	
	
@SuppressWarnings("deprecation")
public 	boolean importTeacher(String filePath){
	
	POIFSFileSystem fs = null;
	HSSFWorkbook wb = null;
	Teacher teacher=null;
	TeacherDAO teacherDAO=null;
	
	try {
		fs = new POIFSFileSystem(new FileInputStream(filePath));
		wb = new HSSFWorkbook(fs);

		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row;
		HSSFCell cell;

		int rowNum;
		rowNum = sheet.getLastRowNum();
		teacher=new Teacher();
		teacherDAO=new TeacherDAO();
		
		for (int i = 1; i < rowNum + 1; i++) {

			row = sheet.getRow(i);
			cell = row.getCell((short) 0);
			teacher.setTeacher_NO(cell.getStringCellValue());
			cell = row.getCell((short) 1);
			teacher.setTeacher_name(cell.getStringCellValue());
			cell = row.getCell((short) 2);
		    teacher.setTeacher_sex(cell.getStringCellValue());
			cell = row.getCell((short) 3);
			teacher.setTeacher_office(cell.getStringCellValue());
			cell = row.getCell((short) 4);
		    teacher.setTeacher_phone(cell.getStringCellValue());
			cell = row.getCell((short) 5);
			teacher.setTeacher_telPhone(cell.getStringCellValue());
			cell = row.getCell((short) 6);
			teacher.setTeacher_email(cell.getStringCellValue());
			cell = row.getCell((short) 7);
			teacher.setTeacher_password(cell.getStringCellValue());
			
			
			teacherDAO.addTeacher(teacher);

		}
	} catch (NumberFormatException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	} catch (FileNotFoundException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	} catch (IOException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	}catch (ClassNotFoundException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	} catch (SQLException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	} finally{
	try {
		if(teacherDAO!=null)
			teacherDAO.release();
	} catch (SQLException e) {
		// TODO 自动生成 catch 块
		e.printStackTrace();
		return false;
	}
		
	
	}
	
	
	return true;
	
}
	
	
	
	
	
	
	
	
	
	

	/**
	 * The doGet method of the servlet. <br>
	 * 
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request
	 *            the request send by the client to the server
	 * @param response
	 *            the response send by the server to the client
	 * @throws ServletException
	 *             if an error occurred
	 * @throws IOException
	 *             if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		String functionStr = request.getPathInfo();

		if (functionStr.equals("/OpenMain")) {
			String successPage = "/importdata/Excel.jsp";
			request.getRequestDispatcher(successPage)
					.forward(request, response);

		} else if (functionStr.equals("/AddMoreClass")) {
			String successPage = "OpenMain";
			String filepath = request.getParameter("class");
			boolean inportClassSuccess = importClass(filepath);
			
			if (inportClassSuccess == false) {
				request.setAttribute("message", "导入班级失败");
			} else if (inportClassSuccess == true) {
				request.setAttribute("message", "导入班级成功");
			}

			request.getRequestDispatcher(successPage)
					.forward(request, response);

		} else if (functionStr.equals("/AddMoreStudent")) {
			String successPage = "OpenMain";
			String filepath = request.getParameter("student");
			
			
			boolean importStudentSuccess=importStudent(filepath);
			if (importStudentSuccess == false) {
				request.setAttribute("message1", "导入学生失败");
			} else if (importStudentSuccess == true) {
				request.setAttribute("message1", "导入学生成功");
			}
			
			request.getRequestDispatcher(successPage)
					.forward(request, response);

		} else if (functionStr.equals("/AddMoreTeacher")) {
			String successPage = "OpenMain";
			String filepath = request.getParameter("teacher");
               boolean importTeacherSuccess=importTeacher(filepath);
               if (importTeacherSuccess == false) {
   				request.setAttribute("message2", "导入教师失败");
   			} else if (importTeacherSuccess == true) {
   				request.setAttribute("message2", "导入教师成功");
   			}
			request.getRequestDispatcher(successPage)
					.forward(request, response);

		}else if(functionStr.equals("/DBDelete")){
			
			String successPage = "OpenData";
			
			String sqlStr=request.getParameter("sql");
			DataSource data=new DataSource();
			boolean dataresult=data.exeupdate(sqlStr);
			
			if(dataresult==true){
				request.setAttribute("message", "SQL语句执行成功");
				
			}else if(dataresult==false){
				request.setAttribute("message", "SQL语句有错误");
			}
			request.getRequestDispatcher(successPage)
			.forward(request, response);

			
		}else if(functionStr.equals("/OpenData")){
			String successPage = "/importdata/datasource.jsp";
			request.getRequestDispatcher(successPage)
			.forward(request, response);
			
		}

	}

	/**
	 * The doPost method of the servlet. <br>
	 * 
	 * This method is called when a form has its tag value method equals to
	 * post.
	 * 
	 * @param request
	 *            the request send by the client to the server
	 * @param response
	 *            the response send by the server to the client
	 * @throws ServletException
	 *             if an error occurred
	 * @throws IOException
	 *             if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		request.setCharacterEncoding("gb2312");
		this.doGet(request, response);
	}

}