linux - 在 Perl 中覆盖 Excel 文件的现有工作表

Question

引用the question I had asked before是:

我的代码如下:

        if ( !-e $xls_file ) #Checking whether to create a new Excel file or not
        {
                #if yes create a new excel file and 4 worksheets
                print OUTFILE "Creatin a new sheet.\n";
                $excelFileHandle= Spreadsheet::WriteExcel->new($xls_file) ;
                &InitFormatting ($excelFileHandle);#For format of each cell.    
                ($handle, $row, $col)    = &AddSheet("$monthYearStr", "$SHEET_TITLE", @ReportColLabels);
                ($handle1, $row1, $col1) = &AddSheet("$STATUS_CODE_SHEET_NAME", "$STATUS_CODE_SHEET_TITLE", @Status_Code_labels);
                ($handle2, $row2, $col2) = &AddSheet("$SUCCESS_COUNT_SHEET_NAME", "$SUCCESS_SHEET_TITLE", @Success_count_labels);
                ($handle3, $row3, $col3) = &AddSheet("$FAILURE_COUNT_SHEET_NAME", "$FAILURE_SHEET_TITLE", @Failure_count_labels);
                $new = 1;
        }

        else  #File exists needs to parsed and append the data.
        {
                print OUTFILE "Opening the existing sheet.\n";
                my $parser   = Spreadsheet::ParseExcel->new();
                my $workbook = $parser->parse($xls_file);
                if ( !defined $workbook )
                {   
                        die $parser->error(), ".\n";
                }   
                my $worksheet = $workbook->worksheet($monthYearStr);
                ( $row_min, $row_max ) = $worksheet->row_range();
                ( $col_min, $col_max ) = $worksheet->col_range();

                my $worksheet1 = $workbook->worksheet($monthYearStr);
                ( $row_min1, $row_max1 ) = $worksheet1->row_range();
                ( $col_min1, $col_max1 ) = $worksheet1->col_range();

                $new = 0;
        }

        if ( $new = 1 ) 
        {
                $handle->write($row, $col++, "$present_date", $num_field_format);
                ------Contiuned for remainin row and col for $monthYearStr $STATUS_CODE_SHEET_NAME $SUCCESS_COUNT_SHEET_NAME $FAILURE_COUNT_SHEET_NAME-------
        }

        elsif ( $new == 0)
        {
                ###Appending the data####

                $parser   = Spreadsheet::ParseExcel::SaveParser->new();
           $template = $parser->Parse($xls_file);
            if ( ! defined $template )
            {
                    die $parser->error(), ".\n";
            }

            my $worksheet = $template->worksheet($monthYearStr);
            my $row = $row_max + 1;

            my $cell = $worksheet->get_cell( $row_max, $row_max );
            my $format_number = $cell->{FormatNo};

            $worksheet->AddCell($row, $col++, "$present_date", $format_number);
            ---------Continued for remaining row and col only for $monthYearStr $STATUS_CODE_SHEET_NAME -----------------------------

 }

现在，当 Excel 文件存在时，如何每次覆盖工作表的 success_count 和 failure_count 。请建议我如何做到这一点。

Answer 1

如果没有 AddSheet 子例程，很难判断要做什么，但您应该能够根据 POD 使用类似的内容覆盖工作表中的单元格:

foreach $worksheet ($workbook->sheets()) {
   if( $worksheet->get_name() eq $SUCCESS_COUNT_SHEET_NAME ) {
       $worksheet->write('A1', $success_count);
   }
   elsif( $worksheet->get_name() eq $FAILURE_COUNT_SHEET_NAME ) {
       $worksheet->write('A1', $failure_count);
   }
}