Code covered by the BSD License  

Highlights from
Generation of Random Variates

image thumbnail

Generation of Random Variates

by

James Huntley (view profile)

 

generates random variates from over 870 univariate distributions

[data, text, rawData, customOutput]=xlsread1(file,sheet,range,mode,customFun)
function [data, text, rawData, customOutput]=xlsread1(file,sheet,range,mode,customFun)
% XLSREAD Get data and text from a spreadsheet in an Excel workbook.
%   [NUMERIC,TXT,RAW]=XLSREAD(FILE) reads the data specified in the Excel 
%   file, FILE. The numeric cells in FILE are returned in NUMERIC, the text 
%   cells in FILE are returned in TXT, while the raw, unprocessed cell 
%   content is returned in RAW.  
%
%   [NUMERIC,TXT,RAW]=XLSREAD(FILE,SHEET,RANGE) reads the data specified
%   in RANGE from the worksheet SHEET, in the Excel file specified in FILE. 
%   It is possible to select the range of data interactively (see Examples
%   below). Please note that the full functionality of XLSREAD depends on 
%   the ability to start Excel as a COM server from MATLAB. 
%
%   [NUMERIC,TXT,RAW]=XLSREAD(FILE,SHEET,RANGE,'basic') reads an XLS file as
%   above, using basic input mode. This is the mode used on UNIX platforms
%   as well as on Windows when Excel is not available as a COM server.  
%   In this mode, XLSREAD does not use Excel as a COM server, which limits
%   import ability. Without Excel as a COM server, RANGE will be ignored
%   and, consequently, the whole active range of a sheet will be imported. 
%   Also, in basic mode, SHEET is case-sensitive and must be a string.
%
%   [NUMERIC,TXT,RAW]=XLSREAD(FILE,SHEET,RANGE,'',CUSTOMFUN)
%   [NUMERIC,TXT,RAW,CUSTOMOUTPUT]=XLSREAD(FILE,SHEET,RANGE,'',CUSTOMFUN)
%   When the Excel COM server is used, allows passing in a handle to a
%   custom function.  This function will be called just before retrieving 
%   the actual data from Excel. It must take an Excel Range object (e.g. of
%   type 'Interface.Microsoft_Excel_5.0_Object_Library.Range') as input,
%   and return one as output.  Optionally, this custom function may return
%   a second output argument, which will be returned from XLSREAD as the
%   fourth output argument, CUSTOMOUTPUT.  For details of what is possible 
%   using the EXCEL COM interface, please refer to Microsoft documentation.
%
%   INPUT PARAMETERS:
%   FILE: string defining the file to read from. Default directory is pwd.
%         Default extension is 'xls'.
%   SHEET: string defining worksheet name in workbook FILE.
%          double scalar defining worksheet index in workbook FILE. See
%          NOTE 1.
%   RANGE: string defining the data range in a worksheet. See NOTE 2.
%   MODE: string enforcing basic import mode. Valid value = 'basic'.  This
%   is the mode always used when COM is not available (e.g. on Unix).
%
%   RETURN PARAMETERS:
%   NUMERIC = n x m array of type double.
%   TXT = r x s cell string array containing text cells in RANGE.
%   RAW = v x w cell array containing unprocessed numeric and text data.
%   Both NUMERIC and TXT are subsets of RAW.
%
%   EXAMPLES:
%   1. Default operation:  
%      NUMERIC = xlsread(FILE);
%      [NUMERIC,TXT]=xlsread(FILE);
%      [NUMERIC,TXT,RAW]=xlsread(FILE);
%
%   2. Get data from the default region:
%      NUMERIC = xlsread('c:\matlab\work\myspreadsheet')
%
%   3. Get data from the used area in a sheet other than the first sheet:
%      NUMERIC = xlsread('c:\matlab\work\myspreadsheet','sheet2')
%
%   4. Get data from a named sheet:
%      NUMERIC = xlsread('c:\matlab\work\myspreadsheet','NBData')
%
%   5. Get data from a specified region in a sheet other than the first
%      sheet:
%      NUMERIC = xlsread('c:\matlab\work\myspreadsheet','sheet2','a2:j5')
% 
%   6. Get data from a specified region in a named sheet:
%      NUMERIC = xlsread('c:\matlab\work\myspreadsheet','NBData','a2:j5')
% 
%   7. Get data from a region in a sheet specified by index:
%      NUMERIC = xlsread('c:\matlab\work\myspreadsheet',2,'a2:j5')
% 
%   8. Interactive region selection:
%      NUMERIC = xlsread('c:\matlab\work\myspreadsheet',-1);
%      You have to select the active region and the active sheet in the
%      EXCEL window that will come into focus. Click OK in the Data 
%      Selection Dialog when you have finished selecting the active region.
%
%   9. Using the custom function:
%      [NUMERIC,TXT,RAW,CUSTOMOUTPUT] = xlsread('equity.xls', ..., @MyCustomFun)
%      Where the CustomFun is defined as:
%
%      function [DataRange, customOutput] = MyCustomFun(DataRange)
%         DataRange.NumberFormat = 'Date';
%         customOutput = 'Anything I want';
%     
%      This will convert to dates all cells where that is possible.
%
%   NOTE 1: The first worksheet of the workbook is the default sheet. If 
%         SHEET is -1, Excel comes to the foreground to enable interactive 
%         selection (optional). In interactive mode, a dialogue will prompt 
%         you to click the OK button in that dialogue to continue in MATLAB. 
%          (Only supported when Excel COM server is available.)
%   NOTE 2: The regular form is: 'D2:F3' to select rectangular region D2:F3 
%         in a worksheet. RANGE is not case sensitive and uses Excel A1 
%         notation (see Excel Help). (Only supported when Excel COM server 
%         is available.)
%   NOTE 3: Excel formats other than the default can also be read.
%          (Only supported when Excel COM server is available.)
%
%   See also XLSWRITE, CSVREAD, CSVWRITE, DLMREAD, DLMWRITE, TEXTSCAN.

