perl读取excel

因为工作当中遇到要处理大数据的excel的玩意，最多的有几十万行。用perl的方式试试，看看效果如何。

ppm install OLE::Storage_Lite #如果不安装这个，后面两个安装不了 
ppm install Spreadsheet::ParseExcel
ppm install Spreadsheet::WriteExcel

查看是否安装成功

perldoc Spreadsheet::ParseExcel #如果打印出文档则表示安装成功

为保证编码正确

ppm install Unicode::Map

use strict; 
use Spreadsheet::ParseExcel; 
use Spreadsheet::ParseExcel::FmtUnicode; #字符编码
 
my $parser = Spreadsheet::ParseExcel->new(); 
my $formatter = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map=>"CP936");#设置字符编码
#my $workbook = $parser->parse('Book.xls'); 
my $workbook = $parser->parse('E:webdevprojectperla.xls', $formatter);#按所设置的字符编码解析


my $log = "demo.log";

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

open(FILE,">$log");

for my $worksheet ( $workbook->worksheets() ) { 
 
    my ( $row_min, $row_max ) = $worksheet->row_range(); 
    my ( $col_min, $col_max ) = $worksheet->col_range(); 
 
    for my $row ( $row_min .. $row_max ) { 
        for my $col ( $col_min .. $col_max ) { 
 
            my $cell = $worksheet->get_cell( $row, $col ); 
            next unless $cell; 
            print $cell->value()," ";
            print(FILE $cell->value()."	");
        } 
        print "
";
        print(FILE "
");
    } 
}

close(FILE);

记录下，明天去公司试试效果

use strict;  
use warnings;  
use Spreadsheet::XLSX;  
use Encode;  
use Getopt::std;
use File::Basename;
use LWP::Simple;  
use URI::Escape;

#my $converter = Text::Iconv -> new ("utf-8", "windows-1251");  
#my $excel = Spreadsheet::XLSX -> new ('test.xlsx', $converter);  

eval {
    my $l = "D:\log.log";
    
    #读取excel文件
    my $excel_path = $ARGV[0];
    my $dir = dirname($excel_path);
    
    #设置log文件路径
    my $log = $dir."/".$ARGV[1].".log";
    open(FILE,">$l");
    print(FILE "http://localhost/kwm/source/index.php?s=Admin/Index/extract/log/".$ARGV[1]);
    close(FILE);
    #print 'http://localhost/kwm/source/index.php?s=Admin/Index/extract/log/'.$ARGV[1];
    
    
    #print $log;die;
    #print(FILE "praam $excel_path
");
    my $excel = Spreadsheet::XLSX->new($excel_path);
    #print(FILE "excel read over
");
    

    

    my $sheet = $excel->Worksheet(0);

    open(FILE,">$log");

    printf("Sheet: %s/n", $sheet->{Name}); 

    $sheet -> {MaxRow} ||= $sheet -> {MinRow};   
             
    foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {  
        $sheet -> {MaxCol} ||= $sheet -> {MinCol};  
        foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}) {  
            my $cell = $sheet -> {Cells} [$row] [$col];  
            my $a = "";
            if ($cell) {  
                $a = $cell->{Val};  
                #$a = encode("gbk", decode("utf8", $a));  
                #printf("( %s , %s ) => %s/n", $row, $col, $a);  
                #printf("$a ");  
            }
            
             print(FILE $a."	");
        }  
        #print "/n"; ## one row over  
        print(FILE "
");
    }  
    close(FILE);

    my $content = get('http://localhost/kwm/source/index.php?s=Admin/Task/extract/log/'.$ARGV[1]."/id/".$ARGV[2]."/uid/".$ARGV[3]); 
    print encode("gbk", $content);
    die;
};

if($@) {
    #print "error";
}