writetable/readtable with multi-line headers

35 次查看（过去 30 天）
ET
ET 2025-1-14
评论： Walter Roberson 2025-1-14
How can I use writetable to make a table to excel sheet and .csv file which has multi-line headers. Headers can include both per-variable headers like units and description, but also per-table headers like description or comments.
For example, take a table like below.
T = table(["Blue";"Red";"Green"],[50;100;75],[0.1;0.5;0.9],...
VariableNames={'Color','Weight','Temp'});
T.Properties.VariableUnits = ["[-]";"[kg]";"°C"];
T.Properties.VariableDescriptions = ["Outside Color";"Gross Weight";"Melting Point"];
T.Properties.Description = "Playdough Properties Chart";
Now I want to make it look like this in excel and .csv files
I know to do this by other means, but I would like to be able to do it with writetable, and also to read it back in with readtable.
Is there a straight-forward way of doing this?

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

采纳的回答

Voss
Voss 2025-1-14
Ran in:
Here's one way:
% specify the file to write to
output_file = 'test.csv';
% construct the table
T = table(["Blue";"Red";"Green"],[50;100;75],[0.1;0.5;0.9],...
VariableNames={'Color','Weight','Temp'});
T.Properties.VariableUnits = ["[-]";"[kg]";"[°C]"]; % added [] around °C
T.Properties.VariableDescriptions = ["Outside Color";"Gross Weight";"Melting Point"];
T.Properties.Description = "Playdough Properties Chart";
T
T = 3x3 table
Color Weight Temp _______ ______ ____ "Blue" 50 0.1 "Red" 100 0.5 "Green" 75 0.9
T.Properties
ans =
TableProperties with properties: Description: 'Playdough Properties Chart' UserData: [] DimensionNames: {'Row' 'Variables'} VariableNames: {'Color' 'Weight' 'Temp'} VariableTypes: ["string" "double" "double"] VariableDescriptions: {'Outside Color' 'Gross Weight' 'Melting Point'} VariableUnits: {'[-]' '[kg]' '[°C]'} VariableContinuity: [] RowNames: {} CustomProperties: No custom properties are set. Use addprop and rmprop to modify CustomProperties.
% write the table to the output file
fid = fopen(output_file,'w');
fprintf(fid,'%s\n', ...
T.Properties.Description, ...
strjoin(T.Properties.VariableNames,','), ...
strjoin(T.Properties.VariableDescriptions,","), ...
strjoin(T.Properties.VariableUnits,","));
fclose(fid);
writetable(T,output_file,'WriteMode','append','WriteVariableNames',false);
% check the contents of the output file
type(output_file)
Playdough Properties Chart Color,Weight,Temp Outside Color,Gross Weight,Melting Point [-],[kg],[°C] Blue,50,0.1 Red,100,0.5 Green,75,0.9
% read the output file
opts = detectImportOptions(output_file);
opts.VariableNamesLine = 2;
opts.VariableDescriptionsLine = 3;
opts.VariableUnitsLine = 4;
opts.DataLines = [5 Inf];
opts = setvartype(opts,1,'string');
Tnew = readtable(output_file,opts);
tmp = readlines(output_file);
Tnew.Properties.Description = tmp(1);
Tnew.Properties.VariableDescriptions = string(Tnew.Properties.VariableDescriptions);
Tnew.Properties.VariableUnits = string(Tnew.Properties.VariableUnits);
Tnew
Tnew = 3x3 table
Color Weight Temp _______ ______ ____ "Blue" 50 0.1 "Red" 100 0.5 "Green" 75 0.9
Tnew.Properties
ans =
TableProperties with properties: Description: 'Playdough Properties Chart' UserData: [] DimensionNames: {'Row' 'Variables'} VariableNames: {'Color' 'Weight' 'Temp'} VariableTypes: ["string" "double" "double"] VariableDescriptions: {'Outside Color' 'Gross Weight' 'Melting Point'} VariableUnits: {'[-]' '[kg]' '[°C]'} VariableContinuity: [] RowNames: {} CustomProperties: No custom properties are set. Use addprop and rmprop to modify CustomProperties.
% the table read from file is equivalent to the original table
isequal(Tnew,T)
ans = logical
1
  2 个评论
ET
ET 2025-1-14
Thank you for this answer, and this does indeed work for .csv case, though I'm not sure of how to use fprintf to also print to an excel sheet. I guess I was hoping there was a way to tell writetable to write the variableunits and variabledescriptions in the same way that readtable allows you to read them.
Walter Roberson
Walter Roberson 2025-1-14
There is not support in writetable() for writing the units or variable descriptions.

请先登录，再进行评论。

更多回答（1 个）

Walter Roberson
Walter Roberson 2025-1-14
writetable() first just the header lines. Then writetable() the data with WriteMode='append'
When you readtable() you can specify VariableNamesRange, VariableUnitsRange, VariableDescriptionsRange
  2 个评论
ET
ET 2025-1-14
How do you write just the header lines with writetable?
Walter Roberson
Walter Roberson 2025-1-14
Actually, writematrix() or writecell() would be easiest for the headers. Construct either an array of string() objects or a cell array of character vectors and writematrix() or writecell() as appropriate.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2024a

Community Treasure Hunt

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

Start Hunting!

Translated by