In your programme, create a new worksheet and copy the current data into it as follows:

Start reading at 1,1 in the original worksheet...
...only subtract 1 from both coordinates when you write to the new one, so you achieve the goal.

After the new worksheet is done writing, delete the original,
then rename the new one with the name of the original one.

Cheers, Sören

use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;
use Spreadsheet::WriteExcel;

my $val=0;
my $last_col;
my $col=0;
my $row=0;
my @req_sfr_array;
my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('formatting.xls');
my $row1;
my $col1;

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}
my $workbook = Spreadsheet::WriteExcel->new("perl.xls");
    $worksheet  = $workbook->addworksheet();
    
   

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

    print DEBUG_LOG "PARSING WORKSHEET:", $worksheet->get_name(), "\n"
+;
    
    print "\n";
    print "ROW_MIN = $row_min, ROW_MAX = $row_max\n";
    print "COL_MIN = $col_min, COL_MAX = $col_max\n";
    print "\n";

    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {
            my $cell = $worksheet->get_cell( $row, $col );
            next unless ($cell);;
            next unless ($cell->value() =~ /\S+/);;
            
            my $val = 0;
            print "Row, Col    = ($row, $col)\n";
            print "Value       = ", $cell->value(), "\n";
            print "\n";
            $val = $cell->value();
        }
        if($val eq "Register")
        {
            if($row==0 and $col!=0)
            {
                $row1=$row;
                $col1=$col-$col;
            }
            elsif($row!=0 and $col==0)
            {
                $row1=$row-$row;
                $col1=$col;
            }
            else
            {
            $col1=$col-$col;
            $row1=$row-$row;
            }
         $worksheet->write($row1, $col1, "$val");
    
        }
        else
        {
        // i'm not getting what logic i should write to shift the cell
+ after encountering first cell and shifting
        }
    }
}
[download]

not able to figure out what to write next, help me out!

$worksheet->write($row1, $col1, "$val");

Basically, you will need a new worksheet (as said above) to write into for any worksheet you want to rewrite.

So the code for writing will be: $new_worksheet->write($row -1, $col -1, "$val");

Cheers, Sören

Créateur des bugs mobiles - let loose once, run everywhere.
(hooked on the Perl Programming language)

XLS file