从Excel文件批量注册到SQLServer数据库 [英] Bulk Registration from Excel file to SQLServer database

问题描述

 受保护的  void  linkInsertData_Click(新 OleDbConnection( @"  +  ;扩展属性= Excel 8.0")；
            尝试
            {
                字符串 fname = " ;
                字符串 lname = " ;
                字符串 mobnum = " ;
                字符串 city = " ;
                字符串 state = " ;
                字符串 zip = " ;

                OleDbCommand objCommand = 新建 OleDbCommand(" ，objCon);
                objCon.Open();
                OleDbDataReader oreader = objCommand.ExecuteReader();
                 while (oreader.Read())
                {
                    fname = oreader [ 0 ].ToString();
                    lname = oreader [ 1 ].ToString();
                    mobnum = oreader [ 2 ].ToString();
                    city = oreader [ 3 ].ToString();
                    状态= oreader [ 4 ].ToString();
                    zip = oreader [ 5 ].ToString();
                    AddToSqlServer(fname，lname，mobnum，city，state，zip);
                }
            }
            捕获
            {

            }
        }

        公共 字符串 AddToSqlServer(字符串 fname， string  lname， string  mobnum， string 城市， 字符串状态，字符串 zip)
        {
            布尔 result =  false ;
            尝试
            {
                
                字符串 connectstring = " ;
                SqlConnection con = 新 SqlConnection(connectstring);
                SqlCommand cmd = 新 SqlCommand();
                cmd.CommandText = " ;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;

                cmd.Parameters.Add(新 SqlParameter(" ，fname));
                cmd.Parameters.Add(新 SqlParameter(" ，lname));
                cmd.Parameters.Add(新 SqlParameter(" ，mobnum))；
                cmd.Parameters.Add(新 SqlParameter(" ，城市))；
                cmd.Parameters.Add(新 SqlParameter(" ，状态))；
                cmd.Parameters.Add(新 SqlParameter(" ，zip))；

                cmd.ExecuteNonQuery();结果=  true ;
            }
            捕获
            {
            }
            返回 result.ToString();
        } 

在这里，我正在实现从excel文件对学生数据的批量注册，以及在asp.net中上传文件时的实现
将数据存储在SQLServer数据库中.
我使用的是C#语言.我在声明中弄错了
OleDbCommand objCommand =新的OleDbCommand("select * from [Sheet1 $]"，objCon);
该错误显示"Microsoft Jet数据库引擎找不到对象"Sheet1 $"."

解决方案

，objCon);
该错误显示"Microsoft Jet数据库引擎找不到对象"Sheet1

"."
导致此错误的原因是什么.请给我一个解决方案.

请参阅类似的讨论:
将数据从Excel传输到SQL Server [ ^ ]

protected void linkInsertData_Click(object sender, EventArgs e)
        {
            OleDbConnection objCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\MyProjects\\IIMK Projects\\Test\\example.xls" + ";Extended Properties=Excel 8.0");
            try
            {
                string fname = "";
                string lname = "";
                string mobnum = "";
                string city = "";
                string state = "";
                string zip = "";

                OleDbCommand objCommand = new OleDbCommand("select * from [Sheet1]", objCon);
                objCon.Open();
                OleDbDataReader oreader = objCommand.ExecuteReader();
                while (oreader.Read())
                {
                    fname = oreader[0].ToString();
                    lname = oreader[1].ToString();
                    mobnum = oreader[2].ToString();
                    city = oreader[3].ToString();
                    state = oreader[4].ToString();
                    zip = oreader[5].ToString();
                    AddToSqlServer(fname, lname, mobnum, city, state, zip);
                }
            }
            catch
            {

            }
        }

        public string AddToSqlServer(string fname, string lname, string mobnum, string city, string state, string zip)
        {
            bool result = false;
            try
            {
                
                string connectstring = "Data Source=DEVTRAINEE13-PC\\SQL2008TRA;Initial Catalog=Mockups;Integrated Security=true";
                SqlConnection con = new SqlConnection(connectstring);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "sp_AddfromXcel";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;

                cmd.Parameters.Add(new SqlParameter("@fname", fname));
                cmd.Parameters.Add(new SqlParameter("@lname", lname));
                cmd.Parameters.Add(new SqlParameter("@mobnum", mobnum));
                cmd.Parameters.Add(new SqlParameter("@city", city));
                cmd.Parameters.Add(new SqlParameter("@State", state));
                cmd.Parameters.Add(new SqlParameter("@Zip", zip));

                cmd.ExecuteNonQuery(); result = true;
            }
            catch
            {
            }
            return result.ToString();
        }

Here I am implementing bulk registration of students data from excel file and when uploading file in asp.net
store data in SQLServer database.
I am using c# language.I got mistake in the statement
OleDbCommand objCommand = new OleDbCommand("select * from [Sheet1$]", objCon);
The error shows "Microsoft Jet database engine could not find the object ''Sheet1$''."
what should be the cause of this error.Please give me a solution.

解决方案

", objCon);
The error shows "Microsoft Jet database engine could not find the object ''Sheet1

''."
what should be the cause of this error.Please give me a solution.

Refer similar discussion:
transfer data from Excel to SQL Server[^]

这篇关于从Excel文件批量注册到SQLServer数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！