Modify an Excel sheet from Matlab

Question

Is it possible to open a worksheet in excel from matlab and edit the formulas? The idea is to automate an uncertainty analysis by creating a second sheet with the uncertainty in each cell for the value from the previous cell. Essentially, I want to treat the cells as variables and do SQRT(SUM(Partials(xi)^2)) for each cell. Matlab should have no problem with the calc, but can it edit the formulas in sheets?

The process currently is to copy and paste from excel to matlab. Here's a small function that does the uncertainty in matlab against on array of equations:

function [f_u_total f_u] = uncertAnalysis(f, vars, vars_u)
    f_u = [];
    f_u_total = [];
    for(i=1:length(f))
        f(i)
        item = uncertAnalysisi(f(i), vars, vars_u);
        f_u = [f_u; item(1)];
        f_u_total = [f_u_total; item(1)];
    end
end


function [f_u_total f_u] = uncertAnalysisi(f, vars, vars_u)
    f_u = [];
    % take the partials and square them
    for i=1:length(vars)
        f_u = [f_u; vars(i) (diff(f, vars(i)).*vars_u(i)).^2];
    end
    % calculate the RSS
    f_u_total = (sum(f_u(:,2))).^.5;
end

As an aside, the equations look something like this (why I'm not doing this by hand):

=(9*C!S3^2/C!V3^4*C!W3*(C!O3-
C!P3)/C!X3*C!Q3^6*C!F3^4/C!Y3^6/(C!U3^C!Z3)^6*F3^2+1/4*C!S3^2/C!V3^4*C!W3/(C!O3-
C!P3)/C!X3*C!Q3^6*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*O3^2+1/4*C!S3^2/C!V3^4*C!W3/(C!O3-
C!P3)/C!X3*C!Q3^6*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*P3^2+9*C!S3^2/C!V3^4*C!W3*(C!O3-
C!P3)/C!X3*C!Q3^4*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*Q3^2+1/C!V3^4*C!W3*(C!O3-
C!P3)/C!X3*C!Q3^6*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*S3^2+9*C!S3^2/C!V3^4*C!W3*(C!O3-
C!P3)/C!X3*C!Q3^6*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*C!Z3^2/C!U3^2*U3^2+4*C!S3^2/C!V3^6*C!W3*(C!O
3-C!P3)/C!X3*C!Q3^6*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*V3^2+1/4*C!S3^2/C!V3^4/C!W3*(C!O3-
C!P3)/C!X3*C!Q3^6*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*W3^2+1/4*C!S3^2/C!V3^4*C!W3*(C!O3-
C!P3)/C!X3^3*C!Q3^6*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*X3^2+9*C!S3^2/C!V3^4*C!W3*(C!O3-
C!P3)/C!X3*C!Q3^6*C!F3^6/C!Y3^8/(C!U3^C!Z3)^6*Y3^2+9*C!S3^2/C!V3^4*C!W3*(C!O3-
C!P3)/C!X3*C!Q3^6*C!F3^6/C!Y3^6/(C!U3^C!Z3)^6*LOG(C!U3)^2*Z3^2)^(1/2)

Solution 1

You should be able to do it through COM/ActiveX/Automation. Look at the External Interfaces document; there's an example for how to access Excel documents through Excel's Automation interfaces.

I have next-to-no experience manipulating Excel in this manner, but I know you can do just about anything in Excel through Automation and editing cell formulas doesn't sound that hard.

edit: I can't find a reference to the Excel object model, but here's another example: http://support.microsoft.com/kb/301982

Solution 2

EDIT: My previous assumption that XLSWRITE wouldn't work was wrong. I just tried the following in MATLAB:

xlswrite('xltest.xls',{'1' '2' '=SUM(A1,B1)'});

and when I opened the file in excel, the function was in fact there! The limitation on this would be that you would have to use only the functions that are in Excel.

Unfortunately, I don't believe XLSREAD can read the formulae into MATLAB (it appears to just get the result).

PREVIOUSLY SUGGESTED OPTIONS:

You may want to check out the Spreadsheet Link EX software on the MathWorks website, although I'm a bit unfamiliar with it and am not sure if even that can do what you need. Something else that you should look into is MATLAB Builder EX, which "lets you integrate MATLAB® applications into your organization's Excel® workbooks as macro functions or add-ins". Sounds promising...

Solution 3

This isn't a terribly elegant solution, but if you save a new .xls spreadsheet that's simply a tab-delimited (or CSV) file, you can have Matlab generate formulas and when Excel opens the document the values will populate.

In Perl, I've handled it something like this:

open(OUTPUT,'>tmpfile.xls');
print OUTPUT "1\t2\t=A1+B1\n";
close(OUTPUT);

And when tmpfile.xls is opened in Excel, cell C1 will display as 3, which will dynamically update appropriately if A1 or B1 are changed.

(I'm not good with Matlab, so I have no knowledge of any sort of plugins)

Solution 4

Use COM/ActiveX. You can open an Excel instance via the following command:

xlApp = COM.Excel.Application;

Then use a combination of code completion and the VBA help in Excel itself to work out the rest.

Remember to close Excel with

xlApp.Quit;
delete(xlApp);

On a side note, so-called CSE (Control-Shift-Enter) formulae may help? See Google.

Solution 5

As an alternative, see the code below (xlswrite) for using ActiveX from Matlab:

http://www.mathworks.com/matlabcentral/fileexchange/2855

View more solutions

Comments

[ccook]

to clarify, you can save it as an xls and that will keep the equations and not the values?

[ccook]

Seems similar to some XLS reading methods i used in LabView... hm. Checking out the link, ty

[ccook]

I think those would definitely work, somewhat of a price barrier though. If i don't find a free option I think I will go with what's looking like the second one. ty +1

[kyle]

That's correct. It's definitely janky, but Excel will do auto-translation and maintain the formulas. When in doubt, open it and then save it as "real" .xls :) (I feel horrible suggesting this, though)

[ccook]

lol, 'janky' indeed. But it might be the easiest way to get the formulas.

[ccook]

Thank you jason, this looks promising. It looks like i could add an extra worksheet to the workbook as well.

[nimrodm]

Take a look at mathworks.com/support/solutions/data/… for an example.

[kyle]

Also, to clarify, there's no real need even to save it as .xls, that's mostly just so that double-click open with Excel will work (in Windows). Excel will open the file regardless of extension, though you may have to specify its encoding.

[ccook]

Ty kyle, took some 'fedangling' to get it to not save the values.

[kyle]

Ha, you may have just beat "janky". Strange that it wouldn't go dynamic for you, though.

[Hans]

Do you not need something preceeding your first command so as to initiate COM? If you do, what is the command?

[Hans]

Is that the same as xlApp = actxserver('Excel.Application'); ?