Where is my mistake in trying to write a cell array with both strings and numbers to XLS file?

   datecolumn = datestr(dlmread('data/time.txt'),'mm/dd/yy');
for loop = 1:numberofloans
   datamatrix = {'Interest Rate','Date','Principal','Accumulated Interest';
              loan(loop).interestrate,cellstr(datecolumn),loan(loop).principal,loan(loop).accumulatedinterest}
   xlswrite('data/loanhistory.xlsx',datamatrix,loop,'A1')
end

For loop = 12,

datamatrix = 

    'Interest Rate'    'Date'         'Principal'      'Accumulated Interest'
    [         0.05]    {30x1 cell}    [30x1 double]             [30x1 double]

The Excel file has the header row, the interest rate in A2, but the rest is blank, i.e. the last three cells in the second row failed to write to the Excel spreadsheet.

[Henry Giddens]

The problem is that you are trying to write 3 matrices into single cells in the excel spreadsheet. Excel cannot put a 30x1 array into a single cell. You can write cell arrays using xlswrite - in fact that is what you have done, but the data that is not the correct size will not be written.

You should maybe try and rearrange your data so it is something like this:

datamatrix = 
    'Interest Rate'    'Date'         'Principal'      'Accumulated Interest'
    [         0.05]    'Date 1'       [1]              [2]
    [         0.05]    'Date 2'       [2]              [3]
.
.
.
    [         0.05]    'Date 30'       [30]              [31]

Answer 1

[Mostafa]

You can't pass data of "cell" class to an xls file, so you need to convert all data to strings. Either cast char(data) or access the data inside data{1} , and for numeric values use num2str(data).

[Daniel Bridges]

I don't fully understand your answer; perhaps it is "too shorthand" for me.

Is the datecolumn cell ({30x1 cell} of datamatrix) the only data of cell class? (I think so.) I'm not sure how to convert it into a 30x1 vector of strings ...

I am confused because I thought datecolumn, 30x8 char, was already a vector of strings. But when I tried writing it to XLS, MATLAB gives the error:

 Error using xlswrite (line 219)
ActiveX - Element of a cell array cannot be a character
matrix.

Perhaps this error message indicates a separate problem -- that for some reason a cell array -- A = {(data)} -- cannot be used for storing character matrices. But I don't understand the difference between a character matrix and a string -- but apparently a string is merely a one-row character matrix. ... so I'm still confused.

Perhaps the problem is having a cell nested within a cell, that I need to have one number and one string per cell. Perhaps this is what you were saying -- that the data being 'cell' class meant that cell had a cell inside it, instead of the data to be written?

I have been able to work around the problem in this way, but how do I combine the data to execute only one xlswrite command, instead of sixty?

 for loop = 1:numberofloans
   datamatrix = {'Interest Rate','Date','Principal','Accumulated Interest'};
   xlswrite('data/loanhistory.xlsx',datamatrix,loop,'A1');
   xlswrite('data/loanhistory.xlsx',loan(loop).interestrate,loop,'A2');
   xlswrite('data/loanhistory.xlsx',cellstr(datecolumn),loop,'B2');
   xlswrite('data/loanhistory.xlsx',loan(loop).principal,loop,'C2');
   xlswrite('data/loanhistory.xlsx',loan(loop).accumulatedinterest,loop,'D2');
end

[Mostafa]

Basically, the problem with data of 'cell' class, is that they can be strings, numeric values, tables, arrays, or actually any type of data. This is perfect in the Matlab environment, since it enables all sorts of stuff, however you can't simply export this data "as it is" outside Matlab.

datamatrix = 
    'Interest Rate'    'Date'         'Principal'      'Accumulated Interest'
    [         0.05]    {30x1 cell}    [30x1 double]             [30x1 double]

This is the data you have inside Matlab, a 2*4 cell matrix. The first row has 'simple data', i.e. not arrays or matrices, so it was exported correctly, however in the second row we see a {30x1} data, which simply cannot be exported in one step. In order to export this data using xlswrite, you need to:

• Expand the {30x1} arrays you have into 30 rows,
• Format the data correctly, and
• Fill in the empty spaces.

%Your for loop here

So, why did your solution work? Because when you did a for loop, you already accessed the data one item at a time, so Matlab was able to export each item of the data individually. The problem here is that xlswrite is very time consuming, and putting it in a for loop will probably slow your code a lot. What you can do better is to format the data first, then write the data in a single step. A simple solution for that maybe:

%Your original data
InterestRateValue = loan(loop).interestrate;
DateValue = cellstr(datecolumn);
PrincipalValue = loan(loop).principal;
AccInterestValue = loan(loop).accumulatedinterest;
%Get max possible data length
maxArrLength = max(max(max(numel(InterestRateValue), numel(DateValue)), numel(PrincipalValue)), numel(AccInterestValue));
%Create empty matrix with max possible size
newDataMat = repmat({''}, [maxArrLength +1, 4]);
%Fill in headers
newDataMat(1,1:end) = {'Interest Rate','Date','Principal','Accumulated Interest'};
%Fill in data as per the columns
newDataMat(2:numel(InterestRateValue)+1, 1) = num2cell(InterestRateValue);
newDataMat(2:numel(DateValue)+1, 2) = cellstr(DateValue);
newDataMat(2:numel(PrincipalValue)+1, 3) = num2cell(PrincipalValue);
newDataMat(2:numel(AccInterestValue)+1, 4) = num2cell(AccInterestValue);

Presumeably writing newDataMat into the xls file (like in your original question) will fill in the data correctly. The code is a bit long, but I've figured that the size of the data may vary in each loop.