Re^3: Deleting entire row and column

[Happy-the-monk]

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

[harishnv]

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
        }
    }
}

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

[Happy-the-monk]

$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

[harishnv]

how to also copy the format of the original and sheet name? I tired writing this but didn't change the name of the sheet inside the foreach $worksheet $worksheet1 = $workbook->addworksheet($worksheet);