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?

Question

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.

Answer 1

1. 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.

2. Don't mix UI code SaveFileDialog and operation logic together.

3. Using early return would improve your code readability. It reduce nested statement size. You may google for it.

4. 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.

Answer 2

So I've been going around, trying out different methods, and kinds of codes and implementing my own as well. Thus far, the most successful and fast code that I have come across (and modified) is the following:

        var headers = dgvSearchFilter.Columns.Cast<DataGridViewColumn>();
        string delimiter = ",";
        DataTable dt = new DataTable();
        foreach (DataGridViewColumn col in dgvSearchFilter.Columns)
        {
            dt.Columns.Add(new DataColumn(col.Name, typeof(string)));
        }

        foreach (DataGridViewRow row in dgvSearchFilter.Rows)
        {
            DataRow dataRow = dt.NewRow();
            foreach (DataGridViewCell cell in row.Cells)
            {
                if (row.Cells[cell.ColumnIndex].Value == null || row.Cells[cell.ColumnIndex].Value == DBNull.Value || String.IsNullOrWhiteSpace(row.Cells[cell.ColumnIndex].Value.ToString()))
                {
                    dataRow[cell.ColumnIndex] = " ";
                }
                else
                {
                    dataRow[cell.ColumnIndex] = cell.Value.ToString();
                }
            }
            dt.Rows.Add(dataRow);
        }

        string unique = DateTime.Now.ToString("yyyyMMddHHmmssffff");
        string fileName = "SQLQueryOutput_" + unique + ".csv";
        using (StreamWriter swr = new StreamWriter(File.Open(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), fileName), FileMode.CreateNew), Encoding.Default, 1000000))
        {
            swr.WriteLine(string.Join(",", headers.Select(column => "\"" + column.HeaderText + "\"").ToArray()));
            foreach (DataRow dr in dt.Rows)
            {
                var line = dr.ItemArray.Select(r => r.ToString().Contains(delimiter) || r.ToString().Contains("\n") ? "\"" + r + "\"" : r);
                swr.WriteLine(string.Join(delimiter, line));
            }
        }

        MessageBox.Show("Your file was generated and its ready for use.");

It's not excel format, it's CSV. However, you can use it on a different thread. It generates a CSV on your desktop with a name_uniqueValue.

Basically, you convert the columns of the data grid view into comma separated values. Then you add them in a DataTable. One by one, you loop through the DataGridView and add values to the DataTable. You then use StreamWriter to write those values to a CSV. 1 million rows in almost/less than a minute.

Give it a try for all those who want to convert DataGridView into CSV.