%   Copyright 1984-2007 The MathWorks, Inc.
%   $Revision: 1.23.4.24 $  $Date: 2007/12/06 13:30:15 $
%=============================================================================

Excel = evalin('base','Excel'); % added command (Brandao 12/09/2008)
% initialise variables
data = [];
text = {};
rawData = {};

Sheet1 = 1;
if nargin < 2
    sheet = Sheet1;
    range = '';
elseif nargin < 3
    range = '';
end

% handle input values
if nargin < 1 || isempty(file)
    error('MATLAB:xlsread:FileName','Filename must be specified.');
end

if ~ischar(file)
    error('MATLAB:xlsread:InputClass','Filename must be a string.');
end

if nargin > 1
    % Verify class of sheet parameter
    if ~ischar(sheet) && ...
            ~(isnumeric(sheet) && length(sheet)==1 && ...
              floor(sheet)==sheet && sheet >= -1)
        error('MATLAB:xlsread:InputClass',...
            'Sheet argument must a string or an integer.');
    end

    if isequal(sheet,-1)
        range = ''; % user requests interactive range selection.
    elseif ischar(sheet)
        if ~isempty(sheet)
            % Parse sheet and range strings
            if isempty(strfind(sheet,':'))
            else
                range = sheet; % only range was specified. 
                sheet = Sheet1;% Use default sheet.
            end
        else
            sheet = Sheet1; % set sheet to default sheet.
        end
    end
end
if nargin > 2
    % verify class of range parameter
    if ~ischar(range)
        error('MATLAB:xlsread:InputClass',...
            'Range argument must a string. See HELP XLSREAD.');
    end
end
if nargin >= 4
    % verify class of mode parameter
    if ~isempty(mode) && ~(strcmpi(mode,'basic'))
        warning('MATLAB:xlsread:InputClass',...
            'Import mode string is invalid. XLSREAD resets mode to normal.');
        mode = '';
    end
else
    mode = '';
end

custom = false;
if nargin >= 5 
    if strcmpi(mode,'basic') || ~ispc
        warning('MATLAB:xlsread:Incompatible',...
         ['Custom functions cannot be used in basic mode or on non-Windows platforms.\n'...
          'The custom function argument will be ignored.'])
    elseif ~isa(customFun,'function_handle')
            warning('MATLAB:xlsread:NotHandle', ...
                'The fifth argument to XLSREAD must be a function handle.');
    else
        custom = true;
    end
end 
    
%==============================================================================
% block command crossed out (Brandao 12/09/2008)
% handle requested Excel workbook filename
% try
%     file = validpath(file,'.xls');
% catch exception
%     err = MException('MATLAB:xlsread:FileNotFound','XLSREAD unable to open file %s.\n%s',...
%                            file,exception.message);
%     throw(err);
% end
%==============================================================================
% select import mode from either normal or basic mode.
if strcmpi(mode,'basic') || ~ispc
    warning('MATLAB:xlsread:Mode',...
        ['XLSREAD has limited import functionality on non-Windows platforms\n'...
            'or in basic mode.  Refer to HELP XLSREAD for more information.']);
    try
		if nargout > 2
	        [data,text,rawData] = xlsreadold(file,sheet);
		else
			[data,text] = xlsreadold(file,sheet);
		end
    catch exception
        if isempty(exception.identifier)
            exception = MException('MATLAB:xlsreadold:FormatError','%s', exception.message);
        end
        throw(exception);
    end
    return;
