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

7 views (last 30 days)
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.
  1 Comment
Henry Giddens
Henry Giddens on 13 Oct 2016
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]

Sign in to comment.

Answers (1)

Mostafa
Mostafa on 13 Oct 2016
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).
  2 Comments
Daniel Bridges
Daniel Bridges on 13 Oct 2016
Edited: Daniel Bridges on 13 Oct 2016
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
Mostafa on 17 Oct 2016
Edited: Mostafa on 17 Oct 2016
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.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!