how to Write all Variables in workspace into excel ?

35 次查看（过去 30 天）
kH
kH 2020-9-4
编辑： Walter Roberson 2023-11-22
Example
In Workspace Variable stored :
a 23384x1 double
b 122223x1 double
c 23384x1 double
d 23384x1 double
e 'ADDC'
Output Required in Excel
a b c d e
1 1 ADDC
3 1 ADDC
3 2 ADDC
4 4
. 4
. 4
. .
.
. 1222223 row
.
23384 row

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

采纳的回答

Dana
Dana 2020-9-4
编辑：Dana 2020-9-4
The easiest way is probably to put everything into a cell array and then write it to a .xls file using writecell. The question then becomes about the best way to create the cell array. If you have a relatively small number of variables that's always the same and has the same size, you can just do it manually.
If you want to automate, here's one way to do it:
a = [1;3;3;4];
b = [1;1;2;4;4;4];
e = 'ADDC';
save variables % save workspace to variables.mat
data = load('variables'); % load back in and assign to struct variable
f = fieldnames(data); % cell containing variable names
nf = numel(f); % number of variables
sz = zeros(nf,1); % array to hold dimensions of variables
% Here we get variable dimensions for each variable
for j = 1:nf
dataj = data.(f{j}); % load in variable j
% convert char arrays to string
if ischar(dataj)
dataj = convertCharsToStrings(dataj);
data.(f{j}) = dataj;
end
sz(j) = numel(dataj); % size of variable j
end
mxsz = max(sz); % max variable size
c = cell(mxsz+1,nf); % cell array to hold data
c(1,:) = f'; % column headers
for j = 1:nf
dataj = data.(f{j})(:); % variable j (turned into a column vector if necessary)
c(2:sz(j)+1,j) = num2cell(dataj); % assign to cell array
end
The cell array c is:
7×3 cell array
{'a' } {'b'} {'e' }
{[ 1]} {[1]} {["ADDC" ]}
{[ 3]} {[1]} {0×0 double}
{[ 3]} {[2]} {0×0 double}
{[ 4]} {[4]} {0×0 double}
{0×0 double} {[4]} {0×0 double}
{0×0 double} {[4]} {0×0 double}
Note that I'm unclear on what you wanted for that last column. In your post, you put three rows of "ADDC", and then nothing afterward. I'm guessing that's not what you want. If you only want one appearance of "ADDC", then the above is how you do it. If instead you wanted each character in the character array in a different row, take out the "convert char arrays to string" block, in which case c would be:
7×3 cell array
{'a' } {'b'} {'e' }
{[ 1]} {[1]} {'A' }
{[ 3]} {[1]} {'D' }
{[ 3]} {[2]} {'D' }
{[ 4]} {[4]} {'C' }
{0×0 double} {[4]} {0×0 double}
{0×0 double} {[4]} {0×0 double}
In either case, you can write this to an Excel file by
writecell(c,'variables.xls')
NB: DON'T USE a .xlsx EXTENSION HERE. For some reason, empty values don't get written properly in the .xlsx format and you'll get some strange looking output. Use .xls instead, and if you need to convert to .xlsx, do that manually from inside Excel ("Save as").
  5 个评论
显示 3更早的评论
Dana
Dana 2020-9-7
If you're on a pre-2019a version of MATLAB, an equivalent to writecell is to first convert the cell array to a table using cell2table, and then use writetable.
It's true that, for reasons that aren't clear to me, files made with xlswrite are a different size than those made with writecell/writetable (sometimes larger, sometimes smaller). The differences don't seem to be massive as far as I've seen, but I haven't pushed things to the limit.
Also, thinking about it more, I think the way I dealt with the character arrays in my previous post isn't robust to having multiple rows of e. Here's a better way I think (with the cell2table/writetable method instead of writecell):
a = [1;3;3;4];
b = [1;1;2;4;4;4];
e = 'ADDC';
save variables % save workspace to variables.mat
data = load('variables'); % load back in and assign to struct variable
f = fieldnames(data); % cell containing variable names
nf = numel(f); % number of variables
sz = zeros(nf,1); % array to hold dimensions of variables
% Here we get variable dimensions for each variable
for j = 1:nf
dataj = data.(f{j}); % load in variable j
% convert char/string arrays to cell arrays
if ischar(dataj) || isstring(dataj)
dataj = cellstr(dataj);
data.(f{j}) = dataj;
end
sz(j) = numel(dataj); % size of variable j
end
mxsz = max(sz); % max variable size
c = cell(mxsz+1,nf); % cell array to hold data
c(1,:) = f'; % column headers
for j = 1:nf
dataj = data.(f{j})(:); % variable j (turned into a column vector if necessary)
if iscell(dataj) % if already a cell array
c(2:sz(j)+1,j) = dataj; % assign directly to to cell array
else % otherwise
c(2:sz(j)+1,j) = num2cell(dataj); % covert to cell and assign to cell array
end
end
T = cell2table(c(2:end,:),'VariableNames',c(1,:));
writetable(T,'variables.xls')
Walter Roberson
Walter Roberson 2020-9-7
编辑：Walter Roberson 2023-11-22
There is more than one way to write a string (character vector) to a .xlsx file. One is to write the literal character vector as text inside the XML that is inside the .xlsx file. The other is to create a reference table of charater vectors on a different XML file inside the .xlsx, and then the XML for the worksheet refers to an entry number. The two varieties give the same result when you look at them, but the version with reference tables is potentially more compact.
I have not encountered any rule or guideance about when something should be added to the reference table or not, so it might depend upon the time/space tradeoffs that the program is willing to go to. xlswrite() on Windows would typically invoke Excel, and that is likely to have a different selection algorithm than MATLAB has for writetable().

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by