使用Perl处理Excel重复数据

本文目的：由于小可爱有个几万条的Excel格式数据需要去重，遍历Excel表格，对比id跟name是否相同，如果id跟name有重复的，把这个重复的id数据放到新的Excel表格1，如果没有重复，放到另外一个新的Excel表格2。
表格数据如下图：

这个文件56495行，有150多M。
思路如下：

1. 使用Perl模块Spreadsheet::ParseExcel读取目标文件，对比每一行
2. 使用Perl模块Spreadsheet::WriteExcel创建Excel,把符合预期的目标数据写在创建的Excel里

安装方式参考改文章：使用Perl对TCGA数据库下载后的文件合并与转换。
Windows下安装步骤如下：

cpan Spreadsheet::ParseExcel
cpan Spreadsheet::WriteExcel
复制代码

Mac下安装

sudo perl -MCPAN -e "install 'Spreadsheet::ParseExcel'"
sudo perl -MCPAN -e "install 'Spreadsheet::WriteExcel'"
复制代码

步骤一：读取目标Excel文件

目标：先遍历目标Excel的行，代码如下：

#!/usr/bin/perl -w

use strict;
use warnings;

use Spreadsheet::ParseExcel;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('test.xls');

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

for my $worksheet ( $workbook->worksheets() ) {
    my ( $row_min, $row_max ) = $worksheet->row_range();

    for my $row ( $row_min .. $row_max ) {
        print "Row    = ($row)\n";
        print "\n";
    }
}
复制代码

结果由于行数太多，报内存溢出，如下图：

（注意：模块Spreadsheet::ParseExcel只能读95-2003格式的Excel文档，对于office 2007 Excel则要安装Spreadsheet::XLSX。）

查了资料原因是因为程序在写入读取文件内容时直接将所有文件内容读入数组导致这个问题，故需要别的思路解决这个问题。

结合网上文章有以下几种解决问题的思路方式：

1. windows下的话，建议用win32：：OLE，效率会高很多

代码如下：

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->Open("$file",0,'True');
my $Sheet = $Book->Worksheets(1);

#$maxCol = $Sheet-> UsedRange -> Columns->Count;
$maxRow = $Sheet-> UsedRange -> Rows->Count;
$i = 0;

foreach my $row (1..$maxRow) {
  # foreach my $col (1..$maxCol)
  #{
  $xm = $Sheet->Cells($row,2)->{'Value'};
  $cj = $Sheet->Cells($row,3)->{'Value'};
  $i++;
}
复制代码

2. 如果要用Spreadsheet::ParseExcel

• 应该是获取它的行数
• 然后用while循环
• 不能用foreach，也不要grep，用正则。
• 也不要用for

3. exce表太大了

• 转成csv格式(Excel另存为，可以选择csv格式)
• 然后采用逐行读取处理
• while（）循环处理数据

思路1不具有跨平台的特性，兼容平台的特性比较差，舍弃。
思路2也会消耗过大性能，舍弃。
思路3的尝试思路如下：

• 先尝试遍历转成csv格式的数据，代码如下：

#!/usr/bin/perl

use warnings;
use strict;

my $file = "mRNAsymbol.csv";
open my $fl, "< $file" or die "can not open the file:$file\n$!";

while (<$fl>) {
    next if /^Name/;
    chomp;
    #my ($id,$name,$type) = split ",", $_;
    my ($id,$name,$type) = split ",";    # 省略默认变量$_
    last unless $id;
    print "id:$id\n name:$name\n type:$type\n\n\n";
}

close $fl;
复制代码

或者如下代码（暂不可用，只做这种写法的记录）：

#!/usr/bin/perl

use warnings;
use strict;

my $file = "mRNAsymbol.csv";
open (FILE,"<$file") or die "Cannot open file $!\n";

while (defined (my $fl = <FILE>)) {
    next if /^Name/;
    chomp;
    #my ($id,$name,$type) = split ",", $_;
    my ($id,$name,$type) = split ",";    # 省略默认变量$_
    last unless $id;
    print "id:$id\n name:$name\n type:$type\n\n\n";
}

close (FILE);
复制代码