(Perl Programming) How to read data from excel spreadsheet using Spreadsheet::ParseExcel module?

The Spreadsheet::ParseExcel Perl module can be used to read information from Excel 95-2003 binary files.

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

In previous program we have created Sales.xls file.  Below mentioned program is fetching (reading) the data from Sales.xls file and then storing it into sales table.

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

#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";

#The new() method is used to create a new Spreadsheet::ParseExcel parser object.
my $parser   = Spreadsheet::ParseExcel->new();
#The Parser parse() method returns a "Workbook" object.
my $workbook = $parser->Parse("Sales.xls");

#The Parser error() method returns an error string if a parse() fails
#    error()                              error_code()
#   =======                         ============
#   ''                                          0
#  'File not found'                       1
#   'No Excel data found in file'    2
#   'File is encrypted'                  3

if ( !defined $workbook ) {
        die $parser->error(), ".\n";

}

#The worksheet() method returns a single Worksheet object using either its name or index
my $worksheet = $workbook->worksheet(0); # or sheet1

#Returns a two-element list ($min, $max) containing the minimum and maximum defined rows in the worksheet.
my ( $row_min, $row_max ) = $worksheet->row_range();

#Returns a two-element list ($min, $max) containing the minimum and maximum of defined columns in the #worksheet.
my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min .. $row_max ) {
   
     my $i=0;
     my @Temp_Array=();
          
     for my $col ( $col_min .. $col_max ) {
  
    #Return the "Cell" object at row $row and column $col if it is defined.
    #Otherwise returns undef.
   
     my $cell = $worksheet->get_cell( $row, $col );
      
        #if not defined next unless $cell (defined cell);  
        if (! defined ($cell) ) {   
            $Temp_Array[$i++]=" ";
            next;
        }
  
        #The value() method returns the formatted value of the cell.
         $Temp_Array[$i++]= $cell->value();
     
     } # column for loop ends here
       
     #avoid first row of excel sheet (avoid Title row)
     if ($Temp_Array[0] ne "Year" ) {
    
     #Insert fetched data from cell into Sales table
     my $Query="insert into sales (sale_year, sale_amount ) values  ($Temp_Array[0], $Temp_Array[1])";
     $DBH->do($Query);
          
     }
     
 } # row for loop ends here
 
  $DBH->disconnect; #disconnect Database connection

#===========Output Sales Table===============#

