Microsoft.ACE.OLEDB.12.0 ( 64-bit ) : Insert into EXCEL .xlsm return blank sheet after 2000 rows

See more:

C#

ASP.NET

Hi All,

I encountered the following issue with Microsoft.ACE.OLEDB.12.0 ( 64-bit ), kindly advise :


Excel file has many sheets and huge number of columns but no. of rows on these worksheets are less than 2000, hence all worksheet are filling well except one having no. of rows are more then 2000. On local environment all working fine, but after hosting on IIS (on LIVE also) it's failing.
I have tried on LIVE server on debug mode (using visual studio) and it worked their. But the same site files via IIS failing.

Environment :
- AccessDatabaseEngine 2010 ( x64 ) + SP1
- Windows Server 2008 R2 Enterprise ( 64-bit )
- Microsoft SQL Server 2008 R2 Enterprise Edition (64-bit)
- IIS 7
- Simple Excel_Template.xlsm with columns layout A-AM

Scripting ( with ASP.NET - C# ) to retrieve selected data from MSSQL and insert into the excel template file.

Everything working FINE if the dataset / number of rows less than 2000.
The scripts started not behave correctly after 200X onwards :
- No exception when debugging
- OleDbCommand.ExecuteNonQuery successful ( return indicator 1 throughout entire loops )
- If put a break at 2000 row count then excel file .xlsm was generated successfully
- If put a break at 2010 row count then excel file .xlsm was generated but output file is blank ( exactly same as template file before insert any row/data )
- All processes, loops and connections are working fine.
- No data or formatting issue because the remaining rows having similar length and pattern

The scripts are similar to the below :

public static bool fillExcelFile(string filePath, DataSet dataSet, string sheetName, string tempSheetName)
    {


        DataTable oledbdatatbl = null;
        using (OleDbDataAdapter oledbadap = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", sheetName),
                  string.Format(@"Data Source={0};Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;", filePath)))
        {
            try
            {
                oledbdatatbl = new System.Data.DataTable("ReportTable");
                oledbadap.Fill(oledbdatatbl);
            }
            catch (Exception ex)
            {
                Error_SendMail(ex);
                return false;
            }
        }






        System.Text.StringBuilder sbColumns = new System.Text.StringBuilder();
        int coloumsCount = 0;


        for (int icol = 0; icol < (oledbdatatbl.Columns.Count); icol++)
        {
            coloumsCount++;
            sbColumns.Append(string.Format(",[{0}]", oledbdatatbl.Columns[icol].ColumnName));

        }

        if (sbColumns.Length > 0)
            sbColumns.Remove(0, 1);




        using (OleDbConnection oledbConn = new OleDbConnection(string.Format(@"Data Source={0};Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;", filePath)))
        {
            using (OleDbCommand oledbCmd = new OleDbCommand())
            {
                oledbConn.Open();

                System.Text.StringBuilder sbValues = new System.Text.StringBuilder();
                int valuesCount = 0;

                for (int irow = 0; irow < dataSet.Tables[0].Rows.Count; irow++)
                {
                    if (sbValues.Length > 0)
                        sbValues.Remove(0, sbValues.Length);

                    try
                    {
                        valuesCount = 0;
                        for (int idatacol = 0; idatacol < dataSet.Tables[0].Columns.Count; idatacol++)
                        {
                            valuesCount++;

                            sbValues.Append(string.Format(",'{0}'", Convert.ToString(dataSet.Tables[0].Rows[irow][idatacol]).Contains("'") ?
                                Convert.ToString(dataSet.Tables[0].Rows[irow][idatacol]).Replace("'", "''") :
                                Convert.ToString(dataSet.Tables[0].Rows[irow][idatacol])));
                        }

                        if (sbValues.Length > 0)
                            sbValues.Remove(0, 1);
                    }
                    catch (Exception ex)
                    {
                        Error_SendMail(ex);
                        continue;
                    }


                    if (coloumsCount == valuesCount)
                    {
                        try
                        {
                            oledbCmd.CommandText = string.Format("insert into [{0}$]({1})values({2})", tempSheetName, sbColumns.ToString(), sbValues.ToString());
                            oledbCmd.CommandType = System.Data.CommandType.Text;

                            if (oledbConn.State == System.Data.ConnectionState.Closed)
                                oledbConn.Open();

                            oledbCmd.Connection = oledbConn;
                            oledbCmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {

                            if (oledbConn.State == System.Data.ConnectionState.Open)
                                oledbConn.Close();
                            Error_SendMail(ex);
                        }
                    }
                }

                if (oledbConn.State == System.Data.ConnectionState.Open)
                    oledbConn.Close();
            }
        }
        return true;
    }