#Convert-DroppedCSVsToExcel
#Alan Kaplan
#5-3-2018

<#
.Synopsis
   Convert text file(s) to Excel XLSX format
.DESCRIPTION
   This will convert CSV or XLX files to Microsoft Excel XLSX formatted files.  
.Parameter csv
Path of CSV file(s) to be converted
.Parameter MaxWidth
The maximum width for a column, default is 150
.Parameter Delete
If this switch is present, the original CSV file will be deleted after the convert has concluded
.EXAMPLE
$env:\userprofile\desktop\myfile.csv | ConvertTo-Excel -Delete
Convert the myfile.csv file on the user's desktop to myfile.xlsx, deleting the original when done
.EXAMPLE
ConvertTo-Excel -csv $env:\userprofile\desktop\myfile.csv -MaxWidth 45
Convert the myfile.csv file on the user's desktop to myfile.xlsx limiting column width to 45, without deleting the original when done
.EXAMPLE
Get-ChildItem $env:\userprofile\desktop\CSVFiles | ExportTo-Excel -delete
Convert the csv files in the CSVFiles folder on the desktop to.xlsx, deleting the original when done
.NOTES
alan dot kaplan at VA dot Gov
version 1.1 5/2/18 better support of pipeline
#>
Function ConvertTo-Excel {
    Param
    (
        [CmdletBinding()]
   
        # CSV are the input files
        [Parameter(Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true, 
            Position = 0)]
        [Alias('FullName', 'Path')]
        [string[]]$CSV,

        [Parameter(Mandatory = $False,
            Position = 1)]
        [int]$MaxWidth = 150,

        [Parameter(Mandatory = $False,
            Position = 2)]
        [switch]$Delete = $false
    )
    Begin {
        # Specify to save in a standard .XSLX format.
        # See: http://msdn.microsoft.com/en-us/library/bb241279.aspx
        $error.Clear()
        $xlOpenXMLType = 51 
        Try {
            $objXL = New-Object -comObject Excel.Application
        }
        Catch {
            Write-Warning "This requires Excel"
        }
    }
    Process {
        Foreach ($file in $CSV) {
            if (!(Test-Path $file)) {
                write-Warning "File not found"
            }
            Else {
                #allows XLS and other extensions
                $f = Get-Item $file
                $outFile = $f.FullName.Replace($f.Extension, ".xlsx")

                #Following is based in large part from the code at
                #https://www.simple-talk.com/sysadmin/powershell/powershell-data-basics-file-based-data/

                $objXL.Visible = $False 
                $oWbk = $objXL.Workbooks.Open($file)
                [int]$objSheetCount = $oWbk.Worksheets.count

                for ($i = 1; $i -le $objSheetCount; $i++) { 
                    $bWrap = $false
                    $objSheet = $oWbk.Worksheets[$i]
                    $objRange = $objSheet.UsedRange
                    $objRange.EntireColumn.AutoFit() | Out-Null
                    [int]$ActiveColCount = $objRange.Columns.Count
                    for ($j = 1; $j -le $ActiveColCount; $j++) {
                        $col = $objSheet.Columns.Item($j)
                        if ($col.columnwidth -gt $maxWidth) { 
                            $col.columnwidth = $maxWidth 
                            ($objSheet.Cells($j).EntireColumn).wraptext = $true 
                            $bWrap = $true
                        }
                    }

                    if ($bWrap) {
                        #Align Top
                        $objRange.verticalalignment = -4160 
                    }
                }
                Try {
                    $oWbk.saveAs($OutFile, $xlOpenXMLType) | Out-Null
                    $oWbk.Saved = $true
                    $oWbk.Close()
                    Write-verbose "Converted $file file to $outfile"
                    # cleanup
                    if ($delete -eq $true) {
                        If (Test-Path -path $file) { Remove-Item -path  $file}
                    }
                }
                Catch {
                    Write-Warning "Skipped $file"
                }
              [System.Runtime.Interopservices.Marshal]::ReleaseComObject($oWbk) | Out-Null
            }
        }
}
        End {
            #make sure Excel closes
            $objXL.Quit()
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objXL) | Out-Null
            [System.GC]::Collect()
            [System.GC]::WaitForPendingFinalizers()
            Write-Debug 'Done'
        }
}

Add-Type -AssemblyName System.Windows.Forms
Add-type -AssemblyName Microsoft.VisualBasic

