Search code examples
perlxls

Print stored string into xls in a single cell

I have an array stored with string which I extracted from a text file and wanted to print on xls in a single cell. The below command able to print in the same column but separated row. Finding a way to print in a single cell.

#!/usr/bin/perl
    
use strict;
use warnings;
use Spreadsheet::WriteExcel;


my $workbook = Spreadsheet::WriteExcel->new("result.xls");
my $wrksheet = $workbook->add_worksheet("summary");
$wrksheet->write_col(1,0,[@{read_out_your_file_misc("temp_stage.txt")}]);
$workbook->close() or die "Error closing file: $!";
    
sub read_out_your_file_misc {
    my $content_of_file = [];
    open my $fhh, '<', shift() or die "can't open file:$!";
    while (<$fhh>) {
        chomp;
        #push @{$content_of_file}, $_, $/;
    push @{$content_of_file}, $_;
    }
    return $content_of_file;
}
Solution

  • If you supply an array with more than one element in it, more than one row in column 0 (A) will be populated by write_col. You can join the elements in the array to fix that. If this is a pattern that you use frequently, you may even change your read_out_your_file_misc function to either return an array or a scalar, depending on what you want.

    sub read_out_your_file_misc {
    my $filename = shift or die "ERROR: read_out_your_file_misc needs a filename";
    my $delim = shift || ',';        # make the default delimiter a comma

    open my $fhh, '<', $filename or die "ERROR: $filename: $!";
    my @content_of_file = <$fhh>;    # read the whole file at once
    close $fhh;
    chomp @content_of_file;          # chomp the complete array

    if(wantarray) {    # return an array if that's wanted
        @content_of_file;
    } else {           # else return it as a scalar with $delim between elements
        join($delim, @content_of_file);
    }
}

    Now depending on the type of the receiving variable, the function behaves differently.

    Get the file content as a scalar and populate A2 only:

    my $result = read_out_your_file_misc('temp_stage.txt');
$wrksheet->write_col(1, 0, [$result]);

    Get the file content as an array and populate from A4 and downwards:

    my @result = read_out_your_file_misc('temp_stage.txt');
$wrksheet->write_col(3, 0, \@result);