How can I add multiple charts in excel with activex

I've been writing a script that needs to add up to a hundred or so excel charts in one worksheet... I'm using activex to access excel through matlab and I'm using the Excel.ActiveSheet.ChartObjects.Add(Left,Top,Width,Height) function. One problem though, it won't seem to let me add sheets when either the 'left' or 'top' position is greater than a certain number, around 250-350. Is there anyway to fix this?

Many thanks, Mirage

[Eric]

I don't seem to have this problem. Here's what I tried:

xlsobj = actxserver('Excel.Application');
xlsobj.Visible = 1;
WB_COM = xlsobj.Workbooks.Add
Sheet = WB_COM.Sheets.Item(1);
chart1 = Sheet.ChartObjects.Add(100,100,200,200);
chart2 = Sheet.ChartObjects.Add(100,300,200,200);
chart3 = Sheet.ChartObjects.Add(100,900,200,200);
chart4 = Sheet.ChartObjects.Add(900,900,200,200);

All four of these charts were created on the desired worksheet. I'm using Excel 2010 on Windows 7 x64 with Matlab R2012b.

Sorry I can't be of more help.

-Eric

[Mirage]

Odd... I tried this as well and it works.. but it still won't work in my code!! I'll show you the code... it's a bit long.

function [LinSlope PolySlope LinSlope_Str PolySlope_Str] = xlsgraphchart(Excel,XseriesRange,YseriesRange,X_Axis_Title,Y_Axis_Title,Left,Top,Width,Height,PageIdx)

This function creates a graph in excel and plots a linear and polynomial trendline

[LinSlope PolySlope LinSlope_Str PolySlope_Str] = xlsgraphchart(Excel,XseriesRange,YseriesRange,X_Axis_Title,Y_Axis_Title,L eft,Top,Width,Height,PageIdx)

Input argument: Excel = Active Excel COM created through activex XseriesRange = The range of cells to be graphed for the X-axis YseriesRange = The range of cells to be graphed for the Y-axis X_Axis_Title = Implied in name Y_Axis_Title = Also implied in name [Left Top Width Height] = Position and size of the chart PageIdx = Worksheet to be graphed

Output arguments: LinSlope = Returns the slope of the linear trendline PolySlope = Returns the slope of the polynomial trendline LinSlope_Str = Returns the string value of the linear trendline PolySlope_Str = Returns the string value of the polynomial trendline

ExcelWs = Excel.Workbooks;

XLSheet = Excel.ActiveWorkBook.Sheets;
GetXLSheet = get(XLSheet, 'Item', PageIdx);
invoke(GetXLSheet,'Activate');
XLActive = Excel.ActiveSheet;
Excel.Visible = 0;

XLChartObject = XLActive.ChartObjects.Add(Left,Top,Width,Height);

XLChart = XLChartObject.Chart;
XLChart.SeriesCollection.NewSeries;

XLChart.SeriesCollection(1).Value = XLActive.Range(YseriesRange);
XLChart.SeriesCollection(1).XValue = XLActive.Range(XseriesRange);

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

XLChartObject.Chart.HasTitle = true;
XLChartObject.Chart.ChartTitle.Text = [X_Axis_Title ' Vs. ' Y_Axis_Title]; % view it again

%Set X-axis Title
ChartAxes = invoke(XLChartObject.Chart,'Axes',1);
set(ChartAxes,'HasTitle',1);
set(ChartAxes.AxisTitle,'Caption',X_Axis_Title);

%Set Y-axis Title
ChartAxes = invoke(XLChartObject.Chart,'Axes',2);
set(ChartAxes,'HasTitle',1);
set(ChartAxes.AxisTitle,'Caption',Y_Axis_Title);

%%Starts adding trendlines
% Linear trendline

Type_Linear = -4132;
Type_Poly   = 3;
% Type_Power  = 4;
% Type_Exp    = 5;

