Export martrix to excel in Loop

2014 11 22
2 个回答
回答已采纳
更新时间：2014 11 27
2 次查看（30 天）

0 个投票

I have a FOR loop in my code and need to save Result ( vertcat of A and B ) in ONE Excel file in new Row(every columns are same) ,but MATLAB save them (each step) in new Excell file !
for Example:
for i=1:5
A=ones(2,5);
B=zeros(3,5);
xlswrite('testdata.xls',[A;B]);
i=i+1;
end
can you help me

6 个评论
显示 4更早的评论 隐藏 4更早的评论

dpb
dpb 2014-11-22
Simplest is to build the entire array in memory and then xlswrite only once after the loop.
Alternatively, you've got to build a dynamic range argument to place the new section where it belongs; what you've told it to do is to write each time starting at cell A1.
Isay
Isay 2014-11-22
编辑：Isay 2014-11-22
can you say an example ?
my REAL matrix have 1million Rows and 6000 column! I can't build it in memory!
dpb
dpb 2014-11-22
Excel can't handle that size an array anyway, can it?
Isay
Isay 2014-11-22
i guess yes!
dpb
dpb 2014-11-23
编辑：dpb 2014-11-23
Just because the limitations on the number of rows or columns is greater than the limit, that doesn't necessarily mean Excel has any more system memory than does Matlab...I'd guess 48GB is likely going to bring it to its knees as well...or even if not if it actually will try to page, virtual memory paging will kill you. I don't really think Excel is any "more smarter" than Matlab will be on handling this much data. I don't have 64-bit OS so can't really test it, but just sayin'...
Isay
Isay 2014-11-24
please forget 1million Row and other things, and look at my message: i need to Edit " xlswrite('testdata.xls',[A;B]);" to vaertcat A and B UNDER each other in each STEP of Loop. IN ONE EXCEL FILE Just it !
can you Edit for me ?

请先登录，再进行评论。

请先登录，再回答此问题。

 采纳的回答

Image Analyst
Image Analyst 2014-11-23

0 个投票

If you can't fit the 1,000,000 x 6,000 array (48 GB) in memory, then you have to use ActiveX to send it to Excel. See attached demo.

6 个评论
显示 4更早的评论 隐藏 4更早的评论

Isay
Isay 2014-11-23
it's faild .
can you send to me A simple code of it ?
Image Analyst
Image Analyst 2014-11-23
How did it fail? What was the error message?
Isay
Isay 2014-11-23
error at line 89 .
please forget 1million Row and other things, and look at my message: i need to Edit " xlswrite('testdata.xls',[A;B]);" to vaertcat A and B UNDER each other in each STEP of Loop. IN ONE EXCEL FILE Just it !
can you Edit for me ?
Image Analyst
Image Analyst 2014-11-24
Try to increase the row each time
numberOfRows = size(A, 1) + size(B, 1);
thisRow = (i-1)*numberOfRows + 1;
cellReference = sprintf('A%d', thisRow);
xlswrite('testdata.xls',[A;B], cellReference);
Isay
Isay 2014-11-24
thanks, But your code Add sheet in each step of (For) loop. I need all of them in ONE Sheet.
Image Analyst
Image Analyst 2014-11-27
Isay, I don't know why it does that - according to the help it should not. But try this - it works:
delete('testdata.xls'); % Delete any prior file.
for i=1:5
A=ones(2,5);
B=zeros(3,5);
numberOfRows = size(A, 1) + size(B, 1);
thisRow = (i-1)*numberOfRows + 1
cellReference = sprintf('A%d:E%d', thisRow, thisRow + numberOfRows-1)
xlswrite('testdata.xls',[A;B], cellReference);
end

请先登录，再进行评论。

更多回答（1 个）

Moh
Moh 2014-11-27

0 个投票

try
idx = 0;
for i=1:5
A=ones(2,5);
B=zeros(3,5);
[C,D]=size([A;B]);
xlswrite('testdata.xls',[A;B],1,strcat('A',num2str(1+idx)));
idx = idx+C;
end

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by