Code covered by the BSD License  

Highlights from
xlsgraph

image thumbnail

xlsgraph

by

 

27 May 2004 (Updated )

Creates graphs from existing Excel sheet containing column data.

xlsgraph(xtitle,ytitle,chartype,chart_title,varargin)
function xlsgraph(xtitle,ytitle,chartype,chart_title,varargin)

%XLSGRAPH Creates graphs from existing Excel sheet containing column data.
%
% xlsgraph(xtitle,ytitle,chartype,chart_title,filename,sheetname)
%
% xlsgraph  : creates an Excel graph by searching specified sheet for
%             selected columns headers.
%
%       xtitle:         title of column to be x-axis (picked from titles).
%       ytitle:         title(s) of column to be y-axis (picked from titles).
%       chartype:       integer number corresponds to chart type (from the
%                       list below) or string of characters to name the
%                       chart type (ex. '3DLine')
%       chart_title:    This title will be used for both chart sheet name
%                       and chart title.
%       filename:       Name of excel file.
%       sheetname:      sheet name.
% 
%   CHARTYPE:
% 1- ColumnClustered		2- ColumnStacked	3- ColumnStacked100
% 4- 3DColumnClustered		5- 3DColumnStacked	6- 3DColumnStacked100	
% 7-3DColumn			    8-BarClustered		9-BarStacked
% 10-BarStacked100		    11-3DBarClustered	12-3DBarStacked
% 13-3DBarStacked100      	14-Line			    15-LineStacked
% 16-LineStacked100		    17-LineMarkers		18-LineMarkersStacked
% 19-LineMarkersStacked100	20-3DLine		    21-Pie
% 22-3DPie			        23-PieOfPie		    24-PieExploded
% 25-3DPieExploded		    26-BarOfPie		    27-XYScatter
% 28-XYScatterSmooth	29-XYScatterSmoothNoMarkers	30-XYScatterLines
% 31-XYScatterLinesNoMarkers	32-Area			33-AreaStacked
% 34-AreaStacked100		        35-3DArea		36-3DAreaStacked
% 37-3DAreaStacked100		    38-Doughnut		39-DoughnutExploded
% 40-Radar			            41-RadarMarkers		42-RadarFilled
% 43-Surface			        44-SurfaceWireframe	45-SurfaceTopView
% 46-SurfaceTopViewWireframe	47-Bubble		    48-Bubble3DEffect
% 49-StockHLC			        50-StockOHLC		51-StockVHLC
% 52-StockVOHLC
%
% Examples:
%      xlsgraph('col_1','col_2','XYScatterSmooth','1 vs 2','data.xls','Data');
%      xlsgraph('col_1','col_4','XYScatterSmooth','1 vs 4','data.xls','Data');
%      xlsgraph({'col_1','col_2'},{'col_3','col_4'},'Line','1&2 vs 3&4','data.xls','Data');
%
%   See also XLSREAD, XLSFINFO, XLSWRITE, XLSCELL, XLSHEETS, MSOPEN, XLSCHART

%   Copyright 2004 Fahad Al Mahmood
%   Version: 1.0 $  $Date: 26-May-2004
%            1.1 $  $Date: 01-Jun-2004  (Search Criteria Changed to "Match Case")

if length(varargin)==1
    filename = varargin{1};
    sheetname = 'Sheet1';
elseif length(varargin)==2
    filename = varargin{1};
    sheetname = varargin{2};
end

% Making sure the name of the sheet are according to Excel rules.
%  (1) Making sure each sheetname entered does not exceed 31 characters.    
if length(chart_title)>31
    error(['sheet (' chart_title ') exceeds 31 characters! (see xlsheets help)'])
