Inport Excel File To Sql Server Table

最新推荐文章于 2022-08-04 16:14:06 发布

原创最新推荐文章于 2022-08-04 16:14:06 发布 · 871 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #excel #table #file #dataset #string

ASP.NET 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一种将Excel文件中的数据导入到SQL Server数据库的方法。通过使用OleDb连接Excel并读取数据，然后逐条插入到指定的SQL Server表中。此过程包括连接字符串配置、SQL命令执行等关键步骤。

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

public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataSet ds;
        ds = GetOleData("D://excel.xls");
        try
        {
            SqlConnection conn = new SqlConnection("Server=localhost;database=GestDB;uid=sa;pwd=;");
            conn.Open();
            SqlCommand comm = new SqlCommand();
            for (int i = 0; i < ds.Tables["MyRecords"].Rows.Count; i++)
            {
                    string[] strArray ={ ds.Tables["MyRecords"].Rows[i].ItemArray[0].ToString(), ds.Tables["MyRecords"].Rows[i].ItemArray[1].ToString(), ds.Tables["MyRecords"].Rows[i].ItemArray[2].ToString() };
                    string sqlstr = "insert into test values('" + strArray[0].ToString() + "','" + strArray[1].ToString() + "','" + strArray[2].ToString() + "')";
                    comm.CommandText = sqlstr;
                    comm.Connection = conn;
                    comm.ExecuteNonQuery();
            }
            conn.Close();
        }
        catch (Exception ee)
        {
            throw ee;
        }
    }
    private DataSet GetOleData(string FileAddr)
    {
        OleDbConnection objConn = null;
        DataSet ds = new DataSet();

        string strConn = "Provider=Microsoft.Jet.OleDB.4.0;" + "Data Source=D://excel.xls;Extended Properties=Excel 8.0;";
        objConn = new OleDbConnection(strConn);
        objConn.Open();
        string strSql = "select * from [Sheet1$A1:C5]";
        OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
        OleDbDataAdapter sqlada = new OleDbDataAdapter();
        sqlada.SelectCommand = objCmd;
        sqlada.Fill(ds, "MyRecords");
        objConn.Close();
        return ds;
    }
}