search

dfinke / ImportExcel

PowerShell module to import/export Excel spreadsheets, without Excel
https://www.powershellgallery.com/packages/ImportExcel/
Apache License 2.0
2.47k stars 400 forks source link

Accessing redirected known folder fails in some situations #1060

Closed chuckchamberland closed 3 years ago

chuckchamberland commented 3 years ago

When trying to apply worksheet protection to a spreadsheet, protection will fail to be enabled if the target file resides in a redirected known folder (such as the Desktop). This is a common scenario now that the OneDrive desktop client can enable known folder redirection without any additional configuration. Example below:

#OneDrive Desktop folder backup is enabled - "Desktop" redirects to a subfolder within OneDrive
#This path works
$path = "C:\Users\username\OneDrive - OrgName\Desktop\workbook.xlsx"

#This path doesn't
$path =  "C:\Users\username\Desktop\workbook.xlsx"

$xls = Open-ExcelPackage -path $path
$sheet = $xls.Workbook.Worksheets['Sheet1']
$sheet.Protection.IsProtected = $true
Set-ExcelRange -Range $sheet.cells["G:G"] -Locked:$false
$sheet.Protection.SetPassword("test")

Close-ExcelPackage $xls

This is a silent failure, so I can't determine at what point access to the file is failing. Running this script line-by-line shows that the excel package appears to be correctly retrieved ($xls). Therefore I suspect a problem with either $sheet.Protection or Close-Excel.

dfinke commented 3 years ago

Close-ExcelPackage has a -Password parameter, but that is for the book. Sanity check would be to see if that fails too.

Sorry, I've never tried the $sheet.Protection.IsProtected or $sheet.Protection.SetPassword("test"), the are part of the 3rd party DLL I use in the module (called EPPlus) that saves the file and gives access to those properties. Could be an error in there, or it could be OneDrive.

chuckchamberland commented 3 years ago

Tangential question: How do you enable protection without setting the password?

Running Close-ExcelPackage $xls -Password "test2" also does not work with C:\User\Username\Desktop, but does with the redirected path.

My guess is it's an issue with the way that EPPlus handles saving the package. What version is used in ImportExcel?

dfinke commented 3 years ago

EPPLus v 4.5.3

Have not played with the protection aspects of the library.

stale[bot] commented 3 years ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.