programatically change range of excel sheet depending on data

Hello everyone,

I am creating an array which may vary in size,especially number of rows.I am exporting this array to an excel sheet. Using : xlswrite('example33.xlsx',er1,'b3:em28') Right below the last row i am doing some other calculations and exporting another array. xlswrite('example33.xlsx',er2(fr,:),'b29:em29') As it becomes obvious if the number of rows from the first array becomes more than what i have specified i lose information. I tried something really logical and practical like xlswrite('example33.xlsx',er1,'b3:max(max(Reading))') %Reading is the array that defines the size of the array i export to excel. But Matlab does not like it.

Does anyone have any idea or previous experience on this matter?

Thank you in advance.

Answer 1

[Fangjun Jiang]

It can be handled. Before you write the array to the Excel sheet, you can use size() function to get the number of rows and columns. xlswrite() allows you to specify the range of the sheet to write to so you just need to "remember" the position of the last writing and properly calculate the position of the next writing.

The only thing you need to do is to have a function to convert a number to the Excel column position. For example, second column means 'B', 26th column means 'Z' and 27th column means 'AA'. The function is called 'dec2base27'. It is inside the xlswrite.m. You need to dig it out and make it a separate function so you can use it. Maybe also dig out the 'base27dec' function too. I've done it and advised several others to do the same. It's quite useful for doing xlswrite().

[Adriano]

How should extracting the function from that file must be done exactly? I don't want to mess anything with the MATLAB functions or scripts. Putting it into another file wouldn't conflict with the fact that xlswrite() has a function with the same name?

[van lef]

Thanks for the quick response.

I am trying to open the file but it comes out in machine language...

Tried on a different pc also and the same happens.

What am i missing?

[Fangjun Jiang]

What do you mean, guys?

Type edit xlswrite.m, search for dec2base27, till you see the line 'function s = dec2base27(d)'. Copy that function (just less than 10 lines of code, plus the comments) and paste to a new file and save it as dec2base27.m.

You are done! Put that file in your MATLAB path so you can use it anywhere.

[van lef]

These are very usefull information but maybe i didn't clarify enough what my problem is.

The columns are stable for my code. The lines are dependend on the size of an other array.

example: xlswrite('example33.xlsx',er1,'b3:em28')

all are stable except the '28' which can be 24,60 or something else.

And right after this line i want to continue with another array.

I would like to know if it possible to manipulate that point.

something like xlswrite('example33.xlsx',er1,'b3:em max(max(Reading))')

[Fangjun Jiang]

Assume your column is always the same, something like this:

StartRow=3;

N_Col=size(array,1);

Range=['b',num2str(StartRow),':em',num2str(StartRow+N_Col-1)];

xlswrite(File,Array,Range);

StartRow=StartRow+N_Col;

N_Col=size(array2,1);

Range=...

[Fangjun Jiang]

You have a misunderstanding. For example, array=rand(3,4)

max(max(array)) is the maximum value of array, it might be 0.98

or something else. What you need is the number of rows in array,

which can be obtained by size(array,1).

By the way, call to xlswrite should be xlswrite(file, array, sheet, range)

[Bheki Ngobe]

Hi everyone, I am trying to print string consisting of characters and nuumbers, and am getting the following error Warning: Could not start Excel server for export. XLSWRITE will attempt to write file in CSV format. > In xlswrite (line 174) Error using xlswrite (line 187) An error occurred on data export in CSV format.

Caused by: Error using dlmwrite (line 112) The input cell array cannot be converted to a matrix.

code is copied exactyy as is from matlab command example here it is

   d = {'Time','Temperature'; 12,98; 13,99; 14,97};
   xlswrite('testdata2.xls', d, 1, 'E1')