read and write xlsx files
7 次查看(过去 30 天)
显示 更早的评论
Hi All,
I have a folder with 20 xlsx files. i want matlab to read all the xlsx files and take column 3 from each file and write in a new table. (please note i want column 3 of each xlsx files to be in a sepeate column in the new table so i can plot them against each other.
Thanks,
0 个评论
回答(1 个)
Mathieu NOE
2022-10-5
hello see example code below
you can either write the result as a simple numeric array (as here) with writematrix, but if you really need the result as a table, simply use writetable instead.
I assuled here all input data files have same amount of data (number of rows) so it was pretty easy to do thevertical concatenation. Otherwise we might have to do some padding
fileDir = pwd; % current directory (or specify which one is the working directory)
outfile = 'OUT.xlsx'; % output file name
S = dir(fullfile(fileDir,'*.xlsx')); % get list of data files in directory
S = natsortfiles(S); % sort file names into natural order , see :
%(https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)
out_data = [];
for k = 1:length(S)
filename = S(k).name % to actually show filenames are sorted (see command window)
tmp = readmatrix( fullfile(fileDir, filename)); %
out_data = [out_data tmp(:,3)]; % store column 3 of each xlsx files and do vertical concatenation
end
% store out_data in excel file
writematrix(out_data,fullfile(fileDir,outfile));
4 个评论
Mathieu NOE
2022-10-6
Finally , came up with yet improved code where no need anymore to give estimated max row qty
enjoy it !
clc
clearvars
fileDir = pwd; % current directory (or specify which one is the working directory)
outfile = 'OUT.xlsx'; % output file name
S = dir(fullfile(fileDir,'data00*.xlsx')); % get list of data files in directory
S = natsortfiles(S); % sort file names into natural order , see :
%(https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)
out_data = [];
max_nn = 0;
for k = 1:length(S)
filename = S(k).name % to actually show filenames are sorted (see command window)
out = readmatrix( fullfile(fileDir, filename),"Range",'C:C'); % extract column 3 (C) of each xlsx files
nn = numel(out);
oo = size(out_data,1);
% case 1 : out_data longer (strictly) than new data (out)
if oo<nn % need to padd some NaN's to out_data before doing the final horizontal concatenation
out_data = [out_data; NaN(nn-oo,size(out_data,2))]; % vertical concatenation
end
% case 2 : out_data shorter (strictly) than new data (out)
if oo>nn % need to padd some NaN's to out before doing the final horizontal concatenation
out = [out; NaN(oo-nn,1)]; % vertical concatenation
end
% case 3 : out_data same length as new data (out)
% nothing to do !!
out_data = [out_data out]; % final horizontal concatenation
end
% store out_data in excel file
writematrix(out_data,fullfile(fileDir,outfile));
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!