START ===== TemplateGenerator.0.1.0.cmd
@echo off
ECHO %0 > %0.log
ECHO %0.error.log >%0.error.log
set BaseDir=%cd%
echo BaseDir is %BaseDir% 1>>%0.log 2>>%0.error.log
ECHO.
set ExcelFileToParse="%BaseDir%\input\configs.xls"
echo ExcelFileToParse is %ExcelFileToParse% 1>>%0.log 2>>%0.error.log
echo.
set InputDir=%BaseDir%\input
echo InputDir is %InputDir% 1>>%0.log 2>>%0.error.log
echo.
::CONFIGURE THE CURRENT DIRECTORY AS THE OUTPUT DIR
set OutputDir=%BaseDir%\output
echo OutputDir is %Outputdir% 1>>%0.log 2>>%0.error.log
echo.
::set PerlScript=parseExcelToCsv.pl
set PerlScript="%BaseDir%\TemplateGenerator.0.1.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 %PerlScript% %ExcelFileToParse% %OutputDir% %TokenDelimiterAsciiNumber% %RowEndAsciiNumber% %InputDir% 1>>%0.log 2>>%0.error.log
perl %PerlScript% %ExcelFileToParse% %OutputDir% %TokenDelimiterAsciiNumber% %RowEndAsciiNumber% %InputDir% 1>>%0.log 2>>%0.error.log
::UNCOMMENT TO OPEN THE ERROR LOG AND THE RUN LOG FILES
::%0.error.log
::%0.log
::debug pause
::SLEEP FOR 3 SECONDS
for /l %%i in (1,-1,0) do CLS&ECHO DONE !!! %%i&ping localhost -n 2 >NUL&CLS
END ================== TemplateGenerator.0.1.0.cmd
.
START ===== TemplateGenerator.0.1.0.pl
use Spreadsheet::ParseExcel;
use strict;
package TemplateGenerator ;
sub main {
my $ExcelFileToParse = "$ARGV[0]" ;
my $OutputDir="$ARGV[1]" ;
my $delimiter = chr($ARGV[2]);
my $rowEnd = chr($ARGV[3]);
my $InputDir = "$ARGV[4]" ;
my @arrayTemplateFiles = () ;
my $arrayTemplateFiles = readDirGetArrayOfFiles ($InputDir);
@arrayTemplateFiles = @$arrayTemplateFiles ; #dereference
# - foreach template file
foreach my $templateFile (@arrayTemplateFiles ) {print "working on \$templateFile is $templateFile\n";
my $FileToPrint = buildFileToPrint ( $templateFile , $OutputDir ) ;
my $StrTemplateFile = readFileReturnString ( "$InputDir/$templateFile" ) ;
ParseExcel ( $ExcelFileToParse , $delimiter , $rowEnd, $StrTemplateFile , $FileToPrint) ;
# -- parseExcel
# --- foreach row of the Excel file , read template file into strWork
# ---- foreach column in row - Find and Replace Col title from strWork
# ---- writeGeneratedFile with strWork
} #eof foreach template file
} #eof sub main
sub readDirGetArrayOfFiles {
my $InputDir = shift ;
# create a list of all *.pl files in
# the current directory
opendir(DIR, "$InputDir");
my @files = grep(/\.tmpl$/,readdir(DIR));
closedir(DIR);
# print all the filenames in our array
foreach my $templateFile (@files) {print "\$templateFile is $templateFile\n";
}
return \@files;
} #eof main
sub buildFileToPrint
{my $FileToPrint = shift ;
my $OutputDir = shift ;
$FileToPrint =~m/^.*(\\|\/)(.*)/; # strip the remote path and keep the filename
$FileToPrint =~ s/^(.*)(\.)(.*)/$1/ ; #strip the file extension
$FileToPrint =~ s/^.*(\\|\/)(.*)/$2/ ; # strip the remote path and keep the filename
$FileToPrint = "$OutputDir/$FileToPrint" ;
return $FileToPrint ;
} #eof sub BuildFileToPrint
sub ParseExcel {
my $ExcelFileToParse = shift ;
my $delimiter = shift ;
my $rowEnd = shift ;
my $strTemplate = shift ;
my $FileTemplate = shift ;
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->Parse("$ExcelFileToParse");
#foreach my $oWkS (@{$oBook->{Worksheet}}) {foreach my $worksheet (@{$workbook->{Worksheet}}) {#for my $worksheet ( $workbook->worksheets() ) {
my $RowMin = $worksheet->{MinRow} ; my $RowMax= $worksheet->{MaxRow} ;
# my ( $RowMin, $RowMax) = $worksheet->row_range();
# my ( $col_min, $col_max ) = $worksheet->col_range();
for my $row ( ($RowMin + 1) .. $RowMax) {my $strToReturn = $strTemplate ;
my $FileToPrint = $FileTemplate ;
my $col_min = $worksheet->{MinCol} ; my $col_max = $worksheet->{MaxCol} ; for my $col ( ($col_min + 1) .. $col_max ) {
my $cell = $worksheet->{Cells}[$row][$col] ; next unless $cell;
print " Row, Col = ($row, $col)\n";
print " Value = ", $cell->Value() ; #The Value
my $cellValueToReplace = $worksheet->{Cells}[$RowMin][$col] ; print "\$cellValueToReplace is $cellValueToReplace \n" ;
my $StrToReplace = '¤' . $cellValueToReplace->Value() . '¤' ;
print "\$StrToReplace is $StrToReplace \n" ;
my $StrValueToReplaceWith = $cell->Value() ;
print " \$StrValueToReplaceWith is $StrValueToReplaceWith \n" ;
$strToReturn =~ s/$StrToReplace/$StrValueToReplaceWith/g ;
$FileToPrint =~ s/$StrToReplace/$StrValueToReplaceWith/g ;
#print "\$strToReturn is $strToReturn \n" ;
#$strToReturn .= $cell->Value() . $delimiter ; #The Value
#print "Unformatted = ", $cell->Unformatted(), "\n";
}
chop ($strToReturn) ; #remove the latest delimiter
$strToReturn .= $rowEnd ; #end the row
print "\$FileToPrint is $FileToPrint " ;
printToFile ( $FileToPrint , $strToReturn ) ;
} #eof row
} #eof 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];
}
sub readFileReturnString {my $fileToRead = shift ;
print " readFileReturnString \$fileToRead is $fileToRead \n" ;
my $string = ();
{local $/=undef;
open FILE, "$fileToRead " or print "Couldn't open \$fileToRead $fileToRead : $!";
$string = <FILE>;
close FILE;
}
return $string ;
} #eof sub readFileReturnString
# =========================================== eof sub trim
# Action !!!
main();
1 ;
__END__
END ================== TemplateGenerator.0.1.0.pl
.
START ===== TemplateGenerator.0.1.0.ReadMe.txt
This Instruction assumess that use the following dir to extract the project
BaseDir=E:\perl\sfw\csv
if you do not simply find E:\perl\sfw\csv and replace it with <<YourExtractDir>> in this document
- Install strawberry perl from here: http://strawberryperl.com/ , add the folder containing the dmake.exe and perl.exe to your path
- to install the OLE::Storage_Lite go to E:\Perl\sfw\csv\lib\OLE-Storage_Lite-0.19\OLE-Storage_Lite-0.19
run the following commands :
perl Makefile.PL
dmake
dmake test
dmake install
dmake clean
- To install the SpreadSheet::ParseExcel module go to :
cd E:\perl\sfw\csv\lib\Spreadsheet-ParseExcel-0.2603
run the following commands:
perl Makefile.PL
dmake
dmake test
dmake install
dmake clean
- Now you have all the prerequisites to run the script :
- double click the E:\Perl\sfw\csv\parseExcelToCsv.2.0.cmd file if nothing happens check its contents ...
END ================== TemplateGenerator.0.1.0.ReadMe.txt
Posts
