Using Matlab to create Excel charts with X and Y values from range

Question

I have about 20 columns of data, each ~20,000 rows. All calculations are done in Matlab, and then charts are created.

Unfortunately, I need the whole output to be in an Excel File, including several editable charts. Using xlswrite I created the Excel file, now I am struggling to create the charts the way I want.

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

 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 

The error:

Error using COM.Excel_Application/Range. Object returned error code: 0x800A03EC"

I have not been able to find any solutions for adding charts through the ActiveX object in Matlab. Any posts I have found on this topic were outdated or unhelpful.

---

Summary, my question is:

• How can I insert charts in Excel using Matlab (using a general, up to date ActiveX code structure).
• How can I select the columns for the XValue and the YValues (Range)
• How can I get access to chart titles, axes, line appearance and legend

---

I'm using Excel 2016 and Matlab R2017a.

---

Edit:

during this week I developed a own solution, that still doesn't work perfectly but is close to what I want. Could you have a look on this code too please:

Important is only chart1. I wanted to set the XValue to column B and the YValues to column H - P of the sheet1 (Tabelle1).

%%%%% general Code to insert a Chart in Excel using Matlab %%%%%
%% start Excel and open Workbook
excel = actxserver('Excel.Application');
wb = excel.Workbooks.Open('C:\...\Test.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')); % 'Tabelle1' is the german equal to sheet1, my excel is german
%% Name chart sheet
chart1.Name = '1. TestChart';
%% 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
%% add 2nd chart
chart2 = wb.Charts.Add([], chart1); %place after chart1
chart2.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $Q:$Q'));
% ... same procedure as above

%% use to quit all open Excel processes
% excel.Quit;

another Error occurs:

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

---

for now this question is nearly answered. For further bug related answers have a look at a related question

Answer 1

As I suggested in the comments, a better documented method for doing this would be to use VBA. As it happens, the VBA documentation can actually be used to take most of the guesswork out of interacting with the COM object directly from Matlab, as the syntax is similar.

Here is some updated Matlab code which achieves your three points. I have included links to the MSDN documentation where appropriate:

% Start Excel and open workbook
Excel = actxserver('Excel.Application');  
WB = Excel.Workbooks.Open('C:\...\test.xlsx');
% Show the workbook
Excel.visible = 1;
% Add chart
Chart = invoke(WB.Charts,'Add');
% Get Sheet object
SheetObj = Excel.Worksheets.get('Item', 'Sheet1');
% Name chart sheet
Chart.Name = 'TestChart';

% Set source data range of chart
% X and Y data can also be set to Matlab arrays, by Srs.Values and Srs.XValues, ensuring equal length
  % Y data
Srs = Chart.SeriesCollection.Add(SheetObj.Range('B2:B16'));
  % X data, could be a Matlab array of correct length
Srs.XValues = SheetObj.Range('A2:A16');
  % Series name
Srs.Name = 'Test Series';

% For chart types,  see https://msdn.microsoft.com/en-us/library/office/ff837417.aspx
Chart.ChartType = 'xlXYScatterSmooth'; 
% Set chart title,  see https://msdn.microsoft.com/en-us/library/office/ff196832.aspx
Chart.HasTitle = true;
Chart.ChartTitle.Text = 'Test Title';
% Set chart legend, see https://msdn.microsoft.com/en-us/library/office/ff821884.aspx
Chart.HasLegend = true;

---

Your error:

You were encountering an error because you were trying to access the Range object of the Excel application. This doesn't exist! All Range objects belong to a Sheet object, which is what I retrieve first in the above code.

---

Creating many series:

You say you have many columns of data, here is a way to include them through a loop. It also finds the last used row in each column.

% ... CREATE WORKBOOK / CHART AS BEFORE ...
%
Chart.Name = 'TestChart';

% Set source data range of chart, do X and Y data for each series    
columns = 2:4;
colnames = {'xdata', 'my series 1', 'my series 2', 'my series 3'};
for col = columns
      % Get Excel column *letter* from column *number*
    colchar = strrep([char(96+floor((col-1)/26)) char(97+rem(col-1,26))],char(96),'');
      % Last row of data, see https://msdn.microsoft.com/en-us/library/office/ff839539.aspx
      % Data must be contiguous (no gaps / blank cells)
    lastrow = num2str(SheetObj.Range([colchar, '2']).End('xlDown').Row);
      % Y data, creating range strings by concatenation of column character and row number
    Srs = Chart.SeriesCollection.Add(SheetObj.Range([colchar, '2:', colchar, lastrow]));
      % X data, same approach is used for last row, but only column = 1 = "A" 
    Srs.XValues = SheetObj.Range(['A2:A', lastrow]);
     % Set column name, to use the first row do
     % Srs.Name = SheetObj.Range([colchar, '1']);
    Srs.Name = colnames{col};
end

Chart.ChartType = 'xlXYScatterSmooth'; 
%
% ... TITLE / LEGEND AS BEFORE ...

Output chart sheet and input "Sheet1":

---

Edit:

In the above, I loop over the column numbers to populate the y data. If you know the column letter then you can just loop over colchar instead of creating it.

% There are shortcuts to creating a cell array of consecutive letters, 
% Like columnletters = cellstr(('H':'J')');
for colchar = {'H', 'I', 'J'};
    % ... same as the above for loop, but don't need to get colchar from col
    lastrow = num2str(SheetObj.Range([colchar, '2']).End('xlDown').Row);
    Srs = Chart.SeriesCollection.Add(SheetObj.Range([colchar, '2:', colchar, lastrow]));
    Srs.XValues = SheetObj.Range(['A2:A', lastrow]);
    Srs.Name = SheetObj.Range([colchar, '1']);

end