#!/opt/perl5.8/bin/perl
#package Spreadsheet::ParseExcel::Simple;

use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;
#use Spreadsheet::ParseExcel::Simple;

#use strict;
#    use Spreadsheet::ParseExcel;
    my $oExcel = new Spreadsheet::ParseExcel;

    #1.1 Normal Excel97
    my $oBook = $oExcel->Parse('test1.xls');
    my($iR, $iC, $oWkS, $oWkC);
    print "FILE  :", $oBook->{File} , "\n";
    print "COUNT :", $oBook->{SheetCount} , "\n";
    print "AUTHOR:", $oBook->{Author} , "\n";
    for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
        $oWkS = $oBook->{Worksheet}[$iSheet];
        print "--------- SHEET:", $oWkS->{Name}, "\n";
        for(my $iR = $oWkS->{MinRow} ;
                defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $i
+R++) {
            for(my $iC = $oWkS->{MinCol} ;
                            defined $oWkS->{MaxCol} && $iC <= $oWkS->{
+MaxCol} ; $iC++) {
                $oWkC = $oWkS->{Cells}[$iR][$iC];
              #  $oWkC = $oWkS->{Cells}[$iR][0];
                print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC)
+;  # Formatted Value
#
                #print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC
+);  # Formatted Value
#                print "( $iR , $iC ) =>", $oWkC->{Val}, "\n" if($oWkC
+);  # Original Value
            }
        }
    }
[download]

my excel input will be in format

component	mode		command
comp1		mode1		command1
comp2		mode2		command1
		mode2		command2
comp3		mode1		command1
		mode1		command2
				

i am getting output as

--------- SHEET:Sheet1
( 0 , 0 ) =>component
( 0 , 2 ) =>mode
( 0 , 4 ) =>command
( 2 , 0 ) =>
( 3 , 0 ) =>comp1
( 3 , 2 ) =>mode1
( 3 , 4 ) =>command1
( 6 , 0 ) =>comp2
( 6 , 2 ) =>mode2
( 6 , 4 ) =>command1
( 7 , 2 ) =>mode2
( 7 , 4 ) =>command2
( 8 , 4 ) =>
( 9 , 0 ) =>comp3
( 9 , 2 ) =>mode1
( 9 , 4 ) =>command1
( 10 , 2 ) =>mode1
( 10 , 4 ) =>command2

but i need to maintain it as hash value ie hash of array of hashes ie

%hash=(

comp1  => [
            { command => "command1",  mode => "mode1"  },
            { command => "command2",    mode => "mode1"},
          ],  
        
comp2  => [
            { command => "command1",  mode => "mode1"  },
            { command => "command2",    mode => "mode1"},
          ],  
)
[download]

or

%hash=>{

 comp1=>{

      mode1=>{

              (command => command1)
               (command=>command2)
              }

       mode2=>{

               (command => command1)
               (command=>command2)
              }
          },

  comp2=>{
        mode1=>{

              (command => command1)
               (command=>command2)
              }

       mode2=>{

               (command => command1)
               (command=>command2)
              }
          },
}
[download]

and after building this in to hash ,i will built an text file using key value as comp1_text,comp2_text etc as the filename . My comp1_text will be input to another perl script which will populate my database automatically.

ie secondscript.pl comp1_text.

my comp1_text will be in format as

command1:comp1:mode1:
command2:comp1:mode1:

I need help in how to modify my script such that i can bulid my hash in specified format below and write in an text file by creating it in the specified format.
can any one help me in this regard. Thanks, srins.

2006-05-25 Retitled by GrandFather, as per Monastery guidelines
Original title: 'How to build the hash for my above format which i parse from excel file and write that in text file using that hash, delimited by colon values'

READMORE tags added by Arunbear