上传xls文件并解析数据存入msyql数据库

 

1－文件上传类

 

package com.tobebest_en.servlet;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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

import org.apache.commons.fileupload.DiskFileUpload;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileUpload;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.RequestContext;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.servlet.ServletRequestContext;

import com.sun.org.apache.xerces.internal.parsers.JAXPConfiguration;
import com.tobebest_en.commons.XlsSQL;

public class UploadXLS extends HttpServlet {

  private String uploadpath = "c://upload//"; // 用于存放上传文件的目录
  private String temppath = "c://upload//tmp//"; // 用于存放临时文件的目录
 /**
  * Constructor of the object.
  */
 public UploadXLS() {
  super();
 }

 /**
  * Destruction of the servlet. <br>
  */
 public void destroy() {
  super.destroy(); // Just puts "destroy" string in log
  // Put your code here
 }

 /**
  * 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 {

  doPost(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 {

  FileItemFactory factory = new DiskFileItemFactory();
  ServletFileUpload upload = new ServletFileUpload(factory);
  Iterator items;
  try {
  items = upload.parseRequest(request).iterator();
  while(items.hasNext()){
  FileItem item = (FileItem) items.next();
  if(!item.isFormField()){

  String name = item.getName();
  System.out.println(name);
  String filename = name.substring(name.lastIndexOf("//")+1);
  System.out.println(filename);
  String realpath = request.getRealPath("/upload");
  String path = realpath+File.separatorChar+filename;
  System.out.println(realpath);
  System.out.println(path);
  File file = new File(path);
  item.write(file);

  response.setContentType("text/html");
  response.setCharacterEncoding("gb2312");
  PrintWriter out = response.getWriter();
  out.print("<font size='2'>fileName："+name+"<br>");
  out.print("save path："+path+"</font>");

  XlsSQL xls = new XlsSQL(filename,path);
  int result = xls.insertXls();
  System.out.println("success !");
  }

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

 

 }

 /**
  * Initialization of the servlet. <br>
  *
  * @throws ServletException if an error occurs
  */
 public void init() throws ServletException {
  // Put your code here
  if(!new File(uploadpath).isDirectory())
         new File(uploadpath).mkdirs();
     if(!new File(temppath).isDirectory())
         new File(temppath).mkdirs();

 }

}

 

2－解析xls数据存入vo对象

package com.tobebest_en.commons;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.tobebest_en.util.DBUtil;
import com.tobebest_en.vo.ResourceInfo;

public class XlsSQL {
 private static String path = null;
 private static String name =null;
 public XlsSQL(String name,String path)
 {
  this.name = name;
  this.path = path;
 }

