perl读取excel

farmer111

　　因为工作当中遇到要处理大数据的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:\webdev\project\perl\a.xls', $formatter);#按所设置的字符编码解析

my $log = "demo.log";

if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
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()."\t");
}
print "\n";
print(FILE "\n");
}
}
close(FILE);
View Code　　记录下，明天去公司试试效果





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\n");
my $excel = Spreadsheet::XLSX->new($excel_path);
#print(FILE "excel read over\n");


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."\t");
}  
#print "/n"; ## one row over  
print(FILE "\n");
}  
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";
}
View Code