Slow excel

[papo66]

Hi,
I use a form with OleBoundControl, which displays an Excel chart and table.
It works, but the problem is the total display time, about 15 seconds.

code:
wait "I take graph from excel ..." wind nowa
create cursor bla (bla g)
append blank
append general bla from (lcF)
*** It takes about 1 sec
thisform.oleboundcontrol1.ControlSource="bla.bla"
thisform.oleboundcontrol1.refresh()
*** It takes next 1 sec

but here the PC freezes, the wheel is spinning, (the video on the second monitor stops) and this takes up to 15 seconds, why ?

The xlsx file is small. Settings of excel doesn't take effect:
if val(olesheet.version) > 11
olesheet.calculation=-4135
olesheet.ScreenUpdating=.f.
olesheet.EnableEvents=.f.
olesheet.UserControl=.f.
olesheet.Interactive=.f.
olesheet.DisplayStatusBar=.f.

Older versions of excel works fine.
I can't find a solution anywhere.

 

[Paul Gibson]

I think you are going to struggle to get a comprehensive answer from here because it's going to be extremely difficult, from our end, to replicate your scenario since you are using an OleBoundControl. Your code shows you are appending the OLE object from a file (path) stored in the variable lcF but no one will know what lcF is pointing to, what that contains, so will not be able to run your scenario for themselves.

Your sample just shows a series of commands setting certain properties on the Excel application object where you say it runs slowly. For example you are setting the Calculation property to -4135, so we can see you are setting it as such:

xlCalculationManual

-4135

Calculation is done when the user requests it.

...do you know if the big pause is on that specific line or one of the others?

The very generic advice is, have you stepped through the code line by line and found the exact line where the delay is? Or another thing that can be extremely useful is if you switch coverage logging on (SET COVERAGE) just before the big delay and then switch it off just after. For example, do something like:

lcCoveragePath = && a path to a log/txt file that works for your folder structure
Set Coverage To (lcCoveragePath)

just after the APPEND GENERAL line and then at an appropriate point towards the end of the process just call SET COVERAGE TO, with no file path to switch coverage logging off. Then you can open the coverage log file and examine the execution time for each line.

In truth it might just tell you exactly what you already know but it'll give you concrete confirmation of which line or lines are the slowest and the lines might give you additional clues. We can only guess.

You could also try to perform your Excel automation code outside of the OleBoundControl, just as a test, a proof of concept. Take what you are doing are write it as pure Excel automation just in a PRG which instantiates Excel and runs through the commands, try it with code that ensures that Excel is visible throughout the process so you can also see the Excel window and see if it is displaying any prompts/alerts during the process. Again, something like this might give you additional clues as to what is happening at different stages and why a delay might happen in a newer version of Excel.

 

[Tore Bleken]

Have you considered FoxCharts? It's pure VFP and totally free. https://github.com/VFPX/FoxCharts
Here you can read about it: https://doughennig.com/papers/Pub/FoxCharts.pdf

 

[iswithzhz]

In what business scenarios would binding an entire Excel sheet using OleBoundControl be considered? Does this come at too high a cost?While Visual FoxPro (VFP), as a legacy language, offered relatively compatible Excel support within its technological era, binding an entire worksheet indeed incurs significant resource overhead. We invite you to try our developed component, which enables viewing and rendering of Excel documents without requiring Microsoft Office installation.

 

[Chriss Miller]

Follow up on Pauls advice: When the freezing occurs within the Excel process involved in this (and yes, it will be when you append an excel file into a general field and display that in an olebound control) then the coverage might not show a line of VFP code taking long, both lines before and after the freeze may be reported as short running, it's still worth giving that a try.

But I guess you alreaddy know this >10 second freeze occurs when you display the sheet, your attempt of setting some properties of the olesheet comes too late, doesn't it? And you can only make these setting after the sheet is appended, you can only work with the defaults in that case.

On the other hand, I second the recommendation of FoxCharts for that use case, get rid of the olebound control and the general field anyway, it's not recommended for over a decade.

 

[Paul Gibson]

100% agree. Coverage logging isn't a always catch-all, silver bullet solution, especially when interacting with external components. It can be an invaluable tool in many scenarios but it can very occasionally report that a line or function that you think it taking an extremely long time actually ran in no time but thankfully that's few and far between. I regularly find that coverage logging can provide good solid evidence in those kinds of scenarios where you are pulling your hair out because your original understanding of the logic does not appear to be the same as reality.

I don't coverage log much, when I do it usually means something extremely strange has happened to make me resort to it but when I do it nearly always provides the kind of information that helps, greatly.

I'd also echo Tore's suggestion. If this OLE Bound Control is entirely for the purpose of a chart and you can't easily solve your issue with Excel, I would definitely look for some other solution that gives you much greater control of your destiny and FoxCharts can be that solution. With anything that relies on Excel you are always at the mercy of the user's Excel installation, that means different versions and even different configurations can throw you off when you had no way of anticipating those combinations. Users can do all kinds of configurations of Excel that you may never know about unless it's a problem for you.

The other thing being that your user has to have Excel if you are relying on it and that might not be a good thing. Take a user who grants you remote access to their server for jobs like support and maintenance but they obviously don't install Excel on their server. What if you needed to check a feature, using your remote access but can't get past the reliance upon Excel. I'm not saying that Excel integration is a bad thing but if you can keep it to only those places that 100% need it then that's a good thing.

Something like FoxCharts puts the control back with you, as you are in control over what version of FoxCharts you integrate with and distribute with your application so if you get your chart working with FoxCharts and then never even change which version you use, then in theory it'll always work because you are keeping that/those components static. It's nice when you can rely on certain aspects to not change around you. In my server access example, FoxCharts would still work when running your application on a server that didn't have Excel but obviously Excel integration would not.

 

[Chriss Miller]

You might speed this up a little simply by starting an Excel.Application or at least an Excle.Sheet object with application start.

Later when you let an olebound control render an Excel chart (which the olebound control delegates to an Excel OLE server doing that) it would not necessarily use that instance of the Excel application or sheet or even just the process, but once Excel runs further Excel processes also start faster.

To be very clear about this, just because the general field only contains an xlsx file and an oleboundcontrol is a VFP native control doesn't mean VFP does all the work. An olebound control bound to an ole object or file in a general field will always need an ole server that'll take care of the rendering of the display of it. So oleboundcontrol always just like OLE automation means addressing a secondary process to do things. And I guess it's the startup of that, which takes long.