3

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?

blizz
  • 4,102
  • 6
  • 36
  • 60
  • Are you wanting to encrypt or compress? – mjolinor Oct 28 '13 at 15:04
  • have you tried to use [this](http://stackoverflow.com/questions/17688468/how-to-export-a-csv-to-excel-using-powershell)? – kravasb Oct 28 '13 at 15:10
  • Password protection in Excel is very easy to bypass. If you need *real* protection, you need to use another method, such as Windows' built-in EFS features. http://gallery.technet.microsoft.com/scriptcenter/Create-Encrypted-Folder-6f0fe0c9 – alroc Oct 28 '13 at 15:13
  • I just need encryption. I care less about compression. – blizz Oct 28 '13 at 16:16
  • @Oleksandr - what you provided requires that Excel be installed – blizz Oct 28 '13 at 16:17
  • @blizz If you need encryption, then a password-protected Excel file (which is easily bypassed) will not give you what you're looking for. It's also [possible to crack zipfile passwords](http://security.stackexchange.com/questions/17774/how-to-recover-a-lost-zip-file-password). You need to **actually encrypt** the file(s). – alroc Oct 28 '13 at 17:14

3 Answers3

1

.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.

Jimbo
  • 2,529
  • 19
  • 22
1

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.

Community
  • 1
  • 1
kravasb
  • 696
  • 5
  • 16
1

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")
Community
  • 1
  • 1
Florian Feldhaus
  • 5,567
  • 2
  • 38
  • 46
  • great example, thanks! do you know if I can do the same for an .xls file? I am able to do it for .xlsx. If not then is it possible to do conversion from .xlsx to .xlsx or vice-versa? – Stephanie May 03 '17 at 22:59
  • Hi Stephanie, epplus only supports XLSX because XLS is a proprietary, closed format. I recommend to have a look at [PSExcel](https://github.com/RamblingCookieMonster/PSExcel). It uses epplus, but simplifies handling Excel files. It has the Cmdlets Import-XLSX and Export-XLSX which may achieve what you want with XLSX files. – Florian Feldhaus May 04 '17 at 11:13
  • Thank you for your response. I have downloaded the PSExcel folder and testing with it. Import and export also seems to be importing and exporting to .xlsx. How would it export to .xls? I can't find any examples on it. – Stephanie May 04 '17 at 16:55
  • As already stated, epplus does not support xls as it is a closed format. If you have Excel installed, you may be able to achieve it with https://gist.github.com/gabceb/954418 – Florian Feldhaus May 04 '17 at 17:59
  • 1
    Yeah, I don't have excel on the server and I wouldn't like to work with interop. However, I found this might be possible with ClosedXML. Just wanted to share that with you for your future reference. Thanks! – Stephanie May 04 '17 at 18:09
  • Can you please tell me how to read these excel values into database ? – Shesha Sep 06 '19 at 07:42