how-to parse excel file in perl

START ===== D:\perl\sfw\csv\tmp\parseExcelToCsv.cmd

@echo off  

ECHO %0 > %0.log

ECHO %0.error.log >%0.error.log

set BaseDir=D:\perl\sfw\csv

echo BaseDir is %BaseDir%  1>>%0.log 2>>%0.error.log

ECHO.

set ExcelFileToParse="parse.xls"

echo ExcelFileToParse is %ExcelFileToParse%  1>>%0.log 2>>%0.error.log

echo.

set OutputDir=D:\perl\sfw\csv

echo OutputDir is %Outputdir%  1>>%0.log 2>>%0.error.log

echo.

::set PerlScript=parseExcelToCsv.pl

set PerlScript=parseExcelToCsv.2.0.pl

echo PerlScript is %PerlScript%  1>>%0.log 2>>%0.error.log

ECHO.

::This is the ASCII number of the token delimiter to use in the csv file

set TokenDelimiterAsciiNumber=44

echo TokenDelimiterAsciiNumber is %TokenDelimiterAsciiNumber%  1>>%0.log 2>>%0.error.log

::This is the ASCII number of the token delimiter to use in the csv file

set RowEndAsciiNumber=13

echo RowEndAsciiNumber is %RowEndAsciiNumber% %RowEndAsciiNumber% 1>>%0.log 2>>%0.error.log

echo Action !!!  1>>%0.log 2>>%0.error.log

echo perl %BaseDir%\%PerlScript% %ExcelFileToParse% %OutputDir% %TokenDelimiterAsciiNumber% %RowEndAsciiNumber% 1>>%0.log 2>>%0.error.log

perl %BaseDir%\%PerlScript% %ExcelFileToParse% %OutputDir% %TokenDelimiterAsciiNumber% %RowEndAsciiNumber% 1>>%0.log 2>>%0.error.log

%0.error.log

%0.log

::debug pause

END ================== D:\perl\sfw\csv\tmp\parseExcelToCsv.cmd

. 

START ===== D:\perl\sfw\csv\tmp\parseExcelToCsv.2.0.pl

use Spreadsheet::ParseExcel;

use strict;

package parseExcelToCsv ; 

sub main {

my $ExcelFileToParse = "$ARGV[0]" ; 

my $OutputDir="$ARGV[1]" ;

my $delimiter = chr($ARGV[2]);

my $rowEnd = chr($ARGV[3]);

print "\$delimiter is $delimiter \n" ; 

print "\$rowEnd is $rowEnd \n" ; 

$ExcelFileToParse =~m/^.*(\\|\/)(.*)/; # strip the remote path and keep the filename

my $FileToPrint = $ExcelFileToParse ; 

$FileToPrint =~ s/^(.*)(\.)(.*)/$1/ ;     #strip the file extension

$FileToPrint .= ".csv" ;         #add the acsv file t extension 

$FileToPrint = "$OutputDir/$FileToPrint";         #add the directory in front ... 

my $strToPrint = ParseExcel ( $ExcelFileToParse  , $delimiter , $rowEnd) ; 

PrintToFile( $FileToPrint , $strToPrint )  ;

print $strToPrint ; 

}

sub ParseExcel {

my $ExcelFileToParse = shift ; 

my $delimiter = shift ; 

my $rowEnd = shift ; 

my $strToReturn = "";

my $parser   = Spreadsheet::ParseExcel->new();

my $workbook = $parser->Parse("$ExcelFileToParse");

for my $worksheet ( $workbook->worksheets() ) {

my ( $row_min, $row_max ) = $worksheet->row_range();

my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min .. $row_max ) {

for my $col ( $col_min .. $col_max ) {

my $cell = $worksheet->get_cell( $row, $col );

next unless $cell;

print " Row, Col    = ($row, $col)\n";

print " Value = ", $cell->value() ;                #The Value

$strToReturn .=  $cell->value() . $delimiter       ;                #The Value

print "Unformatted = ", $cell->unformatted(), "\n";

}

chop ($strToReturn) ; #remove the latest delimiter

$strToReturn .= $rowEnd ;     #end the row    

}

}

return $strToReturn ; 

} #eof sub

sub PrintToFile {

my $FileOutput = shift ; 

my $StringToPrint = shift ; 

#READ ALL ROWS OF A FILE TO ALIST 

open (FILEOUTPUT, ">$FileOutput") || 

print "could not open the \$FileOutput $FileOutput!\n"; 

print  FILEOUTPUT $StringToPrint ; 

close FILEOUTPUT ;

#debug $strToReturn .=  $StringToPrint; 

}

# =========================================== eof sub PrintToFile

sub trim    

{

$_[0]=~s/^\s+//;

$_[0]=~s/\s+$//;

return $_[0];

}

# =========================================== eof sub trim 

# Action !!!

main();

1 ; 

__END__ 

END ================== D:\perl\sfw\csv\tmp\parseExcelToCsv.2.0.pl