Reputation: 4168
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?
Upvotes: 3
Views: 7748
Reputation: 5932
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")
Upvotes: 1
Reputation: 706
You can try to (if you really need use PowerShell):
Note! That this approach is hard one. You should expect strange behavior and plenty of security issues depending on assembly you choose to create excel files.
As for me, the best option for you is to create a simple console application in c#, which will use the assemblies above. Then launch it on a target machine every time you need.
P.S. I've tried to create PowerShell script for your issue with no success. Unfortunatelly, PowerShell works unpredictably with external libraries.
Upvotes: 1
Reputation: 2537
.net 4.5 now includes complete zip compression with passwords. So once you install 4.5 you should be able to access this library from powershell without any other dependencies.
Upvotes: 1