#!/usr/bin/perl
#-----------------------------------------------------------------------
# Description:
#
# Convert a tab-delimited file to Excel 2003 format
#
# Usage:
#
#   csv2excel.pl -i filetoconvert -o convertedfile [-s styletemplatefile] [-t styletemplate]
#
# where
#
#   -i specifies the name of the tab-delimited file.  If "-i" is not
#      specified, then STDIN is used.
#   -o specifies the name of the Excel file to be created
#   -s specifies an optional stylesheet (described below).  If '-s'
#      is not specified, then all cells will default to text formatting.
#   -t specifies the style template inline, rather than a seperate file.
#      The formatting will be similar to the Style Template below, except
#      that it will be a comma-separated list of style definitions.
#      ex:  -t="A:number:0.00,C:number".
#      -> Thought about eliminating the column labels, but let's just go with it.
#      !!! We need to be able to convert dates into excel dates.   Excel dates are epoch seconds.
#      !!! Incoming dates in the CSV file might be in a variety of formats, depending on the client's
#      !!! locale settings.   So we'll need to pass the format the date is in to this program.
#      !!! I don't think we'll ever have commas in a date format.  Usually it's '/'.
#      !!! If we can pass the 'dateFormat' value stored in Common::DateFormat for the locale, that should
#      !!! do the trick (ie. 'D.M.Y', or 'Y/M/D', etc.)
#
# Style Template:
#   This is a file that can be used to define column-specific
#   formatting.  This is useful if you want certain columns to be text
#   and others to be numeric.  The style template should contain lines
#   of the form NN:code, where NN is the Excel alpha column label,
#   and code is the format type ("text" or "number").  Note that this
#   only affects the data rows; the header will automatically be
#   formatted as text cells with a gray background.
#
# For 'number' columns, the default is to display numbers as integers.
# To allow for decimals, you must augment your style template.  For example,
# to show two decimal places for column A, your style template should have
# the following:
#   A:number:0.00
#
# Warning: if the number of lines exceeds 65536, and/or if the length
# of any given cell exceeds 256 characters, you _will_ end up with
# a truncated report.  Therefore, it's important that you perform any
# data bounds checking BEFORE calling this script.
#
# 8/5/11: Added support for numeric formatting.
#-----------------------------------------------------------------------
use strict;

use Spreadsheet::WriteExcel;
use Getopt::Long;
use Data::Dumper;
use Text::CSV;

use lib '/app/tools/common/lib';
use Common::UTF8;
use Common::Log;

my %args;
parseCommandLine( \%args );
my $gInputFile  = $args{input};
my $gOutputFile = $args{output};

my %gFormatMap;
my @columnTemplates;
if ( $args{style} ) {
    _columnTemplatesFromFile( \@columnTemplates, $args{style} );
} elsif ( $args{template} ) {
    _columnTemplatesFromString( \@columnTemplates, $args{template} );
}

#------------------------------------------------------------------------
# gFormatMap - contains optional formatting for each column (first column
#    is column 0).
#------------------------------------------------------------------------

if ( scalar @columnTemplates ) {
    foreach my $line (@columnTemplates) {
        my ( $colName, $fmt, $extra ) = split( ":", $line );

        my $colNumber = name2number($colName);

        $fmt = lc $fmt;
        if ( $fmt ne 'text' and $fmt !~ m/^number/ and $fmt !~ /date/ ) {
            die("Unknown format '$fmt': only 'text' ,'number' or 'date' are allowed\n");
        }

        #print("[$colNumber] colName($colName)   format($fmt)\n");

        if ( !exists $gFormatMap{$colNumber} ) {

            $fmt = "$fmt:$extra" if ($extra);

            $gFormatMap{$colNumber} = $fmt;
        } else {
            die("Duplicate format detected for column '$colName'\n");
        }
    }

}

#----------------------------------------------------
# Open the output file.  Currently this is Excel 2003.
#----------------------------------------------------
my $workbook = Spreadsheet::WriteExcel->new($gOutputFile);

my $worksheet = $workbook->add_worksheet();

#--------------------------------
# Freeze the top row (the header)
#--------------------------------
$worksheet->freeze_panes( 1, 0 );

my $headerFormat = $workbook->add_format();

