Writing cell arrays to Excel

Hello,

I have a 100x1 cell array A that looks like A=[A1; A2; ...; A100 ]. Each row i, in turn, is an by 4 vector. The first 2 columns of shows the values of 6 parameters and the remaining 2 columns of A are scalar. So, a typical row in looks like [{0,2,1,2,3,4} {1,2,1,2,3,1}{0}{4}]. I would like to stack the rows on top of each other and write the cellarray into an excel file. In the final excel file, I should have row and 4 columns. How can I do this?

Thank you for your time.

George

Answer 1

[Deepak]

Hi @george pepper,

To convert the entire dataset into tabular format and write into an excel file, we can first iterate each row and concatenate the two parameter vectors and the two scalar values into a single row vector. Then, we can dynamically calculate the total number of elements in each row to ensure the flattened data is correctly pre-allocated in a numeric matrix. Next, we can use “cell2mat” function to convert the parameter vectors from cell arrays to numeric arrays to concatenate them with scalar values. Finally, we can use “writematrix” function to export the data into an Excel file.

Below is the sample MATLAB code to achieve the same result:

A = { 
    {0, 2, 1, 2, 3, 4} {1, 2, 1, 2, 3, 1} {0} {4}; 
    {1, 3, 2, 3, 4, 5} {2, 3, 2, 3, 4, 2} {1} {5}; 
    }; 

numRows = size(A, 1); 

% Determine the number of elements in each row 
numElements = length(A{1, 1}) + length(A{1, 2}) + 2;  

% Initialize an empty cell array to store the flattened results 
flattenedData = zeros(numRows, numElements); 

for i = 1:numRows 
    % Extract and concatenate the contents of the first two cells 
    param1 = cell2mat(A{i, 1}); 
    param2 = cell2mat(A{i, 2}); 
    scalar1 = A{i, 3}{1}; % Extract the scalar value 
    scalar2 = A{i, 4}{1}; % Extract the scalar value 
    
    % Combine them into a single row 
    flattenedData(i, :) = [param1, param2, scalar1, scalar2]; 
end 

filename = 'output.xlsx'; 
writematrix(flattenedData, filename);

Please find attached the documentation of functions used for reference:

cell2mat - www.mathworks.com/help/matlab/ref/cell2mat.html

writematrix - www.mathworks.com/help/matlab/ref/writematrix.html

I believe this assists in resolving the issue.