(Perl Programming) How to write data into excel spreadsheet using Spreadsheet::WriteExcel module?

The Spreadsheet-WriteExcel Perl module can be used to create a cross-platform Excel binary file. Text, numbers, formulas, hyperlinks, images and charts can be written to the cells.

You can download Spreadsheet::WriteExcel module from CPAN repository (http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.37/lib/Spreadsheet/WriteExcel.pm) or if you are using Active Perl just run “ppm install Spreadsheet-WriteExcel” command.

In previous program we have created Sales table.  Below mentioned program, we are going to write Sales table data into Sales.xls file.

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

 #create connection with data base, Connect function requires database name(test), user name(root) and password(xyz).
# if connection is not established die option display an error message (standard Error Message) and your script will die

my $DBH = DBI->connect('DBI:mysql:test', 'root', 'xyz') or die "Connection Error: $DBI::errstr\n";

my $Query="select sale_year, sale_amount from sales"; #Query statment

#Prepares a statement for later execution by the database engine and returns a reference to a statement handle object.
my $Sth = $DBH->prepare("$Query");
$Sth->execute;

my @Sale_Year=();
my @Sale_Amount=();
my @Information=();

#$sth->fetchrow_array returns an array of the results.
  while(my @Row = $Sth->fetchrow_array())
{
 push @Sale_Year , $Row[0]; # store Year data
 push @Sale_Amount, $Row[1]; # store Amount data
}

 push @Information, [ @Sale_Year ];
 push @Information, [ @Sale_Amount ];

#Information array looks like
#@Information =  (
#                [1996, 1997,....],
#               [5000,7000,....  ],
#          );
#################################
           # Create a new Excel workbook

            my $Workbook = Spreadsheet::WriteExcel->new('Sales.xls');
 
            # Add a worksheet
           my $Worksheet = $Workbook->add_worksheet();

           #  Add and define a format
          my $Format = $Workbook->add_format(); # Add a format
          $Format->set_bold();
          $Format->set_align('center');
 
          my @Title      = ('Year', 'Amount');
   
      #The write_row() method can be used to write a 1D or 2D array of data in one go.
      #This is useful for converting the results of a database query into an Excel worksheet.
      #You must pass a reference to the array of data rather than the array itself.
      #The write() method is then called for each element of the data.
 
         $Worksheet->write_row('A1', \@Title, $Format);
         $Worksheet->write_row('A2', \@Information, $Format);
 
         $Workbook->close();
        

$DBH->disconnect; #disconnect Database connection

   
=====================Output===================