perl & excel

*Ps hs m \0E)F` ^|0#!/usr/bin/perl -w51Testing软件测试网-o"{7@^4a\1nva
use strict;51Testing软件测试网6Do!G4Q6OLe
p*IbH
use Spreadsheet::WriteExcel;
h m8Ij3t-XsSs5@0use Spreadsheet::ParseExcel;51Testing软件测试网)pxZpH$d
my $report_title = "Product Verison: xx.xx.xx Testing Report";
2z a\$N_,Q xW0my @testsuit = ("TestSuite", "TS_1","TS_2");
.hZdd$G.[V0my @testenv = ("TestEnv","TestEnv1","TestEnv2","TestEnv3");51Testing软件测试网!fg4C2^(B7S#a

(M LH4O&f N
McO0Q['j0my $start_num = 6;51Testing软件测试网po1h nlF:{#o
my $num_ts = @testsuit - 1;
Kv yb0Z.uN*h/W1@ M-V0my $num_env = @testenv-1;51Testing软件测试网e)U,o2~ z0s1u5?-d
my $num_total = 0;
6TGNtr:}h0my $num_pass = 0;
u
y(e
L|:Y n(t&SK0my $num_fail = 0;51Testing软件测试网a3H$P ~;}
E9Q,|&C&_2R
my $num_na = 0;
}(Q'G M/Ct$t0my $num_other =0;

my $workbook=Spreadsheet::WriteExcel->new("CellFormatting.xls");51Testing软件测试网2[gn1b W%t+?{
die "Problems creating new Excel file: $!" unless defined $workbook;51Testing软件测试网1rB0xw4b?i

+oT,vMcx"P-m`*f0my $worksheet1 = $workbook->add_worksheet("Overview");
*M#{9VG`,olG0my $worksheet2 = $workbook->add_worksheet("Test Result");

########################51Testing软件测试网`"bxdh
##Format setting
g(PdD/Y6Yk @o0########################
~N*}xH{"e0my $fmt_default = $workbook->add_format();
.]QW$A%BU8q%G] ?f0my $fmt_normal = $workbook->add_format();51Testing软件测试网R1A-uN3F4p2J{\O ?
my $fmt_title = $workbook->add_format();51Testing软件测试网+YQg2u$zi
my $fmt_subtitle = $workbook->add_format();51Testing软件测试网@5{;n0E\6C.e
my $fmt_subtitle_forMerge = $workbook->add_format();51Testing软件测试网iJ-e|:n
my $fmt_pass = $workbook->add_format();
7`!Mc
]e:x0my $fmt_fail = $workbook->add_format();51Testing软件测试网7`v9c^ s({(g%H6pRFj
my $fmt_na = $workbook->add_format();51Testing软件测试网~ ?M hF*_*R:h5]

$fmt_default->set_font("Arial");51Testing软件测试网Z-K5YeyIg
$fmt_default->set_bold("1");
!N d }5x_v @a8G0$fmt_default->set_color('black');51Testing软件测试网2i e,}q@0cG"[i%H!K
$fmt_default->set_size("11");
]jV D:F*y~c0$fmt_default->set_align('center');
Y/f;IS h0gNi0$fmt_default->set_align('vcenter');51Testing软件测试网1Wr+p~6}
$fmt_default->set_border("1");

$fmt_title->copy($fmt_default);51Testing软件测试网(uk+L@:}A)@,K
$fmt_title->set_color("blue");51Testing软件测试网/gx)Z"[xg1cq0Y8z
$fmt_title->set_border("5");
V8b;O}k0$fmt_title->set_size("16");

$fmt_subtitle->copy($fmt_default);51Testing软件测试网.x6_ySWp)}6^7D,p
$fmt_subtitle->set_color("black");51Testing软件测试网r*}E%O hWo
$fmt_subtitle->set_border("5");51Testing软件测试网#y;O!{SK-I.ie
$fmt_subtitle->set_size("12");
@4HX$[;KC6}.I6h0$fmt_subtitle->set_bg_color("orange");
5upl/x-VL2q G0$fmt_subtitle->set_text_wrap();
h]g_d@ |ZJ0$fmt_subtitle_forMerge->copy($fmt_subtitle);

