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