#$gray = $workbook->set_custom_color(23,200,200,200);
my $headerFillColor = $workbook->set_custom_color( 23, 216, 216, 216 );

#$headerFormat->set_bg_color("gray");
$headerFormat->set_bg_color($headerFillColor);
$headerFormat->set_bold();
$headerFormat->set_size(8);
$headerFormat->set_text_wrap();

#$headerFormat->set_bottom(6); # double line
#$headerFormat->set_bottom(5); # continuous (weight 3)
$headerFormat->set_bottom(2);    # continuous (weight 2)
$headerFormat->set_left(1);      # continuous (weight 1)
$headerFormat->set_right(1);     # continuous (weight 1)
$headerFormat->set_top(1);       # continuous (weight 1)

#---------------------
# Format for data rows
#---------------------
my $gDataFormat = $workbook->add_format();
$gDataFormat->set_size(8);

my $gDateFormat = $workbook->add_format( num_format => 'yyyy-mm-dd' );
$gDateFormat->set_size(8);

# if there are any column-specific formats for a data column,
# they'll be stored here
my %gColumnFormat;

my $gColumn = 0;
my $gRow    = 0;

#-----------------------------------------------------
# If any input file was specified, we'll read from it.
# Otherwise use STDIN
#-----------------------------------------------------
my $csv = Text::CSV->new( { binary => 1 } );
my $dataSource;

if ($gInputFile) {
    open IFILE, "$gInputFile" or die( "$gInputFile: " . $! . "\n" );
    binmode IFILE, ":utf8";
    $dataSource = *IFILE;
} else {
    binmode STDIN, ":utf8";
    $dataSource = *STDIN;
}

while ( my $row = $csv->getline($dataSource) ) {
    if ( 0 == $gRow ) {
        foreach my $field (@$row) {
            Log->debug("Header: $field");
            printField( $gRow, $field, undef, $headerFormat );
        }
    } else {
        my $col = 0;
        foreach my $field (@$row) {
            my $_fmt = $gFormatMap{$col};

            if ( $_fmt =~ m/^number:/i ) {
                #
                # Use a column-specific format object instead of the default format object
                #
                my ( $ignore, $format ) = split( ":", $_fmt );

                if ( !exists( $gColumnFormat{$col} ) ) {
                    $gColumnFormat{$col} = $workbook->add_format();
                    $gColumnFormat{$col}->set_size(8);
                    $gColumnFormat{$col}->set_num_format($format);    # XXX
                }

                $_fmt = "number";
                printField( $gRow, $field, $_fmt, $gColumnFormat{$col} );
            } else {

                # 'date' will also land here, but we'll do the parsing in printField.
                #
                printField( $gRow, $field, $_fmt, $gDataFormat );
            }

            $col++;
        }
    }

    $gRow++;

    $gColumn = 0;
}

close IFILE if ($gInputFile);

if ( $gRow > 65535 ) {
    print STDERR "ERROR: tdtoxl_v2: Maximum rows exceeded!!!\n";
}

#=====================
#
#  Subroutines below
#
#=====================
sub replace_newline {
    my $field = shift;
    $field =~ s/\\n/\n/g;
    return $field;
}

