xlsread对于纯数据的xls文件支持很完美,也就是说当xls文件里的每个格子都是“数”时,xlsread会直接返回一个实数矩阵。但是通 常我们拿到xls文件并不是这样,它的表头多半是描述性文字,它的数据也有可能是文字,有些位置的数据还有可能是缺失的。xlsread对这样的文件读取 无能为力,或者说需要大量的时间去协调数据的位置信息。要是有一个函数,能够按照原有的顺序直接读取所有的单位格数据就好了。当然,这时候返回的矩阵就不能是一个数值矩阵了,它将会是一个cell矩阵,里面的每个元素类型可能不一样。

matlab本身并不提供这个功能,但是另外有一个函数officedoc完美的实现这个功能。这个函数包可以去上去下载,解压缩后放到工作路径上即可。使用方法可以查询help officedoc。officedoc是收费函数包,但有免费版本,而且其免费版本可以实现上面我们所说的效果(收费版本主要是可以用来修改office文件)。




Excel = actxserver('Excel.Application');

set(Excel, 'Visible', 1);

Workbooks = Excel.Workbooks;

Workbook = invoke(Workbooks, 'Open', [cd,'\feature\ABC.xls']);



feature1 = ddereq(read_excel, 'R1c1:R40c40');



invoke(Excel, 'Quit');







XLSREAD Get data and text from a spreadsheet in an Excel workbook.[NUMERIC,TXT,RAW]=XLSREAD(FILE) reads the data specified in the Excelfile, FILE. The numeric cells in FILE are returned in NUMERIC, the textcells in FILE are returned in TXT, while the raw, unprocessed cellcontent is returned in RAW.[NUMERIC,TXT,RAW]=XLSREAD(FILE,SHEET,RANGE) reads the data specifiedin RANGE from the worksheet SHEET, in the Excel file specified in FILE.It is possible to select the range of data interactively (see Examplesbelow). Please note that the full functionality of XLSREAD depends onthe ability to start Excel as a COM server from MATLAB.[NUMERIC,TXT,RAW]=XLSREAD(FILE,SHEET,RANGE,'basic') reads an XLS file asabove, using basic input mode. This is the mode used on UNIX platformsas well as onwhen Excel is not available as a COM server.In this mode, XLSREAD does not use Excel as a COM server, which limitsimport ability. Without Excel as a COM server, RANGE will be ignoredand, 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 acustom function.This function will be called just before retrievingthe actual data from Excel. It must take an Excel Range object (e.g. oftype 'Interface.Microsoft_Excel_5.0_Object_Library.Range') as input,and return one as output.Optionally, this custom function may returna second output argument, which will be returned from XLSREAD as thefourth output argument, CUSTOMOUTPUT.For details of what is possibleusing 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. SeeNOTE 1.RANGE: string defining the data range in a worksheet. See NOTE 2.MODE: string enforcing basic import mode. Valid value = 'basic'.Thisis 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 firstsheet: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 theEXCEL window that will come into focus. Click OK in the DataSelection 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. IfSHEET is -1, Excel comes to the foreground to enable interactiveselection (optional). In interactive mode, a dialogue will promptyou 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:F3in a worksheet. RANGE is not case sensitive and uses Excel A1notation (see Excel Help). (Only supported when Excel COM serveris 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.

Reference page in Help browserdoc xlsread


a comma separated value file.M = CSVREAD('FILENAME') reads a comma separated value formatted fileFILENAME.The result is returned in M.The file can only containnumeric values.M = CSVREAD('FILENAME',R,C) reads data from the comma separated valueformatted file starting at row R and column C.R and C are zero-based so that R=0 and C=0 specifies the first value in the file.M = CSVREAD('FILENAME',R,C,RNG) reads only the range specifiedby RNG = [R1 C1 R2 C2] where (R1,C1) is the upper-left corner ofthe data to be read and (R2,C2) is the lower-right corner.RNGcan also be specified using spreadsheet notation as in RNG = 'A1..B7'.CSVREAD fills empty delimited fields with zero.Data files wherethe lines end with a comma will produce a result with an extra lastcolumn filled with zeros.See also csvwrite, dlmread, dlmwrite, load, fileformats, textscan.

Reference page in Help browserdoc csvread



Are there any examples that show how to use the ActiveX automation interface to connect MATLAB to Excel?

Problem Description

I am trying to control Excel from MATLAB using ActiveX. Are there any examples that show how to use the ActiveX automation interface from Excel to do this?


Most of the functionality that you get from ActiveX is dependent on the object model, which the external application implements. Consequently, we are usually unable tp provide much information about the functions that you need to use in the remote application to perform a particular function. We do, however, have an example that shows how to do perform common functions in Excel.

We also recommend that you become more familiar with the Excel object model in order to better use Excel's ActiveX automation interface from MATLAB. You can find more information on this interface by selecting the "Microsoft Excel Visual Basic Reference" topic in the Microsoft Excel Help Topic dialog. This topic area contains a searchable description of Excel methods and properties.

The following example demonstrates how to insert MATLAB data into Excel. It also shows how to extract some data from Excel into MATLAB. For more information, refer to the individual comments for each code segment.

% Open Excel, add workbook, change active worksheet,

% get/put array, save, and close

% First open an Excel Server

Excel = actxserver('Excel.Application');

set(Excel, 'Visible', 1);

% Insert a new workbook

Workbooks = Excel.Workbooks;

Workbook = invoke(Workbooks, 'Add');

% Make the second sheet active

Sheets = Excel.ActiveWorkBook.Sheets;

sheet2 = get(Sheets, 'Item', 2);

invoke(sheet2, 'Activate');

% Get a handle to the active sheet

Activesheet = Excel.Activesheet;

% Put a MATLAB array into Excel

A = [1 2; 3 4];

ActivesheetRange = get(Activesheet,'Range','A1:B2');

set(ActivesheetRange, 'Value', A);

% Get back a range. It will be a cell array,

% since the cell range can

% contain different types of data.

Range = get(Activesheet, 'Range', 'A1:B2');

B = Range.value;

% Convert to a double matrix. The cell array must contain only scalars.

B = reshape([B{:}], size(B));

% Now save the workbook

invoke(Workbook, 'SaveAs', 'myfile.xls');

% To avoid saving the workbook and being prompted to do so,

% uncomment the following code.

% Workbook.Saved = 1;

% invoke(Workbook, 'Close');

% Quit Excel

invoke(Excel, 'Quit');

% End process


There are several options for connecting MATLAB with Excel. For an example that shows how to connect MATLAB with Excel using Excel Link, please refer to the following URL:

For an example that shows how to connect MATLAB with Excel using DDE, please refer to the following URL:

For information on how to use the XLSREAD function to read .xls files, please refer to the following URL:

在Matlab GUI中读取数据或其它文件

假设在GUI(in Matlab)中设计好一按钮,点击以后弹出对话框,并希望获取来自电脑上任一文件夹下的数据或其它文件。


[filename, pathname] = uigetfile;if (filename==0 & pathname==0)msgbox('您没有选择文件,请重新选择!','打开文件出错','error');elseNumericalDatum=load([pathname, filename]);

% NumericalDatum=xlsread([pathname, filename]);

%然后把从计算机其它文件夹读取的数据文件保存到当前文件夹save NumericalDatum.dat NumericalDatum -ascii;

% xlswrite('NumericalDatum.xls',NumericalDatum);




%最后再从当前文件夹将数据读取出来,用于当前程序load NumericalDatum.dat;

% NumericalDatum=xlsread('NumericalDatum.xls');

评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页