Transfer Excel To SQL By C/S-优快云博客

本文介绍了一款基于ASP.NET的EXCEL自动化导入SQL数据库工具的实现方法，通过创建页面来简化EXCEL文件的导入流程，提高工作效率。

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

随着公司业务增多,EXCEL需要导入的次数越来越多,为了解决人工导入太繁琐,在网上找了点资料做了个页面自动将EXCEL导入SQL功能

ASPX

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TransferExceltoSQL._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 runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <input id="File1" type="file" runat="server" />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:boundfield DataField="fid"></asp:boundfield>
                <asp:boundfield DataField="fname"></asp:boundfield>
                <asp:boundfield DataField="fdatetime"></asp:boundfield>
            </Columns>
        </asp:GridView>

        <br />
        <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />

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

.ASPX.CS

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Linq;
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;
using System.Xml.Linq;

namespace TransferExceltoSQL
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            CreateTable();
        }
        public void CreateTable()
        {
            string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + File1.PostedFile.FileName.ToString() + ";" +"Extended Properties=Excel 8.0;";

            OleDbConnection objConn = new OleDbConnection(sConnectionString);

            objConn.Open();

            OleDbCommand objCmdSelect = new OleDbCommand("SELECT fid, fname, fdatetime FROM [Sheet1＄]", objConn);

            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

            objAdapter1.SelectCommand = objCmdSelect;

            DataSet objDataset1 = new DataSet();

            objAdapter1.Fill(objDataset1, "test");

            this.GridView1.DataSource = objDataset1.Tables[0].DefaultView;

            this.GridView1.DataBind();

            DataTable dt = objDataset1.Tables[0];

            DataView myView = new DataView(dt);

            SqlConnection conn;

            string dns = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];

            conn = new SqlConnection(dns);

            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "sp_transextosql";

            int count = 0;

            try
            {
                foreach (DataRowView myDrv in myView)
                {
                    count++;
                    if (conn.State.ToString() != "Closed")
                        conn.Close();
                    cmd.Parameters.Clear();

                    SqlParameter paraid = cmd.Parameters.Add("@fid", SqlDbType.Int);
                    SqlParameter paraname = cmd.Parameters.Add("@fname", SqlDbType.NVarChar, (50));
                    SqlParameter paradatetime = cmd.Parameters.Add("@fdatetime", SqlDbType.DateTime);

                    paraid.Direction = ParameterDirection.Input;
                    paraname.Direction = ParameterDirection.Input;
                    paradatetime.Direction = ParameterDirection.Input;

                    paraid.Value = Convert.ToInt32(myDrv[0]);
                    paraname.Value = Convert.ToString(myDrv[1].ToString());
                    paradatetime.Value = Convert.ToDateTime(myDrv[2]);

                    conn.Open();
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                Page.Response.Write("alert('第" + count.ToString() + "条数据出错!');");
                objConn.Close();
                throw ex;
            }
            finally
            {
                objConn.Close();
            }
        }
    }
}

SQL PROC

CREATE proc sp_transextosql
(@fid int,
@fname nvarchar(50),
@fdatetime datetime)
as
insert into t_TransExcelToSQL(f_id, f_name, f_datetime) values(@fid,@fname,@fdatetime)

GO

转载于:https://www.cnblogs.com/anson2020/archive/2007/08/21/864258.html