#-----------------------------------------------------------------------------
# File          : excelList.pl
# Author        : Ryan Herbst, ryan@amaroq.com
# Created       : 07/16/2004
#-----------------------------------------------------------------------------
# Description:
# This script will read in a viewdraw part list and generate a formated
# excel spreadsheet. The format of the part list is automatically detected
# by this script. Column names must not contain any spaces within them.
# This script takes the following args:
#    PROJECT_NAME
#    PROJECT_DIR
#-----------------------------------------------------------------------------
# Copyright (c) 2004 by Ryan Herbst. All rights reserved.
#-----------------------------------------------------------------------------
# Modification history:
# 07/16/2004: created.
#-----------------------------------------------------------------------------

# Load Excel Interface Library
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :D ATE);

# Extract args
if ( $ARGV[0] eq "" || $ARGV[1] eq "" ) {
   print "Usage: excelList.pl PROJ_NAME PROJ_DIR\n";
   exit;
}
else {
   $project = $ARGV[0];
   $dir     = $ARGV[1];
}

# Open part list
$data_file="$project.lst";
open(DAT, $data_file) || die("Could not open file: $data_file");
@raw_data=<DAT>;
close(DAT);

# Setup two arrays, one for field names, the other for field lengths
my @fld_name;
my @fld_len;
my $fld_count=0;

# Look for header line in source file
foreach $header (@raw_data) {

   # '#' is always in the header regardless of format of header
   if ( $header =~ /^ \#/ ) {

      #temp array to hold field name
      my $fld_char;
      my $count=0;
      my $size=0;

      # Clear newlines
      chop($header);

      # First field is always #
      $fld_char = "\#";

      # Get individual charactors in string
      @char_list = split(//,$header);

      # Go through each charactor in the string
      foreach $char (@char_list) {

         # If char is a space or a # increment field size
         if ( $char eq ' ' || $char eq '#' ) {
            $count++;
         }

         # Char is non space
         else {

            # If count is non zero set previous field size & field name
            if ( $count ne 0 ) {
               $fld_len[$fld_count]  = $count + $size;
               $fld_name[$fld_count] = $fld_char;
               $fld_char = "";
               $fld_count++;
               $count = 0;
               $size  = 0;
            }

            # Record charactor, increment size
            $fld_char .= $char;
            $size++;
         }
      }

      # Record Last Field
      $fld_len[$fld_count]  = $count + $size;
      $fld_name[$fld_count] = $fld_char;
      $fld_count++;

      # End loop at header
      last;
   }
}

# Open Excel File
my $Excel = Win32::OLE->new("Excel.Application");
$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
#$Book->Worksheets(3)->Delete;
#$Book->Worksheets(2)->Delete;
$Sheet->{Name} = "$project" . "_bom";

# Add header to excel file
$row = 4;
$last_col = 'A';
for ($i=0; $i < $fld_count; $i++ ) {
   $Sheet->Cells($row,$i+1)->{Value} = $fld_name[$i];
   if ( $i < ($fld_count-1) ) { $last_col++; }
}

# Setup border & alignment for header
$range= "A".$row.":"."$last_col"."1";
with (my $Borders = $Sheet->Range($range)->Borders(xlEdgeBottom),
   LineStyle =>xlDouble, Weight => xlThick , ColorIndex => 1);

# Start another loop through each line
$row++;
foreach $line (@raw_data) {

   # Only process lines containing true data
   if ( $line !~ /^ \#/ && length($line) > 20 ) {

      # Get Each Sub Field
      $off = 0;
      for ($i=0; $i < $fld_count; $i++) {

         # Extract sub strings
         $sub[$i] = substr($line,$off,$fld_len[$i]);
         $off += $fld_len[$i];

         # Strip Leading spaces
         $sub[$i] =~ s/^\s*//;

         # Strip Trailing spaces
         $sub[$i] =~ s/\s*$/ /;
      }

      # Process Found Data, skip bad lines
      if ( $sub[1] !~ /--/ && $sub[1] !~ /1-/ ) {

         # Process each sub field
         for ($i=0; $i < $fld_count; $i++) {

            # Setup Alignment / data type
            $range= "A"."$row".":"."$last_col"."$row";
            $Sheet->Range($range)->{NumberFormat} = "@";

            # Add border
            if ( $sub[0] =~ /[0-9]/ && $row ne 5 ) {
               with (my $Borders = $Sheet->Range($range)->Borders(xlEdgeTop),
                  LineStyle =>xlContinuous, Weight => xlThin, ColorIndex => 1);
            }

            # Fill Up Data
            $Sheet->Cells($row,$i+1)->{Value} = $sub[$i];
         }
         $row++;
      }
   }
}

# Add border to last line
$row--;
$range= "A"."$row".":"."$last_col"."$row";
with (my $Borders = $Sheet->Range($range)->Borders(xlEdgeTop),
   LineStyle =>xlDouble, Weight => xlThick, ColorIndex => 1);

# Setup format & autofit for cells
$range = "A:" . "$last_col";
$Sheet->Columns($range)->AutoFit();

# Add file information to top
$Sheet->Cells(1,1)->{Value} = "BOM For $project";
$Sheet->Cells(2,1)->{Value} = "Generated " . localtime time;
$Sheet->Cells(1,1)->Font->{FontStyle} = "Bold";
$Sheet->Cells(1,1)->Font->{Size}      = 14;
$Sheet->Cells(2,1)->Font->{FontStyle} = "Bold";

# Format Page For Printing
with ($Sheet->PageSetup, PrintTitleRows => "$4:$4", PrintTitleColumns => "");
with ($Sheet->PageSetup, Zoom => Variant(VT_BOOL, 0),
   FitToPagesTall => 20, FitToPagesWide => 1, Orientation => xlLandscape,
   PrintTitleRows => "4:4");

# Save File
$Book->SaveAs("$dir\\$project"."_bom.xls");