/ 
Spreadsheet-ExcelHashTable-0.02
River stage zero No dependents
/ Spreadsheet::ExcelHashTable

NAME

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

VERSION

Version 0.02

SYNOPSIS

Spreadsheet::ExcelHashTable reads tables from Excel and converts them to Perl Data Structure and writes Perl Hash to a Excel Sheet

Motivation

This Utility is more useful for converting randomly organized  Excel Tables to Perl hash. In my case it more useful in converting 
this Excel Information to a EDA(Electronic Design Automation) tool scripts, using Template Tool Kit.

ExcelHashTable

Excel Table

In this context Excel Table is the following. In the below example "Employee is a table".

<Employee.xls>  (ExcelHashTable cannot understand Merged Cells)

----------------------------------------------------------------
| Employee | ID       |  Designation     	|   Department
----------------------------------------------------------------
|  Mike    |  1001    |  Software Analyst 	|   BU1
----------------------------------------------------------------
|  Srinik  |  1002    |  Analyst  		|   BU2
----------------------------------------------------------------

As shown above in the above Excel sheet, "Employee" is a table. So the complete table could be parsed in the following way

my $excel_table  = Spreadsheet::ExcelHashTable->new("Employee.xls");
$excel_table->parse_table("sheet1", "Employee") ;
my $excel_hash = $excel_table->get_table("Employee");

Structure of Hash Returned by get_table(<table_name>)
	$excel_hash  = { "Employee" => {
	Mike  => {
		ID    => "1001",
		Designation  => "Software Analyst"
		Department      => "BU1"
		},
	},
	Srinik   => { ....
	}
}

Merging Excel Tables

This Module is also helpful in merging various Excel Tables in different work books and also across various Excel sheets. Below is a example which finds two Employee table from different excel sheets and create a Perl data structure/Hash.

my $excel_table  = Spreadsheet::ExcelHashTable->new("Employee.xls");
$excel_table->parse_table("sheet1", "Employee") ;
$excel_table->parse_table("sheet2", "Employee") ;
$excel_table->parse_table("sheet3", "Employee") ;
my $excel_hash = $excel_table->get_table("Employee");

Below two lines dumps the Employee hash to a Excel Sheet

$excel_table->set_write_xls("Employee_new.xls");
$excel_table->write_excel( "sheet1", "Employee" );

Different Excel Tables

This module can parse different Excel Tables from various Excel WorkBook/Sheets. Below is the example shown

<Company.xls>
	<BU Table> - <sheet1>

	----------------------------------------
	| BU  | Employee_Name   | Employee_Id
	----------------------------------------
	| BU1 | Mike  		| 1001
	----------------------------------------
	| BU2 | Srinik  	| 1002
	----------------------------------------

<Employee Table> - sheet2

	---------------------------------------------------------------
	| Employee | ID       |  Designation     	|   Department
	---------------------------------------------------------------
	|  Mike    |  1001    |  Software Analyst 	|   BU1
	---------------------------------------------------------------
	|  Srinik  |  1002    |  Analyst  		|   BU2
	---------------------------------------------------------------


my $excel_table  = Spreadsheet::ExcelHashTable->new("Company.xls");
$excel_table->parse_table("sheet1", "BU") ;
$excel_table->parse_table("sheet2", "Employee") ;
my $excel_hash = $excel_table->get_xl_tables(); # Returns complete set of tables

Hash Structure

$excel_hash  = { "Employee" => {
			Mike  => {
				ID    => "1001",
				Designation  => "Software Analyst"
				Department      => "BU1"
				},
			},
			Srinik   => { ....
			},
		{ "BU" => {
			BU1 => {
				Employee_Name => "Mike"
				Employee_Id => "1001"
				}
			}
	}

excel_checker(<sheet>, <table>)

Returns 1 If the Excel Table has errors. Excel Table need to have certain format, so that any excel can be parsed.

error()

Displays Errors meesage caugh using excel_checker() function. $excel_table->error();

get_table(<table>)

Return only the particular <table> hash.

get_xl_tables()

Return the complete hash, self->{excel_hash}

set_read_xls(<xl_file>)

Set the Excel File through this function.

Example: You can set excel while declaring the object like the one shown below

my $excel_table  = Spreadsheet::ExcelHashTable->new("Company.xls");

or you if you want parse a different table from another xl sheet, change the XL using

excel_table->set_read_xls("Project.xls");

set_write_xls(<xl_file>)

set the excel file for writing. You cannot use the same file which you are reading.

Example: excel_table->set_write_xls("Project.xls"); excel_table->write_excel("sheet1", "Project"); excel_table->write_excel("sheet2", "Employee");

write_excel(<sheet>, <table>)

Writes Hash table back to Excel

excel_hash

Access excel_hash from $excel_table object, you can modify/manupulate the excel_hash parsed from the excel sheet

my $excel_table  = Spreadsheet::ExcelHashTable->new("Company.xls");
$excel_table->parse_table("sheet1", "BU") ;
$excel_table->parse_table("sheet2", "Employee") ;
$excel_table->{excel_hash} = .............

You then use write_excel to write it to Excel or get_table to return the hash.

Limitations/Rules

Follow rules in writing Excel sheet, so ExcelHashTable can parse the table

- Currently there cannot be any Cells Left emptly for a table, if any empty cell found the parsing stops. In the below example
"Paul" is not parsed.

	----------------------------------------------------------------------
	| Employee | ID       |  Designation     	|   Department
	----------------------------------------------------------------------
	|  Mike    |  1001    |  Software Analyst 	|   BU1
	----------------------------------------------------------------------
	|  Srinik  |  1002    |  Analyst		|   BU2
	----------------------------------------------------------------------
	|    	   |          |          		|
	----------------------------------------------------------------------
	| Paul    |  1003    |   Engineer               |   BU2
	----------------------------------------------------------------------

- Dont Merge any cells. Currenty L</excel_checker> cannot check this now. Will put this as a Part of next release

- xlsx format is not yet supported or havent been tested

AUTHOR

Srinik, <srinik.perl@gmail.com>

BUGS

Please report any bugs or feature requests to bug-spreadsheet-excelhashtable at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Spreadsheet-ExcelHashTable. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc Spreadsheet::ExcelHashTable

You can also look for information at:

LICENSE AND COPYRIGHT

Copyright 2011 Srinik.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.