c#multithreading winforms export-to-excel

C# Windows Form - How do I run my export to excel on a different Thread AND where do I include code for a progress bar?

I get over 100000+ rows in a Data Grid View at times and I want the users to be able to export them to excel faster. Currently, my windows form goes in the "Not Responding" stage but it is actually carrying out the export in the Back End. I want to carry out the export on a different thread so that it is faster, AND I want to add a progress bar to show the progress of the export itself.

I have tried the following:

Create a new Task - time to export actually gets longer
Create a different thread to run on using Thread thread = new Thread(Export) - it gives an error when the show Dialogue Box line is run

My code is as follows:

 private void BtnSearchExportCSV_Click(object sender, EventArgs e)
    {
        Export();
    }

    private void CopyAllToClipBoard()
    {
        dgvSearchFilter.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
        dgvSearchFilter.MultiSelect = true;
        dgvSearchFilter.RowHeadersVisible = false;
        dgvSearchFilter.SelectAll();

        DataObject dataObj = dgvSearchFilter.GetClipboardContent();
        if (dataObj != null)
        {
            Invoke((Action)(() => { Clipboard.SetDataObject(dataObj); }));
            //Clipboard.SetDataObject(dataObj);
        }
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occurred while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

    private void Export()
    {
        try
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel Documents (*.xls)|*.xls";
            sfd.FileName = "Export.xls";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                // Copy DataGridView results to clipboard
                CopyAllToClipBoard();

                object misValue = System.Reflection.Missing.Value;
                Excel.Application xlexcel = new Excel.Application();
                // Without this you will get two confirm overwrite prompts
                xlexcel.DisplayAlerts = false;
                Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
                Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                // Paste clipboard results to worksheet range
                Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
                CR.Select();
                xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

                // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
                // Delete blank column A and select cell A1
                //Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
                //delRng.Delete(Type.Missing);
                //xlWorkSheet.get_Range("A1").Select();

                // Save the excel file under the captured location from the SaveFileDialog
                xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlexcel.DisplayAlerts = true;
                xlWorkBook.Close(true, misValue, misValue);
                xlexcel.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlexcel);

                // Clear Clipboard and DataGridView selection
                Clipboard.Clear();
                dgvSearchFilter.ClearSelection();

                // Open the newly saved excel file
                if (File.Exists(sfd.FileName))
                    System.Diagnostics.Process.Start(sfd.FileName);
            }
        }
        catch (Exception exception)
        {
            MessageBox.Show("The following exception occurred: " + exception.ToString());
        }
    }
}

I'm getting more familiar with C#. However, this is the first time I have encountered something like this.

Thank you.

Solution

Moving your operation to another thread would NOT make it faster, but it would not block UI anymore. User won't see "unresponsive application".

Because your code is triggered by Button.Click which is running by UI thread. If your operation takes time, your UI would be block by your operation.
Don't mix UI code SaveFileDialog and operation logic together.
Using early return would improve your code readability. It reduce nested statement size. You may google for it.
System.Threading.ThreadStateException is occurred because you're using SaveFileDialog and ClipBoard in thread. To resolve this, you need to move this two function out of your Thread invoke function. If you really want to make it work. Following might getting it work. However I don't suggest this implementation.
```
Thread op = new Thread( operation );
op.SetApartmentState( ApartmentState.STA );
op.Start();
```

Example below, including where you put progress window:

private void Export()
{
  // Do UI check first
  SaveFileDialog sfd = new SaveFileDialog();
  sfd.Filter = "Excel Documents (*.xls)|*.xls";
  sfd.FileName = "Export.xls";

  // If failed , early return
  if (sfd.ShowDialog() != DialogResult.OK)
  {
    return;
  }
  ProgressWindow prg = new ProgressWindow();
  prg.Show();
  // Do your copy and export code below, you may use task or thread if you don't want to let current form unresponsive.
  operation();
  // After finished, close your progress window
  prg.Close();
}

void operation()
{
     // Copy DataGridView results to clipboard
     CopyAllToClipBoard();

     object misValue = System.Reflection.Missing.Value;
     Excel.Application xlexcel = new Excel.Application();
     // Without this you will get two confirm overwrite prompts
     xlexcel.DisplayAlerts = false;
     Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
     Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
     // Paste clipboard results to worksheet range
     Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
     CR.Select();
     xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

     // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
     // Delete blank column A and select cell A1
     //Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
     //delRng.Delete(Type.Missing);
     //xlWorkSheet.get_Range("A1").Select();

     // Save the excel file under the captured location from the SaveFileDialog
     xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
     xlexcel.DisplayAlerts = true;
     xlWorkBook.Close(true, misValue, misValue);
     xlexcel.Quit();

     releaseObject(xlWorkSheet);
     releaseObject(xlWorkBook);
     releaseObject(xlexcel);

     // Clear Clipboard and DataGridView selection
     Clipboard.Clear();
     dgvSearchFilter.ClearSelection();

     // Open the newly saved excel file
     if (File.Exists(sfd.FileName))
         System.Diagnostics.Process.Start(sfd.FileName);
}

Per your description, ProgressWindow could be a Form without "close" button.