Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
create excel plot with MATLAB?

Subject: create excel plot with MATLAB?

From: Morgan Fox

Date: 15 Jul, 2010 18:20:10

Message: 1 of 16

I have written a script which looks in a directory, calls up appropriate files, and analyzes them all one at a time with a for loop. It takes data from an excel file, manipulates it, and saves it to a new excel file. I need an excel plot as well as the data in the new excel file... is there a way to have excel create a plot through MATLAB? I know I can send a MATLAB plot to excel with xlswritefig, but that's not exactly what I'm going for.

Any ideas?

Thanks

Subject: create excel plot with MATLAB?

From: Andy

Date: 15 Jul, 2010 19:26:20

Message: 2 of 16

"Morgan Fox" <mdf8@duke.edu> wrote in message <i1njgq$1og$1@fred.mathworks.com>...
> I have written a script which looks in a directory, calls up appropriate files, and analyzes them all one at a time with a for loop. It takes data from an excel file, manipulates it, and saves it to a new excel file. I need an excel plot as well as the data in the new excel file... is there a way to have excel create a plot through MATLAB? I know I can send a MATLAB plot to excel with xlswritefig, but that's not exactly what I'm going for.
>
> Any ideas?
>
> Thanks

You can use actxserver to have very fine control of Excel documents in MATLAB. Here is some sample code to get you started, but you should spend lots of time reading the documentation and exploring the Excel objects.



%%

e = actxserver('excel.application');
eWs = e.Workbooks;
eW = eWs.Add;
eS = eW.ActiveSheet;
e.Visible = 1;

%%

x=sin(1:100)';
eS.Range('A1:A100').Value = x;

%%

eCO = eS.ChartObjects.Add(100, 30, 400, 250);
eCO.Chart.ChartWizard(eS.Range('A1:A100'));
eCO.Chart.ChartType = 1; % view the chart before moving on

%%

eCO.Chart.ChartType = 65; % view it again

%%

eCO.Chart.HasTitle = true;
eCO.Chart.ChartTitle.Text = 'This is the title text'; % view it again

%%

eW.Close;
e.Quit;
delete(e)

Subject: create excel plot with MATLAB?

From: Morgan Fox

Date: 15 Jul, 2010 19:43:04

Message: 3 of 16

Andy, I think that will work. I appreciate it.

Subject: create excel plot with MATLAB?

From: Andy

Date: 15 Jul, 2010 20:03:40

Message: 4 of 16

% Apologies for my previous post. The following is better:

%%

e = actxserver('excel.application');
eWs = e.Workbooks;
eW = eWs.Add;
eS = eW.ActiveSheet;
e.Visible = 1;

%%

x=(0:2:100)';
y=sin(x);
eS.Range('A1:B50').Value = [x y];

%%

eCO = eS.ChartObjects.Add(100, 30, 400, 250);
eC = eCO.Chart;
eC.SeriesCollection.NewSeries;
% better control of the data if the line number
% is not your x-axis
eC.SeriesCollection(1).Value = eS.Range('B1:B50');
eC.SeriesCollection(1).XValue = eS.Range('A1:A50');

%%

eCO.Chart.ChartType = 1; % view the chart before moving on

%%

eCO.Chart.ChartType = 65; % view it again

%%

eCO.Chart.HasTitle = true;
eCO.Chart.ChartTitle.Text = 'This is the title text'; % view it again

%%

eW.Close;
e.Quit;
delete(e)

Subject: create excel plot with MATLAB?

From: WC Luk

Date: 21 Mar, 2012 17:29:35

Message: 5 of 16

Thank you for posting this.

I have two follow up questions though.

1. how do you label the axis? how do I find the objects to the x and y axis, is there one?

2. More importantly, where do you go find out the methods(or language...not sure what the proper term is here) to program this? I use the get function in matlab but it is evidently that a lot of options are not shown in the get function.
Is there a library somewhere you can refer me to?

