rhysshadow has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to edit an existing excel file by using the SaveParser module that I found while searching the web. I am following the directions found here: Modifying Excel Files My problem is that while the code runs fine, and it's saving a new file, there is no new added data, and I can't figure out what I am doing wrong.


 my $filename = $fdat{status_sprx_file};
 my $tmpdir = '/tmp/sprx';

my $add_wl = $fdat{cc_download} eq 'cc_download' ? 1 : 0;
 my ($wlFile, $ext) = $filename =~ /(.+)\.(xls)/ if $add_wl == 1;

         if ($add_wl == 1)
         {
             $wlFile .= " Worklist" . "." . $ext;
         }



     my $tmpdir = '/tmp/sprx';
     mkdir $tmpdir;
     $fdat{status_sprx_file} =~ /([^\\\/]+)$/;
     $filename = $1;

 my $uploadFile = $cgi->upload("status_sprx_file");

      my $buffer;
      open UPLOADFILE, ">", "$tmpdir/$uploadFile" or die "Error: $!";

         binmode UPLOADFILE;

         while (<$uploadFile>)
         {
             print UPLOADFILE;
         }

         close UPLOADFILE;

my $parser = new Spreadsheet::ParseExcel::SaveParser if $add_wl == 1;
my $wlTemplate = $parser->Parse("$tmpdir/$filename") if $add_wl == 1;

my $worksheet;
$worksheet = $workbook->worksheet(0);

 while (1)
         {
             print STDERR "\nROW: $row\n" if $debug_sprx;
             my $c = $worksheet->get_cell($row, $check_col);
             my $format;
             my $token = undef;
             my $invCustNumCell;

             if (not $c)
             {
                 last if ++$blank_rows >= 15;
                 $row++;
                 next;
             }
             else
             {
                $blank_rows = 0;
             }

             my $str = $c->unformatted();
             ($str) = cleanString($str);

             if ($str eq 'CUSTOMER ID' and $add_wl == 1)
             {
                 print STDERR "adding cell\n";
                $wlTemplate->AddCell(0, $row, 11, 'WORKLIST');
             }
}

 if ($add_wl == 1)
         {
            my $wlWorkbook;

            local $^W = 0;

            $wlWorkbook = $wlTemplate->SaveAs("$tmpdir/$wlFile");
            $wlWorkbook->close();

         }

if ($add_wl == 1)
         {

        print $cgi->header(-access_control_allow_headers => 'X-Request
+ed-With',
                           -access_control_allow_origin => '*',
                           -content_disposition => "filename=$wlFile",
                           -content_type => 'application/vnd.ms-excel'
+,
                       );
            my $content = read_file("$tmpdir/$wlFile");
            print $content;
            #unlink("$tmpdir/$wlFile");
        }

[download]

I didn't unlink the file, so that I can download it from the server and see what it is from the server itself, and it's really just a copy of the uploaded file, so it's not saving the new data.

Any help would be appreciated

Replies are listed 'Best First'.
Re: Trouble using Spreadsheet::ParseExcel::SaveParser
by tangent (Parson) on Jun 07, 2021 at 21:02 UTC
    Where you have: 
    my $parser = new Spreadsheet::ParseExcel::SaveParser if $add_wl == 1;
my $wlTemplate = $parser->Parse("$tmpdir/$filename") if $add_wl == 1;

my $worksheet;
$worksheet = $workbook->worksheet(0);

    [download]
    I'm wondering where $workbook is coming from? Perhaps it should be: 
    $worksheet = $wlTemplate->worksheet(0);

    [download]
    Personally I would not use Spreadsheet::ParseExcel::SaveParser. It is a wrapper around Spreadsheet::ParseExcel and Spreadsheet::WriteExcel and I would just use those modules directly. Something like this. 
    my $parser = Spreadsheet::ParseExcel->new;
my $workbook = $parser->parse( $file );
my $worksheet = $workbook->worksheet( 0 );

my $new_workbook = Spreadsheet::WriteExcel->new( $new_file );
my $new_worksheet = $new_workbook->add_worksheet;

my ( $row_min, $row_max ) = $worksheet->row_range;
my ( $col_min, $col_max ) = $worksheet->col_range;

for my $row ( $row_min .. $row_max ) {
    for my $col ( $col_min .. $col_max ) {
        my $new_value = '';
        my $cell = $worksheet->get_cell( $row, $col );
        if ( $cell ) {
            my $value = $cell->value;
            if ( defined $value ) {
                $new_value = $value;
                if ( $col == $check_col && $value eq 'CUSTOMER ID' ) {
                    # do what you need for this condition
                }
            }
        }
        $new_worksheet->write( $row, $col, $new_value );
    }
}
$new_workbook->close;

    [download]
    If you can tell us what it is you wish to add to the new file we can help further.
     
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom