将Excel文件转换为asp.net中的数据库时发生异常 [英] Exception occured while Converting Excel file to database in asp.net

问题描述

我正在尝试使用框架3.5和sql express 2008将Excel Doc转换为数据库，而当我尝试运行它时却出现以下异常:

找不到可安装的ISAM."

我使用的代码是:

SqlConnection con =新的SqlConnection(ConfigurationManager.ConnectionStrings ["Homeconstr"].ConnectionString);
字符串路径= FileUpload1.FileName.ToString();
字符串xlconst = @数据源=" +路径+; Provider = Microsoft.ACE.OLEDB.12.0;扩展属性= Excel 12.0;";
OleDbConnection xlcon =新的OleDbConnection(xlconst);
OleDbCommand xlcom =新的OleDbCommand(从* [Sheet1 $]中选择*"，xlcon);
xlcon.Open(); //异常发生在这里
OleDbDataReader dr;
dr = xlcom.ExecuteReader();
SqlBulkCopy bk =新的SqlBulkCopy(con);
bk.DestinationTableName ="emp_tracker";
bk.WriteToServer(dr);
xlcon.Close();

I am Trying to convert Excel Doc to database I m using framework 3.5 and sql express 2008, when I m trying to run it is giving me following Exception :

"Could not find installable ISAM.."

the code I M using is :

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Homeconstr"].ConnectionString);
string path = FileUpload1.FileName.ToString();
string xlconst = @"Data Source=" + path + ";Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;";
OleDbConnection xlcon = new OleDbConnection(xlconst);
OleDbCommand xlcom = new OleDbCommand("Select * from [Sheet1$]", xlcon);
xlcon.Open(); //Exception Occuring here
OleDbDataReader dr;
dr = xlcom.ExecuteReader();
SqlBulkCopy bk = new SqlBulkCopy(con);
bk.DestinationTableName = "emp_tracker";
bk.WriteToServer(dr);
xlcon.Close();

推荐答案

，xlcon);
xlcon.Open(); //异常发生在这里
OleDbDataReader dr;
dr = xlcom.ExecuteReader();
SqlBulkCopy bk =新的SqlBulkCopy(con);
bk.DestinationTableName ="emp_tracker";
bk.WriteToServer(dr);
xlcon.Close();

", xlcon);
xlcon.Open(); //Exception Occuring here
OleDbDataReader dr;
dr = xlcom.ExecuteReader();
SqlBulkCopy bk = new SqlBulkCopy(con);
bk.DestinationTableName = "emp_tracker";
bk.WriteToServer(dr);
xlcon.Close();

首先，路径变量将仅包含文件名而不是完整路径.为此，您必须使用Server.MapPath来解析上载文件的路径.这意味着您必须将上传的Excel文件存储在服务器上的特定位置，然后才可以执行Server.MapPath.

其次，对于您的特定错误，这是Microsoft所说的:找不到可安装的ISAM [ ^ ]

最后，建议您不要使用这种方法导入Excel数据，因为您不能保证是否在服务器上安装了Excel.此代码需要将Excel安装在服务器上才能工作.作为替代方案，我更喜欢将Excel数据复制并粘贴到文本框中，然后单击提交"或保存"按钮，将分隔的数据解析为列表，然后对其进行迭代，将每条记录保存到数据库中.这样，您可以轻松控制执行并提供更强大的错误处理机制.

First, the path variable would just contain file name and not the full path. For that you would have to use Server.MapPath instead to resolve the path of the uploaded file. Which means you would have to store the uploaded Excel files in a specific location on the server and only then you can do a Server.MapPath.

Second, for your specific error this is what Microsoft has to say about it: Could not find installable ISAM[^]

Finally, as a suggestion, try not using this approach to import Excel data because you cannot guarantee if Excel would be installed on the server. This code would need Excel installed on the server to work. As an alternative, I prefer copying and pasting Excel data into a text box and on the submit or save button click, parse this delimited data into a list and then iterate over it saving each record to the database. This way you can easily control the execution and provide more robust error handling mechanism.

这篇关于将Excel文件转换为asp.net中的数据库时发生异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！