www.gusucode.com > mbctools 工具箱 matlab 源码程序 > mbctools/xregExcel.m

    function varargout = xregExcel(action, varargin)
%XREGEXCEL Interface for reading and writing Excel sheets.
%
%   [OUT1, ...] = XREGEXCEL(ACTION, ARG1, ...) provides an interface for
%   reading and writing data to an Excel sheet.

%  Copyright 2000-2016 The MathWorks, Inc. and Ford Global Technologies, Inc.

persistent excelVer;

switch lower(action)
    case 'start'
        [varargout{1},excelVer] = i_startExcel;
        varargout{2} = excelVer;
    case 'prepare'
        if excelVer == 97
            i_preparePage97(varargin{:});
        end
    case 'read'
        if excelVer == 97
            [varargout{1:nargout}] = i_readExcelSheet97(varargin{:});
        end
    case 'export'
        if excelVer == 97
            i_exportExcelSheet97(varargin{:});
        end
    case {'cancel' 'close'}
        i_closeExcel(varargin{:})
end

% --------------------------------------------------------------------
%
% --------------------------------------------------------------------
function [excelStruct, excelVer] = i_startExcel
% Create a new worksheet. Doing it this way ensures that all calls to
% excel.sheet go to the same COM server so only one COM instance of EXCEL
% is instantiated

svr = actxserver('excel.application');
% Which version of excel are we attached to?
try
    svr.UserControl = true;
    excelVer = 97;   % Office 97/2000
    bks = svr.workbooks.Add;
catch ME
    error('Error starting Excel: %s',ME.message);
end
excelStruct.dataBook = bks;
excelStruct.Server = svr;

% --------------------------------------------------------------------
%
% --------------------------------------------------------------------
function i_preparePage97(excelStruct)
% Note extra calls to release in this code ensure that no activeX handle are
% left hanging around, which would crash EXCEL when it is closed
activeBook = excelStruct.dataBook;
% Make the application visible
server = get(activeBook, 'application');

activeSheet = get(activeBook, 'activeSheet');
% Set the name of the Active Sheet
set(activeSheet,'Name','MBC Data');

% Get the first two rows
r12 = get(activeSheet,'range','1:2');
set(r12.interior,'colorindex', 24);
set(r12.borders, 'colorindex', 0);
release(r12);

col1 = get(activeSheet,'columns',1);
set(col1.font, 'bold', 1);
set(col1.font,'size',12);
release(col1);

c12 = get(activeSheet, 'range', 'A1:A2');
set(c12.interior, 'colorindex', 17);
set(c12.font,'bold',1);
set(c12.font,'size',12);
release(c12);

c13 = get(activeSheet,'range','A1:A3');
c13.value = {'Name:'; 'Unit:'; 'Data:'};
release(c13);

set(server, 'Visible', 1);

release(activeSheet);
release(server);

% --------------------------------------------------------------------
%
% --------------------------------------------------------------------
function [names, units, doubleData] = i_readExcelSheet97(excelStruct, worksheet)

% Read from the excelStruct active sheet
USE_EXCEL_STRUCT = nargin == 1;
if USE_EXCEL_STRUCT
    worksheet = get(excelStruct.dataBook, 'activeSheet');
end

try
    currentRange = worksheet.UsedRange.CurrentRegion;
catch %#ok<CTCH>
    error(message('mbc:xregExcel:ProtectedSheet'));
end


if USE_EXCEL_STRUCT
    release(worksheet);
end

BLOCK_SIZE = 400;

% Getting data from excel can introduce activeX warnings
warn = warning('off','all');

% Does A1 contain the string 'Name:', if so then data starts in column B
% So remove first column of data by resizeing and offsetting the range
A1 = get(currentRange, 'Resize', 1, 1);
v = A1.value;
if ischar(v) && strcmpi(v, 'Name:') || strcmpi(v, 'Names:')
    currentRange = get(currentRange, 'Offset', 0, 1);
    if currentRange.Columns.Count>1
        % Only remove a column from the range if there is more than one
        currentRange = get(currentRange, 'Resize', [], currentRange.Columns.Count - 1);
    end
end

% Get values and text from first two lines to check for names and units
headerRange = get(currentRange, 'Resize', 2, []);
headerText = headerRange.Formula;
headerValues = headerRange.Value;

nHeaders = 0;

% Get names and units
IsBlank = cellfun('isempty', headerText);
IsText = cellfun('isclass', headerValues, 'char') & ~IsBlank;
if any(IsText(1,:))
    nHeaders = nHeaders+1;
    names = headerText(1,:);
    names(IsBlank(1,:)) = {'VAR'};
    
    if any(IsText(2,:))
        nHeaders = nHeaders+1;
        units = headerText(2,:);
    else
        if all(IsBlank(2,:))
            nHeaders = nHeaders+1;
        end
        units = repmat({''}, size(names));
    end
elseif ~all(IsBlank(:))
    % Initialise names and units to size of data
    names = cell(1, size(headerValues,2));
    names(:) = {'VAR'};
    units = repmat({''}, size(names));
else
    names = {};
    units = {};
end

doubleData = zeros(0, length(names));
blockHasMoreData = true;
dataRange = -1;
if size(doubleData, 2)==0
    % No columns of data found
    blockHasMoreData = false;
