用perl读写excel表格
1. 说明:
最近对perl很感兴趣,正好朋友问我一个excel的问题,不想用宏来写,就用perl练了练手。
所用相关知识如下:
1) perl hash
2) win32::ole,对excel表格控制
3) drag&drop的实现
4) perl2exe,将perl程序转换为windows可执行文件
2.需求
2.1 功能需求:
表格中有多个sheet,当用户在第一个sheet中,输入货号和定单号时,要求其它的sheet中也可以自动生成相应的货号和定单号。(这其实可以用excel宏来实现,但问题是用宏实现后,一旦对第一个sheet排序,后面的sheet会乱。)在各个sheet中,货号为关键字,每一行各不相同。
2.2 界面需求
尽量简单,最好做成单独的程序。实际上使用了,drag and drop的功能,直接把一个或多个excel表拖到程序上,就可以实现。
3. 设计
从第一个表中取出第三列和第四列(分别对应货号和定单号)的内容,放到hash中,然后分别取出后面每一个sheet的三四列hash,如果第一列的hash key不存在,则插入到后面的sheet中。
假定如下:
1)后面的工作表不能有空行,如果有空行,会出现插入错位。
2)货号和订单号只能在第三列和第四列,且不包含第一行的列头。
4. 关键代码
4.1 获取拖放的文件参数
#Get the original execel files from the argment list
while ($file = shift(@ARGV)) {
if ($file =~ //.xls/) {
push(@orgfiles, $file);
} else {
print "Please drop excel files./n";
}
}
4.2 打开文件
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
$open_excel_file = $Excel->Workbooks->Open($orgfile) || die Win32::OLE->LastError();
4.3 获得表格中的数据并存到hash中
#sheet_no为传入参数,代表第几个sheet
sub get_styleno_hash()
{
my ( $sheet_no) = (@_);
my $current_sheet = $open_excel_file->Worksheets($sheet_no);
my $row_count = $current_sheet->UsedRange->Rows->Count;
my $style_id;
my $order_id;
my %hash_styleno;
undef %hash_styleno;
foreach my $irow (2..$row_count)
{
if(!$current_sheet->Cells($irow,3)->{'Value'})
{
next;
}
$style_id = $current_sheet->Cells($irow,3)->{'Value'};
$order_id = $current_sheet->Cells($irow,4)->{'Value'};
#print "inital hash$sheet_no, style_id=$style_id, order_id=$order_id /n";
$hash_styleno{$style_id} = $order_id;
}
return %hash_styleno;
}
4.4 比较并保存到sheet中
sub add_order_to_sheet()
{
my ($sheet_no )= (@_);
print "start operate on sheet $sheet_no.../n";
my %hash_current = get_styleno_hash($sheet_no);
#my @testcurrent=%hash_current;
#print " @testcurrent /n";
my $current_hash_size = keys %hash_current;
my $current_sheet = $open_excel_file->Worksheets($sheet_no);
my $row_count = $current_sheet->UsedRange->Rows->Count;
#print "Current Used Range: $row_count /n";
#print "Current Hash size: $current_hash_size /n";
my $key;
my $value;
foreach $key ( keys %g_hash_style)
{
#print "sheet1 key=$key, value=$g_hash_style{$key} /n";
if(!(exists $hash_current{$key})) #if it's not already in the other sheet
{
#print "catched not in sheet $sheet_no /n ";
$value = $g_hash_style{$key};
# add one line in the current sheet
$current_hash_size = $current_hash_size +1;
$current_sheet->Cells($current_hash_size+1,3)->{'Value'} = $key;
$current_sheet->Cells($current_hash_size+1,4)->{'Value'} = $value;
}
}
}
4.5 保存excel并退出
#$open_excel_file->SaveAs($orgfile);
$open_excel_file->Save();
$open_excel_file->Close;
$Excel->Quit();
5. 转换perl成exe文件
下载perl2exe,运行perl2exe myperlfile.pl