从Excel导入数据 [英] Import Data from Excel

问题描述

i我试图将数据从excel导入sql server table。在本地我可以插入超过7585行，但是当我部署我的网页时应用程序到服务器端我只能导入7584行当我尝试插入超过7584行我得到以下错误。

请帮我解决这个问题。



这是我使用的代码

Hi,

i am trying to import data from excel into sql server table.In local i can able to insert more than 7585 rows but when i deploy my web application to server side i can import only 7584 rows when i try to insert more than 7584 rows i get the following error.
please help me to fix this.

this is the code i use

if (FileUpload1.HasFile)
                    {
                        string _strFileType = "", _strNewFileName = "", _strOLEDBConnection = "";
                        var fileName = Path.GetFileName(FileUpload1.FileName);

                        // get exstension of file
                        _strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();

                        int fileExtPos = fileName.LastIndexOf(".");

                        string newfilename = fileName.Substring(0, fileExtPos);
                        _strNewFileName = newfilename + "___" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss-ffff") + _strFileType;

                        // store the file inside ~/App_Data/uploads folder
                        string _strPath = Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName);
                        FileUpload1.SaveAs(_strPath);

                        string ListName = txtmpListName.Text;
                        string originalfilename = fileName;
                        string _strdate = DateTime.Now.ToString("yyyy-MM-dd");
                        string _strSQL = "";
                        ////Create a connection string to access the Excel file using the ACE provider.
                        ////This is for Excel 2007. 2003 uses an older driver.
                        //var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), fileName)

+ ";Extended Properties=Excel 12.0;");
                        if (_strFileType.Trim() == ".xls")
                        {
                            _strOLEDBConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName) +

";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        }
                        else if (_strFileType.Trim() == ".xlsx")
                        {
                            _strOLEDBConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName) +

";Extended Properties=\"Excel 12.0;HDR=Yes\"";
                        }

                        //Fill the dataset with information from the BV and REV worksheet.
                        var adapterbvandrev = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", _strOLEDBConnection);
                        var dsbvandrev = new DataSet();
                        adapterbvandrev.Fill(dsbvandrev);
}

错误：

System.Data.OleDb.OleDbException（0x80004005）：外部表格不是预期的格式。 System.Data.ProviderBase上的System.Data.OleDb.OleDbConnectionFactory.CreateConnection（DbConnectionOptions选项，DbConnectionPoolKey poolKey，Object poolGroupProviderInfo，DbConnectionPool池，DbConnection owningObject）上的System.Data.OleDb.OleDbConnectionInternal..ctor（OleDbConnectionString constr，OleDbConnection连接） .DbConnectionFactory.CreateConnection在System.Data.ProviderBase（DbConnectionOptions选项，DbConnectionPoolKey poolKey，对象poolGroupProviderInfo，池类DBConnectionPool，的DbConnection owningConnection，DbConnectionOptions USEROPTIONS）在System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection（的DbConnection owningConnection，DbConnectionPoolGroup poolGroup，DbConnectionOptions USEROPTIONS）。 System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection（DbConnection outerC）中的DbConnectionFactory.TryGetConnection（DbConnection owningConnection，TaskCompletionSource`1 retry，DbConnectionOptions userOptions，DbConnectionInternal& connection） System.Data.Common.DbDataAdapter上的System.Data.OleDb.OleDbConnection.Open（）处的System.Data.ProviderBase.DbConnectionInternal.OpenConnection（DbConnection outerConnection，DbConnectionFactory connectionFactory）上的onnection，DbConnectionFactory connectionFactory，TaskCompletionSource`1 retry，DbConnectionOptions userOptions） System.Data.Common.DbDataAdapter.Fill中的.FillInternal（DataSet数据集，DataTable [] datatables，Int32 startRecord，Int32 maxRecords，String srcTable，IDbCommand命令，CommandBehavior行为）（DataSet dataSet，Int32 startRecord，Int32 maxRecords，String srcTable，IDbCommand命令，CommandBehavior行为）在Elector.Content.ImportVoterList.btnSubmit_Click的System.Data.Common.DbDataAdapter.Fill（DataSet dataSet）（对象发送者，EventArgs e）

