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.