Export Cell-Data to .xlsx [Saves less data]

2019 7 17
2 个回答
回答已采纳
更新时间：2019 7 31
42 次查看（30 天）

1 个投票

Hello Everyone,
I am using following code to export Cell-data to .xlsx file.
filename='(AutoCheck7000).xlsx';
output = [header; Dataset];
xlswrite(filename,output); % Write to the new excel file.
Where as;
header is cell with 1*13 size
dataset is cell with 189*13 size
I have concentrated them using a variable output but xlswrite is only writing 2 header names instead of all names.
For instance;
the result is:
Whereas, the output-variable is as follows, so .xlsx should show all header names as well:
Any help would be appreciated :-)
Best regard,s
Waqar Ali Memon

3 个评论
显示 1更早的评论 隐藏 1更早的评论

Stephen23
Stephen23 2019-7-17
@Waqar Ali Memon: please upload the variables header and Dataset by clicking the paperclip button.
Waqar Ali Memon
Waqar Ali Memon 2019-7-17
Hello Stephen Cobeldick,
I have clipped both Dataset and header variables.
Thank you :-)
Waqar Ali Memon
Waqar Ali Memon 2019-7-17
Hello Stephen Cobeldick,
While using following code:
file=strcat(strcat('FileName.xlsx'));
fid = fopen(file, 'wt') ; %%% opens above created CSV file.
fprintf(fid, '%s,', header{1,1:end-1}) ; %%% saves header in it.
fprintf(fid, '%s\n', header{1,end}) ; %%% moves cursor to next row.
fclose(fid); %%% closes above created CSV file.
xlswrite(filename,Dataset);
I am getting this error:
Error using fprintf
Function is not defined for 'cell' inputs.

请先登录，再进行评论。

请先登录，再回答此问题。

 采纳的回答

Peter Jarosi
Peter Jarosi 2019-7-17
编辑：Peter Jarosi 2019-7-17

0 个投票

I recommend converting your cell-data to table and using function writetable().
filename='myExcelFile.xlsx';
myTable = cell2table(Dataset, 'VariableNames', header);
writetable(myTable, filename, 'WriteVariableNames', true);
There is a new function writecell() introduced in R2019a, which can write cell objects into Excel files directly. You may also want to try it. https://www.mathworks.com/help/matlab/ref/writecell.html

5 个评论
显示 3更早的评论 隐藏 3更早的评论

Waqar Ali Memon
Waqar Ali Memon 2019-7-17
Great, this is also working and it sounds clean as well. But there is only one option :-( to accept the answer. So, I am accepting this and wish you all the best for your Star Badge. You deserve it :-)
Thank you :-)
Peter Jarosi
Peter Jarosi 2019-7-17
Than you very much!
I appreciate that you accepted this answer.
Waqar Ali Memon
Waqar Ali Memon 2019-7-18
Just an update:
Before using above code we need to follow 3 steps suggested by Peter Jarosi in other post.
First Step:
header = cellfun(@char, header, 'un', 0);
Second Step: Construct valid MATLAB identifiers from input strings.
header = matlab.lang.makeValidName(header)
Third Step:
Dataset = cellfun(@num2str, Dataset, 'un', 0);
Finally:
filename='myExcelFile.xlsx';
myTable = cell2table(Dataset, 'VariableNames', header);
writetable(myTable, filename, 'WriteVariableNames', true);
Credit goes to Peter Jarosi :-)
Peter Jarosi
Peter Jarosi 2019-7-18
Thanks!
I like your more elegant method fixing header problems:
header = matlab.lang.makeValidName(header)
Waqar Ali Memon
Waqar Ali Memon 2019-7-31
Hello Peter Jarosi,
Hope you would be doing great:-). I am incurring a little problem here. By exporting a data to table and than writing a table removes pre 0. I mean it saves 0409 to 409. Is there any way to preserve initial zeros?
Second, can we sort the data in columns as well? For instance, it sorts Text data to flush left and numerical data to flush right. Can we initialize a directon for that?
Any help would be appreciated.
Thank you :-)

请先登录，再进行评论。

更多回答（1 个）

Peter Jarosi
Peter Jarosi 2019-7-17

1 个投票

Actually, your Dataset and header need some massage before converting to table:
filename='(AutoCheck7000).xlsx';
load('Dataset.mat');
Dataset = cellfun(@num2str, Dataset, 'un', 0);
load('header.mat');
header = cellfun(@char, header, 'un', 0);
header = strrep(header, '02', 'file_02');
header = strrep(header, ' ', '_');
header = strrep(header, '.', '_');
header = strrep(header, '-', '_');
header = strrep(header, '(', '_');
header = strrep(header, ')', '_');
header = strrep(header, '[', '_');
header = strrep(header, ']', '_');
myTable = cell2table(Dataset, 'VariableNames', header);
writetable(myTable, filename, 'WriteVariableNames', true);
It'll work for you. I've tested.

2 个评论
显示 无 隐藏 无

Waqar Ali Memon
Waqar Ali Memon 2019-7-17
Hello Peter Jarosi,
Thank you for the solution, could you please elaborate, why and how to add these messages.
For example, if new header is added, show i need to add extra message ?
Once again, appreciated :-)
Peter Jarosi
Peter Jarosi 2019-7-17
编辑：Peter Jarosi 2019-7-17
You're very welcome Waqar Ali Memon!
Massaging header was needed, because it violated the rules of variable names. Quote "A valid variable name begins with a letter and contains not more than namelengthmax characters. Valid variable names can include letters, digits, and underscores." End quote. https://www.mathworks.com/help/matlab/ref/isvarname.html
Your header was in cells of cells format instead of cells of strings format, that's why I applied
header = cellfun(@char, header, 'un', 0);
Your dataset contained mixed numeric and string cells, so I converted all of them to strings.
Dataset = cellfun(@num2str, Dataset, 'un', 0);
Usually it's not necessary but it seemed to be the easiest way to fix your problem.
You may also accept my other answer, because that is more general, so that it might help others to solve their problems (and helps me to achieve rising star badge :-))

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Data Type Identification 的更多信息

产品

版本

R2017b

标签

Community Treasure Hunt

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

Start Hunting!

Translated by