The LibXL engine does not calculate the results of a formula. [4.8.108 in the documentation]
It does however calculate the formula once you open the XL sheet on your local machine. Unfortunately, our use case requires us to read the formula programmatically while it is on a Web server. Before we abandon LibXL And the monkey bread XL plug-in in this case:
Is there any clever work around where we can force the XL sheet to update and then read the results off the sheet?
You could do the calculations yourself…
How can I do that?
Read numbers and do calculation yourself.
I am sorry to not have a calculation engine in there.
But maybe you can use XojoScript to do the calculations if you translate the cell references.
It looks like we’re going to have to use the Microsoft graph APIs and talk with OneDrive. I wonder if anyone else out there has a more elegant solution?
Hello Jay,
Something else to investigate.
Are you aware of the Excel Javascript API?
You can access workbooks, perform operations, control calculation and file ops with the Excel Javascript API.
More specifically here, if you scroll down a little to the section titled Control Calculation Behavior. There is a calculation mode and type.
Not sure, where is right for you, but you may call JS from within Xojo using ExecuteJavaScript, implement client-side (or perhaps implement server side if you use a server-side or mixed client side-server side Javascript framework).
Again unsure of your goal but suspect you could probably continue with MBS LibXL and just enhance with a little Javascript.
Kind regards, Andrew
Is that for one document you make?
@Christian Schmitz [quote]Is that for one document you make?[/quote]
I’m not 100% sure of the limits of the API, having only used it sparingly (having mostly used VBA out of familiarity in the past). However, I’ve seen snippets of code like window.open('ms-excel:ofv|u|' + filename); which suggest you can operate on (I guess) any excel file you have permission to access, not just those you author. I’ve only used it on models I have authored. I might experiment when I have time.
A couple of other resources that might shed some light:
excel-add-ins-workbooks; and
excel workbook
Kind regards, Andrew
Wow. Thanks Andrew! We are also looking at the poorly named but looks promising Microsoft Graph APIs.
Jay, I may not understand your problem. You seem to be saying that you want to read an Excel file with Xojo. And some cells contain formulas. And you want to not see the formula (such as “=A1+A2”) but the calculated result (e.g. “5”)?
I’ve done that with the LibXL MBS plugin.
Here’s how to read the formula or the calculated content (which has been previously calculated by Excel before saving the Excel file):
if sheet.IsFormula(y,x) then
v = Sheet.ReadFormula(y,x)
else
v = sheet.ReadString(y,x)
end if
Isn’t that what you want? If not, please explain the issue better. What do you mean by “when it’s on a web server”? What difference does it make whether the excel file is on a server or one a local disk? You still open the file in your Xojo code, so it should make no difference.
After giving this some thought I think I understand now:
Suppose you have an Excel file with formulas in it whose results appear in other cells. Now your program modifies some values in it. If you’d do this in Excel, you’d immediately see the new results in the respective cells. Not so if you modify with LibXL - the cells with the formula keep the previous values. I guess that’s what Jay wants to solve.
Honestly, I believe only Excel and related programs from Microsoft can help here, because any attemp at trying to re-calculate all possible formulas (especially if you have no control over what formulas are used) is near-impossble. And then there are special behaviors about rounding and whatnot that only Excel “gets right”, and you’d have a hard time to know them all and re-implement them correctly.
With that, the only way to solve this is to use Microsoft’s apps or services:
If the code runs on a Windows or macOS server, then the easiest solution mignt be to just open the file in Excel there, then have Excel save it. Now it’ll contain the updated values.
Or use the obscure Javascript APIs (probably only works on Windows servers?), which may perform better.
You could script LibreOffice via shell class to load the modified file and save it again.
Thanks guys, it sure seems like we have to jump through an awful lot of hoops. There are to be a way to access the native Microsoft tools without tearing it into Google or libre office or something like that. But then again it is Microsoft…
We frequently get very very large complicated spreadsheets from clients. We currently tear them down and make all of the charts and V look up’s into a sequel lite database. Then we write a very large class to represent the calculation and read the sequel a database. Usually the formula involved has 20 and puts in two or three outputs.
15 days later they change some of the charts and we have to go in and re import all the sequel light. This could be a horrible pain in the butt as sometimes there’s as many as 65 tables.
Because they always give it to us in XL. We simply want to read the formulas on the first worksheet of the workbook natively. Am I making any sense?
Makes sense
How do you do this import to SQLite ?
And generate the class ? Which of course means a recompile of your code 
Almost seems that if you could factor the “spreadsheet” into a Xojo Script or something at least if that changed you dont have to recompile all your code
There may be other options to avoid having to recompile all the code but I’d need to know a lot more before I could make any suggestions
Lock the format and tell your clients that every change costs 1000$.