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

조회 수: 25(최근 30일)
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
Henry Giddens
Henry Giddens 2016년 10월 13일
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]

댓글을 달려면 로그인하십시오.

답변(1개)

Mostafa
Mostafa 2016년 10월 13일
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
Mostafa
Mostafa 2016년 10월 17일
편집: Mostafa 2016년 10월 17일
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.

댓글을 달려면 로그인하십시오.

Community Treasure Hunt

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

Start Hunting!

Translated by