end
%  (2) Making sure each sheetname does not contain any illegal character.
if any(ismember([':','\','/','?','*'],chart_title)) | ismember('[',chart_title(1))
    error(['sheet (' chart_title ') contains an illegal character! (see xlsheets help)'])
end
%  (3) Making sure each sheetname is not blank.
if isempty(chart_title)
    error(['sheet ' chart_title ' is empty! (see xlsheets help)'])
end
%  (4) Making sure each sheetname is a character string.
if ~ischar(chart_title)
    error(['sheet (' chart_title ') is NOT a character string! (see xlsheets help)'])
end

[fpath,file,ext] = fileparts(char(filename));
if isempty(fpath)
    fpath = pwd;
end

if ~iscell(xtitle)
    xtitle = {xtitle};
end

if ~iscell(ytitle)
    ytitle = {ytitle};
end

chart_type = {...
        'xlColumnClustered',        
    'xlColumnStacked',
    'xlColumnStacked100',
    'xl3DColumnClustered',
    'xl3DColumnStacked',
    'xl3DColumnStacked100',
    'xl3DColumn',
    'xlBarClustered',
    'xlBarStacked',
    'xlBarStacked100',
    'xl3DBarClustered',
    'xl3DBarStacked',
    'xl3DBarStacked100',        
    'xlLine',
    'xlLineStacked',
    'xlLineStacked100',
    'xlLineMarkers',
    'xlLineMarkersStacked',
    'xlLineMarkersStacked100',
    'xl3DLine',
    'xlPie',
    'xl3DPie',
    'xlPieOfPie',
    'xlPieExploded',
    'xl3DPieExploded',
    'xlBarOfPie',
    'xlXYScatter',
    'xlXYScatterSmooth',
    'xlXYScatterSmoothNoMarkers'
    'xlXYScatterLines',
    'xlXYScatterLinesNoMarkers',
    'xlArea',
    'xlAreaStacked',
    'xlAreaStacked100',
    'xl3DArea',
    'xl3DAreaStacked',
    'xl3DAreaStacked100',
    'xlDoughnut',
    'xlDoughnutExploded',
    'xlRadar',
    'xlRadarMarkers',
    'xlRadarFilled',
    'xlSurface',
    'xlSurfaceWireframe',
    'xlSurfaceTopView',
    'xlSurfaceTopViewWireframe',
    'xlBubble',
    'xlBubble3DEffect',
    'xlStockHLC',
    'xlStockOHLC',
    'xlStockVHLC',
    'xlStockVOHLC'};

% Making sure chart type integer is within the range 1 to 52 or the chart type
% string is a recognized command by Excel
if ~ismember(chartype,[1:52]) & ~ismember(['xl' chartype],chart_type);
    error('Chart Type must be an integer number between 1-52');
end

if isnumeric(chartype)
    chartype = chart_type{chartype};
elseif ~ismember(['xl' chartype],chart_type);
    error('Chart Type is not recognized as an Excel chart type!');
else
    chartype = ['xl' chartype];
end


% Equating the number of titles in (xtitle) and (ytitle) if necessery
if length(xtitle)~=length(ytitle) & length(xtitle)==1
    for i=1:length(ytitle)
        xtitle_temp{i} = xtitle{1};
    end
    xtitle = xtitle_temp;
elseif length(xtitle)~=length(ytitle) & length(xtitle)>1
    error('Lengths of (xtitle) & (ytitle) does not match!');
end

Excel = actxserver('Excel.Application');
set(Excel,'Visible',0);

Workbook = invoke(Excel.Workbooks, 'open', [fpath filesep file ext]);
sheet = get(Excel.Worksheets,'Item',sheetname);
invoke(sheet, 'Activate');
ExAct = Excel.Activesheet;

% Making sure targetted column headers do exist
for n=1:length(xtitle)
    ExActRange = get(ExAct,'Range','A1');
    ExActRange.Select;
    Cell_Found = invoke(Excel.Cells,'Find',xtitle{n},Excel.ActiveCell,-4123,1,1,1,1,1); 
    try
        Cell_Found.Activate;
    catch
        invoke(Excel,'Quit');
        delete(Excel);
        error(['Column (' xtitle{n} ') cannot be found!']);
    end
end

for n=1:length(ytitle)
    ExActRange = get(ExAct,'Range','A1');
    ExActRange.Select;
    Cell_Found = invoke(Excel.Cells,'Find',ytitle{n},Excel.ActiveCell,-4123,1,1,1,1,1); 
    try
        Cell_Found.Activate;
    catch
        invoke(Excel,'Quit');
        delete(Excel);
        error(['Column (' ytitle{n} ') cannot be found!']);
    end
end

% Adding Chart
Charts = Workbook.Charts;
Chart = invoke(Charts,'Add');
try
    Excel.ActiveChart.Name = chart_title;
catch
    invoke(Excel,'Quit');
    error(['Sheet (' chart_title ') already exists!']);
end

Excel.ActiveChart.ChartType = 'xlXYScatterSmoothNoMarkers';
Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
invoke(Series,'Delete');

for n=1:length(ytitle)
    Chart.Select;
    NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
    sheet = get(Excel.Worksheets,'Item',sheetname);
    invoke(sheet,'Activate');
    
    ExAct = Excel.Activesheet;
    ExActRange = get(ExAct,'Range','A1');
    ExActRange.Select;          
    
    % ------------
    %   xtitle
    % ------------
    
    Cell_Found = invoke(Excel.Cells,'Find',xtitle{n},Excel.ActiveCell,-4123,1,1,1,1,1);
    % Function Find(What, [After], [LookIn], [LookAt], [SearchOrder],
    % [SearchDirection As XlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat])
    %
    % [After] = Excel.ActiveCell
    % [LookIn] = -4123 % xlFormulas
    %          = -4163 % xlValues
    %          = -4144 % xlComments
    % [LookAt] = 1 % xlWhole
    %          = 2 % xlPart
    % [SearchOrder] = 1 % xlByRows
    %               = 2 % xlByColumns
    % [SearchDirection] = 1 % xlNext
    %                   = 2 % xlPrevious
    %  [MatchCase] = 1 % True
    %              = 0 % False
    %  [MatchByte] = 1 % True
    %              = 0 % False
    %  [SearchFormat] = 1 % True
    %                 = 0 % False   
    Cell_Found.Activate;
    Excel.Select;
    R = Excel.Selection.Row;
    C = Excel.Selection.Column;
    X=0;
    
    end_R = R;
    while ~isnan(X)
        end_R=end_R+1;
        ExActRange = get(ExAct,'Range',[xlcolumn(C) int2str(end_R)]);
        ExActRange.Select;
        X = Excel.Selection.Value;
    end
    
    Chart.Select;
    NewSeries.XValues = ['=' sheetname '!R' int2str(R+1) 'C' int2str(C) ':R' int2str(end_R-1) 'C' int2str(C)];
    
    % ------------
    %   ytitle
    % ------------
    invoke(sheet, 'Activate');
    Cell_Found = invoke(Excel.Cells,'Find',ytitle{n},Excel.ActiveCell,-4123,1,1,1,1,1);
    % Function Find(What, [After], [LookIn], [LookAt], [SearchOrder],
    % [SearchDirection As XlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat])
    %
    % [After] = Excel.ActiveCell
    % [LookIn] = -4123 % xlFormulas
    %          = -4163 % xlValues
    %          = -4144 % xlComments
    % [LookAt] = 1 % xlWhole
    %          = 2 % xlPart
    % [SearchOrder] = 1 % xlByRows
    %               = 2 % xlByColumns
    % [SearchDirection] = 1 % xlNext
    %                   = 2 % xlPrevious
    %  [MatchCase] = 1 % True
    %              = 0 % False
    %  [MatchByte] = 1 % True
    %              = 0 % False
    %  [SearchFormat] = 1 % True
    %                 = 0 % False
    Cell_Found.Activate;
    Excel.Select;
    R = Excel.Selection.Row;
    C = Excel.Selection.Column;
    X=0;
    
    end_R = R;
    while ~isnan(X)
        end_R=end_R+1;
        ExActRange = get(ExAct,'Range',[xlcolumn(C) int2str(end_R)]);
        ExActRange.Select;
        X = Excel.Selection.Value;
    end
    
    ExActRange = get(ExAct,'Range',[xlcolumn(C) int2str(R+1)],[xlcolumn(C) int2str(end_R-1)]);
    ExActRange.Select;
    
    Chart.Select;
    NewSeries.Name = ['=' sheetname '!R' int2str(R) 'C' int2str(C)];
    NewSeries.Values = ['=' sheetname '!R' int2str(R+1) 'C' int2str(C) ':R' int2str(end_R-1) 'C' int2str(C)];
    
end

% Setting the (X-Axis) and (Y-Axis) titles.
y_axis_title = '';
x_axis_title = '';
unique_xtitle = unique(xtitle);
unique_ytitle = unique(ytitle);
for i=1:length(unique_ytitle)
    y_axis_title = [y_axis_title ' ' unique_ytitle{i}];
end
for i=1:length(unique_xtitle)
    x_axis_title = [x_axis_title ' ' unique_xtitle{i}];
end
y_axis_title = y_axis_title(2:end);
x_axis_title = x_axis_title(2:end);


ChartAxes = invoke(Chart,'Axes',1);
set(ChartAxes,'HasTitle',1);
set(ChartAxes.AxisTitle,'Caption',x_axis_title);
ChartAxes = invoke(Chart,'Axes',2);
set(ChartAxes,'HasTitle',2);
set(ChartAxes.AxisTitle,'Caption',y_axis_title); 

% Setting the Chart Title.
Excel.ActiveChart.HasTitle = 1;
Excel.ActiveChart.ChartTitle.Characters.Text = chart_title;
%Excel.ActiveChart.ChartTitle.Characters.Text = ['(' y_axis_title ') vs. (' x_axis_title ')'];
Excel.ActiveChart.ChartTitle.Font.Bold = 1;
Excel.ActiveChart.ChartTitle.Font.Size = 12;
Excel.ActiveChart.ChartType = 'xlXYScatterSmoothNoMarkers';

% Setting the Plot Area Style
Excel.ActiveChart.PlotArea.Border.LineStyle = 1;
Excel.ActiveChart.PlotArea.Border.ColorIndex = 2;
Excel.ActiveChart.PlotArea.Border.Weight = 1;
Excel.ActiveChart.PlotArea.Interior.ColorIndex = 0;

% Setting the (X-Axis) Scale
Excel.ActiveChart.Axes(1).Select;
Excel.ActiveChart.Axes(1).MinimumScaleIsAuto = 1;
Excel.ActiveChart.Axes(1).MaximumScaleIsAuto = 1;
Excel.ActiveChart.Axes(1).MinorUnitIsAuto = 1;
Excel.ActiveChart.Axes(1).MajorUnitIsAuto = 1;
% Excel.ActiveChart.Axes(1).MinimumScale = min(m(:,1));             
% Excel.ActiveChart.Axes(1).MaximumScale = max(m(:,1));
% Excel.ActiveChart.Axes(1).MinorUnit = 1;
% Excel.ActiveChart.Axes(1).MajorUnit = 0.1;
Excel.ActiveChart.Axes(1).Crosses = 1;                    % (1) Automatic (comment next line)
% (2) Custom (Uncomment next line)
% Excel.ActiveChart.Axes(1).CrossesAt = 0;                % Cross value.
Excel.ActiveChart.Axes(1).ReversePlotOrder = 0;           % (True) or (False)
Excel.ActiveChart.Axes(1).ScaleType = 0;                  % (0) Linear (1) Logarithmic
Excel.ActiveChart.Axes(1).DisplayUnit = 0;                % 0,'xlHundreds','xlThousands','xlMillions'
%   'xlThousandMillions','xlMillionMillions'
Excel.ActiveChart.Axes(1).HasDisplayUnitLabel = 1;        % (True) or (False)

% Setting the (Y-Axis) Scale
Excel.ActiveChart.Axes(2).Select;
Excel.ActiveChart.Axes(2).MinimumScaleIsAuto = 1;
Excel.ActiveChart.Axes(2).MaximumScaleIsAuto = 1;
% Excel.ActiveChart.Axes(2).MinimumScale = min(m(:,locy));             
% Excel.ActiveChart.Axes(2).MaximumScale = max(m(:,locy));
Excel.ActiveChart.Axes(2).MinorUnitIsAuto = 1;
Excel.ActiveChart.Axes(2).MajorUnitIsAuto = 1;
% Excel.ActiveChart.Axes(2).MinorUnit = 1;
% Excel.ActiveChart.Axes(2).MajorUnit = 0.1;
Excel.ActiveChart.Axes(2).Crosses = 1;                    % (1) Automatic (2) Custom (Uncomment next line)
% Excel.ActiveChart.Axes(2).CrossesAt = 0;                % Cross value.
Excel.ActiveChart.Axes(2).ReversePlotOrder = 0;           % (True) or (False)
Excel.ActiveChart.Axes(2).ScaleType = 0;                  % (0) Linear (1) Logarithmic
Excel.ActiveChart.Axes(2).DisplayUnit = 0;                % 0,'xlHundreds','xlThousands','xlMillions'
%   'xlThousandMillions','xlMillionMillions'
Excel.ActiveChart.Axes(1).HasDisplayUnitLabel = 1;        % (True) or (False)
% Excel.ActiveChart.Axes(2).TickLabels.NumberFormat = '0.00';     % Format the (Y-Axis) numbers

% Setting the Major/Minor Gridlines in (X-Axis) & (Y-Axis)
Excel.ActiveChart.Axes(1).HasMajorGridlines = 1;
Excel.ActiveChart.Axes(1).HasMinorGridlines = 1;
Excel.ActiveChart.Axes(2).HasMajorGridlines = 1;
Excel.ActiveChart.Axes(2).HasMinorGridlines = 1;

% Setting the (X-Axis) Major/Minor Gridlines Style
Excel.ActiveChart.Axes(1).MajorGridlines.Border.ColorIndex = 15;
Excel.ActiveChart.Axes(1).MajorGridlines.Border.Weight = 1;
Excel.ActiveChart.Axes(1).MajorGridlines.Border.LineStyle = 2;
Excel.ActiveChart.Axes(1).MinorGridlines.Border.ColorIndex = 15;
Excel.ActiveChart.Axes(1).MinorGridlines.Border.Weight = 1;
Excel.ActiveChart.Axes(1).MinorGridlines.Border.LineStyle = 3;

% Setting the (Y-Axis) Major/Minor Gridlines Style
Excel.ActiveChart.Axes(2).MajorGridlines.Border.ColorIndex = 15;
Excel.ActiveChart.Axes(2).MajorGridlines.Border.Weight = 1;
Excel.ActiveChart.Axes(2).MajorGridlines.Border.LineStyle = 2;
Excel.ActiveChart.Axes(2).MinorGridlines.Border.ColorIndex = 15;
Excel.ActiveChart.Axes(2).MinorGridlines.Border.Weight = 1;
Excel.ActiveChart.Axes(2).MinorGridlines.Border.LineStyle = 3;


% Setting the Major/Minor Gridlines in (X-Axis) & (Y-Axis)
Excel.ActiveChart.Axes(1).HasMajorGridlines = 1;
Excel.ActiveChart.Axes(1).HasMinorGridlines = 1;
Excel.ActiveChart.Axes(2).HasMajorGridlines = 1;
Excel.ActiveChart.Axes(2).HasMinorGridlines = 1;

colors = [1 3 4 5 7 9 10 11 12 13 14 16 17 18 22 23 25 43 44 45 46];
k=0;
% Setting Legend Styles
for i=1:length(ytitle)
    k=k+1;
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.Border.ColorIndex = colors(k);
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.Border.Weight = 3;
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.Border.LineStyle= 1;
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.MarkerBackgroundColorIndex = 45;
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.MarkerForegroundColorIndex = 46;
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.MarkerStyle = 0;        % (0) for none. (1) square (2) diamond ... etc
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.Smooth = 1;
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.MarkerSize = 4;
    Excel.ActiveChart.Legend.LegendEntries(i).LegendKey.Shadow = 1;
    if k==21 k=0; end
end

Excel.ActiveChart.ChartType = chartype;

% Saving & Quitting
invoke(Workbook,'Save');
invoke(Excel,'Quit');
delete(Excel);


function loc = xlcolumn(column)

if isnumeric(column)
    if column>256
        error('Excel is limited to 256 columns! Enter an integer number <256');
    end
    letters = {'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'};
    count = 0;
    if column-26<=0
        loc = char(letters(column));
    else
        ocolumn = column;
        while column-26>0
            count = count + 1;
            column = column - 26;
        end
        loc = [char(letters(count)) char(letters(column))];
    end
    
else
    letters = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
    if size(column,2)==1
        loc =findstr(column,letters);
    elseif size(column,2)==2
        loc1 =findstr(column(1),letters);
        loc2 =findstr(column(2),letters);
        loc = (26 + 26*loc1)-(26-loc2);
    end
end

Contact us