File Coverage

blib/lib/Spreadsheet/ExcelHashTable.pm

Criterion	Covered	Total	%
statement	18	205	8.7
branch	0	56	0.0
condition	0	3	0.0
subroutine	6	20	30.0
pod	7	9	77.7
total	31	293	10.5

package Spreadsheet::ExcelHashTable;
use 5.006;
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Tie::IxHash;

=head1 NAME

	Spreadsheet::ExcelHashTable - Converts Excel Table to Perl Hash and vicerversa

=head1 VERSION

	Version 0.02

=cut

our $VERSION = '0.02';


sub new {
	my $class =  shift;
	my $self = {
	excel_hash => {},
	excel_file => '',
	excel_sheet => '',
	};
	bless ( $self, $class);
	tie %{$self->{excel_hash}}, "Tie::IxHash";
	$self->{excel_file} = shift if(@_);
	my $parser = Spreadsheet::ParseExcel->new();
	$self->{excel_obj} = $parser->parse($self->{excel_file});
	return $self;
}

sub set_read_xls {
	my $self = shift;
	$self->{excel_file} = shift ;
	my $parser = Spreadsheet::ParseExcel->new();
	$self->{excel_obj} = $parser->parse($self->{excel_file});
}

sub parse_table {
	my $self = shift;
	my $sheet = shift;
	my $var = shift;
	$self->{excel_sheet} = $sheet;
	$self->{sheet_obj} = $self->{excel_obj}->worksheet($sheet);
	$self->{table_name} = $var;
	if ($self->excel_checker()) {  $self->error(); die;   }
	my $cell_info = $self->_search_var($var);
	if ( ! exists $self->{excel_hash}->{$var} ) { $self->{excel_hash}->{$var} = {}; }
	my ($row_v, $col_v) = split " ", $cell_info;
	my $row_v_max = $self->_get_table_row_max($row_v, $col_v);
	my $col_v_max = $self->_get_table_col_max($row_v, $col_v);
	$self->_get_table_hash($row_v, $col_v, $row_v_max, $col_v_max);
}

