在没有 Office 的服务器上对 Excel 文件执行 PowerShell 操作（打开和使用密码另存为）

秃头47

我正在尝试执行一个使用 Excel 文件运行的 PowerShell 脚本，该脚本打开 excel 并使用密码保护对文件进行 SaveAs。这在装有 Office 的 PC 上效果很好，但在没有安装 Office 的服务器上就不行。

我知道有一些库可以使用，但我不知道如何使用它们，这里是 PowerShell 脚本的代码：

$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()

我已经阅读了一些关于诸如 EPPlus ( https://archive.codeplex.com/?p=epplus ) 之类的库的内容，但我不知道如何使用它。

如果值得一提，我在尝试在服务器上执行 PowerShell 脚本时遇到的错误如下：

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

在这里综合我的多条评论作为答案：

下载 EPPlus：https ://www.nuget.org/packages/EPPlus/
阅读一些文档并检查他们的示例代码：https : //github.com/JanKallman/EPPlus/wiki/Getting-Started
然后像这样打开并保存您的文件：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。