Code covered by the BSD License  

Highlights from
xlschart

image thumbnail

xlschart

by

 

06 Mar 2004 (Updated )

Writes column headers and a matrix to excel and graphs all columns or two specified columns.

xlschart(titles,m,chartype,chart_title,varargin)
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

Contact us