Scalability of Frames

Add-in Express™ Support Service
That's what is more important than anything else

Scalability of Frames
Office 2010 to Office 365 Frames Compatibility 
Michael Kaden




Posts: 322
Joined: 2017-11-15
Hello Andrei, Update....

Thank you for your good advise and continuous support.

I did some work on WPF/WinForm AutoSize, AutoSizeMode, Dock etc. and I now feel quite comfortable with using WPF User Control and WinForm ElementHost. So no need to add WPF Window as New Item. Thank you.

When I put a progress bar on the WPF window (as ElementHost in WinForm) and click the button on this Window:

Private Sub BU01_Click(sender As Object, e As System.Windows.RoutedEventArgs) Handles BU01.Click
For K = 1 To 100
PB01.Value = K
System.Threading.Thread.Sleep(1000)
Next
End Sub


The ProgressBar does not change, only when the Sub execution is completed after 100 seconds, it jumps to 100 %

How do I make the progress bar increment? Find many forums on this all suggesting to move the task connected to the ProgressBar to the BackgroundWorker and change the Bar with the BackgroundWorker.ProgressChanged Event. See below, that does not seem to be possible when my work is related to Excel.

My other still pending issue is that I have to transfer the cell values of very large Excel files to Global Variables or Text files for further processing and because I do not want the user to have to wait, I want to do that (extracting Excel cell values) in another thread.

I looked at Threading (BackgroundWorker) over the weekend. I also read your forum about Threading and understand that opening another Excel instance in another but the UI (main) thread is not a good idea. I tried it and agree with your comments.

I tried to use OpenXML and although it worked sometimes, but gave trouble often including being extremely slow. Also the format of the cell value is sometimes not right. Loading the required XML References also gave some build errors with the ADX MSI SetUp Project? So that option seems to be no good.

Is there any way to extract cell values (and properties) of an Excel workbook, asynchronous in a Backgroundworker Thread?

Thank you very much & kind regards

Michael
Posted 11 Apr, 2021 11:57:33 Top
Michael Kaden




Posts: 322
Joined: 2017-11-15
Hello Andrei, Update no 2

I got the idea that I can use the BackGroungWorker to split the tasks and move the ProgressBar even if I have to do all the Excel work in the UI thread.

In my Button I have


 BackgroundWorker1.RunWorkerAsync()


Then I have

Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork

    Me.BackgroundWorker1.ReportProgress(0, "Task1")
 Me.BackgroundWorker1.ReportProgress(0, "Task2")
 Me.BackgroundWorker1.ReportProgress(0, "Task3")
 Me.BackgroundWorker1.ReportProgress(0, "Task4")
 Me.BackgroundWorker1.ReportProgress(0, "Task5")
 Me.BackgroundWorker1.ReportProgress(0, "Task6")

End Sub



Then in the U IThread I have

Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs)
Select Case e
Case "Task1"
'Do the Excel work package No1
Case "Task2"
ProgressBar.Value = 30
Case "Task3"
'Do the Excel work package No2
Case "Task4"
ProgressBar.Value = 60
Case "Task5"
'Do the Excel work package No3
Case "Task6"
ProgressBar.Value = 100
End select
End Sub


Do you think this will work?

I think that I will have to open and close Excel in each work package which will take up additional time, just for the sake of having a moving progress bar. I will check out the timing implications and report back.

The above reminds me also of the ADX sendmessage/On_Sendmessage event.

Still the question remains, is there any way to extract Excel cell values in another thread?

kind regards

Michael
Posted 12 Apr, 2021 01:14:26 Top
Andrei Smolin


Add-in Express team


Posts: 18007
Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
The above reminds me also of the ADX sendmessage/On_Sendmessage event.


I suppose the way the class above gets informed about the change is actually the same as SendEMssage|OnSendMessage uses.

Michael Kaden writes:
I think that I will have to open and close Excel in each work package which will take up additional time, just for the sake of having a moving progress bar. I will check out the timing implications and report back.


You should run your Excel-related code on the main thread. This is a really strict requirement. You shouldn't expect to be able to process Excel cells on a background thread.

To reflect the progress in the UI, the controls should be created on another thread. That means, in your code processing the cells, in case of System.Windows.Forms, you would need to use MyControl.BeginInvoke(); see https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.control.begininvoke?view=netframework-4.8. Don't know whether anything at all is required in case of WPF. I try to find this out.

