将Excel中的某些字段（在数据库中不存在的信息）导入数据库

最新推荐文章于 2021-06-07 17:05:16 发布

zudesalin

最新推荐文章于 2021-06-07 17:05:16 发布

阅读量576

点赞数

分类专栏： j2ee 文章标签： Excel某些字段在数据库中不存在的信息

本文链接：https://blog.csdn.net/zudesalin/article/details/8250157

版权

j2ee 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

AddStudentAction

---------------------------------------------------------------------------------------

/*
* Generated by MyEclipse Struts
* Template path: templates/java/JavaClass.vtl
*/
package sx.action;

import java.io.File;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.domin.student;

import ExcelServer.StudentExcel;

import servers.StudentInfo;
import sx.form.AddStudentForm;

public class AddStudentAction extends Action {
List<student> exitlist=null;
boolean exit=false;
public ActionForward execute(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response) {
  AddStudentForm addStudentForm = (AddStudentForm) form;// TODO Auto-generated method stub
  String path=addStudentForm.getPath();
  try {
   path=new String(path.getBytes("ISO-8859-1"),"gb2312");
   File file=new File(path);
   StudentInfo s=new StudentInfo();//自定义的方法
   StudentExcel se=new StudentExcel();
   List<student> list=se.input(file, request, response);

   for(int i=0;i<list.size();i++){
   student student=(student)list.get(i);
   exitlist=s.getStudentByid(student.getS_id());

   if(exitlist.size()==0){
    System.out.println("不存在的id="+student.getS_id()+student.getS_name());
    s.savestudent(student);
   }else{
    exit=true;
   }

   }
   request.setAttribute("deptList",request.getSession().getAttribute("deptList"));
   response.setContentType( "text/html;charset=GBK ");
   PrintWriter   out   =   response.getWriter();

   if(exit){
    out.print("<script>alert('由于部分学生信息已存在，我们只将未存在信息的添加进数据库');window.location.href='admin/addStudent.jsp'</script>");
   }else{
   out.print("<script>alert('添加成功');window.location.href='admin/addStudent.jsp'</script>");
   }

  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return null;
}
}

------------------------------------------------------------------------------------------------------------------------

studentExcel.java

------------------------------------------------------------------------------------------------------------

package ExcelServer;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;

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

import com.domin.student;

import jxl.Sheet;
import jxl.Workbook;

public class StudentExcel {
Workbook workbook;
List list=new ArrayList();
public List input(File file,HttpServletRequest request,HttpServletResponse response){
try {
   request.setAttribute("deptList",request.getSession().getAttribute("deptList"));
   response.setContentType( "text/html;charset=GBK ");
   PrintWriter   out   =   response.getWriter();
  InputStream input=new FileInputStream(file);
  workbook=workbook.getWorkbook(input);
  Sheet sheet=workbook.getSheet(0);
  int rows=sheet.getRows();
   if(rows<=1){

       out.print("<script>alert('您所添加的表是空表，请正确操作！');history.back();window.location.href='admin/addStudent.jsp';</script>");
        }else{
  for(int i=1;i<rows;i++){
   String cell1=sheet.getCell(0,i).getContents()+"";
   String cell2=sheet.getCell(1,i).getContents()+"";
   if(cell1!=null&&cell2!=""){
    student s=new student();
    s.setS_id(Integer.parseInt(cell1));
    s.setS_name(cell2);
    s.setGroup_id(0);
    s.setS_password("123");
    list.add(s);
   }
  }}
} catch (Exception e) {
  try {
   request.setAttribute("deptList",request.getSession().getAttribute("deptList"));
   response.setContentType( "text/html;charset=GBK ");
   PrintWriter out;

    out = response.getWriter();

  out.print("<script>alert('您所添加的表格式不对，请正确操作！');history.back();window.location.href='admin/addStudent.jsp';</script>");
  e.printStackTrace();
  } catch (IOException e1) {
   // TODO Auto-generated catch block
   e1.printStackTrace();
  }
}
return list;
}
}

--------------------------------------------------------------------------------------------

StudentInfo.java 自定义的2个方法

public List getStudentByid(int sid){
getsession();
List list=session.createQuery("from student where s_id="+sid).list();
return list;
}public void savestudent(student s){
session=util.HibernateSessionFactory.getSession();
session.beginTransaction();
      try{
       session.save(s);
       session.getTransaction().commit();
      }catch(Exception e){
e.printStackTrace();
session.getTransaction().rollback();
}finally{
session.close();
}

}