update the column headng by readin the excel file

% figure
% imshow(imread('2023-07-14_13h06_04.png'))
data = readtable('Book2.xlsx', 'VariableNamingRule','preserve')
data = 6×6 table
    No    Components         DescriptioninGUI         Parameters    Value1    Value2
    __    __________    __________________________    __________    ______    ______

    1       {'bb'}      {'Length'                }      {'L' }        80       110  
    2       {'bb'}      {'Width'                 }      {'W' }       NaN       NaN  
    3       {'bb'}      {'thickness'             }      {'T' }       1.6         2  
    4       {'bb'}      {'height above the plate'}      {'HA'}       NaN       NaN  
    5       {'bb'}      {'height below the plate'}      {'HB'}       NaN       NaN  
    6       {'bb'}      {'edge width'            }      {'CE'}       NaN       NaN  
emptyRows = all(ismissing(data),2);
data(emptyRows,:) = [];
variable = data.Parameters; 
values = {};
for i = 1:size(data, 1)
    values{i} = table2array(data(i, 5:end)); 
    values{i}(all(ismissing(values{i}),2),:) = [];
end 
% remove empty cell
values = values(~cellfun(@isempty,values));
combinations = combvec(values{:})';
combinations = sortrows(combinations,1);                        % <— ADDED
Lv = ~all(ismissing([data.Value1 data.Value2]),2);              % <— ADDED
p = variable(Lv);
comitable= array2table(combinations);
comitable.Properties.VariableNames = p(1:size(combinations,2))
comitable = 4×2 table
     L      T 
    ___    ___

     80    1.6
     80      2
    110    1.6
    110      2

EDIT — (14 Jul 2023 at 15:57)

Added ‘Lv’ (and references to it) to select the correct values of ‘p’.

17 个评论
显示 15更早的评论隐藏 15更早的评论

Star Strider 2023-7-21

在 MATLAB Online 中打开

This was not part of your original post, and the file is different. That aside, hoping to use ‘Book2.xlsx’ instead, reveals that it is no longer posted, so I cannot run the code to see what the results could be.

% figure
% imshow(imread('2023-07-14_13h06_04.png'))
data = readtable('Book2.xlsx', 'VariableNamingRule','preserve')
Error using readtable
Unable to find or open 'Book2.xlsx'. Check the path and filename or file permissions.
emptyRows = all(ismissing(data),2);
data(emptyRows,:) = [];
variable = data.Parameters; 
values = {};
for i = 1:size(data, 1)
    values{i} = table2array(data(i, 5:end)); 
    values{i}(all(ismissing(values{i}),2),:) = [];
end 
% remove empty cell
values = values(~cellfun(@isempty,values))
combinations = combvec(values{:})'
combinations = sortrows(combinations,1)                         % <— ADDED
% % Lv = ~all(ismissing([data.Value1 data.Value2]),2);              % <— ADDED
% % p = variable(Lv);
% % comitable= array2table(combinations);
% % comitable.Properties.VariableNames = p(1:size(combinations,2))

.

Star Strider 2023-7-25

在 MATLAB Online 中打开

Book2.xlsx

I got this far by making some improvements.

The ‘nOflements’ variable is missing, replaced it with ‘numlements’ and that seems to work.

Try this —

data = readtable('Book2.xlsx', 'VariableNamingRule','preserve')
data = 5×6 table
    No    Components    Specification     Parameters    V1     V2 
    __    __________    ______________    __________    ___    ___

    1      {'bb'  }     {'length'    }      {'L'}        50     75
    2      {'bb'  }     {'width'     }      {'W'}        10     15
    3      {'bb'  }     {'height'    }      {'H'}       NaN    NaN
    4      {'bb'  }     {'thickness' }      {'T'}       NaN    NaN
    5      {'bb_1'}     {'allignment'}      {'A'}       NaN    NaN
emptyRows = any(ismissing(data),2)                          % <— CHANGED (Corrected)
emptyRows = 5×1 logical array
   0
   0
   1
   1
   1
 
% data(emptyRows,:) = []
data = data(~emptyRows,:)                                   % <— CHANGED
data = 2×6 table
    No    Components    Specification    Parameters    V1    V2
    __    __________    _____________    __________    __    __

    1       {'bb'}       {'length'}        {'L'}       50    75
    2       {'bb'}       {'width' }        {'W'}       10    15
 
%
 
 Parameters = data(:,4)
Parameters = 2×1 table
    Parameters
    __________

      {'L'}   
      {'W'}   
 
 
 
values = {};
 
