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, Sep 10 '18 at 07: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, Sep 10 '18 at 07: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, Sep 10 '18 at 07:46
@FrankerZ its taking more than 20 minutes while adding the data table to XLWorkbook object. — Jzl, Sep 10 '18 at 07:46
@martijn I am not saving anything to data base. I am reading from one source (Its a different file format and different files) and writing the final output into one excel file — Jzl, Sep 10 '18 at 07:49
It's quite possible the dataset is so large it causes issues within the memory management system. It might work if cut the work up into smaller pieces, like reading and saving one datatable at a time — martijn, Sep 10 '18 at 08:12
why do you want to dump such a huge dataset into Excel? Is there no better way to work with it? — ADyson, Sep 10 '18 at 09:11
@Jzl: you mean to say there is no way you can open a datatable separately? you have to open them all at once? — martijn, Sep 10 '18 at 09:47
@ADyson my data is like that. then Yes I am looking for other ways too — Jzl, Sep 10 '18 at 09:55
"my data is like that". I don't get your point. Why do you need it in Excel specifically? — ADyson, Sep 10 '18 at 09:57
@martijn I have a data set and different data tables. I just want to export it into different excel sheets. please suggest me if there is any other way to achive it — Jzl, Sep 10 '18 at 09:57
@Jzl - my answer here might help with this. It's a pure OpenXml approach - https://stackoverflow.com/questions/32690851/export-big-amount-of-data-from-xlsx-outofmemoryexception/32787219#32787219 — petelids, Sep 26 '18 at 11:42

Jzl · Answer 1 · 2018-09-13T12:02:40.103

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)

Export large data to excel in ASP.Net C#

1 Answers1