% Adds trendline
XLChart.SeriesCollection(1).Trendlines.Add(Type_Linear);
% Displays R squared equation
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 1;
% Reads R squared string from chart
Lin_R_Squared = XLChart.SeriesCollection(1).Trendlines(1).DataLabel.Text;
% Removes R squared equation or else when the y equation is collected
% later, the string will return both the r and y equation
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 0;
% Displays y equation
XLChart.SeriesCollection(1).Trendlines(1).DisplayEquation = 1;
% Reads y equation string from chart
Lin_y_Eq = XLChart.SeriesCollection(1).Trendlines(1).DataLabel.Text;
% Keeps the linear trendline on the chart
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 1;
% Since the equation is returned as a string rather than in integer value,
% regexp is used to distinguish patterns and extract only the information
% needed. In this case if the string is 'R = 0.423', we only want 0.423 or
% if 'y = -0.98x3 + 89E-03x2 + x + 42', we only want [-0.98, 89E-03, 1, 42]
Lin_R_squared_Cell = regexp(Lin_R_Squared,'[+-eE\d*\x\.]+','match');
Lin_y_Eq_Cell = regexp(Lin_y_Eq,'[+-eE\d*\x\.]+','match');
% Converts the resulting array of strings to integers
Lin_R_squared_Num = str2num(Lin_R_squared_Cell{length(Lin_R_squared_Cell)});

% The regexp used returns the integer value in every second position within
% the array
NewLength = int8(length(Lin_y_Eq_Cell)/2);
Temp = Lin_y_Eq_Cell;
Lin_y_Eq_Cell = cell(1,NewLength);
t=2;
for i=1:NewLength
    Lin_y_Eq_Cell{i} = Temp{t};
    t=t+2;
end

