CODEWITHSUNDEEP
perl
user3185596
user3185596

Reputation: 79

Excel::Writer::XLSX append text on a cell

I looked at the documentation couldn;t find it so far. I'm using "Excel::Writer::XLSX" to write text to excel.

Question is : How can I append any text on a cell. Supposed cell A1 has "abc" already written , how can i append it with say "def" with any delimiter . Finally A1 cell should have "abc-def".

Currently its overwritten the old data and only showing "def" . 

File has say :  
Hostname name abc
Random Lines 
Hostname name def
Random Data 



open my $FH, '<', $filenames or die $!;
while(<$FH>)
{
            if($_ =~ /^Hostname+\s+name+\s(.*)/){
            my $hostname = $1;
            print "\nHostname : $1\n";      
                $worksheet->write(0, 0, $hostname);
            }
}

Now if you look at the code..when the first regex matach its write on the cell as abc ...Now when the regex match next time ...it delete abc and write def..I wanted someway to append it...

thanks in advance.

Upvotes: 1

Views: 702

Answers (1)

bolav
bolav

Reputation: 6998

Excel::Writer::XLSX does not expose functionality to read the temporary file, while you create it. You need to save the value outside the loop:

use strict;
use warnings;

my $hostname = '';
my $delimiter = '-';
open my $FH, '<', $filenames or die $!;
while(<$FH>)
{
    if($_ =~ /^Hostname+\s+name+\s(.*)/){
        $hostname .= $delimiter if ($hostname);
        $hostname .= $1;
        print "\nHostname : $hostname\n";      
    }
}
$worksheet->write(0, 0, $hostname);

To hack the internal data structure to get the temporary value this is how it says it:

# Write a shared string or an in-line string based on optimisation level.
if ( $self->{_optimization} == 0 ) {
    $index = $self->_get_shared_string_index( $str );
}
else {
    $index = $str;
}
$self->{_table}->{$row}->{$col} = [ $type, $index, $xf ];

So to read the string back, without optimisation:

my $value = $self->{_table}->{$row}->{$col}->[1];

Upvotes: 2

Related Questions