Exception of Backgroundworker Progress

Question

I used the Backgroundworker to display progress when exporting data to excel. But exeption happen related to Thread. Here is my code:

    public frmPLANNING()
        {
            InitializeComponent();
            lblStatus.Text = string.Empty;
            backgroundWorker1.ProgressChanged += new ProgressChangedEventHandler(backgroundWorker1_ProgressChanged);
            backgroundWorker1.DoWork += new DoWorkEventHandler(backgroundWorker1_DoWork);
            backgroundWorker1.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker1_RunWorkerCompleted);
            backgroundWorker1.WorkerReportsProgress = true;
            progressBar1.Maximum = 100;
        }
private void tbrExport_Click(object sender, EventArgs e)
        {
            if (backgroundWorker1.IsBusy)
            {
                backgroundWorker1.CancelAsync();
            }
            else
            {
                progressBar1.Value = progressBar1.Minimum;

                backgroundWorker1.RunWorkerAsync();
            }
        }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {            
            SqlConnection cnn;
            var ssqltable = "PLANNING";
            string sql = null;
            string data = null;
            var i = 0;
            var j = 0;
            Microsoft.Office.Interop.Excel.Application xlApp;
            Workbook xlWorkBook;
            Worksheet xlWorkSheet;
            object misValue = Missing.Value;
            xlApp = new Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
            cnn = new SqlConnection(strConStr);
            cnn.Open();
            sql = "SELECT * From " + ssqltable;
            var dscmd = new SqlDataAdapter(sql, cnn);
            var ds = new DataSet();
            dscmd.Fill(ds);           
                foreach (DataTable dt in ds.Tables)
                {
                    for (var i1 = 0; i1 < dt.Columns.Count; i1++)
                    {
                        xlWorkSheet.Cells[1, i1 + 1] = dt.Columns[i1].ColumnName;
                    }
                }
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {                   
                    var s = i + 1;

                    for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                    {
                        data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                        xlWorkSheet.Cells[s + 1, j + 1].EntireColumn.NumberFormat = "@";

                        xlWorkSheet.Cells[s + 1, j + 1] = data;
                        var _totalProgress = ds.Tables[0].Rows.Count-1;

                        backgroundWorker1.ReportProgress(i * 100 / _totalProgress);
                    }
                    Thread.Sleep(100);  
            }                      
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                xlWorkBook.SaveAs(savePath);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);              
                MessageBox.Show("PLANNING_EXCEL_Template.xlsx  file has been created!");
            }
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            if (!backgroundWorker1.CancellationPending)
            {
                lblStatus.Text = e.ProgressPercentage + "%";
                progressBar1.Value = e.ProgressPercentage;
            }
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {           
            lblStatus.Text = "Task completed!";
        }

And this is the exception:

An exception of type 'System.Threading.ThreadStateException' occurred in System.Windows.Forms.dll but was not handled in user code

Additional information: Current thread must be set to single thread apartment (STA) mode before OLE calls can be made. Ensure that your Main function has STAThreadAttribute marked on it. This exception is only raised if a debugger is attached to the process.

Peter Duniho Peter Duniho · Accepted Answer · 2017-05-24T06:35:05

Excel automation requires that you create and use your Microsoft.Office.Interop.Excel.Application and related objects in a particular way:

They must be used in the thread where they are originally created, and
That thread must be an single-threaded apartment (STA) thread.

Put another way, you need to treat the Excel objects the same way you treat your UI objects. Make sure they are created in the main UI thread (which is already STA), and use Control.Invoke() or similar to make sure any code that accesses those objects is executed in that main UI thread.

You can still use the BackgroundWorker, or whatever worker thread paradigm you like, to handle the SQL queries. But you'll need to make sure that the results are passed off to a delegate invoked on the main UI thread, where they can then be copied into the Excel data objects.

Exception of Backgroundworker Progress

2 Answers