ASP.NET MVC2 and IIS 7.5 . Problems when read large data from Excel 2010

Question

I have a problem when reading large amount of data from Excel 2010

I use the code below to read data:

public static DataTable GetExcelData(string filePath, string extension)
        {
            try
            {
                string conStr = "";
                switch (extension)
                {
                    case ".xls": //Excel 97-03
                        //conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                        //conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR={YES}'";
                        conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
                        break;
                    case ".xlsx": //Excel 07
                        //conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                        conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
                        break;
                }

                //conStr = String.Format(conStr, filePath, true);
                OleDbConnection connExcel = new OleDbConnection(conStr);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataTable dt = new DataTable();
                cmdExcel.Connection = connExcel;

                //Get the name of First Sheet
                log.Debug("Excel ConnectionString = " + conStr);
                connExcel.Open();
                log.Debug("Open Excel connection ok");
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                connExcel.Close();
                log.Debug("SHEET NAME = " + SheetName);
                //Read Data from First Sheet
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                oda.SelectCommand = cmdExcel;
                oda.Fill(dt);
                connExcel.Close();

                return dt;
            }
            catch(Exception ex)
            {
                log.Debug("GetExcelData Error:" + ex.ToString());
                return null;
            }
        }

My ASP.NET MVC 2.0 project works correctly when running on Visual Studio 2010 with both a small Excel file and a large Excel file.

But, when I deploy to IIS 7.5 (windows 7 pro), my website only works correctly with small Excel file.

With a large excel file, it throw the following error:

GetExcelData 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() 

(On IIS 7.5, my website uploads large file Excel success)

How can I fix this?

Answer 1

It might be a web.config change that is needed to allow larger files over 4MB. You can add the following to the web.config to allow this,

<configuration>
    <system.web><!-- maxRequestLength (KB) -->
        <httpRuntime maxRequestLength="10000" executionTimeout="110"/>
    </system.web>
</configuration>

This may help,

Maximum request length exceeded

I use the following connection string where '{0}' is the file path, for .xls files,

strConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties= ""Excel 12.0 Xml;HDR=YES;IMEX=1"""

and for .xlsx files I use,

strConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""";

It won't be the size of the files if you have set up the web.config up. I would try and save the file as an 97-2003 workbook .xls file and use the relevant connection string. If you are hosting this on a server, the server will need the Office drivers needed to process the file.

UPDATE Set HDR to No and go through each row dismissing the header row. This will avoid a data type issue that occurs when the columns are checked for the data type.