How do we plot graphs from excel file?

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!

Answer 1

[Mathieu NOE]

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


    

[Mathieu NOE]

Natural-Order Filename Sort - File Exchange - MATLAB Central (mathworks.com)

is what you need to run my code

[Anu]

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]

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]

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]

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]

Thanks, @Mathieu NOE! It solves the problem! :-)

[Mathieu NOE]

My pleasure !

would you mind accepting my answer ?

all the best

[Anu]

It's my pleasure too @Mathieu NOE! Thanks!