image thumbnail

Portfolio Optimizer Tool

by

 

22 Dec 2010 (Updated )

Portfolio Optimizer Tool

ExcelReport
classdef ExcelReport < handle
%EXCELREPORT 
% Utiltiy class to create a Excel documentation
%
%   Copyright 2009-2011 The MathWorks, Inc
    
    properties (Access = protected)
        xlsHandle = [];     % Excel automation handle
        enabled = true;     % Reporting enabled/disabled
    end
    
    
    methods (Access = public)

        function this = ExcelReport(filename,sheetname,disable_flag,visible_flag)
        % Create Excel Report object
        %
        % Input arguments:
        % filename:     Filename of Excel report
        % sheetname:    Name of worksheet
        % disable_flag: If this flag is set to 1, reporting is disabled (optional)
        %               May be used to temporarely disable reporting
        % visible_flag: If this flag is set to 1, Excel sheet is visible
        %               during reporting process
        %
        % Last change:   4. January 2010
        % Author:        patric.schenk@mathworks.ch

            % Argument checking
            % -----------------
            if ~exist('filename','var')
                error('ExcelReport:ExcelReport','Please provide a filename');
            end
            if ~ischar(filename)
                error('ExcelReport:ExcelReport','Filename not valid');
            end
            if ~strcmp(filename(end-4:end),'.xlsx') && ~strcmp(filename(end-3:end),'.xls')
                filename = [filename,'.xlsx'];
            end
            if ~exist('sheetname','var')
                error('ExcelReport:ExcelReport','Please provide sheet name');
            end
            if ~ischar(sheetname)
                error('ExcelReport:ExcelReport','Sheet name not valid');
            end
            this.enabled = true;
            if exist('disable_flag','var')
                if disable_flag
                    this.enabled = false;
                end
            end
            if ~exist('visible_flag','var')
                visible_flag = false;
            end

            % Connect to Excel and open/create sheet
            % ---------------------------------------
            try
                if this.enabled
                    % Connect to Excel
                    this.xlsHandle  = actxserver('Excel.Application');
                    this.xlsHandle.visible = visible_flag;
                    % Open file if existing or create new
                    if ~any(filename=='\')    
                        filename = fullfile(pwd,filename);
                    end
                    if exist(filename,'file')
                        % Open file
                        this.xlsHandle.Workbooks.Open(filename);  
                    else
                        % Add new workbook and save file
                        this.xlsHandle.Workbooks.Add;
                        this.xlsHandle.ActiveWorkbook.SaveAs(filename);  
                    end
                    % Activate/add selected sheet
                    found = false;
                    for sheetcount = 1:this.xlsHandle.ActiveWorkBook.Sheets.Count
                        % try to find selected sheet
                        if strcmp(this.xlsHandle.ActiveWorkBook.Sheets.Item(sheetcount).Name,sheetname)
                            this.xlsHandle.ActiveWorkbook.Sheets.Item(sheetcount).Activate;
                            found = true;
                            break;
                        end
                    end
                    if ~found
                        sheet = this.xlsHandle.ActiveWorkBook.Sheets.Add;
                        sheet.Name = sheetname;
                    end
                    % set narrow margins
                    this.xlsHandle.ActiveSheet.PageSetup.HeaderMargin = 0;
                    this.xlsHandle.ActiveSheet.PageSetup.FooterMargin = 0;
                    this.xlsHandle.ActiveSheet.PageSetup.TopMargin = 30;
                    this.xlsHandle.ActiveSheet.PageSetup.BottomMargin = 20;
                    this.xlsHandle.ActiveSheet.PageSetup.LeftMargin = 30;
                    this.xlsHandle.ActiveSheet.PageSetup.RightMargin = 20;
                end    % if enabled
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.xlsHandle.Quit;
                    catch
                    end
                end
                error('ExcelReport:ExcelReport',ME.message);
            end    
        end
        
        function closeReport(this)
        % Close Excel Report

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:CloseReport','Excel handle not valid');
                return
            end

            % Finalize report
            try
                % Save and close Excel report
                this.xlsHandle.ActiveSheet.Range('A1').Select;
                this.xlsHandle.ActiveWorkbook.Save; 
                this.xlsHandle.ActiveWorkbook.Close(false);
                this.xlsHandle.Quit;
                this.xlsHandle = [];  % this removes Excel task

            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.xlsHandle.Quit;
                        this.xlsHandle = [];
                    catch
                    end
                end
                error('ExcelReport:CloseReport',ME.message);
            end        
        end
        
        function insertText(this,range,text,options)
        % Write a text to Excel Report
        %
        % Input arguments:
        % range:   Range to write text (can be multiple cells)
        %          - e.g. 'A3:C3','B4',[4,3], [5,5,32,6] (equals 'E5:F32')
        % text:    Text to add to cell, one of:
        %          - String for single line
        %          - Cell vector containing strings for multiple lines
        % options: Options structure (optional)
        %          - Call getDefaultTextOptions() to retrieve structure setup

            % Get enabled flag
            if this.enabled == false
                return
            end
            % check arguments
            if nargin < 3
                warning('ExcelReport:InsertText','Not enough input arguments, no text inserted');
                return
            end
            if ~ischar(range) && ~isnumeric(range)
                warning('ExcelReport:InsertText','Range not valid, no text inserted');
                return
            end
            if isnumeric(range)
                % replace row/column indeces by corresponding range
                switch numel(range)
                    case 2
                        range = this.index2range(range(1),range(2));
                    case 4
                        range = [this.index2range(range(1),range(2)),':',this.index2range(range(3),range(4))];
                    otherwise
                        warning('ExcelReport:InsertText','Numeric range not valid, no text inserted');
                        return
                end
            end
            if ~ischar(text) && ~iscell(text)
                warning('ExcelReport:InsertText','Text format not valid, no text inserted');
                return
            end
            if ~exist('options','var')
                options = this.getDefaultTextOptions;
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:InsertText','Excel handle not valid');
                return
            end

            % Write data to Excel
            % -------------------
            try 
                % If text is a cell vector, create single string with linefeeds
                if iscell(text)
                    tmp = text{1};
                    for i = 2:length(text)
                        tmp = [tmp,char(10),text{i}];
                    end
                    text = tmp;
                end
                % Write text
                r = this.xlsHandle.ActiveSheet.Range(range);
                r.Select;
                r.Merge;
                r.Value = text;
                if ~isempty(options)
                    r.Font.Name = options.FontName;
                    r.Font.Size = options.FontSize;
                    r.Font.Bold = options.Bold;
                    r.Font.Color = sum(round(options.FGColor*255).*[256*256,256,1]);
                    r.Interior.Color = sum(round(options.BGColor*255).*[256*256,256,1]);    
                    r.Borders.LineStyle = options.Grid;
                    if options.Grid
                        r.Borders.Weight = 1;
                        r.Borders.Color = sum(round(options.GridColor*255).*[256*256,256,1]);
                    end
                    switch options.HorizontalAlignment
                        case 'left'
                            r.HorizontalAlignment = 2;
                        case 'center'
                            r.HorizontalAlignment = 3;
                        case 'right'
                            r.HorizontalAlignment = 4;
                        otherwise
                            error('ExcelReport:InsertText','Horizontal must be one of left/center/right');
                    end
                    switch options.VerticalAlignment
                        case 'top'
                            r.VerticalAlignment = 1;
                        case 'middle'
                            r.VerticalAlignment = 2;
                        case 'bottom'
                            r.VerticalAlignment = 3;
                        otherwise
                            error('ExcelReport:InsertText','Horizontal must be one of top/middle/bottom');
                    end
                end
                % autofit row height
                r.Select;
                this.xlsHandle.Selection.EntireRow.AutoFit;
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:InsertText',ME.message);
            end
        end        
        
        function insertTable(this,range,data,title,options)
        % Write a table to Excel Report
        %
        % Input arguments:
        % range:   Upper left range of table in worksheet 
        %          - e.g. 'A3','B4',[4,3] (equals 'C4')
        % data:    Data to be written
        % title:   Titel of table (optional)
        % options: Options structure (optional)
        %          - call getDefaultTableOptions() to retreive structure setup

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Argument checking
            if nargin < 3
                warning('ExcelReport:InsertTable','Not enough input arguments, no table inserted');
                return
            end
            if ~ischar(range) && ~isnumeric(range)
                warning('ExcelReport:InsertTable','Range not valid, no table inserted');
                return
            end
            if isnumeric(range)
                % replace row/column indeces by corresponding range
                switch numel(range)
                    case 2
                        range = this.index2range(range(1),range(2));
                    otherwise
                        warning('ExcelReport:InsertTable','Numeric range not valid, no text inserted');
                        return
                end
            end
            if ~iscell(data) && ~isnumeric(data)
                warning('ExcelReport:InsertTable','Data not valid, no table inserted');
                return
            end
            if ~exist('title','var')
                title = [];
            elseif ~ischar(title)
                title = [];
            end
            if ~exist('options','var')
                options = this.getDefaultTableOptions;
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:InsertTable','Excel handle not valid');
                return
            end

            % Write data to Excel
            % -------------------
            try
                % Get row/col index of range to start with
                [row0,col0] = this.range2index(range);
                % Get size of table (without title)
                [rows,cols] = size(data);
                % do we need to merge cells?
                if ~isempty(options) && ~isempty(options.MergeNumberOfColumns)
                    if numel(options.MergeNumberOfColumns) == cols
                        merge_cols = options.MergeNumberOfColumns;
                    else
                        warning('ExcelReport:InsertTable','Number of elements in "MergeNumberOfColumns" must match number of columns in table data');
                        return
                    end
                else
                    merge_cols = ones(1,cols);
                end
                % Write title (using insertText)
                if ~isempty(title)
                    curr_range = [range,':',this.index2range(row0,col0+sum(merge_cols)-1)];
                    this.insertText(curr_range,title,options.Title);
                    % move start range one row down
                    row0 = row0+1;
                end
                % Write table content
                if ~iscell(data)
                    data = num2cell(data);
                end
                for row = 1:rows
                    for col = 1:cols
                        if merge_cols(col) == 1
                            curr_range =  this.index2range(row0+row-1,col0+sum(merge_cols(1:col))-1);  
                        else
                            curr_range =  [this.index2range(row0+row-1,col0+sum(merge_cols(1:col-1))),':',this.index2range(row0+row-1,col0+sum(merge_cols(1:col))-1)];  
                        end
                        r = this.xlsHandle.ActiveSheet.Range(curr_range);
                        r.Merge;
                        r.Value = data{row,col};
                    end
                end
                curr_range = [this.index2range(row0,col0),':',this.index2range(row0+rows-1,col0+sum(merge_cols)-1)];
                r = this.xlsHandle.ActiveSheet.Range(curr_range);
                if ~isempty(options)
                    r.Font.Name = options.FontName;
                    r.Font.Size = options.FontSize;
                    r.Font.Bold = options.Bold;
                    r.Font.Color = sum(options.FGColor.*[16711680,65280,255]);
                    r.Interior.Color = sum(options.BGColor.*[16711680,65280,255]);
                    if options.MajorGrid
                        r.Borders.Item('xlEdgeTop').LineStyle =1;
                        r.Borders.Item('xlEdgeBottom').LineStyle =1;
                        r.Borders.Item('xlEdgeLeft').LineStyle =1;
                        r.Borders.Item('xlEdgeRight').LineStyle =1;
                        if options.GridColor
                            r.Borders.Item('xlEdgeTop').Color = sum(options.GridColor.*[16711680,65280,255]);
                            r.Borders.Item('xlEdgeBottom').Color = sum(options.GridColor.*[16711680,65280,255]);
                            r.Borders.Item('xlEdgeLeft').Color = sum(options.GridColor.*[16711680,65280,255]);
                            r.Borders.Item('xlEdgeRight').Color = sum(options.GridColor.*[16711680,65280,255]);
                            r.Borders.Item('xlEdgeTop').Weight = 1;
                            r.Borders.Item('xlEdgeBottom').Weight = 1;
                            r.Borders.Item('xlEdgeLeft').Weight = 1;
                            r.Borders.Item('xlEdgeRight').Weight = 1;
                        end
                    end
                    if options.MinorGrid
                        % only set these options if there is multiple rows/columns
                        if rows > 1
                            r.Borders.Item('xlInsideHorizontal').LineStyle =1;
                            r.Borders.Item('xlInsideHorizontal').Weight = 1;
                            if options.GridColor
                                r.Borders.Item('xlInsideHorizontal').Color = sum(options.GridColor.*[16711680,65280,255]);
                            end
                        end
                        if cols > 1
                            r.Borders.Item('xlInsideVertical').LineStyle =1;
                            r.Borders.Item('xlInsideVertical').Weight =1;
                            if options.GridColor
                                r.Borders.Item('xlInsideVertical').Color = sum(options.GridColor.*[16711680,65280,255]);
                            end
                        end
                    end
                    switch options.HorizontalAlignment
                        case 'left'
                            r.HorizontalAlignment = 2;
                        case 'center'
                            r.HorizontalAlignment = 3;
                        case 'right'
                            r.HorizontalAlignment = 4;
                        otherwise
                            error('ExcelReport:InsertTable','Horizontal must be one of left/center/right');
                    end
                    switch options.VerticalAlignment
                        case 'top'
                            r.VerticalAlignment = 1;
                        case 'middle'
                            r.VerticalAlignment = 2;
                        case 'bottom'
                            r.VerticalAlignment = 3;
                        otherwise
                            error('ExcelReport:InsertTable','Horizontal must be one of top/middle/bottom');
                    end
                end
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:InsertTable',ME.message);
            end

        end  
        
        function insertFigure(this,range,figure_handle,dimensions)
        % Insert a MATLAB figure or axes into Excel Report
        %
        % Input arguments:
        % range:         Range in worksheet to place the picture
        %                - e.g. 'A3','B4',[4,3] (equals 'C4')
        % figure_handle: Figure handle or axes handle
        %                Note: for axes, if you want to copy multiple
        %                objects just pass a vector of axes handles. 
        %                (first has to be the main axes object)
        % dimensions:    Two element vector with custom width and height (optional)

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Argument checking
            if nargin < 3
                warning('ExcelReport:InsertFigure','Not enough input arguments, no figure inserted');
                return
            end
            if ~ischar(range) && ~isnumeric(range)
                warning('ExcelReport:InsertFigure','Range not valid, no figure inserted');
                return
            end
            if isnumeric(range)
                % replace row/column indeces by corresponding range
                switch numel(range)
                    case 2
                        range = this.index2range(range(1),range(2));
                    otherwise
                        warning('ExcelReport:InsertFigure','Numeric range not valid');
                        return
                end
            end
            if ~any(ishghandle(figure_handle))
                warning('ExcelReport:InsertFigure','Figure handle not valid, no figure inserted');
                return
            end
            if exist('dimensions','var')
                if ~isnumeric(dimensions) || length(dimensions) ~= 2
                    warning('ExcelReport:InsertFigure','Invalid dimensions property, no figure inserted');
                    return
                end
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:InsertFigure','Excel handle not valid');
                return
            end

            % Write figure to Excel
            % ----------------------
            try
                % do not close figure
                close_me = false;
                % use pixel units
                set(figure_handle,'Units','pixels');
                % if handle points to a axes object, first copy content
                % into a new invisible figure
                if ishghandle(figure_handle(1),'axes')
                    % remember to close extra figure at the end
                    close_me = true;
                    % get content and position of original plot
                    axes_position = get(figure_handle(1),'Position');
                    axes_tightinset = get(figure_handle(1),'TightInset');
                    % create new figure
                    f = figure('visible','off');
                    set(f,'Position',[100, ...  %size of figure + buffer of 15pixels
                                      100, ...
                                      axes_position(3)+axes_tightinset(1)+axes_tightinset(3)+15, ...
                                      axes_position(4)+axes_tightinset(2)+axes_tightinset(4)+15]);
                    % copy axes object(s)
                    ax = [];
                    for i = 1:length(figure_handle)
                        ax(i) = copyobj(figure_handle(i),f);
                    end
                    % position main axes object
                    new_axes_position = get(ax(1),'Position');
                    new_axes_tightinset = get(ax(1),'TightInset');
                    set(ax(1),'Position',[new_axes_tightinset(1:2)+5,new_axes_position(3:4)]); % buffer of 5 pixels
                    %set(ax(1),'Units','normalized');
                    % position other objects
                    % compute offset between old and new position of main object
                    new_axes_position = get(ax(1),'Position');
                    offset = new_axes_position(1:2) - axes_position(1:2);
                    for i = 2:length(ax)
                        % add offset to all elements
                        pos = get(figure_handle(i),'Position');
                        set(ax(i),'Position',[pos(1:2)+offset,pos(3:4)]);
                    end
                    for i = 1:length(ax)
                        set(ax(i),'Units','Normalized');  % axes will stretch when resizing figure
                    end
                    % use this figure
                    figure_handle = f;
                end
                
                % Resize figure 
                if exist('dimensions','var')
                    pos = get(figure_handle,'Position');
                    set(figure_handle,'Position',[pos(1:2),dimensions(1),dimensions(2)]);
                end
                % Copy content of figure to clipboard
                set(figure_handle,'PaperPositionMode','auto');
                print(figure_handle,'-dmeta','-painters','-r600');  % vector format and 600dpi
                % Restore dimension of figure
                if exist('dimensions','var')
                    set(figure_handle,'Position',pos);
                end
                % Close figure?
                if close_me
                    close(figure_handle);
                end
                % Paste image in clipboard to Excel range
                r = this.xlsHandle.ActiveSheet.Range(range);
                r.Select;
                this.xlsHandle.ActiveSheet.Paste;
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:InsertFigure',ME.message);
            end
        end

        function insertPicture(this,range,filename,dimensions)
        % Insert a picture into Excel Report
        %
        % Input arguments:
        % range:        Range in worksheet to place the picture
        %                - e.g. 'A3','B4',[4,3] (equals 'C4')
        % filename:     Name of image file (e.g. jpg, gif, etc..)
        % dimensions:    Two element vector with custom width and height

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Argument checking
            if nargin < 3
                warning('ExcelReport:InsertPicture','Not enough input arguments, no picture inserted');
                return
            end
            if ~ischar(range) && ~isnumeric(range)
                warning('ExcelReport:InsertPicture','Range not valid, no picture inserted');
                return
            end
            if isnumeric(range)
                % replace row/column indeces by corresponding range
                switch numel(range)
                    case 2
                        range = this.index2range(range(1),range(2));
                    otherwise
                        warning('ExcelReport:InsertPicture','Numeric range not valid, no text inserted');
                        return
                end
            end
            if ~ischar(filename)
                warning('ExcelReport:InsertPicture','Filename not valid, no picture inserted');
                return
            end
            if ~exist(filename,'file')
                warning('ExcelReport:InsertPicture','Filename not valid, no picture inserted');
                return
            end
            % make sure we have absolute path to image
            filename = which(filename);
            if ~exist('dimensions','var')
                warning('ExcelReport:InsertPicture','Dimension of image not defined');
                return
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:InsertPicture','Excel handle not valid');
                return
            end

            % Write picture to Excel
            % ----------------------
            try
                % add rectangle shape
                s = this.xlsHandle.ActiveSheet.Shapes.AddShape(1,0,0,dimensions(1),dimensions(2));
                s.Line.Visible = 0;
                % add picture as background
                s.Fill.UserPicture(filename);
                % move to selected range
                s.Left = this.xlsHandle.ActiveSheet.Range(range).Left;
                s.Top = this.xlsHandle.ActiveSheet.Range(range).Top;

            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:InsertPicture',ME.message);
            end
        end
        
        function createPDF(this,filename)
        % Create a pdf file from current Excel report
        %
        % Input arguments:
        % filename:     Name of pdf report

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Argument checking
            if nargin < 2
                warning('ExcelReport:CreatePDF','Please enter filename');
                return
            end
            if ~ischar(filename)
                warning('ExcelReport:CreatePDF','Filename not valid');
                return
            end
            % add current directory if filename is relative
            if isempty(fileparts(filename))
                filename = fullfile(pwd,filename);
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:CreatePDF','Excel handle not valid');
                return
            end

            % Create pdf
            % ----------
            try
                % Use Excel function 'ExportAsFixedFormat' to convert selection
                xlTypePDF = 0;
                xlQualityStandard = 0;
                IncludeDocProperties = true;
                IgnorePrintAreas = false;
                this.xlsHandle.ActiveSheet.ExportAsFixedFormat(xlTypePDF,filename,xlQualityStandard,IncludeDocProperties,IgnorePrintAreas);
                
                % Note: if shapes are not rescaled in the pdf, install
                % hotfix KB973402. It's a MS bug.
               
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:CreatePDF',ME.message);
            end
        end
        
        function setColumnWidth(this,range,width)
        % Set the width of one or multiple columns
        %
        % Input arguments:
        % range: Range in worksheet (e.g. E:F or A)
        % width: New width of columns in range (leave empty for autofit)

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Argument checking
            if nargin < 2
                warning('ExcelReport:SetColumnWidth','Not enough input arguments, column width not set');
                return
            end
            if ~ischar(range)
                warning('ExcelReport:SetColumnWidth','Range not valid, column width not set');
                return
            end
            if ~exist('width','var')
                width = [];  % autofit
            end
            if ~isnumeric(width)
                warning('ExcelReport:SetColumnWidth','Width not valid, column width not set');
                return
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:SetColumnWidth','Excel handle not valid');
                return
            end

            % Set width
            % ---------
            try
                % we need a little trick here 
                % select a cell that is (most probably :) not merged with other cells 
                % (row 10000). Otherwise unwanted columns might be changed.

                % build range to select, either a single column or multiple
                [r1,rem_str] = strtok(range,':');
                range = [r1,'10000'];
                if ~isempty(rem_str)
                    r2 = rem_str(2:end);
                    range = [range,':',r2,'10000'];
                end
                this.xlsHandle.ActiveSheet.Range(range).Select;
                if isempty(width)
                    this.xlsHandle.Selection.EntireColumn.AutoFit;
                else
                    this.xlsHandle.Selection.columnWidth = width;
                end
                this.xlsHandle.ActiveSheet.Range('A1').Select;
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:SetColumnWidth',ME.message);
            end
        end

        function setRowHeight(this,range,height)
        % Set the height of one or multiple rows
        %
        % Input arguments:
        % range: Range in worksheet (e.g. 34:50 or 23)
        % height: New height of rows in range (leave empty for autofit)

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Argument checking
            if nargin < 2
                warning('ExcelReport:setRowHeight','Not enough input arguments, row height not set');
                return
            end
            if ~ischar(range)
                warning('ExcelReport:setRowHeight','Range not valid (must be string type), row height not set');
                return
            end
            if ~exist('height','var')
                height = [];  % autofit
            end
            if ~isnumeric(height)
                warning('ExcelReport:setRowHeight','Height not valid, row height not set');
                return
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:setRowHeight','Excel handle not valid');
                return
            end

            % Set height
            % ----------
            try
                % we need a little trick here 
                % select a cell that is (most probably :) not merged with other cells 
                % (column ZZ). Otherwise unwanted rows might be changed.

                % build range to select, either a single column or multiple
                [c1,rem_str] = strtok(range,':');
                range = ['ZZ',c1];
                if ~isempty(rem_str)
                    c2 = rem_str(2:end);
                    range = [range,':','ZZ',c2];
                end
                this.xlsHandle.ActiveSheet.Range(range).Select;
                if isempty(height)
                    this.xlsHandle.Selection.EntireRow.AutoFit;
                else
                    this.xlsHandle.Selection.rowHeight = height;
                end
                this.xlsHandle.ActiveSheet.Range('A1').Select;
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:setRowHeight',ME.message);
            end
        end        
        
        function setOrientation(this,orientation)
        % Set paper orientation
        %
        % Input arguments:
        % orientation: One of 'Portrait' / 'Landscape' or 0 / 1 respectively

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Argument checking
            if nargin < 2
                warning('ExcelReport:SetOrientation','Not enough input arguments, orientation not set');
                return
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:SetOrientation','Excel handle not valid');
                return
            end

            % Set orientation
            try
                if isnumeric(orientation)
                    if orientation == 0
                        orientation = 'xlPortrait';
                    else
                        orientation = 'xlLandscape';
                    end
                else
                    if strcmp(orientation,'Portrait')
                        orientation = 'xlPortrait';
                    else
                        orientation = 'xlLandscape';
                    end
                end
                this.xlsHandle.Activesheet.PageSetup.Orientation = orientation;
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:SetOrientation',ME.message);
            end

        end
        
        function setFitToPage(this,fittopageswide,fittopagestall)
        % Setup worksheet to fit to a number of pages
        %
        % Input arguments:
        % fittopageswide: Number of pages wide (default = 1)
        % fittopagestall: Number of pages tall (default = 1)

            % Get enabled flag
            if this.enabled == false
                return
            end
            % Get enabled flag
            if this.enabled == false
                return
            end
            % Argument checking
            if ~exist('fittopageswide','var')
                fittopageswide = 1; 
            end
            if ~isnumeric(fittopageswide)
                warning('ExcelReport:setFitToOnePage','Number of pages width not valid');
                return
            end
            if ~exist('fittopagestall','var')
                fittopagestall = 1; 
            end
            if ~isnumeric(fittopagestall)
                warning('ExcelReport:setFitToOnePage','Number of pages height not valid');
                return
            end
            % Get handle to Excel
            if ~isa(this.xlsHandle,'COM.Excel_Application')
                warning('ExcelReport:setFitToOnePage','Excel handle not valid');
                return
            end

            try
                % Change page setup to fit-to-one-page 
                this.xlsHandle.ActiveSheet.PageSetup.Zoom = false; 
                this.xlsHandle.ActiveSheet.PageSetup.FitToPagesWide = fittopageswide;
                this.xlsHandle.ActiveSheet.PageSetup.FitToPagesTall = fittopagestall;
                
            catch ME
                % connection to Excel failed
                % try to close Excel
                if isa(this.xlsHandle,'COM.Excel_Application')
                    try
                        this.closeReport();
                    catch
                    end
                end
                error('ExcelReport:setFitToOnePage',ME.message);
            end

        end
         
     end
    
    
    methods (Access = public, Static)
        
        function text_options = getDefaultTextOptions
        % --- Create default table options structure ---
            text_options.Grid = false;
            text_options.GridColor = [0,0,0];
            text_options.BGColor = [1,1,1];
            text_options.FGColor = [0,0,0];
            text_options.FontSize = 10;
            text_options.FontName = 'Arial';
            text_options.Bold = false;
            text_options.HorizontalAlignment = 'left';
            text_options.VerticalAlignment = 'top';     
        end
        
        function table_options = getDefaultTableOptions
        % Create default table options structure
            table_options.Title.Grid = false;
            table_options.Title.GridColor = [0,0,0];
            table_options.Title.BGColor = [1,1,1];
            table_options.Title.FGColor = [0,0,0];
            table_options.Title.FontSize = 10;
            table_options.Title.FontName = 'Arial';
            table_options.Title.Bold = false;
            table_options.Title.HorizontalAlignment = 'center';
            table_options.Title.VerticalAlignment = 'bottom';
            table_options.MajorGrid = false;
            table_options.MinorGrid = false;
            table_options.GridColor = [0,0,0];
            table_options.BGColor = [1,1,1];
            table_options.FGColor = [0,0,0];
            table_options.FontSize = 10;
            table_options.FontName = 'Arial';
            table_options.Bold = false;
            table_options.HorizontalAlignment = 'left';
            table_options.VerticalAlignment = 'bottom';
            table_options.MergeNumberOfColumns = [];
            % Note: if defined, number of elements of MergeNumberOfColumns 
            % matches number of columns in table data. Each element defines
            % the number of columns to merge per row before inserting data.
        end
        
        function range = index2range(row,col)
        % Return range info from row/column index
        %
        % Input arguments:
        % row: Row index
        % col: Column index

            range = '';
            % add column info as letters
            % -> maximum is column ZZ
            if col > 26
                range = [range,char(64+floor((col-1)/26))];
            end
            tmp = mod(col,26);
            if tmp == 0
                tmp = 26;
            end
            range = [range,char(64+tmp)];
            % add row info as number
            range = [range,num2str(row)];
        end
        
        function [row,col] = range2index(range)
        % Extract row and column index from range
        %
        % Input arguments:
        % range:  Range in worksheet (e.g. 'E3')

            index = find(double(range)<65,1);
            row = str2double(range(index:end));
            col_tmp = range(1:index-1);
            % -> maximum is column ZZ
            col = 0;
            if length(col_tmp)>1
                col = col + (double(col_tmp(1))-64)*26;
                col_tmp = col_tmp(2);
            end
            col = col + double(col_tmp)-64;
        end
        
    end
   
end











%         function addSheet(this,sheetname)
%         % Add/Activate selected sheet
%         %
%         % Input arguments:
%         % sheetname: Name of sheet to add
% 
%         % Get enabled flag
%         if this.enabled == false
%             return
%         end
%         % Get handle to Excel
%         if ~isa(this.xlsHandle,'COM.Excel_Application')
%             warning('ExcelReport:AddSheet','Excel handle not valid');
%             return
%         end
%         % Argument checking
%         if nargin < 2
%             warning('ExcelReport:AddSheet','Not enough input arguments, no sheet added');
%             return
%         end
%         if ~ischar(sheetname)
%             warning('ExcelReport:AddSheet','Sheet name not valid, no sheet added');
%             return
%         end
% 
%         % Add/activate sheet
%         % ------------------
%         try 
%             % Try to find sheet
%             found = false;
%             for sheetcount = 1:this.xlsHandle.ActiveWorkBook.Sheets.Count
%                 % try to find selected sheet
%                 if strcmp(this.xlsHandle.ActiveWorkBook.Sheets.Item(sheetcount).Name,sheetname)
%                     this.xlsHandle.ActiveWorkbook.Sheets.Item(sheetcount).Activate;
%                     found = true;
%                     break;
%                 end
%             end
%             if ~found
%                 sheet = this.xlsHandle.ActiveWorkBook.Sheets.Add;
%                 sheet.Name = sheetname;
%             end
%         catch ME
%             % connection to Excel failed
%             % try to close Excel
%             if isa(this.xlsHandle,'COM.Excel_Application')
%                 try
%                     this.closeReport();
%                 catch
%                 end
%             end
%             error('ExcelReport:AddSheet',ME.message);
%         end
%         end       

Contact us