-
Notifications
You must be signed in to change notification settings - Fork 4
/
Convert-ExcelToCsv.ps1
80 lines (69 loc) · 2.48 KB
/
Convert-ExcelToCsv.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<#
.SYNOPSIS
Saves worksheets in an Excel workbook as CSV files
.EXAMPLE
PS> .\Convert-ExcelToCsv.ps1 test.xlsx -filter Status*
Saving sheet Status brandskydd månad (7 rows) to test_Status brandskydd månad.csv
Saves only those sheets whose name matches the given filter
#>
param(
[parameter(mandatory=$true)]
$excelFile,
[parameter(mandatory=$false, parametersetname="filter")]
$filter = "*",
[parameter(mandatory=$false, parametersetname="sheetnames")]
[String[]] $sheetName,
[parameter(mandatory=$false)]
$outputprefix,
[parameter(mandatory=$false)]
[Switch] $noOutput
)
# Need some Win32 functionality to get the PID of the Excel app
Add-Type -TypeDefinition @"
using System;
using System.Runtime.InteropServices;
public static class Win32Api
{
[System.Runtime.InteropServices.DllImportAttribute( "User32.dll", EntryPoint = "GetWindowThreadProcessId" )]
public static extern int GetWindowThreadProcessId ( [System.Runtime.InteropServices.InAttribute()] System.IntPtr hWnd, out int lpdwProcessId );
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
}
"@
$xls = gi $excelFile
$xlCSV = 6
if(! $outputprefix ) {
$outputprefix = $xls.FullName -replace $xls.Extension,""
}
$xl = New-Object -com "Excel.Application"
$excelPid = [IntPtr]::Zero
[Win32Api]::GetWindowThreadProcessId( $xl.HWND, [ref] $excelPid ) | Out-Null
# Workaround for "Old type library..." bug for non-US cultures
$thread = [System.Threading.Thread]::CurrentThread
$currentCulture = $thread.CurrentCulture
$thread.CurrentCulture = New-Object System.Globalization.CultureInfo("en-US")
$xl.Application.Interactive = $false
$wb=$xl.workbooks.open($xls)
$xl.displayalerts=$False
$selectedSheets = $wb.Sheets | ?{ (!$sheetName -and $_.Name -like $filter) -or $sheetName -contains $_.Name }
Write-Host "Selected $($selectedSheets | measure | select -expandproperty Count) sheets"
if( $selectedSheets ) {
$selectedSheets | %{
$csvFile = New-Object psobject -Property @{ `
SheetName = $_.Name; `
RowCount = $_.UsedRange.Rows.Count; `
Path = ("{0}_{1}.csv" -f $outputprefix,$_.Name) `
}
Write-Host "Saving sheet $($csvFile.SheetName) ($($csvFile.RowCount) rows) to $($csvFile.Path)"
$_.SaveAs($csvFile.Path, $xlCSV, $null, $null, $false, $false, $false)
if(!$noOutput) {
$csvFile
}
}
}
# Discard all changes and close the file
$wb.Saved = $true
$wb.close($false)
$xl.quit()
kill -Id $excelPid
$thread.CurrentCulture = $currentCulture