Code:
#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Formula;
use Excel::Template;
use Text::CSV_XS;
my $name = 'call_report.xls';
# other files we are reading
my $file_ring = "/var/log/asterisk_agents_log/ringnoanswer_strip.log";
my $file_exitempty = "/var/log/asterisk_agents_log/exitempty_strip.log";
my $file_exitempty_of = "/var/log/asterisk_agents_log/exitempty-of_strip.log";
my $file_exittimeout = "/var/log/asterisk_agents_log/exitwithtimeout_strip.log";
my $file_exittimeout_of = "/var/log/asterisk_agents_log/exitwithtimeout-of_strip.log";
my $file_rna = "/var/log/asterisk_agents_log/rna_strip.log";
my $workbook = Spreadsheet::WriteExcel->new("/var/log/asterisk_agents_log/$name");
# worksheets
my $ringnoanswer = $workbook->addworksheet("RingNoAnswer");
my $exitempty = $workbook->addworksheet("ExitEmpty");
my $exitempty_of = $workbook->addworksheet("ExitEmpty-OF");
my $exitwithtimeout = $workbook->addworksheet("ExitWithTimeout");
my $exitwithtimeout_of = $workbook->addworksheet("ExitWithTimeout-OF");
my $rna = $workbook->addworksheet("RNA");
my @array_ring = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Operator', 'Issue', 'WaitTime' );
my @array_exittimeout = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue', '' );
my @array_exittimeout_of = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue', '' );
my @array_exitempty = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue' );
my @array_exitempty_of = ( 'Day','Start Date', 'Start Time', 'End Date', 'End Time', 'Queue', 'Issue' );
my @array_rna = ('Date', 'Time', 'Queue', 'RNA Operator', 'RNA Event', 'RNA WaitTime', '', 'RNA Operator', 'Count');
my %cf = (
font => 'Arial',
border => 1,
border_color => 'black'
);
my %bold = (
bold => 1
);
my %ch = (
align => 'center',
valign => 'center',
font => 'Arial',
size => 11,
border => 1,
border_color => 'black',
bg_color => 'yellow'
);
my $ch = $workbook->addformat(%ch, %bold); # header
my $cn = $workbook->addformat(%cf, size => 10); # normal text
my $cs = $workbook->addformat(%cf, size => 8); # small text
$ringnoanswer->autofilter('A1:I1');
$exitempty->autofilter('A1:G1');
$exitempty_of->autofilter('A1:G1');
$exitwithtimeout->autofilter('A1:H1');
$exitwithtimeout_of->autofilter('A1:H1');
$rna->autofilter('A1:H1');
set_columns($ringnoanswer, "A",
[8, 16, 13, 16, 13, 45, 15, 15, 12],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitempty, "A",
[8, 16, 13, 16, 13, 45, 13],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitempty_of, "A",
[8, 16, 13, 16, 13, 25, 13],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitwithtimeout, "A",
[8, 16, 13, 16, 13, 45, 20, 3],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitwithtimeout_of, "A",
[8, 16, 13, 16, 13, 45, 20, 3],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($rna, "A",
[15, 15, 45, 20, 15, 15, ,50, 25, 5],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
$ringnoanswer->write('A1', [ @array_ring ], $ch);
$exitempty->write('A1', [ @array_exitempty ], $ch);
$exitempty_of->write('A1', [ @array_exitempty_of ], $ch);
$exitwithtimeout->write('A1', [ @array_exittimeout ], $ch);
$exitwithtimeout_of->write('A1', [ @array_exittimeout ], $ch);
$rna->write('A1', [ @array_rna ], $ch);
# I'm trying to use formula for those columns below. Unfortunately not working
#$rna->write_formula('H2', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H1, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H3', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H2, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H4', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H3, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H5', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H4, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H6', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H5, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H7', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H6, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H8', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H7, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H9', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H8, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H10', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H9, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H11', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H10, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H12', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H11, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H13', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H12, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H14', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H13, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H15', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H14, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H16', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H15, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H17', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H16, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H18', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H17, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H19', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H18, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H20', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H19, $D$3:$D$150)), 0)),"")');
#$rna->write_string('I2', '=COUNTIFS($D$1:$D$150,$H2)');
#$rna->write_string('I3', '=COUNTIFS($D$1:$D$150,$H3)');
#$rna->write_string('I4', '=COUNTIFS($D$1:$D$150,$H4)');
#$rna->write_string('I5', '=COUNTIFS($D$1:$D$150,$H5)');
#$rna->write_string('I6', '=COUNTIFS($D$1:$D$150,$H6)');
#$rna->write_string('I7', '=COUNTIFS($D$1:$D$150,$H7)');
#$rna->write_string('I8', '=COUNTIFS($D$1:$D$150,$H8)');
#$rna->write_string('I9', '=COUNTIFS($D$1:$D$150,$H9)');
#$rna->write_string('I10', '=COUNTIFS($D$1:$D$150,$H10)');
#$rna->write_string('I11', '=COUNTIFS($D$1:$D$150,$H11)');
#$rna->write_string('I12', '=COUNTIFS($D$1:$D$150,$H12)');
#$rna->write_string('I13', '=COUNTIFS($D$1:$D$150,$H13)');
#$rna->write_string('I14', '=COUNTIFS($D$1:$D$150,$H14)');
#$rna->write_string('I15', '=COUNTIFS($D$1:$D$150,$H15)');
#$rna->write_string('I16', '=COUNTIFS($D$1:$D$150,$H16)');
#$rna->write_string('I17', '=COUNTIFS($D$1:$D$150,$H17)');
#$rna->write_string('I18', '=COUNTIFS($D$1:$D$150,$H18)');
#$rna->write_string('I19', '=COUNTIFS($D$1:$D$150,$H19)');
#$rna->write_string('I20', '=COUNTIFS($D$1:$D$150,$H20)');
write_from_log($ringnoanswer, $file_ring);
write_from_log($exitempty, $file_exitempty);
write_from_log($exitempty_of, $file_exitempty_of);
write_from_log($exitwithtimeout, $file_exittimeout);
write_from_log($exitwithtimeout_of, $file_exittimeout_of);
write_from_log($rna, $file_rna);
$ringnoanswer->activate();
$workbook->close();
exit(0);
print_setup($ringnoanswer);
print_setup($exitempty);
print_setup($exitempty_of);
print_setup($exitwithtimeout);
print_setup($exitwithtimeout_of);
print_setup($rna);
sub print_setup {
my $exitempty = shift;
$exitempty->set_landscape();
$exitempty->center_horizontally();
$exitempty->set_margins_LR(0.25);
$exitempty->set_margin_top(0.5);
$exitempty->set_margin_bottom(0.36);
$exitempty->set_footer('&L&D&R&P of &N', 0.17);
$exitempty->hide_gridlines();
}
sub set_columns {
my ($ws, $col, $width, $format, $hidden) = @_;
for (my $i=0; $i < @$width; $i++) {
my $column = "$col:$col";
$ws->set_column($column, $$width[$i], $$format[$i], $$hidden[$i]);
$col++;
}
}
sub write_from_log {
my ($worksheet, $file) = @_;
# Create a new CSV parsing ojbect with the CSV options that I needed
my $csv = Text::CSV_XS->new({
'quote_char' => '', # what? no quote character? you got it!
'escape_char' => '\\', # a backslash
'sep_char' => '|',
'binary' => 0
});
# Row and column are zero indexed!
my $row = 1;
open (CSVFILE, "<", $file) || die "Unable to open $file for reading: $!, stopped";
while (<CSVFILE>) {
if ($csv->parse($_)) {
my @Fld = $csv->fields;
my $col = 0;
my $keep = 1;
if ($keep) {
foreach my $token (@Fld) {
if ($token =~ /^<img/) {
$token =~ /<img src="(.*)" loc="(.*)">/i;
my ($img, $loc) = ($1, $2);
$worksheet->insert_bitmap($loc, $img);
} else {
$worksheet->write($row, $col, $token);
}
$col++;
}
}
$row++ if ($keep);
} else {
my $err = $csv->error_input;
print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
}
}
}