perl读取excel
因为工作当中遇到要处理大数据的excel的玩意,最多的有几十万行。用perl的方式试试,看看效果如何。
ppm install OLE::Storage_Lite #如果不安装这个,后面两个安装不了 ppm install Spreadsheet::ParseExcel ppm install Spreadsheet::WriteExcel
查看是否安装成功
perldoc Spreadsheet::ParseExcel #如果打印出文档则表示安装成功
为保证编码正确
ppm install Unicode::Map
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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);
记录下,明天去公司试试效果
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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"; }