怎样将上传的excel表导入到sql数据库表中？

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb"
Inherits="test1.MultiUpload" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>多文件上传</title>
<script language="JavaScript">
    function addFile()
    {
     var str = '<INPUT type="file" size="50" NAME="File">'
     document.getElementById('MyFile').insertAdjacentHTML("beforeEnd",str)
    }
</script>
</HEAD>
<body>
<form id="form1" method="post" runat="server" enctype="multipart/form-data">
<center>
<asp:Label Runat="server" ID="MyTitle"></asp:Label>
<P id="MyFile"><INPUT type="file" size="50" NAME="File"></P>
<P>
<input type="button" value="增加(Add)" onclick="addFile()">
<asp:Button Runat="server" Text="上传" ID="Upload"></asp:Button>
<input onclick="this.form.reset()" type="button" value="重置(ReSet)">
</P>
</center>
<P align="center">
<asp:Label id="strStatus" runat="server" Font-Names="宋体" Font-Bold="True" Font-Size="9pt" Width="500px"
BorderStyle="None" BorderColor="White"></asp:Label>
<asp:Button id="Button1" runat="server" Text="导入数据库"></asp:Button>
</P>
</form>
</body>
</HTML>

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Configuration
Public Class MultiUpload
    Inherits System.Web.UI.Page
    Protected WithEvents Upload As System.Web.UI.WebControls.Button
    Protected WithEvents MyTitle As System.Web.UI.WebControls.Label
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents strStatus As System.Web.UI.WebControls.Label#Region " Web Form Designer Generated Code "    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()    End Sub    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub#End Region
    Dim cmd As SqlCommand
    Dim conn As New SqlConnection(PublicModule.connstr)    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MyTitle.Text = "<h3>多文件上传</h3>"
        Upload.Text = "开始上传"
        If (Me.IsPostBack) Then Me.SaveImages()
    End Sub    Private Function SaveImages() As System.Boolean
        '遍历File表单元素
        Dim files As System.Web.HttpFileCollection = System.Web.HttpContext.Current.Request.Files        '状态信息
        Dim strMsg As New System.Text.StringBuilder("上传的文件分别是：<hr color=red>")
        Dim iFile As System.Int32        Try
            For iFile = 0 To files.Count - 1
                '检查文件扩展名字
                Dim postedFile As System.Web.HttpPostedFile = files(iFile)
                Dim fileName, fileExtension As System.String
                fileName = System.IO.Path.GetFileName(postedFile.FileName)
                If Not (fileName = String.Empty) Then
                    fileExtension = System.IO.Path.GetExtension(fileName)
                    strMsg.Append("上传的文件类型：" + postedFile.ContentType.ToString() + "<br>")
                    strMsg.Append("客户端文件地址：" + postedFile.FileName + "<br>")
                    strMsg.Append("上传文件的文件名：" + fileName + "<br>")
                    strMsg.Append("上传文件的扩展名：" + fileExtension + "<br><hr>")
                    '可根据扩展名字的不同保存到不同的文件夹
                    postedFile.SaveAs(System.Web.HttpContext.Current.Request.MapPath("images/") + fileName)
                End If
            Next
            strStatus.Text = strMsg.ToString()
            Return True
        Catch Ex As System.Exception
            strStatus.Text = Ex.Message
            Return False
        End Try

    End Function    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connstring As String
        Dim strsql As String
        Dim conn As New OleDbConnection
        connstring = "provider=microsoft.Jet.oledb.4.0;data source= Server.MapPath("e.xls")"
        conn.ConnectionString = connstring
        conn.Open()
        Dim strdbname As String = "Table2"
        Dim strnewfilename = Server.MapPath("+ fileName +")
        Dim sql As String = "select * into" + strdbname + " from FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + strnewfilename + "',[Sheet1$])"
        Dim cmd As OleDbCommand
        cmd.Connection = conn
        cmd.CommandText = sql
        Try
            cmd.ExecuteNonQuery()
            Response.Write("恭喜成功")
        Catch err As Exception
            Response.Write(err.Message)
        End Try
        cmd.Dispose()
        conn.Close()    End Sub
End Class

