How to merge cells dynamically using some variable as input in excel using perl

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

Hi Everyone, Hope you all are doing well, this is my first question here. Actually I'm trying to merge cells dynamically using merge_range. I need to make every cells merged in column A only when column B has some common string. I'm very new to perl, so not aware of much methods in it. I have tried this, but not sure how to proceed for this.

while (<CSVFILE1>) {
    if ($csv->parse($_)) {
        my @Fld = $csv->fields;

        my $col = 0;
        my $a = 1;
        foreach my $token (@Fld) {
            for my $a (@Fld) {
               my $a    = $a + 10;
               my $b    = $a + 10;
            }
            my $range = "A".$a.":A".$b;
            $worksheet->write($row, $col, $token);
            $worksheet->merge_range($range, $token, $merge_format);
                my $temp    = $a;
            my $a    = $b;
            my $b    = $temp;
            $col++;
                    $token++;
     }
        $row++;
    }
    else {
        my $err = $csv->error_input;
        print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
    }
}
[download]

Comment on How to merge cells dynamically using some variable as input in excel using perl Download Code

Replies are listed 'Best First'.
Re: How to merge cells dynamically using some variable as input in excel using perl by kcott (Archbishop) on Jun 21, 2018 at 10:33 UTC
G'day Sandeep Kumar, Welcome to the Monastery. "I'm very new to perl, ..." That's quite OK. We all were once. :-) There's possibly other issues here but, without being able to see any code outside of the `while` loop you posted, it would be pure guesswork trying to divine them. So, for now, I'm just going to focus on two areas: the variables `$a` and `$b`; and, the use of `my`. In perlvar, you'll see that $a and $b are special variables. While you are learning Perl, I strongly recommend that you do not use them for anything except their special purpose. That's a standalone issue unrelated to what follows. From the code you posted, it looks like you haven't really understood my. You actually have four instances of "`my $a ...`" throughout your code; each has its own scope; and, mostly, they do nothing useful as they go out of scope before being used in a meaningful way. Your treatment of `$b` is similar. Try adding `print` statements throughout your code to see the values of `$a` and `$b`: you may be surprised. I don't know if you thought you needed `my` as part of the assignment (you don't, by the way); however, I think it's evident that there's a big hole in your understanding here. I suggest you go back to basics and read: "perlintro: Perl introduction for beginners". That may well raise further questions, which is fine: just ask. — Ken	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: How to merge cells dynamically using some variable as input in excel using perl
by kcott (Archbishop) on Jun 21, 2018 at 10:33 UTC

G'day Sandeep Kumar,

Welcome to the Monastery.

"I'm very new to perl, ..."

That's quite OK. We all were once. :-)

There's possibly other issues here but, without being able to see any code outside of the while loop you posted, it would be pure guesswork trying to divine them. So, for now, I'm just going to focus on two areas: the variables $a and $b; and, the use of my.

In perlvar, you'll see that $a and $b are special variables. While you are learning Perl, I strongly recommend that you do not use them for anything except their special purpose. That's a standalone issue unrelated to what follows.

From the code you posted, it looks like you haven't really understood my.

You actually have four instances of "my $a ..." throughout your code; each has its own scope; and, mostly, they do nothing useful as they go out of scope before being used in a meaningful way. Your treatment of $b is similar. Try adding print statements throughout your code to see the values of $a and $b: you may be surprised.

I don't know if you thought you needed my as part of the assignment (you don't, by the way); however, I think it's evident that there's a big hole in your understanding here. I suggest you go back to basics and read: "perlintro: Perl introduction for beginners". That may well raise further questions, which is fine: just ask.

— Ken

[reply]
[d/l]
[select]