Plots in excel sheets using matlab

I have an excel file with 50 sheets. Each excel sheet has 4 coloumns. I want to plot graph in each excel sheet using 4 coloumns as 4 series of data. How is it possible.

[KALYAN ACHARJYA]

Suppose sheet 1, C1,C2,C3 and C4

Whcih type of plot, is it bar C1,C2,C3 and C4 or vs??

[Vikas Saroha]

line connecting all points vs 1,2,3,------till no of rows.

[KALYAN ACHARJYA]

Sorry, its not clear to me.

Answer 1

[Bob Thompson]

If you want to create a plot in the excel sheet directly you need to use the ActXServer capability.

Here is a brief layout of what you're looking for. It's not going to be perfect, but it should get you started. Variable names are optional, but keep track of what they are calling.

rows = 100; % Set number of rows, if variable this gets much tougher

xl = actxserver('Excel.Application');  % Open excel
% set(xl,'Visible',1);                 % Make excel visible
xlsdir = pwd;                          % Set excel to work in current directory
xls = xl.Workbooks.Open('myexcelfile.xlsx');  % Open specific document
xlss = xls.Worksheets;                 % Define worksheet variable, for easier calling

for i = 1:50;
    xlssl = xlss.get('Item',i);        % Select next worksheet
    xlssl.Activate                     % Activate selected worksheet
    xlchart = xlssl.ChartObjects.Add(xloc,yloc,xsize,ysize); % Create a chart in a defined location and size
    xlchart.Chart.Charttype = 'xlxyscattersmoothNoMarkers';  % Set chart type, can adjust as needed
    for j = 1:rows
        xlchart.Chart.SeriesCollection.NewSeries;               % Create an new series
        xlchart.Chart.SeriesCollection(j).Name = seriesname;  % Name the new series. Must be as string!
        xlchart.Chart.SeriesCollection(j).XValues = [1 2 3 4];
                                       % Define xvalues of series
        xlchart.Chart.SeriesCollection(j).Values = xlssl.Range(sprintf('A%i:D%i',j,j));
                                       % Define yvalues of series
        xlchart.Chart.SeriesCollection.Item(j).Format.Line.ForeColor.SchemeColor = 4;
    end
    
    % Useful formatting stuff
    xlchart.Chart.HasTitle = 1;
    xlchart.Chart.Axes(1).HasTitle = 1;
    xlchart.Chart.Axes(2).HasTitle = 1;
    xlchart.Chart.Axes(1).AxisTitle.Text = 'Number';
    xlchart.Chart.Axes(2).AxisTitle.Text = 'Other Number';
    
    % Set Xaxis range
    xlchart.Chart.Axes(1).Minimumscale = 0;
    xlchart.Chart.Axes(2).Minimumscale = 0;
    xlchart.Chart.Axes(1).MaximumScale = 3.0;
    % Activate grid lines
    xlchart.Chart.Axes(1).HasMajorGridlines = 1;
end

xls.Save;                            % Save the document
xl.Quit;                             % Close the document
xl.delete;                           % Remove Excel reference to allow the document to be opened independantly

[Vikas Saroha]

Dear Bob,

When the command

xls = xl.Workbooks.Open('output.xlsx'); is executed, this error is displayed:

Error using

Interface.Microsoft_Excel_16.0_Object_Library.Workbooks/Open

Invoke Error, Dispatch Exception:

Source: Microsoft Excel

Description: Sorry, we couldn't find output.xlsx. Is it

possible it was moved, renamed or deleted?

Help File: xlmain11.chm

Help Context ID: 0

How to solve this issue?

[Guillaume]

The error message is very clear. Output.xlsx cannot be found. Considering you haven't specified the full path of the file, it's no surprise. If you don't specify the path of the file, excel will be looking for it in its working directory only, most likely C:\users\yourusername\My documents

How to solve this issue?

Specify the full path of the file. If it's in matlab current directory:

xls = xl.Workbooks.Open(fullfile(pwd, 'Output.xlsx');

[Vikas Saroha]

Thanks. It works.

[Dheeraj]

Does there exist a complete guide for the chart variables such as the line below?

xlchart.Chart.SeriesCollection.Item(j).Format.Line.ForeColor.SchemeColor

I want some other costomization options such as removing legend and all...

[Dheeraj]

Nevermind, found it.

https://learn.microsoft.com/en-us/office/vba/api/excel.chart(object)