如何使用ASP.NET将Excel数据导入到sqlserver数据库表中? [英] how to import excel data into sqlserver database table using ASP.NET?

问题描述

我收到了错误消息，因为Microsoft Jet数据库引擎找不到对象"sheet1 $".确保对象存在，并且正确拼写其名称和路径名称.

我的代码是:

I got the error as The Microsoft Jet database engine could not find the object ''sheet1$''. Make sure the object exists and that you spell its name and the path name correctly.

MY CODE IS:

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

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

        }
        protected void btnSend_click(object sender, EventArgs e)
        {
            String strConnection = "Data Source=MANIADSBHI-PC;Initial Catalog=master;Integrated Security=True";
            //file upload path
            string path = fileuploadExcel.PostedFile.FileName;
            //Create connection string to Excel work book
            string excelConnectionString = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source=D:sampledata2003.xls;Extended Properties=Excel 8.0;Persist Security Info=False";
            //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            excelConnection.Open();
            //Create OleDbCommand to fetch data from Excel

            OleDbCommand cmd = new OleDbCommand("Select * from  [sheet1$]", excelConnection);
            OleDbDataReader da= cmd.ExecuteReader(); 
             
            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
           // Give your Destination table name
            sqlBulk.DestinationTableName = "emp";
            sqlBulk.WriteToServer(da);
            excelConnection.Close();
        }
    }
} 

请任何人帮助我解决这个错误，我在第31,32行得到了错误
OleDbCommand cmd =新的OleDbCommand(从[sheet1 $]中选择*"，excelConnection);
OleDbDataReader da = cmd.ExecuteReader();

Pls anyone help me in this error, i got the error on line no:31,32
OleDbCommand cmd = new OleDbCommand("Select * from [sheet1$]", excelConnection);
OleDbDataReader da= cmd.ExecuteReader();

推荐答案

''.确保对象存在，并且正确拼写其名称和路径名称.

我的代码是:

''. Make sure the object exists and that you spell its name and the path name correctly.

MY CODE IS:

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

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

        }
        protected void btnSend_click(object sender, EventArgs e)
        {
            String strConnection = "Data Source=MANIADSBHI-PC;Initial Catalog=master;Integrated Security=True";
            //file upload path
            string path = fileuploadExcel.PostedFile.FileName;
            //Create connection string to Excel work book
            string excelConnectionString = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source=D:sampledata2003.xls;Extended Properties=Excel 8.0;Persist Security Info=False";
            //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            excelConnection.Open();
            //Create OleDbCommand to fetch data from Excel

            OleDbCommand cmd = new OleDbCommand("Select * from  [sheet1

"，excelConnection)； OleDbDataReader da = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = 新 SqlBulkCopy(strConnection); // 输入目标表名称 sqlBulk.DestinationTableName = " ; sqlBulk.WriteToServer(da); excelConnection.Close(); } } }

", excelConnection); OleDbDataReader da= cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); // Give your Destination table name sqlBulk.DestinationTableName = "emp"; sqlBulk.WriteToServer(da); excelConnection.Close(); } } }

请任何人帮助我解决这个错误，我在第31,32行得到了错误
OleDbCommand cmd =新的OleDbCommand(从[sheet1

Pls anyone help me in this error, i got the error on line no:31,32
OleDbCommand cmd = new OleDbCommand("Select * from [sheet1

"，excelConnection中选择*);
OleDbDataReader da = cmd.ExecuteReader();

", excelConnection);
OleDbDataReader da= cmd.ExecuteReader();

这篇关于如何使用ASP.NET将Excel数据导入到sqlserver数据库表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！