perl读写Excel

背景

  测试过程中对测试数据、测试结果处理时，经常会遇到如下几种情况：

l         输入为txt、Excel等文件时，需要造一系列测试数据，写入文件中。

l         对保存在txt、Excel等文件中的用例执行结果进行验证。需要验证的数据很多时，往往是对其中的一部分进行验证。

l         将测试结果保存到Excel中，或绘制成图表。数据量很大时，要花费大量的时间处理数据，如果这个任务需要频繁的去执行，效率可想而知。

 

针对以上情况，为提高工作效率，最好使用工具来完成上面的任务。该工具应该可以：

l         读、写txt、Excel等文件。

l         按照一定规则生成测试数据，写入文件。

l         从文件中读取用例执行结果，与预期结果进行比较。

l         将测试结果按照一定格式或绘制成图表写入文件中。

其中，读、写txt、Excel等文件是最基本的，本文就是要讲述如何使用Perl语言及Win32::OLE模块来实现读、写Excel（至于txt等其他文本文件的解析，可以使用Perl+正则表达式来实现，不在本文中讲述）。

准备

安装Perl

  在下载perl的最新版本。本例中使用Perl v5.10.0。

操作系统为Windows Vista。

安装Perl 模块

  在上搜索并下载以下模块（下载最新版本）：

l         Win32::OLE

 

?         手动安装步骤：

l         安装VC。

l         下载的模块均为.tar.gz包，需要使用7-zip解压。

l         解压后，进入到目录。

l         $ Perl Makefile.PL

l         $ nmake

l         $ nmake test

l         $ nmake install

 

?         网络安装：

l         $ ppm install Win32::OLE 或 perl –MCPAN –e “install Win32::OLE”

 

  安装完成后，可以输入perldoc Win32::OLE查看使用说明。

实现

  OLE（Object Linking and Embedding）是一种基于COM的技术。OLE允许应用程序使用其他应用程序提供的通用接口来访问其组件和功能。而在Perl里使用Win32::OLE模块，来实现对OLE对象的支持。

  实现Automation接口的应用程 序（或者DLL动态链接库）被称作 Automation Server。而创建并使用Automation接口的应用程序被称做 Automation Controller 或者 Automation Client 。许多应用程序，比如 Microsoft Word，Excel，Access，以及其他 Office 应用程序等等，都已经实现了 Automation 接口。Automation 被广泛使用在 ASP，CGI 脚本中，通过 ADO 技术来存取某个 Data Respository。
    为了创建一个 Automation 对象，Automation Server 需要在系统中注册一下自己。一般来说，一个应用程序在安装时，已经帮你做了这一步，当然啦，即便没有，那么你也可以使用 regsrv32.exe 这样的工具来手动注册你的应用。这将在系统的注册表中插入一个条目，来告诉COM如何找到这个组件，它提供了什么类型的接口，它是什么类型的 Automation Server。你能在一个应用程序的文档中找到有关它的 Automation 对象，以及相关信息。使用Perl语法同样能够创建这样的对象并访问它的组件。

3.1.1 使用Win32::OLE

?         创建一个OLE Server对象

  首先，我们要创建一个可用来表示OLE Server的Perl对象。它的意思是：如果我们想控制其他应用程序的OLE对象，比如说Excel的，我们必须创建一个Perl对象用来表示Excel。所以，尽管我们的程序只是个OLE控制器，它应该包含用来描述OLE Server的对象。

  你可以使用Win32::OLE->new创建一个新的OLE Server对象。它接收程序ID（类似“Speech.VoiceText”）参数，并且返回一个Server对象：

 

my $server = Win32::OLE->new('Excel.Application', 'Quit');

 

  当你的程序不再使用这些Server对象时，有一些Server对象（特别是那些Microsoft Office应用程序）不会自动终止，它们需要一些Quit方法，这就是上面我们使用第二个参数的原因。

  你也可以直接将你的程序挂接到已经在运行的OLE Server上。

my $server = Win32::OLE->GetActiveObject('Excel.Application');

 

 

如果该Server不存在或因为一些原因拒绝连接，上面语句会执行失败（返回undef）。也可以使用固定的对象名（通常是文件名）来启动合适的Server并且把该对象加载到内存中。

 

my $server = Win32::OLE->GetObject('MyDocument.Doc');

 

 

?         调用方法

  一旦创建了一个Server对象，你需要调用它的方法使它工作。调用OLE的方法就像普通的Perl对象方法一样：

 

$server->Foo(@arguments);

 

   这是Perl方法的调用----但是它也会触发OLE对象的方法调用。当程序执行这个语句时，$server对象会执行它的Foo()方法。在应用程序的对象模型文档中找到可以使用的方法。

 

