Write cell array with multiple elements in the cell to Excel File

Question

Danna Pinto 2019-4-10

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/455514-write-cell-array-with-multiple-elements-in-the-cell-to-excel-file

编辑： Runcong Kuang 2023-11-7

datasample.mat

Is there a way to write a cell array in which somes cells contain more than one element (vector of numbers) into an excel or cvs file?

I have a cell array that contains a column in which the cells contains vectors of 1-3 numbers, when I tried to use xlswrite it only wrote the cells that contained one element and skipped over the cells which contained more than one element.

Is there another function that I can use instead, or can I do something to structure my data differently so that I can use the xlswrite function?

I have attached my data, so you can see what I mean.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Bob Thompson 2019-4-10

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/455514-write-cell-array-with-multiple-elements-in-the-cell-to-excel-file#answer_369968

xlswrite is not going to write multiple contents of a single cell because an Excel cell can only contain one piece of information at a time, so xlswrite is not sure which bit of data you want in that particular cell, and decides to not write any.

Your best bet would be to reorganize your data. Unfortunately, I am unable to look directly at your data (it's on my end, not yours), but from what you described it should be possible to put all of your data into a 2D array. I have given a method here that isn't the most efficient, but I don't know how to vectorize it off the top of my head. Others are free to comment with better ideas.

data = {};% Your cells with data
nums = NaN(size(data,1),3);
for i = 1:size(data,1);
    nums(i,:) = data{i,column}; % column is the column of your data
end

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Danna Pinto 2019-4-14

在 MATLAB Online 中打开

Thank you for your answer.

What do you mean the column of my data? Do you mean the column of the cell array that contains the vectors?

Also something doesn't seem to work with the code, this is what I did with my data

data_cell= {nameOfTrialCell' TargetTimes_Cell' targetNum_cell' Trialtype_cell'}; 
nums= NaN(size(data_cell,2),3);
for i = 1:size(data_cell,2);
    nums(i,:)= data_cell{i,2};
end

and I got this error

The following error occurred converting from cell to double:

Error using double

Conversion to double from cell is not possible.

请先登录，再进行评论。

Answer 2

Peter Perkins 2019-4-10

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/455514-write-cell-array-with-multiple-elements-in-the-cell-to-excel-file#answer_369994

在 MATLAB Online 中打开

A cell array is not realluy a good choice for storing your data. Use a table, and use writetable to write out that "ragged" array.

>> t = cell2table(data,'VariableNames',{'FileName' 'List' 'X' 'Y'});
>> head(t)
ans =
  8×4 table
                  FileName                       List        X    Y
    _____________________________________    ____________    _    _
    {'numOfStream_1numTarget_3other.wav'}    {1×3 double}    3    2
    {'numOfStream_2numTarget_2other.wav'}    {1×2 double}    2    2
    {'numOfStream_3numTarget_2other.wav'}    {1×2 double}    2    2
    {'numOfStream_4numTarget_1other.wav'}    {[ 21.1676]}    1    2
    {'numOfStream_5numTarget_1other.wav'}    {[ 20.9014]}    1    2
    {'numOfStream_6numTarget_2other.wav'}    {1×2 double}    2    2
    {'numOfStream_7numTarget_3other.wav'}    {1×3 double}    3    2
    {'numOfStream_8numTarget_2other.wav'}    {1×2 double}    2    2
>> writetable(t,'test.csv');

This creates a file like

FileName,List_1,List_2,List_3,X,Y
numOfStream_1numTarget_3other.wav,5.33195011337869,12.1648752834467,12.1648752834467,3,2
numOfStream_2numTarget_2other.wav,13.8178911564626,25.8402267573696,,2,2
numOfStream_3numTarget_2other.wav,8.86573696145125,15.5477097505669,,2,2
numOfStream_4numTarget_1other.wav,21.167619047619,,,1,2
numOfStream_5numTarget_1other.wav,20.9013605442177,,,1,2
numOfStream_6numTarget_2other.wav,14.5290022675737,24.5581405895692,,2,2
[snip]

where the "ragged" array has been written to three columns in the file.

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

Walter Roberson 2023-10-26

在 MATLAB Online 中打开

outfilename = 'test.csv';
V1 = [11;12]; V2 = [4 5 6; 7 8 9]
V2 = 2×3
     4     5     6
     7     8     9
T = table(V1, V2)
T = 2×2 table
    V1        V2     
    __    ___________

    11    4    5    6
    12    7    8    9
TT = rowfun(@(varargin)cell2table(cellfun(@mat2str, varargin, 'uniform', 0),'VariableNames', T.Properties.VariableNames), T)
TT = 2×1 table
            Var1         
      V1          V2     
    _____________________

    {'11'}    {'[4 5 6]'}
    {'12'}    {'[7 8 9]'}
writetable(TT.Var1, outfilename, 'QuoteStrings', 'all')
%cross-check
dbtype(outfilename)
1     V1,V2
2     "11","[4 5 6]"
3     "12","[7 8 9]"
%now see if we can read it back in
opts = detectImportOptions(outfilename, 'Delimiter', ',');
opts = setvartype(opts, opts.VariableNames', 'char')
opts = 
  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
       TrailingDelimitersRule: 'ignore'
                EmptyLineRule: 'skip'
                     Encoding: 'UTF-8'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'V1', 'V2'}
                VariableTypes: {'char', 'char'}
        SelectedVariableNames: {'V1', 'V2'}
              VariableOptions: [1-by-2 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

   Location Properties:
                    DataLines: [2 Inf]
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview
tempT = readtable(outfilename, opts);
recoveredT = cell2table(cellfun(@str2num, table2cell(tempT), 'uniform', 0), 'VariableNames', tempT.Properties.VariableNames)
recoveredT = 2×2 table
    V1        V2     
    __    ___________

    11    4    5    6
    12    7    8    9