第三章 MATLAB与Excel文件的数据交换

郑志勇(www.ariszheng.com)

1   案例背景

Excel是一款非常优秀的通用表格软件，在学习、工作与科研中大量的数据可能都是以excel表格的方式存储的。Excel在矩阵计算、数据拟合与优化算法等方面的功能尚不足，Excel与Matlab相结合是处理复杂数据问题的有效方法。 如何利用matlab强大的数值计算功能处理excel中的数据，首要解决的问题就是如何将excel中的数据导入到matlab中或将matlab数值计算的结果转存入excel中，本章主要介绍以函数方式与exlink宏的两种方法实现matlab与excel的数据交互。

2  数据交互函数

2.1 获取文件信息xlsfinfo函数

在读取excel目标数据文件前，可以通过xlsfinfo函数获取该文件的相关信息，为matlab函数的后续操获得有效信息（例如，文件类型、文件内部结构、相关的软件版本等）。

xlsinfo函数语法：

[typ, desc, fmt] = xlsfinfo(filename)

 

输入参数：

Filename：目标文件地址（若文件在matlab当前的工作目录中，Filename为’文件名’，如果文件不在matlab当前的工作目录中，filename为’文件路径\文件名’，例如：E:\other\案例书籍\abc.xls）

 

输出参数：

Typ：目标文件类型

Desc：目标文件内部表名称（sheetname）

Fmt：支持目标文件的软件版本

 

测试函数：m文件CaseXlsfinfo.m

%code by ariszheng@gmail.com

10-6-22

%%

%文件名称“excel.xls”

[typ, desc, fmt] = xlsfinfo('excel.xls')

%文件在当前工作目录下，直接输入文件名称即可。

system('taskkill /F /IM EXCEL.EXE');

注释：由于作者matlab2009a与excel2007数据交互时，每次使用xls类函数，都会重新开启一个excel进程，若反复使用xls类函数会导致系统中多个excel进程并存，消耗系统资源，导致系统运行速度下降，故作者使用system('taskkill /F /IM EXCEL.EXE')调用windows的taskkill函数关闭刚使用的excel进程。在2011以后的版本中，经测试上述问题已不存在。

结果输出：

typ =

Microsoft Excel Spreadsheet

%文件类别为excel文件

desc =

    'Sheet1'    'Sheet2'    'Sheet3'

%文件中数据表为  'Sheet1'    'Sheet2'    'Sheet3'

fmt =

xlExcel8

%文件版本为xlExcel8版本 对应的为excel 97~2003版本

成功: 已终止进程 "EXCEL.EXE"，其 PID 为 5508。

2.2 读取数据xlsread函数

Matlab从excel中读取数据的函数为xlsread，xlsread函数是使用频率较高的函数之一。

xlsread函数语法

1．[ data,textdate]= xlsread(filename)

 

输入参数：

Filename：目标文件地址（若文件在matlab当前的工作目录中，Filename为’文件名’，如果文件不在matlab当前的工作目录中，filename为’文件路径\文件名’）

 

输出参数：

Data: 数值数据

Textdate: 文字数据

 

表1：目标文件excel.xls内容

测试函数m文件CaseXlsRead.m，测试excel文件内容如表1。

%调用xlsread函数

[data,textdate]= xlsread('excel.xls')

%textdate的第一列为日期文本，第一行为列名称。

Hs300Date=textdate(2:14,1)

11以后的版本可不添加此行命令

system('taskkill /F /IM EXCEL.EXE')

结果输出：

data =

  1.0e+003 *

    1.0000    0.9940

    0.9828    0.4180

    0.9926    0.1740

    0.9832    0.2280

……

    0.9675    0.1830

    0.9747    0.9730

    0.9672    0.3140

textdate =

    'date'         'price'    'Vol'

    '2005-1-4'          ''       ''

    '2005-1-5'          ''       ''

……

    '2005-1-19'         ''       ''

    '2005-1-20'         ''       ''

成功: 已终止进程 "EXCEL.EXE"，其 PID 为 5208。

 

2．data= xlsread(filename, sheet, range)

输入参数：

Filename：目标文件地址（若文件在matlab当前的工作目录中，Filename为’文件名’，如果文件不在matlab当前的工作目录中，filename为’文件路径\文件名’）

Sheet：数据表名称，例如excel默认表名称sheet1。

Range：数据所在位置，例如A1，B13等

 

输出参数：

Data: 数值数据

测试函数m文件CaseXlsRead.m

%数据位置为excel.xls文件 表1  位置为B3:B14的列数据

Hs300Price=xlsread('excel.xls', 1, 'B3:B14')

system('taskkill /F /IM EXCEL.EXE')

Hs300Vol=xlsread('excel.xls', 1, 'C3:C14')

%数据位置为excel.xls文件 表1  位置为C3:C14的列数据

system('taskkill /F /IM EXCEL.EXE')

结果输出：

Hs300Price =

  982.7900

  992.5600

……

  967.4500

  974.6900

  967.2100

 

成功: 已终止进程 "EXCEL.EXE"，其 PID 为 2432。

Hs300Vol =

   418

  ……

   994

   740

   183

   973

   314

