How to insert a Chart in Excel through Matlab

79 views (last 30 days)
Kilian Weber
Kilian Weber on 13 Jun 2017
Commented: Kilian Weber on 19 Jun 2017
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.

Answers (1)

Guillaume
Guillaume on 13 Jun 2017
Edited: Guillaume on 13 Jun 2017
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.
  16 Comments
Kilian Weber
Kilian Weber on 19 Jun 2017
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

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!