function FillGrid(Spreadsheet1,M,DoPaste,DoWaitbar,MinR,MinC,MaxR,MaxC,AsStr)
% FILLGRID populates a grid, i.e. Microsoft Spreadsheet Object, with a cell array
% The cell array may contain mixed types.
%    FILLGRID(SPREADSHEET,M) populates SPREADSHEET with M
%    FILLGRID(SPREADSHEET,M,DOPASTE) usage obsolete
%    FILLGRID(SPREADSHEET,M,DOPASTE,DOWAITBAR) also uses a progress
%       indicator if DOWAITBAR is 1 (default), does not if DOWAITBAR is 0
%    FILLGRID(SPREADSHEET,M,DOPASTE,DOWAITBAR,MINR,MINC,MAXR,MAXC) specifies
%       the minimum and maximum rows and cloumns to use
%    FILLGRID(SPREADSHEET,M,DOPASTE,DOWAITBAR,MINR,MINC,MAXR,MAXC,ASSTR)
%       forces all values to be placed as strings, instead of the default
%       behavior which populates the grid with numbers when possible.
%
%    Example:
%       figure;
%       spreadsheet = actxcontrol('OWC11.Spreadsheet.11',[0 0 560 420]);
%       M = {'abc', 123; 12.17, logical(1)};
%       FillGrid(spreadsheet,M);

TRUE = 1;
FALSE = 0;
TEST = TRUE;

if nargin<4 DoPaste = TRUE; end;
if nargin<5 DoWaitbar = TRUE; end;
if nargin<6 MinR =   0; end;
if nargin<7 MinC =   0; end;
if nargin<8 MaxR = 9e99; end;
if nargin<9 MaxC = 9e99; end;
if nargin<10 AsStr=0; end;

% USE AN ENUMERATED TYPE WORKAROUND
ClassEnumType = {'cell','char','logical','numeric'};

% FIND THE ACTIVE SHEET
ActSheet = get(Spreadsheet1,'ActiveSheet');

% CLEAR CONTENTS
ActSheet.Cells.ClearContents;

% FIND THE ACTIVE CELL, ROW AND COLUMN
% THIS IS NOT NECESSARY, BUT IT IS DONE FOR DEMONSTRATION
ActCell = get(Spreadsheet1,'ActiveCell');
ActCellRow = 1;%get(ActCell,'Row');
ActCellColumn = 1;%get(ActCell,'Column');

if DoWaitbar h = waitbar(0,'Please wait...'); end;
[L,W] = size(M);
MinR = max(MinR,1);
MinC = max(MinC,1);
MaxR = min(L,MaxR);
MaxC = min(W,MaxC);
DoPaste=0;
if DoPaste
    for i=1:10
        for j=1:10
            if ~isstr(M{i,j})
                if M{i,j} == floor(M{i,j})
                    M2{i,j}=sprintf('%d',M{i,j});
                else
                    M2{i,j}=sprintf('%f',M{i,j});
                end;
            end;
        end;
    end;
    clipboard('copy',M2);
    ActCellRow=1;
    ActCellColumn=1;
    XLfmt = nn2an(ActCellRow,ActCellColumn);
    Select(Range(ActSheet,XLfmt));
    Spreadsheet1.ActiveCell.Paste
else
    if DoWaitbar MaxRC = MaxR.*MaxC; end;
    for r = MinR:MaxR
        for c = MinC:MaxC
            if DoWaitbar
                i = c+W.*(r-1);
                waitbar(i./MaxRC,h,sprintf( ...
                    'Posting %d of %d (%d%% done)', ...
                    i,MaxRC,floor(100.*i./MaxRC)));
            end;

            % Select current cell
            XLfmt = nn2an(ActCellRow+r-1,ActCellColumn+c-1);
            Select(Range(ActSheet,XLfmt));

            % Assign value
            ActCell = get(Spreadsheet1,'ActiveCell');
            if putcellvalue(ActCell,M(r,c),AsStr) break; end;
            % Re-select starting cell
            XLfmt = nn2an(ActCellRow,ActCellColumn);
            Select(Range(ActSheet,XLfmt));

        end;
    end;
end;
if DoWaitbar close(h); end;



function DoBreak = putcellvalue(ActCell,M,AsStr)
% PUTCELLVALUE puts a value in an active cell
%    PUTCELLVALUE(ACTCELL,M) puts M in ACTCELL
%    PUTCELLVALUE(ACTCELL,M,ASSTR) converts M to a string before putting
%       it if ASSTR = 1, leaves it as is if ASSTR = 0 (default)
%
%    PUTCELLVALUE handles all classes and is used by FILLGRID.
%
% IT'S NOT FANCY, BUT IT WORKS
%
%    See also Graph_and_Table, QuestDlgWithGrid, SearchAndReplaceMany,
%       SpreadSheet, DatabaseEditingTool, DuplicateFileFinder,
%       PutCellValue, nn2an, FillGrid
%
%    Keywords: grid spreadsheet ActiveX Active-X Active X GUI Table
%       graph_and_table plot graph table grid object flexgrid
%       msflexgrid ocx tabular

% Michael Robbins
% robbins@bloomberg.net
% michaelrobbins1@yahoo.com


if nargin<3 AsStr=0; end;

if AsStr prefix = ''; else prefix = ''''; end;
DoBreak=0;
switch class(M)
    case 'cell', % DE-CELLIZE
        if prod(size(M)) > 1
            errordlg('bad cell');
        else
            putcellvalue(ActCell,M{:},AsStr);
        end;
    case 'char', % POST (DATES WILL POST NUMERICLY UNLESS AsStr==1
        set(ActCell,'Value',[prefix M]); DoBreak=1;
    case 'logical', % LOGICAL -> INT
        putcellvalue(ActCell,int32(M),AsStr);
    otherwise
        if isnumeric(M)
            if AsStr % # -> STRING
                if isinteger(M) fstr='%d'; else fstr='%f'; end;
                putcellvalue(ActCell,sprintf(fstr,M),AsStr);
            else % POST
                set(ActCell,'Value',M);
            end;
        else
            errordlg('bad class');
        end;
end;



function cr = nn2an(r,c)
% Thanks Brett Shoelson, via CSSM

t = [floor((c - 1)/26) + 64 rem(c - 1, 26) + 65];
if(t(1)<65), t(1) = []; end
cr = [char(t) num2str(r)];