(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===================

   

      

(PHP Programming) How to generate Pie Chart using eZ components graph library?

The eZ components graph library (ezGraph component) provides object oriented APIs to render different types of charts (Bar chart, line chart, Pie Chart etc)

You can download the ez components package from http://ezcomponents.org/download site.

Download and extract the .zip file or .tar.bz file into the PEAR folder. (Or folder path present in PHP include_path).

Now create table course_enrollee with two fields (course_name and enrollee) (I am using MYSQL Database)

CREATE TABLE course_enrollee (course_name VARCHAR( 60 ) NOT NULL ,
enrollee INT NOT NULL );

Insert values into course_enrollee table

INSERT INTO course_enrollee (course_name, enrollee) VALUES ('Data Structures', 30, 'Digital Design', 20, 'Computer Organization', 35, 'System Design', 40);

course_name                      enrollee
Data Structures                       30

Digital Design                          20
Computer Organization            35
System Design                         40

Now to generate Pie chart using ezGraph component follow below mentioned code

 //Info:The PHP classes of the eZ Components can be conveniently used from within your PHP script. You don't have to use any require or include statements for any of the eZ Components classes that you use, this is because of the integrated autoload mechanism which can locate the classes for you when you instantiate or use them otherwise.

 // set up autoloader (specify ezc directory path)
 require_once 'ezc/Base/src/ezc_bootstrap.php';

// Connect to Mysql database
      $dbh = mysql_connect("localhost", "root", "xyz");

      if (!$dbh) {
          die('Could not connect: ' . mysql_error());
      }
     
      mysql_select_db("test");
//=========================================================//   

// create Query statement
$Query="select course_name, enrollee from course_enrollee";
     
 if (! $Result = mysql_query($Query)) { // execute query using mysql_query statment
    die("Can't execute query: ".mysql_error());
 }

   $Values=array(); //initialize empty array we are going to store our result in key value pair
  
   //use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.
  
   //MYSQL_ASSOC is used as the second argument to mysql_fetch_array(), so that it returns the row as an associative array.
  
   while ($row = mysql_fetch_array($Result, MYSQL_ASSOC)) {
  
       $key=$row["course_name"];
       $value=$row["enrollee"];
      
       $Values[$key]=$value; // array $Values is going to act as a dataset for pie chart
     }
//============================================================//   

// initialize object of class ezcGraphPieChart
//Class for pie charts. Can only use one dataset which will be dispalyed as a pie chart
$Graph = new ezcGraphPieChart();
//Dark color pallet for ezcGraph
$Graph->palette = new ezcGraphPaletteBlack();

//Class to transform chart primitives into image primitives. Renders charts in a two dimensional view
$Graph->renderer = new ezcGraphRenderer2d();

$Graph->renderer->options->moveOut = .2; //Percent to move pie chart elements out of the middle on highlight.
$Graph->renderer->options->pieChartOffset = 63; //Offset for starting with first pie chart segment in degrees.
$Graph->renderer->options->pieChartGleam = .3; //Enhance pie chart with gleam on top.
$Graph->renderer->options->pieChartGleamColor = '#FFFFFF'; //Color used for gleam on pie charts.
$Graph->renderer->options->pieChartGleamBorder = 2; // Do not draw gleam on an outer border of this size.
$Graph->renderer->options->pieChartShadowSize = 3; //Size of shadows.
$Graph->renderer->options->pieChartShadowColor = '#000000'; //Color used for pie chart shadows.
$Graph->renderer->options->legendSymbolGleam = .5; //Opacity of gleam in legend symbols
$Graph->renderer->options->legendSymbolGleamSize = .9; //Size of gleam in legend symbols
$Graph->renderer->options->legendSymbolGleamColor = '#FFFFFF'; //Color of gleam in legend symbols
$Graph->renderer->options->pieChartSymbolColor = '#BABDB688'; // Color of pie chart symbols

// add data points
 $Graph->title = " Course Enrollee Statistics"; //graph title

 // Pass $Values array (dataset) to ezcGraphArrayDataSet This data set is specified as an associative array, with keys representing X-axis labels and values representing Y-axis points.
 $Graph->data['Course Enrollee Statistics'] = new ezcGraphArrayDataSet( $Values);


// render graph
//renderToOutput() method takes care of generating the necessary SVG declarations to render a pie chart with the specified height and width. The renderToOutput() method will also take care of sending the correct Content-Type header to the requesting client.

$Graph->renderToOutput(600,600);

 ?>

//===================Output===================//

(Perl Programming) How to Generate Line Graph/Chart using DBD::Chart Module?

The DBD::Chart Module provides native SQL interface to render line graph. The default output format is PNG, JPEG or GIF Format. In this article we are going to see how we can use DBD::Chart module to produce Line Graph.

You can download DBD::Chart module from CPAN repository (http://search.cpan.org/dist/DBD-Chart/Chart.pm) or if you are using Active Perl just run “ppm install DBD-Chart” command.

Create Sales Table with two fields “sale_amount and sale_year”. (I am using MYSQL database)

CREATE TABLE Sales (sale_year INT NOT NULL , sale_amount FLOAT NOT NULL );

Insert Values into the table for example

INSERT INTO sales (sale_year, sale_amount) VALUES ('2003', '4000'), ('2004', '5000');

sale_year sale_amount

1996 5000

1997 7000

1998 6000

1999 8000

2000 5000

2001 4000

2002 3000

2003 4000

2004 5000

After creating a sales table, follow below mentioned code to generate Line Graph.

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

#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 $i=0; # initialize Counter

my $buf;

my $Sth_Line;

# create connection with Char database (Chart is data source name)

my $dbh = DBI->connect('dbi:Chart:');

# Once connection is established, we want to create line graph so create line table with two fields year and Amount

$dbh->do('CREATE CHART line (Year INT, Amount INT )');

# create sql statment to fetch year and amount fields values from sales table

my $Query_Sales="SELECT sale_year, sale_amount from sales;";

# once connection is established prepare the Query_Sales SQL string to query the database

my $Sth = $DBH->prepare($Query_Sales);

$Sth->execute or die "SQL Error: $DBI::errstr\n"; # execute the prepared query or display error message

# fetch each row of the results from MYSQL database

while (my @row = $Sth->fetchrow_array) {

         my $Sale_Year=$row[0];

         my $Sale_Amount=$row[1];

        # we need to insert fetched year and sales values into line table

        $Sth_Line = $dbh->prepare('INSERT INTO line VALUES( ?, ?)');

        $Sth_Line->execute($Sale_Year, $Sale_Amount);

} # While loop end's here

$Sth->finish(); # free up resources

$Sth_Line->finish(); # free up resources

# after adding all values into the line table prepare SQL statment with line chart related fields to generate customized line graph/char

my $rsth = $dbh->prepare(

"SELECT LINEGRAPH FROM line WHERE WIDTH=? AND HEIGHT=? AND X_AXIS=? AND Y_AXIS=? AND TITLE = 'Sales Amount Statistics By Year' AND COLOR IN ('red') AND SHOWPOINTS=1 AND SHOWGRID=0 AND SHOWVALUES=1 AND SHAPE IN ('fillcircle') AND BACKGROUND='lgray' AND X_ORIENT='VERTICAL' AND SIGNATURE='Copyright(C) 2010, LampSys'");

# chart Width, Height

# chat x axis field name (X_AXIS) and y axis field name (Y_AXIS) (we have two fields year and amount)

# TITLE (Title of Chart)

# COLOR (represent Color of line chart), SHOWPOINTS (if 1 display points on Line chart)

#SHOWGRID=1 (if 1 generate line graph with grid background) SHOWVALUES=1 (if 1 display the value of points)

#SHAPE IN ('fillcircle') (display points as colored circles)

# BACKGROUND='lgray' (represent background color of line graph)

#X_ORIENT='VERTICAL' (display X axis values vertically)

#SIGNATURE='Copyright(C) 2010, LampSys'");

# specify width, hight , x axis and y axis fields

$rsth->execute(1000, 600, 'year', 'month');

#Binds a variable ($buf) with attributes (output columns year, amount) of a SELECT statement.

$rsth->bind_col(1, \$buf);

#Whenever a row is fetched from the database $buf

#appears to be automatically updated simply because it now refers to the same memory location as the corresponding column value.

$rsth->fetch;

$rsth->finish(); #free up resources

#open a file (as PNG, JPEG, GIF) in binary mode

open(OUTF, '>Sales.png');

binmode OUTF;

print OUTF $buf;

close(OUTF);

print "Sales.png Generated\n";
$DBH->disconnect;  #disconnect Database connection

#-------------------OutPut--------------------------------#