上传excel并存入数据库

上传:

定义上传类:

import java.io.File;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.fileupload.*;
import org.apache.commons.fileupload.disk.*;
import org.apache.commons.fileupload.servlet.*;

public class FileUpload {
 private String fileName = null;
 
 private String destPath = null;
 
 // 保存在上传路径中保存的文件名
 private String destFileName;

 private HttpServletRequest request;

 /**
  *
  * @param request 請求
  * @param fileName 指定上傳后的文件名
  * @param destPath 上傳后文件的保存路徑
  */
 public FileUpload(HttpServletRequest request, String fileName, String destPath) {
  this.request = request;
  this.fileName = fileName;
  this.destPath = destPath;
 }

 private boolean preFileUpload() throws Exception {
   boolean isMultipart = ServletFileUpload.isMultipartContent(request);//检查输入请求是否为multipart表单数据。

   // 是文件上传操作
   if (isMultipart)
   {
    FileItemFactory factory = new DiskFileItemFactory();//为该请求创建一个DiskFileItemFactory对象，通过它来解析请求。执行解析后，所有的表单项目都保存在一个List中。
    ServletFileUpload upload = new ServletFileUpload(factory);
   
    List<FileItem> items = upload.parseRequest(request);
         Iterator<FileItem> itr = items.iterator();
        
         while (itr.hasNext()) {
       FileItem item = (FileItem) itr.next();
       //忽略其他不是文件域的所有表单信息
       if (!item.isFormField()) {
        String name = item.getName();
        long size = item.getSize();
        if((name==null||name.equals("")) && size==0)
        {
        continue;
        }
         // 注意item.getName()
         // 会返回上载文件在客户端的完整路径名称，这似乎是一个BUG。
         // 为解决这个问题，这里使用了fullFile.getName()。
         name=name.replace('//','/');
        
         String fn = null;
         if (this.fileName == null || "".equals(this.fileName))
         {
          fn = name;
         }
         else
         {
          fn = this.fileName + name.substring(name.lastIndexOf('.'), name.length());
         }
      File fullFile = new File(fn);
         destFileName = destPath + System.getProperty("file.separator") + fullFile.getName();
         File savedFile = new File(destPath,fullFile.getName());
         item.write(savedFile);
       }
     }
        
         // 成功返回
         return true;
   }
   else
   {   //　不是上传文件操作
    return false;
   }
 }

 public int process() {
  // 如果没有指定文件保存路径，则默认取tomcat工程目录下的temp目录
  if (destPath == null || "".equals(destPath))
  {
   destPath = request.getRealPath("") + System.getProperty("file.separator") + "onloadwpp";
  }
  
  File tmpDir = new File(destPath);
  boolean success = true;

  try {
   if (!tmpDir.exists()) {
    tmpDir.mkdirs();
    success = true;
   }
  }
  catch (Exception e) {
   // We won't log it because the directory
   // creation failed for some reason - a SecurityException
   // or something else. We don't care about it, as the
   // code below tries to use java.io.tmpdir
   e.printStackTrace();
   success = false;
  }
  
  // 如果目录指定失败，把文件保存在您是目录中
  if (!success) {
   destPath = System.getProperty("java.io.tmpdir");
  }
    
  try {
    preFileUpload();

  } catch (Exception e) {
   
   e.printStackTrace();
  }
  
  return 0;
 }

 public String getFileName() {
  return fileName;
 }

 public void setFileName(String fileName) {
  this.fileName = fileName;
 }

 public String getDestPath() {
  return destPath;
 }

 public void setDestPath(String destPath) {
  this.destPath = destPath;
 }

 public String getDestFileName() {
  return destFileName;
 }

 public void setDestFileName(String destFileName) {
  this.destFileName = destFileName;
 }

 public HttpServletRequest getRequest() {
  return request;
 }

 public void setRequest(HttpServletRequest request) {
  this.request = request;
 }

}
在SERVLET里面调用

FileUpload fu=new FileUpload(request,null,null);
  fu.process();//实现上传
  File excel = new File(fu.getDestFileName()); 
  fu.getDestFileName().//为上传的路径名

 

以下为读取EXCEL方法,返回读取后的数据的LIST

public List<String> ReadExcel(File excel, InputStream fileIn,
   String strSeperator, boolean readFirstLine) throws BiffException,
   IOException {
  String seperator = "|";
  if (strSeperator != null && !"".equals(strSeperator)) { // set the
                // seperator .
   seperator = strSeperator;
  }
  List<String> lsAll = new ArrayList<String>(); // the Excel content .

  Workbook workbook = null; // the work space .
  String cellContent = ""; // cell content .
  Cell cell = null;

  File excelDoc = excel; // read excel
  if (excelDoc != null && fileIn == null) {
   workbook = Workbook.getWorkbook(excelDoc);
  } else if (excelDoc == null && fileIn != null) {
   workbook = Workbook.getWorkbook(fileIn);
  } else {
   throw new RuntimeException();
  }
  Sheet sheet = workbook.getSheet(0); // get the first sheet .
  int sheetRows = sheet.getRows(); // 总行�?
  // int sheetCols = sheet.getColumns(); //表的总列�?
  int sheetCols = 1; // 暂时只输入手机号�?,只用�?�?
  String[] lineContent = new String[sheetCols];
  for (int i = 0; i < sheetRows; i++) {
   String linecontent = "";
   String outLine = "";

   ///for (int j = 0; j < sheetCols; j++) {
    cell = sheet.getCell(0, i);
    cellContent = cell.getContents();
    if ("".equals(cellContent) || cellContent == null) {
     //cellContent = "NULL"; // if null or "" set the default
           // value
    }else
    {

    outLine += cellContent + seperator;
    lineContent[0] = cellContent;
    linecontent += cellContent;
    lsAll.add(outLine);
    }
   //}

   

  }

  return lsAll;

 }
  

File excel = new File(fu.getDestFileName());

List ls=new ArrayList();

ls=ReadExcel(excel ,null ,"#" ,false );

下面就是通过ls读取数据到数据库中.