On WPF Window. The point is: every Window class instance requires an Application class of this or that sort. Your findings confirm that {Excel, Word, Outlook, etc.}.Application isn't the Application class that WPF Window wants. Similarly, with Windows Forms Controls: the System.Windows.Forms.Form needs an Application as well. Even though the {Excel, Word, Outlook, etc.}.Application has most of things required, it isn't the Application WPF and WinForms need. We see this quite often with third-party controls: some of them only work correctly in an application, not in a class library.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Apr, 2021 07:04:55 Top
Michael Kaden




Posts: 322
Joined: 2017-11-15
Dear Andrei,

as in my example above, I ru all the Excel related code on the main thread with the e

Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) 


and the first trial were successful- I make a simple example to highlight when the problems come in:


Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork 
 
 Me.BackgroundWorker1.ReportProgress(0, "Task1") 
System.Threading.Thread.Sleep(Time to sleep the BGW)

 Me.BackgroundWorker1.ReportProgress(0, "Task2") 
System.Threading.Thread.Sleep(Time to sleep the BGW)
 Me.BackgroundWorker1.ReportProgress(0, "Task3") 
System.Threading.Thread.Sleep(Time to sleep the BGW)

 Me.BackgroundWorker1.ReportProgress(0, "Task4") 
System.Threading.Thread.Sleep(Time to sleep the BGW)

 Me.BackgroundWorker1.ReportProgress(0, "Task5") 
System.Threading.Thread.Sleep(Time to sleep the BGW
)
 Me.BackgroundWorker1.ReportProgress(0, "Task6") 
 
End Sub


In the BackgroundWorker1_ProgressChangedEvent, which is in the main thread I now made the working tasks also longer with a sleep

Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) 
Select Case e 

Case "Task1" 
'Do a lengthy task
System.Threading.Thread.Sleep(Time to sleep the ProgressChanged Event)

Case "Task2" 
Print to Console "Progress Value 30%"
ProgressBar.Value = 30
 
Case "Task3" 
'Do a lengthy task
System.Threading.Thread.Sleep(Time to sleep the ProgressChanged Event)

Case "Task4" 
Print to Console "Progress Value 60%"
ProgressBar.Value = 60 

Case "Task5" 
'Do a lengthy task
System.Threading.Thread.Sleep(Time to sleep the ProgressChanged Event)

Case "Task6" 
Print to Console "Progress Value 80%"
ProgressBar.Value = 80 

End select 
End Sub 



The real interesting thing is, that it works perfect as long as the BackGroundWorker sleep is longer than the Length task Sleeper.

In every case, even if the BackGroundWorker is only 1 ms and the task is 5000 ms the print is in the correct order:

"Progress Value 30%"
"Progress Value 60%"
"Progress Value 80%"

and the timing reflects the timing used for the tasks, But the Process Bar is not updated but jumps to 80 % at the end of all the ProgressChangedEvent called.

Your help is highly appreciated.

kind regards

Michaeel
Posted 12 Apr, 2021 07:53:47 Top
Andrei Smolin


Add-in Express team


Posts: 18007
Joined: 2006-05-11
Hello Michael,

Your implementation has a flaw: imagine an unexpected dialog window Excel shows in the middle of the process: your BackgroundWorker won't know about this.

To wait for some time, you can start a thread executing Thread.Sleep(someTime).

The UI doesn't redraw since it has no free time to do this: the thread is either doing something (such as printing whatever) or it sleeps.

That's why I talked about showing the UI on another thread. Is your ProgressBar a WPF or WinForms control?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Apr, 2021 08:11:35 Top
Michael Kaden




Posts: 322
Joined: 2017-11-15
Dear Andrei,

thank you for your prompt reply.

The ProgressBar is in a WPF_User_Control which is in an ElementHost of a WinForm

Do I understand it correctly, that the UI will only redraw when ALL procedures (processes) which are (queuing) in the UI thread are finished. That would explain that there are no "Gaps" for redraw available if the task in the first BackgroundWorker1.ReportProgress Event is not finished or still sleeping, when the next BackgroundWorker1.ReportProgress Events are fired.

But why does it write the Console correctly?


Is there a solution to this?

I started to search for information on your comment "showing the UI on another thread" and "BeginInvoke". Up to now, I was under the impression that UI components can only be manipulated or shown within the (main) UI Thread.

Thank you & kind regards

Michael

Michael
Posted 12 Apr, 2021 10:21:16 Top
Andrei Smolin


Add-in Express team


Posts: 18007
Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
But why does it write the Console correctly?


You need more resources for drawing than for typing a string on the Console. Moreover, since drawing is a resource-consuming operation, Windows combines several drawing requests into a single operation. Kind of optimization. This implies postponing some or all of the drawing requests and performing them when Windows decides.

