Export-ExcelCSV.ps1 #powershell #excel

Export-ExcelCSV.ps1

# Author: Miodrag Milic <[email protected]>
# Last Change: 12-Feb-2016.

param(
    # Path to Excel file
    [string] $Path
)

if (!(Test-Path $Path)) { throw Path not found: $Path }
ps excel -ea 0| kill     

$Path = Resolve-Path $Path
$excel = New-Object -COM "Excel.Application"
if (!($excel)) {throw "Can not create Excel COM object" }

$workbook = $excel.Workbooks.Open($Path)
$worksheets = $workbook.Worksheets

$base_name = $path -replace '.xlsx$'
$worksheets | % {
    $sheet = $_
    $csv_name = $base_name + '_' + $sheet.name + '.csv'
    if (Test-Path $csv_name) {  rm $csv_name }
    $sheet.SaveAs($csv_name, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlUnicodeText) ; # xlCSVWindows, xlCSV, xlUnicodeText
}

$workbook.Saved = $true
$workbook.close()
$excel.quit()
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) > $null
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) > $null

ps excel | kill     #for some reason Excel stays
ls "$(Split-Path $Path)\*.csv" | % { (Get-Content $_) -replace '\t',',' | Set-Content $_ -Encoding utf8 }

#bug: https://support.microsoft.com/en-us/kb/320369
#$ci = [System.Globalization.CultureInfo]'en-US'
#$workbook = $excel.Workbooks.PSBase.GetType().InvokeMember('Open', [Reflection.BindingFlags]::InvokeMethod, $null, $excel.Workbooks, $Path, $ci)
#[void]$workbook.PSBase.GetType().InvokeMember( 'SaveAs', [Reflection.BindingFlags]::InvokeMethod, $null, $workbook, ($res, $fmt), $ci)
#[void]$workbook.PSBase.GetType().InvokeMember( 'Close', [Reflection.BindingFlags]::InvokeMethod, $null, $workbook, 0, $ci)