Inport Excel File To Sql Server Table

最新推荐文章于 2022-09-09 07:51:56 发布

dreamlins

最新推荐文章于 2022-09-09 07:51:56 发布

阅读量860

点赞数

CC 4.0 BY-SA版权

分类专栏： ASP.NET 文章标签： sql server excel table file dataset string

本文链接：https://blog.youkuaiyun.com/dreamlins/article/details/1222726

ASP.NET 专栏收录该内容

8 篇文章

订阅专栏

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

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

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;
    }
}