CODEWITHSUNDEEP
excelmatlab
JPow
JPow

Reputation: 51

MATLAB: Write Dynamic matrix to Excel

I'm using MATLAB R2009a and following this example:

http://uk.mathworks.com/help/matlab/matlab_external/using-a-matlab-application-as-an-automation-client.html

I'd like to edit it so that I can write a matrix of unknown size into a column in an excel sheet, therefore not explicitly stating the range. I've attempted it this way:

%Put MATLAB data into the worksheet
Hop = [47; 53; 93; 10]; %Pretend I don't know what size this matrix is.
p = length(Hop);
p = strcat('A',num2str(p));
eActivesheetRange = e.Activesheet.get('Range','A1:p');
eActivesheetRange.Value = Hop;

However, this errors out. I've tried several variations of this to no avail. For example, using 'A:B' puts this array in columns A and B in excel and a NAN into every cell beyond my array. As I only want column A filled, using simple ('Range','A') errors out also.

Thanks in advance for any advice you can offer.

Upvotes: 3

Views: 752

Answers (2)

akamath
akamath

Reputation: 580

Essentially, the idea is to replace xx in 'B1:Bxx' with the number of elements in your matrix.

I tried this: 

e = actxserver('Excel.Application');
eWorkbook = e.Workbooks.Add;
e.Visible = 1;
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item',1);
eSheet1.Activate;
A = [1 2 3 4];
eActivesheetRange = e.Activesheet.get('Range','A1:A4');
eActivesheetRange.Value = A;

The above is directly from the link you shared. The reason why what you are trying to do is failing is that the p you pass into e.Activesheet.get() is a variable and not a string. To avoid this, try the following: 

B = randi([0 10],10,1)
eActivesheetRange = e.Activesheet.get('Range',['B1:B' num2str(numel(B))]);
eActivesheetRange.Value = B;

Here, num2str(numel(B)) will pass in a string, which is the number of elements in B. This is variable in the sense that it depends on the number of elements in B.

Upvotes: 0

Suever
Suever

Reputation: 65460

You're having issues because you're trying to use your variable p in a string directly

range = 'A1:p';

    'A1:p'

This isn't going to work, you want to include the value of p. There are a number of ways you can do this.

In the code you have provided, you have already set p = 'A10' so if you wanted to append that to your range, you'd perform string concatenation

 p = 'A10';
 range = strcat('A1:', p);

I personally prefer to use sprintf to place the number directly into my strings rather than concatenating a bunch of strings.

p = 10;
range = sprintf('A1:A%d', p)

    'A1:A10`

So if we adapt your code to use this we should get 

range = sprintf('A1:A%d', numel(Hop));
eActivesheetRange = e.Activesheet.get('Range', range);
eActivesheetRange.Value = Hop;

Also just to be a little explicit, I would use numel rather than length as length is ambiguous. Also, I would flatten Hop into a column vector just to make sure that it's the proper dimension to be written to the spreadsheet.

eActivesheetRange.Value = Hop(:);

Upvotes: 3

Related Questions