如何解决'OLE DB提供程序";Microsoft.ACE.OLEDB.12.0";对于链

Question

我在.net应用程序中使用excel数据导入到sql表，但在保存数据时出现以下错误：

我的代码：

protected void btnSave_Click(object sender, EventArgs e)
{
string FileName = lblFileName.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 07
CommandText = "spx_ImportFromExcel07";
break;
}
//Read Excel Sheet using Stored Procedure
//And import the data into Database Table
String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
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 = rbHDR.SelectedItem.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.";  
}
catch (Exception ex)
{
lblMessage.ForeColor = System.Drawing.Color.Red;     
lblMessage.Text = ex.Message;  
}
finally
{
con.Close();
con.Dispose();
Panel1.Visible = true;
Panel2.Visible = false; 

在web.config中使用excel格式的连接字符串，如下所示：

<connectionStrings>
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'"/>

我想不通这个错误。有人能帮忙吗？

Answer 1

要读取c#代码中的excel文件，您应该有microsoft oledb提供程序。读取xlsx格式的文件需要Microsoft.ACE.OLEDB.12.0提供程序，而Microsoft.Jet.OLEDB.4.0用于xls格式的文件。用于读取excel文件的C#代码如下所示。

using System.Data.OleDb;
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Filename.xls") + ";Extended Properties=Excel 8.0"); 
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);
con.Open(); 
OleDbDataReader dr = cmd.ExecuteReader(); 
string col0 = ""; 
string col1 = ""; 
string col2 = ""; 
while (dr.Read()) 
{ 
col0 =Convert.ToString(dr[ 0]); 
col1 =Convert.ToString(dr[ 0]); 
col2 =Convert.ToString(dr[ 0]); 
/*******************************
Write Statements to Insert values of each rows into table 
*********************************/
} 
con.Close(); 
}

Answer 2

这是一篇将数据从excel导入数据库的好文章。将Excel导入Sql按照那里的指示去做，你就会成功。