thanks
WC

Subject: create excel plot with MATLAB?

From: WC Luk

Date: 21 Mar, 2012 21:48:18

Message: 6 of 16

> 1. how do you label the axis? how do I find the objects to the x and y axis, is there one?

I just found out how to label your axis.

Following the example above, once you have defined eS to be your sheet and eC to be your chart object, you could label the axis like this.

% Make sure you have a graph on display already%

%% First you have to initialize the AxisTitle on your chart
eC.Chart.axes(1).HasTitle = 1;
%% Then you could change the text.
eC.Chart.axes(1).AxisTitle.Text = "whatever";

so axes(1) is the primary x axis
axes(2) is the primary y axis
if you have secondary axis
axes(2,1) and axes(2,2) would give you the x,y of secondary

But my second question still remains...how do you find an list of all the functions and how to use the commands ...or what the commands are? is there a list or a book somewhere? I thought the VBA skills would translate but there are so many subtle differences that it makes it feel like this interface is a completely different animal.

Cheers,
WC






> %%
>
> e = actxserver('excel.application');
> eWs = e.Workbooks;
> eW = eWs.Add;
> eS = eW.ActiveSheet;
> e.Visible = 1;
>
> %%
>
> x=(0:2:100)';
> y=sin(x);
> eS.Range('A1:B50').Value = [x y];
>
> %%
>
> eCO = eS.ChartObjects.Add(100, 30, 400, 250);
> eC = eCO.Chart;
> eC.SeriesCollection.NewSeries;
> % better control of the data if the line number
> % is not your x-axis
> eC.SeriesCollection(1).Value = eS.Range('B1:B50');
> eC.SeriesCollection(1).XValue = eS.Range('A1:A50');
>
> %%
>
> eCO.Chart.ChartType = 1; % view the chart before moving on
>
> %%
>
> eCO.Chart.ChartType = 65; % view it again
>
> %%
>
> eCO.Chart.HasTitle = true;
> eCO.Chart.ChartTitle.Text = 'This is the title text'; % view it again
>
> %%
>
> eW.Close;
> e.Quit;
> delete(e)

Subject: create excel plot with MATLAB?

From: Stan

Date: 4 Jul, 2012 22:37:18

Message: 7 of 16

Hello, I have a question regarding this:

eC.SeriesCollection(1).Value = eS.Range('B1:B50');
eC.SeriesCollection(1).XValue = eS.Range('A1:A50');

The first line indicates the range for y-values and the second line for x-values.

My Question:
Is there a way, with ActiveX to select y-values over multuiple columns? For example: how can the y-range be set as B1:F50?

Subject: create excel plot with MATLAB?

From: WC Luk

Date: 5 Jul, 2012 16:07:06

Message: 8 of 16

"Stan" wrote in message <jt2giu$dfg$1@newscl01ah.mathworks.com>...
> Hello, I have a question regarding this:
>
> eC.SeriesCollection(1).Value = eS.Range('B1:B50');
> eC.SeriesCollection(1).XValue = eS.Range('A1:A50');
>
> The first line indicates the range for y-values and the second line for x-values.
>
> My Question:
> Is there a way, with ActiveX to select y-values over multuiple columns? For example: how can the y-range be set as B1:F50?

Well, for beginners, it is not possible to select B1:F50 as the y-range EVEN in excel.

I think what you are trying to simulate is how Excel automatically assigns rows/columns of data into different series if you highlight a bunch of data and then select insert chart.

To simulate this you could do this:

Say that your data are in the range of B1:F50 and your sheet handle is Sht.

Rge = Sht.Range('B1:F50');
Chrtobj = Sht.ChartObjects.Add(100, 30, 400, 250); % Create Chart Object.
Chrtobj.Chart.Charttype = 'xlXYScatter';%% Specify chart type, feel free to change this to whatever chart type you want.
Chrtobj.Chart.SetSourceData(Rge);%% So the trick here is to use the SetSourceData function.