% The following if statement is used to make sure an equation is fully
% complete after extracted from regexp (e.g. if there is an equation such
% as 'y = 2x2 + 1', there is no x1 value but regexp only returns [2 1].
% However, it should be [2 0 1] so when the derivative is done later, the
% derivative is 2 rather than 1.
if ~isempty(strfind(Lin_y_Eq_Cell{1},'x'))
    [xx yy] = find(Lin_y_Eq_Cell{1} == 'x');
    if NewLength < 2
        ExpMatch = [1;2];
        Lin_y_Eq_Cell_Temp = Lin_y_Eq_Cell;
        Lin_y_Eq_Cell = cell(1,length(ExpMatch));
        for i=1:length(ExpMatch)
            Lin_y_Eq_Cell{i} = '0';
        end
        Lin_y_Eq_Cell{1} = Lin_y_Eq_Cell_Temp{1};
    end
end

% if an equation results in 'y = 2x2 + x +1' then the 'x' value has no
% coefficient ahead of it. The following for loop detects if there is a
% single x by itself and replaced by a 1
for s=1:length(Lin_y_Eq_Cell)
    if ~isempty(strfind(Lin_y_Eq_Cell{s},'x'))
        if strcmpi(Lin_y_Eq_Cell{s}(1),'x')
            Lin_y_Eq_Cell{s} = '1';
        end
    end
end

Lin_y_Eq_Cell_Final = cell(1,length(Lin_y_Eq_Cell));
for s=1:length(Lin_y_Eq_Cell)
    if ~isempty(strfind(Lin_y_Eq_Cell{s},'x'))
        [xx yy] = find(Lin_y_Eq_Cell{s} == 'x');
        Lin_y_Eq_Cell_Final{s} = Lin_y_Eq_Cell{s}(1:yy-1);
    else
        Lin_y_Eq_Cell_Final{s} = Lin_y_Eq_Cell{s};
    end
end

%%Polynomial 1
% View the linear comments for a detailed overview

XLChart.SeriesCollection(1).Trendlines.Add(Type_Poly,2);
XLChart.SeriesCollection(1).Trendlines(2).DisplayRSquared = 1;
Poly1_R_Squared = XLChart.SeriesCollection(1).Trendlines(2).DataLabel.text;
XLChart.SeriesCollection(1).Trendlines(2).DisplayRSquared = 0;
XLChart.SeriesCollection(1).Trendlines(2).DisplayEquation = 1;
Poly1_y_Eq = XLChart.SeriesCollection(1).Trendlines(2).DataLabel.text;
XLChart.SeriesCollection(1).Trendlines(2).DisplayEquation = 0;
Poly1_R_squared_Cell = regexp(Poly1_R_Squared,'[+-eE\d*\x\.]+','match');
Poly1_y_Eq_Cell = regexp(Poly1_y_Eq,'[+-eE\d*\x\.]+','match');
Poly1_R_squared_Num = str2num(Poly1_R_squared_Cell{length(Poly1_R_squared_Cell)});

NewLength = int8(length(Poly1_y_Eq_Cell)/2);
Temp = Poly1_y_Eq_Cell;
Poly1_y_Eq_Cell = cell(1,NewLength);
t=2;
for i=1:NewLength
    Poly1_y_Eq_Cell{i} = Temp{t};
    t=t+2;
end

% e.g. if y = x2 + 5, the derivative will be 1 because it is missing the
% x^1 term, therefore the coefficient for the x^1 term must be appended
% even though it is 0
if ~isempty(strfind(Poly1_y_Eq_Cell{1},'x'))
    [xx yy] = find(Poly1_y_Eq_Cell{1} == 'x');
    if (str2num(Poly1_y_Eq_Cell{1}(yy+1))+1) > NewLength
        ExpMatch = [1:str2num(Poly1_y_Eq_Cell{1}(yy+1))+1];
        Poly1_y_Eq_Cell_Temp = Poly1_y_Eq_Cell;
        Poly1_y_Eq_Cell = cell(1,length(ExpMatch));
        for i=1:length(ExpMatch)
            Poly1_y_Eq_Cell{i} = '0';
        end
        for i=1:length(Poly1_y_Eq_Cell_Temp)
            if ~isempty(strfind(Poly1_y_Eq_Cell_Temp{i},'x'))
                [xx yy] = find(Poly1_y_Eq_Cell_Temp{i} == 'x');
                PosIdx = (str2num(Poly1_y_Eq_Cell_Temp{i}(yy+1)));
                if length(Poly1_y_Eq_Cell_Temp{i}) == yy;
                    Poly1_y_Eq_Cell{length(Poly1_y_Eq_Cell)-1} = Poly1_y_Eq_Cell_Temp{i};
                else
                    Poly1_y_Eq_Cell{length(Poly1_y_Eq_Cell)-(PosIdx)} = Poly1_y_Eq_Cell_Temp{i};
                end
            else
                Poly1_y_Eq_Cell{length(Poly1_y_Eq_Cell)} = Poly1_y_Eq_Cell_Temp{length(Poly1_y_Eq_Cell_Temp)};
            end
        end
    end

end

%
Poly1_y_Eq_Cell_Final = cell(1,length(Poly1_y_Eq_Cell));
for s=1:length(Poly1_y_Eq_Cell)
    if ~isempty(strfind(Poly1_y_Eq_Cell{s},'x'))
        if strcmpi(Poly1_y_Eq_Cell{s}(1),'x')
            Poly1_y_Eq_Cell_Final{s} = '1';
        else
            [xx yy] = find(Poly1_y_Eq_Cell{s} == 'x');
            Poly1_y_Eq_Cell_Final{s} = Poly1_y_Eq_Cell{s}(1:yy-1);
        end
    else
        Poly1_y_Eq_Cell_Final{s} = Poly1_y_Eq_Cell{s};
    end
end

%%Polynomial 2

XLChart.SeriesCollection(1).Trendlines.Add(Type_Poly,3);
XLChart.SeriesCollection(1).Trendlines(3).DisplayRSquared = 1;
Poly2_R_Squared = XLChart.SeriesCollection(1).Trendlines(3).DataLabel.text;
XLChart.SeriesCollection(1).Trendlines(3).DisplayRSquared = 0;
XLChart.SeriesCollection(1).Trendlines(3).DisplayEquation = 1;
Poly2_y_Eq = XLChart.SeriesCollection(1).Trendlines(3).DataLabel.text;
XLChart.SeriesCollection(1).Trendlines(3).DisplayEquation = 0;
Poly2_R_squared_Cell = regexp(Poly2_R_Squared,'[+-eE\d*\x\.]+','match');
Poly2_y_Eq_Cell = regexp(Poly2_y_Eq,'[+-eE\d*\x\.]+','match');
Poly2_R_squared_Num = str2num(Poly2_R_squared_Cell{length(Poly2_R_squared_Cell)});

% Gets rid of '=' sign from the first term of the equation in the cell and
% replaces the new value into the same position
NewLength = int8(length(Poly2_y_Eq_Cell)/2);
Temp = Poly2_y_Eq_Cell;
Poly2_y_Eq_Cell = cell(1,NewLength);
t=2;
for i=1:NewLength
    Poly2_y_Eq_Cell{i} = Temp{t};
    t=t+2;
end

if ~isempty(strfind(Poly2_y_Eq_Cell{1},'x'))
    [xx yy] = find(Poly2_y_Eq_Cell{1} == 'x');
    if (str2num(Poly2_y_Eq_Cell{1}(yy+1))+1) > NewLength
        ExpMatch = [1:str2num(Poly2_y_Eq_Cell{1}(yy+1))+1];
        Poly2_y_Eq_Cell_Temp = Poly2_y_Eq_Cell;
        Poly2_y_Eq_Cell = cell(1,length(ExpMatch));
        for i=1:length(ExpMatch)
            Poly2_y_Eq_Cell{i} = '0';
        end
        for i=1:length(Poly2_y_Eq_Cell_Temp)
            if ~isempty(strfind(Poly2_y_Eq_Cell_Temp{i},'x'))
                [xx yy] = find(Poly2_y_Eq_Cell_Temp{i} == 'x');
                PosIdx = (str2num(Poly2_y_Eq_Cell_Temp{i}(yy+1)));
                if length(Poly2_y_Eq_Cell_Temp{i}) == yy;
                    Poly2_y_Eq_Cell{length(Poly2_y_Eq_Cell)-1} = Poly2_y_Eq_Cell_Temp{i};
                else
                    Poly2_y_Eq_Cell{length(Poly2_y_Eq_Cell)-(PosIdx)} = Poly2_y_Eq_Cell_Temp{i};
                end
            else
                Poly2_y_Eq_Cell{length(Poly2_y_Eq_Cell)} = Poly2_y_Eq_Cell_Temp{length(Poly2_y_Eq_Cell_Temp)};
            end
        end
    end
end

Poly2_y_Eq_Cell_Final = cell(1,length(Poly2_y_Eq_Cell));
for s=1:length(Poly2_y_Eq_Cell)
    if ~isempty(strfind(Poly2_y_Eq_Cell{s},'x'))
        if strcmpi(Poly2_y_Eq_Cell{s}(1),'x')
            Poly2_y_Eq_Cell_Final{s} = '1';
        else
            [xx yy] = find(Poly2_y_Eq_Cell{s} == 'x');
            Poly2_y_Eq_Cell_Final{s} = Poly2_y_Eq_Cell{s}(1:yy-1);
        end
    else
        Poly2_y_Eq_Cell_Final{s} = Poly2_y_Eq_Cell{s};
    end
end
% Chooses the best fit between polynomial 1 and polynomial 2 and returns
% that y equation and also leaves the equation visible on the chart
if Poly2_R_squared_Num <= Poly1_R_squared_Num
    XLChart.SeriesCollection(1).Trendlines(2).DisplayRSquared = 1;
    XLChart.SeriesCollection(1).Trendlines(2).DisplayEquation = 1;
    Poly_y_Eq_Cell = cell(1,length(Poly1_y_Eq_Cell));
    Poly_y_Eq_Cell = Poly1_y_Eq_Cell_Final;
    Poly_R_Squared = Poly1_R_squared_Cell;
else
    XLChart.SeriesCollection(1).Trendlines(3).DisplayRSquared = 1;
    XLChart.SeriesCollection(1).Trendlines(3).DisplayEquation = 1;
    Poly_y_Eq_Cell = cell(1,length(Poly2_y_Eq_Cell));
    Poly_y_Eq_Cell = Poly2_y_Eq_Cell_Final;
    Poly_R_Squared = Poly2_R_squared_Cell;
end

Equation = zeros(1,length(Poly_y_Eq_Cell));
for i=1:length(Poly_y_Eq_Cell)
    Equation(1,i) = str2num(Poly_y_Eq_Cell{i});
end
% Returns the derivative of a given array (e.g. polyder([5 4 3 2]) = [15 8
% 3] since y = 5x3 + 4x2 + 3x1 + 2x0 => dy/dx = 15x2 + 8x + 3
PolySlope = polyder(Equation);

Equation = zeros(1,length(Lin_y_Eq_Cell_Final));
for i=1:length(Lin_y_Eq_Cell_Final)
    Equation(1,i) = str2num(Lin_y_Eq_Cell_Final{1});
end
LinSlope = polyder(Equation);

% Creates strings for the polynomial and linear equations (e.g. a [4 5 -6]
% vector would create '4x2 + 5x1 - 6x0'
Poly_Power = length(PolySlope)-1;
PolySlope_Str = '';

for i=1:length(PolySlope)
if true
    % code
  end     PolySlope_Temp = [num2str(PolySlope(i)) 'x' num2str(Poly_Power)];
    if sign(PolySlope(i)) == -1
        PolySlope_Str = [PolySlope_Str ' ' PolySlope_Temp];
    else
        PolySlope_Str = [PolySlope_Str ' + ' PolySlope_Temp];
    end
    Poly_Power = Poly_Power-1;
end

Lin_Power = length(LinSlope)-1;
LinSlope_Str = '';

for i=1:length(LinSlope)
    LinSlope_Temp = [num2str(LinSlope(i)) 'x' num2str(Lin_Power)];
    if sign(LinSlope(i)) == -1
        LinSlope_Str = [LinSlope_Str ' ' LinSlope_Temp];
    else
        LinSlope_Str = [LinSlope_Str ' + ' LinSlope_Temp];
    end
    Lin_Power = Lin_Power-1;
end

[Eric]

I wonder if the problem is in how you're calling this function. I added the following code at the top of your function to test what you've written. Here's what I added:

 Excel = actxserver('Excel.Application');
 Excel.Visible = 1;
 Excel.Workbooks.Add;
 PageIdx = 1;
 Left = 1000;
 Top = 1000;
 Width = 100;
 Height = 100;

I then ran your code below and it works fine:

ExcelWs = Excel.Workbooks;

 XLSheet = Excel.ActiveWorkBook.Sheets;
 GetXLSheet = get(XLSheet, 'Item', PageIdx);
 invoke(GetXLSheet,'Activate');
 XLActive = Excel.ActiveSheet;
 Excel.Visible = 0;

 XLChartObject = XLActive.ChartObjects.Add(Left,Top,Width,Height);

I did set Excel.Visible to 1 after running this code to check that the graph was created correctly (and it was).

This makes me think that perhaps your problem is in the calling of this function rather than this function itself. Perhaps you're not passing in the values for Left and Top that you think you're passing in?

As an aside, I always tell people to be careful when using things like ActiveSheet, ActiveWorkBook, etc. If the user does anything to Excel that changes what is "active" your code will run into problems (i.e., start making changes to the wrong sheet or workbook).

Good luck,

Eric

[Mirage]

Hey Eric,

First of all thanks for all the help so far... after running that code bits at a time, I've realized it's not XLChartObject = XLActive.ChartObjects.Add(Left,Top,Width,Height); that is causing the problem... it is

XLChart.SeriesCollection(1).Trendlines.Add(Type_Linear);
% Displays R squared equation
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 1;
% Reads R squared string from chart
Lin_R_Squared = XLChart.SeriesCollection(1).Trendlines(1).DataLabel.Text;
% Removes R squared equation or else when the y equation is collected
% later, the string will return both the r and y equation
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 0;
% Displays y equation
XLChart.SeriesCollection(1).Trendlines(1).DisplayEquation = 1;
% Reads y equation string from chart
Lin_y_Eq = XLChart.SeriesCollection(1).Trendlines(1).DataLabel.Text;
% Keeps the linear trendline on the chart
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 1;

For some reason, Lin_y_Eq and Lin_R_Squared return the correct strings when I run XLChartObject = XLActive.ChartObjects.Add(Left,Top,Width,Height); with left and top are below ~3-400. But, say you run it at 10000, it returns empty strings and then it crashes because regexp can't find anything in the string. Do you know why these strings are returning empty values? Try running the full code with Top = 10, Left = 10 and then Top = 10000, Left = 10. Thanks.

Mirage

[Eric]

I created a dummy test spreadsheet to test this out. My usage code is:

Excel = actxserver('Excel.Application');
WB = Excel.Workbooks.Open(fullfile(pwd,'xlsgraphchart.xlsx'));
LinSlope, PolySlope, LinSlope_Str, PolySlope_Str] = xlsgraphchart(Excel, 'A1:A10','B1:B10','X','Y',10000,10000,1000,1000,1);

For the Lin_y_Eq and Lin_R_Squared strings I get:

K>> Lin_y_Eq

Lin_y_Eq =

y = 3x + 2

K>> Lin_R_Squared

Lin_R_Squared =

R² = 1

These are each correct. I've now looked a bit further at what you're doing. I would probably actually do the fitting in Matlab rather than Excel. Alternatively, you might be able to use the COM interface to Excel to perform the calculations using Excel functions rather than reading values off of the plot strings. That might simplify things. I'll look at this a bit more.

-Eric

[Mirage]

So I feel like I've pretty much narrowed it down to

Lin_R_Squared = XLChart.SeriesCollection(1).Trendlines(1).DataLabel.Text;

or more specifically the Datalabel.text object that is giving me problems.

[Eric]

I wonder if Matlab or Excel is choking on the superscript "2" character somehow. I was surprised to see that print out.

You can see my answer for how to easily calculate R^2 for the linear fit without the need for reading from the trendline label. The polynomial fit is a bit trickier.

-Eric

Answer 1

[Eric]

Here's how you can get the linear fit parameters (in Excel) without reading text strings from the trendline labels:

K>> V = Excel.WorksheetFunction.LinEst(GetXLSheet.Range(YseriesRange),GetXLSheet.Range(XseriesRange),true,true)

 V = 

    [    3.0000]    [    2.0000]
    [9.8546e-17]    [6.1146e-16]
    [         1]    [8.9509e-16]
    [9.2675e+32]    [         8]
    [  742.5000]    [6.4095e-30]

The slope is the (1,1) element, the offset is the (1,2) term, and the R^2 value is the (3,1) term. For my case the equation is y = 3x+2 and the R^2 is 1.

You actually can use linest() to perform polynomial fits as well, but you would probably need to have the x^2, x^3, etc. data in the worksheet as well. You might be able to create VBA arrays of these values somehow and use them, but I'm not sure.

Alternatively, you can read the values directly into Matlab and perform calculations there. You can use

Yvals = cell2mat(GetXLSheet.Range(YseriesRange).Value);
Xvals = cell2mat(GetXLSheet.Range(XseriesRange).Value);

You can then use Matlab's polyfit to perform the fitting for you. To calculate the R^2 parameter, see http://en.wikipedia.org/wiki/Coefficient_of_determination. The following code implements this. In this case y is the data vector and fit_vec is a vector of fit values.

ybar = mean(y); %Mean of the data
sst = sum( (y - ybar).^2 ); %Total sum of squares
gof.sse = sum( (y - fit_vec).^2 ); %Residual sum of squares
if sst ~= 0
  gof.rsquare = 1 - gof.sse/sst;
else
  gof.rsquare = NaN;
end
gof.rmse = sqrt(mean((fit_vec-y).^2));

You could then also use Matlab to create the equation strings for you as well. You could borrow code from the disp() function from http://www.mathworks.com/help/matlab/matlab_oop/a-polynomial-class.html to do that.

To summarize, I would say the best solution is to use the existing code you've got for creating the plots. This seems to work well. Then read the data into Matlab and calculate fit parameters there.

-Eric

[Mirage]

Thanks!! Still pretty new to the whole activex/excel thing through Matlab. I'll let you know how it works.

[Mirage]

That worked, thanks a lot! I used polyfit to get the polynomials and derived the rsquared values and used linest for the linear slope.... definitely wasted way too much time trying it the other way.

[Eric]

I'm glad you got it working.

-Eric