在vs中将excel表中的数据导入sql server 数据库中

最新推荐文章于 2021-04-25 13:31:27 发布

转载最新推荐文章于 2021-04-25 13:31:27 发布 · 2.5k 阅读

文章标签：

#excel #sql server #数据库 #server #sqlserver #dataset

本文介绍了一种从Excel文件导入数据到SQL Server数据库的方法。首先通过OLE DB连接读取Excel文件内容，并将其加载到DataTable中。接着展示了如何使用存储过程将数据批量插入到SQL Server数据库表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实现思路：

1.先将excel表中的数据导入到dataview中

2.然后在将dataview中的数据导入到sql server中

aspx源码：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<asp:Button ID="btnUpload" runat="server" Text="导入" OnClick="btnUpload_Click" /><br />

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

</div>

</form>

</body>

</html>

.cs源码：

using System;

using System.Data;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

/// <summary>

/// 实现excel中的数据导入sql数据库中

/// 思路：1.先将excel中的数据导入dateview，然后将dateview中的数据导入sql数据库里面

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void btnUpload_Click(object sender, EventArgs e)

{

//excel的链接字符串

string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + File1.PostedFile.FileName.ToString() + ";" + "Extended Properties=Excel 8.0;";

//建立excel的链接

OleDbConnection objConn = new OleDbConnection(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("select * from [Sheet1$]",objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet objDataset1 = new DataSet();

objAdapter1.Fill(objDataset1, "XLData");

GridView1.DataSource = objDataset1.Tables[0];

GridView1.DataBind();

DataTable dt = objDataset1.Tables[0];

DataView myView = new DataView(dt);

//sql server的数据库连接

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);

SqlCommand cmd = conn.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "Proc_Address";

int count = 0; //用来记录出错的条数

try

{

foreach (DataRowView myDrv in myView)

{

count++;

//关闭上一次的sql连接

if (conn.State.ToString() != "Closed")

{

conn.Close();

//清空每一次的sql参数

cmd.Parameters.Clear();

}

SqlParameter paraCardNo = cmd.Parameters.Add("@CardNo", SqlDbType.Char);

SqlParameter paraToAddress = cmd.Parameters.Add("@ToAddress", SqlDbType.Char);

SqlParameter paraCCAddress = cmd.Parameters.Add("@CCAddress", SqlDbType.Char);

SqlParameter paraYYYYMM = cmd.Parameters.Add("@YYYYMM", SqlDbType.Char);

//表明是输出参数

paraCardNo.Direction = ParameterDirection.Input;

paraToAddress.Direction = ParameterDirection.Input;

paraCCAddress.Direction = ParameterDirection.Input;

paraYYYYMM.Direction = ParameterDirection.Input;

//参数赋值

paraCardNo.Value = myDrv[0].ToString().Trim();

paraToAddress.Value = myDrv[1].ToString().Trim();

paraCCAddress.Value = myDrv[2].ToString().Trim();

paraYYYYMM.Value = DateTime.Now.Year + DateTime.Now.Month;

conn.Open();

cmd.ExecuteNonQuery(); //写入sql数据库

}

catch

{

Page.Response.Write("alert('第"+count.ToString()+"数据出错！')");

objConn.Close(); //关闭excel连接

}

objConn.Close(); //关闭excel连接

}

存储过程“ Proc_Address”

ALTER PROCEDURE Proc_Address

@CardNo char(20),

@ToAddress char(50),

@CCAddress char(50),

@YYYYMM char(12)

insert into EmailAddress (CardNo,ToAddress,CCAddress,YYYYMM) values (@CardNo,@ToAddress,@CCAddress,@YYYYMM)

return