Create a Perl script to store the information in Excel

Hello,
--------------------------------------
id name address
1 cristine New York
2 murray Redmond
3 kumar Sunnyvale
--------------------------------------

This is 'samples.xlsx' file.
'id' string in A1, 'name' in B1, 'address' in C1 column in excel file.

Create a Perl script to store the information about employee using the header as in raw 1. The Perl script should be able to provide data of any employee using the id or name or address. For example: If I need details that who is the person lives in Sunnyvale, the Perl script should give me back the result as Kumar.

*What data structure you have used and why?
*Try out usage of GetOptions in Perl script...?
*How can I manage duplicate data if any?

Please help me and answer me..

Cristine Thine
Profile Answers by Cristine Thine Questions by Cristine Thine
Apr 14th, 2011
3
7851

Questions by Cristine Thine

Perl

Answer

Showing Answers 1 - 3 of 3 Answers

elavv
Profile Answers by elavv

Aug 10th, 2011

You can construct one hash where the the emp id as key to the hash and rest of the elements as values in the structure of array ref.

Code
#!<SHEBANG LINE HERE>


use strict;


use Spreadsheet::ParseExcel::Simple;


 


my %output_Hash;


 


 


my $xls = Spreadsheet::ParseExcel::Simple->read('INPUT_FILE.xls');


 


foreach my $sheet ($xls->sheets) {


#my $sheet = ($xls->sheets)[2]; # Sheet 1


#my $data = ($sheet->next_row)[-1]; # last col of row 1


my $sheet_name = $sheet->{sheet}->{Name};


 


open (OUT ,">",$sheet_name.'.xml') or die $!;


 


my($emp_id,$emp_name,$emp_adress);


 while ($sheet->has_data) {   


         my @data = $sheet->next_row; 


         $emp_id = $data[0]; 


     $emp_name = $data[1] ;


         $emp_adress = $data[2];


         


push( @{$output_Hash{$emp_id}}, [$emp_name,$emp_adress]); 


 


foreach my $emp_id (sort keys %output_Hash) 


  {


  foreach my $array_ref(@{$output_Hash{$emp_id}})


{


my($emp_name,$emp_adress)=@{$array_ref};


}


 }