jsp 中如何将 excel 导入到数据库中 ( 2 )

你是用的什么上传方法`
<%@page pageEncoding="UTF-8" %>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<%@ page import="javax.activation.*"%>
<%@ page import="org.apache.commons.fileupload.*"%>
<%@ include file="../common/preAction.jsp"%>
<%@ taglib uri="http://nmt.com.hk/tags" prefix="nmttag"%>
<%@ include file="../common/header.jsp"%>
<%@ page import="com.newmodern.eve.gui.lnf.MLUtils" %>
<%@ page import="com.nml.roims.job.SendingEmail"%>
<%@ page import="java.net.URLEncoder"%>
<%@ page import="com.nml.roims.controller.QU_BJDWH.ChangeCharSet" %>
<%@ page import="com.nml.roims.controller.SEC_QX.Split"%>
<%@ page import="com.nml.roims.common.ROIMSUtilities"%>
/**
* <p>Description:  </p>
* <p>Copyright:    Copyright (c) 2007</p>
* <p>Company:      New Modern Technology</p>
* @author          Nick Yu
* @version         $v 1.3 2007/05/27 11:25:05 his_jack Exp $
*/
<%
     Split st=new Split();
     ChangeCharSet ch=new ChangeCharSet();
     String resieve=null;
     String sende=null;
     String titles=null;
     String content=null;
     List   resultlist=(List)request.getSession().getAttribute("showlist");
     resieve=(String)resultlist.get(0);
     sende=(String)resultlist.get(1);
     titles=(String)resultlist.get(2);
     content=(String)resultlist.get(3);
     String exlspath=(String)request.getSession().getAttribute("reportpath");
     String exlsname=(String)request.getSession().getAttribute("xlsname");
     StringBuffer showreportname = new StringBuffer("");
     for(int i=exlspath.length()-1; i>=0;i--)
      {
             if(exlspath.charAt(i)!='/')
             {
                 showreportname.append(exlspath.charAt(i));
             }
             else
             {
                 break;
             }
      }
      StringBuffer resultshowreportname = new StringBuffer("");
      for(int i=showreportname.toString().length()-1; i>=0;i--)
        {
             if(showreportname.toString().charAt(i)!='/')
             {
                 resultshowreportname.append(showreportname.toString().charAt(i));
             }
             else
             {
                 break;
             }
      }
%><script type="text/javascript">
<input type="hidden" name="attachprem" value="0" />'
+ '<table cellspacing="0" cellpadding="0"><tbody>' +
'<tr><td style="vertical-align:top;"></td>'
+
'<td id="att" style="font-size:10pt;"><div id="ia"><span class="l" onclick="javascript:initattachment(this);">上传附件</span></div>'
+
'<div id="aa" style="display:none;"><span class="l" onclick="javascript:addattachment(this);">增加附件</span></div></td>'
        +
'</tr></tbody></table>' + '' ;
document.write(attHTML);
if(maxatts)
MAX_attachments = maxatts;
}
//////////Attachments Operations!
var VT_attachments = 0;
var MAX_attachments = 5;
var attach=null;
function initattachment(me)
{
    var ia = me.parentNode;
    var att = ia.parentNode;
    if(att.id != 'att')
    return;

    Ohidden(ia);
    var aa = gObId('aa');
    Oshow(aa);
    addattachment(aa);
}function addattachment(me)
{
    attach = document.createElement('div');
    var aa = gObId('aa');
    var att = aa.parentNode;
    if(att.id != 'att')
    return;    switch(VT_attachments)
     {
          case 0:
               fileid0();
               break;
          case 1:
               fileid1();
                break;
          case 2:
               fileid2();
                break;
          case 3:
               fileid3();
                break;
          case 4:
               fileid4();
                break;
          case 5:
               fileid5();
                break;
          case 6:
               fileid6();
                break;
          case 7:
               fileid7();
                break;
          case 8:
               fileid8();  break;
          case 9:
               fileid9();
                break;
     }    att.insertBefore(attach,aa);
    VT_attachments ++ ;
    if(VT_attachments >= MAX_attachments)
    {
        Ohidden(aa);
    }
}function fileid0()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid0"  type="file" name="fileid0" width="550" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}function fileid1()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid1"  type="file" name="fileid1" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function fileid2()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid2"  type="file" name="fileid2" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function fileid3()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid3"  type="file" name="fileid3" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function fileid4()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid4"  type="file" name="fileid4" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function fileid5()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid5"  type="file" name="fileid5" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function fileid6()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid6"  type="file" name="fileid6" /></span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function fileid7()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid7"  type="file" name="fileid7" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function fileid8()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid8"  type="file" name="fileid8" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function fileid9()
{
    attach.innerHTML = '<span style="font-size:10pt;"><input id="fileid9"  type="file" name="fileid9" /> </span><span class="l" onclick="javascript:delattachment(this);" style="font-size:10pt;">删除</span>';
}
function delattachment(me)
{
    var attach = me.parentNode;
    var att = attach.parentNode;
    if(att.id != 'att')
        return;
    att.removeChild(attach);
   VT_attachments -- ;
    if(VT_attachments == 0)
    {
        Ohidden(gObId('aa'));
        Oshow(gObId('ia'));
    }else if(VT_attachments < MAX_attachments)
        Oshow(gObId('aa'));
}//Elements Operations
function Oshow(e)
{
    e.style.display = "";
}
function Ohidden(e)
{
    e.style.display = "none";
}
function gObId(id)
{
try{
        return document.getElementById(id);
    }
    catch(e){
        return null;
    }
}
function isOshow(e)
{
    return !(e.style.display == 'none');
}
    //-->
</script>
<style>
.l {color:#0000cc;text-decoration:underline;cursor:pointer;cursor:hand;}
</style>

<%System.out.println((String)(request.getSession().getAttribute("state"))); %>
<% if(request.getSession().getAttribute("state")!=null) {%>
<%
SendingEmail   sm=new SendingEmail();
    String  getEmailTofrompage=(String)request.getParameter("QU_BJDSDEM.ReceiveEmail");
String  getccEmail=(String)request.getParameter("QU_BJDSDEM.SendEmailMan");

    System.out.println(getEmailTofrompage);
    System.out.println(getccEmail);

    String  tomail=null;
    String  ccmail=null;
    String  title=null;
    String  contentcc=null;
    List  resultlist2=(List)request.getSession().getAttribute("showlist");
    tomail=(String)resultlist2.get(0);
    ccmail=(String)resultlist2.get(1);
    title=(String)resultlist2.get(2);
    contentcc=(String)resultlist2.get(3);
    List  tomaillist=new ArrayList();
    List  cclist=new ArrayList();
    List  pathlist=new ArrayList();

    request.getSession().removeAttribute("state");
    String uploadPath =sm.getEmailConfig("uploadPath");
    File testdir = new File(uploadPath); //为了保证目录存在,如果没有则新建该目录
    if (!testdir.exists()) {
    testdir.mkdirs();
    }
    DiskFileUpload fu = new DiskFileUpload();
    // 设置最大文件尺寸，这里是4MB
    fu.setSizeMax(50194304);
    // 设置缓冲区大小，这里是50kb
    fu.setSizeThreshold(14096);
    // 得到所有的文件：
    List fileItems = fu.parseRequest(request);
    FileItem file_item;
    for(int   i=0;i<fileItems.size();i++)
    {
           file_item=(FileItem)fileItems.get(i);
       // System.out.println("file_item="+file_item);

    }

    Iterator i = fileItems.iterator();
    // 依次处理每一个文件：
    String   EmailTitle=null;
    String   Emailcctem=null;

    List   ToEmaillist=new ArrayList();
    List   ToCCEmail=new ArrayList();

    while (i.hasNext()) {

    FileItem fi = (FileItem) i.next();
    // 检查当前的项目是普通的表单元素，还是一个上载的文件
    if (fi.isFormField()) {
    // 获得表单域的名字

      if (fi.getFieldName().equals("QU_BJDSDEM.ReceiveEmail"))
      {

              String   ToEmail =fi.getString();
               String[]  tt = st.split(ToEmail,";");
               for(int j=0;j<tt.length;j++)
               {
                   ToEmaillist.add(tt[j]);
               }


          // System.out.println("ToEmail  is ["+ToEmail+"]");
      }
      if (fi.getFieldName().equals("QU_BJDSDEM.SendEmailMan"))
      {
              String   CCEmail =fi.getString();
              String[]  cc = st.split(CCEmail,";");
              for(int h=0;h<cc.length;h++)
              {
               ToCCEmail.add(cc[h]);
               }

           // System.out.println("CCEmail  is ["+CCEmail+"]");
      }
      if (fi.getFieldName().equals("BAS_BJDYJ.txtEmail_title"))
      {
                 EmailTitle =fi.getString("UTF-8");
                 //String vl= ch.getstring(EmailTitle);
           // System.out.println("EmailTitle  is ["+vl+"]");
      }
       if (fi.getFieldName().equals("BAS_BJDYJ.txtEmail_content"))
      {
                 Emailcctem =fi.getString("UTF-8");
            //  System.out.println("Emailcctem  is ["+Emailcctem+"]");
      }


    String fieldName = fi.getFieldName();
    }
    // 获得文件名，这个文件名包括路径：
    String fileName = fi.getName();
    if (!(fileName == null)) {
    // 写入文件，暂定文件名为a.txt，可以从fileName中提取文件名：
    String uploadFileName = fileName.substring(fileName.trim().lastIndexOf("\\") + 1);
    fi.write(new File(uploadPath + uploadFileName));
    pathlist.add(uploadPath+uploadFileName);
    System.out.println("ok");
    }
    }

     System.out.println("ToEmaillist is ["+ToEmaillist+"]");
     System.out.println("ToCCEmail is ["+ToCCEmail+"]");
     String   senderEmail=(String)request.getSession().getAttribute("LoginEmail");
     pathlist.add(exlspath);
     request.getSession().removeAttribute("state");
     sm.send(senderEmail,ToEmaillist,ToCCEmail,EmailTitle,Emailcctem,pathlist);
  %>
  <script language="javascript">

             alert("<%=MLUtils.getResourceString("QU_BJDSDEM.SendSuccess")%>");
             window.location.href('../QU_BJDWH/mainPage.jsp');

  </script>
<%}else{%>
<script language="javascript">
   function   checkonsubmit()
   {
       var  CkTomail=document.getElementById("QU_BJDSDEM.ReceiveEmail").value;
       if(CkTomail=="")
       {
  alert( "<%=ROIMSUtilities.getResourceByCode("QU_BJDSDEM.ReceiveEmail")%>" + "<%=ROIMSUtilities.getResourceByCode("ROIMS.Common_field_Mandatory")%>");
          return  false;
       }
       else
       {
       return true;
       }

   }

</script>
<H1 align=center><%=MLUtils.getResourceString("QU_BJDWH.conactCustomer")%></H1>
  <FORM   action="../roims/jsp/QU_BJDWH/SendEmail.jsp" enctype="multipart/form-data"  method="post" name="form1"  id="form1" onSubmit="return checkonsubmit()" >
      <input type="hidden" id="state" name="state" value="yy">
<div>
<table border="0" cellpadding="3" cellspacing="0" align="center"><tr>

    <td >
        <nmttag:label  id="BAS_BJDYJ.lbl_operation_center" value="QU_BJDSDEM.ReceiveEmail" />
    </td>
    <td >
        <nmttag:text   id="QU_BJDSDEM.ReceiveEmail" width="<%=new Integer(410)%>" value="<%= resieve%>" /> *
    </td>
</tr><tr>
    <td>
        <nmttag:label  id="BAS_BJDYJ.lbl_operation_center" value="QU_BJDSDEM.CCEmail" />
        </td>
        <td>
        <nmttag:text   id="QU_BJDSDEM.SendEmailMan" width="<%=new Integer(410)%>" value="<%= sende%>" />
    </td>
</tr><tr>
    <td>
        <nmttag:label  id="BAS_BJDYJ.lbl_email_title" value="BAS_BJDYJ.lbl_email_title" />
    </td>
    <td>
        <nmttag:text  id="BAS_BJDYJ.txtEmail_title" value="<%=titles %>" width="<%=new Integer(410)%>" fieldLength="<%=new Integer(100)%>"/>
    </td>
</tr></table>
<div align="center"></div>
<table border="0" cellpadding="3" cellspacing="0" align="center">
<tr>
    <td>
        <nmttag:label  id="BAS_BJDYJ.lbl_email_content" value="BAS_BJDYJ.lbl_email_content" />
    </td>
     <td >
         <nmttag:textarea   id="BAS_BJDYJ.txtEmail_content" value="<%= content%>" cols="<%= new Long(65)%>" rows="<%= new Long(8)%>" fieldLength="<%=new Integer(400)%>"/>
    </td>
</tr>
<tr>
         <td>
             <nmttag:label  id="BAS_BJDYJ.lbl_email_title" value="QU_BJDSDEM.Annex" />
        </td>
        <td>

        <nmttag:label  id="BAS_BJDYJ.lbl_email_title" value="<%= resultshowreportname.toString()%>" />

       </td>
</tr><tr>
         <td>

        </td>
        <td>
           <script type="text/javascript">Attachment(10);</script>
       </td>
</tr>
</table>
</div>
<div align="center">
     <input type="submit" name="Submit" value="<%=MLUtils.getResourceString("QU_BJDSDEM.lblSend")%> ">
</div>

   </FORM>
<%request.getSession().setAttribute("state","1");%><%}%>
<%@ include file="../common/footer.jsp"%>
看看这个，坚决了名字很长的问题

你可以看看上面我实现的方法`~希望对你有用
fi.getString("UTF-8"); 解决文件名出现不正常

