CODEWITHSUNDEEP
c#exceloledbexcel-interop
Shalabh Gupta
Shalabh Gupta

Reputation: 11

.XLSX files not generated with Worksheets having more than 8K records

I am using Excel 2007 and OLE-DB provider to generate .xlsx files. The files has 2 sheets, 1st one a sheet containing graph and the 2nd Sheet contains Data.

I am using following connection string to generate the excel:

using (OleDbConnection conn = new OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test.xlsx;Extended  Properties=\"Excel 12.0 XML;HDR=Yes;IMEX=0\""))

The Graph is generated using Excel-Interop, then the workbook is closed. Followed by this a new workflow starts that opens the same Excel file and inserts Data.

The Code is something like this.

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilename + ";Extended Properties=\"Excel 12.0 XML;HDR=Yes;IMEX=0\""))
            {
                clsCommon.LogMessages("Excel Connection opening");
                conn.Open();
                clsCommon.LogMessages("Connection opened.");

                using (OleDbCommand cmd_createTable = new OleDbCommand("CREATE TABLE [" + SheetName + "](" + sb.ToString() + ")", conn))
                {
                    cmd_createTable.ExecuteNonQuery();
                    //cmd_createTable.CommandTimeout = 14000;
                }

                clsCommon.LogMessages("Excel Sheet Created.");

                //write the data
                List<string> lstRowData = new List<string>();
                string rowdata = string.Empty;

                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (row[i].ToString().Contains("'"))
                        {
                            rowdata = rowdata + "'" + row[i].ToString().Replace("'", "''") + "',";
                        }
                        else
                        {
                            rowdata = rowdata + "'" + row[i].ToString() + "',";
                        }
                    }
                    rowdata = rowdata.Remove(rowdata.Length - 1, 1);
                    lstRowData.Add(rowdata);
                    rowdata = string.Empty;
                }


                #region Creating Single Command to execute multiple statements.
                using (OleDbCommand cmd_Insert = new OleDbCommand())
                {
                    clsCommon.LogMessages("Data Rows for Sheet:" + SheetName + ", is: " + lstRowData.Count.ToString());                                                
                    cmd_Insert.Connection = conn;
                    cmd_Insert.CommandTimeout = 14000;

                    foreach (string item in lstRowData)
                    {
                        cmd_Insert.CommandText = "Insert into [" + SheetName + "$] values(" + item + ")";
                        cmd_Insert.ExecuteNonQuery();
                    }                        
                    cmd_Insert.Dispose();
                }
                #endregion

                conn.Close();
                conn.Dispose(); 
            }

The problem is that, the excel is getting generated properly where worksheets that have smaller amount of Data.

For the worksheets with data more than 8k(approx), the above lines of code are writing the data to the excel properly, but when the file is buffered to the webpage, the datasheets with huge records are not present.

The Code where the file is buffered to the webpage is as follows:

   Response.Clear();
   Response.ClearContent();
   Response.ClearHeaders();
   Response.BufferOutput = true;
   Response.ContentType = "application/vnd.ms-excel";
   Response.AppendHeader("Content-Disposition", string.Format("attachment; filename={0}", Path.GetFileName(newFilePath)));
   Response.WriteFile(newFilePath);
   if (Response.IsClientConnected)
   {
   Response.Flush();
   }
   try
   {
   Response.Close();
   HttpContext.Current.ApplicationInstance.CompleteRequest();
   }
   catch
   {
   // Eat Exception
   }

The Strange Fact:

The same code works fine on my local development environment and all the worksheets are populated properly. I have Excel 2007 installed on my machine and the server as well. I have checked the permissions on the Server using DCOM Configuration Manager (Under Component Service) and they look correct.

Workarounds Tried

From

Extended Properties=\"Excel 12.0 XML;HDR=Yes;IMEX=0\""))

To

Extended Properties=\"Excel 12.0 ;HDR=Yes;IMEX=0\""))

without luck.

Could you please help me with this issue. Do I need to change some settings of Excel 2007 on my server to enable large data exports.

Thanks for your quick assistance

--Shalabh Gupta

Upvotes: 0

Views: 1503

Answers (2)

Venkat Singri
Venkat Singri

Reputation: 104

i faced same issue for more than 6k records,

changing .xlsx to .xls is worked for me.

Upvotes: 0

Yahia
Yahia

Reputation: 70369

IMPORTANT: Interop (used for the first worksheet in your code) is NOT supported in sever-scenarios (like ASP.NET or similar) by MS.

There are many options to read/edit/create Excel files without Interop:

MS provides the free OpenXML SDK V 2.0 - see http://msdn.microsoft.com/en-us/library/bb448854%28office.14%29.aspx (XLSX only)

This can read+write MS Office files (including Excel).

Another free option see http://www.codeproject.com/KB/office/OpenXML.aspx (XLSX only)

IF you need more like handling older Excel versions (like XLS, not only XLSX), rendering, creating PDFs, formulas etc. then there are different free and commercial libraries like ClosedXML (free, XLSX only), EPPlus (free, XLSX only), Aspose.Cells, SpreadsheetGear, LibXL and Flexcel etc.

Upvotes: 2

Related Questions