在matlab中ungetfile后,matlab读取excel文件及其数据处理

转自

http://blog.chinaunix.net/uid-25067956-id-1743352.html

在许多时候我们要借助于matlab读取excel的内容进行处理，以下是一种常用的处理方法

office的表格文件也就是xls文件本质上就是一个二维矩阵，二维矩阵是用来保存数据的最佳方式，所以在日常工作中，我们从其它地方获取的数据通常都被保存为xls格式，但处理数据时，我们却需要把xls文件的数据导入到matlab里进行处理。

如果你只处理一个文件并且只做一次的话，你可以手动来拷贝粘贴，这花费不了你太多时间。如果有很多xls文件，或者你的xls文件的内容可能随时被修改，那么下面的方法可以派上用场。

matlab自身提供了大量的函数，包括读取office文件。其中xlsread和xlswrite就是专门用来读取xls文件里的数据的。这两个函数的使用方法可以直接查看matlab自带的帮助。

xlsread对于纯数据的xls文件支持很完美，也就是说当xls文件里的每个格子都是“数”时，xlsread会直接返回一个实数矩阵。但是通

常我们拿到xls文件并不是这样，它的表头多半是描述性文字，它的数据也有可能是文字，有些位置的数据还有可能是缺失的。xlsread对这样的文件读取

无能为力，或者说需要大量的时间去协调数据的位置信息。要是有一个函数，能够按照原有的顺序直接读取所有的单位格数据就好了。当然，这时候返回的矩阵就不能是一个数值矩阵了，它将会是一个cell矩阵，里面的每个元素类型可能不一样。

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

例子：

在matlab中读取xls格式的文件内容如应用如下函数:

1.bb=xlsread('c:feature.xls','a0:an40'),其中:c:feature.xls为文件存放的地址,a0:a40为将要读取的单元格的范围.bb为读取的矩阵在MATLAB中的变量名.

2.使用m文件脚本如下:

Excel =

actxserver('Excel.Application');

set(Excel,

'Visible', 1);

Workbooks =

Excel.Workbooks;

Workbook =

invoke(Workbooks, 'Open',

[cd,'\feature\ABC.xls']);

%%读取ABC.xls:sheet1a1(即R1C1)~an40(即R240c40)范围内的40by40矩阵

read_excel=ddeinit('excel','ABC.xls:sheet1');

feature1 =

ddereq(read_excel, 'R1c1:R40c40');

feature1

%%关闭ABC.xls

invoke(Excel,

'Quit');

delete(Excel);

注意:在使用时将m文件与xls文件存于同一个目录下.另外:sheet1:可以重命名,且读取sheet的名称要和实际存放的名称相同.

matlab读取excel,txt文件函数

注意matlab不识别中文，读写的文件中最好不含有中文

excel读取函数xlsread

text读取函数csvread

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

onWindowswhen

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

CSVREAD

Read

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

Matlab如何读取Excel表格数据

Subject: 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? Solution: 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 delete(Excel); 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: http://www.mathworks.com/support/solutions/data/27338.shtml For an example that shows how to connect MATLAB with Excel using

DDE, please refer to the following URL: http://www.mathworks.com/support/solutions/data/31072.shtml For information on how to use the XLSREAD function to read .xls

files, please refer to the following

URL:

在Matlab

GUI中读取数据或其它文件

假设在GUI(in

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

做法：假设要读取在桌面上一名叫ATR_ALL.dat的数据文件，并将其值赋到Data变量，其程序代码为：

[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);

msgbox('打开及读取数据完毕!','确认','warn');end

......

在新的应用文件中

%最后再从当前文件夹将数据读取出来，用于当前程序load

NumericalDatum.dat;

%

NumericalDatum=xlsread('NumericalDatum.xls');