#!/usr/bin/perl 
use strict;
use DBI;
use Spreadsheet::WriteExcel;

my $listId = shift;
my $p_filename = shift;

use constant C_HEADING => 0;
use constant C_WIDTH   => 1;
my @columns = (
  ['AgentID', 10 ], 
  ['Qu 1', 5 ], ['Qu 2', 5 ], ['Qu 3', 5 ], 
  ['Qu 4', 5 ], ['Qu 5', 5 ], ['Qu 6', 5 ], 
  ['Qu 7', 5 ], ['Qu 8', 5 ], ['Avg', 10 ]
);

my $workbook = Spreadsheet::WriteExcel->new($p_filename);
my $sheet    = $workbook->add_worksheet('Data');
my $fmt_default = $workbook->add_format(
  font => 'Verdana', border => 1, align => 'center');
   
my $fmt_bold = $workbook->add_format(
  font => 'Verdana', border => 1, align => 'center',
  bold => 1); 

my $fmt_avg = $workbook->add_format(
  num_format => '0.00',   border     => 1,
  align      => 'center', bold       => 1,
); 
   
for (0..$#columns){
  $sheet->write(0,$_,$columns[$_]->[C_HEADING], $fmt_bold);
  $sheet->set_column($_, $_, $columns[$_]->[C_WIDTH]);
}

my %score = (
  'strongly agree'=>5,'agree'=>4,'neutral'=>3, 
  'disagree'=>2,'strongly disagree'=>1 );

my $dsn = 'Server=server;Database=database;Trusted_Connection=Yes';
my $dbh = DBI->connect("DBI:ODBC:Driver={SQLServer};$dsn")
  or die("\n\nCONNECT ERROR:\n\n$DBI::errstr");

my $sth = $dbh->prepare(<<EOQ
  SELECT Custom1,  Custom13, Custom14, Custom15, Custom16,
         Custom17, Custom18, Custom19, Custom20
  FROM [purl].[dbo].[MailFiles]
  WHERE ListId=? AND Completed =?
EOQ

);
$sth->execute($listId,1);

my $i = 1;
while ( my $row = $sth->fetchrow_arrayref ) {
  #print "$_ = $ans{lc $_} ; " for @$row; print "\n";
  $sheet->write_string($i,0,$row->[0], $fmt_default);
  $sheet->write_number($i,$_,$score{lc $row->[$_]},$fmt_default) for (
+1..$#$row);
  $i++;
  $sheet->write_formula($i-1, 9, "=AVERAGE(B$i:I$i)", $fmt_avg  );
}

# average
$sheet->write_string($i,0,'Average', $fmt_default);
for my $c (1..8){
  my $col = chr(65+$c);
  $sheet->write_formula($i, $c, "=AVERAGE(${col}2:${col}$i)",$fmt_avg 
+ );
}
$sheet->activate();
exit;
[download]