文件上传及导入数据库

Jsp：

<form name="uploadform" method="post" action="<%=basePath%>servlet/UploadServlet" ENCTYPE="multipart/form-data" onSubmit="return checkEmpty(uploadform)" >
  <p> </p>
  <p> </p> 
  
  <table align="center" >

        <tr>
          <td ><div align="right">上传新报表：</div></td>
            <td  colspan="2" align="left">
                            <input name="x" size="40" type="file">
            </td>
        </tr>
  </table>
        <br/>
        <table align="center">
        <tr><td align="center"><input name="upload" type="submit" value="开始上传"/></td></tr>
        </table>
</form>

上传：

package com.web;
import java.awt.HeadlessException;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;

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

import org.apache.commons.fileupload.*;

import java.sql.SQLException;
import java.util.*;
import org.apache.commons.fileupload.servlet.*;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;


public class UploadServlet extends HttpServlet {

    public void doPost(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        res.setContentType("text/html; charset=GB18030");
        PrintWriter out = res.getWriter();
        DiskFileItemFactory factory = new DiskFileItemFactory();
        // 允许设置内存中存储数据的门限，单位：字节
        factory.setSizeThreshold(4096);

        // 如果文件大小大于SizeThreshold，则保存到临时目录
        factory.setRepository(new File("D:\\"));
        ServletFileUpload upload = new ServletFileUpload(factory);

        // 最大上传文件，单位：字节
        upload.setSizeMax(1000000);

        try {
            List fileItems = upload.parseRequest(req);
            Iterator iter = fileItems.iterator();

            if (iter.hasNext()) {
                FileItem item = (FileItem) iter.next();
                String fileName = item.getName();
                // 忽略其他不是文件域的所有表单信息
                String fileFileName = fileName.substring(fileName.lastIndexOf("\\") + 1, fileName.length());
                fileName = fileName.substring(fileName.lastIndexOf("\\") + 1, fileName.length());
                //String[] GsName1=fileName.split(".xls");
                //String[] GsName=GsName1[0].split("-");
                //获取文件类型
            	  String      type =  fileFileName.substring(fileFileName.lastIndexOf('.')   +   1);
            	        //判断文件是否是图片类型
            	        if(type.equals("xls")){
            	        	try {
			//if(InDb.FindDb(GsName[0],GsName[1])){
			//System.out.println("表存在");
			// JOptionPane.showMessageDialog(null, "该文件存在!请先删除数据！", "系统信息", JOptionPane.ERROR_MESSAGE); 
			 //String url = "/home/ControlDb/have.jsp?first="+GsName[0]+"&second="+GsName[1]+"";  
			//req.getRequestDispatcher(url).forward(req, res);
			// }else{
			//System.out.println("表不存在");
			// 保存上传的文件到指定的目录指定文件名中
				item.write(new File("D:\\",fileName));
				InDb.ExceltoDb(fileName);
				//删除excel文件
				File tempFile = new File("D:\\",fileName);
				tempFile.delete();
				JOptionPane.showMessageDialog(null,"上传成功!", "系统信息", JOptionPane.INFORMATION_MESSAGE); 
				out.println(
					 "<meta http-equiv=\"refresh\"content=\"0;url=/CaiWu/home/ControlDb/AddDb.jsp\">");  
								//}
							} catch (HeadlessException e) {
								e.printStackTrace();
							} catch (SQLException e) {
								e.printStackTrace();
							} catch (Exception e) {
								e.printStackTrace();
							}
            	        }else
            	        {
            	        JOptionPane.showMessageDialog(null,"文件格式不对!请上传xls格式!", "系统信息", JOptionPane.ERROR_MESSAGE); 
            	        	out.println(
            	                    "<meta http-equiv=\"refresh\"content=\"0;url=/CaiWu/home/ControlDb/AddDb.jsp\">"); 
            	        }
                }
            
        } catch (FileUploadException e) {
            out.println(e);
        }
    }
}

导入数据库：

package com.web;

import java.io.FileInputStream;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import publicUse.DBConnect;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;   

import javax.servlet.http.HttpServlet;



public class InDb {
    /**
     * 读取xls文件内容
     * @param gsName 
     * @return 
     * @throws SQLException 
     */
    public static  void ExceltoDb(String fileName) throws SQLException{
        	DBConnect connect = new DBConnect();
                try{
            DecimalFormat df2  = new DecimalFormat("###.0000");  
            FileInputStream fis = new FileInputStream("D:/"+fileName);   
            jxl.Workbook rwb = Workbook.getWorkbook(fis);   
            Sheet[] sheet = rwb.getSheets();

                Sheet rs1 = rwb.getSheet(0); 
         	   String a[] = new String[138];
 
                for (int j = 1; j < rs1.getRows(); j++) {   
                   Cell[] cells = rs1.getRow(j);
            	   for(int ii=0;ii<138;ii++){
                   a[ii]=cells[ii].getContents();
         		   if(a[ii]==null|a[ii]==""){
          			  a[ii]="0";
          		   }
                   }            	   
          		 String AddSql = "insert into caiwu values(...)          		 
                         System.out.println(AddSql);
            	         connect.executeUpdate(AddSql);
                }
             
            fis.close();
            connect.close();

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

	public static <retrun> boolean FindDb(String fileName1,String time) throws SQLException{
		  boolean a = false;
		  DBConnect connect1 = new DBConnect();
    	  String GetInfoSql = "select * from caiwu where gsname = '"+fileName1+"' and time='"+time+"'";
    	  System.out.println(GetInfoSql);
    	  ResultSet rs =connect1.executeQuery(GetInfoSql);
    	  try {
    		    while (rs.next()) {
    		    		a= true;
    		    }
    	     rs.close();
    	  } catch (SQLException ex) {}
    	   connect1.close();
		return a;

    }

}