#######################################
#  Command-line script for using 
#  ExcelDriver. See UsageMessage below
#  for usage description
#
#  Copyright: Microsoft Corporation
#
#######################################

# Write out each command line argument
foreach ($Arg in $Args)
{ Write-Host "Argument: $Arg" -Fore Green }


# Temporarily set ExcelDriver path - needs to be updated to GAC
$dllpath = "F:\hpcbuild\private\xl\ExcelRunner\bin\ExcelDriver.dll"


 
# Usage message to print when incorrectly used
$UsageMessage = "Utility for driving Excel with a simple command-line interface.

XLCMD /input:[workbook path] /set:<range>=<value> /get:<range> /macro:<macroname> /output:[workbook save path]
  
  /input:[workbook path]        Specifies the .xlsx or .xlsm file to open.
  /set:<range>=<value>          Sets the specified <range> to <value>.
       <range> must be an A1-style reference to a single cell.
  /get:<range>                  Reads the specified <range> and writes the value 
                                to stdout.
        <range> must be an A1-style reference to a single cell.
  /macro:<macroname>            Runs the specified macro.
  /wait:<TRUE/FALSE>            Executes the specified commands and then waits
                                for Excel to exit if true. Defaults to false.
  /output:[workbook save path]  Specified the full file path at which to save the final workbook.
"

# Set default wait behavior to false
$waitForExit = $False
            
Try {
    # There should be one or two args without an initial / or - switch. 
    # This is the workbook to load and optionally the workbook to save to
    $sourceWorkbooks = foreach($Arg in $Args) {Select-String -InputObject $Arg "/input:"} 
    if (($sourceWorkbooks | measure-object).Count -ne 1) 
    {
                    Write-Host("$UsageMessage")
                    exit 1
    }
    
    $destWorkbooks = foreach($Arg in $Args) {Select-String -InputObject $Arg "/output:"} 
    if (($destWorkbooks | measure-object).Count -gt 1) 
    {
                    Write-Host("$UsageMessage")
                    exit 1
    }

    # Load ExcelDriver - use $hider to avoid printing message
    #$hider = [Reflection.Assembly]::LoadFile($dllpath)

    $config = [System.Configuration.ConfigurationManager]::OpenExeConfiguration("F:\hpcbuild\private\xl\ExcelRunner\xlcmd\bin\Debug\xlcmd.exe")
    
    
    $hider = [Reflection.Assembly]::LoadFile([IO.Path]::GetFullPath("ExcelDriver.dll"))
    
    $xlDriver = new-object ExcelDriver.xlDriver

    # Open source workbook. Only one element in Source workbooks list. 
    foreach($workbook in $sourceWorkbooks)
    {
        $wbPath = $workbook.ToString().Substring("/input:".Length)
        $xlDriver.OpenWorkbook([IO.Path]::GetFullPath($wbPath))
    }

    # Now walk through all the arguments and execute each one.
    foreach ($Arg in $Args)
    {
        # arguments are in the form /argType:parameter
        $parts = $Arg.Split(':', 2)
        if (($parts | measure-object).count -ne 2)
        {
           Write-Host("ERROR: Unknown or ill-formed argument: $Arg`n")
           Write-Host("$UsageMessage")
           $xlDriver.Dispose()
           exit 1
        }

        # determine behavior based on arguement type
        switch ($parts[0].ToLower()){
            
            /set {
                # argument should be in the form cellRef=value
                $argument = $parts[1]
                $cell = $argument.Split('=', 2)
                if (($cell | measure-object).count -ne 2)
                {
                    Write-Host("ERROR: Invalid /set argument: $argument`n")
                    Write-Host("$UsageMessage")
                    $xlDriver.Dispose()
                    exit 1
                }
                
                $xlDriver.SetCellValue($cell[0], $cell[1])
                break
            }    
            
            /get {
                $cellRef = $parts[1]
                $cellValue = $xlDriver.GetCellValue($cellRef)
                
                Write-Host("Cell $cellRef = $cellValue`n")
                break
            }
            
            /macro {
                $xlDriver.RunMacro($parts[1])
                break
            }
            
            /wait {
                $parsedWait = $False
               
                $parsedWait = ($parts[1].ToLower() -eq "true")
                
                if((!$parsedWait) -and ($parts[1].ToLower() -ne "false")){
                    Write-Host("Boolean value is invalid in parameter: $Arg")
                    Write-Host("Wait defaulting to false.`n")
                }
                $waitForExit = $parsedWait
                break
            }
            
            # Input and output workbooks handled above
            /input { break }
            /output { break }
            
            default {
                # fail on unknown arguments
                Write-Host("ERROR: Unknown argument: $Arg`n")
                Write-Host("$UsageMessage")
                $xlDriver.Dispose()
                exit 1
            }
        }
    }

    # If all calculations occured successfully and output workbook set, save workbook
    if (($destWorkbooks | measure-object).Count -eq 1)
    {
        # Load interop dll for saving behavior
        $interopDllPath = "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\14.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll"
        $hider = [Reflection.Assembly]::LoadFile($interopDllPath)
        
        foreach($workbook in $destWorkbooks){
            $tmpPath = $workbook.ToString().Substring("/output:".Length)
            $outputWorkbookPath = [IO.Path]::GetFullPath($tmpPath)
        }
        
        $retryCount = 0
	    $execeptionOnSave = $False
        $MAXRETRYCOUNT = 5

        # Attempt to save up to MAXRETRYCOUNT - non-fatal single failures may occur due to environment conditions
	    while($retryCount -lt $MAXRETRYCOUNT)
	    {
		  Try
		  {
        
            $xlDriver.Workbook.SaveAs(  $outputWorkbookPath, 
                                        $xlDriver.Workbook.FileFormat,
                                   	    $null,
                                   	    $null,
                                   	    $false,
                                   	    $false,
                                   	    [Microsoft.Office.Interop.Excel.XlSaveAsAccessMode]::xlNoChange,
                                   		[Microsoft.Office.Interop.Excel.XlSaveConflictResolution]::xlLocalSessionChanges,
                                   		$false,
                                   		$null,
                                   		$null,
                                   		$true)
		  }
		  Catch
		  {
	        $exceptionOnSave = $true
            # Write-Host("Retry save!") - for testing fix
		  }
	
          # Update count if save attempt failed, otherwise break out of retry loop
		  if($exceptionOnSave -eq $True)
		  {
			$exceptionOnSave = $false
			$retryCount = $retryCount + 1
		  }
		  else
		  {
			break
		  }
	   } # Retry loop
       
       # If save is unsucessful, report it and exit.
       if($retryCount -eq $MAXRETRYCOUNT)
       {
          Write-Host("Save to output workbook failed.")
          $xlDriver.Dispose()
          exit 1
       }
    } # Output workbook condition

    # If waiting for Excel process to exit, clean up and wait on Excel process. Otherwise, just dispose.
    if ($waitForExit)
    {
        $excelProc = $xlDriver.ExcelProcess
        
        # Suggested by Excel Dev team as solution preventing Excel process from staying after closing
        $xlDriver = $null
        # Force garbage collection of COM objects. 
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
        # Force garbage collection of COM object members
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()

        $excelProc.WaitForExit()
    }
    else
    {
        $xlDriver.Dispose()
    }
                
} 
catch 
{
    # Exception occured during processing or  
    Write-Host("Unhandled exception: $_")
    $xlDriver.Dispose()
    exit 1
}
   
# No errors occurred. Complete with success code.            
exit 0
