<#
***********************************************************************************************************
This functionmodule contains the heading and body line CSV writing functions for Voyage files.  I'm putting 
this into a module to avoid having to maintain code in multiple places.
Version:    1.0
Author:     Ira Babb
Date:       06-15-2013
***********************************************************************************************************
#>
###########################################################################################################

#############################################################################################################################

Function ExcelTranslate ($Column,$Row)
<#
***********************************************************************************************************
v2.0
This function is passed a column and a row and essentially translates them from numeric or alpha values 
into Excel row/column values.

Example 1:
ExcelTranslate 2 15
Returns: B15

Example 2:
ExcelTranslate H 9
Returns: H9

This function is useful when you have changing numeric data but need to reference specific columns in 
alpha Excel format.

***********************************************************************************************************
#>
{
	#If this is an integer we'll process it.  If not we'll assume it's alphanumeric and move on
	if ($Column -is [int])
    {
		#Divide the column by 26 in order to put it in a base 10 context
		$LetterCheck = $Column/26
		#Modulus divide that number by 2 to see if we have a whole number
		$WholeCheck = $LetterCheck%1
		#if we're further out than 26 columns (i.e. Lettercheck is >1) we need to process slightly differently because we have multiple alpha values (i.e. AZ3 rather than A3)
		if ($LetterCheck -gt 1)
		{
			#If WholeCheck is >0 we know we have a decimal number and we need to get the whole and decimal components of that number
			if ($WholeCheck -gt 0)
			{
				#Convert to string
				$LetterCheck = [string]$LetterCheck
				#Split on the decimal point
				$WholeNumber,$DecimalNumber = ([string]$LetterCheck).Split("{.}")
				#Add back the decimal point to the decimal number
				$DecimalNumber="."+$DecimalNumber
				#Convert to integer
				$WholeNumber = [int]$WholeNumber
				#Multiply the decimal number by 26 and convert to integer
				$DecimalNumber = [int]([decimal]$DecimalNumber*26)
			}
			else
			{
				#Divide the column by 26 and get the whole number  
				$WholeNumber = ([math]::floor([int] $Column / [int] 26))-1
				#Get the decimal remainder
				$DecimalNumber = $WholeCheck
			}
		}
		else 
		{
			#If we're <1 we're in the first 26 characters and the whole number is not relevant but needs to be statically defined
			$WholeNumber = 0
			$DecimalNumber = $Column
		}
		#Based on whether we're >26 we'll need a two-level alpha column (i.e. AD vs A).  If this is zero then we only need a single letter.
		switch ($WholeNumber)
		{
			0 {$ColumnProcessed=""}
			1 {$ColumnProcessed="A"}
			2 {$ColumnProcessed="B"}
			3 {$ColumnProcessed="C"}
			4 {$ColumnProcessed="D"}
			5 {$ColumnProcessed="E"}
			6 {$ColumnProcessed="F"}
			7 {$ColumnProcessed="G"}
			8 {$ColumnProcessed="H"}
			9 {$ColumnProcessed="I"}
			10 {$ColumnProcessed="J"}
			11 {$ColumnProcessed="K"}
			12 {$ColumnProcessed="L"}
			13 {$ColumnProcessed="M"}
			14 {$ColumnProcessed="N"}
			15 {$ColumnProcessed="O"}
			16 {$ColumnProcessed="P"}
			17 {$ColumnProcessed="Q"}
			18 {$ColumnProcessed="R"}
			19 {$ColumnProcessed="S"}
			20 {$ColumnProcessed="T"}
			21 {$ColumnProcessed="U"}
			22 {$ColumnProcessed="V"}
			23 {$ColumnProcessed="W"}
			24 {$ColumnProcessed="X"}
			25 {$ColumnProcessed="Y"}
			26 {$ColumnProcessed="Z"}
		}
		switch ($DecimalNumber)
		{
			1 {$ColumnProcessed+="A"}
			2 {$ColumnProcessed+="B"}
			3 {$ColumnProcessed+="C"}
			4 {$ColumnProcessed+="D"}
			5 {$ColumnProcessed+="E"}
			6 {$ColumnProcessed+="F"}
			7 {$ColumnProcessed+="G"}
			8 {$ColumnProcessed+="H"}
			9 {$ColumnProcessed+="I"}
			10 {$ColumnProcessed+="J"}
			11 {$ColumnProcessed+="K"}
			12 {$ColumnProcessed+="L"}
			13 {$ColumnProcessed+="M"}
			14 {$ColumnProcessed+="N"}
			15 {$ColumnProcessed+="O"}
			16 {$ColumnProcessed+="P"}
			17 {$ColumnProcessed+="Q"}
			18 {$ColumnProcessed+="R"}
			19 {$ColumnProcessed+="S"}
			20 {$ColumnProcessed+="T"}
			21 {$ColumnProcessed+="U"}
			22 {$ColumnProcessed+="V"}
			23 {$ColumnProcessed+="W"}
			24 {$ColumnProcessed+="X"}
			25 {$ColumnProcessed+="Y"}
			26 {$ColumnProcessed+="Z"}
			0 {$ColumnProcessed+="Z"}
		}
	}
	else
	{
		$ColumnProcessed = $Column
	}
	$ColumnRow = $ColumnProcessed+[string]$Row
	Return $ColumnRow
}

#############################################################################################################################

Function MakeTable ($TableName,$BeginColumn,$BeginRow,$EndColumn,$EndRow,$ExcelSheet,$ExcelObject)
{
	$BeginRange = ExcelTranslate $BeginColumn $BeginRow
	$EndRange = ExcelTranslate $EndColumn $EndRow
	
	$Range=$ExcelSheet.Range("${BeginRange}:$EndRange")
	$Range.Activate() |out-null
	$ListObject = $ExcelObject.ActiveSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Range, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)
	$ListObject.Name = "$TableName"
	$ListObject.TableStyle = "TableStyleMedium13"
}

#############################################################################################################################

Function GoHome ($ExcelSheet)
{
	$Range=$ExcelSheet.Range("A1:A1")
	$Range.Activate() |out-null					
}

#############################################################################################################################