#!/usr/bin/perl

# Version 0.03
# January 2002
# Written by Miles Light (miles@mileslight.com)


use strict;
use Spreadsheet::WriteExcel;
use Text::ParseWords;

if ($#ARGV < 1) {  usage()  }

my $book = pop @ARGV;
my @args = @ARGV;
my $sheetname = " ";

unless ($book =~ /\.xls/i) { xlserror() } 

open (F,">$book") || die writeerror($book,$!) ;
close F;
	
	# Create a new Excel workbook
	my $workbook  = Spreadsheet::WriteExcel->new($book);

	# Set Formatting:
	my $numformat = $workbook->addformat();
	my $txtformat = $workbook->addformat();
	$numformat->set_num_format('#,##0.00');
	$numformat->set_font("Courier New");
	$txtformat->set_font("Courier New");
	$txtformat->set_align("Left");

my $i = 0;
foreach my $file (@args)	{   # Loop over the input parameters -- make a sheet out of each one.
	
	$i++;
	$sheetname = rename_sheet($file,$i);

	# Open the Comma Seperated Variable file
	open (CSVFILE, $file) or die "Couldn't open the .csv file named $file: $!\n\n";

	# Create a new worksheet:
	my $worksheet = $workbook->addworksheet($sheetname);

	# Row and column are zero indexed
	my $row = 0;
	
	while (<CSVFILE>) {
		
		my @Fld = parse_csv($_);  # Parse, leaving the quotes in-tact
		my $col = 0;
		foreach my $token (@Fld) { # if it's a number, then use number formatting:
		    if (($token =~ /-?(?:\d+(?:\.\d*)?|\.\d+)/) && ($token !~ /\"/))
				    { $worksheet->write($row, $col, $token, $numformat) }
		    else	    { $token =~ s/\"//g; $token =~ s/\n//g;
				      $worksheet->write($row, $col, $token, $txtformat) }
		$col++;
		}
		$row++;
	    }
close CSVFILE;
}

# Parse the line:
sub parse_csv {  return quotewords(",",1, $_[0]);  }

# Error Messages:

sub xlserror {
	die <<EOM;

	ERROR: Last argument given did not end with .xls
	
	Usage: xlwrite file1.csv [file2.csv ...] workbook.xls

EOM

}

sub usage {
	die <<EOM;

	XLWrite: An executable to create XLS files from .csv source files.
	Miles K. Light                              miles\@mileslight.com
	Spreadsheet::WriteExcel by John McNamara    jmcnamara\@cpan.org 
	January 2002
	Usage:  xlwrite file1.csv [file2.csv ... ] workbook.xls

EOM

}

sub rename_sheet {    # Check the name of the sheet arguments: use the text just before the (last?) period

	my ($filename,$num) = @_;  my $name;
	$filename =~ /\W?(\w{1,})(\.\w+)?$/;  # Match last word chars, not including the suffix.

	if ($1)  { $name = $1 }
	  else    { $name = "Sheet$num";
		  print "\n\tCouldn't find a reasonable worksheet name for parameter number: $num\n";
		  print "\t  ... using a default name -- Sheet$num.\n"; 
		  }
	return $name;
}

sub writeerror {  
	my ($filename,$errormsg) = @_;
	print <<EOM;
	
	ERROR: Cannot write to file. Is it already open in MS Excel?
	Error message was: $errormsg
	Tried to write to: $filename
	
EOM
}