Supercharged pandas: Encrypting Excel Files Written from DataFrames | by Ji Wei Liew | Jun, 2023

Initialize the ExcelHelper class

• __init__(self, launch=True, encrypt=False, password=None, size=20, quit_=True) That is the initialization name for the ExcelHelper.
• If launch equals to True, the workbook is displayed after encryption is finished.
• If encrypt equals to True, the self._encrypt() methodology, which can be defined later, known as.
• password permits the person to enter a most well-liked password, in any other case it’s going to mechanically counsel a powerful password with size variety of characters, the place the utmost size is 255.

Open workbook

• _open_wb(self, path, seen=False) converts the given path to an absolute path after which opens it. Changing a path to an absolute path is important in any other case functions dispatched by win32com.shopper can’t discover the file. (Beforehand, I used a try-except block to prepend the trail with the present working listing however that’s unnecessarily verbose and takes a little bit of time to essentially perceive what one is attempting to do.)
• seen controls whether or not the applying is seen to the person. Typically, it is smart to point out the applying solely after the encryption is accomplished. So if we’re launching and encrypting, we should always set seen=True solely after self._encrypt() is finished.

Encrypt Excel

• _encrypt(self, seen=False) encrypts the Excel workbook after which shows the applying by setting the self.xl.Seen attribute after the encryption is accomplished.
• Setting self.xl.DisplayAlerts to True is essential in any other case the launched Excel file is not going to give any alerts (working example, in the event you press Ctrl+F and attempt to discover some gibberish and there can be no immediate 😱; it occurred for me and I used to be actually confused!).

Execute methodology

• execute(self, path, launch, encrypt, quit_) handles the three eventualities described above.
• quit_ argument closes the Excel utility (trailing underscore is a conference to indicate that stop is a reserved key phrase in python). When ExcelHelper is initiated, if launch=False, the Excel utility runs within the background and the Excel file is opened. If the person now double-clicks on the Excel file, he can be prompted that it will possibly solely be open in read-only mode. It’s fairly troublesome for non-technical person to shut the file; the workaround can be to open Process supervisor, choose the Excel program after which Finish Process. Due to this fact, there’s a have to name .Stop() to terminate the Excel utility. We might have simply closed the workbook, however maybe there isn’t any have to deal with this so finely for now.

Initially, I used to be utilizing from cryptography.fernet import Fernet; Fernet.generate_key() to generate random passwords. Whereas a number of customers have been pleasantly stunned by the size and randomness of the password, I didn’t prefer it very a lot as a result of it’s a bit too lengthy and doesn’t comprise a wide range of punctuation. I googled and located a greater approach on StackOverflow. (I’m at all times very impressed by how simply one can get very prime quality solutions on stackoverflow. All of the robust work has already been accomplished by all of the giants, all we have to do is to seek for it, copy, paste, and make small tweaks (e.g. change variable names).) The operate is sort of easy and fairly self-explanatory.

import secrets and techniques
import string
def gen_password(self, size):
char = string.ascii_letters + string.digits + string.punctuation
return ''.be a part of(secrets and techniques.selection(char) for _ in vary(size))

Simply as the whole lot was going a bit too easily, whereas testing my code, I observed that often the passwords can’t be used to open the file! I used to be actually puzzled. It took a little bit of trial-and-error and I started to suspect that there might be some characters which aren’t appropriate as passwords as a result of this phenomenon solely occurred when passwords comprise 2 backslashes .

Right here’s a little bit of background to will let you respect the state of affairs: I take advantage of Powershell and Notepad++, and my code prints the passwords to stdout. Subsequent, I spotlight the password printed on Powershell after which paste it when Excel prompts me to enter the password. So the problem is that is an escape character, therefore the primary needs to be ignored once I enter this because the password. It’s troublesome to deal with and for the aim of passwords, I can do with one much less character. Therefore, all I did was slice out backslash in string.punctuation.

  def _get_password(self, size):
string_punc = string.punctuation[:23] + string.punctuation[24:]
char = string.ascii_letters + string.digits + string_punc
return ''.be a part of(secrets and techniques.selection(char) for _ in vary(size))

Since there may be nearly no value-add to instantiate an ExcelHelper object in case you are not launching or encrypting the Excel file, one ought to begin with if launch or encrypt:. Subsequent, is merely passing the key phrase arguments from to_excelp to ExcelHelper and returning the item and password.

def to_excelp(df, *arg, launch=True, encrypt=False, password=None, **kw):
''' Writes to Excel and opens it'''
filename, *arg = arg
if not filename.endswith(('.xlsx','.xls','.xlsm')):
filename += '.xlsx'
if os.path.isfile(filename):
title, ext = filename.rsplit('.')
filename = f'{title}_{timestr()}.{ext}'
# Default index=False
index = kw.get('index', False)
if not index:
kw['index']=False
df.to_excel(filename, *arg, **kw)
if launch or encrypt:
xl = ExcelHelper(filename, launch=launch, encrypt=encrypt, password=password)
return xl, xl.password
else:
return filename

If you’re writing dataframes to a number of totally different Excel information by calling this operate, I’d suggest to retailer the ends in a listing of tuples. You possibly can subsequently iterate over this record of tuples to get the trail to the Excel information and their passwords. Storing the item may be helpful in future, particularly in the event you intend so as to add extra functionalities to ExcelHelper.

l_xl_pw = []
for df in (df1, df2, df3, df4):
xl, pw = df.to_excelp(launch=False, encrypt=True, password=None)
l_xl_pw.append((xl, pw))
l_path_pass = [[xl.path, pw] for (xl, pw) in l_xl_pw]
df_path_pass = pd.DataFrame(l_path_pass, columns=['Path', 'Pw'])
# df_path_pass may also be written to Excel utilizing .to_excelp(), how elegant! :D

ExcelHelper may also be added to different your present scripts too.

def some_func():
df = pd.read_excel('some_file.xlsx')
# some knowledge manipulation...
df.to_excel('some_file_modified.xlsx')
def some_func(launch=False, encrypt=True, password='5tr0ngP@ssw0rd'):
df = pd.read_excel('some_file.xlsx')
# some knowledge manipulation...
df.to_excel('some_file_modified.xlsx')
if launch or encrypt:
xl = ExcelHelper('some_file_modified.xlsx', launch=launch, encrypt=encrypt, password=password)
return xl, xl.password

Revisiting previous code written on my own is sort of a stroll down reminiscence lane which reveals how little I knew again then. Though I’m very a lot embarrassed by it, I’m glad to know that I’ve progressed since then.

“When you’re not embarrassed by your previous code then you definately aren’t progressing as a programmer.” [Anonymous]

Writing these little courses, capabilities takes time however there are immense advantages for having them because it automates the mechanical and not-so-fun elements of labor and permits one to concentrate on the essential duties. (Think about having to consider passwords with uppercase, lowercase, numbers and punctuation and storing them in a file on a regular basis.)