none
VSTO - Excel 2010, Background Worker thread - Progressbar and progress Status. RRS feed

  • Question

  • Hi,

    I'm using VS-2015 and developing MS Excel 2010- Addin.

    Goal - I intend to show a progress bar while the data download is in progress.

    Exception Details:

    1)

    "Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.Tools.Office.Runtime.Interop.IHostItemFactoryNoMAF'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{A0885C0A-33F2-4890-8F29-25C8DE7808F1}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))."

    After googling I understand that VSTO related events should be on main thread while I'm pushing it on background thread which is not acceptable. So what's the alternative ?

    If I call the function on main thread i.e. after show dialog then it works fine but that would mean if the requested record set is huge then there will be moments where entire excel goes blank and user will think something went wrong or nothing happened.

    frmProgressBar.ShowDialog();

    Followed by below code

    PopulateExcelWorksheet(object entityNotes)

    2) My lblProgressStauts never updates the status like 'downloading 5000 records...' 

    My Code:

            ProgressBar pb;
            Form frmProgressBar;
            System.Windows.Forms.Label lblProgressStatus;
    
            private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                if (frmProgressBar == null)
                {
                    frmProgressBar = new Form();
                    frmProgressBar.ControlBox = false;
                    frmProgressBar.BackColor = Color.White;
                    frmProgressBar.Opacity = .9;
                    frmProgressBar.Size = new Size(365, 80);
                    frmProgressBar.FormBorderStyle = FormBorderStyle.None;
                    frmProgressBar.StartPosition = FormStartPosition.CenterScreen;
    
                    pb = new ProgressBar();
                    pb.Height = 30;
                    pb.Width = 300;
                    pb.Location = new System.Drawing.Point(25, 8);
    
                    lblProgressStatus = new System.Windows.Forms.Label();
                    lblProgressStatus.Location = new System.Drawing.Point(28, 45);
                    lblProgressStatus.Height = 10;
                    lblProgressStatus.Text = "Download in progress ....";
                    frmProgressBar.Load += FrmProgressBar_Load;
                    frmProgressBar.Controls.Add(pb);
                }
    
                // Code to get the count of records to be displayed.
    
                this.timer1.Start();
                System.Threading.SynchronizationContext.SetSynchronizationContext(new WindowsFormsSynchronizationContext());
                frmProgressBar.ShowDialog();
    
            }
    
            private void FrmProgressBar_Load(object sender, EventArgs e)
            {
                showProgressbar();
            }
    
            private void showProgressbar()
            {
                if (!backgroundWorker1.IsBusy)
                {
                    if (System.Windows.Forms.Application.RenderWithVisualStyles)
                    {
                        pb.Style = ProgressBarStyle.Marquee;
                        pb.MarqueeAnimationSpeed = 100;
                    }
                    else
                    {
                        pb.Style = ProgressBarStyle.Continuous;
                        pb.Maximum = 100;
                        pb.Value = 15;
                        pb.Minimum = 10;
                    }
    
                    timer1.Enabled = true;
                    backgroundWorker1.RunWorkerAsync();
                }
            }
    
            private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
            {
                // doing the heavy lift from database getting 100k records.
                EntityNotes = EFModel.EntityNotes.SqlQuery("usp_GetDetails, @DateFrom, @DateTo ",
                                                    DateFrom, DateTo).AsNoTracking()
                                                    .ToList();
                //Exception code
                PopulateExcelWorksheet(EntityNotes);
            }
    
            private void PopulateExcelWorksheet(object entityNotes)
            {
                
                System.Windows.Forms.BindingSource  BSource = new System.Windows.Forms.BindingSource();
                
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1];
    //Exception IS RAISED HERE
                Microsoft.Office.Tools.Excel.Worksheet extendedWorkSheet = Globals.Factory.GetVstoObject(worksheet);
    
                //WorkbookCleanUp
                if (extendedWorkSheet.Controls.IndexOf("ListObject_dummy") > -1)
                {
                    extendedWorkSheet.Controls.Remove("ListObject_dummy");
                }
    
                Microsoft.Office.Interop.Excel.Range cell = extendedWorkSheet.Range["$A$1:$AT$1"];
    
                this.ListObject_Dummy = extendedWorkSheet.Controls.AddListObject(cell, "ListObject_dummy");
    
                // adding events
                ListObject_Dummy.Change += new Microsoft.Office.Tools.Excel.ListObjectChangeHandler(EventHandler_Method);
    
                //Cell Validation Code
                CellValidationList(extendedWorkSheet);
    
                this.ListObject_Dummy.SetDataBinding(EntityNotes);
                
            }
    
            private void EventHandler_Method(Range targetRange, ListRanges changedRanges)
            {
                //
            }
    
            private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
            {
                pb.Value = e.ProgressPercentage;
            }
    
            private void backgroundWorker1_WorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
            {
                frmProgressBar.Close();
            }
        }


    • Edited by StSingh Wednesday, April 19, 2017 10:52 AM Code update
    Wednesday, April 19, 2017 10:50 AM

Answers

  • Hi StSingh,

    you had mentioned that," I understand that VSTO related events should be on main thread while I'm pushing it on background thread which is not acceptable."

    do you mean your form is going on background which is not supported?

    did you try to create Task pane using User Control which can support all windows forms controls.

    you can use all the controls that you want.

    it can be display on left or right side of the screen and you can make it visible / hide from button in ribbon.

    then you not need to use windows form and it can solve your issue.

    because you can put all your code on button that placed on a Task pane.

    on the same task pane you can put the progress bar and other control and your code can be run together.

    so you can easily display the progress bar and status with in task pane and same time query get executed.

    let me know if this approach you can integrate in your Addin.

    then you had mentioned that ,"If I call the function on main thread i.e. after show dialog then it works fine but that would mean if the requested record set is huge then there will be moments where entire excel goes blank and user will think something went wrong or nothing happened."

    what I did not understand is if you are showing the progressbar and if data are huge then why user think that nothing is happening because progressbar is running.

    I also did not understand that why the sheet will be empty ?

    second thing you had mentioned that,"My lblProgressStauts never updates the status like 'downloading 5000 records...' "

    did you wrote any code for that? how you calculate that this much data are imported?

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, April 20, 2017 7:39 AM
    Moderator