I have personally never found it useful to use that. Excel almost nevers gets it the way I want. I would suggest doing it the hard way and use a for loop and go through the columns to get the series individually.

Hope this answers your question.

Subject: create excel plot with MATLAB?

From: Stan

Date: 6 Jul, 2012 04:03:11

Message: 9 of 16

Okay that is helpful. However, if this has to be done in a loop, then that is a problem becasue this is a range of columns:

for i = 1:5 %I am stopping at 5 because I would need to loop through columns B-F
.
.
.
end

The column names are not numeric. The counter that loops through is i which is numeric. How would it know that it has to specify a column in EXCEL?

Subject: create excel plot with MATLAB?

From: WC Luk

Date: 6 Jul, 2012 14:17:08

Message: 10 of 16


Build a function that converts Integers to Alphabets(excel style) and vice versa. If you're gonna use this actxserver a lot , you'll be using this converter all the time.

Subject: create excel plot with MATLAB?

From: Stan

Date: 7 Jul, 2012 02:40:31

Message: 11 of 16

"WC Luk" <jack.luk@hotmail.com> wrote in message <jt6s14$rpl$1@newscl01ah.mathworks.com>...
>
> Build a function that converts Integers to Alphabets(excel style) and vice versa. If you're gonna use this actxserver a lot , you'll be using this converter all the time.

Ok I found one: XLSCOLSTR2NUM (file exchange)
http://www.mathworks.com/matlabcentral/fileexchange/15748-excel-column-number-to-column-name

XLSCOLNUM2STR(1) gives 'A'
XLSCOLSTR2NUM(A) gives 1

Suppose I want to plot 10 columns as my y-variable. As you suggested, the loop would loop throuh 10 columns (the numbers take up 50 rows per column) to select them each time through and plot them against a common x. so the loop would only apply to y. So I would need the following in a loop:

start = [XLSCOLNUM2STR(1+1) '1']
end = [XLSCOLNUM2STR(1+1) '50']
range=[start ':' end] %range would equal B1:B50

Now, I need to select C1:C50, D1:D50, etc, etc. So, I would need the following:

start = [XLSCOLNUM2STR(2+1) '1']
end = [XLSCOLNUM2STR(2+1) '50']
range=[start ':' end] %range would equal C1:C50

start = [XLSCOLNUM2STR(3+1) '1']
end = [XLSCOLNUM2STR(3+1) '50']
range=[start ':' end] %range would equal C1:C50

and so on............the basic trend is:
[XLSCOLNUM2STR(i+1) '50'], with i running from 1 to 10

I was looking at the code posted above. Here is the original code:

> x=(0:2:100)';
> y=sin(x);
> eS.Range('A1:B50').Value = [x y];
> %%
> eCO = eS.ChartObjects.Add(100, 30, 400, 250);
> eC = eCO.Chart;
> eC.SeriesCollection.NewSeries;
> eC.SeriesCollection(1).Value = eS.Range('B1:B50');
> eC.SeriesCollection(1).XValue = eS.Range('A1:A50');
> %%
> eCO.Chart.ChartType = 1; % view the chart before moving on
> %%
> eCO.Chart.ChartType = 65; % view it again

Here is what I was thinking of for a loop:

> x=(0:2:100)';
> y=sin(x);
> eS.Range('A1:K50').Value = [x y]; %11 columns including column A, which is x
> %%
> eCO = eS.ChartObjects.Add(100, 30, 400, 250);
> eC = eCO.Chart;
> eC.SeriesCollection.NewSeries;

for i = 1:10
    > eC.SeriesCollection(1).Value = eS.Range([XLSCOLNUM2STR(i+1) '1'] ':' [XLSCOLNUM2STR(3+1) '50']);
end

