Supercharged pandas: Encrypting Excel Files Written from DataFrames

Introduction

In this article, I will be sharing how to incorporate an ExcelHelper class to open and encrypt the Excel file after writing dataframes to Excel. I've included this Encryption capability in the to_excelp function in my previous article.

Supercharged pandas: Reading from and Writing to Excel

For data scientists and machine learning enthusiasts who are reading this, you are likely to find it helpful as it will speed up your work when exporting dataframes to Excel.

Motivation

In a recent project, I had to analyze data and prepare statistics for a few people. As the data contained sensitive information, there was a need to password-protect the files. This is very much aligned with my skill set and if you have read my previous article, within pypo.py, I already have a to_excelp function which opens the Excel file after it has been created by the df.to_excel() method. While this is worked well for me, it seems like a good time to relook at how it was implemented and also add the capability to password-protect the Excel file.

---

Content

1. Opening and Encrypting the Excel file
2. Generating a strong password
3. Putting it all together

Full code here.

Part 1 – Opening and Encrypting the Excel File

When working with python and Pandas, there are good reasons to open the Excel file, for example, visually inspecting it during testing, or formatting it before sending to stakeholders. If there is an additional need to encrypt the Excel file, then there would be 3 scenarios to handle: open-only, encrypt-only, open & encrypt. If we are neither opening nor encrypting the Excel file, then nothing needs to be done since df.to_excel() would suffice.

ExcelHelper is a class written to launch Excel (the application) and then open the workbook based on the path provided. Programmatically, this is a 2-step process. Most people never realize it because the Excel application and workbook launches together when you double-click an Excel file.

Initialize the ExcelHelper class

• __init__(self, launch=True, encrypt=False, password=None, length=20, quit_=True) This is the initialization call for the ExcelHelper.
• If launch equals to True, the workbook is displayed after encryption is done.
• If encrypt equals to True, the self._encrypt() method, which will be explained later, is called.
• password allows the user to input a preferred password, otherwise it will automatically suggest a strong password with length number of characters, where the maximum length is 255.

Open workbook

• _open_wb(self, path, visible=False) converts the given path to an absolute path and then opens it. Converting a path to an absolute path is necessary otherwise applications dispatched by win32com.client cannot find the file. (Previously, I used a try-except block to prepend the path with the current working directory but that is unnecessarily verbose and takes a bit of time to really understand what one is trying to do.)
• visible controls whether the application is visible to the user. Generally, it makes sense to show the application only after the encryption is completed. So if we are launching and encrypting, we should set visible=True only after self._encrypt() is done.

Encrypt Excel

• _encrypt(self, visible=False) encrypts the Excel workbook and then displays the application by setting the self.xl.Visible attribute after the encryption is completed.
• Setting self.xl.DisplayAlerts to True is important otherwise the launched Excel file will not give any alerts (case in point, if you press Ctrl+F and try to find some gibberish and there would be no prompt

  Tags: Encryption Excel Pandas Pandas Dataframe Python

  Add Fav

Comment