Powershell - 将CSV转换为XLS而不安装Excel [英] Powershell - Convert CSV to XLS without Excel installed

问题描述

我有一台服务器自动生成报告。报告采用CSV格式。我需要能够直接加密文件，无需第三方压缩（无WinZIP或WinRAR）。

我认为最好的想法是将CSV转换为XLS，然后通过Powershell将密码保护为XLS文件。不幸的是，我没有在服务器上安装Office，而且我发现用于转换文件的这些示例需要安装Excel。

有没有人知道在没有安装Excel的情况下，将PowerShell转换为XLS的方法？或者如果没有，你能想到一个更好的方法来密码保护CSV文件而不压缩到ZIP或RAR？

解决方案

我已经提供了一个加载CSV文件并将其导出到Excel而不安装Excel的示例此处。由于此问题具体要求保护Excel文件，因此我创建了一个使用 EPPlus 保护选项的示例。请参阅我的原始答案从CSV中加载数据，并了解如何设置EPPlus。

 ＃加载EPPlus 
 $ DLLPath =C： \Windows\System32\WindowsPowerShell\v1.0\Modules\EPPlus\EPPlus.dll
 [Reflection.Assembly] :: LoadFile（$ DLLPath）| Out-Null 
 
＃创建Excel文件
 $ ExcelPackage = New-Object OfficeOpenXml.ExcelPackage 
 $ Worksheet = $ ExcelPackage.Workbook.Worksheets.Add（Protected）
 
＃加密
 $ ExcelPackage.Encryption.Algorithm = [OfficeOpenXml.EncryptionAlgorithm] :: AES256 
 $ ExcelPackage.Encryption.IsEncrypted = $ true 
 $ ExcelPackage.Encryption.Password ='Excel'
 
＃保护工作簿
 $ ExcelPackage.Workbook.Protection.LockRevision = $ true 
 $ ExcelPackage.Workbook.Protection.LockStructure = $ true 
 $ ExcelPackage.Workbook.Protection.LockWindows = $ true 
 $ ExcelPackage.Workbook.Protection.SetPassword（Workbook）
 
 $ ExcelPackage.Workbook.View.SetWindowSize（150,525,14500 ，6000）
 $ ExcelPackage.Workbook.View.ShowHorizo​​ntalScrollBar = $ false 
 $ ExcelPackage.Workbook.View.ShowVerticalScrollBar = $ false 
 $ ExcelPackage.Workbook.View.ShowSheetTabs = $ false 
 
＃保护工作表
 $ Worksheet.Protection.AllowAutoFilt er = $ false 
 $ Worksheet.Protection.AllowDeleteColumns = $ false 
 $ Worksheet.Protection.AllowDeleteRows = $ false 
 $ Worksheet.Protection.AllowEditObject = $ false 
 $ Worksheet。 Protection.AllowEditScenarios = $ false 
 $ Worksheet.Protection.AllowFormatCells = $ false 
 $ Worksheet.Protection.AllowFormatColumns = $ false 
 $ Worksheet.Protection.AllowFormatRows = $ false 
 $ Worksheet.Protection.AllowInsertColumns = $ false 
 $ Worksheet.Protection.AllowInsertHyperlinks = $ false 
 $ Worksheet.Protection.AllowInsertRows = $ false 
 $ Worksheet.Protection.AllowPivotTables = $ false 
 $ Worksheet.Protection.AllowSelectLockedCells = $ false 
 $ Worksheet.Protection.AllowSelectUnlockedCells = $ false 
 $ Worksheet.Protection.AllowSort = $ false 
 $ Worksheet.Protection.IsProtected = $ true 
 $ Worksheet.Protection.SetPassword（Worksheet）
 
＃保存Excel文件
 $ ExcelPackage.SaveAs（$ HOME\Downloads\test.xlsx）
  

I have a server generating reports automatically. The reports are in CSV format. I need to be able to encrypt the file directly, without third party compression (no WinZIP or WinRAR).

I thought the best idea would be to convert the CSV to XLS and then password protect the XLS file, all through Powershell. Unfortunately, I do not have Office installed on the server and all the examples I have found for converting a file this way require that Excel be installed.

Does anyone know of a way to convert CSV to XLS in Powershell without having Excel installed? Or if not, can you think of a better way to password protect the CSV file without compressing it to ZIP or RAR?

解决方案

I already provided an example for loading a CSV file and exporting it to Excel without having Excel installed here. As this question specifically asked for protection of Excel files, I created an example for using the EPPlus protection options. Refer to my original answer for loading the data from CSV and for details how to setup EPPlus.

# Load EPPlus
$DLLPath = "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\EPPlus\EPPlus.dll"
[Reflection.Assembly]::LoadFile($DLLPath) | Out-Null

# Create Excel File
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage 
$Worksheet = $ExcelPackage.Workbook.Worksheets.Add("Protected")

# Encryption
$ExcelPackage.Encryption.Algorithm = [OfficeOpenXml.EncryptionAlgorithm]::AES256
$ExcelPackage.Encryption.IsEncrypted = $true
$ExcelPackage.Encryption.Password = 'Excel'

# Protection of Workbook
$ExcelPackage.Workbook.Protection.LockRevision = $true
$ExcelPackage.Workbook.Protection.LockStructure = $true
$ExcelPackage.Workbook.Protection.LockWindows = $true
$ExcelPackage.Workbook.Protection.SetPassword("Workbook")

$ExcelPackage.Workbook.View.SetWindowSize(150, 525, 14500, 6000)
$ExcelPackage.Workbook.View.ShowHorizontalScrollBar = $false
$ExcelPackage.Workbook.View.ShowVerticalScrollBar = $false
$ExcelPackage.Workbook.View.ShowSheetTabs = $false

# Protection of Worksheet
$Worksheet.Protection.AllowAutoFilter = $false
$Worksheet.Protection.AllowDeleteColumns = $false
$Worksheet.Protection.AllowDeleteRows = $false
$Worksheet.Protection.AllowEditObject = $false
$Worksheet.Protection.AllowEditScenarios = $false
$Worksheet.Protection.AllowFormatCells = $false
$Worksheet.Protection.AllowFormatColumns = $false
$Worksheet.Protection.AllowFormatRows = $false
$Worksheet.Protection.AllowInsertColumns = $false
$Worksheet.Protection.AllowInsertHyperlinks = $false
$Worksheet.Protection.AllowInsertRows = $false
$Worksheet.Protection.AllowPivotTables = $false
$Worksheet.Protection.AllowSelectLockedCells = $false
$Worksheet.Protection.AllowSelectUnlockedCells = $false
$Worksheet.Protection.AllowSort = $false
$Worksheet.Protection.IsProtected = $true
$Worksheet.Protection.SetPassword("Worksheet")

# Save Excel File
$ExcelPackage.SaveAs("$HOME\Downloads\test.xlsx") 

这篇关于Powershell - 将CSV转换为XLS而不安装Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！