for i = 1:size(data, 1)
 
    values{i} = table2array(data(i, 5:end));
 
    values{i}(all(ismissing(values{i}),2),:) = [];
 
end
 
 
   values = values(~cellfun(@isempty,values))
values = 1×2 cell array
    {[50 75]}    {[10 15]}
 
   n = numel(values);
 
  numcomb = prod(cellfun(@numel, values));
 
 
 
% Use nested loops to create combinations
combinationc = cell(numcomb, n);                                    % Preallocate
 
for a = 1:numcomb
 
    remainder = a;
 
    for b = 1:n
 
        array = values{b};
 
        numlements = numel(array);
 
       
 
%         Compute the current index for the current cell array
 
        % index = mod(remainder - 1, nOflements) + 1;
        index = mod(remainder - 1, numlements) + 1;                 % Guessing Here
 
        remainder = (remainder - index) / numlements;
 
       
 
%         Assign the current element to the combination
        
        % Check_Variable = array(index)
       combinationc{a, b} = array(index);                           % Change Name To Avoid OVershadowing The 'combinations' Function, Change Indexing
    end
end
combinationc                                                        % View Result
combinationc = 4×2 cell array
    {[50]}    {[15]}
    {[75]}    {[15]}
    {[50]}    {[10]}
    {[75]}    {[10]}

Is this the result you want?

.

PA 2023-7-27

编辑：PA 2023-7-27

do You know how can i convert this combinations from a cell array to a vector keeping the text entries .

{'50'} {'15'} {'Name'}

{'75'} {'15'} {'Name'}

{'50'} {'10'} {'Age' }

{'75'} {'10'} {'Age' }

{'50'} {'15'} {'Age' }

{'75'} {'15'} {'Age' }

{'50'} {'10'} {'Name'}

{'75'} {'10'} {'Name'}

i tried to use str2doublr but i get this

c =

50 15 NaN

75 15 NaN

50 10 NaN

75 10 NaN

50 15 NaN

75 15 NaN

50 10 NaN

75 10 NaN

Star Strider 2023-7-27

在 MATLAB Online 中打开

Mixed numeric and text can only be combined in a cell array or a table, and a string array is another option (although I am not certain that it is much of an improvement over the cell array here, other than with respect to indexing). The cell2table function might be an option.

Perhaps one of these —

C = {{'50'}    {'15'}    {'Name'}
    {'75'}    {'15'}    {'Name'}
    {'50'}    {'10'}    {'Age' }
    {'75'}    {'10'}    {'Age' }
    {'50'}    {'15'}    {'Age' }
    {'75'}    {'15'}    {'Age' }
    {'50'}    {'10'}    {'Name'}
    {'75'}    {'10'}    {'Name'}};
T1 = cell2table(C)
T1 = 8×3 table
      C1        C2         C3   
    ______    ______    ________

    {'50'}    {'15'}    {'Name'}
    {'75'}    {'15'}    {'Name'}
    {'50'}    {'10'}    {'Age' }
    {'75'}    {'10'}    {'Age' }
    {'50'}    {'15'}    {'Age' }
    {'75'}    {'15'}    {'Age' }
    {'50'}    {'10'}    {'Name'}
    {'75'}    {'10'}    {'Name'}
Text = string(C(:,3));
T2 = array2table(cellfun(@str2double,C(:,[1 2])), 'VariableNames',{'N1','N2'});
T2 = addvars(T2, Text)
T2 = 8×3 table
    N1    N2     Text 
    __    __    ______

    50    15    "Name"
    75    15    "Name"
    50    10    "Age" 
    75    10    "Age" 
    50    15    "Age" 
    75    15    "Age" 
    50    10    "Name"
    75    10    "Name"
S = [cellfun(@str2double,C(:,[1 2])) string(C(:,3))]
S = 8×3 string array
    "50"    "15"    "Name"
    "75"    "15"    "Name"
    "50"    "10"    "Age" 
    "75"    "10"    "Age" 
    "50"    "15"    "Age" 
    "75"    "15"    "Age" 
    "50"    "10"    "Name"
    "75"    "10"    "Name"

.

请先登录，再进行评论。

Answer 2

Image Analyst 2023-7-14

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1996113-update-the-column-headng-by-readin-the-excel-file#answer_1273248

在 MATLAB Online 中打开

You forgot to attach your workbook, and forgot to say what kind of variable you want to work with in MATLAB (table, cell array, double). Cell arrays don't have column headings unless you mean the first row of the cell array. If you have the first row be a string, and subsequent rows have numbers, then you'd be best off using a table instead of a cell array.

t = readtable(yourWorkbookFileName)

If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer