how-to convert csv to confluence wiki with perl

package CsvToWiki; 

my $VERSION='1.0.0' ; 

use strict ; use warnings ; use Exporter; 

my @ISA = qw(AutoLoader Exporter);

my @EXPORT = qw( RunCsvToWiki );

BEGIN {     

    #add the scripts dir to the INC

    $0 =~ m/^(.*)(\\|\/)(.*)\.([a-z]*)/; 

    push ( @INC , $1) ;     

} #eof sub

use Configurator ; use Logger ; use FileHandler ; 

#    anonymous hash !!!

our ( $confHolder , $confHolderOfObj , $objLogger , $MyBareName , $objConfigurator)= () ; 

our ( $objFileHandler , $RunDir ) = () ; 

our ( $FileCsvInput , $FolderOutput , $HtmlPageSize ) = () ; 

our ( $CsvFileSeparator  , $TokenStart , $TokenStop  , $LineStart  , $LineStop )  = () ; 

our ( $BareFileCsvInput , $FileOutput , $StrToWriteStart , $StrToWriteStop , $Pager ) = () ; 

our ( $DirCsvInput  , $DirCsvOutput , $RegexFilterForCsvToWikiSourceFiles) = () ; 

our (  $EnvironmentName  , $ProductBaseDir , $ProductVersionDir , $HostName ) = () ; 

our (  $ConfDir  ) = () ; 

our ( $FileConfig ) = () ; 

# -----------------------------------------------------------------------------

# Initialize the object with the minimum data it will need to operate 

# -----------------------------------------------------------------------------

sub Initialize {

    my $self = shift ; 

    $objLogger = new Logger (\$confHolder) ; 

    $objFileHandler = new FileHandler ( \$confHolder ) ; 

    my $ActionName = ref ( $self ) ; 

    my $msg = "\$ActionName is $ActionName" ; 

    $objLogger->LogDebugMsg     ( "$msg" ) ; 

    $objLogger->LogDebugMsg ( "The \$ActionName is $ActionName " ) ; 

    $EnvironmentName = $confHolder->{'EnvironmentName'} ; 

    $ProductBaseDir = $confHolder->{'ProductBaseDir'} ; 

    $ProductVersionDir = $confHolder->{'ProductVersionDir'} ; 

    $HostName= $confHolder->{'HostName'} ; 

    $ConfDir = "$ProductVersionDir/conf/$HostName/ini" ; 

    $FileConfig = "$ConfDir/$EnvironmentName" . '.' . "$ActionName" . '.' . "$HostName" . '.ini' ; 

    $objLogger->LogDebugMsg ( "\$FileConfig is $FileConfig " ) ; 

    my $objConfigurator = clone Configurator($FileConfig , $confHolder); 

    # get the hash having the vars 

    $confHolderOfObj = $objConfigurator->getConfHolder () ; 

        #get it from the conf 

        $DirCsvInput = $confHolderOfObj->{'DirCsvInput'} || "$ProductVersionDir/docs/excel" ; 

        #get it from the conf 

        $DirCsvOutput = $confHolderOfObj->{'DirCsvOutput'} || "$ProductVersionDir/docs/csv" ;  ; 

        $objFileHandler->MkDir ( $DirCsvOutput ) unless ( -d $DirCsvOutput ); 

        #v1.3.0 ysg --- Add

        my ($RefFilesAndDirs , $RefDirsToProcess , $RefFilesToProcess ) = 

        $objFileHandler->ReadDirRecursivelyReturnArrays ( $DirCsvOutput , '(.*?)\.html') ; 

        my @FilesAndDirs = @$RefFilesAndDirs ; 

        my @DirsToProcess = @$RefDirsToProcess ; 

        my @FilesToProcess = @$RefFilesToProcess ; 

        foreach my $FileHtmlOldOutput ( @FilesToProcess ) { 

            $objLogger->LogDebugMsg ( 

                                    "CsvToWiki->Initialize: unlink \$FileHtmlOldOutput $FileHtmlOldOutput" ); 

            unlink ( $FileHtmlOldOutput ) ; 

            }

        $RegexFilterForCsvToWikiSourceFiles = 

                                    $confHolderOfObj->{'RegexFilterForCsvToWikiSourceFiles'} || '^.*\.csv' ; 

        # set a default page size if not defined

        $HtmlPageSize = $confHolderOfObj->{'HtmlPageSize'} || 20; 

        $CsvFileSeparator = chr(    $confHolderOfObj->{'CsvFileSeparator'}) || chr(44) ; 

        $TokenStart = ''  ; 

        $TokenStop = '|' ; 

        $LineStart = '||' ; 

        $LineStop = ' ' . "\n" ; 

        $StrToWriteStart = '{table-plus:autoTotal=true|autoNumber=true|sortColumn=2|sortIcon=true|columnTypes=S,I,I}' . "\n" ; 

        $StrToWriteStop = '{table-plus}' . "\n" ; 

        $Pager = '' ; 

} #eof sub

# =============================================================================

# START OO

#sourcF:\\\\\\http://www.netalive.org/tinkering/serious-perl/#oop_constructors

sub new {

    my $class = shift ;        # Class name is in the first parameter

    $confHolder = ${ shift @_ } if ( @_ )  ; 

    my $self = { };  # Anonymous hash reference holds instance attributes

    bless($self, $class);          # Say: $self is a $class

        $self->Initialize() ;    

    return $self;

} #eof const 

sub AUTOLOAD {

    my $self = shift ; 

    no strict 'refs'; 

    my $name = our $AUTOLOAD;

    *$AUTOLOAD = sub { 

    my $msg = () ; 

    $msg .= "BOOM! BOOM! BOOM! \n RunTime Error !!!\n" ; 

    $msg .= "Undefined Function $name(@_)\n" ;

    cluck(  "$self , $msg" ) ; 

    };

    goto &$AUTOLOAD;    # Restart the new routine.

}    

    sub get    {

        my $self = shift;

        my $name = shift;

        return $self->{$name};

    } #eof sub get 

    sub set    {

        my $self = shift;

        my $name = shift;

        my $value = shift;

        $self->{$name}=$value;

    } #eof sub set 

    sub dumpFields        {

        my $self = shift ; 

        my $strFields = () ; 

        foreach my $key (keys %$self)        {

            $strFields .= "$key = $self->{$key}\n";

        }

        return $strFields ; 

    } #eof sub dumpFields 

sub DESTROY {

    my $self = shift;

    #debug print "the DESTRUCTOR is called  \n" ; 

    return ; 

} 

# STOP OO

# =============================================================================

sub RunCsvToWiki {

    my $self = shift ; 

    my $msg = () ; 

    $msg = "CFPoint24    OK    Convert the csv files from the $DirCsvInput dir to html files in the $DirCsvOutput dir"  ;

    $objLogger->LogInfoMsg ( $msg ) ; 

    my ($RefFilesAndDirs , $RefDirsToProcess , $RefFilesToProcess ) = 

    $objFileHandler->ReadDirRecursivelyReturnArrays ( $DirCsvInput , $RegexFilterForCsvToWikiSourceFiles) ; 

    my @FilesAndDirs = @$RefFilesAndDirs ; 

    my @DirsToProcess = @$RefDirsToProcess ; 

    my @FilesToProcess = @$RefFilesToProcess ; 

    foreach my $FileToProcess ( @FilesToProcess ) {

        $objLogger->LogDebugMsg ( "csv \$FileToProcess is $FileToProcess " ) ; 

        #read the input metadata file 

        my $RefRowsFileInput = $objFileHandler->ReadFileReturnArray ( $FileToProcess ) ; 

        my @RowsFileInput = @$RefRowsFileInput; 

        $objLogger->LogDebugMsg ( "\@RowsFileInput is @RowsFileInput " ) ; 

        #FOREACH LINE OF THE FILE DO SOMETHING

        my $OldTableName = '' ; 

        my $ColList = '' ; 

        my $StrOutput = '' ; 

        my $rowCounter= 0 ; 

        my $PageStartLine = 0 ; 

        $Pager = '<table><tr>' ; 

        # start with 1 

        for ( my $rowCounter = 1 ; $rowCounter <= $#RowsFileInput ; 

                                            $rowCounter=$rowCounter + 1)                                     {

                if ( $rowCounter % $HtmlPageSize == 0 

                                || $rowCounter == $#RowsFileInput )     {

                    # $1 should produce the full path till the last \ or /

                    # $2 should produce the BareName without the .extension

                    # $3 should produce the file extension after the last dot

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

                    my $LinkName = $3 ; 

                    # use human readable 

                    my $StartItemNumberInPage = $rowCounter  ; 

                    $StartItemNumberInPage = $rowCounter - $HtmlPageSize +1 unless ( $rowCounter == $#RowsFileInput) ; 

                    $StartItemNumberInPage = $rowCounter - ($rowCounter % $HtmlPageSize ) if ( $rowCounter == $#RowsFileInput) ; 

                    my $EndItemNumberInPage = $rowCounter ;  

                    $LinkName = $LinkName . '.' . "$StartItemNumberInPage"   . '-' . "$EndItemNumberInPage"  . '.html' ; 

                    $Pager .= '<td>' . '<a href="' . $LinkName   . "\">" . 

                    "$StartItemNumberInPage" . '-' . "$EndItemNumberInPage"  . "</a></td>" ;   

                    #            print "\$Pager is $Pager " ; 

                    $objLogger->LogDebugMsg (  "\$LinkName is $LinkName " ) ; 

                    $objLogger->LogDebugMsg (  "\$rowCounter is $rowCounter " ) ; 

                    $objLogger->LogDebugMsg (  "\$#RowsFileInput is $#RowsFileInput " ) ; 

                }

        } #eof for

        $Pager .= '</tr></table>' ; 

        $Pager = "" ; 

        $rowCounter = 0 ; 

        foreach my $line ( @RowsFileInput  ) {

            $objLogger->LogDebugMsg ( "\$line is $line " ) ; 

            chop($line ) ; 

            my @tokens= split( "$CsvFileSeparator" , $line ) ; 

            $line = '' ; 

            if ( $rowCounter == 0 ) {

                $TokenStop = '||' ;  

            } else {

                $TokenStop = '|' ;   

                }

            foreach my $token ( @tokens)     {

                $token = GenerateToken ( $token )  ;

                $line = $line . $token ; 

            }

            $line = GenerateLine ( $line , $rowCounter ) ;         

            $rowCounter++ ;

            $StrOutput = $StrOutput . $line ; 

            if ( $rowCounter % $HtmlPageSize == 0 || $rowCounter == $#RowsFileInput)     {

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

                    my $LinkName = $3 ; 

                    # use human readable numbers start with 1 not 0

                    my $StartItemNumberInPage = 0 ; 

                    $StartItemNumberInPage    = $rowCounter - $HtmlPageSize +1 unless $rowCounter == $#RowsFileInput ; 

                    $StartItemNumberInPage    = $rowCounter - ( $rowCounter % $HtmlPageSize ) if ( $rowCounter == $#RowsFileInput ) ; 

                    my $EndItemNumberInPage = $rowCounter ;  

                    $LinkName = $LinkName . '.' . "$StartItemNumberInPage"  . '-' . $rowCounter   . '.html' ; 

                    $FileOutput = "$DirCsvOutput" . '/' . "$LinkName" ;

                    WriteFile ( $FileOutput , $StrOutput , $PageStartLine ) ; 

                    $objLogger->LogDebugMsg ( "\$StrOutput is $StrOutput" ) ; 

                    $StrOutput = '' ; 

                    $PageStartLine = $PageStartLine + $HtmlPageSize ; 

                }

                #debug $objLogger->LogDebugMsg ( "\$StrOutput is $StrOutput" ) ; 

        } #eof foreach line

        # END FOREACH LINE 

    } #eof foreach $FileToProcess 

    $Pager = ""; 

} #eof sub ExecuteRunCommand 

sub GenerateToken    {

    my $token = shift ; 

    $token = $TokenStart . $token . $TokenStop ;     

}    # eof sub GenerateToken

sub GenerateLine    {

    my $line = shift ; 

    my $rowCounter = shift ; 

    if ( $rowCounter % 2 != 0 ) 

        { $line = $LineStart . $line . $LineStop ; }

    else 

        {        $line = $LineStart . $line . $LineStop ; }

    return $line ; 

} # eof sub GenerateLine

sub trim        {

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

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

    return $_[0];

}

#eof sub trim 

sub WriteFile    {

    my $FileToWrite = shift ; 

    my $StrToWrite = shift ; 

        my $PageStartSize = shift ; 

        my $head .= 

'<html>

<head>

<title>tables</title>

  <style type="text/css">

    table tbody tr td {

      background-color: #eee;

    }

    table tr.odd  td {

      background-color: #fff;

    }

  </style>

</head><body>

' ; 

$StrToWrite = $head . $Pager . $StrToWriteStart . $StrToWrite .  $StrToWriteStop;   

$StrToWrite .= $Pager . '</body></html>' ; 

#$LinkName = $LinkName . '.' . $rowCounter . '-' . ( $rowCounter + $HtmlPageSize ) . '.html' ; 

$objFileHandler->PrintToFile ( $FileToWrite , $StrToWrite ) ; 

} #eof sub WriteToFile 

1 ; 

__END__

# VersionHistory:

# 1.0.0 --- 2011.12.25 - 20:13:19 --- Changed default conf dir

# 1.4.0 --- 2011.08.13 16:54:07 --- Added Control Flow documentation 

# 1.3.0 --- 2011.07.14 20:34:44 --- Added clean of html files 

# 1.2.1 --- 2011.07.06 11:05:05 --- Removed FilF:\\\\\\:Find 

# 1.0.0 --- 2011.07.06 10:26:59 --- ysg --- Added RegexFilterForCsvToWikiSourceFiles , full proof Initialize 

# 1.0.001.0.01_222618 --- Yordan Georgiev --- Generates Select All with full [] column list from Table

# 1.0.601.0.04_1.0.65 --- Yordan Georgiev --- REnamed to NiceHtmlTableGenerator.1.0.pl -- added css and a nice ttable

#hint: add to some rows : color:#FF0000; red or style="color:#009900" green to underline some colors

# [r1c1link alias|r1c1 pagetitle|r1c1 tip]

# RowNum    WorkFlowName    SubObjectType    SubObjectName    LogPageId    FolderId    ImaticaFolderName    WorkFlowId    WorkFlowLastUpdateTime    SubObjectInstanceId    PageId

# PageId = =G2&"-"&B2&"-"&C2&"-"&D2

# LogId = = "[etl-log-"&K2&"]"

# 

pre

how-to convert an Excel file into multiple html files by worksheets

Download the whole thingy from here

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