> eC.SeriesCollection(1).XValue = eS.Range('A1:A50');
> %%
> eCO.Chart.ChartType = 1; % view the chart before moving on
> %%
> eCO.Chart.ChartType = 65; % view it again

Is this what you meant? Is this making sense or have I missed something?

Subject: create excel plot with MATLAB?

From: WC Luk

Date: 9 Jul, 2012 17:53:07

Message: 12 of 16

Yes, that is what I mean.

Although there are a few bugs in your script though. My corrections may not be complete as I haven't test them. But here are a few major bugs I saw

you are making 10 New series right? so you need to make 10 new series. so put the
> eC.SeriesCollection.NewSeries;
into the for loop. Also in your existing for loop, the Series that you are assigning to isn't changin as you are always refering to SeriesCollection(1). Change it to SeriesCollection(i). You need to do the same for your XValue too.

Idk what you are trying to do the ChartType........Test your script before you put it on here. It'll help you get to your point quicker and better learning for yourself too.

Subject: create excel plot with MATLAB?

From: Stan

Date: 11 Jul, 2012 17:33:28

Message: 13 of 16

"WC Luk" <jack.luk@hotmail.com> wrote in message <jtf5q3$quj$1@newscl01ah.mathworks.com>...
> Yes, that is what I mean.
>
> Although there are a few bugs in your script though. My corrections may not be complete as I haven't test them. But here are a few major bugs I saw
>
> you are making 10 New series right? so you need to make 10 new series. so put the
> > eC.SeriesCollection.NewSeries;
> into the for loop. Also in your existing for loop, the Series that you are assigning to isn't changin as you are always refering to SeriesCollection(1). Change it to SeriesCollection(i). You need to do the same for your XValue too.
>
> Idk what you are trying to do the ChartType........

Ah, I see what you mean for the first two points.

However, I'm not certain about what you mean by ChartType. I'm just using the same ChartType as what was posted earlier. Actually, I would prefer to keep this style with no changes. Or am I missing something?

Subject: create excel plot with MATLAB?

From: Stan

Date: 13 Jul, 2012 04:41:36

Message: 14 of 16

Okay, I tried this but it gave an error message:

x=(0:2:100)';
y=sin(x);
eS.Range('A1:K50').Value = [x y];

%%

eCO = eS.ChartObjects.Add(100, 30, 400, 250);
eC = eCO.Chart;
for ii = 1:10
eC.SeriesCollection.NewSeries;
% better control of the data if the line number
% is not your x-axis
%eC.SeriesCollection(ii).Value = eS.Range('[[[char(ExcelCol(ii+1)) '1'] ':'] char(ExcelCol(3+1)) '50']');
%eC.SeriesCollection(1).Value = eS.Range('B1:B50');
eC.SeriesCollection(ii).Value = eS.Range('B1:K50');
eC.SeriesCollection(1).XValue = eS.Range('A1:A50');
end

>> ExcelMat

Error: Object returned error code: 0x800A03EC

Error in ExcelMat (line 25)
eC.SeriesCollection(ii).Value = eS.Range('B1:K50');

The EXCEL file only has numbers in columns B1:B50. Columns C1:C50 have "N/A" in them.

Any suggestions?

Subject: create excel plot with MATLAB?

From: Cynthia Cruz

Date: 8 Oct, 2014 00:46:16

Message: 15 of 16

Can you give me more specifics on how to format a secondary axis. I have tried the following based on your suggestion:

% Set the primary x-axis
Axes = invoke(Excel.ActiveChart,'Axes',1);
set(Axes,'HasTitle',1);
set(Axes.AxisTitle,'Caption','Day')
 
% Set the primary y-axis
Axes = invoke(Excel.ActiveChart,'Axes',2);
set(Axes,'HasTitle',1);
set(Axes.AxisTitle,'Caption','Use')

% Set the secondary x-axis
Axes = invoke(Excel.ActiveChart,'Axes',(2,1));
set(Axes,'HasTitle',1);
set(Axes.AxisTitle,'Caption','Day')