 public static int insertXls() {
  int result = 0;
  PreparedStatement ps = null;
  Connection conn = DBUtil.getConn();
  List list = XlsSQL.getVo();
  Iterator iter = list.iterator();
  String sql = null;
  try {

   while (iter.hasNext()) {
    sql = "insert into en_resources(gtId, rank_id, userId, resource_name, resource_url, resource_desc, resource_image, resource_time, resource_state, resource_link_image, ticked_number, Published, end_date, state_date, onclick_count, resource_website, resource_ticketing, resource_email, resource_extra, maximum_subnumber, grade_count, Submmission_email, email_message,sub_type_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    ps = (PreparedStatement) conn.prepareStatement(sql);
    ResourceInfo resourceinfo = (ResourceInfo) iter.next();
    ps.setInt(1, resourceinfo.getGtId());
    ps.setInt(2, resourceinfo.getRankId());

    ps.setInt(3, resourceinfo.getUserid());
    ps.setString(4, resourceinfo.getResourceName());
    ps.setString(5, resourceinfo.getResourceUrl());
    ps.setString(6, resourceinfo.getResourceDesc());
    ps.setString(7, resourceinfo.getResourceImage());
    Date dat = new Date();
    String today = String.valueOf(dat.getDate())
      + "/"
      + String.valueOf((dat.getMonth() + 1))
      + "/"
      + String.valueOf((dat.getYear() + 1900)
        + " "
        + String.valueOf((dat.getHours()) + ":"
          + String.valueOf((dat.getMinutes()))));
    ps.setString(8, today);
    ps.setInt(9, resourceinfo.getResourceState());

    ps.setString(10, resourceinfo.getResourceLinkImage());

    ps.setInt(11, resourceinfo.getTicketNumber());
    ps.setInt(12, resourceinfo.getIsPublished());
    ps.setString(13, resourceinfo.getEndDate());
    ps.setString(14, resourceinfo.getStartDate());
    ps.setDouble(15, resourceinfo.getOnclickCount());
    ps.setString(16, resourceinfo.getResourceWebsite());
    ps.setString(17, resourceinfo.getResourceTicketing());
    ps.setString(18, resourceinfo.getResourceEmail());
    ps.setString(19, resourceinfo.getResourceExtra());

    ps.setInt(20, resourceinfo.getSubNumber());
    ps.setInt(21, resourceinfo.getGradeCount());
    ps.setString(22, resourceinfo.getSubmmission_email());
    ps.setString(23, resourceinfo.getEmail_message());
    ps.setInt(24, resourceinfo.getSubTypeId());
    result = ps.executeUpdate();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return result;
 }

 public static List getVo() {

  Workbook book = null;
  try {
   book = Workbook.getWorkbook(new File(path));
  } catch (BiffException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  Sheet se = book.getSheet(0);// 得到第1个sheet(Excel通常会有3个Sheet)
  int rownum = se.getRows(); // 得到总行数
  // Cell ce = null;
  // StringBuffer su = new StringBuffer();
  ArrayList list = new ArrayList();
  Statement stmt = null;
  ResourceInfo addressInfo = null;

  for (int i = 2; i < rownum; i++) {
   addressInfo = new ResourceInfo();
   if (se.getCell(0, i).getContents().length()==0) {
    addressInfo.setGtId(Integer.parseInt(se.getCell(0, i)
      .getContents()));
   } else {
    addressInfo.setGtId(1);
   }

   if (se.getCell(1, i).getContents() != null) {
    addressInfo.setRankId(Integer.parseInt(se.getCell(1, i)
      .getContents()));
   } else {
    addressInfo.setRankId(0);
   }

   if (se.getCell(2, i).getContents() != null
     || !("".equals(se.getCell(2, i).getContents()))) {
    addressInfo.setUserid(Integer.parseInt(se.getCell(3, i)
      .getContents()));
   } else {
    addressInfo.setUserid(1);
   }

   if (se.getCell(3, i).getContents() != null) {
    addressInfo.setUserid(Integer.parseInt(se.getCell(3, i)
      .getContents()));
   } else {
    addressInfo.setUserid(5);
   }

   if (se.getCell(4, i).getContents() != null) {
    addressInfo.setSubTypeId(Integer.parseInt(se.getCell(4, i)
      .getContents()));
   } else {
    addressInfo.setSubTypeId(1);
   }
   if (se.getCell(5, i).getContents() != null) {
    addressInfo.setResourceName(se.getCell(5, i).getContents());
   } else {
    addressInfo.setResourceName("");
   }
   if (se.getCell(6, i).getContents() != null) {
    addressInfo.setResourceUrl(se.getCell(6, i).getContents());
   } else {
    addressInfo.setResourceUrl("");
   }
   if (se.getCell(7, i).getContents() != null) {
    addressInfo.setResourceDesc(se.getCell(7, i).getContents());
   } else {
    addressInfo.setResourceDesc("");
   }

   if (se.getCell(8, i).getContents() != null) {
    addressInfo.setResourceImage(se.getCell(8, i).getContents());
   } else {
    addressInfo.setResourceImage("");
   }

   Date dat = new Date();
   String today = String.valueOf(dat.getDate())
     + "/"
     + String.valueOf((dat.getMonth() + 1))
     + "/"
     + String.valueOf((dat.getYear() + 1900)
       + " "
       + String.valueOf((dat.getHours()) + ":"
         + String.valueOf((dat.getMinutes()))));
   if (se.getCell(9, i).getContents() != null) {
    addressInfo.setResourceTime(se.getCell(9, i).getContents());
   } else {
    addressInfo.setResourceTime(today);
   }

   if (se.getCell(10, i).getContents() != null) {
    addressInfo.setResourceState(Integer.parseInt(se.getCell(10, i)
      .getContents()));
   } else {
    addressInfo.setResourceState(3);
   }

   if (se.getCell(11, i).getContents() != null) {
    addressInfo.setResourceLinkImage(se.getCell(11, i)
      .getContents());
   } else {
    addressInfo.setResourceLinkImage("");
   }

   if (se.getCell(12, i).getContents() != null) {
    addressInfo.setTicketNumber(Integer.parseInt(se.getCell(12, i)
      .getContents()));
   } else {
    addressInfo.setTicketNumber(1);
   }

   if (se.getCell(13, i).getContents() != null) {
    addressInfo.setIsPublished(Integer.parseInt(se.getCell(13, i)
      .getContents()));
   } else {
    addressInfo.setIsPublished(0);
   }

   if (se.getCell(14, i).getContents() != null) {
    addressInfo.setEndDate(se.getCell(14, i).getContents());
   } else {
    addressInfo.setEndDate(today);
   }
   if (se.getCell(15, i).getContents() != null) {
    addressInfo.setStartDate(se.getCell(15, i).getContents());
   } else {
    addressInfo.setStartDate(today);
   }

   if (se.getCell(16, i).getContents() != null) {
    addressInfo.setOnclickCount(Integer.parseInt(se.getCell(16, i)
      .getContents()));
   } else {
    addressInfo.setOnclickCount(1);
   }
   if (se.getCell(17, i).getContents() != null) {
    addressInfo.setResourceWebsite(se.getCell(17, i).getContents());
   } else {
    addressInfo.setResourceWebsite("");
   }
   if (se.getCell(18, i).getContents() != null) {
    addressInfo.setResourceTicketing(se.getCell(18, i)
      .getContents());
   } else {
    addressInfo.setResourceTicketing("1");
   }

   if (se.getCell(19, i).getContents() != null) {
    addressInfo.setResourceEmail(se.getCell(19, i).getContents());
   } else {
    addressInfo.setResourceEmail("");
   }
   if (se.getCell(20, i).getContents() != null) {
    addressInfo.setResourceExtra(se.getCell(20, i).getContents());
   } else {
    addressInfo.setResourceExtra("");
   }
   if (se.getCell(21, i).getContents() != null) {
    addressInfo.setSubNumber(Integer.parseInt(se.getCell(21, i)
      .getContents()));
   } else {
    addressInfo.setSubNumber(1);
   }
   if (se.getCell(22, i).getContents() != null) {
    addressInfo.setGradeCount(Integer.parseInt(se.getCell(22, i)
      .getContents()));
   } else {
    addressInfo.setGradeCount(1);
   }
   if (se.getCell(22, i).getContents() != null) {
    addressInfo.setGradeCount((Integer.parseInt(se.getCell(23, i)
      .getContents())));
   } else {
    addressInfo.setGradeCount(1);
   }

   if (se.getCell(24, i).getContents() != null) {
    addressInfo.setSubmmission_email(se.getCell(24, i)
      .getContents());
   } else {
    addressInfo.setSubmmission_email("");
   }

   if (se.getCell(25, i).getContents() != null) {
    addressInfo.setEmail_message(se.getCell(25, i).getContents());
   } else {
    addressInfo.setEmail_message("");
   }

   list.add(addressInfo);
  }
  book.close();
  return list;
 }
}