q9P:U\+_:Y0I0$fmt_pass->copy($fmt_default);
4dy9cu4J&d0$fmt_pass->set_color("green");51Testing软件测试网o a`fp+t5ol f-]9i
$fmt_fail->copy($fmt_default);51Testing软件测试网r!J^ oqr1U
$fmt_fail->set_color("red");
` h;q X%P:Pc"y0$fmt_na->copy($fmt_default);51Testing软件测试网6FO/C'M(n0Q7w
$fmt_na->set_color("blue");51Testing软件测试网,t llka4nZ
$fmt_normal->copy($fmt_default);51Testing软件测试网U-~"`|7f~L
$fmt_normal->set_color("black");

u*P%Hi*H6L]1t-n0########################
V.AB.HGOIe'`s0##write title51Testing软件测试网5o*@Kf5o)klznu
########################51Testing软件测试网mC6h&k bx7?L.X\"A
$worksheet1->merge_range('A1:H1',$report_title,$fmt_title);51Testing软件测试网R3e3}n5B$o)l
$worksheet1->merge_range('A2:A5',$testsuit[0],$fmt_subtitle_forMerge);
` Q2B8Ov5B1ZW0$worksheet1->merge_range('B2:B5',$testenv[0],$fmt_subtitle_forMerge);51Testing软件测试网8S'p:H7L [@'u?n

"cr'I ni5T0########################
Nff%_
Jp9bD5T0##Add Test Suit for Overview51Testing软件测试网jN+G9s ZC
########################
8M0_:_o.hi;WMN0my $i = 0;51Testing软件测试网}?-Pj@[(C
my $j = 0;

for ($i = 0; $i < $num_ts; $i++)
2l,dG,u3O0{51Testing软件测试网:?Nz Q4byHD
    my $start =$start_num  + $i * $num_env;51Testing软件测试网L:_I~JJ|#Q+d]
    my $end = $start + $num_env -1;
wqi~"[&o0 51Testing软件测试网"O pRcw+|2R
    print "start: $start, end: $end\n";
7G5{6sm*|2Y1q0    $worksheet1->merge_range("A$start:A$end","$testsuit[$i+1]",$fmt_default);

}
4G3R'e4|G,n4d#N-y0Y"n0print ("Finish Adding test suit!\n");51Testing软件测试网t_0J,pKv6z

1zs7Z(DJ/Z#t0########################
O3d#p2p5K0##Add Test Env
"j Lt%F&|R
]0########################51Testing软件测试网I:P%M4`7KH
my $ts_start = $start_num;
aVM7h%CuC/k@0for ( $i =0; $i < $num_ts; $i++) {51Testing软件测试网;b%{
K&zr0eG
    for ( $j = 0; $j < $num_env; $j++)
/S~&j.{+M{rB0    {51Testing软件测试网+R I,~8? j2s
       print("$i: B$ts_start: $testenv[$j+1]\n");51Testing软件测试网 qViz9IP
      
Tfj/A,o6xd,i't)~0       $worksheet1->write("B$ts_start", "$testenv[$j+1]",$fmt_normal);51Testing软件测试网;^;S-}/{-oT
       $ts_start++;      
7y5Y7z:JuI0    }51Testing软件测试网~@tf2aRG[
}
(a5r4TC7jvcBn a}0########################
T)FF+Xkx7^~0###Parse Client List File51Testing软件测试网@?I5F
de,]&e
########################51Testing软件测试网`yT@{*w#~6d!|V
my $clientListFile="ClientList.txt";
,K.x;m\O;UP [0my @kernel_info = GET_ARRAY($clientListFile);
3c MN^9ba2C_0$worksheet1->write_row("C2",\@kernel_info,$fmt_subtitle);

1HW:h
^:o`\%y#T0########################
~?*E$r3G-d|eC0###Parse the framwork file
Xq7t;y2p0########################
+N0mJ]/f0my $parser   = Spreadsheet::ParseExcel->new();51Testing软件测试网\ `8n"t,e9F
my $workbookForParse= $parser->Parse('SrcData.xls') or die "Could not open source Excel file!!\n";51Testing软件测试网t8b {X)I-s.I I6\
my $worksheet2parse = $workbookForParse->worksheet('Overview');

s$x1H:VZ,U$f0
Tl(@Z#Cu|L0########################51Testing软件测试网.s7q)YkC,[0e]Db
###Parse Result File51Testing软件测试网+F j?5x1~
m(z^f
########################51Testing软件测试网%In,T3o'j-e0\J/U
my $testResultFile = "testResult.txt";
"r J cfR8zG0my @test_result = GET_ARRAY($testResultFile);51Testing软件测试网tDU0^W5g3T8XP

c6XS9fW9L0########################51Testing软件测试网"h ]Ra}u/m0M
##Fill in test result in the report file51Testing软件测试网MN&oA-DD&R
########################
nxt_0]+J+I0#$i = 0;51Testing软件测试网:cfjj^${ D#EF
##Test Suite start row number. A6
3qCv,\8?
lc0$ts_start = 6;
\/J Dpw;m0##TestSuite count number." TC_1/TC_2"51Testing软件测试网0Dh?Og
Y3z;J i
my $ts_num = 2;
g-FU%h3Gp'|@C0##Test Env start row number. B651Testing软件测试网S.W k0Ql/c
my $env_start = 6;
.nzi0QU,X4o1DE0##Test Env count number. TestEnv1/2/351Testing软件测试网.w8N;d|bI
my $env_num = 3;51Testing软件测试网lDir+T)D+wY
##Client List start cell. C251Testing软件测试网im-y/EH@x~
my $client_start = 2;
O;B!FB@u0##Client count number: 651Testing软件测试网"@K1Sqw+Aw3b k
my $client_num = 6;
G^n!IIA1l;Nc0##Client List start cell: C251Testing软件测试网R;J4HV De4Cr
my $pos = "C";51Testing软件测试网Q&sAT@%u
##TestSuite posistion51Testing软件测试网b1V^'k+|h+s d Qg
my $pos_num = 6;
2S"blR(B[AS@V0##row number for result array.51Testing软件测试网(z-`"jTU6rhQc
w3W
my $result_row_num = $#test_result + 1;
nBG2I%\}ce1ti0##col number for result array.51Testing软件测试网/TXP1BD
my $result_col_num = $#{$test_result[0]} + 1;51Testing软件测试网t4P)qmL7i

_:O4q{L0##Find out same Test Suit name.
M ]v+_z@_0for ($i =0 ; $i < $result_row_num;$i++)51Testing软件测试网 |7gZ9djb
{51Testing软件测试网(C]wg~Yt4m
    ##looking for testsuit
psAtH]
jw#Q0    for ($j =0; $j<$ts_num; $j++)
s0zU]n7X;M0    {
8Y+h v9NRwmO-O[`|0    ##TestSuite position.51Testing软件测试网;s5AXn)A!Fq
       my $cell_pos=("$pos_num"+$j *$env_num-1);51Testing软件测试网I0jl
aj\,]8m Z{
       #print "==".$cell_pos."==\n";
2a.d~:F-Kpp0       if ($test_result[$i][0] eq $worksheet2parse->get_cell($cell_pos,0)->value())
0U:B-}*](N0       {51Testing软件测试网RR&?L5iH:I8z
         #print "==Get the testsuite: $test_result[$i][0],$test_result[$i][2] @ A$cell_pos.\n";
u4xV2c:I7dn$Sb#n0         ##looking for test env.51Testing软件测试网 oF9esg|8H&lV1r&O
         for (my $k=0;$k<$env_num;$k++)
5l7E?,t.G8VM0         {           
SAn!OD7K0            if ($test_result[$i][1] eq $worksheet2parse->get_cell($cell_pos,1)->value())51Testing软件测试网@^kER
            {
IAHOTT;o(b0                # print "===Get the testenv:$test_result[$i][1],$test_result[$i][2] @ B$cell_pos.\n";51Testing软件测试网tw9R&xK6vzx
                ##looking for test clients
*a Z,a*j8|)cH}7p2^0                for (my $h=0;$h<$client_num;$h++)
{]R:X\vD0                {
[.Z Uj3yAY0                    my $client_pos = $client_start+$h;                   51Testing软件测试网#zozQ%Q4~ e(z-v
                    if ($worksheet2parse->get_cell(1,$client_pos)->value()=~/$test_result[$i][2]/)
Z6W^ibd9S%r
b0                    {

                        if ($test_result[$i][3] eq "pass")#{ print "pass: $test_result[$i][2] @ $cell_pos,$client_pos\n";}
%^G;XW"F8M0                        {
b3yL*j%?(y0                         $worksheet1->write($cell_pos, $client_pos,$test_result[$i][2],$fmt_pass); 51Testing软件测试网qa.Is U;k'~[y
          #$num_pass++;51Testing软件测试网+z.b0W:fg2w0F
      }51Testing软件测试网/R}(z$RY3jP
B
      elsif($test_result[$i][3] eq "fail")51Testing软件测试网a/tg&_wejkx
                        {51Testing软件测试网#oO6f'Q*oSG-L%k
       $worksheet1->write($cell_pos, $client_pos, $test_result[$i][2],$fmt_fail);
3gJ*\zT;Ud0       #$num_fail++;
|#w{SM1[0      }elsif($test_result[$i][3] eq "-")51Testing软件测试网}aS;Kry$v;v
                        {51Testing软件测试网j8P'p&G$c@
       $worksheet1->write($cell_pos, $client_pos, "-",$fmt_na);51Testing软件测试网;FdTX-[rwlp j
       #$num_na++;
&l(U#j |P zT0      }else51Testing软件测试网4mr BK-nwxOzb
      {
]t2ww@
L*f0       $worksheet1->write($cell_pos, $client_pos, $test_result[$i][2],$fmt_default);
}0l1a J5\PC0       #$num_other++;51Testing软件测试网a(^]d,L \
      }
P^5XW&[QvT,ko0                    }
;R8F8f/g/]V0S5j-y"Cu#Q0                   
(l;VQ$`Up5Q0                }#//end for looking for test client
y2K0o&l1jC0            }#//end fo getting suitable test env51Testing软件测试网c9I7lB2a$av4YJ&[
   
A\:ES@O0            $cell_pos ++;51Testing软件测试网](^$e0lT8e/b
         }#//end for looking for test env      51Testing软件测试网-Hx&R/mwf
       }#//end for geting suitable testsuit51Testing软件测试网#tvx;Rs$\
    }#//end for looking for testsuit
