in reply to PERL : Parse/Open an excel file and replace the negative values in a sheet to '0'

The synopsis at the Spreadsheet::ParseExcel page shows an example program that shows how to visit each of the cells of a spreadsheet, and could be modified to test each for a negative value and replace it with 0.

It might work better to use VBA for this task.

Crudest solution would be convert to csv and open with Text::CSV. You lose formatting and formulas that way. But it would take 5 minutes to write.

Dum Spiro Spero
  • Comment on Re: PERL : Parse/Open an excel file and replace the negative values in a sheet to '0'

Replies are listed 'Best First'.
Re^2: PERL : Parse/Open an excel file and replace the negative values in a sheet to '0'
by DarshanS (Initiate) on May 29, 2015 at 07:26 UTC
    Was able to fix the same with a simple tweek in formula itself. Just a SAMPLE:- 
    for(my ($i,$j) = (2,1);$i<=$size+2;$i++,$j++){
my $formula = $worksheet->write('D'.$i,"=IF((C${j}-C${i})*6 < 0, 0,(C$
+{j}-C${i})*6)");        }

"=IF((C${j}-C${i})*6 < 0, 0,(C${j}-C${i})*6)"

    [download]
[reply]
[d/l]
Re^2: PERL : Parse/Open an excel file and replace the negative values in a sheet to '0'
by DarshanS (Initiate) on May 27, 2015 at 06:03 UTC

    kindly do help me out. I had gone through the examples in Parce:Excel category, couldn't figure out the solution though. Have a good day.

[reply]

      See this article - note the warnings

      That link also contains links to numerous example programs. I'm not here to do your homework for you. But I will be happy to help you troubleshoot code you have written.

      Dum Spiro Spero
[reply]

        Infact my problem is something else. If i am able to get the logic for the question i have asked for, i would be able to resolve. As far i came till now on this. Just incase if you would like to help.! 

        

use warnings;
use Spreadsheet::ParseExcel::SaveParser;

my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Test.xls');

my $worksheet = $workbook->worksheets(1);
my $row = 4;
my $col = 5;
my $cell = $worksheet->get_cell( $row, $col );
#print $cell,"\n";
print "value = ",$cell->value(), "\n";
my $cell_value = cell->value();

if($cell_value < 0 ) {
    $cell_value = 0;
}


$workbook->SaveAs( 'Test_Parse.xls' );

        [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom