#!perl
############################################################
## txt2excel-1.0
## Written By: Rohit Mishra ( rohit[at]rohitmishra.com)
############################################################

use strict;
use Getopt::Long;
require Spreadsheet::WriteExcel;
require IO::Scalar;

my @file;
my $xls_sheet;
my $help;
my $recordsep;

GetOptions( "file=s" => \@file,
	    "xls=s" => \$xls_sheet,
	    "recordsep=s" => \$recordsep,
	    "help" => \$help
	  );

@file = split(/,/,join(',',@file ));

if( $help ){
	&Usage;
	exit(0);
}

if( ! @file ){
	&Usage;
	print "Null text file list. Exiting ..\n";
	exit(1);
}
if( ! $xls_sheet ){
	&Usage;
	print "Output file must be specified. Exiting ..\n";
	exit(1);
}

my $number = 1;
my $file = undef;

my $xls_str;
tie *XLS, 'IO::Scalar', \$xls_str;
my $workbook  = Spreadsheet::WriteExcel->new(\*XLS);
my $name;

foreach $file ( @file ){
	$name = "Category $number";
	print "Reading file: $file\n";
	open F, "<", $file or croak("Can't open $file: $!");
	$/ = $recordsep || "\n";
	my @data = <F>;
	close F;
	$/ = "\n";
	
	print "Generating sheet: $name\n";
	my $worksheet = $workbook->addworksheet( $name );
	
	for ( my $row = 0; $row < @data; $row++ ) {
		chomp( $data[$row] );
		$data[$row] =~ s/^\s+//;
		my @line = split(/\s+/, $data[$row] );
		for ( my $col = 0; $col < @line; $col++ ) {
			$worksheet->write_string($row, $col, $line[$col] || "");
		}
	}
	$number++;
}
$workbook->close();
print "Sheet done.\n";
open( XL_SHEET,">$xls_sheet" ) || die( "Could not open file: $xls_sheet\n" );
print XL_SHEET $xls_str;
close( XL_SHEET );
print "Output file (XLS): $xls_sheet\n";

sub Usage(){
	print <<EOF;

This script generates the Excel binary from the files provided to this script as input.

Note: The Excel file produced by this script is compatible with Excel 5, 95, 97, 2000
and 2002.

Usage: $0 
	  -f[ile] <comma separated file list> ( Required ) 
	          
		  The argument to this option, can be one or more files.
		  If the argument contains a single file, the output
		  Excel workbook will contain one worksheet.
		  If more than one file is provided to this option, a new 
		  worksheet is added to the workbook, and the elements to 
		  that file, will be added to the new worksheet.

		  The default cell delimiter is hardcoded into this script
		  and is one or more than one space ("\\s" character in perl) 

		  The default record separater can be specified from the command
		  line. ( See -r option below )
	   
	  -x[ls] <O/P xls sheet> ( Required )
		  
		  The argument to this option, will be the output Excel workbook,
		  generated by this script.
		
	  -r[ecordsep] <record separator> ( Default: "\\n" )
		  
		  Argument to this option, will be the record separator used, while
		  reading the files.
	  
          -h[elp]

                 Print this help information.

EOF
}


=head1 NAME

txt2excel-1.0.pl

=head1 DESCRIPTION

This script generates the Microsoft Excel binary file from the files provided to this script as input.

Note: The Excel file produced by this script is compatible with Excel 5, 95, 97, 2000
and 2002.

Usage: txt2excel-1.0.pl -f[ile] <comma separated file list> ( Required ) 
	          
		  The argument to this option, can be one or more files.
		  If the argument contains a single file, the output
		  Excel workbook will contain one worksheet.
		  If more than one file is provided to this option, a new 
		  worksheet is added to the workbook, and the elements to 
		  that file, will be added to the new worksheet.

		  The default cell delimiter is hardcoded into this script
		  and is one or more than one space ("\\s" character in perl) 

		  The default record separater can be specified from the command
		  line. ( See -r option below )
	   
	  -x[ls] <O/P xls sheet> ( Required )
		  
		  The argument to this option, will be the output Excel workbook,
		  generated by this script.
		
	  -r[ecordsep] <record separator> ( Default: "\\n" )
		  
		  Argument to this option, will be the record separator used, while
		  reading the files.
	  
          -h[elp]

                 Print this help information.

=head1 README

This script generates the Microsoft Excel binary file from the files provided to this script as input.
The Microsoft Excel file produced by this script is compatible with MS Excel 5, 95, 97, 2000
and 2002. For details see help on usage.


=head1 PREREQUISITES

This script requires the C<strict> module.  It also requires
C<Spreadsheet::WriteExcel> and C<Getopt::Long>.

=head1 COREQUISITES

Spreadsheet

=pod OSNAMES

Unix, Solaris, Linux

=head1 AUTHOR

Rohit Mishra E<lt>F<rohit@rohitmishra.com>E<gt>

=head1 COPYRIGHT

Copyright (c) 2006 Rohit Mishra <rohit@rohitmishra.com>. All rights reserved
This program is a free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=pod SCRIPT CATEGORIES

Educational/ComputerScience
Educational

=cut