I am getting problems with adding charts in Excel 2010 using activeX

Question

Mirage 2012-10-19

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/51338-i-am-getting-problems-with-adding-charts-in-excel-2010-using-activex

Hi, I'm using activex to access Excel 2010 through Matlab and I need to add several charts to each worksheet. I created a function called xlsgraphchart to do exactly this for me, however it gives me errors whenever I add a chart at a number greater than a certain row. This code also plots the trendlines and extracts the values from the trendlines and returns the slope. The problem lies when I use,

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

in the following code. Anytime the 'Top' variable is greater than say 300 or so, it throws me an error.

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

%% The Excel variable in the function argument is an application, e.g. Excel = actxserver('excel.application');

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);
Type_Linear = -4132;
Type_Poly   = 3;
% Type_Power  = 4;
% Type_Exp    = 5;
XLChart.SeriesCollection(1).Trendlines.Add(Type_Linear);
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 1;
Lin_R_Squared = XLChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 0;
XLChart.SeriesCollection(1).Trendlines(1).DisplayEquation = 1;
Lin_y_Eq = XLChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
XLChart.SeriesCollection(1).Trendlines(1).DisplayRSquared = 1;
Lin_R_squared_Cell = regexp(Lin_R_Squared,'[+-eE\d*\x\.]+','match');
Lin_y_Eq_Cell = regexp(Lin_y_Eq,'[+-eE\d*\x\.]+','match');
Lin_R_squared_Num = str2num(Lin_R_squared_Cell{length(Lin_R_squared_Cell)});
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
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
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
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
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
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);
Poly_Power = length(PolySlope)-1;
PolySlope_Str = '';
for i=1:length(PolySlope)
    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

2 个评论
显示无隐藏无

Image Analyst 2012-10-19

You forgot to post the error message. Paste it exactly as it appears - don't paraphrase.

Mirage 2012-10-25

There was no specific error, the error was that it just simply wouldn't graph and would return an empty string because I suppose the position where I added the chart was out of bounds or out of the dimensions of the excel sheet which I didn't think was possible. Maybe this question might be better paraphrased http://www.mathworks.com/matlabcentral/answers/51893-how-can-i-add-multiple-charts-in-excel-with-activex Thanks.

请先登录，再进行评论。

请先登录，再回答此问题。

I am getting problems with adding charts in Excel 2010 using activeX

2 个评论
显示无隐藏无

回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

I am getting problems with adding charts in Excel 2010 using activeX

2 个评论 显示 无隐藏 无

回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

2 个评论
显示无隐藏无