Automate values from text files to each sheet of excel file-CodePudding

Is there any way to automate values from multiple text files to multipe worksheets in the same excel file? (Ex: Textfile1 values to Sheet1, Textfile2 values to Sheet2 and so on) Because for now I have to change the name of the text file and the excel worksheet everytime I extract values from different text files so that I need an automation to ease the work. Below is the recent Perl script I am using.

use strict;
use warnings;
use Excel::Writer::XLSX;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);

{
    my $output_fn = 'result.xlsx';
    my $input_fn = 'accuracy_final_copy.txt.gz';
    my $workbook = Excel::Writer::XLSX->new( $output_fn );
    my $worksheet = $workbook->add_worksheet();
    my $zip = IO::Uncompress::Gunzip->new( $input_fn )
      or die "gunzip failed: $GunzipError\n";
 
    $worksheet->write( 0, 0,  "Accuracy value" );

    my $col = 0;
    my $row = 1;
    while (!$zip->eof()) {
        my $line = $zip->getline();
        chomp($line);
        next if $line !~ /\S/;  # skip empty lines
        my $value = $line;
        $worksheet->write( $row, $col, $value );
        $row  ;
    }
    $workbook->close();
}

CodePudding user response：

That's mostly all good what you have, just add a suitable loop

use warnings;
use strict;
use feature 'say';

use Excel::Writer::XLSX;

my @files = @ARGV ? @ARGV : die "Usage: $0 filenames\n";

my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );

for my $file (@files) {
    say "Processing $file";

    my $worksheet = $workbook->add_worksheet();

    my $fname = $file =~ s{.*/(.*)}{$1}r;  # extract barename
    $worksheet->write( 0, 0,  $fname );

    open my $fh, '<', $file or do { 
        warn "Can't open $file: $!"; 
        next
    };

    my ($col, $row) = (0, 1);
    while (<$fh>) { 
        chomp;
        my $value = $_; 
        $worksheet->write( $row, $col, $value );

          $row;
    }   
}
$workbook->close;

This writes a new worksheet for each file, with one row per line, in the first column.

CodePudding user response：

Here is an example which adds all files in the current directory ending with .gz as Sheet1, Sheet2, ..., etc. to the result.xlsx file:

use feature qw(say);
use strict;
use warnings;
use Excel::Writer::XLSX;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);

{
    my $output_fn = 'result.xlsx';
    my @input_files = <*.gz>;
    my $workbook = Excel::Writer::XLSX->new( $output_fn );
    for my $input_fn (@input_files) {
        say "Adding file $input_fn ..";
        add_sheet($input_fn, $workbook);
    }
    $workbook->close();
}

sub add_sheet {
    my ($input_fn, $workbook) = @_;

    my $worksheet = $workbook->add_worksheet();
    my $zip = IO::Uncompress::Gunzip->new( $input_fn )
      or die "gunzip failed: $GunzipError\n";

    $worksheet->write( 0, 0,  "Accuracy value" );
    my $col = 0;
    my $row = 1;
    while (!$zip->eof()) {
        my $line = $zip->getline();
        chomp($line);
        next if $line !~ /\S/;  # skip empty lines
        my $value = $line;
        $worksheet->write( $row, $col, $value );
        $row  ;
    }
}