• 热门专题

如何用Perl对Excel的数据进行提取并分析

作者:iVictor  发布日期:2015-07-28 21:46:02
Tag标签:数据  
  • 巡检类工作经常会出具日报,最近在原有日报的基础上又新增了一个表的数据量统计日报,主要是针对数据库中使用较频繁,数据量又较大的31张表。该日报有两个sheet组成,第一个sheet是数据填写,第二个sheet则是基于第一个sheet的数据进行的文字描述和图表展示。

    文字描述主要包括两部分:一、呈现该31张表中数据量最大的9张表。呈现结果类似于:emp(约14万),dept(约100万)。。。当然,这个只是举例,为了避免引起不必要的麻烦(主要是企业信息安全方面的考虑),我这里不可能将具体日报的内容贴出来。毕竟,这个会涉及到表名和数据量大小。二、相对于昨日,这31张表中有哪些表的数据量在今日有所增长。

    对于这种机械类的工作,每次做起来都比较繁琐,头疼。Perl之父Larry Wall说过,懒惰、急躁、傲慢是程序员的三大美德。于是就着手写了一个perl程序,每次只要把当天的数据量copy到Excel中,执行该程序即可。

    程序内容如下:

    use strict;
    use Spreadsheet::XLSX;
    use Unicode::UTF8simple;
    use DateTime;
    my ($col1,$col2,%hash3,%hash4,@tabname,$num,$num1);
    my $dt=DateTime->from_epoch(epoch=>time);
    my $duration=DateTime::Duration->new(days=>-1);
    my $dt1=$dt+$duration;
    my $date=$dt->month.'-'.$dt->day.'-'.substr($dt->year,2,2);
    my $date1=$dt1->month.'-'.$dt1->day.'-'.substr($dt1->year,2,2);
    my $uref =new Unicode::UTF8simple;
    my $file='C:UsersVictorDesktop需做数据迁移的数据表数据量巡检日报-20150713.xlsx';
    $file=$uref->fromUTF8('gb2312',$file);
    my $workbook  = Spreadsheet::XLSX -> new ($file);
    my $worksheet = $workbook->worksheet('数据填写');
    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();
    for my $col ( $col_min .. $col_max ){
        my $cell = $worksheet-> get_cell( 0, $col );
        next unless $cell;
        $col1=$col if ($cell->value() eq $date1);
        $col2=$col if ($cell->value() eq $date);
    }
    for my $row (1..$row_max){
        my $cell = $worksheet->get_cell($row,1);
        my $cell1= $worksheet->get_cell($row,$col1);
        my $cell2= $worksheet->get_cell($row,$col2);
        $hash3{$cell->value()}=$cell1->value();
        $hash4{$cell->value()}=$cell2->value();
        push @tabname,$cell->value();
    }
    print $uref->fromUTF8('gb2312','(1)需做数据迁移的数据表当前数据量较大的依次为(不计日志表):
    ');
    my $str2=$uref->fromUTF8('gb2312','约');
    my $str3=$uref->fromUTF8('gb2312','万');
    my $str4=$uref->fromUTF8('gb2312','、');
    foreach my $key(sort {$hash4{$b}<=>$hash4{$a}} keys %hash4){
           printf '%s(%s%d%s)%s',$key,$str2,$hash4{$key}/10000,$str3,$str4;
           $num++;
           last if $num == 11;   
    }
    print '
    ';
    print $uref->fromUTF8('gb2312','(2)务开通定单调度关键数据表数据量增长趋势:
    ');
    foreach(@tabname){
        if($hash3{$_} <= $hash4{$_}){
            print $_.$str4;
            $num1++;
        }
    }
    print $uref->fromUTF8('gb2312','这$num1张表相对昨日有所增长。
    ')

    说明如下:

    1> 在这里,用了三个模块,其中Spreadsheet::XLSX用于读取2007及以上版本的Excel。可惜的是,CPAN中貌似没有一个模块支持对已有Excel进行写操作。具体在本例中,第二个sheet中的文字描述完全可以在第一个sheet中数据基础上生成。但因找不到对既有Excel进行写的模块,所以生成的文字描述无法插入到第二个sheet中。于是只能退而求其次,只生成文字描述,然后再手动粘贴到第二个sheet中。第二个模块是Unicode::UTF8simple,主要用于UTF8和其它字符集的转换,在本例中,即中文字符集gb2312。第三个模块是DateTime,用于构造今天的日期和昨日的日期。

    2> 

    my $dt=DateTime->from_epoch(epoch=>time);
    my $duration=DateTime::Duration->new(days=>-1);
    my $dt1=$dt+$duration;
    my $date=$dt->month.'-'.$dt->day.'-'.substr($dt->year,2,2);
    my $date1=$dt1->month.'-'.$dt1->day.'-'.substr($dt1->year,2,2);

    用于构造今天的日期和昨日的日期,其中$date是今天的日期,$date1是昨天的日期。

    3>

    my $worksheet = $workbook->worksheet('数据填写');
    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();
    for my $col ( $col_min .. $col_max ){
        my $cell = $worksheet-> get_cell( 0, $col );
        next unless $cell;
        $col1=$col if ($cell->value() eq $date1);
        $col2=$col if ($cell->value() eq $date);
    }

    第一个sheet的名字是“数据填写”,首先获取该sheet行、列的范围。

    第一行的内容如下:

    所以上面这个for语句用于获取今日日期和昨日日期所在的列。

    注意上面for语句中的next unless $cell,它的意思是如果$cell为空,则继续下一个循环。因第一列第一行为空,所以该语句尤为必要。

    4> 

    for my $row (1..$row_max){
        my $cell = $worksheet->get_cell($row,1);
        my $cell1= $worksheet->get_cell($row,$col1);
        my $cell2= $worksheet->get_cell($row,$col2);
        $hash3{$cell->value()}=$cell1->value();
        $hash4{$cell->value()}=$cell2->value();
        push @tabname,$cell->value();
    }

    分别构造两个哈希表,键均是第一列的表名,值是对应的数据量大小。其中hash3对应的是昨日的数据量,hash4对应的是今日的数据量。

    将表名放到数组中,用于后续今日和昨日数据量的比较。

    5> 

    print $uref->fromUTF8('gb2312','(1)需做数据迁移的数据表当前数据量较大的依次为(不计日志表):
    ');
    my $str2=$uref->fromUTF8('gb2312','约');
    my $str3=$uref->fromUTF8('gb2312','万');
    my $str4=$uref->fromUTF8('gb2312','、');
    foreach my $key(sort {$hash4{$b}<=>$hash4{$a}} keys %hash4){
           printf '%s(%s%d%s)%s',$key,$str2,$hash4{$key}/10000,$str3,$str4;
           $num++;
           last if $num == 11;   
    }

    对hash4,即今天的数据量进行排序,打印出31个表中排名前11位的表,输入结果形式如下:

    EMP(约1100万)、DEPT(约1000万)、location(约812万)、sales(约123万)...

    因“约”、“万”、“、”均是中文字符,所以需要转换。因数据量的单位是万,在这里,我们将$hash4{$key}的值除以10000。

    6>

    print '
    ';
    print $uref->fromUTF8('gb2312','(2)务开通定单调度关键数据表数据量增长趋势:
    ');
    foreach(@tabname){
        if($hash3{$_} <= $hash4{$_}){
            print $_.$str4;
            $num1++;
        }
    }
    print $uref->fromUTF8('gb2312','这$num1张表相对昨日有所增长。
    ')

    构造文字描述的第二部分,将今日相对于昨日数据量有所增加的表打印处理。输出结果类似于:

    emp、dept、location、sales这4张表相对昨日有所增长。

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规