Function Get-DragAndDrop
{
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$False)]
        [string]
        $formTitle = "Choose File(s)",

        [Parameter(Mandatory=$False)]
        [string]
        $Instructions = "Drag file(s) to box below",
 
        [Parameter(Mandatory=$False)]
        [string]
        $status = "Ready",

        [Parameter(Mandatory=$False)]
        [string]
        $btnTitle = "Continue"
    )

    Add-Type -assemblyname System.Windows.Forms
    Add-Type -assemblyname System.Drawing

    ### Create form ###

    $form = New-Object System.Windows.Forms.Form
    $form.Text = $Formtitle
    $form.Size = '400,320'
    $form.StartPosition = "CenterScreen"
    $form.MinimumSize = $form.Size
    $form.MaximizeBox = $False
    $form.Topmost = $True
    $form.AutoSize = $True

    ### Define controls ###

    $button = New-Object System.Windows.Forms.Button
    $button.Location = '300,230'
    $button.Size = '75,23'
    $button.Width = 12
    $button.AutoSize = $True
    $button.AutoSizeMode.GrowAndShrink
    $button.Text = $btnTitle
    $button.DialogResult = [System.Windows.Forms.DialogResult]::OK

    $label = New-Object Windows.Forms.Label
    $label.Location = '5,5'
    $label.AutoSize = $True
    $label.Text = $instructions

    $listBox = New-Object Windows.Forms.ListBox
    $listBox.Location = '5,25'
    $listBox.Height = 200
    $listBox.Width = 380
    $listBox.IntegralHeight = $False
    $listBox.AllowDrop = $True
    
    $statusBar = New-Object System.Windows.Forms.StatusBar
    $statusBar.Text = $Status

    ### Add controls to form ###

    $form.SuspendLayout()
    $form.Controls.Add($button)
    $form.Controls.Add($label)
    $form.Controls.Add($listBox)
    $form.Controls.Add($statusBar)
    $form.ResumeLayout()

    $form.AcceptButton = $button

    ### Write event handlers ###
    $listBox_DragOver = [System.Windows.Forms.DragEventHandler]{
	if ($_.Data.GetDataPresent([Windows.Forms.DataFormats]::FileDrop)) 
	{
	    $_.Effect = 'Copy'
	}
    Else
	{
	    $_.Effect = 'None'
	}
    }
	
    $listBox_DragDrop = [System.Windows.Forms.DragEventHandler]{
	    foreach ($filename in $_.Data.GetData([Windows.Forms.DataFormats]::FileDrop)) 
        {
		    $listBox.Items.Add($filename)
	    }
    $statusBar.Text = ("List contains $($listBox.Items.Count) items")
    }

    ### Add events to form ###

    $button.Add_Click($button_Click)
    $listBox.Add_DragOver($listBox_DragOver)
    $listBox.Add_DragDrop($listBox_DragDrop)
    #$form.Add_FormClosed($form_FormClosed)

    #### Show form and return result ###
    $dialogResult = $Form.ShowDialog()
    if ($dialogResult -eq [System.Windows.Forms.DialogResult]::OK)
     {
       $form.SuspendLayout()
       [array]$items =  $listbox.Items| sort -CaseSensitive
       if ($items.Count -gt 1){
       [array]$items
       }
       ELSE
       {
       [string]$items[0]
       }
       $Form.Close() | out-null
     }
}


### Script Begins

$files = Get-DragAndDrop -formTitle "Convert CSV Files to Excel" -status "Waiting for Files"
if ($files.Count -eq 0){
    Write-Warning "No CSV files, quitting."
    Exit
}

$MaxColWidth = [Microsoft.VisualBasic.Interaction]::InputBox("Enter maximum width for columns", "Max Column Width", 150)
if ($MaxColWidth -eq ''){Exit}

$retval = [Microsoft.VisualBasic.Interaction]::MsgBox("Delete original files?",'YesNoCancel,defaultbutton2,Question', "Delete Files")

switch ($retval)
{
    'Yes'   {$DeleteFiles = $true}
    'No'    {$DeleteFiles = $false}
    Default {Exit}
}

$files| 
Where-Object {$_} |
ForEach-Object  {
    $file = $_
    Write-Progress $file
    ConvertTo-Excel -csv $file -MaxWidth $MaxColWidth
    #Convert function can do this, too.
    if ($deleteFiles) {Remove-Item $file}
}

Write-Host "Done" -ForegroundColor Green