从 Excel 导入数据到 SQL Server

Vivekananda Swamy.M

4.59/5 (36投票s)

2009 年 1 月 16 日

CPOL

402559

19688

本文主要描述如何将 Excel 表格中的数据导入到 SQL Server，使用 Gridview 控件查看 SQL Server 中的数据，以及删除数据。如果 Excel 表格中包含空值（null 值），则这些值将被替换为零.........

下载源代码 - 155.6 KB

引言

本文主要描述如何将 Excel 表格中的数据导入到 SQL Server，使用 Gridview 控件查看 SQL Server 中的数据，以及删除数据。如果 Excel 表格中包含空值（null 值），则这些值将被替换为零.........

Using the Code

在这里，我创建了一个逐步的过程，用于将数据从 Excel 导入到 SQL Server，并将 null 值替换为零，然后将其存储在 SQL Server 数据库中。

以下是逐步过程

步骤 1

创建一个示例表

create table emp
(
sno int identity,
fname nvarchar(20),
lname nvarchar(20),
mobnum nvarchar(15),
city nvarchar(20),
state nvarchar(30),
zip int
)

第二步

获取一个网页并将其命名为 Excel2sql.aspx （以下是包含在 Excel2sql.aspx 页面中的代码）。

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

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>:: Importing Data From Excel Sheet to SQL Server ::</title>
<style type="text/css">
.style1
{
width: 50%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>

<table align="center" class="style1">
<tr>
<td align="center">
<asp:LinkButton ID="insertdata" runat="server" onclick="insertdata_Click">
	Insert Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="viewdata" runat="server" onclick="viewdata_Click">
	View Data</asp:LinkButton>
</td>
<td align="center"> 
<asp:LinkButton ID="deletedata" runat="server" onclick="deletedata_Click">
	Delete Data</asp:LinkButton>
</td>
</tr>
<tr>
<td colspan="9" align="center">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</td>

</tr>
<tr>
<td colspan="9" align="center">
<asp:Label ID="lblmsg" runat="server" Width="500px"></asp:Label>
</td>

</tr>
</table>

</div>
</form>
</body>
</html>

步骤 3

以下是 Excel2sql.aspx.cs 文件中的 C# 代码

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb; //This namespace is mainly used for dealing with 
		       //Excel sheet data
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection oconn = new OleDbConnection
		(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
		Server.MapPath("example.xls") + ";
		Extended Properties=Excel 8.0");//OledbConnection and 
				// connectionstring to connect to the Excel Sheet
        try
        {
            //After connecting to the Excel sheet here we are selecting the data 
	   //using select statement from the Excel sheet
	   OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
	            oconn.Open();  //Here [Sheet1$] is the name of the sheet 
				//in the Excel file where the data is present
            OleDbDataReader odr = ocmd.ExecuteReader();
            string fname = "";
            string lname = "";
            string mobnum = "";
            string city = "";
            string state = "";
            string zip = "";
            while (odr.Read())
            {
                fname = valid(odr, 0);//Here we are calling the valid method
                lname = valid(odr, 1);
                mobnum = valid(odr, 2);
                city = valid(odr, 3);
                state = valid(odr, 4);
                zip = valid(odr, 5);
                //Here using this method we are inserting the data into the database
	       insertdataintosql(fname, lname, mobnum, city, state, zip);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }

//This valid method is mainly used to check where the null values are 
//contained in the Excel Sheet and replacing them with zero
    protected string valid(OleDbDataReader myreader, int stval)//if any columns are 
				//found null then they are replaced by zero
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }
    protected void viewdata_Click(object sender, EventArgs e)//Code to View 
						// the data from the SQL Server
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
	AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter("select * from emp", conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        catch (DataException de)
        {
            lblmsg.Text = de.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Shown Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }
    public void insertdataintosql(string fname, string lname, 
		string mobnum, string city, string state, string zip)
    {//inserting data into the Sql Server
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
	AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "insert into emp(fname,lname,mobnum,city,state,zip) 
			values(@fname,@lname,@mobnum,@city,@state,@zip)";
        cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
        cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
        cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
        cmd.Parameters.Add("@state", SqlDbType.NVarChar).Value = state;
        cmd.Parameters.Add("@zip", SqlDbType.Int).Value = Convert.ToInt32(zip);
        cmd.CommandType = CommandType.Text;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    protected void deletedata_Click(object sender, EventArgs e)//Here we are deleting 
						// the data from the SQL Server
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
	AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "delete from emp";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            cmd.ExecuteScalar();
            conn.Close();
        }
        catch (DataException de1)
        {
            lblmsg.Text = de1.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Deleted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
    }
}

关注点

使用 OledbConnection 连接到 Excel 表格
用于连接到 Excel 表格的连接字符串
从 Excel 文件中选择数据
将 Excel 表格中的 null 值替换为零，并将其插入到 SQL Server
查看和删除数据

历史

2009 年 1 月 16 日：初始发布