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


#
#createxls_charts.pl - example of creating Excel files from perl scripts
#(c) Shpatserman Maria
#



#creating Excel file
my $workbook = Spreadsheet::WriteExcel->new("/home/masha/ExampleExcel_with_Chart.xls");

#open file with pattern  of col names and list names
my $file = '/home/masha/article_pattern.txt';

open FH, '<:encoding(utf8)', $file  or die "Couldn't open $file: $!\n";
my @array;
while (<FH>) {
    next if /^#/; # Ignore the comments in the pattern file.
    chomp;
    push(@array,$_);
}

close FH;

my $worksheet1=$workbook->add_worksheet('Hotels_Rating');

my $format = $workbook->add_format(valign=>'vcenter',align=>'left');
$format->set_bold();
$format->set_underline();
$worksheet1->write('B1',$array[0],$format);
$worksheet1->write('C1',$array[1],$format);
$worksheet1->write('B6',$array[2],$format);
#connecting to the database
my $dbh=DBI->connect('dbi:mysql:example:localhost', 'root', '') or die "Can't connect";
my $sth=$dbh->prepare('SELECT * FROM hotels;');
$sth->execute();
my $rows = $dbh->selectall_arrayref($sth);
$worksheet1->select();
$worksheet1->write_col('B2',$rows);
$sth->finish();
$dbh->disconnect();

# Add the chart extracted using the chartex utility
my $chart = $worksheet1->embed_chart('B9','/home/masha/mychart01.bin');
# Link the chart to the worksheet data using a dummy formula.

$worksheet1->store_formula("=Hotels_Rating!A1");


#counting summary of the rating
$worksheet1->write('C6','=SUM(C2:C5)',$format);


#formating columns
$worksheet1->set_column(1,1,15);
$worksheet1->set_column(2,2,10);
$workbook->close();