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.
采纳的回答
Fangjun Jiang
2011-8-19
1 个投票
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().
7 个评论
Adriano
2011-8-19
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
2011-8-19
Fangjun Jiang
2011-8-19
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
2011-8-19
Fangjun Jiang
2011-8-19
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
2011-8-19
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
2016-1-22
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')
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