% Set the secondary y-axis
Axes = invoke(Excel.ActiveChart,'Axes',(2,2));
set(Axes,'HasTitle',1);
set(Axes.AxisTitle,'Caption',RF')






"WC Luk" <jack.luk@hotmail.com> wrote in message <jkdib2$r30$1@newscl01ah.mathworks.com>...
> > 1. how do you label the axis? how do I find the objects to the x and y axis, is there one?
>
> I just found out how to label your axis.
>
> Following the example above, once you have defined eS to be your sheet and eC to be your chart object, you could label the axis like this.
>
> % Make sure you have a graph on display already%
>
> %% First you have to initialize the AxisTitle on your chart
> eC.Chart.axes(1).HasTitle = 1;
> %% Then you could change the text.
> eC.Chart.axes(1).AxisTitle.Text = "whatever";
>
> so axes(1) is the primary x axis
> axes(2) is the primary y axis
> if you have secondary axis
> axes(2,1) and axes(2,2) would give you the x,y of secondary
>
> But my second question still remains...how do you find an list of all the functions and how to use the commands ...or what the commands are? is there a list or a book somewhere? I thought the VBA skills would translate but there are so many subtle differences that it makes it feel like this interface is a completely different animal.
>
> Cheers,
> WC
>
>
>
>
>
>
> > %%
> >
> > e = actxserver('excel.application');
> > eWs = e.Workbooks;
> > eW = eWs.Add;
> > eS = eW.ActiveSheet;
> > e.Visible = 1;
> >
> > %%
> >
> > x=(0:2:100)';
> > y=sin(x);
> > eS.Range('A1:B50').Value = [x y];
> >
> > %%
> >
> > eCO = eS.ChartObjects.Add(100, 30, 400, 250);
> > eC = eCO.Chart;
> > eC.SeriesCollection.NewSeries;
> > % better control of the data if the line number
> > % is not your x-axis
> > eC.SeriesCollection(1).Value = eS.Range('B1:B50');
> > eC.SeriesCollection(1).XValue = eS.Range('A1:A50');
> >
> > %%
> >
> > eCO.Chart.ChartType = 1; % view the chart before moving on
> >
> > %%
> >
> > eCO.Chart.ChartType = 65; % view it again
> >
> > %%
> >
> > eCO.Chart.HasTitle = true;
> > eCO.Chart.ChartTitle.Text = 'This is the title text'; % view it again
> >
> > %%
> >
> > eW.Close;
> > e.Quit;
> > delete(e)

Subject: create excel plot with MATLAB?

From: WC Luk

Date: 5 Nov, 2014 16:41:10

Message: 16 of 16

Cynthia,

What would you want to format with your secondary axis.
I would advise using the 'get' function to see what it is you can change and go from there.

Cheers
WC

"Cynthia Cruz" wrote in message <m121go$h54$1@newscl01ah.mathworks.com>...
> Can you give me more specifics on how to format a secondary axis. I have tried the following based on your suggestion:
>
> % Set the primary x-axis
> Axes = invoke(Excel.ActiveChart,'Axes',1);
> set(Axes,'HasTitle',1);
> set(Axes.AxisTitle,'Caption','Day')
>
> % Set the primary y-axis
> Axes = invoke(Excel.ActiveChart,'Axes',2);
> set(Axes,'HasTitle',1);
> set(Axes.AxisTitle,'Caption','Use')
>
> % Set the secondary x-axis
> Axes = invoke(Excel.ActiveChart,'Axes',(2,1));
> set(Axes,'HasTitle',1);
> set(Axes.AxisTitle,'Caption','Day')
>
> % Set the secondary y-axis
> Axes = invoke(Excel.ActiveChart,'Axes',(2,2));
> set(Axes,'HasTitle',1);
> set(Axes.AxisTitle,'Caption',RF')

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us