How to specify X tick intervals on an XY plot and a line plot on a chart, on a specific Excel sheet, via MATLAB?
3 views (last 30 days)
Show older comments
I want to create an XY scatter plot (and a linear regression line), on a chart, on a specific worksheet on an Excel file, via MATLAB. The x and y values (for the XY scatter plot), and the y estimates (for the linear regressoin line), are already saved on that specific worksheet of the Excel file (please see attachment). All I want to do now is to create an XY scatter plot and a linear regression line on a chart, on the same worksheet of the Excel file. With help from MATLAB Forum a couple of months ago, I wrote a MATLAB script (shown below). My script worked okay, with one exception. The x tick marks were not evenly spaced. It seemed that the x tick marks were determined by the actually x values, which were not evenly spaced. This resulted in a problem ==> The linear regression line did not appear as a straight line.
Is there a way that I can specify the x tick marks, so that the x tick marks are evenly spaced (within a minimum and a maximum values on the x axis)? so that the linear regression line will appear as a straight line?
I tried "myChartObject.Chart.Axes(1).Minimum", "myChartObject.Chart.Axes(1).Maximum", "myChartObject.Chart.Axes(1).XTicks", etc. However, none of them worked.
I think the fundamental issue that I am having now is that I do not know the correct syntax of the possible commands/methods/functions/properties of the Active-X objects in MATLAB that are specific for Excel chart and plot creations. If anyone can tell me where I can find those resources, I would greatly appreciate it.
For your review, the Excel file (with the x, y, and y estimate values) is also attached.
Here is my script.
% clear memory, clear Command Window
clear; clc;
% set file path and file name for an Excel file
filename = 'practice.xlsx';
fullpathToExcelFile = [pwd '\' filename];
% name the data sheet that I want to work on
sheetName = 'DataSheet';
% create an object of Active-X COM server for Excel application
excelApp = actxserver('Excel.Application'); % create an Active-X COM server for Excel application
excelApp.DisplayAlerts = false; % turn alerts off. So we don't get the message: "The file already exists. Do you want to replace it?" all the time.
excelApp.Visible = true; % make this Excel application visible, so we can see what happens
% check existence of an Excel file
myWorkBook = excelApp.workbooks.Open(fullpathToExcelFile); % open a workbooks object by using the specified filepath and filename. NOTE: Full path is required
% define my work sheet
workSheets = myWorkBook.Sheets;
myWorkSheet = workSheets.Item(sheetName);
% create an object of ChartObjects
myChartObject = myWorkSheet.ChartObjects.Add(150, 30, 400, 250);
% create an object of Chart.
myPlots = myChartObject.Chart;
myPlots.HasTitle = true;
myPlots.ChartTitle.Text = 'ECAP Growth Function';
% Set X-axis and Y-axis titles.
myChartObject.Chart.Axes(1).HasTitle = true; % X axis title
myChartObject.Chart.Axes(1).AxisTitle.Text = 'Stimulus Intensity (cu)';
myChartObject.Chart.Axes(2).HasTitle = true; % Y axis title
myChartObject.Chart.Axes(2).AxisTitle.Text = 'ECAP Amplitude (uV)';
% create an object of SeriesCollection (an XY plot for the raw data)
line1 = myPlots.SeriesCollection.NewSeries;
%myPlots.SeriesCollection.Item(1).XValue = myWorkSheet.Range('A2:A14');
myPlots.SeriesCollection.Item(1).Values = myWorkSheet.Range('B2:B14');
line1.ChartType = 'xlXYScatter';
line1.Name = 'raw data';
% create another object of SeriesCollection (a simple line for linear regression)
line2 = myPlots.SeriesCollection.NewSeries;
myPlots.SeriesCollection.Item(2).XValue = myWorkSheet.Range('A2:A14');
myPlots.SeriesCollection.Item(2).Values = myWorkSheet.Range('C2:C14');
line2.ChartType = 'xlLine';
line2.Name = 'linear regression';
% save and close Excel file
myWorkBook.Save();
myWorkBook.Close;
% quit and delete this Active-X COM Excel application object, and delete this object
excelApp.Quit;
excelApp.delete;
Here is a snap shot of the chart on Excel that I have produced. (NOTE: The linear regression line does not appear as a straight line, because the x tick marks are determined by the actual x values that are not spaced evenly.)
3 Comments
dpb
on 6 Jan 2019
Record a macro in Excel and decipher it, then, maybe your simplest route.
"My boss prefers..."
There's no accounting for st.... :(
Accepted Answer
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!