Export data to specified excel rows using writematrix

2022 2 13
1 个回答
回答已采纳
更新时间：2022 2 14
15 次查看（30 天）

0 个投票

Hi,
I am trying to export data to some rows in excel. Here is an example:
writematrix(subjects,'Matlab to Excel Subject Data.xlsx','Sheet',2,'Range','B3:I3')
I have tried using 'WriteMode' and 'append' however, this overrides my headings and titles in the excel file. I would like to fill B3:I3 and continute to B40:I40. I have tried using a for loop which will increase the row by 1.
Here is what I tried:
for i = 3:40
writematrix(subjects,'Matlab to Excel Subject Data.xlsx','Sheet',2,'Range','B',(i+1),':I'(i+1))
end
Unforetunely this does not work. Is there a solution to this?

10 个评论
显示 8更早的评论 隐藏 8更早的评论

Walter Roberson
Walter Roberson 2022-2-13
Perhaps
writematrix(subjects,'Matlab to Excel Subject Data.xlsx', 'Sheet', 2, 'Range', "B"+(i+1) + ":I " + (i+1))
Cassandra Martin
Cassandra Martin 2022-2-13
sadly it didn't work:
Error using writematrix (line 156)
The range must be a character vector of the form 'A1:B2' or 'A1'.
Voss
Voss 2022-2-13
编辑：Voss 2022-2-13
How about this?
for i = 3:40
writematrix(subjects,'Matlab to Excel Subject Data.xlsx','Sheet',2,'Range',sprintf('B%d:I%d',i+1,i+1))
end
Voss
Voss 2022-2-13
But if you want to write to B3:I3 and then continue to B40:I40, those i+1's should be i's because i+1 will give you B4:I4 to B41:I41. So:
for i = 3:40
writematrix(subjects,'Matlab to Excel Subject Data.xlsx','Sheet',2,'Range',sprintf('B%d:I%d',i,i))
end
Cassandra Martin
Cassandra Martin 2022-2-13
It runs without errors, but those fields are blank in excel. Thanks for the reply, we are getting somewhere!
Cassandra Martin
Cassandra Martin 2022-2-13
编辑：Cassandra Martin 2022-2-13
I tried setting it to i = 6 (just as a random row to see if it goes through), and it hasn't shown up either.
Voss
Voss 2022-2-13
Maybe, as another test, try using writematrix() with a different matrix than the one you want to write (subjects) and see if that works, e.g.:
for i = 3:40
writematrix(randn(1,8),'Matlab to Excel Subject Data.xlsx','Sheet',2,'Range',sprintf('B%d:I%d',i,i))
end
(And be sure you're looking at Sheet 2 when you inspect the xlsx file afterward.)
If that works ok, then can you save your variable subjects to a mat file and attach it here? Or describe it (e.g., what type is it and what size)? Does it have NaNs in it (they will show up as blank cells in the file)?
Also, I notice that you're writing the same thing, subjects, to each row. I don't know if that's actually what you're doing or if that was just the code you posted here as an example. If perhaps subjects is a matrix with 38 rows and 8 columns that you want to write to rows 3 to 40 and columns B to I in the file you can write the whole thing without the for-loop:
writematrix(subjects,'Matlab to Excel Subject Data.xlsx','Sheet',2,'Range','B3:I40')
Cassandra Martin
Cassandra Martin 2022-2-13
编辑：Walter Roberson 2022-2-14
The randn(1,8) works well. The reason why I have the same thing, subjects, is because I am trying to seperate the data in their own rows. The subjects matrix has 1 row and 8 columns. This is also within some other for loops (which I have been able to get working).
for subjectNumber = 1:37 %loop over subjects
for trialType = 1:2 %loop over trials
feetPosition = ['apart'];
movementType = ['a_eo'];
for i = 3:40
data = load(['C:\Users\cassi\Documents\school\Thesis\Participant Data\Subject ',...
num2str(subjectNumber),'\trial ',num2str(trialType),'\',num2str(feetPosition),'\', num2str(movementType),'.mat']);
data = data.a_eo;
addpath('C:\Users\cassi\Documents\school\Thesis')
savepath
run('Master_File')
writematrix(subjects,'Matlab to Excel Subject Data.xlsx', 'Sheet', 2, 'Range',sprintf('B%d:I%d',i,i))
end
end
end
Voss
Voss 2022-2-13
Since randn(1,8) works, I guess the thing to do would be to make sure the variable subjects has the values you expect right before you write it to file. It looks like maybe the value of subjects is set by the script Master_File, so you might set a breakpoint in there where subjects is set, and/or a breakpoint on the writematrix() line so you can check the value of subjects right before it goes to file.
Cassandra Martin
Cassandra Martin 2022-2-14
Thank you for all of your help. I've decided it will be best to create one giant matrix and then export it to excel using this method: 'Range','B3:I40'

请先登录，再进行评论。

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

 采纳的回答

Jeremy Hughes
Jeremy Hughes 2022-2-14
Ran in:

0 个投票

Ran in:
I believe it should be possible to do this using (almost) the syntax you tried, and there are some suggestions in the comments, but I wanted to share the "WriteMode" parameter as that should do exactly what you're looking for.
for i = 1:5
writematrix(randn(1,3),'test.xlsx','WriteMode','append');
end
A = readmatrix('test.xlsx')
A = 5×3
0.1677 1.1237 1.0494 -1.1193 -0.3099 -0.5449 -1.1076 1.6841 -0.0993 -2.1830 -0.7395 0.7467 -1.0709 0.0737 0.2002
That said, it's going to be a lot slower writing single rows at a time, since writematrix has to open, write, and close the file each time. It's not meant for incremental writing. So the solution to build up the matrix first makes the most sense to me.
The append appoach could be useful if you have very large arrays, but I suspect that the XLSX format will limit you before the array size will.

更多回答（0 个）

类别

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

产品

版本

R2020a

标签

Community Treasure Hunt

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

Start Hunting!

Translated by