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

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

I am having an Excel sheet which contains several numeric entries. Out of which, there are some negative numbers as well. I need to replace all the negative values in it to '0'. Over-writing in same excel is preferable output. More details: Excel name - Test.xls , Sheet name - Sheet1 , Say there are 20 - rows and columns containing several numeric entries. These rows and columns contains formulas too - Say, the values in D column are derived from the entries in C column after a minor manipulation like Add/Sub etc...

Steps i have tried:- -------------------

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

    # Open the template with SaveParser
    my $parser   = new Spreadsheet::ParseExcel::SaveParser;
    my $template = $parser->Parse('Test.xls');
    my $f_volume;
    my $f_volume = $workbook->add_format(set_num_format => '#,##0');
    $worksheet->write('A:D', $f_volume);
[download]

Hi, I am very new to Perl. Please do help me out resolve a scenario where i am stuck. I tried from the already suggested similar query, but i couldn't fix it.

Comment on PERL : Parse/Open an excel file and replace the negative values in a sheet to '0' Download Code

Replies are listed 'Best First'.
Re: PERL : Parse/Open an excel file and replace the negative values in a sheet to '0' by GotToBTru (Prior) on May 26, 2015 at 19:34 UTC
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	[reply]
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]
Re^3: PERL : Parse/Open an excel file and replace the negative values in a sheet to '0' by GotToBTru (Prior) on May 27, 2015 at 13:35 UTC
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]
Re^4: PERL : Parse/Open an excel file and replace the negative values in a sheet to '0' by DarshanS (Initiate) on May 28, 2015 at 05:34 UTC
Re^5: PERL : Parse/Open an excel file and replace the negative values in a sheet to '0' by GotToBTru (Prior) on May 28, 2015 at 14:13 UTC
Re: PERL : Parse/Open an excel file and replace the negative values in a sheet to '0' by stevieb (Canon) on May 26, 2015 at 19:15 UTC
Please wrap your code/output in <code></code> tags, as it makes it awfully difficult to tell what's happening and to test it without them. Cheers, -stevieb	[reply]
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 05:59 UTC
Changed accordingly, kindly do help me out. I had gone through the examples in Parce:Excel category, couldn't figure out though.	[reply]


Perl Monk, Perl Meditation
	PerlMonks