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)

Answer 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

Answer 2

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)

Answer 3

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...

Answer 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.