Script for fecth data from excel files

#!/usr/bin/env perl

use v5.14;
use utf8;
use strict;
use warnings;

use FindBin qw/$Bin/;

use Docopt;
use JSON;
use File::Slurp qw/read_file write_file/;

use Spreadsheet::XLSX;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw/sheetRef/;

sub read_json_file {
    my $filepatch = shift;
    die "File not exists" unless -e $filepatch;

    my $json = JSON->new()->utf8()->relaxed();
    my $data = eval { $json->decode( scalar read_file($filepatch) ) };

    die "Wrong JSON file format" unless $data;
    return $data;
}

sub write_json_file {
    my ($filepatch, $data, $pretty) = @_;

    my $json = JSON->new()->utf8();
    $json->pretty() if $pretty;

    return write_file( $filepatch, $json->encode($data) );
}

sub mute {
    my $sub = shift;
    return eval {
        local *STDERR;
        open STDERR, ">>", undef;
        $sub->();
    };
}

sub read_excel_file {
    my $filepatch = shift;
    die "File not exists" unless -e $filepatch;

    my $extension = lc [split '\.', $filepatch]->[-1];
    my $workbook;

    if ($extension eq 'xlsx' or $extension eq 'xlsm') {
        $workbook = mute(sub{ Spreadsheet::XLSX->new( $filepatch ) });
    } elsif ( $extension eq 'xls' ) {
        $workbook = mute(sub{ Spreadsheet::ParseExcel->new()->parse( $filepatch ) });
    } else {
        die "Wrong excel file extension";
    }

    die "Not valid excel file" unless $workbook;
    return $workbook;
}

sub fetch_result {
    my ($map, $workbook) = @_;

    my %worksheets = map {$_->get_name() => $_} $workbook->worksheets();

    walk_recursive(
        $map,
        sub {
            my $re = qr/^(?<worksheet>.+)!((?<cell>\w+\d+)|(?<range>\w+\d+:\w+\d+))$/;
            return "WRONG_ADDRESS_FORMAT" unless shift =~ $re;

            my $worksheet = $worksheets{ $+{worksheet} };
            return "WRONG_WORKSHEET_NAME" unless $worksheet;

            if ($+{cell}) {
                my $cell = $worksheet->get_cell(sheetRef( $+{cell} ));
                return "WRONG_CELL_ADDRESS" unless $cell;

                return $cell->unformatted();
            }
            if ($+{range}) {
                my ($start, $end) = map { [sheetRef($_)] } split ':', $+{range};
                my $isFlat = $start->[0] == $end->[0] || $start->[1] == $end->[1];

                my @range;
                foreach my $col ($start->[0]..$end->[0]) {
                    my @row = map {
                        my $cell = $worksheet->get_cell($col, $_);
                        $cell ? $cell->unformatted() : "WRONG_CELL_ADDRESS";
                    } ($start->[1]..$end->[1]);
                    push @range, $isFlat ? @row : \@row;
                }

                return \@range;
            }

        }
    );

    return $map;
}

sub walk_recursive {
    my ($data, $sub) = @_;
    my $ref_type = ref($data);

    if (!$ref_type) {
        $data = $sub->($data);
    } elsif ( $ref_type eq 'HASH' ) {
        foreach my $key ( keys %$data ) {
            $data->{$key} = walk_recursive( $data->{$key}, $sub );
        }
    } elsif ( $ref_type eq 'ARRAY' ) {
        for (my $i = 0; $i < @$data; $i++) {
            $data->[$i] = walk_recursive( $data->[$i], $sub );
        }
    }

    return $data;
}

my $doc = <<"EOF";
Script for fetch data from excel files

Usage:
    $FindBin::Script <excel> <map> <out> [--pretty-output]

Options:
    <excel>             Path to excel file
    <map>               Path to JSON map file
    <out>               Path to output JSON file
    -p --pretty-output  Human-readable output JSON formating
EOF

my $opts     = docopt( doc => $doc );
my $map      = read_json_file($opts->{'<map>'});
my $workbook = read_excel_file($opts->{'<excel>'});
my $result   = fetch_result($map, $workbook);

write_json_file($opts->{'<out>'}, $result, $opts->{'--pretty-output'});

1;