How to determine the end of an array you are putting data into in an excel file?

Hello,

So I have a code that is taking some raw data from s2p files and crunching the numbers and then outputting the results into columns in an excel file. The following code will place the "S21_ang" value into the g column from the row 2 all the way to row 810. However, there are not always 800 values. Sometimes there are 200, 400 and sometimes 1600. So I wanted to see if there was a way to code it so that I do not have to manually input the limit and instead it will go until there are no more values.

    xlswrite([fname,'.xlsx'],S12_ang,'Sheet1','g2:g810');

[Paolo]

You will need to dynamically generate the final value for the range. Check out my answer here, I believe it should help you solve the problem:

https://uk.mathworks.com/matlabcentral/answers/409301-how-can-i-determine-last-row-and-pointed-out-in-a-xlsread#answer_327954

[Jesus Perez]

Hi Paolo,

Thank you, it seems like your solution should help, however, it I am trying to play around with it and it won't run an example in the command window.

It tells me 'Undefined function or variable 'xlscol'

So I am unable to get it going.

Do you perhaps know why it won't recognize that function?

[Paolo]

Only reason as to why it would give you that error is because it can't find the function. Did you place the file in your working directory?

Answer 1

[dpb]

Read the doc (carefully)... :)

...
RANGE   Character vector or scalar string that specifies a rectangular portion of
        the worksheet to read. Not case sensitive. Use Excel A1 reference style.

        * If you specify a SHEET, RANGE can either fit the size of
          ARRAY or specify only the first cell (such as 'D2').
        ...

So, just use

xlswrite([fname,'.xlsx'],S12_ang,'Sheet1','G2');

or wherever is the target. Ensure the vector/array is in the shape you want in the spreadsheet, though.

Alternatively, you can dynamically calculate the range; I built a couple utility functions that translate to/from Excel ranges way back when, I think maybe there are some in ML itself now.

[Jesus Perez]

Thanks for the help! This would have worked perfectly, however it takes up the first row and in an earlier piece line of code I set tittles to each columns on the first row.

Is there a way around it?

[dpb]

It works as advertised...illustration:

>> xlsread('test.xls')  % see what is in the hanging around test sheet
ans =
   NaN     1
    10     2
    11     3
    12   NaN
    13   NaN
    14   NaN
    15   NaN
>> xlswrite('test.xls',[20:25].','Sheet1','B2')  % overwite column B starting row 2
>> xlsread('test.xls')  % see the result...
ans =
   NaN     1
    10    20
    11    21
    12    22
    13    23
    14    24
    15    25
>>

The entry in cell B1 is unchanged.

ADDENDUM And just to prove it doesn't matter what the content of B1 is...

>> xlswrite('test.xls',{'Title B'},'Sheet1','B1')
>> [~,~,r]=xlsread('test.xls')
r =
  7×2 cell array
    {'string'}    {'Title B'}
    {[    10]}    {[     20]}
    {[    11]}    {[     21]}
    {[    12]}    {[     22]}
    {[    13]}    {[     23]}
    {[    14]}    {[     24]}
    {[    15]}    {[     25]}
>> xlswrite('test.xls',[30:35].','Sheet1','B2')
>> [~,~,r]=xlsread('test.xls')
r =
  7×2 cell array
    {'string'}    {'Title B'}
    {[    10]}    {[     30]}
    {[    11]}    {[     31]}
    {[    12]}    {[     32]}
    {[    13]}    {[     33]}
    {[    14]}    {[     34]}
    {[    15]}    {[     35]}
>>

[Jesus Perez]

This is perfect! However it did not quite turn out that way for me... Here is the line of code with my info

xlswrite(outputfile,S11_norm.',fname,'B');

So the outputfile is the excel sheet that is getting filled up, S11_norm are just decimal values, and fname is from a struct of names so each sheet in the output file will take on the name of the excel sheet is comes from.

Did I make a mistake in the syntax? I attached a picture of where fname comes from.

I aslo attached picutres of what the excel sheet came out as and what it should look like.

[dpb]

Really can't do anything with images but on

xlswrite(outputfile,S11_norm.',fname,'B');

you asked about syntax and, yes, there is a syntax error in that you didn't specify the starting cell; you specified only the column. If you want to start in a specific cell, you must identify that cell specifically as does my example.

And, of course, the data have to be organized such that the upper LH position of the array is to go into that cell and everything flow down and to the right from there is where you want it to land.

I don't know about the sheet names being in a struct; I've never tried that--I'm pretty sure you would have to dereference and only pass one specific sheet name with each call (altho I've not tried nor researched the doc further on the question, I'd be surprised if xlswrite were vectorized that way).

[Jesus Perez]

Got it!