&{8gM:x5l,s2m0}
8C_g$g?p?K6U0$workbook->close();

#####################################################################
xD7h#tu1h7z!P0# Name: GET_ARRAY                                                   #51Testing软件测试网1dG\.AQ%M0CT2Q
# Paramter:                                                         #51Testing软件测试网4@!EQ4msR/H!Y2ON
#          1. test ile name                                         #51Testing软件测试网*S ^D1x)E
# Function: Parse test file, read DATA to one Array.                #51Testing软件测试网 pk'w*^%R$B
# Return: @array_result :Two dimission array:                       #51Testing软件测试网 @?+fqU}~
#####################################################################51Testing软件测试网"v/bL!sRVv/?
sub GET_ARRAY
+z{/KXR'p%u0{51Testing软件测试网]'CXI1Rr3i8_^+Dv5Z1Z

my @array_result;
j0q6VU3Oxo0my ($result_file,) = @_;#"testResult.txt";51Testing软件测试网6b5Q ~p1Q,i Rw f
open (RESULT,$result_file)|| die ("Cannot open file: $result_file");51Testing软件测试网@| QW7R%I
my $i =0;
.g0?}?Ew
N\?0while (my $line=<RESULT>)
)i9Ef6ed/S*Y0{
"j~.K1wSL0   chomp($line);51Testing软件测试网^&c J2`~
   #print $line."\n";
%nz;?7n?o4xuP0   my @temp = split/\s+/,$line;51Testing软件测试网/q"^P7p9G`
   #print "@temp,\n";
6ic+UaZ kfwS3|0   my $j=0;51Testing软件测试网+w6p
W(g8tS
   for ( $j =0; $j < $#temp+1; $j++)
`1B aw4}0I$M3]S0   {
r0}fU`9@%p-Sn1^0      $array_result[$i][$j]=$temp[$j];
2s0QQ0pq*M3^0   }   
jn2J,sHZ/heE0$i++;
5nM2{;@i,w\v0}51Testing软件测试网d'C9^2c,e7j.m
uF
close(RESULT);51Testing软件测试网%B|E5]m IF W(N}
@array_result;51Testing软件测试网'oX x2?)e
}51Testing软件测试网GcO|+xC.C/}
51Testing软件测试网nkM["b~{"H