#perl -w
# export-spreadsheet.pl
# Perl script to generate spreadsheet files for OOo-Calc and MS-Excel
# n6lhv@arrl.net (Wayne Smith)
# created:  20-December-2004
# updated:  22-December-2004

# Set up the environment
use strict;
use DBI;
use Spreadsheet::WriteExcel;
#use Win32::OLE;

# Connect to the database (MySQL) via ODBC
my $DSN  = "DBI:ODBC:fcculs";
my $user = "root";
my $pw   = "";
my $dbh  = "";


# Generate a WorkBook
sub writeAWorkBook {
  # we'll need these local variables to set things up
  my $ulsTbl  = "";
  my $workbook  = "";
  my $worksheet  = "";
  my $sth  = "";

  # Row and column are zero indexed in Spreadsheet::WriteExcel
  my $row = 0;
  my $column = 0;

  # we'll need these local variables for the actual ULS table data
  my @dataOneRow  = "";
  my $dataOneField  = 0;

  # Let's use a more human-readable variable for the passed parameter
  $ulsTbl = $_[0];

  # Create a new Excel workbook
  # (Note: This can't seem to be moved out of the subroutine...ws)
  $workbook = Spreadsheet::WriteExcel->new($ulsTbl . ".xls")
   or die "Couldn't open workbook file " . $ulsTbl . "for writing: " . $workbook->errstr;


  # Let's display our progress to the user...
  print "Now generating the '" . $ulsTbl . "' worksheet...\n";

  # Create a new worksheet within the workbook
  $worksheet = $workbook->addworksheet($ulsTbl);
    
  # Prepare the SQL query
  $sth = $dbh->prepare('SELECT * FROM ' . $ulsTbl)
    or die "Couldn't prepare statement: " . $dbh->errstr;

  # Execute the SQL query
  $sth->execute()
   or die "Couldn't execute statement: " . $sth->errstr;

  # Write out the column (field) headers
  for ( $column = 1 ; $column <= $sth-> {NUM_OF_FIELDS} ; $column++ ) {
    $worksheet->write($row, $column-1, $sth-> {NAME}->[$column-1]);
  }

  # increment the worksheet row counter
  $row++;

  # Read the matching records and print them out
  # (Note: We might need to write out some variables as strings manually in the future...ws)
  while (@dataOneRow = $sth->fetchrow_array()) {
    $column = 0;
    foreach $dataOneField (@dataOneRow) {
      $worksheet->write($row, $column, $dataOneField);
      $column++;
    }
    $row++;
  }

  # Finish with the query
  $sth->finish;
          
  # Set the active worksheet
  $worksheet->activate();
}


# Generate a WorkBook with a single WorkSheet
sub main {
  # Connect to the database (MySQL) via ODBC
  $dbh = DBI->connect($DSN,$user,$pw)
   or die "Cannot connect: $DBI::errstr\n" unless $dbh;
    
  # Generate a WorkBook using the command-line parameter
  writeAWorkBook ($ARGV[0]);

  # Disconnect from the database
  $dbh->disconnect;
}


# Main
main;