Last Updated on March 12, 2023 by Jay
This tutorial will show you how to use Python to set the Sensitivity Label in Excel.
My company recently added a requirement for Excel, and for every spreadsheet we open, we have to add a “Sensitivity Label” for data protection purposes. What’s even worse, is that we can’t edit the Excel file unless we add the Sensitivity Label. This is such an annoying requirement and causes problems for some of my automation processes.
If you have a similar experience, I’m here to help! I’ll show you how to use Python to automatically add a Sensitivity Label in Excel, then make the file editable by Python.
For this demo, we’ll use the xlwings Python library, which is one of the best libraries for Excel automation. One of the key features of xlwings library is the ability to access the native pywin32 object, which is very important for our task. To install xlwings, type the following in the command prompt.
pip install xlwings
You might have heard about an “xlwings add-in”. Don’t worry about that as we won’t be using it.
Quick Intro to xlwings
The following sample code will open an Excel spreadsheet, enter “hello world” then save and close the spreadsheet. It’s a simple code but very effective in helping you understand how to work with xlwings.
import xlwings as xw #wb = xw.Book() ## this will open a new and blank Excel spreadsheet wb = xw.Book('file1.xlsx') ## this will open an existing Excel file ws = wb.sheets ws.range(2,2).value = 'hello world' ## write 'hello world' in cell B2 (row 2, col 2) wb.save() wb.close()
As shown in the above sample code, we can use the xlwings API to control an Excel file to do a lot of things. However, it has limitations, and not everything in Excel is accessible using just xlwing’s API. This is when the pywin32 library comes to the rescue.
Pywin32 Library (API)
Pywin32 is also a Python library that provides access to many of the Windows APIs from Python. It means that we can use Python to control a lot of things in Windows using Pywin32. On the other hand, xlwings is a wrapper of Pywin32. xlwings is more user-friendly whereas Pywin32 is not. But Pywin32 is more powerful than xlwings.
To access the native pywin32 API through xlwings, we need to call:
Finding The Sensitivity Label Info
Finding the Sensitivity Label info is a bit tricky because each company would probably use different labels. You will need to test an Excel file created by your company’s computer.
You’ll need the following code to find your company’s Excel Sensitivity Label info. According to Microsoft, the following attributes are required, so we just need to find the following values:
The AssignmentMethod is easy, according to documentation, just set it to 2.
Justification also doesn’t matter too much. Set it to a string.
LabelId and LabelName are important, and these will vary by company. To find what they are for your company:
import xlwings as xw wb = xw.Book('a_file_from_your_company.xlsx') >> labelinfo = wb.api.ActiveDocument.SensitivityLabel >> labelinfo.LabelId '123123123-123123123-ajskldfj123' >> labelinfo.LabelName 'Public'
As shown, the labelinfo.LabelId will contain a unique string that your company uses for the Label Id. I made up the string in the above example for illustration only, you’ll need to find out your own company’s label ID.
For the labelinfo.LabelName, just open any Excel and check what names are available. For my company, there are four levels: Public, Internal, Confidential, and Restricted. I usually go with Confidential as I don’t really share files with external parties.
Set The Sensitivity Label Info
Now you know your company LabelId and LabelName, let’s set them up using Python! This time, instead of extracting the Sensitivity Label information, we need to create one by using the CreateLabelInfo() function.
After creating the label info, we can set the four required values, then call SetLabel() to apply the Label information.
labelinfo = wb.api.SensitivityLabel.CreateLabelInfo() labelinfo.AssignmentMethod = 2 labelinfo.Justification = 'init' labelinfo.LabelId = '123123123-123123123-ajskldfj123' labelinfo.LabelName = 'Confidential' wb.api.SensitivityLabel.SetLabel(labelinfo, labelinfo)
After executing the above code, we should see the “ADD SENSITIVITY LABEL” disappear and our Excel is now editable!