function xlschart(titles,m,chartype,chart_title,varargin)
%XLSCHART Creates graphs in Microsoft Excel.
%
% xlschart(titles,m,chartype,chart_title)
% xlschart(titles,m,chartype,chart_title,filename)
% xlschart(titles,m,chartype,chart_title,xtitle,ytitle)
% xlschart(titles,m,chartype,chart_title,filename,sheetname)
% xlschart(titles,m,chartype,chart_title,xtitle,ytitle,filename)
% xlschart(titles,m,chartype,chart_title,xtitle,ytitle,filename,sheetname)
%
% xlschart : writes column headers and a matrix to excel
% and graphing all columns or 2 or more specified
% columns using excel.
%
% titles: Column titles (cell array).
% m: matrix of numbers.
% 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.
% xtitle: title of column to be x-axis (picked from titles).
% ytitle: title(s) of column to be y-axis (picked from titles).
%
% 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:
% titles = {'1st','2nd','3rd','4th','5th','6th','7th','8th','9th','10th'};
% m = magic(10);
% xlschart(titles,m,'LineStacked100','My Title')
% xlschart(titles,m,17,'My Title','sample.xls','Sheet2');
% xlschart(titles,m,'Line','My Title','1st','2nd','sample.xls','Sheet2');
% xlschart(titles,m,20,'My Title','1st','8th','sample.xls','Sheet2');
% xlschart(titles,m,'XYScatterSmooth','My Title','10th','9th','sample.xls','Sheet2');
% xlschart(titles,m,30,'My Title','2nd',{'7th','6th'},'sample.xls','Sheet2');
% xlschart(titles,m,29,'My Title',{'2nd','5th'},{'7th','6th'},'sample.xls','Sheet2');
%
% See also XLSREAD, XLSFINFO, XLSWRITE, XLSCELL, XLSHEETS, , CPTXT2XLS, MSOPEN
% Copyright 2004 Fahad Al Mahmood
% Version: 1.0 $ $Date: 5-Mar-2004
% Version: 1.5 $ $Date: 7-Mar-2004
% (Option for multiple ytitles for one xtitle added)
% Version: 1.6 $ $Date: 8-Mar-2004
% (CharType Added)
% Version: 1.7 $ $Date: 29-Apr-2004
% (CharType Can be String of Characters)
% Version: 2.0 $ $Date: 18-May-2004
% (Chart Title Added)
% Setting Chart Type
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'};
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
if length(titles)~= size(m,2)
error('Number of Column Titles does NOT match the number of columns in matrix');
end
switch nargin
case 5
filename = varargin{1};
case 6
if ismember(varargin{1},titles)
if iscell(varargin{1}) xtitle = varargin{1};
else xtitle = {varargin{1}}; end
if iscell(varargin{2}) ytitle = varargin{2};
else ytitle = {varargin{2}}; end
else
filename = varargin{1};
sheetname = varargin{2};
end
case 7
if iscell(varargin{1}) xtitle = varargin{1};
else xtitle = {varargin{1}}; end
if iscell(varargin{2}) ytitle = varargin{2};
else ytitle = {varargin{2}}; end
filename = varargin{3};
case 8
if iscell(varargin{1}) xtitle = varargin{1};
else xtitle = {varargin{1}}; end
if iscell(varargin{2}) ytitle = varargin{2};
else ytitle = {varargin{2}}; end
filename = varargin{3};
sheetname = varargin{4};
end
% Testing if (filename) is specified and/or new.
if ~exist('filename','var')
file_new = 1;
file_name = 0;
else
file_name = 1;
[fpath,file,ext] = fileparts(char(filename));
if isempty(fpath);
fpath=pwd;
end
if exist(filename,'file')
file_new = 0;
else
file_new = 1;
end
end
% Tesing if (xtitle) & (ytitle) is specified.
if exist('xtitle','var')
graph_all = 0;
else
graph_all = 1;
end
% Testing if (sheetname) is specified.
if ~exist('sheetname','var')
sheetname = 'Sheet1';
end
%---------------------------------------
% Opening Excel & Setting the Visibility
%---------------------------------------
Excel = actxserver('Excel.Application');
if ~file_name
Excel.Visible = 1;
else
Excel.Visible = 0;
end
%---------------------------------------------
% Opening/Creating Workbook & Activating Sheet
%---------------------------------------------
if ~file_name
Workbook = Excel.Workbooks.Add;
elseif file_name & file_new
Workbook = Excel.Workbooks.Add;
invoke(Workbook, 'SaveAs', [fpath filesep file ext]);
elseif file_name & ~file_new
Workbook = invoke(Excel.Workbooks, 'open', [fpath filesep file ext]);
end
% Adding column names
nc = size(m,2);
nr = size(m,1);
LastCol = xlcolumn(nc);
sheet = get(Excel.Worksheets, 'Item', sheetname);
invoke(sheet, 'Activate');
ExAct = Excel.Activesheet;
ExActRange = get(ExAct,'Range','A1',[LastCol '1']);
set(ExActRange,'Value',titles);
% Adding Values under the column names
ExActRange = get(ExAct,'Range',['A2:' LastCol int2str(size(m,1)+1)]);
set(ExActRange,'Value',m);
% Selecting the values matrix and creating a chart.
% Graphing Everything against the first column
if graph_all
Charts = Workbook.Charts;
Chart = invoke(Charts,'Add');
Excel.ActiveChart.SeriesCollection(1).Delete;
for i=1:length(titles)-1
invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
Excel.ActiveChart.SeriesCollection(i).Name = titles{i+1};
Excel.ActiveChart.SeriesCollection(i).XValues = ['=' sheetname '!R2C1:R' int2str(nr+1) 'C1'];
Excel.ActiveChart.SeriesCollection(i).Values = ['=' sheetname '!R2C' int2str(i+1) ':R' int2str(nr+1) 'C' int2str(i+1)];
end
Excel.ActiveChart.Name = chart_title;
% Setting the (X-Axis) title.
ChartAxes = invoke(Chart,'Axes',1);
set(ChartAxes,'HasTitle',1);
set(ChartAxes.AxisTitle,'Caption',titles{1});
Excel.ActiveChart.HasTitle = 1;
Excel.ActiveChart.ChartTitle.Characters.Text = chart_title;
% Graphing selected columns
else
xtitle_was_1 = 0;
if length(xtitle)~=length(ytitle) & length(xtitle)==1
xtitle_was_1 = 1;
for i=1:length(ytitle)
xtitle_temp{i} = xtitle{1};
end
xtitle = xtitle_temp;
end
% Testing if (xtitle) and (ytitle) are picked from (titles)
for i=1:length(ytitle)
if ~ismember(xtitle{i},titles)
error(['(' xtitle{i} ') is not a member of column titles']);
end
if ~ismember(ytitle{i},titles)
error(['(' ytitle{i} ') is not a member of column titles']);
end
end
[tf,locx] = ismember(xtitle,titles);
[tf,locy] = ismember(ytitle,titles);
Charts = Workbook.Charts;
Chart = invoke(Charts,'Add');
Excel.ActiveChart.ChartType = 'xlXYScatterSmoothNoMarkers';
Excel.ActiveChart.SeriesCollection(1).Delete;
for j=1:length(locy)
invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
Excel.ActiveChart.SeriesCollection(j).Name = ['=' sheetname '!R1C' int2str(locy(j))];
Excel.ActiveChart.SeriesCollection(j).XValues = ['=' sheetname '!R2C' int2str(locx(j)) ':R' int2str(nr+1) 'C' int2str(locx(j))];
Excel.ActiveChart.SeriesCollection(j).Values = ['=' sheetname '!R2C' int2str(locy(j)) ':R' int2str(nr+1) 'C' int2str(locy(j))];
end
y_axis_title = [];
x_axis_title = [];
for i=1:length(ytitle)
x_axis_title = [x_axis_title ' ' xtitle{i}];
y_axis_title = [y_axis_title ' ' ytitle{i}];
end
% Setting the Chart Sheet Title & Chart Title.
Excel.ActiveChart.HasTitle = 1;
Excel.ActiveChart.ChartTitle.Characters.Text = chart_title;
Excel.ActiveChart.Name = chart_title;
% Setting the (X-Axis) and (Y-Axis) titles.
ChartAxes = invoke(Chart,'Axes',1);
set(ChartAxes,'HasTitle',1);
if xtitle_was_1
set(ChartAxes.AxisTitle,'Caption',xtitle{1});
else
set(ChartAxes.AxisTitle,'Caption',x_axis_title);
end
ChartAxes = invoke(Chart,'Axes',2);
set(ChartAxes,'HasTitle',2);
if length(ytitle)==1
set(ChartAxes.AxisTitle,'Caption',ytitle{1});
else
set(ChartAxes.AxisTitle,'Caption',y_axis_title);
end
end
Excel.ActiveChart.ChartType = 'xlXYScatterSmoothNoMarkers';
% Setting the Plot Area Style
Excel.ActiveChart.PlotArea.Select;
Excel.Selection.Border.ColorIndex = 16;
Excel.Selection.Border.Weight = 1;
Excel.Selection.Border.LineStyle = 1;
Excel.Selection.Interior.ColorIndex = 0;
Excel.Selection.Border.ColorIndex = 2;
Excel.Selection.Border.Weight = 1;
% 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.Select;
Excel.Selection.Border.ColorIndex = 15;
Excel.Selection.Border.Weight = 1;
Excel.Selection.Border.LineStyle = 2;
Excel.ActiveChart.Axes(1).MinorGridlines.Select;
Excel.Selection.Border.ColorIndex = 15;
Excel.Selection.Border.Weight = 1;
Excel.Selection.Border.LineStyle = 3;
% Setting the (Y-Axis) Major/Minor Gridlines Style
Excel.ActiveChart.Axes(2).MajorGridlines.Select;
Excel.Selection.Border.ColorIndex = 15;
Excel.Selection.Border.Weight = 1;
Excel.Selection.Border.LineStyle = 2;
Excel.ActiveChart.Axes(2).MinorGridlines.Select;
Excel.Selection.Border.ColorIndex = 15;
Excel.Selection.Border.Weight = 1;
Excel.Selection.Border.LineStyle = 3;
% Setting Legend Box Border Style
Excel.ActiveChart.Legend.Select;
Excel.Selection.Border.ColorIndex = 1;
Excel.Selection.Border.Weight = 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
if graph_all
limit = nc-1;
else
limit = length(ytitle);
end
for i=1:limit
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
if file_name
invoke(Workbook, 'Save');
invoke(Excel, 'Quit');
end
delete(Excel);
function xlcell_location = xlcolumn(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
xlcell_location = char(letters(column));
else
ocolumn = column;
while column-26>0
count = count + 1;
column = column - 26;
end
xlcell_location = [char(letters(count)) char(letters(column))];
end
