Export large data to excel in ASP.Net C#

Question

I am trying to exporting one data set to excel file. the data set contains more than 20 data table (Each table will appear as the separate sheet in final output excel file) and each table contains more than 30+k rows, around 10 columns. I am reading this data from other resource and holding it into the dataset. not using any database to store it. I am using Open XML and Closed XML from here to do the process. its working fine up to 15-20 data table, but more than 25 its taking too much time. Any suggestions or help will be highly appreciated for this.

Sample code:

 using (XLWorkbook wb = new XLWorkbook())
 {
   foreach (DataTable dt in dsData.Tables)
   {
     wb.Worksheets.Add(dt);
   }
   Response.Clear();
   Response.Buffer = true;
   Response.Charset = "";
   Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
   Response.AddHeader("content-disposition", "attachment;filename=DataSet.xlsx");
   wb.SaveAs(Response.OutputStream);
   Response.Flush();
   Response.End();   
 }

Define "Taking too much time". How long is it taking, is the issue when you're opening the file, or downloading the file? — Blue
– Blue, Commented Sep 10, 2018 at 7:40
Why do you save to a MemoryStream, and then immediately copy that to the OutputStream? Is there a reason why you don't just save to the OutputStream? — ProgrammingLlama
– ProgrammingLlama, Commented Sep 10, 2018 at 7:41
Normally I would never try to save an entire database to excel. For one, databases can contain a lot more data than excel can manage. What is the reason you're saving the database to excel? — martijn
– martijn, Commented Sep 10, 2018 at 7:46
@FrankerZ its taking more than 20 minutes while adding the data table to XLWorkbook object. — Jzl
– Jzl, Commented Sep 10, 2018 at 7:46
@Jzl: you mean to say there is no way you can open a datatable separately? you have to open them all at once? — martijn
– martijn, Commented Sep 10, 2018 at 9:47

Jzl · Accepted Answer · 2018-09-13 12:02:40Z

After some research I came up a solution for this, I tested below code with 1 million + data and its woking fine. May be it will be helpful for some one else. I removed the closedxml reference and I added "Interop.Microsoft.Office.Core.dll" refernce. then I wrote the following code.

Required Namespace: using Excel = Microsoft.Office.Interop.Excel, using System.Runtime.InteropServices

    Excel.Workbook xlWorkBook;
    Excel.Application xlApp;
    Excel.Worksheet xlWorkSheet;
    Excel.Range Startrange;
    Excel.Range HeaderStartrange;
    public bool StartExport(DataTable dtbl, bool isFirst, bool isLast, string strOutputPath, string TemplateLocation, string TemplateFullName, int SectionOrder, int totalNoOfSheets)
    {
        bool isSuccess = false;
        try
        {
            if (isFirst)
            {
                CopyTemplate(TemplateLocation, strOutputPath, TemplateFullName);
                xlApp = new Excel.Application();
                if (xlApp == null)
                {
                    throw new Exception("Excel is not properly installed!!");
                }
                xlWorkBook = xlApp.Workbooks.Open(@strOutputPath + TemplateFullName, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                // To Add Sheets Dynamically
                for (int i = 0; i <= totalNoOfSheets; i++)
                {
                    int count = xlWorkBook.Worksheets.Count;
                    Excel.Worksheet addedSheet = xlWorkBook.Worksheets.Add(Type.Missing,
                            xlWorkBook.Worksheets[count], Type.Missing, Type.Missing);
                    addedSheet.Name = "Sheet " + i.ToString();
                }
            }
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(SectionOrder);
            Startrange = xlWorkSheet.get_Range("A2");
            HeaderStartrange = xlWorkSheet.get_Range("A1");
            FillInExcel(Startrange, HeaderStartrange, dtbl);
            xlWorkSheet.Name = dtbl.TableName;
            if (isLast)
            {
                xlApp.DisplayAlerts = false;
                xlWorkBook.SaveAs(@strOutputPath + TemplateFullName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                xlWorkBook.Close(true, null, null);
                xlApp.Quit();
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
                GC.WaitForPendingFinalizers();
                GC.Collect();
                isSuccess = true;
            }
        }
        catch (Exception ex)
        {
            isSuccess = false;
            throw;
        }
        return isSuccess;
    }

    private void FillInExcel(Excel.Range startrange, Excel.Range HeaderStartRange, DataTable dtblData)
    {
        int rw = 0;
        int cl = 0;
        try
        {
            // Fill The Report Content Data Here
            rw = dtblData.Rows.Count;
            cl = dtblData.Columns.Count;
            string[,] data = new string[rw, cl];
            // Adding Columns Here
            for (var row = 1; row <= rw; row++)
            {
                for (var column = 1; column <= cl; column++)
                {
                    data[row - 1, column - 1] = dtblData.Rows[row - 1][column - 1].ToString();
                }
            }
            Excel.Range endRange = (Excel.Range)xlWorkSheet.Cells[rw + (startrange.Cells.Row - 1), cl + (startrange.Cells.Column - 1)];
            Excel.Range writeRange = xlWorkSheet.Range[startrange, endRange];
            writeRange.Value2 = data;
            writeRange.Formula = writeRange.Formula;
            data = null;
            startrange = null;
            endRange = null;
            writeRange = null;
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

I Called this startExport function in above for each loop with required parameters like(Data, Sheet first or last, output path,template name, section order and total number of sheets required)

Collectives™ on Stack Overflow

Export large data to excel in ASP.Net C#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related