将excel数据导入数据库使用asp.net和c＃

Question

在尝试将数据从excel导入数据库时​​出现以下错误。

的Microsoft Office Access数据库引擎找不到对象 'C：\用户\ DAKTARI \桌面\ smarttable.xls'

这是我的代码背后正在使用。

public partial class Smarttable : System.Web.UI.Page 
{ 
    OleDbConnection Econ; 
    SqlConnection con; 

    string constr, Query, sqlconn; 
    protected void Page_Load(object sender, EventArgs e) 
    { 

    } 


    private void ExcelConn(string FilePath) 
    { 

     constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\DAKTARI\Desktop\smarttable.xls;Extended Properties=""Excel 12.0 Xml;HDR=YES;"""); 
     Econ = new OleDbConnection(constr); 

    } 
    private void connection() 
    { 
     sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString; 
     con = new SqlConnection(sqlconn); 

    } 


    private void InsertExcelRecords(string FilePath) 
    { 
     ExcelConn(FilePath); 

     Query = string.Format("Select [InvoiceNumber],[AmountPaid],[Remarks] FROM [C:\\Users\\DAKTARI\\Desktop\\smarttable.xls]", "Orders$"); 
     OleDbCommand Ecom = new OleDbCommand(Query, Econ); 
     Econ.Open(); 

     DataSet ds = new DataSet(); 
     OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ); 
     Econ.Close(); 
     oda.Fill(ds); 
     DataTable Exceldt = ds.Tables[0]; 
     connection(); 
     //creating object of SqlBulkCopy  
     SqlBulkCopy objbulk = new SqlBulkCopy(con); 
     //assigning Destination table name  
     objbulk.DestinationTableName = "smarttable"; 
     //Mapping Table column  
     objbulk.ColumnMappings.Add("InvoiceNumber", "InvoiceNumber"); 
     objbulk.ColumnMappings.Add("AmountPaid", "AmountPaid"); 
     objbulk.ColumnMappings.Add("Remarks", "Remarks"); 
     //inserting Datatable Records to DataBase  
     con.Open(); 
     objbulk.WriteToServer(Exceldt); 
     con.Close(); 

    } 
    protected void Button1_Click(object sender, EventArgs e) 
    { 
     string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName); 
     InsertExcelRecords(CurrentFilePath); 
    } 
} 

Answer 1

改变这一行

Query = string.Format("Select [InvoiceNumber],[AmountPaid],[Remarks] FROM [C:\\Users\\DAKTARI\\Desktop\\smarttable.xls]", "Orders$"); 

要

Query = "Select [InvoiceNumber],[AmountPaid],[Remarks] FROM [Orders$]"; 

的FROM需要跟着它就像一个表处理工作表名称

Answer 2

你的Excel文件格式使用这意味着Office 2003或更早版本的XLS，但您使用的是ACE OLEDB提供程序用于Office 2007或更高版本：

constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\DAKTARI\Desktop\smarttable.xls;Extended Properties=""Excel 12.0 Xml;HDR=YES;""); 

正确的用法是使用Jet 4.0提供这样的：

constr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'", FilePath); 

而且你有一个错误的查询字符串用来读取工作表里面的数据第二个问题：

Query = string.Format("Select [InvoiceNumber],[AmountPaid],[Remarks] FROM [C:\\Users\\DAKTARI\\Desktop\\smarttable.xls]", "Orders$"); 

这应该改为适当的形式如下：

Query = "SELECT [InvoiceNumber],[AmountPaid],[Remarks] FROM [Orders$]";