Perl与EXCEL
题记：Perl处理数据很厉害，但是它与EXCEL之间还是有一道鸿沟，跨过这条鸿沟固然可以，但是没有那么容易。在工作中，为了不像机器人或者傻瓜一样在那里做一些低附加值工作，这里学习写一篇文章。（MacBook Air）
刚开始并没有目录这回事，后来发现如果没有目录实在是太难找了。因此便有了这个目录，方便自己查找。目录：
1，安装能够专门处理EXCEL的Perl模块
2，查看已安装的Perl模块
3，获取某个文件夹下所有Excel文件的名字：
4，Excel表格中某一行的特定连续几列求和：
5，怎么样复制Excel文件到原位置？
6，找出两列数据中重复的字段：
7，在两列一一对应的数据对中摘取其中一部分（VLOOKUP）
1，安装能够专门处理EXCEL的Perl模块：
首先明确进行EXCEL操作的常见模块为以下三个：Spreadsheet::ParseExcel，Spreadsheet::WriteExcel，Spreadsheet::XLSX；其次这里采用联网的方式在线安装，请保持网络畅通；最后操作之前请确保已安装CPAN，可以在Bash中输入“perldoc CPAN”以查看是否已安装，若安装即可，没有安装请使用命令“perl -MCPAN -e shell”安装。1，找到终端Bash。2，在Bash中输入：sudo su （输入密码）；3，在Bash中输入：cpan；4，在Bash中输入：install Spreadsheet::ParseExcel；5，在Bash中输入：perldoc Spreadsheet::ParseExcel；备注：第二步是为了获得Root权限，只有这个权限才能操作一些东西，不然安装不了会出问题；第四步需要等待比较长的时间，请耐心；第五步是为了查看是否安装成功了。
2，查看已安装的Perl模块：
如果你知道这个模块的名字，那么请输入：perldoc module name；例如：perldoc Spreadsheet::ParseExcel；如果你不知道这个模块的名字，那么例出所有的模块自己看看有没有，请输入：instmodsh。
3，获取某个文件夹下所有Excel文件的名字：
一个朋友给你一个文件夹，内含有10个Excel、10个Word和10个PPT文件，现在你需要将Excel文件找出来，并且需要它们的名字。这个时候将写程序语言的文本文件与目标文件夹放在同一个地址，即：假设你写Perl程序的文本文件为test.txt，你朋友给你的文件夹名为Target，这时你将test.txt与Target放在同一个地方就OK了。当然，你的当前工作目录也要同时调整到这里来，以下程序将帮你实现上述目标：
#!/usr/bin/perl -w
use strict;
my ($file, @dir, @filenames);
#1.你朋友的文件夹叫做Target，记得换名字#################################
my $dir ="Target/"; ##################################################
opendir (DIR,$dir) or die "Wrong!";
@dir = readdir DIR;
foreach $file (@dir)
#2.匹配的是.xls结尾的，记得调整########################################
{if($file =~ /.xls$/){push @filenames, $file;}} ################################
那么问题来了，如果你朋友的文件夹里面还含有子文件夹，而子文件夹里面又含有Excel、Word、PPT之类的文件，还有可能是子文件夹里可能还有文件夹。如果是一个一个点开，头都要晕。那么下边的程序可以获取所有的Excel文件的名字：
#!/usr/bin/perl -w
use strict;
use File::Find;
#1.你朋友的文件夹叫做Target，记得换名字#################################
my $targetpath = 'Target/'; ############################################
my ($xls,@xlss);
sub try
#2.有“自主研发”字样xls文件###########################################
#3. $File::Find::dir为文件地址，$_ 为文件名，$File::Find::name 为绝对路径#########
{if ($_ =~ /.*自主研发.*\.xls/){push @xlss, $_;}} ##############################
find(\&try, $targetpath);
4，Excel表格中某一行的特定连续几列求和：
虽然大多数时候我们只会对Excel的某一列进行求和，但是有可能会要求对某一行的特定几列求和。例如：对位于Excel表格中第34行的10-33列的数字求和。现实生活中，我们只需要用鼠标对Excel表格的数字选中就可以自动求和了，可是如果很多这样的事情呢？手工就比较费时了。下面仅对一个Excel表格进行操作，以下是核心代码：
use Spreadsheet::ParseExcel;
my $parser = Spreadsheet::ParseExcel->new();
#1.这个文件叫Data.xls，可以修改########################################
my $workbook = $parser->parse("Data.xls"); ###############################
if ( !defined $workbook ) {die $parser->error(), ".\n";}
#2.对第1个worksheet操作，可修改#######################################
my $worksheet = $workbook->worksheet(0); ###############################
#3.表示Excel表格第10列到33列。########################################
foreach my $col (9..32) ###############################################
#4.确定对Excel表格的第34行操作。######################################
{ my $cellB = $worksheet -> get_cell (33, $col);
my $valueB = $cellB -> value();
if ($valueB =~ /#REF!/) {$valueB = 0;}
if ($valueB =~ /,/) {$valueB =~ s/,//g;}
$sum = $sum + $valueB;
}
print "$sum\n";
如果正确理解了上面的代码，那么想要求某一特定列的连续几行也变得显而易见了。我们假设对第10列的第5-21行进行求和，那么只需将上面foreach单词所在行与它下面的那行代码变成以下：
#1.表示Excel表格第5行到21行。#########################################
foreach my $row (4..20) ###############################################
#2.确定对Excel表格的第10列操作。######################################
{ my $cellB = $worksheet -> get_cell ($row, 9);##############################
5，怎么样复制Excel文件到原位置？
有的时候会有这样的问题，在一个文件夹中有许多子文件夹，而子文件夹中有Excel文件，我们现在需要复制Excel文件并且粘贴到它原来的位置，然后重新命名。如果不用程序，那么你就得一个一个进去文件夹，Ctrl+C，Ctrl+V，这样的工作非常的繁琐。现在用程序搞定：
#!/usr/bin/perl -w
use strict;
use File::Copy;
use File::Find;
#1.你朋友的文件夹叫做Target，记得换名字#################################
my $targetpath = 'Target/'; ############################################
sub try
#2.对.xls文件进行操作。##############################################
{ if ($_ =~ /\.xls/) ####################################################
#3.新文件的名字在原文件名前加了“Copy”四个字母###########################
{my $newfile = "Copy"."$_"; ############################################
copy ($_, $newfile) or die "The copy operation failed: $!\n";}
}
find(\&try, $targetpath);
通过以上操作，每个Excel文件（.xls）就成功地复制到它所在的地址了，如：原文件叫做“Test.xls”，新文件CopyTest.xl将和它位于同样一个文件夹。在复制成功之后，我们一般需要将原来的文件删除，这叫过河拆桥，在#3处添加以下代码就可以轻松办到：
unlink $_;
我们刚才复制一个文件并命名为新的名字，然后删除原文件，那么为啥不直接重命名原文件？
rename $_, "Copy"."$_";
6，找出两列数据中重复的字段：
经常会有这样的任务，你的老板给你两列数据：第一列有100行，第二列有500行。怎么样找出两列中有重复的字符串呢？这个问题如果人工来操作的话需要这样，看第一列第一行的数据，然后在第二列的500行搜索一遍看有没有重复的，接下来看第一列第二行，然后再第二列的500行搜索一遍看有没有重复的。肉眼得搜索100*500次，太容易出错了。下面使用程序搞定（假定两列数据分别在不同workbook（A.xls和B.xls）中的第2和第3个worksheet中的第5列.100行和第9列.500行）：
#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
use File::Find;
my ($targetpath, @xlss);
#1.你朋友的文件夹叫做Target，记得换名字#################################
$targetpath = 'Target/'; ###############################################
sub try
#2.选择匹配到的两个目标文件##########################################
{if ($_ =~ /.xls/){push @xlss, $File::Find::name;}##############################
}
find(\&try, $targetpath);
@xlss = sort(@xlss);
my ($parser, $workbook, $worksheetA, @contentsA);
$parser = Spreadsheet::ParseExcel->new();
$workbook = $parser->parse("$xlss[0]");
if ( !defined $workbook ) {die $parser->error(), ".\n";}
#3.第一个workbook的第2个worksheet####################################
$worksheetA = $workbook->worksheet(1); #################################
#4.现在对第4列1-100行进行操作########################################
foreach my $row (0..99) ###############################################
{ my $cellA = $worksheetA -> get_cell ($row, 4); ###########################
my $valueA = $cellA -> value();
push @contentsA, $valueA;
}
my ($parser, $workbook, $worksheetB, @contentsB);
$parser = Spreadsheet::ParseExcel->new();
#5.此时对第二个Workbook进行操作######################################
$workbook = $parser->parse("$xlss[1]"); ##################################
if ( !defined $workbook ) {die $parser->error(), ".\n";}
#6.此时对第3个worksheet进行操作######################################
$worksheetB = $workbook->worksheet(2); #################################
#7.此时对9列1-500行进行操作##########################################
foreach my $row (0..499) #####表示Excel表格第1行到500行。##################
{ my $cellB = $worksheetB -> get_cell ($row, 8); ###########################
my $valueB = $cellB -> value();
push @contentsB, $valueB;
}
my (@contentsC, $value, %hash);
@contentsC = (@contentsA, @contentsB);
while ($value = <@contentsC>)
{$hash{$value}++;}
while ($value = <@contentsC>)
#8.打印出重复两次的字符串############################################
{ if ($hash{$value} == 2)###############################################
{print "$value\n";}
}
上面的代码是假设这两列数据分别存放在不同的Workbook中，如果两列数据存放在同一Workbook中，只需修改#5这个地方的索引就行了，因此上述代码其实是一劳永逸的做法。有时候我们不需要输出重复的字段，而是要找出两列数据中仅出现一次的字段，那么对#8稍作修改就可以了。
7，在两列一一对应的数据对中摘取其中一部分（VLOOKUP）
大学本科的时候，我们基地班共计60人，且每个同学有一个唯一的学号。期末考试后，每位同学已登记了各科成绩（生物、数学、物理、化学）。辅导员并不想对成绩优异的同学给予奖励，她仅仅随机抽取了10个学号给予奖励。生物老师好奇，想看看这随机的10个同学的生物成绩怎么样？下面将问题表格化：第1列为学号（共计60行，第2-61行），第3列为生物成绩（共计60行，第2-61行），第8列为随机学号（共计10行，第2-11行）。
#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
use File::Find;
my ($targetpath, @xlss);
#1.你朋友的文件夹叫做Target，记得换名字#################################
$targetpath = 'Target/'; ###############################################
sub try
#2.选择匹配到的两个目标文件##########################################
{if ($_ =~ /.xls/){push @xlss, $File::Find::name;}##############################
}
find(\&try, $targetpath);
@xlss = sort(@xlss);
my ($parser, $workbook, $worksheetA, %hash);
$parser = Spreadsheet::ParseExcel->new();
$workbook = $parser->parse("$xlss[0]");
if ( !defined $workbook ) {die $parser->error(), ".\n";}
#3.第一个workbook的第1个worksheet####################################
$worksheetA = $workbook->worksheet(0); ################################
#4.现在对第1和3列2-61行进行操作#######################################
foreach my $row (1..60) ###############################################
{ my $cellAf = $worksheetA -> get_cell ($row, 0); ############################
my $valueAf = $cellAf -> value();
my $cellAs = $worksheetA -> get_cell ($row, 2); ############################
my $valueAs = $cellAs -> value();
$hash{$valueAf} = $valueAs;
}
my ($parser, $workbook, $worksheetB, @contentsB);
$parser = Spreadsheet::ParseExcel->new();
#5.此时对第二个Workbook进行操作######################################
$workbook = $parser->parse("$xlss[0]"); ##################################
if ( !defined $workbook ) {die $parser->error(), ".\n";}
#6.此时对第1个worksheet进行操作#######################################
$worksheetB = $workbook->worksheet(0); ################################
#7.此时对8列2-11行进行操作###########################################
foreach my $row (1..10) ###############################################
{ my $cellB = $worksheetB -> get_cell ($row, 7); #############################
my $valueB = $cellB -> value();
push @contentsB, $valueB;
}
while (my $valueBt = <@contentsB>)
#8.打印出结果######################################################
{print "$valueBt : $hash{$valueBt}\n";}
这样其实就是实现了VLOOKUP函数的功能。注意，在使用以上代码时，必须要确保学号是唯一字段，所有的学号不能有重复。上面我们将“第一列”数据定位哈希的键，所需要打印的列的数据作为哈希的值，这样可以轻松实现VLOOKUP函数的功能。那么有时候我们需要将所找到的对应行的值全部找出来，而不仅仅是某一列应该怎么办？
#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
use File::Find;
my ($targetpath, @xlss);
#1.你朋友的文件夹叫做Target，记得换名字#################################
$targetpath = 'Target/'; ###############################################
sub try
#2.选择匹配到的两个目标文件##########################################
{if ($_ =~ /\.xls$/){push @xlss, $File::Find::name;}##############################
}
find(\&try, $targetpath);
@xlss = sort(@xlss);
my ($parser, $workbook, $worksheetA, @VA1);
$parser = Spreadsheet::ParseExcel->new();
$workbook = $parser->parse("$xlss[0]");
if ( !defined $workbook ) {die $parser->error(), ".\n";}
#3.第一个workbook的第1个worksheet####################################
$worksheetA = $workbook->worksheet(0);#################################
#4.现在对第1列1-21行进行操作##########################################
foreach my $row (0..20) ###############################################
{ my $cellAf = $worksheetA -> get_cell ($row, 0); ############################
my $valueAf = $cellAf -> value();
push @VA1, $valueAf;
}
my ($parser, $workbook, $worksheetB, @contentsB);
$parser = Spreadsheet::ParseExcel->new();
#5.此时对第二个Workbook进行操作######################################
$workbook = $parser->parse("$xlss[0]"); ##################################
if ( !defined $workbook ) {die $parser->error(), ".\n";}
#6.此时对第1个worksheet进行操作######################################
$worksheetB = $workbook->worksheet(0); ################################
#7.此时对8列2-5行进行操作###########################################
foreach my $row (1..4) #####表示Excel表格第2行到5行。##################
{ my $cellB = $worksheetB -> get_cell ($row, 7); ###########################
my $valueB = $cellB -> value();
push @contentsB, $valueB;
}
my ($contentsA1,$contensB1, $count);
foreach $contensB1 (@contentsB)
{ foreach $contentsA1 (@VA1)
{ $count++;
if ($contentsA1 eq $contensB1)
{my ($parser, $workbook, $worksheetA, @VA1);
$parser = Spreadsheet::ParseExcel->new();
#8.重新打开原始数据所在文件##########################################
$workbook = $parser->parse("$xlss[0]"); ##############################
if ( !defined $workbook ) {die $parser->error(), ".\n";}
$worksheetA = $workbook->worksheet(0);##############################
#9.现在对某一行1-5列进行操作########################################
foreach my $col (0..4) #############################################
{ my $cellAf = $worksheetA -> get_cell ($count - 1, $col);
my $valueAf = $cellAf -> value();
print "$valueAf ";
}
print "\n";
}
}
$count = 0;
}
8.按照第一列数据内容为分类标准，将有相同的第一列中数据的行归为一起
老板给你一个Excel，1000行*10列，第一列的数据是人名依次排列着：ABDGHAAABHHBCJ..第2-10列是一些关于这些人的职位收入等数据，我们不管他。任务来了：将第一列为A的行归整在一起、第一列为B的行归整在一起...，其实相当于按照A列数据排序（相同的排在一起）。以下是代码：
#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
use File::Find;
my ($targetpath, @xlss);
#1.你朋友的文件夹叫做Target，记得换名字#################################
$targetpath = 'Target/'; ###############################################
sub try
#2.选择匹配到的两个目标文件##########################################
{if ($_ =~ /\.xls$/){push @xlss, $File::Find::name;}############################
}
find(\&try, $targetpath);
@xlss = sort(@xlss);
my ($parser, $workbook, $worksheetA, @VA1);
$parser = Spreadsheet::ParseExcel->new();
$workbook = $parser->parse("$xlss[0]");
if ( !defined $workbook ) {die $parser->error(), ".\n";}
#3.##############################################################
$worksheetA = $workbook->worksheet(0); ################################
#4.#############################################################
foreach my $row (0..999) ##############################################
{ my $cellAf = $worksheetA -> get_cell ($row, 0); ############################
my $valueAf = $cellAf -> value();
push @VA1, $valueAf;
}
my($position1, %hash, @keys);
while ($position1 = <@VA1>)
{$hash{$position1}++;}
@keys = sort keys %hash;
my ($oof, $oot, $row);
foreach $oof (@keys)
{ foreach $oot (@VA1)
{
$row++;
if ($oof eq $oot)
{
foreach my $col (0..9)###########################################
{ my $cellAf = $worksheetA -> get_cell ($row, $col);
my $valueAf = $cellAf -> value();
print "$valueAf ";
}
print "\n";
}
}
$row = 0;
}
笔者在运用上面的过程中发现一个问题，如果第一列为英文则可以成功运行，如果是中文的话就会出现问题，这个可以通过下面的方式解决：