#!/usr/bin/perl
#-----------------------------------------------------------------------
# Description:
#
# Convert a tab-delimited file to Excel 2003 format
#
# Usage:
#
#   tdtoxl_v2 -i filetoconvert -o convertedfile [-s 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.
#
# 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 lib '/app/tools/common/lib';
use Common::UTF8;

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


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

if ( $gStyle ) {
   open FILE, "$gStyle" or die( "$gStyle: " .  $! . "\n" );
   while( my $line = <FILE> ) {
      chomp($line);
      $line =~ s/^\s*// if ( $line );
      $line =~ s/\s*$// if ( $line );
      next if ( $line =~ m/^#/ );

      if ( $line =~ m/\#.*$/ ) {
         $line =~ s/\#.*$//;
         $line =~ s/\s*$//;
      }

      my($colName, $fmt, $extra) = split(":",$line);

      my $colNumber = name2number($colName);

      $fmt = lc $fmt;
      if ( $fmt ne 'text' and $fmt !~ m/^number/ ) {
         die("Unknown format '$fmt': only 'text' and 'number' 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");
      }
   }

   close FILE;
}

#----------------------------------------------------
# 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);

# 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 $dataSource = "STDIN";

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

my $header = <$dataSource>;

chomp $header;

print "Header: $header\n";

my @headerList = split("\t",$header);

print "Header list: @headerList\n";

foreach my $field(@headerList){
   print "Header: $field";
   printField($gRow, $field, undef, $headerFormat);
}

$gRow++;
$gColumn = 0;

while (my $fields = <$dataSource>){
   chomp $fields;
   my @fieldList = map { replace_newline($_)} split("\t",$fields);

   my $col = 0;
   foreach my $field(@fieldList){
      #print "printing field $field\n";

      my $_fmt = $gFormatMap{$col}; # either 'number' or 'text'

      my $_field = $field;

      if( $_fmt =~ /^number/i )
      {
         $_field =~ s/\x{a0}//g; # FB18671 number field shouldn't have NBSP
      }

      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 );
         }

         $_fmt = "number";
         printField($gRow, $_field, $_fmt, $gColumnFormat{$col} );
      }
      else
      {
         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);
      } 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 parseCommandLine {
   my($a) = @_;
   my $ifile;
   my $ofile;
   my $style;

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

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

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

}#parseCommandLine

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