START ===== D:\perl\sfw\ExcelToHtml\ExcelToHtml.pl
use Spreadsheet::ParseExcel;
use strict;
package ExcelToHtml ;
sub main {
my $ExcelFileToParse = "$ARGV[0]" ;
my $OutputDir="$ARGV[1]" ;
my $BaseFileName = $ExcelFileToParse ;
print "\$BaseFileName is $BaseFileName \n" ;
$BaseFileName =~ s/^(.*)(\\|\/)(.*)/$3/; #strip the directory part
print "\$BaseFileName is $BaseFileName \n" ;
$BaseFileName =~ s/^(.*)(\.)(.*)/$1/ ; #strip the file extension
print "\$BaseFileName is $BaseFileName \n" ;
ParseExcelAndPrintFiles ( $ExcelFileToParse , $BaseFileName , $OutputDir) ;
}
sub ParseExcelAndPrintFiles {my $ExcelFileToParse = shift ;
my $BaseFileName = shift ;
my $OutputDir = 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 .= makeCell($cell->value() ) ; #The Value
print "Unformatted = ", $cell->unformatted(), "\n";
}
$strToReturn = makeRow( $strToReturn );
} #eof for my row
my $worksheetName = $worksheet->{'Name'} ; my $FileToPrint = "$OutputDir/$BaseFileName" . '.' . $worksheetName . '.' . 'html' ;
print "\$FileToPrint is $FileToPrint \n" ;
$strToReturn = makeTable ( $strToReturn );
$strToReturn = makeFile ( $strToReturn );
print "\$strToReturn is $strToReturn \n" ;
PrintToFile ( $FileToPrint , $strToReturn ) ;
$strToReturn = "" ;
$FileToPrint = "" ;
} #eof for my worksheet
} #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 !!!
sub makeRow
{my $row = shift ;
return "<tr> $row </tr> \n" ;
}
sub makeCell
{my $cell = shift ;
return "<td> $cell </td> " ;
}
sub makeTable
{my $table = shift ;
return "<table> $table </table> " ;
}
sub makeFile
{my $file = shift ;
$file = "<html> <head></head><body> $file </body></html>\n" ;
return $file ;
}
main();
1 ;
__END__
END ================== D:\perl\sfw\ExcelToHtml\ExcelToHtml.pl
.
START ===== D:\perl\sfw\ExcelToHtml\ExcelToHtml.cmd
@echo off
ECHO %0 > %0.log
ECHO %0.error.log >%0.error.log
set BaseDir=D:\perl\sfw\ExcelToHtml
echo BaseDir is %BaseDir% 1>>%0.log 2>>%0.error.log
ECHO.
set ExcelFileToParse="%BaseDir%\TODO.xls"
echo ExcelFileToParse is %ExcelFileToParse% 1>>%0.log 2>>%0.error.log
echo.
set OutputDir=%BaseDir%
echo OutputDir is %Outputdir% 1>>%0.log 2>>%0.error.log
echo.
::set PerlScript=parseExcelToCsv.pl
set PerlScript=ExcelToHtml.pl
echo PerlScript is %PerlScript% 1>>%0.log 2>>%0.error.log
ECHO.
echo Action !!! 1>>%0.log 2>>%0.error.log
echo perl %BaseDir%\%PerlScript% %ExcelFileToParse% %OutputDir% 1>>%0.log 2>>%0.error.log
perl %BaseDir%\%PerlScript% %ExcelFileToParse% %OutputDir% 1>>%0.log 2>>%0.error.log
%0.error.log
%0.log
::debug pause
END ================== D:\perl\sfw\ExcelToHtml\ExcelToHtml.cmd
Posts