成功: 已终止进程 "EXCEL.EXE"，其 PID 为 980。

注释：data= xlsread(filename, sheet, range)形式的xlsread无法读取指定单元格中的非数值内容。若excel中的两列数据一列数据偏大一列偏小。按[ data,textdate]= xlsread(filename)方法导入后，偏大的用科学技术法表示，小的就都成了0.0000, 建议在读取前将excel中数值的格式修改为普通格式。

2.3 写入数据xlswrite函数

 Matlab往excel中写入数据的函数为xlswrite

xlswrite函数语法

[status, message] = xlswrite (filename, M, sheet, range)

输入参数：

Filename：目标文件地址（若文件在matlab当前的工作目录中，Filename为’文件名’，如果文件不在matlab当前的工作目录中，filename为’文件路径\文件名’）；

M: 写入excel中的数据，M存储数据的变量名称；

Sheet: 写入excel中的sheet名称（ 可选，若空默认sheet1）；

Range：写入excel中的单元格区域（可选，若空默认’A1’）；

输出参数：

status: 写入状态

      “1”表示写入成功

“0”表示写入失败

message: 若失败，则显现失败信息

例如：

message =

    message: [1x117 char]

    identifier: 'MATLAB:xlswrite:LockedFile'

 表示目标文件被锁定无法写入（例如，目标文件被其他程序占用时，系统会锁定目标文件），解决方法是关闭excel程序，若还出现上述问题可在任务管理器中结束excel进程。

 

测试函数,m文件CaseXlsWrite.m

%code by ariszheng@gmail.com

10-6-22

%%

%生产随机数据

X=randn(1,10);

%将X随机数据写入excel文件，表“sheet2”中

[status, message]=xlswrite('excel.xls', X, 'sheet2')

system('taskkill /F /IM EXCEL.EXE')

结果输出

status =

 

     1 %表示写入成功

message =

 

       message: ''

    identifier: ''

 

成功: 已终止进程 "EXCEL.EXE"，其 PID 为 368。

如果写入的是字符，使用’aa’与{’aa’}的效果完全不同，’aa’得到的结果是excel两个单元格都是’a’, {’aa’}得到的结果是excel一个单元格是’aa’。

[status, message] = xlswrite ('funddata.xls', {'aa'},'sheet2')

[status, message] = xlswrite ('funddata.xls', {'aa'},'sheet2')

2.4  交互界面uiimport函数

   在新版的 Matlab中提供了界面化的数据交互功能。

Uiimport函数的语法

1.       Uiimport

在命令窗口输入uiimport命令，出现文件选择窗口；

2．uiimport(filename)

   在命令窗口输入uiimport（filename），表示打开数据文件filename；

3．uiimport('-file')

   在命令窗口输入uiimport('-file')，表示在当前文件夹内选择数据文件；

4．uiimport('-pastespecial')

   在命令窗口输入uiimport('-pastespecial')，表示打开当前剪贴板中的数据；

5．S = uiimport(...)

   表示将数据文件按结构存储在S中

函数测试：

〉〉Uiimport

http://www.xfunddata.com/fmatlab/chapter-3.files/image002.jpg

图1 Select Source

选择要打开的数据文件funddata.xls

http://www.xfunddata.com/fmatlab/chapter-3.files/image004.jpg

图2 Import Data

点击“打开”Matlab将会把数据读取

http://www.xfunddata.com/fmatlab/chapter-3.files/image006.jpg

图3 Import Wizard

接着点击“Next”，根据提示数据就读进Matlab中，在Matlab可以根据需求对数据进行计算。

data: [488x12 double] %存储数值内容

textdata: {491x13 cell} %储存非数值内容

3 Excel-Link宏

如果excel文件数据量太大（1GB）以上，使用函数进行数据交互存在一定问题，例如Java内存溢出等等。数据量较大的时候可以使用Excel-Link宏进行数据交互。Matlab提供使其能与excel互动操作的Excel-link宏。Excel-link使得数据在matlab与excel之间随意交换，以及在excel下调用matlab的函数。Excel-link将matlab的强大的数值计算功能、数据可视化功能与excel的数据Sheet功能结合在一起。下面就简单介绍Excel-link的基本操作：

http://www.xfunddata.com/fmatlab/chapter-3.files/image008.jpg

图4： Excel-link功能原理图

 

3.1加载Excel-link宏

http://www.xfunddata.com/fmatlab/chapter-3.files/image010.jpg

图 5：exlink加载方法示意图

      加载方法：在excel工具à 加载宏 à浏览（matlab的安装路径）àtoolbox文件夹

àexlink文件夹à excllink.xla文件（双击打开）。

http://www.xfunddata.com/fmatlab/chapter-3.files/image012.jpg

图 6： exlink加载方法示意图

回到加载宏界面：在Excel link2.3 for use with matlab 选项上打勾，点击确定。

http://www.xfunddata.com/fmatlab/chapter-3.files/image014.jpg

 图7: xlink加载方法示意图

若excel的左上方出现startmatlab、putmatrix、getmatrix、evalstring 等选项，说明excel-link加载成功。

3.2使用Excel-link宏

