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';
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 $workbook = Spreadsheet::WriteExcel->new("/var/log/asterisk_agents_log/$name");
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 $calc = $workbook->addworksheet("Calc");
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 @calc = ('RNA Queue','RNA Operator','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');
$calc->autofilter('A1:F1');
set_columns($ringnoanswer, "A",
[5, 16, 13, 16, 13, 45, 15, 15, 12],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitempty, "A",
[5, 16, 13, 16, 13, 45, 13],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitempty_of, "A",
[5, 16, 13, 16, 13, 25, 13],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitwithtimeout, "A",
[5, 16, 13, 16, 13, 45, 20, 3],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitwithtimeout_of, "A",
[5, 16, 13, 16, 13, 45, 20, 3],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($calc, "A",
[15, 25, 20, 40, 25, 5],
[$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);
$calc->write('A1', [ @calc ], $ch);
$calc->write('A3', 1.2345);
$calc->write('A4', '=SIN(PI()/4)');
$calc->write('C2', 'internal:ringnoanswer!B1');
$calc->write('D5', '=SUM(1, 2, 3)'); # Okay
$calc->write('D6', 1, '=SUM(1, 2, 3)');
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);
$ringnoanswer->activate(); # I'd like the second worksheet to be active
$workbook->close(); # play nice
exit(0);
print_setup($ringnoanswer);
print_setup($exitempty);
print_setup($exitempty_of);
print_setup($exitwithtimeout);
print_setup($exitwithtimeout_of);
print_setup($calc);
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) = @_;
my $csv = Text::CSV_XS->new({
'quote_char' => '', # what? no quote character? you got it!
'escape_char' => '\\', # a backslash
'sep_char' => '|',
'binary' => 0
});
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";
}
}
}
11-17-2016
