CODEWITHSUNDEEP
.netexcelpowershellcsv
blizz
blizz

Reputation: 4168

Powershell - Convert CSV to XLS without Excel installed

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

Answers (3)

Florian Feldhaus
Florian Feldhaus

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

kravasb
kravasb

Reputation: 706

You can try to (if you really need use PowerShell):

  1. Download external .NET library which does not require Excel installed, like this one, or this
  2. Load it into your PowerShell session.
  3. Try to use types defined in that dll in your code.

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

Jimbo
Jimbo

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

Related Questions