Read a file and write segments into Excel using Spreadsheet::WriteExcel

user_6366187 0
Viewed 91

As an extension to my previous question, I need to extract the input file from first line until an empty line and paste the output into a single cell of xls in an order. For example, "E: sda E: qwe E: ass" will be printed in cell A1, NA will be printed on A3, "E: sda E: qwe E: ass" will be printed in cell A5. The pattern of the input file will change, but each segment is separated by an empty line. The below code snippet is only able to print the whole file content into a cell.

Input file:

E: sda
E: qwe
E: sss

NA

E: sda
E: qwe
E: sss

NA

NA

E: xx
E: xxxs

NA

Code:

use strict;
use warnings;
use Spreadsheet::WriteExcel;
my $filename = 'all_in_one.txt';
my $workbook = Spreadsheet::WriteExcel->new("sad.xls");
my $wrksheet = $workbook->add_worksheet("summary");


    for (my $i = 9) {
            my $result1 = read_out_your_file_misc($filename,"\n");
            $wrksheet->write($i, 1, [$result1]);
            $i = $i + 2;
        
    }

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);
    }
}
xls perl
1 Answers

You are reading each line of your input file into a separate element in an array. But, you don't want to do that.

If you change the input record separator variable ($/) to use paragraph mode, you can place each group of lines into a separate cell in every other row:

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $filename = 'all_in_one.txt';
local $/ = ""; # paragraph mode
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

my $workbook = Spreadsheet::WriteExcel->new("sad.xls");
my $wrksheet = $workbook->add_worksheet("summary");
for my $i (0 .. $#content_of_file) {
    $wrksheet->write(2*$i, 1, $content_of_file[$i]);
}

The reason you get the whole file in one cell is that you join all the lines back together into a scalar variable ($result1), then write that single value to a cell. Also, the for loop is a little strange, and it only loops once.

user_1977580
Related