You might see many VB code samples using DoEvents when the developer needs to redraw the form. At all cost avoid doing this as DoEvents opens a door to code reentrancy: imagine your event handlers entered again before they finish. In COM programming this would be a nightmare.

Michael Kaden writes:
Up to now, I was under the impression that UI components can only be manipulated or shown within the (main) UI Thread.


You might find quite many statements on the web supporting that opinion. But, you CAN create UI objects on another thread. Below is some code; sorry I don't have a VB version at the moment; still it doesn't seem to have any *real* C# specificity:

[DllImport("user32.dll")]
private static extern IntPtr GetForegroundWindow();

static ProgressForm ProgressForm = null;
static Thread thread = null;
private void adxRibbonButton3_OnClick(object sender, IRibbonControl control, bool pressed)
{
    if (thread != null)
    {
        timerUpdateProgressForm.Enabled = false;
        if (ProgressForm != null) ProgressForm.Close();
        thread.Join();
        thread = null;
    }
    if (thread == null)
    {
        timerUpdateProgressForm.Enabled = true;
        thread = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(OnThreadStart));
        thread.SetApartmentState(ApartmentState.STA);
        thread.Start(new Win32WindowAdapter(GetForegroundWindow()));
    }
}

private static void OnThreadStart(object data)
{
    if (thread == null) return;
    ProgressForm = new ProgressForm();
    ProgressForm.ShowDialog(data as Win32WindowAdapter);
    ProgressForm = null;
}


[ComVisible(false)]
public class Win32WindowAdapter : System.Windows.Forms.IWin32Window
{
    public Win32WindowAdapter(IntPtr windowHandle)
    {
        Handle = windowHandle;
    }

    public IntPtr Handle
    {
        get;
        private set;
    }
}


My form is a System.Windows.Forms.Form, so I call a method on it in this way:

private delegate void SafeCallDelegate();
private void timerUpdateProgressForm_Tick(object sender, EventArgs e)
{
    if (ProgressForm != null)
    {
        ProgressForm.Invoke(new SafeCallDelegate(ProgressForm.SetProgressValue));
    }
    else
    {
        timerUpdateProgressForm.Enabled = false;
    }
}


The SetProgressValue() above accesses the WPF UserControl on the form:

public void SetProgressValue()
{
    UserControl2 ctrl = this.elementHost1.Child as UserControl2;    
    ctrl.SetProgressValue();
}


Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 13 Apr, 2021 06:26:22 Top
Michael Kaden




Posts: 322
Joined: 2017-11-15
Hello Andrei,

I also include the reply to

https://www.add-in-express.com/forum/read.php?FID=5&TID=16125&MID=83589#message83589

in his topic as the subjects are interlinked.

1.) My experience up to now is that manipulating Excel, for example read cell values, in other than the main UI thread takes between 3 to 5 times as long as in the main UI thread, independent if I use the existing Excel instance or open a new instance (which gives difficulties to close the background Excel process) or if I use OpenXML to get the data. So it does not really make sense to split large Excel tasks and execute them in separate threads. I also think there is a possibility to get thread hunting or collisions with several threads reading from 1 Excel file at the same time. So I will stay with the option to do all tasks related directly to Excel in the main UI thread, and only move other tasks such as working with global variables, databases or text files to other threads. I have still to find out that in case I have to, for example lock a file to make the access safe, will still give increased overall processing speed if I work with multiple threads?

Would you agree to that?

2.) Changing to WPF windows, as elementhost WPFUserControl in WinForm solved the problem of displaying the windows on monitors with different DPI and with the development to ever higher resolution monitors I think that is the way to go?

However I experience a time consuming learning curve with migrating from WinForm to WPF. Syntax for the same things are different and the WPF toolbox has less features than WinForm. For example:

I really battled with the following scenario:

a) If I have a progress bar and a button on one Form and the Button_click starts a loop which increases the progress bar value, with Sleep(2000) intervals. This works very well in a WinForm but in the WPF UserControl as elementHost in a WinForm, the Progress bar jumps from 0 to 100 % after 20 seconds, i.e only after the button_click event has completed.

I use sequencing Backroundworkers, ie. the RunWorkerCompleted event increments the Progress bar Value and starts a new Backgroundworker and so on. Although I find many post in the internet recommending this solution, I think this is not an elegant solution, also the progress bar value change is not always happening as intended. This is however the solution I found most in the internet. The Backgroundworker really only acts as a timer sequencer.

Should I instead use a timer?
Should I create custom events which follow each other instead, i.e.the event trigger itself again until the task in increments is completed?
Do you have a better solution?