#----------------------------------------------
# printField - output a line to the spreadsheet
#----------------------------------------------
sub printField {

    # Get the current row, the data to be written and
    # the mode (text or number)
    my $gRow     = shift;
    my $data     = shift;
    my $dataType = shift;
    my $format   = shift;

    $data =~ s/\s*$//;

    # Output either text or a numerical value
    # Note: if a value is missing and the desired format is
    # number, we actually output it as text s.t. the cell appears
    # with an empty field instead of a zero.

    if ( defined $data ) {
        if ( $data =~ m/(\D*)(\d+)(\D*)(\d*)/ and $dataType and $dataType eq 'number' ) {
            $worksheet->write_number( $gRow, $gColumn, $data, $format );
        } elsif ( $dataType =~ /date/ ) {

            # We are always going to use the 'yyyy-mm-dd' format for dates.
            #
            my ( $ignore, $dateFormat ) = split( ':', $dataType );

            # $dateFormat tells us how the incoming date is formatted, so we can parse it.
            #
            if ( $dateFormat =~ /(Y|M|D)(.)(Y|M|D).(Y|M|D)/ ) {
                my ( $y, $m, $d );

                my $first  = $1;
                my $sep    = $2;
                my $second = $3;
                my $third  = $4;

                my @bits = split( $sep, $data );
                $y = $bits[0] if ( $first eq 'Y' );
                $y = $bits[1] if ( $second eq 'Y' );
                $y = $bits[2] if ( $third eq 'Y' );
                $m = $bits[0] if ( $first eq 'M' );
                $m = $bits[1] if ( $second eq 'M' );
                $m = $bits[2] if ( $third eq 'M' );
                $d = $bits[0] if ( $first eq 'D' );
                $d = $bits[1] if ( $second eq 'D' );
                $d = $bits[2] if ( $third eq 'D' );

                my $formattedDate;

                # NULL dates (0000-00-00) should just be blank.
                if ( $data && '0000-00-00' ne $data ) {
                    $formattedDate = sprintf( "%04d-%02d-%02dT", $y, $m, $d );
                }
                $worksheet->write_date_time( $gRow, $gColumn, $formattedDate, $gDateFormat );
            }
        } else {
            $data = Common::UTF8::Encode($data);
            $worksheet->write_string( $gRow, $gColumn, $data, $format );
        }
    } else {
        $data = Common::UTF8::Encode($data);
        $worksheet->write_string( $gRow, $gColumn, $data, $format );

        #$worksheet->write_utf16be_string($gRow, $gColumn, $data, $format);
    }

    $gColumn++;
}    #printField

#------------------------------------------------------
# name2number - convert a alpha column name to a number
# Example: A = 0, B = 1, AA = 26, AB = 27, etc.
#------------------------------------------------------
sub name2number {
    my ($name) = @_;
    my $number;

    die("name2number: illegal name '$name'") if ( !$name or $name !~ m/^[a-zA-Z]+$/ );

    if ( length($name) == 2 ) {

        my $c1 = lc substr( $name, 0, 1 );
        my $c2 = lc substr( $name, 1, 1 );

        $number = ( ( ord($c1) - ord('a') ) + 1 ) * 26 + ( ord($c2) - ord('a') );

    } elsif ( length($name) == 1 ) {

        my $c1 = lc substr( $name, 0, 1 );
        $number = ord($c1) - ord('a');

    } else {
        die("name2number: only 2-digit column names supported");
    }

    return $number;

}    #name2number

sub _columnTemplatesFromFile {
    my ( $cols, $styleFilePath ) = @_;

    open FILE, "$styleFilePath" or die( "$styleFilePath: " . $! . "\n" );
    while ( my $line = <FILE> ) {
        chomp($line);

        #  Eliminate extra white space
        #
        $line =~ s/^\s*// if ($line);
        $line =~ s/\s*$// if ($line);

        # Skip comment lines
        #
        next if ( $line =~ m/^#/ );

        # Eliminate any in-line comments.
        #
        if ( $line =~ m/\#.*$/ ) {
            $line =~ s/\#.*$//;
            $line =~ s/\s*$//;
        }

        push @$cols, $line;
    }
    close FILE;
}

sub _columnTemplatesFromString {
    my ( $cols, $styleString ) = @_;

    # Keep this pretty simple.  I don't expect (or want) any comments in here.
    #
    push @$cols, split( ',', $styleString );
}

sub parseCommandLine {
    my ($a) = @_;
    my $ifile;
    my $ofile;
    my $style;
    my $template;

    if (
        !GetOptions(
            'i|in=s'       => \$ifile,
            'o|out=s'      => \$ofile,
            's|style=s'    => \$style,
            't|template=s' => \$template,
        )
      ) {
        die( "error reading arguments " . $_ );
    }

    die( usage("Output file must be specified") ) unless ($ofile);

    $a->{input}    = $ifile;
    $a->{output}   = $ofile;
    $a->{style}    = $style;
    $a->{template} = $template;

}    #parseCommandLine

sub usage {
    my $errstr = shift;
    my $text = ($errstr) ? "ERROR: $errstr\n" : "";
    $text .= "Usage $0 [-i inputfile] -o outputfile [-s stylesheet]";
    return $text;
}    #usage