poi,jxl都可以
如果你是SQL SERVER数据库的话可以考虑使用DTS

我写的一个遍历excel文件中数据的方法，把数据取出来后，再insert到数据库中，很简单了，不说了。

   iRowCount = sheet.getPhysicalNumberOfRows();
   iColCount = sheet.getRow(0).getPhysicalNumberOfCells();
   out.println("RowCount,ColCount: " +  iRowCount+","+iColCount+"<br>");

   //开始读取这个Excel表
for (int i = 0; i < iRowCount; i++) {
    HSSFRow row = sheet.getRow(i);
    if(sheet.getRow(i)!=null){

    for (short j = 0; j < iColCount; j++) {
            HSSFCell cell =row.getCell(j);
        if (cell!=null){
        switch(cell.getCellType()) //判断类型
                  {
          case HSSFCell.CELL_TYPE_NUMERIC:
         cell_value = ((Double)cell.getNumericCellValue()).toString() ;
          break;
          case HSSFCell.CELL_TYPE_STRING:
         cell_value = cell.getStringCellValue();
          break;
          case HSSFCell.CELL_TYPE_FORMULA:
         cell_value = cell.getCellFormula();
          break;
         default:
              cell_value="<font color=red>null_value</font>";
                  }  //end switch
   out.print(cell_value+"    ");
           cell_value="";
          } //end if
        } //end   for (short j = 0; j < iColCount; j++) {
   out.print("<br>");

   }  //end  if(sheet.getRow(i)==null){
  else
    iRowCount++; //由于sheet.getPhysicalNumberOfRows()方法不统计空行，所以遇到空行时，要使此值加1才是实际行数

} //end for (int i = 0; i < iRowCount; i++) {

   fs_xls.close();  //关闭文件流

  }catch(Exception e){
   System.out.println("出错了 : " + e );
  } %>

