###############################################################################
# This is a script developed by trial and error to load three CSV files
# into three tabs of a single spreadsheet file.  
# This is a sanitized part of a project to recover some payments reports
# from data produced by three SQL queries that put their results into
# three CSV files.  Parts of this script were borrowed from the internet. 
###############################################################################

param ($BATCHID) 

# Three csv files produced by three SQL queried
$FILE1 = "I:\IS_Apps_MyReports\PaymentDetail-$BATCHID.csv"
$FILE2 = "I:\IS_Apps_MyReports\PaymentDistribution-$BATCHID.csv"
$FILE3 = "I:\IS_Apps_MyReports\PaymentPosting-$BATCHID.csv"

# The final spreadsheet file we will create
$OUTFILE = "I:\IS_Apps_MyReports\PaymentRecoveryReports-$BATCHID.xlsx"

# Create a Excel Workspace
$excel = New-Object -ComObject Excel.Application

# make excel visible
$excel.visible = $true

# add a new blank worksheet
$workbook = $excel.Workbooks.add()

# Adding Sheets
$sheet4 = $workbook.Sheets.add()
$sheet4.name = "PaymentDetail"
$sheet5 = $workbook.Sheets.add()
$sheet5.name = "PaymentDistribution"
$sheet6 = $workbook.Sheets.add()
$sheet6.name = "PaymentPosting"

# The default workbook has three sheets, remove them
($s1 = $workbook.sheets | where {$_.name -eq "Sheet1"}).delete() 

#Start row and column
$r = 1
$c = 1
#Begin working through file 1
$file = (GC $FILE1)
ForEach ($f in $file) {
  $arr = ($f).split(',')
  ForEach ($a in $arr) {
    $sheet4.Cells.Item($r,$c) = "$(($a).replace('"',''))"
    $c++
    }
  $c = 1
  $r++    
  }    
#Select all used cells
$range = $sheet4.UsedRange
#Autofit the columns
$range.EntireColumn.Autofit() | out-null 

#Start row and column
$r = 1
$c = 1
#Begin working through file 2
$file = (GC $FILE2)
ForEach ($f in $file) {
  $arr = ($f).split(',')
  ForEach ($a in $arr) {
    $sheet5.Cells.Item($r,$c) = "$(($a).replace('"',''))"
    $c++
    }
  $c = 1
  $r++    
  }    
#Select all used cells
$range = $sheet5.UsedRange
#Autofit the columns
$range.EntireColumn.Autofit() | out-null 

#Start row and column
$r = 1
$c = 1
#Begin working through file 3
$file = (GC $FILE3)
ForEach ($f in $file) {
  $arr = ($f).split(',')
  ForEach ($a in $arr) {
    $sheet6.Cells.Item($r,$c) = "$(($a).replace('"',''))"
    $c++
    }
  $c = 1
  $r++    
  }    
#Select all used cells
$range = $sheet6.UsedRange
#Autofit the columns
$range.EntireColumn.Autofit() | out-null 

#Saving File
$workbook.SaveAs($OUTFILE)

#Close Excel
#$excel.quit()