Does anybody know if Xojo can do anything at all with Mac Excel out of the box? I know I can import csv and use MBS plugins, and I could theoretically use SimCar software’s Excel solution if there were any docs (which there are not, evidently). I tried following several videos online but none of it runs on the Mac. I know the difference in automation features between Office on the Mac and PC, but I can run Macros and use a lot of libraries for Office on the Mac (latest Office 365 for Mac) so I’m wondering why I can’t do anything with Xojo.
Yes, Christian’s MBS stuff is wonderful but I need the libXL library to use it for Excel and I don’t have an actual programming job to justify this expense at the moment, just learning.
That is indeed the question. It has VBA, and even OLE now (between Excel and Word). So one tentatively assumes it can do some of what it does on the PC. But I know what they say about “assume”.
I’ve looked at this site briefly some weeks ago, but I found the docs for Excel somewhat more difficult to understand than the similar solution from MBS (not a comment on the developer, but just owing to my lack of experience with Xojo). Upon a thorough read, however, it seems that, unlike MBS, the entire Excel solution, including support libraries, are available for one purchase price. It seems that all of the plugins on the site are included in that price, which seems a great value (assuming I can use them).
Is my understanding correct? I would certainly go for this if the whole package were included (as my reading suggests).
I would still like to know if Xojo alone supports Excel on the Mac in any capacity, but it’s looking like the answer is “no”.
Yes; I have both MBS Complete plus libXL for macOS (among other MBS stuff) and Einhugur too.
I tend to use Einhugur when I am wanting cross platform and MBS/libXL when I know I am making something I only need for macOS. I did have a recent project where I used Einhugur and at first I thought something was wrong as the program seemed to lock up when opening a specific xlsx file. But it turns out that it was just taking nearly a full minute to open the file (it had over 180k rows and a couple dozen columns). For most stuff I have done, the Einhugur opens quickly.
As far as “Xojo alone … in any capacity”, xlsx files are just compressed (zip compatible) XML. So you could decompress and deconstruct the XML yourself. But for me, I prefer using MBS or Einhugur and getting a nicer class interface. I’m of the opinion that both MBS and Einhugur are worth having in my toolbox, apart from any Excel integration.
Note that Einhugur has both individual and site licenses available, should you need that instead.
I second this opinion. I’ve paid for a license every year for over 15 years and could not do many things without it. I also have MBS and couldn’t do without it, but I like not needing LibXL for the Einhugur plugins.
Another “free” cross-platform approach to the problems of reading, authoring, editing, recalculating and saving Excel files on Windows, Linux and Mac is to call PowerShell scripts as Shell Commands from within Xojo.
PSWriteOffice is a cross platform PowerShell library for editing office files from a PowerShell script.
PowerShell has been cross platform for years now.
One thing I like about this approach is that when you are doing extensive work in Excel you are debugging the PowerShell and hence Excel functionality separately of your Xojo functionality.
If I understood correctly this is the sort of thing you might want to do.
Example PowerShell script, edits cell D4, recalculates, then displays the workbook.
Clear-Host
Import-Module -Name 'C:\Program Files\WindowsPowerShell\Modules\PSWriteOffice' # import the PSWriteOffice module
$Excel = Get-OfficeExcel -FilePath "c:\your_file_path\your_excel_file.xlsx" # set the path to your excel file
$WorkSheet = $Excel.Worksheets | Where-Object { $_.Name -eq 'Sheet1' } # select Sheet1
$WorkSheet.Cell("D4").Value = 12345 # in this example we wish to edit and recalculate an existing file, so before you start create an excel workbook that has some values from D1 to D4, and a formula in D5 to sum range D1 to D4
$WorkSheet.RecalculateAllFormulas() # the call to recalculate the workbook
Save-OfficeExcel -Excel $Excel -Show # save with the optional show workbook command
You then call the PowerShell script command from within Xojo as a Shell Command.
You can have the PowerShell script return values to populate controls in Xojo (such as a ListBox or WebListBox) etc. I use this approach to reconcile large volumes of financial transactional data etc.
That said, the plugins mentioned earlier are all great tried-and-tested long term solutions, whereas some PowerShell libraries like everything are constantly evolving.
This is a tremendous idea, just on G.P… Though I spend some time on Windows, I’m not a powershell user, because I find the standard command line easier and PS messes me up badly when using the terminal in VS Code. However, this is a game-changer, or would be if I could find out where the PSWriteOffice module got installed on my Mac M1.
I know PS is installed in my /usr/local/microsoft/powershell/7 directory (as well as an app in the Applications directory, for whatever reason), but I don’t see PSWriteOffice in that directory or the subsidiary Modules directory.
I installed PSWriteOffice via PS:
Install-Module -Name PSWriteOffice -RequiredVersion 0.0.2
It seemed to install, no error, but no confirm either. It is definitely not as straightforward, hierarchy-wise, as on the PC.
It’s my understanding that PowerShell accesses Excel and Word through COM, so that might be a dealbreaker right there on the Mac (or Linux). I don’t know if you have any ideas about this.
Anyway, I can’t try the import because I don’t know where the PSWriteOffice module is, but if I find it, I’ll let you know how it goes.
Just interfacing Xojo with Powershell in general is a great idea, since I won’t have to change paths and terminal shells between operating systems if I just include Powershell. It is worth considering, however, that just because PS is cross-platform, it might not have the same skill set on every platform. I didn’t specifically see anything that advertised PSWriteOffice as cross-platform, though I assume you know more about it than I do.
Although that requires Excel to be installed on the device, which may or may not be the case. And does not make it “free” if not. However in a known environment, that may be a viable option too. When just reading the contents, or writing out a spreadsheet instead of report or PDF, the plugin approach has its virtues too.
As always, the more tools in the proverbial toolbox the less everything looks like a nail.
@Douglas_Handy@friedrich_boettger, the solution does not require Excel to be installed on the device. I even use the solution on a Linux webserver to process Excel files uploaded by users.
It’s my understanding that PowerShell accesses Excel and Word through COM, so that might be a dealbreaker right there on the Mac (or Linux). I don’t know if you have any ideas about this.
In normal circumstances that is correct, however there are emerging cross-platform libraries such as PSWriteOffice that break that dependency.
I also do some development in LiveCode that has a paid third party Excel library free of the dependency on an installed version of Excel, Word or Office, which is extremely fast.
If I can manage some free time, I’ll try and post a small tutorial.
If you have a question for MBS Plugin, please let me know.
We had our self written classes 10 years ago. But then people asked for more and more features, so I switched to use libXL. While it is an extra purchase on our website, you can do a lot of things there. For our plugin license, you can get it with OmegaBundle of a few more days.
Björn also has a plugin and so you have two choices. In general we appreciate if you guys just buy both plugin packs, MBS and Einhugur.
I’ve thrown together an app or two in LiveCode also. It’s a great IDE and a fun language, but when they went subscription I stopped serious development. Then when they bifurcated their product into 12 different tiers, I thought they must have hired some marketing guys from Adobe. This was too rich for my blood.
I am anxious to try your solution, but as I say I can’t even get it installed (though PS on the Mac seems to work just fine). Regardless, Christian Schmidt–always a source of good advice whether he’s pushing MBS or not-- did point out that I still have a couple days left for the Omega Bundle. It seems like hitting an ant with a sledgehammer at this point, but on the other hand I might just need that sledgehammer down the road.
I’m glad to see that LiveCode has retained some good developers. They are awfully nice people and the support is (or was) top notch.
All the rest of the people I’ve asked seem to be echoing your sentiments, so either you and Bjorn are giving them a commission or I should (eventually anyway) get both products. Your mention of the OmegaBundle is timely. I have a couple days left to spend every extra cent I have on something I don’t strictly need, but on the other hand I know I’ll have years of fun with it and besides I just bought myself an Ovation guitar a couple months ago and I didn’t have the extra money for that either. And I don’t regret it, even though I’ve only played out with it in bluegrass jams. I consider it an investment. Whether I’m kidding myself or not, only time will tell.
Just so everybody knows, the Omega bundle not only offers the MBS plugins, but the MBS database driver/connectors (for multiple databases, including a souped-up one for Sqlite) as well
The advantage of LibXL(MBS Plugins) is that we can create or read XL files without having Excel installed, and this works on macOS, Windows and Linux.
The drawback is that the LibXL library has to be licensed separately for each target operating system. And if we need to renew these licenses each year, this adds quite a bit up to all other annual license and abo cost.