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