MATLAB Answers

How to add an XY plot and a line plot on a chart, on a specific Excel sheet, via MATLAB?

36 views (last 30 days)
Fuh-Cherng
Fuh-Cherng on 21 Nov 2018
Answered: Fuh-Cherng on 28 Nov 2018
I want to create a chart on a specific sheet on Excel via MATLAB. On this chart, I want to plot some raw data (as an XY scatter plot) and a fitted curve (as a simple line plot). I wrote a script (shown below), but encountered an error message:
The 'Value' property name is ambiguous in the '0002086B_0000_0000_C000_000000000046' class.
Error in AddChartInExcel_For_MatlabForum (line 45)
myPlots.SeriesCollection(1).Value = myWorkSheet.Range('B2:B14');
What shall I do to avoid this error message?
In addition, due to my limited programming skills in MATLAB, I used the xlswrite() function to write my raw data on a specific sheet and then used Active-X to create a chart on the same sheet. In other words, I opened and closed the same Excle file multiple times. It did not seem efficient. Unfortunately, it is the only solution that I can come up with so far. I hope I know how to create a chart by using xlswrite() funciton, or how to write raw data on a specific sheet through Active-X. If so, I will not need to open and close the same Excel file multiple times. I want to make my script efficient.
Any comments will be greatly appreciated!!!
Sincerely,
Fuh
% clear memory, clear Command Window
clear; clc;
% obtain x values, y values, and the y values of a fitted curve
x = [169.00; 170.00; 173.00; 175.00; 176.00; 177.00; 178.00; 179.00; 180.00; 185.00; 190.00; 194.00; 195.00];
y = [11.78; 17.41; 13.83; 4.61; 15.36; 14.85; 15.88; 23.56; 14.85; 33.29; 37.9; 72.74; 65.57];
fittedCurve = [12.13; 12.35; 13.29; 14.25; 14.86; 15.58; 16.42; 17.41; 18.57; 27.77; 44.95; 64.81; 70.31];
% set file path and file name for an Excel file
filename = 'practice.xls';
fullpathToExcelFile = [pwd '\' filename];
% name the data sheet that I want to work on
sheetName = 'DataSheet';
% write headings and data onto spreadsheet
xlswrite(fullpathToExcelFile, {'x' 'y' 'fitted curve'}, sheetName, 'A1:C1');
xlswrite(fullpathToExcelFile, [x y fittedCurve], sheetName, 'A2:C14');
% --- BEGINNING of my codes, trying to create a chart on the same spreadsheet --- %
% 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
% open 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(100, 30, 400, 250);
% create an object of Chart.
myPlots = myChartObject.Chart;
myPlots.HasTitle = true;
myPlots.ChartTitle.Text = 'ECAP Growth Function';
% create an object of SeriesCollection (XY plot for the raw data)
line1 = myPlots.SeriesCollection.NewSeries;
myPlots.SeriesCollection(1).XValue = myWorkSheet.Range('A2:A14');
myPlots.SeriesCollection(1).Value = myWorkSheet.Range('B2:B14');
line1.ChartType = 'xlXYScatter';
line1.Name = 'raw data';
% create another object of SeriesCollection (simple line plot for a fitted curve)
line2 = myPlots.SeriesCollection.NewSeries;
myPlots.SeriesCollection(2).Value = myWorkSheet.Range('C2:C14');
line2.ChartType = 'xlLine';
line2.Name = 'fitted curve';
% 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;
% --- END of my codes, trying to create a chart on the same spreadsheet --- %

  0 Comments

Sign in to comment.

Accepted Answer

Harry Vancao
Harry Vancao on 27 Nov 2018
The warning that you see, "The 'Value' property name is ambiguous ..." is caused by you trying to access "Value" property of the seriesCollection. The correct property name is "Values" (plural).
myPlots.SeriesCollection(2).Values = myWorkSheet.Range('C2:C14');
You should be able to use ActiveX to write data to an Excel spreadsheet. Please find the following documentation for details on how to do this here
The following is a modified script that should demonstrate how to write data without using "xlswrite" and thus avoiding opening Excel several times.
% obtain x values, y values, and the y values of a fitted curve
x = [169.00; 170.00; 173.00; 175.00; 176.00; 177.00; 178.00; 179.00; 180.00; 185.00; 190.00; 194.00; 195.00];
y = [11.78; 17.41; 13.83; 4.61; 15.36; 14.85; 15.88; 23.56; 14.85; 33.29; 37.9; 72.74; 65.57];
fittedCurve = [12.13; 12.35; 13.29; 14.25; 14.86; 15.58; 16.42; 17.41; 18.57; 27.77; 44.95; 64.81; 70.31];
% set file path and file name for an Excel file
filename = 'practice.xls';
fullpathToExcelFile = [pwd '\' filename];
% name the data sheet that I want to work on
sheetName = 'DataSheet';
% write headings and data onto spreadsheet
% xlswrite(fullpathToExcelFile, {'x' 'y' 'fitted curve'}, sheetName, 'A1:C1');
% xlswrite(fullpathToExcelFile, [x y fittedCurve], sheetName, 'A2:C14');
% --- BEGINNING of my codes, trying to create a chart on the same spreadsheet --- %
% 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
% open an Excel file
myWorkBook = excelApp.workbooks.Add % open a workbooks object by using the specified filepath and filename. NOTE: Full path is required
% define my work sheet
workSheets = myWorkBook.Sheets;
myWorkSheet = excelApp.Activesheet
% add data here
eActivesheetRange = get(myWorkSheet, 'Range', 'A1:C1');
eActivesheetRange.Value = {'x' 'y' 'fitted curve'};
eActivesheetRange = get(myWorkSheet, 'Range', 'A2:C14');
eActivesheetRange.Value = [x y fittedCurve];
% create an object of ChartObjects
myChartObject = myWorkSheet.ChartObjects.Add(100, 30, 400, 250);
% create an object of Chart.
myPlots = myChartObject.Chart;
myPlots.HasTitle = true;
myPlots.ChartTitle.Text = 'ECAP Growth Function';
% create an object of SeriesCollection (XY plot for the raw data)
line1 = myPlots.SeriesCollection.NewSeries;
myPlots.SeriesCollection(1).XValue = myWorkSheet.Range('A2:A14');
myPlots.SeriesCollection(1).Values = myWorkSheet.Range('B2:B14');
line1.ChartType = 'xlXYScatter';
line1.Name = 'raw data';
% create another object of SeriesCollection (simple line plot for a fitted curve)
line2 = myPlots.SeriesCollection.NewSeries;
myPlots.SeriesCollection(2).Values = myWorkSheet.Range('C2:C14');
line2.ChartType = 'xlLine';
line2.Name = 'fitted curve';
% save and close Excel file
myWorkBook.SaveAs(fullpathToExcelFile);
myWorkBook.Close;
% quit and delete this Active-X COM Excel application object, and delete this object
excelApp.Quit;
excelApp.delete;
% --- END of my codes, trying to create a chart on the same spreadsheet --- %

  0 Comments

Sign in to comment.

Tags

Community Treasure Hunt

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

Start Hunting!