How do we plot graphs from excel file?

Anu
2021 12 1
1 个回答
回答已采纳
更新时间：2021 12 3
8 次查看（30 天）

0 个投票

I have 100 output files in a folder. The output files are named as output1.xlsx, output2.xlsx, output3.xlsx, and so on. There are two sheets, the second sheet (final graph) is the one that contains data. Please see attached the file. I need to plot for 100 such files.The x axis is the time points i.e., from 0 to 100%. The y axis is the frequency distribution for each variable, img1_F, img2_F, img3_F, img4_M and so on. This frequency distribution over the x axis needs to be plotted for image1 and image2 in the same plot. The title of the plot is the name of the file, in this case it is output1. I need to plot each of them and save as .png in a folder. May I know how should I do it?
Thanks in advance!

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

 采纳的回答

Mathieu NOE
Mathieu NOE 2021-12-1

0 个投票

hello
this is my suggestion, hope it helps
clc
clearvars
fileDir = pwd;
sheet = 2; % data must be retrieved from sheet #2
S = dir(fullfile(fileDir,'output*.xlsx')); % get list of data files in directory
S = natsortfiles(S); % sort file names into natural order (what matlab does not) , see FEX :
%(https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)
legstr = [{'img1 F'};{'img2 F'};{'img3 F'};{'img4 M'};{'img5 M'};{'img6 M'};{'img8 F'};{'img9 M'};{'img10 F'}];
for k = 1:length(S)
data = xlsread(fullfile(fileDir, S(k).name),sheet); % or use a structure (S(k).data ) to store the full data structure
% data array must be splitted in two (fig 1 and fig 2 ) by checking the
% NaN position
ind_nan = find(isnan(data(1,:)));
cols = ind_nan-1;
x1 = data(1,1:cols);
y1 = data(2:end,1:cols);
x2 = data(1,end-cols+1:end);
y2 = data(2:end,end-cols+1:end);
% plot
filename_fig = S(k).name;
ind = findstr(filename_fig,'.');
filename_fig = filename_fig(1:ind-1);
% figure(k); % will create k figures
figure(1); % will create 1 figure only (updated after each new data) => faster batch processing
sg = sgtitle(filename_fig);
sg.FontSize = 20;
sg.FontWeight = 'bold';
subplot(121),plot(x1*100,y1);
xlabel('Percentage');
ylabel('f0 (Hz)');
xtickformat('percentage');
subplot(122),plot(x2*100,y2);
ll= legend(legstr,'Location','South','Orientation','horizontal');
set(ll,'Position',[0.2119 0.015 0.6332 0.0323])
xlabel('Percentage');
ylabel('f0 (Hz)');
xtickformat('percentage')
print(filename_fig, '-dpng')
end

8 个评论
显示 6更早的评论 隐藏 6更早的评论

