Creating an XY Scatter plot with ActX

6 次查看（过去 30 天）
Robert Guldi
Robert Guldi 2021-5-29
评论： Farley Postgate 2022-9-9
I'm trying to make a scatter plot of the A and B columns in an Excel Spreadsheet using ActX. However I am stuggling to use the invoke function, more specifically
invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
I've been having trouble with the invoke function from the start and have been doing my best to avoid it, as it seems like maybe it isn't compatible with R2021a (?), but I am struggling to find any sort of documentation to help me make the XY Scatter other than a few questions posted in 2014. Is there some place where I can find the documentation that I can't seem to find myself, or some other way to create a series of data on the chart? Thanks in advance for any help you might have.

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

回答（1 个）

Allen
Allen 2021-5-30
编辑：Allen 2021-6-1
The below method works similar to calling invoke, but does not expressly call the invoke function. There are also a few additional options for formating charts included.
% Create activeX COM server and intialize objects
Excel = actxserver('Excel.Application');
WB = Excel.Workbooks.Open('Your Excel filename.xlsx'); % Opens the desired Excel file
Excel.WindowState = 'xlMaximized'; % Maximize the window to the screen
Excel.Visible = 1; % Set the Excel file to visible. Default is hidden.
Excel.DisplayAlerts = 0; % Turns off Excel warnings
% Create abbreviated handles. Not necessary, but helpful.
WS = WB.WorkSheet;
WC = WB.Charts;
% Adds a new chartsheet with specified name and select chartsheet
Chart = WC.Add([],WS.Item(WS.Count));
Chart.Name = 'Your Chartsheet Name';
WC.Item(Chart.Name).Select;
% Add data series. CHANGE EXCEL RANGES AS NECESSARY
NSx = Excel.ActiveChart.SeriesCollection.NewSeries;
sheet = 'your worksheet name containing data to add to scatterplot';
NSx.XValues = ['=''',sheet,'''!$A$2:$A$11']; % string representing the excel range with X-data
NSx.Values = ['=''',sheet,'''!$B$2:$B$11']; % string representing the excel range with Y-data
% Changes the chart type to a scatter plot with markers
Excel.ActiveChart.ChartType = 'xlXYScatterLines'; % Only needs to be called once if adding additional series to plot
% Additional series formatting options
NSx.Name = 'Your Series Name'; % string
NSx.Format.Line.Weight = 1.0; % Line weight
NSx.MarkerStyle = 'xlMarkerStyleCircle'; % Markers type
NSx.Format.Line.ForeColor.RGB = RGB([red,green,blue],'excel'); % Line color [0-255]
NSx.MarkerBackgroundColor = 0; % Marker Color (black)
NSx.MarkerSize = 4; % Marker Size
% Assigns descriptions for the axes and chart titles
% Setting the x-axis
XAxis = Excel.ActiveChart.Axes(1);
XAxis.HasTitle = 1;
XAxis.AxisTitle.Caption = 'Time (sec)';
XAxis.TickLabelPosition = 'xlTickLabelPositionLow';
XAxis.HasMajorGridlines = 1;
XAxis.MinimumScale = -SupLim;
XAxis.MaximumScale = SupLim;
% Setting the y-axis
YAxis = Excel.ActiveChart.Axes(2);
YAxis.HasTitle = 1;
YAxis.AxisTitle.Caption = 'Acceleration (g)';
YAxis.HasMajorGridlines = 1;
YAxis.MinimumScale = XLim(1);
YAxis.MaximumScale = XLim(2);
% Sets the chart title
Excel.ActiveChart.HasTitle = 1;
Excel.ActiveChart.ChartTitle.Characters.Text = titxt;
% Change legend position. Legend requires manual fontsize change.
Excel.ActiveChart.Legend.Position = 'xlLegendPositionTop';
% Save and Close Excel file
Excel.ActiveWorkbook.Save;
Excel.Quit;
Excel.delete;
  3 个评论
显示 1更早的评论
Allen
Allen 2021-6-1
编辑：Allen 2021-6-1
@Robert Guldi the error you are getting is likely because there are no chartsheets in the specified workbook. That line is trying to add a new chartsheet after the last chartsheet, but will error if none are present. You can replace with the following to add a chartsheet after the last worksheet instead.
Chart = WC.Add([],WS.Item(WS.Count));
I updated my answer to use this line instead and also remove the portion that was creating a new worksheet named 'delete'. That was a left over bit from one of my scripts that I forgot to remove and is not necessary.
Farley Postgate
Farley Postgate 2022-9-9
This is great, any easy way to plot 4 or 5 series against one series such as time?

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by