sub _get_table_hash {
	my $self = shift;
	my ($row, $col, $row_max, $col_max) = @_;
	foreach my $r ($row+1 ... $row_max ) {
	my $row_key = $self->{sheet_obj}->get_cell($r,$col)->value ;
	if ($row_key =~ /\s+$/ ) { $row_key =~ s/\s+$//g ; }
	chop($row_key) if ($row_key =~ /\s+$/ );
	$self->{excel_hash}->{$self->{table_name}}->{$row_key} = {};
	}
	foreach my $c ($col+1 ... $col_max) {
		my $r = $row+1;
		my $row_key = $self->{sheet_obj}->get_cell($r,$col)->value;
		my $att = $self->{sheet_obj}->get_cell($row,$c)->value;
		if ( $att =~ /\s+$/ ) { $att =~ s/\s+$//g; }
		if ( $row_key =~ /\s+$/ ) { $row_key =~ s/\s+$//g; }
		$self->{excel_hash}->{$self->{table_name}}->{$row_key}->{$att} = "";
		$r = $r + 1;
	}
	foreach my $r ( $row+1 ... $row_max ) {
		foreach my $c ( $col+1 ... $col_max ) {
			my $val = "";
			my $cell = $self->{sheet_obj}->get_cell($r,$c);
			if ( defined $cell ) {
				$val = $cell->value();
				chop($val) if ($val =~ /\s+$/ );
			}
			my $row_key = $self->{sheet_obj}->get_cell($r,$col)->value;
			my $col_key = $self->{sheet_obj}->get_cell($row, $c)->value;
			if ($row_key =~ /\s+$/ ) { $row_key =~ s/\s+$//g; }
			if ($col_key =~ /\s+$/ ) { $col_key =~ s/\s+$//g; }
			$self->{excel_hash}->{$self->{table_name}}->{$row_key}->{$col_key} = $val;
		}
	}
}
sub _get_table_col_max {
	my $self = shift;
	my $row_v = shift;
	my $col_v = shift;
	my $col_v_max = $col_v;
	my ( $row_min, $row_max) = $self->{sheet_obj}->row_range;
	my ( $col_min, $col_max) = $self->{sheet_obj}->col_range;
	foreach my $col ( $col_v ... $col_max ) {
		$col_v_max = $col;
		my $cell = $self->{sheet_obj}->get_cell($row_v, $col);
		if ( ! defined $cell ) { $col_v_max = $col-1; last; }
		if ( $cell->value eq "" ) { $col_v_max = $col-1; last; }
	}
	if ( $col_v_max == $col_v ) {
	my $msg = "ERROR: It is not a Valid Table to create Hash. Double Check <$self->{table_name}>";
	$self->{error_hash}->{$msg} = "";
	return "";
	} else {
		return $col_v_max;
	}
}

sub error {
	my $self = shift;
	foreach my $msg ( keys  %{$self->{error_hash}} ) {
		print "\n\t$msg\n";
	}
}


sub _get_table_row_max {
	my $self = shift;
	my $row_v = shift;
	my $col_v = shift;
	my $row_v_max = $row_v;
	my ( $row_min, $row_max) = $self->{sheet_obj}->row_range;
	my ( $col_min, $col_max) = $self->{sheet_obj}->col_range;
	foreach my $row ( $row_v ... $row_max ) {
		$row_v_max = $row;
		my $cell = $self->{sheet_obj}->get_cell($row, $col_v);
		if ( ! defined $cell ) { $row_v_max = $row-1; last; }
		if ( $cell->value eq "" ) { $row_v_max = $row-1; last; }
	}
	if ( $row_v_max == $row_v ) {
	my $msg = "ERROR: It is not a Valid Table to create Hash. Double Check <$self->{table_name}>";
	$self->{error_hash}->{$msg} = "";
	return "";
	} else {
	return $row_v_max;
	}
}

sub _search_var {
	my $self = shift;
	my $var = shift;

	my ( $row_min, $row_max ) = $self->{sheet_obj}->row_range;
	my ( $col_min, $col_max) = $self->{sheet_obj}->col_range;
	foreach my $col ($col_min ... $col_max ) {
		foreach my $row ( $row_min ... $row_max ) {
			my $cell = $self->{sheet_obj}->get_cell($row, $col);
			if ( defined $cell ) {
				my $val =  $cell->value;
				if ($val =~ /\s+$/ ) { $val =~ s/\s+//g; }
				if ( $val eq "$var") { return "$row $col"; }
			}
		}
	}
	my $msg = "ERROR: No Cell found with <$var> value in <$self->{excel_file} : $self->{excel_sheet}, Make Sure it is Valid Excel Table\n";
	$self->{error_hash}->{$msg} = "";
	return "";
}

sub excel_checker {
	my $self = shift;
	my $sheet =  shift if @_;
	my $var =  shift if @_;
	if ( ! defined  $var ) { $var = $self->{table_name} ; }
	if ( ! defined  $sheet ) { $sheet = $self->{excel_sheet} ; }
	$self->{sheet_obj} = $self->{excel_obj}->worksheet($sheet);
	if (! defined $self->{sheet_obj} ) { $self->{error_hash}->{"Work Sheet $sheet Not found in $self->{excel_file}"} = "" ; return 1;}
	my $cell_info = $self->_search_var($var);
	if ( $cell_info eq "" ) { return 1 ; }
	my ($row_v, $col_v ) = split " " , $cell_info;
	my $row_v_max = $self->_get_table_row_max($row_v, $col_v);
	my $col_v_max = $self->_get_table_col_max($row_v, $col_v);
	if ( $col_v_max eq ""  || $row_v_max eq "" ) { return 1; }
	undef $var;
	undef $sheet;
	return 0;
}

sub _print_table {
	my $self = shift;
	foreach my $key ( keys %{$self->{excel_hash}} ) {
		print "Table: >$key< \n";
		foreach my $k ( keys %{$self->{excel_hash}->{$key}} ) {
			print ">$k<:\n";
			foreach my $j ( keys %{$self->{excel_hash}->{$key}->{$k}} ) {
				print ">$j<: >$self->{excel_hash}->{$key}->{$k}->{$j}<\n";
			}
		}
	}
}

sub set_write_xls {
	my $self = shift;
	my $xls = shift;
	$self->{work_book_obj} = Spreadsheet::WriteExcel->new($xls);
}

sub write_excel {
	my $self = shift;
	my $worksheet = shift;
	my $table = shift;
	my $work_sheet = $self->{work_book_obj}->add_worksheet($worksheet);
	my $row = 2;
	my $col = 2;
	my $format = $self->{work_book_obj}->add_format();
	$format->set_bold();
	$format->set_color("blue");
	$format->set_align("center");
	$work_sheet->write($row, $col, $table, $format );
	foreach my $row_key ( keys %{$self->{excel_hash}->{$table}} ) {
		$row = $row + 1;
		$work_sheet->write($row, $col, $row_key, $format );
	}
	$row = 2;
	foreach my $row_key ( keys %{$self->{excel_hash}->{$table}} ) {
		$col = 2;
		#print "$row_key\n";
		foreach my $col_key ( keys %{$self->{excel_hash}->{$table}->{$row_key}} ) {
			#print "$row $col $col_key\n";
			$col = $col + 1;
			if( $row == 2) { $work_sheet->write($row, $col, $col_key, $format ); }
			$work_sheet->write($row+1, $col, $self->{excel_hash}->{$table}->{$row_key}->{$col_key} );
		}
	$row = $row +1;
	}
}

sub get_table  {
	my $self = shift;
	my $table = shift;
	return $self->{excel_hash}->{$table};
}

sub get_xl_tables  {
	my $self = shift;
	return $self->{excel_hash};
}

1;

__END__