Execute PowerShell manipulation (open and SaveAs with Password) for Excel file on server without Office

0

I'm trying to execute a PowerShell script that operates with Excel File, the script opens the excel and does a SaveAs of the file with a password protection. this works great on a PC with Office, but it doesn't on a server that doesn't have Office installed.

I know that there are some libraries that can be used but I don't know how to use them, here is the code of the PowerShell Script:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $false
$LoadPath = Join-Path -Path $pwd -ChildPath "TEMPLATE4WEEKS.xlsx"    #$pwd is your current working dir
$SavePath = Join-Path -Path $pwd -ChildPath "TEMPLATE4WEEKSprotected.xlsx"
$wb = $excel.Workbooks.Open($LoadPath)


$wb.SaveAs($SavePath,[Type]::Missing,"password")
$excel.Quit()

and I've read something about libraries such as EPPlus (https://archive.codeplex.com/?p=epplus) but I don't know how to use it.

if it worth mentioning, the error I get when trying to execute the PowerShell script on the server is the following:

New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed
due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:1 char:10
+ $excel = New-Object -ComObject Excel.Application
+          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : ResourceUnavailable: (:) [New-Object], COMException
+ FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand

The property 'Visible' cannot be found on this object. Verify that the property exists and can be set.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:2 char:1
+ $excel.Visible = $true
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound

The property 'DisplayAlerts' cannot be found on this object. Verify that         the property exists and can be set.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:3 char:1
+ $excel.DisplayAlerts = $false
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:6 char:1
+ $wb = $excel.Workbooks.Open($LoadPath)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:9 char:1
+ $wb.SaveAs($SavePath,[Type]::Missing,"jacobs4321")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:10 char:1
+ $excel.Quit()
+ ~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
excel
powershell
etl
asked on Stack Overflow Aug 23, 2019 by Baldie47

1 Answer

1

Synthesizing my multiple comments as an answer here:

  1. Download EPPlus: https://www.nuget.org/packages/EPPlus/

  2. Read some of the documentation and check their sample code: https://github.com/JanKallman/EPPlus/wiki/Getting-Started

  3. Then open and save your file like this:

.

Add-Type -Path D:\PathOfExile\epplus.4.5.3.2\lib\net40\EPPlus.dll
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage("C:\Metallica\WhereverIMayRoam\hehe.xlsx")
$ExcelPackage.Workbook.Worksheets.Add("Test")
$ExcelPackage.SaveAs("C:\Metallica\WhereverYOUMayRoam\newfile.xlsx", "hohoho") # This sets the password of the xlsx to hohoho
answered on Stack Overflow Aug 23, 2019 by P-L

User contributions licensed under CC BY-SA 3.0