0

I am trying to generate excel sheet for a large data about a million records. But when using the below code I am getting nothing. The code hangs up after line : wb.SaveAs(MyMemoryStream);. I am using closed XML for the purpose. Can you please guide what can be done to save large data as excel.

My Code:

private void DownloadExcel()
    {
        String Attachment = "attachment; filename=TestFile.xlsx";
        Response.ClearContent();
        DataTable dt = ds.Tables[0];


        using (XLWorkbook wb = new XLWorkbook())

        {
            wb.Worksheets.Add(dt, "Data");
            wb.Style.Font.FontName = "Courier New";
            // wb.Cell("A1:A+" + colCount + "").Style.Font.Bold = true;
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", Attachment);
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.SuppressContent = true;  // Gets or sets a value indicating whether to send HTTP content to the client.
                HttpContext.Current.ApplicationInstance.CompleteRequest();
            }
        }
    }

I am ok with any other library as well to save the data as XML.

Anirudh Agarwal
  • 655
  • 2
  • 7
  • 29
  • Does the code work for at least one record? – B001ᛦ Nov 14 '18 at 11:51
  • Yes this is working fine for fewer records like till 50000. When I try to create file with larger records its getting hanged or in other words not going to next line after : wb.SaveAs(MyMemoryStream); – Anirudh Agarwal Nov 14 '18 at 11:58
  • What version of Excel? Because older versions had a row limit of about 65000. Also, it'd be worth trying a different numbers of records to see your limit of where things start to go wrong. – Nanhydrin Nov 14 '18 at 11:59
  • What Nanhydrin said is the problem in your case, perhaps! – Syed Nov 14 '18 at 12:00
  • Got no idea how to mention the version. But the excel created is opened properly without any warning sign in 2016 version. – Anirudh Agarwal Nov 14 '18 at 12:01
  • So any idea or anything that I need to mention to fix the problem? – Anirudh Agarwal Nov 14 '18 at 12:02
  • According to https://stackoverflow.com/questions/526921/why-is-there-still-a-row-limit-in-microsoft-excel Until Office 2007, this limit was 65000 but in Office 2010, it was raised to 1 million. See if the answer in the link can help you – Syed Nov 14 '18 at 12:14
  • 1
    For 1 Mio Records, I highly doubt Excel is a good solution. Can the resultset be split reasonably into smaller sets that you could export to different sheets? – Fildor Nov 14 '18 at 12:19
  • Most non-interop solutions have to load the entire file into memory and process it as an object. For a spreadsheet with 1M rows, this will certainly cause memory problems, especially if your app is 32-bit. Can you use interop and let Excel (the application) do the heavy lifting? – Hambone Nov 17 '18 at 19:38

1 Answers1

0

You can try any of below approaches:

Approach 1: First you need to create the template of the excel and then use the below function. It will create new excel using your template and inserts data into excel using ACE.OLEDB. It will be much faster but you don't have control over formatting.

   public static void CopyDataTableToExcel(DataTable dtExcel, String excelOutputTemplate, string outExcelPath)
    {
        File.Copy(excelOutputTemplate, outExcelPath, true);

        string qryFieldName = "";
        string qryFieldForCreate = "";
        string qryFieldValue = "";
        string qryFieldValueTemp = "";
        string qryInsert = "";

        for (int i = 0; i < dtExcel.Columns.Count; i++)
        {
            qryFieldName = qryFieldName + (qryFieldName.Trim() != "" ? ", " : "") + "[" + dtExcel.Columns[i].ColumnName + "]";
            qryFieldForCreate = qryFieldForCreate + (qryFieldForCreate.Trim() != "" ? ", " : "") +
                 "[" + dtExcel.Columns[i].ColumnName + "] varchar(255)";
        }

        // Establish a connection to the data source.
        System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + outExcelPath + "';Extended Properties=\"Excel 12.0;HDR=YES;\"");
        objConn.Open();

        // Add two records to the table named 'MyTable'.
        System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
        objCmd.Connection = objConn;

        qryInsert = "Create table InventorySheet (" + qryFieldForCreate + ")";
        objCmd.CommandText = qryInsert;
        objCmd.ExecuteNonQuery();

        for (int i = 0; i < dtExcel.Rows.Count; i++)
        {
            qryFieldValue = "";
            for (int j = 0; j < dtExcel.Columns.Count; j++)
            {
                qryFieldValueTemp = dtExcel.Rows[i][j].ToString();
                if (qryFieldValueTemp.Length > 255)
                {
                    qryFieldValueTemp = qryFieldValueTemp.Substring(qryFieldValueTemp.Length - 255);
                }
                qryFieldValue = qryFieldValue + (qryFieldValue.Trim() != "" ? ", '" : "'") + qryFieldValueTemp.Replace("'", "''") + "'";
            }

            //qryInsert = "Insert into [Sheet1$] Values (" + qryFieldValue + ")";
            qryInsert = "Insert into InventorySheet (" + qryFieldName + ") Values (" + qryFieldValue + ")";
            objCmd.CommandText = qryInsert;
            objCmd.ExecuteNonQuery();
        }

        // Close the connection.
        objConn.Close();
        MessageBox.Show("Exported successfully.");
    }

Approach 2: Serialize the data table into xml:

DataTable dtJobMetaData = yourDataSet.Tables["PDF"];
dtJobMetaData.WriteXml(xmlPath);
Brijesh Kumar Tripathi
  • 1,762
  • 15
  • 25