% else  % block command crossed out (Brandao 12/09/2008)
%     % Attempt to start Excel as ActiveX server process.
%     try
%         Excel = actxserver('excel.application');
%     catch exc1
%         % revert to old XLSREAD that uses BIFFREAD
%         warning('MATLAB:xlsread:ActiveX',...
%             ['Could not start Excel server for import. '...
%                 'Refer to documentation.']);
%         try
% 			if nargout > 2
% 				[data,text,rawData] = xlsreadold(file,sheet);
% 			else
% 				[data,text] = xlsreadold(file,sheet);
% 			end
%         catch exc2
%             message=sprintf('%s\n%s', exc1.message, exc2.message);
%             if isempty(exc2.identifier)
%                 exception = MException('MATLAB:xlsreadold:FormatError', message);
%             else
%                 exception = MException(exc2.identifier, message);                
%             end
%             throw(exception);
%         end
%         return;
%     end
end
%==============================================================================
try
     % open workbook
    Excel.DisplayAlerts = 0; 
    
    
    %Workaround for G313142.  For certain files, unless a workbook is
    %opened prior to openiong the file, various COm calls return an error:
    %0x800a9c64.  The line below works around this flaw.  Since we have
    %seen only one example of such a file, we have decided not to incur the
    %time penalty involved here.
%     aTemp = Excel.workbooks.Add(); aTemp.Close();
    
%     try % block command crossed out (Brandao 12/09/2008)
%         ExcelWorkbook = Excel.workbooks.Open(file,0,true);
%     catch exception  %#OK
%         %do not pollute lasterror state
%     end
    
%     % block command crossed out (Brandao 12/09/2008)
%     format = ExcelWorkbook.FileFormat;
%     if  strcmpi(format, 'xlCurrentPlatformText') == 1
%         error('MATLAB:xlsread:FileFormat', 'File %s not in Microsoft Excel Format.', file);
%     end

    if nargin >= 2
        % User specified at least a worksheet or interactive range selection.
        if ~isequal(sheet,-1)
            % Activate indicated worksheet.
            activate_sheet(Excel,sheet);

            try % importing a data range.
                if ~isempty(range)
                    % The range is specified.
                    Select(Range(Excel,sprintf('%s',range)));
                    DataRange = get(Excel,'Selection');
                else
                    % Only the worksheet is specified. 
                    % Activate upper left cell on sheet. 
                    Activate(Range(Excel,'A1'));
                    
                    % Select range of occupied cells in active sheet.
                    DataRange = Excel.ActiveSheet.UsedRange;
                end
            catch % data range error.
                error('MATLAB:xlsread:RangeSelection',...
                    'Data range is invalid.');
            end

        else
            % User requests interactive range selection.
            % Set focus to first sheet in Excel workbook.
            activate_sheet(Excel,Sheet1);

            % Make Excel interface the active window.
            set(Excel,'Visible',true);

            % bring up message box to prompt user.
            uiwait(warndlg({'Select data region in Excel worksheet.';...
                    'Click OK to continue in MATLAB'},...
                    'Data Selection Dialogue','modal'));
            DataRange = get(Excel,'Selection');
            set(Excel,'Visible',false); % remove Excel interface from desktop
        end
    else
        % No sheet or range or interactive range selection. 
        % Activate default worksheet.
        activate_sheet(Excel,Sheet1);
        
        % Select range of occupied cells in active sheet.
        DataRange = Excel.ActiveSheet.UsedRange;
    end

    %Call the custom function if it was given.  Provide customOutput if it
    %is possible.
    if custom
		if nargout(customFun) < 2
			DataRange = customFun(DataRange);
            customOutput = {};
		else
			[DataRange, customOutput] = customFun(DataRange);	
		end
	end
	
    % get the values in the used regions on the worksheet.
    rawData = DataRange.Value;
    % parse data into numeric and string arrays
    [data,text] = parse_data(rawData); 
    
catch exception
%     try % block command crossed out (Brandao 12/09/2008)
%         ExcelWorkbook.Close(false); % close workbook without saving any changes
%     catch exc2  %#OK
%         %Do not pollute lasterror state
%     end
    rethrow(exception);	% rethrow original error