elseif currentRange.Rows.Count<=2
    % Possible blank units row.  If so the range to import needs to be
    % moved down
    nextCell = get(currentRange, 'Offset', 2, 0);
    nextCell = get(nextCell, 'Resize', 1, 1);
    if ~isempty(nextCell.Text)
        nHeaders = nHeaders+1;
        dataRange = get(currentRange, 'Offset', nHeaders, 0);
        dataRange = dataRange.CurrentRegion;
    else
        % Move range down past the headers that we found
        if nHeaders>=currentRange.Rows.Count
            blockHasMoreData = false;
        else
            dataRange = get(currentRange, 'Offset', nHeaders, 0);
            dataRange = get(dataRange, 'Resize', currentRange.Rows.Count-nHeaders, []);
        end
    end
else
    % Move range down past the headers that we found
    dataRange = get(currentRange, 'Offset', nHeaders, 0);
    dataRange = get(dataRange, 'Resize', currentRange.Rows.Count-nHeaders, []);   
end
warning(warn);

% This loop loads the data in chunks to avoid excessive memory usage.
while blockHasMoreData
    [blockData, dataRange, blockHasMoreData] = i_getBlockOfRange(dataRange, BLOCK_SIZE);
    if iscell(blockData)
        % Convert to a double matrix so make sure everything is a double
        dataNotDouble = ~cellfun('isclass', blockData, 'double') | cellfun('isempty', blockData);
        blockData(dataNotDouble) = {NaN};


        % Note that blockDoubleData = [blockData{:}] is v. slow for large cells
        blockDoubleData = zeros(size(blockData));
        for i = 1:numel(blockData)
            blockDoubleData(i) = blockData{i};
        end
    else
        blockDoubleData = blockData;
    end
    % Concatenate this data onto the whole
    doubleData = [doubleData ; blockDoubleData]; %#ok<AGROW>
end
release(currentRange);
release(headerRange);
if ishandle(dataRange)
    release(dataRange);
end


% --------------------------------------------------------------------
%
% --------------------------------------------------------------------
function [data, range, hasMoreData] = i_getBlockOfRange(range, numRowsRequested)
% How many rows in the range
numRows = range.rows.count;
% Should we get a smaller range
if numRows > numRowsRequested
    reducedRange = get(range, 'Resize', numRowsRequested);
    hasMoreData = true;
    range = get(range, 'Offset', numRowsRequested);
    range = get(range, 'Resize', numRows - numRowsRequested);
else
    reducedRange = range;
    hasMoreData = false;
end
% Get the data from the reduced range
data = reducedRange.value;

% --------------------------------------------------------------------
%
% --------------------------------------------------------------------
function i_exportExcelSheet97(excelStruct, SS)
if excelStruct.Server.Version<12
    % pre 2007
    size_lim = [2^16-2, 255];
else
    % 2007 or later
    size_lim = [2^20-2, 2^14-1];
end
size_SS = size(SS);
if any(size_SS(1:2)>size_lim)
    warnstr = sprintf(['Excel only supports data with a maximum of %d variables and' ...
        ' %d rows.  The exported data has been truncated due to this limitation.'], ...
        size_lim);
    hW = warndlg(warnstr, 'Export warning', 'modal');
    waitfor(hW);

    if size_SS(1)>size_lim(1)
        SS = SS(1:size_lim(1), :);
    end
    if size_SS(2)>size_lim(2)
        SS = SS(:, 1:size_lim(2));
    end
end

i_preparePage97(excelStruct);

worksheet = excelStruct.dataBook.activesheet;

names = get(SS, 'Name');
units = get(SS, 'Units');
data = double(SS);

numCols = length(names);
nameRangeStart = get(worksheet, 'cells', 1, 2);
nameRangeEnd = get(worksheet, 'cells', 1, numCols+1);
unitRangeStart = get(worksheet, 'cells', 2, 2);
unitRangeEnd = get(worksheet, 'cells', 2, numCols+1);
dataRangeStart = get(worksheet, 'cells', 3, 2);
dataRangeEnd = get(worksheet, 'cells', size(data, 1)+2, numCols+1);

r = get(worksheet, 'range', nameRangeStart, nameRangeEnd);
r.value = names(:)';
release(r); release(nameRangeStart); release(nameRangeEnd);

r = get(worksheet, 'range', unitRangeStart, unitRangeEnd);
r.value = units(:)';
release(r); release(unitRangeStart); release(unitRangeEnd);

r = get(worksheet, 'range', dataRangeStart, dataRangeEnd);
r.value = data;
% BUG in export that doesn't write NaN and Inf correctly so replace with
% empty cells
r.value(~isfinite(data)) = {''};

release(r); release(dataRangeStart); release(dataRangeEnd);

release(worksheet);

% --------------------------------------------------------------------
%
% --------------------------------------------------------------------
function i_closeExcel(excelStruct)
activeBook = excelStruct.dataBook;
try %#ok<TRYNC>
    server = get(activeBook, 'application');
    server.UserControl = false;
    activeBook.Close(false);
    release(activeBook);
    % If no workbooks are left open then close Excel
    % Make the application visible
    workbooks = get(server, 'workbooks');
    if get(workbooks, 'count') == 0
        invoke(server,'quit');
    end
    release(workbooks);
    release(server);
end