Create a excel sheet with formula ???

[samirpatry]

Hi All

I was create a program which produce some data.Now i use the Spreadsheet::WriteExcel to store the data.But i need some more fexible way to store the data.

 use Spreadsheet::WriteExcel;
my $workbook =Spreadsheet::WriteExcel->new("final_status.xls"); 
    $worksheet = $workbook->add_worksheet();
   
    $format = $workbook->add_format(); 
    $format->set_bold();
    $format->set_color('Blue');
    $format->set_align('center');

    
    $col = $row = 0;
      
     $worksheet->set_column( 0, 0, 20 ); #Serial No
     $worksheet->set_column( 1, 1, 15 ); #Test ID
     $worksheet->set_column( 2, 2, 15 ); # Status
     
     $worksheet->set_column( 3, 3, 15 );  # Total
     $worksheet->set_column( 4, 4, 15 );  # Pass
           
        
     $worksheet->write(A1,"Serial No:", $format);
     $worksheet->write(B1,"Testcase ID:", $format);
     $worksheet->write(C1,"Status:", $format);
      
         $worksheet->write(D1,"Total:", $format);
     $worksheet->write(D3,"Total Pass:", $format);
      $worksheet->write(D4,"Total Fail:", $format);
      $worksheet->write(E1,"Result:", $format);
        
         
        #  Manual Entry   
           $worksheet->write(C2, "pass");
          $worksheet->write(C3, "fail");
          $worksheet->write(C4, "pass");
          $worksheet->write(C5, "fail");
          $worksheet->write(C6, "fail");
          $worksheet->write(C7, "fail");
          $worksheet->write(C8, "fail");        
          $worksheet->write(C9, "fail");   
         
 ##########code to store the data in row wise         
my $row=3;
my $col=0;
my  $slno=1;
my $testid='00A';
my $status='pass';
$worksheet->write($row,$col,$status);     
my $array_ref;
my @array      = ($slno, $testid, $status);
$array_ref  = \@array;
$worksheet->write_row($row, $col, $array_ref);

###Formula To claculat the total

    $worksheet->write('E3', '=COUNTIF(C2:C9,"pass")'); 
    $worksheet->write('E4', '=COUNTIF(C2:C9,"FAIL")');

Here i manually enter the data.So i think about the other way to implement the same.

1.How can i create a subroutine so that from my main program i pass the 3 value and catch it and write the value in excel sheet.As i not sure about the usges of write_row in subroutine.

In case of formula the row and column not taking the variable.As i need to write the result in last row and last column.

Any hint ,suggestion will help a lot