Error:
System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Elector.Content.ImportVoterList.btnSubmit_Click(Object sender, EventArgs e)

推荐答案

，_strOLEDBConnection）;
var dsbvandrev = new DataSet（）;
adapterbvandrev.Fill（dsbvandrev）;
}

", _strOLEDBConnection); var dsbvandrev = new DataSet(); adapterbvandrev.Fill(dsbvandrev); }

错误：

System.Data.OleDb.OleDbException（0x80004005）：外部表格不是预期的格式。 System.Data.ProviderBase上的System.Data.OleDb.OleDbConnectionFactory.CreateConnection（DbConnectionOptions选项，DbConnectionPoolKey poolKey，Object poolGroupProviderInfo，DbConnectionPool池，DbConnection owningObject）上的System.Data.OleDb.OleDbConnectionInternal..ctor（OleDbConnectionString constr，OleDbConnection连接） .DbConnectionFactory.CreateConnection在System.Data.ProviderBase（DbConnectionOptions选项，DbConnectionPoolKey poolKey，对象poolGroupProviderInfo，池类DBConnectionPool，的DbConnection owningConnection，DbConnectionOptions USEROPTIONS）在System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection（的DbConnection owningConnection，DbConnectionPoolGroup poolGroup，DbConnectionOptions USEROPTIONS）。 System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection（DbConnection outerC）中的DbConnectionFactory.TryGetConnection（DbConnection owningConnection，TaskCompletionSource`1 retry，DbConnectionOptions userOptions，DbConnectionInternal& connection） System.Data.Common.DbDataAdapter上的System.Data.OleDb.OleDbConnection.Open（）处的System.Data.ProviderBase.DbConnectionInternal.OpenConnection（DbConnection outerConnection，DbConnectionFactory connectionFactory）上的onnection，DbConnectionFactory connectionFactory，TaskCompletionSource`1 retry，DbConnectionOptions userOptions） System.Data.Common.DbDataAdapter.Fill中的.FillInternal（DataSet数据集，DataTable [] datatables，Int32 startRecord，Int32 maxRecords，String srcTable，IDbCommand命令，CommandBehavior行为）（DataSet dataSet，Int32 startRecord，Int32 maxRecords，String srcTable，IDbCommand命令，CommandBehavior行为）在Elector.Content.ImportVoterList.btnSubmit_Click（Object sender，EventArgs e）的System.Data.Common.DbDataAdapter.Fill（DataSet dataSet）

Error:
System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Elector.Content.ImportVoterList.btnSubmit_Click(Object sender, EventArgs e)

没有限制Excel中有7584行。在之前的版本Excel 2007中它是65K，但这不是它。

看来你插入的新行有分隔符冲突，所以它无法超越它。

尝试使用其他一些数据，但需要更多行。或者只是在该表单中再次复制现有数据（只需选择10个现有数据并附加）



否则发布您的Excel文件。



您的代码没有任何错误或缺失

There is no limit for 7584 rows in Excel. It was 65K in previous version Excel 2007 but this less its not.
It seems that the new row you insert has the delimitor conflicts so its not able to get beyond that.
Try to take another excel with some other data but more rows. Or simply copy existing data again within that sheet (just select 10 existing and append)

Else post your excel file.

There's nothing wrong or missing with your code

错误不在于您的Web应用程序，而在于您的Excel文件。第7584/7585行的记录需要进行一些测试。确保这2条记录中的数据与其他记录一致。要调试这些记录中的错误，您可以删除第一个7582记录并尝试重新上传。



如其他解决方案中所述，读取数据时没有此限制来自excel文件。

The error is not with your web application, but with your excel file. The record present at row number 7584/7585 needs some testing. Ensure data in these 2 records are consistent with other records. To debug the error in these records, you can delete the 1st 7582 records and try uploading again.

As mentioned in other solution, there is no such limit while reading data from excel file.

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