How to insert a Chart in Excel through Matlab

I know there are many old posts about this topic, but none was able to help me, or outdated.

My project involves about 20 columns of Data, each 20.000 rows. All Calculations in Matlab are done and charts created.

But unfortunatly I need the whole evaluation in an Excel File, including several editable Charts.

Using xlswrite I created the Excel Files, now I struggle to create the Charts the way I like.

I tryed using this code, but got several errors and wasn't able to choose the X and Y Values of the Chart.

if true
 Excel = actxserver('Excel.Application');  
 WB = Excel.workbooks.Open('D:\...\Test.xlsx');
 Charts = WB.Charts; 
 Chart = invoke(Charts,'Add');
 invoke(Chart, 'SetSourceData', Excel.Range('Sheet1!$B$2:$B$16')); %%here an error occurs "Error using 
                                                                      COM.Excel_Application/Range
                                                                      Error: Object returned error code: 0x800A03EC"

end

I searched the web for hours how to use ActiveX to add Charts, but no suggestion worked for me.

So my question is, to make it clear:

1. How to insert Charts in Excel using Matlab (general up to date ActiveX code structure)
2. How to select own columns for the XValue and the YValues (Range)
3. How to get acces to Chart Titles, Axes, Line appearance and Legend

Can anyone please help me out? Thank you a lot!

Kilian

By the way, I'm using latest Excel 2016, and Matlab R2017a. I need this for a project in an internship during college.

Answer 1

[Guillaume]

Not directly relevant to your question: I often see questions where people use invoke and get when dealing with COM. In most cases it's not needed; your code would work just as well (or as bad) with:

excel = actxserver('Excel.Application');
wb = excel.Workbooks.Open(somefile);
charts = wb.Charts;
chart = charts.Add;
chart.SetSourceData(excel.Range('Sheet1!$B$2:$B$16'));

and is easier to read and gives you tab completion.

Relevant to your question: I get no error and a chart with the above code, when testing against an excel file picked at random. This is using excel 2013 but I don't believe 2016 changed anything in that respect. Matlab is R2017a but that does not matter for your problem.

First thing to know is where the error is coming from, the range creation or setSourceData, so decouple that last line into:

rg = excel.Range('Sheet1!$B$2:$B$16');
chart.SetSourceData(rg);

and tell us which line causes the error. I would also recommend

excel.Visible = true;

after you've created the excel object, so you can see what is actually happening.

Finally, if you can attach you workbook (or a dummy one that causes the error) that would help.

[Kilian Weber]

Thanks a lot for that fast reply.