Anu
Anu 2021-12-2
Thanks @Mathieu NOE for your detailed comments. It worked perfectly. With this piece of code, I tried to do for three subplots, where the data arranged in the similar way. But I get an error when I run for kth for loop, otherwise it s working fine. The error is 'Array indices must be positive integers or logical values'.
Here I copy pasted my code for 3 subplots
clc
clearvars
fileDir = pwd;
sheet = 2; % data must be retrieved from sheet #2
S = dir(fullfile(fileDir,'output*.xlsx')); % get list of data files in directory
S = natsortfiles(S); % sort file names into natural order
legstr = [{'img1 F'};{'img2 F'};{'img3 F'};{'img4 M'};{'img5 M'};{'img6 M'};{'img8 F'};{'img9 M'};{'img10 F'}];
for k = 1:length(S)
data = xlsread(fullfile(fileDir, S(k).name),sheet); % or use a structure (S(k).data ) to store the full data structure
% data array must be splitted in two (fig 1 and fig 2 ) by checking the
% NaN position
ind_nan = find(isnan(data(1,:)));
cols = ind_nan-1;
x1 = data(1,1:cols);
length = length(x1)+1; %+1 or +2 depends if the number of NAN columns
% are 1 and 2, respectively
y1 = data(2:end,1:cols);
x2 = data(1,end-length-cols+1:end-length);
y2 = data(2:end,end-length-cols+1:end-length);
x3 = data(1,end-cols+1:end);
y3 = data(2:end,end-cols+1:end);
% plot
filename_fig = S(k).name;
ind = findstr(filename_fig,'.');
filename_fig = filename_fig(1:ind-1);
% figure(k); % will create k figures
figure(1); % will create 1 figure only (updated after each new data) => faster batch processing
sg = sgtitle(filename_fig);
sg.FontSize = 20;
sg.FontWeight = 'bold';
subplot(131),plot(x1*100,y1);
xlabel('Percentage');
ylabel('f0 (Hz)');
xtickformat('percentage');
subplot(132),plot(x2*100,y2);
xlabel('Percentage');
ylabel('f0 (Hz)');
xtickformat('percentage')
subplot(133),plot(x3*100,y3);
ll= legend(legstr,'Location','South','Orientation','horizontal');
set(ll,'Position',[0.2119 0.015 0.6332 0.0323])
xlabel('Percentage');
ylabel('f0 (Hz)');
xtickformat('percentage')
print(filename_fig, '-dpng')
end
Mathieu NOE
Mathieu NOE 2021-12-2
hello
so I understand this is a expanded code for the case there are 3 (and more ?) and not only 2 blocks of data in your excel sheet
maybe it would be worth to attach the excel file where the problem arise
tx
Anu
Anu 2021-12-2
Yes, @Mathieu NOE, you understand it correctly. I tried to generate the code for the case where there are three images (or 4, or more), not only two. I am able to do it with the code that I have written, but if I put the for loop, I am gettig error. My guess is that the way I have written x2,y2 in my code is not correct when I do the for loop.
Please find attached the excel file. Thanks for your help, I really appreciate it!
Mathieu NOE
Mathieu NOE 2021-12-2
So I make a more robust code even if you have more than 3 blocks of data it should work as soon as you keep to have same 1 row empty separation between blocks and that the sheet name reains 'final graph'
I changed that in the code as the excel files may have variable number of sheets (like the last one , the data was not to be serached in sheet #2)
attached my plots - working for both excel files (2 and 3 data blocks)
all the best
clc
clearvars
fileDir = pwd;
sheet = 'final graph'; % data must be retrieved from sheet named 'final graph'
S = dir(fullfile(fileDir,'output*.xlsx')); % get list of data files in directory
S = natsortfiles(S); % sort file names into natural order (what matlab does not) , see FEX :
%(https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)
legstr = [{'img1 F'};{'img2 F'};{'img3 F'};{'img4 M'};{'img5 M'};{'img6 M'};{'img8 F'};{'img9 M'};{'img10 F'}];
for k = 1:length(S)
data = xlsread(fullfile(fileDir, S(k).name),sheet); % or use a structure (S(k).data ) to store the full data structure
% data array must be splitted in two (fig 1 and fig 2 ) by checking the
% NaN position
[m,n] = size(data);
ind_nan = find(isnan(data(1,:)));
nb_blocks = numel(ind_nan)+1;
ind_start = [1 ind_nan+1];
ind_stop = [ind_nan-1 n];
% plot
filename_fig = S(k).name;
ind = findstr(filename_fig,'.');
filename_fig = filename_fig(1:ind-1);
sg = sgtitle(filename_fig);
sg.FontSize = 20;
sg.FontWeight = 'bold';
% figure(k); % will create k figures
figure(1); % will create 1 figure only (updated after each new data) => faster batch processing
for ci = 1:nb_blocks
x = data(1,ind_start(ci):ind_stop(ci));
y = data(2:end,ind_start(ci):ind_stop(ci));
subplot(1,nb_blocks,ci),plot(x*100,y);
ll= legend(legstr,'Location','South','Orientation','horizontal');
set(ll,'Position',[0.2119 0.015 0.6332 0.0323])
xlabel('Percentage');
ylabel('f0 (Hz)');
xtickformat('percentage')
end
print(filename_fig, '-dpng')
end
Anu
Anu 2021-12-2
Thanks, @Mathieu NOE! It solves the problem! :-)
Mathieu NOE
Mathieu NOE 2021-12-3
My pleasure !
would you mind accepting my answer ?
all the best
Anu
Anu 2021-12-3
It's my pleasure too @Mathieu NOE! Thanks!

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Creating, Deleting, and Querying Graphics Objects 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by