I am the king of the universe ... Template Generator ... so f..cking what ...

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