how to Write all Variables in workspace into excel ?

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

[Adam Danz]

https://www.mathworks.com/help/matlab/import_export/exporting-to-excel-spreadsheets.html

Answer 1

[Dana]

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").

[kH]

how can i write using xlswrite ?

Also facing Issue in writing last column containing 'ADDC' thoughout the length of row ?

[Dana]

xlswrite is deprecated and shouldn't be used. Is there some particular reason you want to use it?

For the 'ADDC', you want it to appear in all 122223 rows? There are various ways you could do that, but my suggestion is to change the variable e so that it has that number of rows. So like, e = repmat(e,122223,1). Then just run the code as above.

[kH]

But Writecell Function is not availabel for MATLAB 2017b . So what's the Aleratenative for it and also the data file are larger ?

[Dana]

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]

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().