?         参数

  默认情况下，所有的参数是按位置的（e.g. foo($first, $second, $third)），而不是按名称的（e.g. foo(-name)=>”Yogi”, foo(-title)=>”Coach”）。必须输入的参数排在前面，接着是可选参数；如果你想让可选参数为假，使用undef。

  如果一个方法接收很多位置参数，这会变得很麻烦。可以使用名称参数来代替。

 

?         属性

  一个对象的属性可以使用如下方式存取：

 

$server->{Bar} = $value;

$value = $server->{Bar};

   

  这个例子是设置和查询Bar属性的值。也可以使用对象的Bar()方法来达到同样的效果：

 

$value = $server->Bar;

 

   然而你不能将第一行写为$server->Bar = $value，因为你不能改变一个方法的返回值。

3.2.1 九九乘法表

  将九九乘法表写在Excel中。

 

#! perl -w

use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; # die on erros . . .

my $excel = Win32::OLE->GetActiveObject('Excel.Application') ||
    Win32::OLE->new('Excel.Application', 'Quit'); # get already active # excel application
                                                                       # or open new
my $book = $excel->Workbooks->Add; # open excel file
my $sheet = $book->Worksheets(1); # select worksheet number 1

foreach my $i (1..9) {
my $j = 1;

    while ($j <= $i) {
        my $result = $j * $i;
        my $cell_content = "$j".' X '."$i".' = '."$result";

        $sheet->Cells($i, $j)->{Value} = $cell_content; # set value of cells

        $j ++;
    }
}

$book->SaveAs('D:\\test.xlsx');
$book->Close;

 

3.2.2 系统性能分析

  在Linux服务器上，记录应用程序app-for-test在9：20---15：35时间段内的CPU、内存使用情况，保存到log中。读取log中的时间、CPU使用率和内存使用率，并写入Excel中，方便绘制图表。

  每隔3秒记录一次CPU、内存使用率，并保存到日志。

#! /bin/bash

PID=`ps aux | grep –v grep | grep app-for-test | awk ‘{print $2}’`  # get the pid

top –b –d3 –p $PID > cpu_mem_status.log    # record the %CPU %MEM every 3 seconds

 

  日志内容如下：

图3.2-1 日志内容

 

  图中标红线的是需要提取出的数据，且第一行的时间和后面第一次出现的%CPU、%MEM为一组有效数据。提取这些数据主要是利用正则表达式，这里不再详述。接下来，将分析如何把这些数据写入Excel及绘制图表。

 

#! perl -w

use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; # die on erros


############################################################################78
# Read from "cpu_mem_status.log" and store the data in hash
my %data = (rows => 6,
              columns => 3,
              1 => ['Time', '%CPU', '%MEM'],
              2 => ['09:24:57', 0.3, 1.0],
              3 => ['09:25:00', 1.2, 2.0],
              4 => ['09:25:03', 1.3, 2.0],
              5 => ['09:25:06', 1.4, 2.1],
              6 => ['09:25:09', 1.5, 2.2],
             );

my $excel = Win32::OLE->GetActiveObject('Excel.Application') ||
               Win32::OLE->new('Excel.Application', 'Quit');


# 关掉Excel的提示，比如是否保存之类的。
$excel->{DisplayAlerts} = 'False';

# Workbooks 对象的方法，可以参考Excel VBA教程中Workbooks集合中的方法
my $Book = $excel->Workbooks->Add;
$Book->SaveAs('D:\\cpucputest.xlsx');
$Book->Close;

my $book = $excel->Workbooks->Open('D:\\\cpucputest.xlsx');
my $sheet = $book->Worksheets(2);
my $range = $sheet->Range('A1:C5');

foreach my $i ( 1..$data{'rows'} ) {
    foreach my $j (1..$data{'columns'}) {
        $sheet->Cells($i, $j)->{'Value'} = $data{$i}[$j-1];
    }
}

my $chart = $excel->Charts->Add; # Add chart
$chart->{'ChartType'} = xlLineMarkers; # Set chart type
$chart->SetSourceData( {Source => $range,
                             PlotBy => xlColumns} );
$chart->{'HasTitle'} = 1;
$chart->ChartTitle->{Text} = 'Cpu, Memory status'; # Set chart title

$book->Save;
$book->Close;

Excel的内容及生成的图表如下：

图3.2-2 Excel内容

图3.2-3 图表样式

本文转载自：http://blog.csdn.net/will2ni/article/details/3895677

附录

  参考资料：

http://dev.csdn.net/article/45/45678.shtm