(First of all: I'm sorry for being so incompetent, but I started using Matlab one month ago and so far just got in touch with all kinds of calculations und GUI stuff... So consider this first) I just tried to use what I found about this topic, so I used COM. I read about it's unselessness but I didn't find any alternative code. But I will use your version from now on forward.

Here you can see the code, I used now (still not working on example File):

excel = actxserver('Excel.Application');
wb = excel.Workbooks.Open('D:\...\Test999.xlsx');
charts = wb.Charts;
chart = charts.Add;
rg = excel.Range('Sheet1!$B$2:$B$16');
chart.SetSourceData(rg);
excel.Visible = true;

I get two errors: First at the step (rg = excel.Range('Sheet1!$B$2:$B$16');):

Error using COM.Excel_Application/Range
Error: Ausnahmefehler des Servers.
(my Excel is in German, it probably produced the second error, which means "exception error of the server")

and the second at (chart.SetSourceData(rg);):

Undefined function or variable 'rg'.

[Guillaume]

Well, the second error is because the first error prevented the creation of the rg variable.That's normal.

The problem is thus with the creation of the range object. Instead of the rg = ... line, try:

sheet1 = wb.Worksheets.Item('Sheet1');
rg = sheet1.Range('$B$2:$B$16');

If that does not work, which line causes the error?

As said, if you can attach the workbook it would be easier to help you.

[Kilian Weber]

I tried this too... Still Errors:

Error using Interface.000208D7_0000_0000_C000_000000000046/Item
Invoke Error, Dispatch Exception: Ungültiger Index.

Error in InsertChartinExcelTest (line 6)
sheet1 = wb.Worksheets.Item('Sheet1');

By the way... It maybe wasn't as good of a idea to try these things as my Excel now is getting crazy with opening multible files and denying acces to write to other files too.... Is there a Matlab Code to stop such things ...I don't exactly know what it is Restart of the Computer solved this problem^^ anyway... Is there a command to close active sheets or so?

I added my files down here

[Guillaume]

Well, there's no sheet named 'Sheet1' in your workbook. It's no wonder you get an error when you define a range that refers to a sheet named Sheet1. Try replacing the name with 'Tabelle1', i.e.

chart.SetDataSource(excel.Range('Tabelle1!$B$2:$B$16'));

or, you could refer to the sheet by its index, i.e. to refer to the first sheet:

chart.SetDataSource(wb.Worksheets.Item(1).Range('$B$2:$B$16'));

my Excel now is getting crazy

If every time you try something, you start with:

excel = actxserver('Excel.Application')

and never ends with

excel.Quit

then it's no wonder. actxserver starts a new invisible instance of excel every time you call it. If you never close that instance (with Quit) then you'll end up with zillions instances of excel consuming memory and locking open files. In that case, kill all excel processes with task manager.

This is one of the reason I advised you to set the 'Visible' property to true immediately after the actxserver line. You'll see that new instance of excel and what it's doing.

Note that excel.Quit won't truly quit excel if you've opened a file and made some modification to it. Instead, it'll pop up a dialog asking whether you want to save the changes to the file. If excel is still invisible, the dialog will also be invisible and excel will stay in a state of limbo. To force quit an excel instance, discarding any change made to files:

excel.DisplayAlerts = false;  %prevent the save modification dialog from popping up
excel.Quit;  %quit excel discarding any unsaved changes

[Kilian Weber]

Thanks a lot!! This finally works !! I prefer this chart.SetDataSource(wb.Worksheets.Item(1).Range('$B$2:$B$16')); version, because it's more general, so the programm can later work on Excel Versions of every langage.

I totally forgot, that Excel changes names in different languages...

I always had the Visibility on, I just forgot to add this part of my skript to the question. And I didn't set immediately after the actxserver line. But I hope the excel.Quit will help me with any bugs.

I still got some questions left:

1. In Range('$B$2:$B$16') I select the Y-Values and it automaticly takes the first column of the skript as the X-Values. How can I for example choose the 2nd column as the X-Values of my chart?
2. Do you know a simple way to slesect the Y-Value-Data like this: Range('$B$1:$B$end') (determine the size of the column) wihtout using ActiveX/COM or xlsread?

[Guillaume]

"In Range('$B$2:$B$16') I select the Y-Values and it automaticly takes the first column of the skript as the X-Values"

No, it doesn't. At the moment, your chart is a bar chart which does not even have x-values. You'd need a XY chart (scatter plot) for that. The bar chart uses labels for the x axis, which by default is 1 to number of points. That this matches your first column is a coincidence.

You can change the labels of your chart by iterating over the Series in the Chart's SeriesCollection and changing either their XValues or their Formula:

for sidx = 1 : chart.SeriesCollection.Count  %iterate over the series
  s = chart.SeriesCollection(sidx);
  s.XValues = '=Tabelle1!$C$2:$C$37';  %for example
  %or
  s.Formula = "=Series(somename, xlabels, yrange)";
end

"Do you know a simple way to slesect the Y-Value-Data like this"

No. Other than letting excel autodetermine the range by selecting the whole column with Range('$B:$B'), I don't think there is a way. You either have to iterate over the rows and find the first empty row, or play around with Selection objects (let the selection object auto select the end of the range and query where that end is). Last option is the UsedRange property of the Worksheet, but that one, for some reason, is not always correct.

[Kilian Weber]

So |s.XValues = '=Tabelle1!$B:$B';|should choose column B as XValue? Well as far as I'm conserned it still does the 1,2,3,4... thing. Here my code so far for better understanding:

%%%%%general Code to insert a Chart in Excel using Matlab %%%%%
%%start Excel and open Workbook
excel = actxserver('Excel.Application');
wb = excel.Workbooks.Open('D:\Documents\Studium\Praxissemester\kleine Autoclave\Matlab\Test999.xlsx');
%%makes the created sheet visible
excel.Visible = true;
%%add Chart
charts = wb.Charts;
chart = charts.Add;
%%select XValues
for sidx = 1 : chart.SeriesCollection.Count  %iterate over the series
  s = chart.SeriesCollection(sidx);
  s.XValues = '=Tabelle1!$C:$C';  %for example
  %or
%   s.Formula = "=Series(somename, xlabels, yrange)";
end
%%select YValues: Get Data from the nth worksheet from the coulmns and rows in range
n = 1;
startpos = '$A'; % set it '1' or '$C'  to take the legend Line Title from table
endpos = '$A'; % set for example'$C$45' if you don't want the whole column
range = [startpos ':' endpos];
chart.SetSourceData(wb.Worksheets.Item(n).Range(range));
clear n range;
%%Name chart sheet
chart.Name = 'TestChart';
%%Set chart title,  see https://msdn.microsoft.com/en-us/library/office/ff196832.aspx
chart.HasTitle = true;
chart.ChartTitle.Text = 'Test Title';
%%Set chart types,  see https://msdn.microsoft.com/en-us/library/office/ff837417.aspx
chart.ChartType = 'xlXYScatterSmooth';
%%Set chart legend, see https://msdn.microsoft.com/en-us/library/office/ff821884.aspx
Chart.HasLegend = true;
%%Set Axes Titles
chart.Axes(1).HasTitle = true;
chart.Axes(1).AxisTitle.Text = 'Time [s]'; % XAxes
chart.Axes(2).HasTitle = true;
chart.Axes(2).AxisTitle.Text = 'Temperature[°C]'; %YAxes
% excel.Quit % activate when you need autimatic closing

Did I misunderstand something?

Range('$C:$C') works just fine for my needs so far, thank you

[Guillaume]

"Did I misunderstand something?"

Well, yes, you set the XValues (and other properties of the Series) after you've set the SourceData of the chart. Otherwise, you're just editing the default series that the chart is created with and that are discarded as soon as you set the source data.

Unfortunately, you can't use '$C:$C' as a range as excel will include the header as part of the xdata. One possible way:

%%%%%general Code to insert a Chart in Excel using Matlab %%%%%
%%start Excel and open Workbook
excel = actxserver('Excel.Application');
wb = excel.Workbooks.Open('D:\Documents\Studium\Praxissemester\kleine Autoclave\Matlab\Test999.xlsx');
%%makes the created sheet visible
excel.Visible = true;
%%add Chart
chart = wb.Charts.Add;
chart.ChartType = 'xlXYScatterSmooth'
%%set source data 
chart.SetSourceData(wb.Worksheets.Item(1).Range('$A:$A'));  
series = chart.SeriesCollection(1);
yrange = regexp(series.Formula, '(?<==SERIES\([^,]*,[^,]*,)[^,]+', 'match', 'once');
series.XValues = regexprep(yrange, '\$A', '\$C');
%rest of your code
%WATCH OUT FOR CASE, Chart and chart are not the same!
%The line
Chart.HasLegend = true;
%creates a structure instead of updating the chart

It would be much easier if your xvalues were before the yvalues in your spreadsheet since you could specify the range in SetSourceData:

chart.SetSourceData(wb.Worksheets.Item(1).Range('$A:$A,$C:$C')

But if you want column C as the X values you do have to go through the series object.

[Kilian Weber]

Thanks, could you explain your code, so I can understand what it does?

series = chart.SeriesCollection(1);
yrange = regexp(series.Formula, '(?<==SERIES\([^,]*,[^,]*,)[^,]+', 'match', 'once');
series.XValues = regexprep(yrange, '\$A', '\$C');

Also: How do I plot multible lines (from multible columns) in this version? And where to set X- and YValues? Isn't there a simple solution where I can input something like this:

% set source data for X and Y Values
% XValue
sheet = 1;
Column = B; %(or 2)
% YValues
sheet = 1;
Column = B:P, R , T:Z; %(or 2:16,18,20:24) 
%%%rest of the code

xvalues are always before the yvalues in my case, so?

[Kilian Weber]

I don't know if I'm allowed to share this data, but I want to shoe you what I'm dealing with: down here you can find something very close to the Excel File, that will be used later

ExcelFile on Google Drive

(I wasn't able to uploud it here due to my bad internet connection)

I only want to use sheet 1 (Tabelle 1)

time [h] -column is the XValue in the first chart I want to have column H - P as YValues in one Chart int he second only Q

always use every row and the header as Name

[Guillaume]

If your x column is before the y columns then you can set the range with SetSourceData:

excel = actxserver('Excel.Application');
excel.Visible = true;  %optional
wb = excel.Workbooks.Open('...')
chart1 = wb.Charts.Add;
chart1.ChartType = 'xlXYScatterSmooth';
chart1.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $H:$P'));
%...

chart2 = wb.Charts.Add([], chart1); %place after chart1
chart2.ChartType = 'xlXYScatterSmooth';
chart2.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $Q:$Q'));
%...

wb.SaveAs(...)
excel.Quit;

[Kilian Weber]

This looks perfect, but still... I get that Error:

Error using Interface.000208D8_0000_0000_C000_000000000046/Range
Error: Object returned error code: 0x800A03EC

Error in CodeTestmy (line 13)
chart1.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $H:$P'));

Did you try it out and recieve the same error?

[Guillaume]

Yes, I did try the exact code above, and again today, received no error and got the correct graph.

So I'm not sure what is happening for you. The error tells you the range is not valid. Maybe try:

sheet = wb.Worksheets.Item('Tabelle1');
rg1 = sheet.Range('$B:$B');
rg2 = sheet.Range('$H:$P');
rg = excel.Union(rg1, rg2);

If it doesn't work which line is causing the error?

[Kilian Weber]

Here my full code, maybe I made a mistake above:

%%%%%general Code to insert a Chart in Excel using Matlab %%%%%
%%start Excel and open Workbook
excel = actxserver('Excel.Application');

wb = excel.Workbooks.Open('C:\...\AutoKalTest4hhh.xlsx');

%%makes the created sheet visible
excel.Visible = true;

%%add 1. Chart
chart1 = wb.Charts.Add;
%%set source data 
chart1.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $H:$P'));

%%Name chart sheet
chart1.Name = 'Chart1';
%%Set chart title,  see https://msdn.microsoft.com/en-us/library/office/ff196832.aspx
chart1.HasTitle = true;
chart1.ChartTitle.Text = 'Test Title';
%%Set chart types,  see https://msdn.microsoft.com/en-us/library/office/ff837417.aspx
chart1.ChartType = 'xlXYScatterSmoothNoMarkers';
%%Set chart legend, see https://msdn.microsoft.com/en-us/library/office/ff821884.aspx
chart1.HasLegend = true;
%%Set Axes Titles
chart1.Axes(1).HasTitle = true;
chart1.Axes(1).AxisTitle.Text = 'Time [s]'; % XAxes
chart1.Axes(2).HasTitle = true;
chart1.Axes(2).AxisTitle.Text = 'Temperature[°C]'; %YAxes

The error occured in line 13 chart1.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $H:$P')); as already mentioned above...

Here again my .m file and my .xlsx file exactly the ones I use.

I also tried your new version this way:

sheet = wb.Worksheets.Item('Tabelle1');
rg1 = sheet.Range('$B:$B');
rg2 = sheet.Range('$H:$P');
rg = excel.Union(rg1, rg2);
chart1.SetSourceData(sheet.rg);

but the Error:

Undefined function or variable 'rg'.

Error in CodeTestmy2 (line 19)
chart1.SetSourceData(sheet.rg);

occured

[Guillaume]

Sorry, typo should hve been:

chart1.SetSourceData(rg);

Good news is that creating the range succeeded if you got this error.

Your code above works for me on the spreadsheet you posted a while ago. Your m file and new xlsx file are private. I can't access them

[Kilian Weber]

I got them public now: .xlxs file and .m file

Now I get another error:

Error using Interface.000208D6_0000_0000_C000_000000000046/SetSourceData
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Die maximale Anzahl an Datenreihen pro Diagramm ist 255.
Help File: xlmain11.chm
Help Context ID: 0

Error in CodeTestmy2 (line 19)
chart1.SetSourceData(rg);

Die maximale Anzahl an Datenreihen pro Diagramm ist 255. is german for the maximum of data rows is 255