end
    
% try % block command crossed out (Brandao 12/09/2008)
%     ExcelWorkbook.Close(false); % close workbook without saving any changes
%     %This call could fail if the file is "locked".  This is the same
%     %message you would get if you opened the file in Excel, and then tried
%     %to close the workbook (NOT the application).
%     Excel.Quit;
% catch exception
%     warning(exception.identifier, '%s', exception.message);
%     Excel.Quit;
% end

%--------------------------------------------------------------------------
function [numericArray,textArray] = parse_data(data)
% PARSE_DATA parse data from raw cell array into a numeric array and a text
% cell array.
% [numericArray,textArray] = parse_data(data)
% Input:
%        data: cell array containing data from spreadsheet
% Return:
%        numericArray: double array containing numbers from spreadsheet
%        textArray: cell string array containing text from spreadsheet
%==========================================================================

% ensure data is in cell array
if ischar(data)
    data = cellstr(data);
elseif isnumeric(data) || islogical(data)
    data = num2cell(data);
end

% Check if raw data is empty
if isempty(data)
    % Abort when all data cells are empty.
    textArray = {};
    numericArray = [];
    return
else
    % Trim empty leading and trailing rows
    % find empty cells
    emptycells = cellfun('isempty',data);
    nrows = size(emptycells,1);
    firstrow = 1;
    % find last of leading empty rows
    while (firstrow<=nrows && all(emptycells(firstrow,:)))
         firstrow = firstrow+1;
    end
    % remove leading empty rows
    data = data(firstrow:end,:);
    
    % find start of trailing empty rows
    nrows = size(emptycells,1);
    lastrow = nrows;
    while (lastrow>0 && all(emptycells(lastrow,:)))
        lastrow = lastrow-1;
    end
    % remove trailing empty rows
    data = data(1:lastrow,:);
    
    % find start of trailing NaN rows
    warning('off', 'MATLAB:nonIntegerTruncatedInConversionToChar');
    while (lastrow>0 && ~(any(cellfun('islogical', data(lastrow,:)))) && ...
                        all(isnan([data{lastrow,:}])))
        lastrow = lastrow-1;
    end
    warning('on', 'MATLAB:nonIntegerTruncatedInConversionToChar');
    % remove trailing NaN rows    
    data=data(1:lastrow,:);
    
    [n,m] = size(data);
    textArray = cell(size(data));
    textArray(:) = {''};
end

vIsNaN = false(n,m);

% find non-numeric entries in data cell array
vIsText = cellfun('isclass',data,'char');
vIsNaN = cellfun('isempty',data)|strcmpi(data,'nan')|cellfun('isclass',data,'char');

% place text cells in text array
if any(vIsText(:))
    textArray(vIsText) = data(vIsText);
else
    textArray = {};
end
% Excel returns COM errors when it has a #N/A field.
textArray = strrep(textArray,'ActiveX VT_ERROR: ','#N/A');

% place NaN in empty numeric cells
if any(vIsNaN(:))
    data(vIsNaN)={NaN};
end

% extract numeric data
data = reshape(data,n,m);
rows = size(data,1);
m = cell(rows,1);
% Concatenate each row first
for n=1:rows
    m{n} = cat(2,data{n,:});
end
% Now concatenate the single column of cells into a matrix
numericArray = cat(1,m{:});

    
% trim all-NaN leading rows and columns from numeric array
% trim all-empty trailing rows and columns from text arrays
[numericArray,textArray]=trim_arrays(numericArray,textArray);

% ensure numericArray is 0x0 empty.
if isempty(numericArray)
    numericArray = [];
end

%--------------------------------------------------------------------------
function activate_sheet(Excel,Sheet)
% Activate specified worksheet in workbook.

% Initialise worksheet object
WorkSheets = Excel.sheets;

% Get name of specified worksheet from workbook
try
    TargetSheet = get(WorkSheets,'item',Sheet);
catch
    error('MATLAB:xlsread:WorksheetNotFound',...
          'Specified worksheet was not found.');
end

%Activate silently fails if the sheet is hidden
set(TargetSheet, 'Visible','xlSheetVisible');
% activate worksheet
Activate(TargetSheet);

%--------------------------------------------------------------------------
function [matrixResult,cellResult,rawResult]=xlsreadold(filename,sheet)
% Basic import mode. Range specification not available.
% Interactive range selection not available.
% Read Excel file as binary image file
if nargin > 1
    if isequal(sheet,1) || isequal(sheet,-1)
        sheet = ''; 
    elseif ~ischar(sheet)
        error('MATLAB:xlsread:WorksheetNotFound',...
            'In basic mode, sheet argument must be a string.');
    end
