Plots in excel sheets using matlab
46 views (last 30 days)
Show older comments
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.
3 Comments
Accepted Answer
Bob Thompson
on 21 May 2019
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
5 Comments
Dheeraj
on 2 Mar 2023
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
on 2 Mar 2023
Nevermind, found it.
https://learn.microsoft.com/en-us/office/vba/api/excel.chart(object)
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!