button1_click是我写的读取excel表，可是不正确：（
读取之后我还想再调用存储过程——就是将我写的sql语句变成存储过程，可是好象没有参数，不知道怎么改怎么变！

string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'D:/ExportToExcel/excel/test.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection (mystring);
OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);
DataSet myDs =new DataSet();
myDa.Fill(myDs);

if(myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
string CnnString="Provider=SQLOLEDB;database=testnews;server=(local);uid=sa;pwd=";
OleDbConnection conn =new OleDbConnection(CnnString);
conn.Open ();
OleDbCommand myCmd =null;

for(int i=0; i<myDs.Tables[0].Rows.Count; i++)
{
strSql="insert into news(title,body) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "', '";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "')";

try
{
myCmd=new OleDbCommand(strSql,conn);
myCmd.ExecuteNonQuery();
Label8.Text = "<script language=javascript>alert('数据导入成功.');</script>";
}
catch
{
Label8.Text = "<script language=javascript>alert('数据导入失败.');</script>";
}
}
conn.Close();

我想把上传的excel表数据导入到table5-w中，用button1_click实现，可是代码是错的，不知道怎么导入。
假如这步实现了就用一个按键实现下面筛选的sql判断语句，用datagrid显示通过没有判断的数据。通过的就插入数据库的2个表中。可是我感觉没有参数，不知道怎么把下面的sql 语句变为存储过程
select distinct* into #t1 from Table5_w --从导入的表Table5_w中再筛选
select customer_id into #t2 from #t1 where customer_id in(select customer_id from Customer)--看customer表中有没有
insert into m_customer select customer_id ,'0505' from #t2---custoemr表中有就只插入m-customer表
select customer_id into #t3 from #t1 where customer_id in(select customer_id from LS_101...Customer t)
and customer_id not in(select customer_id from #t2) --看101中有没有
insert into m_customer select customer_id ,'0505' from #t3   ---有的话就插入m-custoemter表
--再插入customer  表中
insert into Customer select .... from  #t3 c,LS_101...Customer t,sales_depot p  ,channel s  ,LS_SDS101...Calling m where ......
--看导不进去的有那些客户编号
select customer_id into #t5  from #t1 where customer_id not in(select customer_id from #t2)and customer_id not in(select customer_id from #t3) and customer_id
  not in(select customer_id from #t4)

1.将文件上传到服务器硬盘上
2.应用如下函数将电子文件里面的数据读取到datatable里面
/// <summary>
/// 返回打开的电子表格中的某个表并形成datatable
/// </summary>
/// <param name="path">文件绝对路径</param>
/// <param name="target">要打开的表名</param>
/// <returns>返回DATATABLE数据库集</returns>
private DataTable ReadExcel(string path,string target)
{
DataSet myDataset = new DataSet();
string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties=Excel 8.0;";
OleDbConnection myOleDbConnection = new OleDbConnection(connectionstring);
OleDbCommand  myOleDbCommand;
myOleDbCommand = new OleDbCommand("SELECT * FROM [" + target + "]",myOleDbConnection);
OleDbDataAdapter myData =  new OleDbDataAdapter(myOleDbCommand);
try
{
myData.Fill(myDataset);
}
catch(Exception ex)
{
throw ex;
}
return myDataset.Tables[0];
}3.逐一读取上述DATATABLE,并至数据库中判断,有存在则不写入,不存在则写入string myValue = "";
for(int i=0;i<myTab.Rows.Count;i++)
{
myValue = myTab.Rows[i][0].ToString();
if(bolBeing(myValue) == false)
{
//写插入SQL语句,将此数据插入至数据库中
}
         else
         {
          //将已经存在的数据输出页面进行查看.
         }
} //检测传递的值是否存在数据库中,是则返回true,不存在则返回false
private bool bolBeing(string myValue)
{
string sql = "select count(*) from 表名 where 字段 = '" + myValue + "' ";
//求取返回值
if(返回值 == "0")
{
return(false);
}
else
{
return(true);
}
}

http://blog.csdn.net/hchxxzx/ http://community.csdn.net/Expert/TopicView.asp?id=4016964

你的意思是用存储过程直接把你的excel导入到sql server里吗？

调试易

怎样将上传的excel表导入到sql数据库表中？

解决方案 »