Support the Import of Encrypted Excel Files

[robinmalik]

This code appears to work for me (assuming a plain text [string] parameter added to the Import-Excel function).

if($Password)
{
	$xl = New-Object -TypeName OfficeOpenXml.ExcelPackage
	try
	{
		$xl.Load($Stream,$Password)
	}
	catch
	{
		$stream.Close()
		$stream.Dispose()
		$xl.Dispose()
		$xl = $null
		throw $_
	}
}
else
{
	$xl = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $stream
}

In the event that a bad password is specified, we get the error: Exception calling "Load" with "2" argument(s): "Invalid password".

Note: This is not the same as a password "protected" workbook or worksheet.

[dfinke]

@robinmalik Could you post a sample xlsx that is protected this way and let me know the password and steps to "unlock" it. Thanks.

May be a clue

http://epplus.codeplex.com/workitem/list/basic?field=CreationDate&direction=Descending&issuesToDisplay=Open&keywords=password&emailSubscribedItemsOnly=false

[robinmalik]

This file has the ever so secure password of 'password': blah4.xlsx

Opening in Excel 2016 I'm prompted twice for a password. The above code seems to open the spreadsheet and import the workbook and worksheet data ok.

[SQLUnderworld]

The request for this support is a great idea! Thank you @robinmalik !

[DarkLite1]

This is indeed a great request! I'll have a look when I have some time to add this feature to the newly updated Import-Excel. Thank you for the example. I'll use it for the test.

[DarkLite1]

Fixed in #227
@dfinke this can be closed when the merge is successful