What Takes to Wrap MATLAB function to Use From Excel?

Question

dpb 2021-3-19

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/777887-what-takes-to-wrap-matlab-function-to-use-from-excel

编辑： dpb 2021-3-22

I've got a MATLAB function that pulls data from an Excel spreadsheet (actually two separate ones) and then writes after processing into a third.

It does not return any values to the spreadsheets containing the data it uses and writes it in a "veritable plethora" of places scattered all around in the target.

What I'd like is for the person who is working in the one spreadsheet containing the source of most of the data to be able to call the MATLAB function to update the other without needing to run MATLAB itself--they don't have the skillset nor the license so I need to be able to wrap the needed runtime.

7 个评论
显示 5更早的评论隐藏 5更早的评论

Mario Malic 2021-3-20

I apologise for tagging you here, but I think we could use your wisdom @Walter Roberson.

Walter Roberson 2021-3-20

MATLAB Compiler SDK is the appropriate toolbox for building something that can be called from Excel. Requires MATLAB Compiler license as well. I have never tried it (I do not have a Windows license for Excel)

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2021-3-20

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/777887-what-takes-to-wrap-matlab-function-to-use-from-excel#answer_653382

https://www.mathworks.com/products/spreadsheet-link.html

Spreadsheet Link™ connects Excel® spreadsheet software with the MATLAB® workspace, enabling you to access the MATLAB environment from an Excel spreadsheet. With Spreadsheet Link software, you can exchange data between MATLAB and Excel, taking advantage of the familiar Excel interface while accessing the computational speed and visualization capabilities of MATLAB.

If they don't have MATLAB then you can either write your program in VBA in Excel and launch executables created by the MATLAB compiler, OR you can have the compiled standalone MATLAB app control Excel though ActiveX.

13 个评论
显示 11更早的评论隐藏 11更早的评论

dpb 2021-3-20

I missed seeing the above reply IA, until now.

I grok -- in this case at the moment I don't need any UI although that might be the way to generalize -- at the moment it's just a black box that reads two workbooks they're working in and updates a third to save making the complex manual edits in it. The working input sheets are the same for a given semester billings so it's just working with the one fixed file that is being updated.

But, it will be needed to generalize somewhat later on, but if I could get this working right now it would solve the immediate crunch.

I found the log; it is/was, indeed, that it didn't find MingGW-w64 compiler; I guess the install I had done separately isn't where it thinks it should be or the particular environment variable isn't/wasn't set. It's pretty rude that the script/batch file doesn't abort with an error message when it doesn't find the compiler.

It's been a long time since have used it at all; it's probably quite out of date now, anyway, so I'll see if i can install fresh and make an executable.

Thanks for the pointers...I was, I think, probably headed down the wrong direction/in the wrong path. :)

dpb 2021-3-21

I thought that was true on the runtime, but wasn't positive, thanks.

I hadn't thought to run in console window -- the splash screen shows; unfortunately the output of disp() isn't echo'ed to the console, but catenating the cells of the array and cleaning up some does let one redirect output by fprintf(), indeed.

Unfortunately, the girls in the office aren't likely to want to use the console. But, the visual output is really only to let know that things are working; I was just echoing what is being put into the file to the screen so I could tell was working. When all that happens is that the splash screen shows up and hangs around for 20-30 seconds but nothing else, it's kinda' disconcerting. :)

I'll have to build a rudimentary GUI to do something for progress indicator; I've got to build a way to set year/semester and be able to handle a non-standard file name/location, anyway, before I can just turn it over in the end even though I can get by for the immediate time crunch this way with a little one-on-one coaching. Right now I've got two versions; one for the SPR and one for FA and both expect a given Excel file and sheet. That works for now to finish getting ready for annual meeting week after next. I can deal with the longer-range later on a more leisurely schedule...."manana!"

This has no subsequent program; it's only updating an Excel that is very time-consuming to do by hand from another.

Thanks for the feedback, muchly appreciated, this was first time I had tried to package anything w/ MATLAB.

dpb 2021-3-21

在 MATLAB Online 中打开

Yes, that was the upshot of the first Q/A? -- I do have the MATLAB Compiler and successfully compiled the existing function as an application and it runs as is.

I'm now trying to figure out whether I can build a little more user-friendly app for the girls in the office with the AppDesigner -- I have figured out how to put some menus and get them to work to be able to select a set of alternative files than the default ones we're working on now -- I can always revert to "plain code" and package it, I'm sure, but was trying to see if the fancy GUI thing would help organize a little more, perhaps.

I discovered I cannot use persistent in a global properties block in AppDesigner; I've yet to experiment on whether can get something to work as want otherwise as a static method in a classdef. That's all new for me, too, never written a MATLAB class.

"I use persistent variables in compiled apps andy the work fine"

Is that between invocations of the app or only on invocations of a function within a single run of the app? What I'd like is

    % Code that executes after component creation
    function startupFcn(app)
      year=2020;                                              % prompt for general use later
      if strcmpi(getenv('username'),'duane.bozarth')          % logged in to SCCC machine
        fd='H:\WPFILES\DF\Financial';                         % head of Financials tree by year
      else                                                    % local machine
        fd='C:\DBNonBusiness\SCCC_Dev_Foundation\Financials\';
      end
      app.awardPath=fd;
      app.awardFile=fullfile(fd,sprintf('%d',year),sprintf('RestrictedAwardSheet_%d.xlsm',year))
      app.awardSheet=sprintf('%d Awards',year);        % revised sheet for fall added columns
    end
    % Menu selected function: BillingFileMenu
    function BillFileFunction(app, event)
      [app.billFile,app.billPath]=uigetfile('*Bill*.xlsx','Select Desired Billing File',"MultiSelect","off");
    end

Instead of the fixed file in the startup code, I'd like that if the user changes that file, directory, that the next time they run it will use that for the default instead. Then, one wouldn't have to always select the right file or update the default somemwhere else or have a default file hanging around to keep current and together after I have left the organization.

Image Analyst 2021-3-22

Persistents and globals do not remain somewhere in computer memory once the app exits, so they're not retained from session to session. That's why you need to store them in a small .mat file. It shouldn't be a big deal. I do it all the time, and even have other files, like splash screen images, sample data files, etc. that I ship along with my executable.

dpb 2021-3-22

I wasn't expecting them to remain in memory; just hoping was a way to get them retained in the executable data space.

请先登录，再进行评论。

Answer 2

dpb 2021-3-20

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/777887-what-takes-to-wrap-matlab-function-to-use-from-excel#answer_653607

While I didn't answer my own Q?, I'll put an Answer here for any others who stumble over the thread and may be as confused as I have been.

Indeed, the solution was as simple as re-installing MingGW-w64 UNDER THE MATLAB APPS BANNER, NOT STANDALONE,

The previous standalone installation I had done years ago still worked/works with the command line tools I use routinely for code development outside MATLAB; but where it is installed wasn't anywhere the MATLAB Compiler looked.

Once reinstalled, it did build the standalone and it runs successfully -- this will be a major help going forward!!!

I've a few things to do to make it work for the actual system on the college server and to handle both semesters, but I updated the local copy here correctly so the functionality is all there!!!

Thanks for all the feedback; it did get me straightened out--the failure when I thought I had everything I needed had me confused--but I didn't find the compilation log until later to see the error and the lack of an error return in the script/batch file when the compiler isn't found is a sizable implementation gaff. I'll submit an enhancement request for that problem.