62,074
社区成员
发帖
与我相关
我的任务
分享怎样将上传的excel表导入到sql数据库表中？
我已经上传了一个excel表，怎样紧接着就把这些数据导入到excel表中？
如果我想执行存储过程，请问怎样定义一个button按键就可以调用存储过程？
如果我想执行存储过程，请问怎样定义一个button按键就可以调用存储过程？
...全文
请发表友善的回复…
发表回复
lovedogdog 2005-08-23
- 打赏
- 举报
mark
share1011 2005-06-01
- 打赏
- 举报
我哭，没有人教我：（
lovedogdog 2005-05-31
- 打赏
- 举报
mark
zdliang 2005-05-31
- 打赏
- 举报
你的意思是用存储过程直接把你的excel导入到sql server里吗？
share1011 2005-05-31
- 打赏
- 举报
有高手会吗？
share1011 2005-05-30
- 打赏
- 举报
http://blog.csdn.net/hchxxzx/
http://community.csdn.net/Expert/TopicView.asp?id=4016964
http://community.csdn.net/Expert/TopicView.asp?id=4016964
hchxxzx 2005-05-30
- 打赏
- 举报
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);
}
}
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);
}
}
share1011 2005-05-30
- 打赏
- 举报
我想把上传的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)
假如这步实现了就 用一个按键实现下面筛选的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)
owenbeckham 2005-05-30
- 打赏
- 举报
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();
heartflying201 2005-05-30
- 打赏
- 举报
ding
share1011 2005-05-30
- 打赏
- 举报
button1_click是我写的读取excel表，可是不正确 ：（
读取之后我还想再调用存储过程——就是将我写的sql语句变成存储过程，可是好象没有参数，不知道怎么改怎么变！
读取之后我还想再调用存储过程——就是将我写的sql语句变成存储过程，可是好象没有参数，不知道怎么改怎么变！
share1011 2005-05-30
- 打赏
- 举报
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
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
share1011 2005-05-30
- 打赏
- 举报
<%@ 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>
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>