b) I wanted a text box, which is limited to numbers only and that the numbers shown in the text box are #.##0 formatted. In WinForm this was easy. I tried StringFormat for the WBS textbox, but this is very complicated, I did not manage to get the result I wanted. In the end I used the KeyUp_Event to get what I wanted.
How would you overcome this?

Is there a better way than google for hours to find ways to get the WinForm capabilities into WBS components?

3.) I also tried Extended.Wpf.Toolkit, but fear that this, as with all Extensions offered on NuGet, including OpenXML:

It is not guaranteed that the compatibility with Windows will always remain.

The licensing and possible cost involved are not clear.

Are you using NuGet packages in your software development?

I am aware that these topics are beyond straight forward ADX issues, but imagine that many of your users are confronted with these problems. So your experienced advice is very much appreciated.

thank you & kind regards

Michael
Posted 21 Apr, 2021 04:22:45 Top
Andrei Smolin


Add-in Express team


Posts: 18007
Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
My experience up to now is that manipulating Excel, for example read cell values, in other than the main UI thread


It isn't recommended to use any Office object model outside of the main thread. This may be the cause of the delays. Most importantly, however, this may cause an exception that you won't be able to solve. Imagine a thread calling the object model while it is busy with something. In this case the exception meaning "We are busy. Try to call us later" is raised. Microsoft provides a way for you to deal with this exception. Nevertheless, that way is for low-level COM programming. Trying to use it in your code makes your code significantly more complex. With no guarantee of success.

For this reason, we recommend that you use all Office object models solely on the main thread. Basically, this exception stands behind our declaring the object models thread-unsafe. For background details, check what Geoff Darst says at https://social.msdn.microsoft.com/Forums/en-US/a4775ced-fa6d-44bf-b039-5bc72188e823/is-applicationclass-thread-safe?forum=vsto.

Michael Kaden writes:
So it does not really make sense to split large Excel tasks and execute them in separate threads


It may be useful to split the task in several batches on the main thread and execute one batch in a go making a small delay before running the next batche. This lets the user do something while your code pauses. Since we talk about the main thread, pausing your code doesn't mean pausing the thread!

Michael Kaden writes:
So I will stay with the option to do all tasks related directly to Excel in the main UI thread, and only move other tasks such as working with global variables, databases or text files to other threads.


Using another thread to run code that doesn't rely on an Office object model is absolutely okay. So, several threads may help you if you can organize non-objecft-model calculations in threads.

Michael Kaden writes:
2.) Changing to WPF windows, as elementhost WPFUserControl in WinForm solved the problem of displaying the windows on monitors with different DPI and with the development to ever higher resolution monitors I think that is the way to go?


Yes, it is. I'm preparing a couple of blogs demonstrating and explaining UI problems in a multi-DPI environment. WPF is the way out of these problems.

Michael Kaden writes:
a) If I have a progress bar and a button on one Form and the Button_click starts a loop which increases the progress bar value, with Sleep(2000) intervals. This works very well in a WinForm but in the WPF UserControl as elementHost in a WinForm, the Progress bar jumps from 0 to 100 % after 20 seconds, i.e only after the button_click event has completed.


Send me some code to the support email address. No need for the code to be compilable. I'll need to see the form design, resources and code as well as the code that changes the progress bar value. So that I could create an example here and test it.

Michael Kaden writes:
Are you using NuGet packages in your software development?


Yes. Nuget packages is a way for you to use a certain version of a certain product and get updates. Nuget packages may not have any relation to Office or Office programming.

Michael Kaden writes:
The licensing and possible cost involved are not clear.


A license is associated with every Nuget package so you should be able to find it. As to the cost, I believe you would have known about it by that moment. I've only used free solutions so far.

I've never used Open-XML solutions and I've never heard about Extended.Wpf.Toolkit.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 21 Apr, 2021 07:39:23 Top
Michael Kaden




Posts: 322
Joined: 2017-11-15
Dear Andrei,

thank you very much. Just send you the simple sample code to

support@add-in-express.com


I think OpenXML is very good as you can, for example read and write cell values without the use of the Excel Object and no Excel process is opened and therefore it can run on another thread. One has to be careful to ensure that the file is locked when writing to it with OpenXML It works well but as said, it takes about 3 to 5 times the time to get the data. Therefore at my present program it does not make sense, but perhaps in the future when the program does not have to wait for the task to be completed.

I read many hours in the Internet about NuGet and as spelled out in my email, for me, uncertainty with regard to licensing and compatibility with all future Windows Versions remain for all NuGet packages.

Thank you very much & kind regards

Michael
Posted 22 Apr, 2021 04:31:26 Top