上次贴的代码不全，重贴
-----------------------------------------------------------------<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<%@ page contentType="text/html; charset=gb2312" language="java"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
<%@ page import="java.io.FileInputStream"%>
<%
  String fileToBeRead="E:/JSP/Excel/test.xls";
  String cell_value=new String();
  int iRowCount,iColCount; //得到Excel表的行数，列数
  try{
  // 创建对Excel工作簿文件的引用

   FileInputStream fs_xls=new FileInputStream(fileToBeRead);
   POIFSFileSystem fs_pio = new POIFSFileSystem(fs_xls);
   HSSFWorkbook workbook = new HSSFWorkbook(fs_pio);
   // 创建对工作表的引用。

   // 本例是按名引用（让我们假定那张表有着缺省名"Sheet1"）
   //HSSFSheet sheet = workbook.getSheetAt("Sheet1");

   // 也可用getSheetAt(int index)按索引引用，
   // 在Excel文档中，第一张工作表的缺省索引是0，
   // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
   HSSFSheet sheet = workbook.getSheetAt(0);

   iRowCount = sheet.getPhysicalNumberOfRows();
   iColCount = sheet.getRow(0).getPhysicalNumberOfCells();
   out.println("RowCount,ColCount: " +  iRowCount+","+iColCount+"<br>");

   //开始读取这个Excel表
for (int i = 0; i < iRowCount; i++) {
    HSSFRow row = sheet.getRow(i);
    if(sheet.getRow(i)!=null){

    for (short j = 0; j < iColCount; j++) {
            HSSFCell cell =row.getCell(j);
        if (cell!=null){
        switch(cell.getCellType()) //判断类型
                  {
          case HSSFCell.CELL_TYPE_NUMERIC:
         cell_value = ((Double)cell.getNumericCellValue()).toString() ;
          break;
          case HSSFCell.CELL_TYPE_STRING:
         cell_value = cell.getStringCellValue();
          break;
          case HSSFCell.CELL_TYPE_FORMULA:
         cell_value = cell.getCellFormula();
          break;
         default:
              cell_value="<font color=red>null_value</font>";
                  }  //end switch
   out.print(cell_value+"    ");
           cell_value="";
          } //end if
        } //end   for (short j = 0; j < iColCount; j++) {
   out.print("<br>");

   }  //end  if(sheet.getRow(i)==null){
  else
    iRowCount++; //由于sheet.getPhysicalNumberOfRows()方法不统计空行，所以遇到空行时，要使此值加1才是实际行数

} //end for (int i = 0; i < iRowCount; i++) {

   fs_xls.close();  //关闭文件流

  }catch(Exception e){
   System.out.println("出错了 : " + e );
  } %>

文件上传可以使用Fileupload组件。这个论坛里多的是。

调试易

jsp 中如何将 excel 导入到数据库中 ( 2 )

解决方案 »

jsp 中如何将 excel 导入 到数据库中 ( 2 )

解决方案 »

jsp 中如何将 excel 导入到数据库中 ( 2 )