使用ASP.Net将Excel工作表数据导入SQL Server [英] Import Excel Sheet data into SQL Server using ASP.Net

问题描述

我已编写以下代码，使用ASP.Net将Excel工作表数据导入SQL Server ...此查询出错 0bject count = cmd.ExecuteNonQuery（）; 并显示此消息

找不到存储过程'spx_Import来自Excel 2007'

I have write the below code to Import Excel Sheet data into SQL Server using ASP.Net...An error occur at this query 0bject count = cmd.ExecuteNonQuery(); and display this message
Could not find stored procedure 'spx_Import From Excel 2007'

public partial class Admin_Impoert_Excell_File : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void upload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            GetExcelSheets(FilePath, Extension, "Yes");
        }
    }

        private void GetExcelSheets(string FilePath, string Extension, string isHDR)
    {
        string conStr="";
        switch (Extension)
        {
           // case ".xls": //Excel 1997-2003
               // conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
               // break;
            case ".xlsx": //Excel 2007
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        //Get the Sheets in Excel WorkBook
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        cmdExcel.Connection = connExcel;
        connExcel.Open();

        //Bind the Sheets to DropDownList
        ddlSheets.Items.Clear();
        ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));
        ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        ddlSheets.DataTextField = "TABLE_NAME";
        ddlSheets.DataValueField = "TABLE_NAME";
        ddlSheets.DataBind();
        connExcel.Close();
        txtTable.Text = "";
       displayname.Text = Path.GetFileName(FilePath);
        upload.Visible = false;
        FileUpload1.Visible = false;
        Panel1.Visible = true; 

    }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string FileName = displayname.Text;
            string Extension = Path.GetExtension(FileName);
            string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
            string CommandText = "";
            switch (Extension)
            {
                //case ".xls": //Excel 97-03
                 //   CommandText = "spx_ImportFromExcel03";
                  //  break;
                case ".xlsx": //Excel 2007
                    CommandText = "spx_Import From Excel 2007";
                    break;
            }
            //Read Excel Sheet using Stored Procedure
            //And import the data into Database Table
            String strConnString = ConfigurationManager.AppSettings["OTAConnectionString"];
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = CommandText;
            cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value = ddlSheets.SelectedItem.Text;
            cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = FolderPath + FileName;
            cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value = yes.Text;
            cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = txtTable.Text;
            cmd.Connection = con;
            try
            {
                con.Open();
                object count = cmd.ExecuteNonQuery();
                lblmessage.ForeColor = System.Drawing.Color.Green;
                lblmessage.Text = count.ToString() + " records inserted.";
            }
                       
        }

        
}

推荐答案

错误消息非常明确：您的数据库不包含名为的存储过程spx_Import来自Excel 2007 。



你确定那些空间应该在那里吗？看看你的2003分支，我希望这个过程被称为 spx_ImportFromExcel2007 。

The error message is quite clear: your database doesn't contain a stored procedure called spx_Import From Excel 2007.

Are you sure those spaces are supposed to be there? Looking at your 2003 branch, I'd expect the procedure to be called spx_ImportFromExcel2007 instead.

这篇关于使用ASP.Net将Excel工作表数据导入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！