#! /usr/local/bin/perl -w

# Extract data from sheet(s) of Excel files in a chosen directory
# George Bell - WIBR BaRC
# 7 December 2004

# Field for project name
# and other fields used to create project key
# (first field = 0)

$PROJECT_FIELD_NUM = 3;
$LAB_FIELD_NUM = 0;

# Use this Perl module
# See http://search.cpan.org/~tmtm/Spreadsheet-ParseExcel-Simple-1.01/Simple.pm
# for help and more info

use Spreadsheet::ParseExcel::Simple;

if (! $ARGV[0])
{
	printUsageAndExit();
}
$dir = $ARGV[0];

opendir(DIR, "$dir") || die "Cannot open .: $!"; 
foreach $file (sort readdir(DIR))
{
    if ($file =~ /\.xls$/)      # if file ends in .xls
	{
		$excelFile = $file;
		
		print STDERR "Processing $dir/$excelFile\n";

		$xls = Spreadsheet::ParseExcel::Simple->read("$dir/$excelFile");
		
		foreach $sheet ($xls->sheets) 
		{
			$lineNum = 0;
		
			while ($sheet->has_data) 
			{  
				# Capture one row of data at time
			
				@data = $sheet->next_row;

				########  Create project key as 'project<tab>lab'   ########

				# (Project key could also contain other info if desired)

				# Skip header (first row) of each sheet
				# Require row to contain both project name and lab name

				if ($lineNum && $data[$PROJECT_FIELD_NUM] && $data[$LAB_FIELD_NUM])
				{
					push @projects, "$data[$PROJECT_FIELD_NUM]\t$data[$LAB_FIELD_NUM]";
				}
				
				$lineNum++;
			}
		}
	}
}

##########  Make non-redundant list of all projects

%seen = ();
foreach $project (@projects) 
{
    push(@projects_nonredundant, $project) unless $seen{$project}++;
}

##########  Print list of projects

$date = localtime;
print "Project list as of $date:\n";

foreach $nonredundant_project (sort @projects_nonredundant)
{
	print "$nonredundant_project\n";
}

##########  Subroutines  ##########

# If no argument is given, print usage and exit;

sub printUsageAndExit
{
	print STDERR "\nPrint a non-redundant list of project names from a directory of Excle files\n";
	print STDERR "USAGE: $0 directory > projectList\n";
	print STDERR "Edit top of file to select column numbers of desired information\n";
	
	exit;
}
