Thread Subject:

create excel plot with MATLAB?

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

"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)

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

% 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)

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

> 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)

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?

"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.

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?

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.

"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?

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.

"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?

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?