dnbnor_to_json.pl

dnbnor_to_json.pl

#!/usr/bin/perl

use strict;
use utf8;
use warnings;
use Switch;
use Spreadsheet::ParseExcel;
use JSON::XS;

my $excel = Spreadsheet::ParseExcel->new;

die "Need an xls-file containing DNB Nor transaction list as frowst argument\n" unless @ARGV;

my $doc = $excel->Parse($ARGV[0]) or die('Could not parse excel file\n', $@);

my @transactions = (); 

for(my $sheet=0; $sheet < $doc->{SheetCount} ; $sheet++) {
    my $current_sheet = $doc->{Worksheet}[$sheet];
    for(my $row = $current_sheet->{MinRow}; defined $current_sheet->{MaxRow} && $row <= $current_sheet->{MaxRow}; $row++) {
        # Skip header row
        if ($row == $current_sheet->{MinRow}){
            next;
        }   
        my %transaction = (); 
        for(my $column = $current_sheet->{MinCol}; defined $current_sheet->{MaxCol} && $column <= $current_sheet->{MaxCol} ; $column++) {
            my $cell = $current_sheet->{Cells}[$row][$column];
            if (defined($cell)){
                switch ($column){
                    case 0 { $transaction{'date'} = $cell->Value || undef; }
                    case 1 { $transaction{'description'} = $cell->Value || undef; }
                    case 2 { $transaction{'interest_date'} = $cell->Value || undef; }
                    case 3 { 
                        $transaction{'debit'} = $cell->Value || undef;
                        $transaction{'credit'} = '0.00';
                        }   
                    case 4 { 
                        $transaction{'credit'} = $cell->Value || undef;
                        $transaction{'debit'} = '0.00';
                        }   
                }   
            }   
        }   
        push @transactions, \%transaction;
    }   
}   

my $json = JSON::XS->new->utf8->encode({'transactions' => \@transactions});
print STDOUT $json . "\n";

exit 0;