Excel Bubble Chart Code

1 次查看（过去 30 天）
Cameron Martineau
Cameron Martineau 2017-6-19
评论： dpb 2017-6-22
I am trying to model a pulley system in excel from a code that will calculate belt tension. I have tried to plot the system in matlab but was having trouble scaling the size of the markers to be the correct diameter so I created a matlab code that will generate an excel file and plot the data from the belt tension code as a Bubble Chart. My problem is that I have a specified diameter for each pulley. I can manually set the Bubble size in excel to be the appropriate diameter but given that we are going to be constantly changing these values, having matlab set the bubble size would be ideal. Currently it models each pulley as the same size in their appropriate X and Y coordinates. Any help would be greatly appreciated! PulleyTable is sample data and below is a copy of the code:
PulleyTable = table({'CRK','IDL3','AT1','ALT','AT2','WP','IDL1','AC','IDL2'}', ...
[0;123.6;185.27;260.42;228.77;138.84;-62.55;-268.3;41],...
[0;2.2;46.86;58.25;127.35;190.34;182.38;98.3;121],...
[164.16;62.26;57.26;60.56;57.26;124.36;72.26;116.26;72.26],logical([1 0 0 1 0 1 0 1 0]'),...'VariableNames',{'Name','X','Y','D','In_Out'});
PulleyTable.Properties.VariableUnits({'X','Y','D','In_Out'})={'mm','mm','mm','CCW'};
filename = 'pulleygraph.xlsx';
writetable(PulleyTable,filename,'Sheet',1,'Range','A1');
Excel = actxserver('Excel.Application');
ResultFile = ['...\pulleygraph.xlsx'];
Workbook = invoke(Excel.Workbooks,'Open',ResultFile);
set(Excel,'Visible',1);
Chart = Excel.ActiveSheet.Shapes.AddChart;
Chart.Name = 'NanoPulley';
ExpChart = Excel.ActiveSheet.ChartObjects('NanoPulley');
ExpChart.Activate;
try
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');
catch e
end
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(2)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(2)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(2)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(3)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(3)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(3)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(4)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(4)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(4)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(5)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(5)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(5)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(6)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(6)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(6)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(7)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(7)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(7)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(8)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(8)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(8)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(9)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(9)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(9)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(10)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(10)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(10)];
Excel.ActiveChart.ChartType = 'xlBubble';
Axes = invoke(Excel.ActiveChart,'Axes',1);
set(Axes,'HasTitle',1);
set(Axes.AxisTitle,'Caption','X')
Axes = invoke(Excel.ActiveChart,'Axes',2);
set(Axes,'HasTitle',1);
set(Axes.AxisTitle,'Caption','Y')
Excel.ActiveChart.HasTitle = 1;
Excel.ActiveChart.ChartTitle.Characters.Text = 'Nano Pulley System';
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit;
Excel.delete;
clear Excel;
  6 个评论
显示 4更早的评论
Cameron Martineau
Cameron Martineau 2017-6-21
Try putting in the file path for pulleygraph.xlsx for ResultFile, like mine is ResultFile = ('C:\Users\cmartineau\Documents\MATLAB\pulleygraph.xlsx'); it should work then. Regardless, here is what the excel file looks like after it is generated currently
dpb
dpb 2017-6-21
编辑：dpb 2017-6-21
I don't understand that at all...each is same size, not representative of Diameter column. What's the actual objective here?
A Matlab scatter graph that does represent actual area in correct proportions is in Answer below.

请先登录，再进行评论。

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

采纳的回答

dpb
dpb 2017-6-21
编辑：dpb 2017-6-21
pt=PulleyTable; % shorten the name...
scatter(pt.X,pt.Y,pt.D.^2/6,'filled')
xlim([-400 400]), ylim([-50 250])
grid on
yields
To make the sizes proportional to the actual diameters, use pi/4*pt.D^.2 and then you can scale that by any proportionality factor you wish to create the pleasing size of the circles desired for the plot.
The color is a detail; add a color triplet vector as desired; didn't worry about it at this point until figure out what it is that actually are trying to accomplish.
  3 个评论
显示 1更早的评论
dpb
dpb 2017-6-21
编辑：dpb 2017-6-21
No problem! :)
While it may take some digging, just have to read the doc's carefully to follow the meaning for the various inputs. In the description of the area argument, it notes it is the area of the patterns that is proportional to the input, not the radius or other distance measure. Hence, to produce something that reflects that area from a distance, one needs the squared term as the scaling variable.
dpb
dpb 2017-6-22
BTW, you can fix up that plot some that might be useful visual effect...
hold all
hSc(2)=scatter(PT.X,PT.Y,10,'k','filled');
yields
where the center is highlighted with a black dot. Also added 'markeredgecolor','k','linewidth',2 to outline the pulleys.
Salt to suit, of course...

请先登录，再进行评论。

更多回答（0 个）

类别

在 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