Startmatlab：点击启动matlab

Putmatrix：  将Excel的数据传输到matlab中

http://www.xfunddata.com/fmatlab/chapter-3.files/image016.jpg

图8: exlink使用方法示意图

在matlab中：可看到传入到matlab中的矩阵x，就算y=sin(x)

http://www.xfunddata.com/fmatlab/chapter-3.files/image018.jpg

图9: exlink使用方法示意图

Getmatrix：将matlab的数据传输到excel中

http://www.xfunddata.com/fmatlab/chapter-3.files/image020.jpg

图 10: exlink使用方法示意图

点击getmatrix出入要传入的矩阵变量名称，确定得

http://www.xfunddata.com/fmatlab/chapter-3.files/image022.jpg

图 11 exlink使用方法示意图

Evalstring：执行string的matlab命令，具体可以参看matlab的help

3.3 Excel2007加载与使用宏

加载方法：

点击excel的office按钮à点击excel选项à在加载项中 点击转到见下图

http://www.xfunddata.com/fmatlab/chapter-3.files/image024.jpg

图12 excel2007加载exlink

浏览（matlab的安装路径）àtoolbox文件夹àexlink文件夹à excllink.xla文件（打开）

 

使用方法：

在excel2007加载项下可以发现exlink相关的按钮，具体使用方法与exlink在excel2003中的使用方法一样。

http://www.xfunddata.com/fmatlab/chapter-3.files/image026.jpg

图13 excel2007使用exlink

 

 

4 交互实例

4.1 基金相关性的计算

 例：funddata.xls中存储着沪深300  指数的价格与博时主题行业、嘉实沪深300、南方绩优成长的复权数据，要求计算出每只基金的与沪深300指数的相关性。

注释：基金的收益率、波动率的计算应该采用基金的复权净值（即分红再投资净值）。由于基金存在分红，即分红前后基金净值存在较大差距，将对基金收益率与波动率计算造成影响，所以要使用复权净值进行计算。

M程序FundCorrelationCase.m

%compute FundCorrelation

%code by ariszheng@gmail.com

12-4-24

%文件信息

[typ, desc, fmt] = xlsfinfo('funddata.xls')

%读取数据

[data,textdate]= xlsread('funddata.xls');

%计算相关性

R = corrcoef(data)

%写入到excel数据

[status, message] = xlswrite ('funddata.xls', R, 'sheet2', 'B2:E5')

%行名称与列名称

textdate=textdate(2,2:5)

[status, message] = xlswrite ('funddata.xls', textdate, 'sheet2', 'B1:E1')

[status, message] = xlswrite ('funddata.xls', textdate’, 'sheet2', 'A2:A5')

%textdate’ 表示转置即将行变为列

运行结果：

 

 

typ =

 

Microsoft Excel Spreadsheet

 

 

desc =

 

    'Sheet1'    'Sheet2'

 

 

fmt =

 

xlExcel8

 

 

R =

 

    1.0000    0.9039    0.9988    0.9658

    0.9039    1.0000    0.8862    0.9730

    0.9988    0.8862    1.0000    0.9548

    0.9658    0.9730    0.9548    1.0000

 

 

status =

 

     1

 

 

message =

 

       message: ''

    identifier: ''

 

 

textdate =

 

    '沪深300'    '博时主题行业'    '嘉实沪深300'    '南方绩优成长'

 

 

status =

 

     1

 

 

message =

 

       message: ''

    identifier: ''

 

 

status =

 

     1

 

 

message =

 

       message: ''

    identifier: '

4.2多个文件的读取和写入

          在实际的项目编程中，很多时候遇到从很多文件中读取数据，若逐个文件进行手工操作不仅身心疲惫，而且容易出错。例如，指数成分股与权重数据每天一个Excel，文件名为：000016weightnextday20100104.xls、000016weightnextday20110630.xls，……，000016weightnextday20120104.xls等，程序化读取的关键是将文件名自动化。以以下程序为例：

 

%XlsReadData

%Code by Ariszheng

12-4-26

clear;

clc;

DataNum=9; %要读取文件数量

Data.Code=zeros(50,DataNum); %定义变量并分配内存

Data.ClosePrice=zeros(50,DataNum);

Data.CFMValue=zeros(50,DataNum);

Data.Weight=zeros(50,DataNum);

fileName='000016weightnextday';%文件名固定部分

fileDate=[20100104 20100630 20100701 20101231 20110104  20110630  20110701  20111230 20120104]; %文件名变化部分，若变化部分由规律可以自动生成。

for i=1:DataNum

    TfileName=[fileName,num2str(fileDate(i)),'.xls'];%组合文件名，i不同文件名称不同。

    %读取文件中所需的数据

    Data.Code(:,i)=xlsread(TfileName,'Index Constituents Data','E2:E51' );

    Data.ClosePrice(:,i)=xlsread(TfileName,'Index Constituents Data','M2:M51' );

    Data.CFMValue(:,i)=xlsread(TfileName,'Index Constituents Data','P2:P51' );

    Data.Weight(:,i)=xlsread(TfileName,'Index Constituents Data','Q2:Q51' );

end