Spreadsheet::ExcelHashTable - Converts Excel Table to Perl Hash and vicerversa
Version 0.02
Spreadsheet::ExcelHashTable reads tables from Excel and converts them to Perl Data Structure and writes Perl Hash to a Excel Sheet
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.
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 => { .... } }
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" );
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" } } }
Returns 1 If the Excel Table has errors. Excel Table need to have certain format, so that any excel can be parsed.
Displays Errors meesage caugh using excel_checker() function. $excel_table->error();
Return only the particular <table> hash.
Return the complete hash, self->{excel_hash}
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 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");
Writes Hash table back to Excel
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.
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
Srinik, <srinik.perl@gmail.com>
<srinik.perl@gmail.com>
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.
bug-spreadsheet-excelhashtable at rt.cpan.org
You can find documentation for this module with the perldoc command.
perldoc Spreadsheet::ExcelHashTable
You can also look for information at:
RT: CPAN's request tracker (report bugs here)
http://rt.cpan.org/NoAuth/Bugs.html?Dist=Spreadsheet-ExcelHashTable
AnnoCPAN: Annotated CPAN documentation
http://annocpan.org/dist/Spreadsheet-ExcelHashTable
CPAN Ratings
http://cpanratings.perl.org/d/Spreadsheet-ExcelHashTable
Search CPAN
http://search.cpan.org/dist/Spreadsheet-ExcelHashTable/
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.
To install Spreadsheet::ExcelHashTable, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Spreadsheet::ExcelHashTable
CPAN shell
perl -MCPAN -e shell install Spreadsheet::ExcelHashTable
For more information on module installation, please visit the detailed CPAN module installation guide.