end
% read XLS file
biffvector = biffread(filename);

% get sheet names
[data, names] = biffparse(biffvector);

% if the names array is empty, this is an old style biff record with 
% no sheet name.  Just return data and empty text cell array.
if isempty(names) 
    matrixResult = data;
    cellResult = cell(names);
	if nargout > 2
	    rawResult = num2cell(data);
	end
    return;
end

if nargin == 1 || isempty(sheet)
    % just get the first sheet
    [n, s] = biffparse(biffvector, names{1});
else
    % try to read this sheet
    try
        [n, s] = biffparse(biffvector, sheet);
    catch
        error('MATLAB:xlsread:WorksheetNotFound',...
            'Specified worksheet was not found.');
    end
end

% trim trailing empty text cells and NaN matrix elements
[matrixResult, cellResult] = trim_arrays(n,s);
% replace empty text cells with char([]).
cellResult(cellfun('isempty',cellResult))={''};

if nargout > 2
	% create raw data return
	if isempty(s)
		rawResult = num2cell(n);
	else
		rawResult = cell(max(size(n),size(s)));
		rawResult(1:size(n,1),1:size(n,2)) = num2cell(n);
		for i = 1:size(s,1)
			for j = 1:size(s,2)
				if (~isempty(s{i,j}) && (i > size(n,1) || j > size(n,2) || isnan(n(i,j))))
					rawResult(i,j) = s(i,j);
				end
			end
		end
	end
	% trim all-empty-string leading rows from raw array
	while size(rawResult,1)>1 && all(cellfun('isempty',rawResult(1,:)))
		rawResult = rawResult(2:end,:);
	end
	% trim all-empty-string leading columns from raw array
	while size(rawResult,2)>1 && all(cellfun('isempty',rawResult(:,1)))
		rawResult = rawResult(:,2:end);
	end
	% replace empty raw data with NaN, to comply with specification
	rawResult(cellfun('isempty',rawResult))={NaN};
end	

%--------------------------------------------------------------------------
function [numericArray,textArray] = trim_arrays(numericArray,textArray)
% trim leading rows or cols
% if the string result has dimensions corresponding to a column or row of
% zeros in the matrix result, trim the zeros.
if ~isempty(numericArray) && ~isempty(textArray)
    [mn, nn] = size(numericArray);
    [ms, ns] = size(textArray);

    if ms == mn
        % trim leading column(textArray) from numeric data
        firstcolm = 1;
        while (firstcolm<=nn && all(isnan(numericArray(:,firstcolm))))
            firstcolm = firstcolm+1;
        end
        numericArray=numericArray(:,firstcolm:end);
    end

    if ns == nn
        % trim leading NaN row(s) from numeric data
        firstrow = 1;
        while (firstrow<=mn && all(isnan(numericArray(firstrow,:))))
            firstrow = firstrow+1;
        end
        numericArray=numericArray(firstrow:end,:);
        
        % trim leading empty rows(s) from text data
        firstrow = 1;
        while (firstrow<=ms && all(cellfun('isempty',textArray(firstrow,:))))
            firstrow = firstrow+1;
        end
        textArray=textArray(firstrow:end,:);
    end
    
    % trim all-empty-string trailing rows from text array
	lastrow = size(textArray,1);
    while (lastrow>0 && all(cellfun('isempty',textArray(lastrow,:))))
        lastrow = lastrow-1;
    end
	textArray=textArray(1:lastrow,:);
    
    % trim all-empty-string trailing columns from text array
	lastcolm = size(textArray,2);
    while (lastcolm>0 && all(cellfun('isempty',textArray(:,lastcolm))))
        lastcolm = lastcolm-1;
    end
	textArray=textArray(:,1:lastcolm);

    % trim all-NaN trailing rows from numeric array
	lastrow = size(numericArray,1);
    while (lastrow>0 && all(isnan(numericArray(lastrow,:))))
        lastrow=lastrow-1;
    end
	numericArray=numericArray(1:lastrow,:);
    
    % trim all-NaN trailing columns from numeric array
	lastcolm = size(numericArray,2);
    while (lastcolm>0 && all(isnan(numericArray(:,lastcolm))))
        lastcolm=lastcolm-1;
    end
	numericArray=numericArray(:,1:lastcolm);
end

Contact us