perl按条件读写excel 2007

花了两天时间写的，感觉一般般，如果有时间，要参考awk思路再写写。

点击(此处)折叠或打开

1. #!/usr/bin/perl -w
   
2. use strict;
   
3. use warnings;
   
4. use Spreadsheet::XLSX;
   
5. use Excel::Writer::XLSX;
   
6. use Encode;
   
7. use File::Spec;
   
8. 
   
9. print "请输入匹配条件：\n";
   
10. chomp (my $a=<STDIN>);
    
11. my @matchstr=split /\s+/,$a;
    
12. #print "matchstr is @matchstr\n";
    
13. 
    
14. print "请同时输入排除的条件，如果有的话:\n";
    
15. chomp (my $b=<STDIN>);
    
16. my @exceptstr=split /\s+/,$b;
    
17. 
    
18. sub writexcel;
    
19. 
    
20. my $dirname='C:';
    
21. my $filename=File::Spec->catfile($dirname,'my2.xlsx');
    
22. print "filename is $filename\n";
    
23. 
    
24. my $workbook=Spreadsheet::XLSX->new($filename) or die "open xlsx error";
    
25. my @worksheets=@{$workbook->{Worksheet}} or die "open sheets error";
    
26. 
    
27. my $workbook2 = Excel::Writer::XLSX->new('b.xlsx') or die "open xlsx2 error.";
    
28. 
    
29. foreach my $worksheet(@worksheets){
    
30. print "sheet is $worksheet->{Name}\n" or die "cannot print name";
    
31. my $row_min=$worksheet->{MinRow};
    
32. my $row_max=$worksheet->{MaxRow};
    
33. my $col_min=$worksheet->{MinCol};
    
34. my $col_max=$worksheet->{MaxCol};;
    
35. 
    
36. my @row_tmp;
    
37. my $s=0;
    
38.  foreach my $row($row_min .. $row_max){
    
39.   my @col_tmp;
    
40.   my @b;
    
41.   my $flag=0;
    
42. 
    
43.   foreach my $col($col_min .. $col_max){
    
44.   my $cell=$worksheet->{Cells}[$row][$col];
    
45.   next unless $cell;
    
46.   
    
47.   if ($cell){
    
48. 
    
49.   my $a=$cell->{Val};
    
50.   $a=encode("gbk",decode("utf8",$a));
    
51.   #printf("( %s , %s ) => %s\n", $row, $col, $a);
    
52.   push @b,$a;
    
53.   }
    
54.   } #col
    
55.   
    
56.   if(scalar @matchstr != 0){
    
57.   foreach my $matchstr(@matchstr){
    
58.   if (grep(/$matchstr/,@b)){
    
59.   $flag=1;
    
60.   last;
    
61.   }}
    
62.   
    
63.   }
    
64. 
    
65. 
    
66.   if(scalar @exceptstr != 0){
    
67.   foreach my $exceptstr(@exceptstr){
    
68.   if (grep(/$exceptstr/,@b)){
    
69.   $flag=0;
    
70.   last;
    
71.   }}}
    
72.   
    
73.   if ($flag == 1){
    
74.   @col_tmp=@b;
    
75.   push @row_tmp,[@col_tmp];}
    
76. 
    
77.   my $r_t=scalar @row_tmp;
    
78.   #print "row size is $r_t\n";
    
79.  } #row
    
80. 
    
81. my $worksheet2=$workbook2->add_worksheet($s) or die "open sheets2 error";
    
82. writexcel($worksheet2,$workbook2,@row_tmp);
    
83. $s++;
    
84. } #sheet
    
85. 
    
86. 
    
87. sub writexcel {
    
88. my $worksheet=shift;
    
89. my $workbook=shift;
    
90. my @value=@_;
    
91. 
    
92. my $format=$workbook->add_format();
    
93. $format->set_bold();
    
94. $format->set_color('blue');
    
95. $format->set_align('center');
    
96. 
    
97. my @row2=@value;
    
98. 
    
99. for my $i(0 .. $#row2){
    
100.  for my $k(0 .. $#{$row2[$i]}){
     
101.  print "i is $i, k is $k\n";
     
102.   my $t=$row2[$i][$k];
     
103. 
     
104.   $t=decode("gbk",$t);
     
105.   $worksheet->write($i,$k,$t,$format);
     
106.   
     
107.   }
     
108.  
     
109.  }
     
110. }