package Excel2003Handler ;
my $VERSION='0.0.3' ;
require Exporter;
use strict ; use warnings ; use Carp qw(cluck);
use lib '.' ; use Spreadsheet::ParseExcel;
BEGIN {
$0 =~ m/^(.*)(\\|\/)(.*)\.([a-z]*)/;
push ( @INC , $1) ;
}
use Logger ; use FileHandler ;
my @ISA = qw(AutoLoader Exporter);
my @EXPORT = qw(DumpExcel ParseExcel GetValue);
our ( $confHolder , $FileInputExcel , $objLogger , $objFileHandler ) = () ;
our ( $DelimiterCsv , $MsgAction ) = () ;
sub Initialize {my $self = shift ;
my $FlagSuccess = 0 ;
$objLogger = new Logger (\$confHolder) ;
$objFileHandler = new FileHandler ( \$confHolder ) ;
$FileInputExcel = $confHolder->{'FileExcelInput'} ;
unless( -f $FileInputExcel ) { $MsgAction = "Cannot find the Excel file $FileInputExcel , exiting !!! \n" ;
return 1 ;
}
$DelimiterCsv ="\t" ;
$FlagSuccess = 1 ;
return $FlagSuccess;
} #eof sub Initialize
# =============================================================================
# START OO
#source:http://www.netalive.org/tinkering/serious-perl/#oop_constructors
sub new {my $class = shift ; # Class name is in the first parameter
$confHolder = ${ shift @_ } if ( @_ ) ; my $self = { }; # Anonymous hash reference holds instance attributesbless($self, $class); # Say: $self is a $class
$self->Initialize() ;
return $self;
} #eof const
sub AUTOLOAD {
my $self = shift ;
no strict 'refs';
my $name = our $AUTOLOAD;
*$AUTOLOAD = sub { my $msg = "BOOM! BOOM! BOOM! \n RunTime Error !!!\nUndefined Function $name(@_)\n" ;
print "$self , $msg";
};
goto &$AUTOLOAD; # Restart the new routine.
}
sub get {my $self = shift;
my $name = shift;
return $self->{$name};} #eof sub get
sub set {my $self = shift;
my $name = shift;
my $value = shift;
$self->{$name}=$value;} #eof sub set
sub DESTROY {
my $self = shift;
#debug print "the DESTRUCTOR is called \n" ;
return ;
}
# STOP OO
# =============================================================================
sub ExcelToCsv {
my $self = shift ;
$FileInputExcel =~ m/^(.*)(\\|\/)(.*)\.([a-z]*)/;
my $DirOutput = $1 ;
#Delete first the old csv files
my ($RefFilesAndDirs , $RefDirsToProcess , $RefFilesToProcess ) =
$objFileHandler->ReadDirRecursivelyReturnArrays ( $DirOutput , '.csv') ;
my @FilesToProcess = @$RefFilesToProcess ;
foreach my $FileToProcess ( @FilesToProcess ) { unlink ( $FileToProcess ) ;
}
my ( $RefWorkSheets , $RefWorkSheetNames ) = $self->ParseExcel () ;
my @WorkSheetNames = @$RefWorkSheetNames ;
my @RefWorkSheets = @$RefWorkSheets ;
foreach my $WorkSheetRef ( @RefWorkSheets ) { my @Rows = @$WorkSheetRef ;
my $StrToPrint = () ;
my $WorkSheetName = shift ( @WorkSheetNames ) ;
my $FileCsvToPrint = "$DirOutput/$WorkSheetName" . '.csv' ;
foreach my $RowRef ( @Rows ) {my @Cols = @$RowRef ;
# from the first till the last number of the array
for ( 0 .. $#Cols ) {my $token = "$Cols[$_]" ;
$StrToPrint .= "$DelimiterCsv" . "$token" if $_ ;
$StrToPrint .= "$token" unless $_ ; #the elemen
} #eof for
$StrToPrint .= "\n" ;
} #eof foreach my $RowRef
$objFileHandler->PrintToFile($FileCsvToPrint , $StrToPrint )
if ( defined ( $StrToPrint));
} #eof foreach foreach my $WorkSheetRef
} #eof sub
# --- foreach worksheet create worksheet refs into array
# --- foreach row in worksheet create refs into array
# --- foreach column in row create refs into array
# --- add col refs , to row refs array , to worksheets refs array
# --- return the whole thingy
sub ParseExcel {
my $self = shift ;
#old my $strTemplate = shift ;
#old my $FileToPrintTemplated = shift ;
my $FileInputExcel = $confHolder->{'FileExcelInput'} ;
unless ( defined ( $FileInputExcel) or -f $FileInputExcel ) { $objLogger->LogInfoErrorMsg ( " Cannot parse Excel 2003 - No file to read !!! " ) ;
$objLogger->LogInfoErrorMsg ( " EXIT 1" ) ;
exit(1) ;
}
my $parser = Spreadsheet::ParseExcel->new();
my $Workbook = $parser->Parse("$FileInputExcel");
if ( !defined $Workbook ) {print STDERR "cannot parse \$FileInputExcel $FileInputExcel $! $parser->error()" ;
die $parser->error(), ".\n";
}
my @RefWorkSheets = () ;
my @WorkSheetNames = () ;
foreach my $worksheet (@{$Workbook->{Worksheet}}) {my $WorkSheetRef = () ;
my $WorkSheetName = $worksheet->{'Name'} ; push ( @WorkSheetNames , $WorkSheetName ) ;
my ( $ColMin , $ColMax , $RowMin , $RowMax ) ;
$ColMin = $worksheet->{MinCol} ; $ColMax = $worksheet->{MaxCol} ;
$RowMin = $worksheet->{MinRow} ; $RowMax= $worksheet->{MaxRow} ; #the first row are the column headers
my $MinRowWithData= $RowMin +1 ;
$objLogger->LogDebugMsg ( "\$ColMin is $ColMin") ;
$objLogger->LogDebugMsg ( "\$ColMax is $ColMax") ;
$objLogger->LogDebugMsg ( "\$RowMin is $RowMin") ;
$objLogger->LogDebugMsg ( "\$RowMax is $RowMax") ;
my @RowRefs = () ;
for my $row ( $MinRowWithData .. $RowMax) {my $RowRef = () ;
my @Row = () ;
for my $col ( $ColMin .. $ColMax ) {# get the cell obj not it's value
my $cell = $worksheet->{Cells}[$row][$col] ; #deprec next unless $cell;
$objLogger->LogDebugMsg ( " Row, Col = ($row, $col)");
# if the cell as a value it is defined
if ( $cell ) {$cell->Value() ;
push ( @Row , $cell->Value () ) ;
#debug $objLogger->LogDebugMsg ( " Value = $cell->Value() "); #The Value
}
else {push ( @Row , undef ) ;
}
} #eof for col
push ( @RowRefs , \@Row ) ;
} #eof row
push ( @RefWorkSheets , \@RowRefs ) ;
} #eof worksheet
return ( \@RefWorkSheets , \@WorkSheetNames ) ;
} #eof sub
sub GetValue {
my $self = shift ;
my $RefWorkSheets = shift ;
my @RefWorkSheets = @$RefWorkSheets ;
my $WorkSheetNumber = shift ;
my $RowNumber = shift ;
my $ColNumber = shift ;
my $RefRows = $RefWorkSheets [$WorkSheetNumber ] ;
return undef unless ( $RefRows ) ;
my @Rows = @$RefRows ;
my $RefCols = $Rows[ $RowNumber ] ;
return undef unless ( $RefCols ) ;
my @Cols = @$RefCols ;
my $ValueToReturn = $Cols [$ColNumber ] ;
} #eof sub GetValue
sub DumpExcel {
my $self = shift ;
my $RefWorkSheets = $self->ParseExcel () ;
my @RefWorkSheets = @$RefWorkSheets ;
foreach my $WorkSheetRef ( @RefWorkSheets ) { my @Rows = @$WorkSheetRef ;
foreach my $RowRef ( @Rows ) {my @Cols = @$RowRef ;
foreach my $Col ( @Cols) {$objLogger->LogDebugMsg ( "\$Col is $Col " );
}
} #eof foreach my $RowRef
} #eof foreach foreach my $WorkSheetRef
} #eof sub DumpExcel
1;
__END__
=head1 NAME
Excel2003Handler
=head1 SYNOPSIS
use Excel2003Handler ;
=head1 DESCRIPTION
Provide a simple interface for handling Excel 2003 and print to csv files, a wrapper build around the Spreadsheet::ParseExcel module. This module has external dependancies on the modules of the morphus tool - google code morhpus
=head2 EXPORT
=head1 SEE ALSO
perldoc perlvars
. No mailing list for this module
=head1 AUTHOR
FirstName.LastName@gmail.com
=head1 COPYRIGHT AND LICENSE
Copyright (C) 2011 Yordan Georgiev
This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.8.1 or,
at your option, any later version of Perl 5 you may have available.
VersionHistory:
0.0.3 --- ysg --- Fixing bug with first char being a \t
0.0.2 --- ysg --- Refactoring. Tested. Works. Added documentation
0.0.1 --- ysg --- Initial version - OO methods + Initialize , ParseExcel , GetValue , DumpExcel
=cut
Posts
