How to implement backgroundworker or threading in ASP.NET(C#) 4.0 WEB application for exporting large amount of data in to excel?

Question

0.00/5 (No votes)

See more:

, +

I want to export some complex data into excel sheet which takes more time and block application so that user cannot proceed further until this task has completed so i have used backgroundworker for that but still it block application and user has to wait until export functionality will completed. Is there any other approaches available for that or what should i change in my code to implement it? to export data into excel i am using ClosedXML Library and my application is on .net 4.0

What I have tried:

<%@ Page Title="" Language="C#" MasterPageFile="~/export_module.master"
AutoEventWireup="true" CodeFile="GenerateReport.aspx.cs"
Inherits="" Async="true" %>


public readonly BackgroundWorker worker = new BackgroundWorker();

protected void Page_Load(object sender, EventArgs e)
{
 if (!Page.IsPostBack)
    {
        worker.WorkerReportsProgress = true;
        worker.WorkerSupportsCancellation = true;
        worker.DoWork += new DoWorkEventHandler(DoWork);
        //worker.ProgressChanged += new ProgressChangedEventHandler(WorkerProgressChanged);
        worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(WorkerCompleted);
    }
}

protected void btn_Export_Click(object sender, EventArgs e)
  {        
    if (!worker.IsBusy){
      worker.RunWorkerAsync("ExportReport");
    }
  }     


 private void DoWork(object sender, DoWorkEventArgs e)
  {
    exportToExcel();
  }

  private void WorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
  { 
  }

 public void ExportReportWithHeaderClosedXML(string reportName, string fileName,  DataTable dataTable)
{
    int usedCells = dataTable.Columns.Count;
    string ReportDate = string.Empty;
    string attachment = "inline;filename=" + fileName + ".xlsx";
    using (XLWorkbook wb = new XLWorkbook())
    {

        //Add method of ClosedXML class library only accepts worksheet name of 31 characters.

        IXLWorksheet worksheet = wb.Worksheet(1);           

        //Insert Report Data
        worksheet.Cell(4, 1).InsertTable(dataTable);
        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ClearHeaders();
        HttpContext.Current.Response.Buffer = true;

        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("Content-Disposition", "inline;filename=" + fileName + ".xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
            HttpContext.Current.Response.End();
        }
    }
}

Posted 3-Apr-19 19:07pm

shah.nilang

Updated 4-Apr-19 2:32am

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2019-04-04T02:32:00

There's no point in using a BackgroundWorker for this. It won't make any difference - the browser will be stuck waiting for the response from the server until the server sends the response. Pushing the code that generates the response onto a background thread will just put more strain on the server, without affecting what happens on the client.

If you want the user to be able to continue interacting with your site whilst the Excel file is being generated, you're going to need to make some changes:

The Excel generation needs to happen in a separate handler - preferably a "generic handler" (.ashx file).
The handler which generates the Excel file needs to disable session state. If you don't, other requests from the same session will be blocked until the handler finishes.
Replace the <asp:Button> which currently triggers the download with a <asp:HyperLink> pointing to the Excel handler.
Set target="_blank" on the HyperLink so that the link opens in a new tab. If you don't, the browser will freeze the current page until the navigation is complete.

This is by no means perfect - the user will see a blank tab whilst the download is being generated, and will have to switch back to the previous tab to continue interacting with your site. If they close the blank tab, the download will be cancelled.

A better option would be to work out why your export is taking so long, and find a way to speed it up. This would require you to profile your code to find the bottleneck.