2

I'm trying to use xlsxwriter to create an xlsx file with a workbook password. I glanced through the documentation and skimmed the source code, but can't seem to find anything about it.

royce3
  • 1,203
  • 1
  • 10
  • 17

4 Answers4

2

The worksheet.protect() only protect the worksheet from being edited. But, we can combine the protect() method with hidden format to hide the data from first view. Only if you have the password can you unlock and unhidden the cells.

Here is my approach:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

test_content = (
    ['Hi', 123],
    ['I',   1123],
    ['AM',  1231231],
    ['LOCKED',1123123],
)

row = 0
col = 0

for text, num in (test_content):
    worksheet.write(row, col,     text)
    worksheet.write(row, col + 1, num)
    worksheet.set_row(row, None, None, {'hidden': True}) #hide the row
    row += 1

worksheet.protect('password') # set the password and protect

workbook.close()
ps2pspgood
  • 61
  • 7
  • Thank you for the suggestion. While this could be useful, I specifically need the workbook password. I need the data to be encrypted, not just hidden. – royce3 Oct 30 '19 at 03:04
2

Found the perfect solution to the problem. Doesn't exactly use xlsxwriter but works flawlessly. Uses pywin32, so sadly limited to windows machines only.

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
#Before saving the file set DisplayAlerts to False to suppress the warning dialog:
excel.DisplayAlerts = False
wb = excel.Workbooks.Open(your_file_name)
# refer https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb214129(v=office.12)?redirectedfrom=MSDN
# FileFormat = 51 is for .xlsx extension
wb.SaveAs(your_file_name, 51, 'your password')                                               
wb.Close() 
excel.Application.Quit()

Here's a link to the original answer.

shantanuskale
  • 99
  • 5
  • I believe this also requires that you're running on Windows and have Microsoft Excel installed. That may not be a viable option if you're generating reports from a Linux box, for example. – royce3 Apr 02 '21 at 19:25
  • Yeah you're right this would only work on Windows with Excel installed. Not compatible with Linux Systems or macs. – shantanuskale Apr 03 '21 at 11:46
1

according to the documentation here

Worksheet level passwords in Excel offer very weak protection. They do not encrypt your data and are very easy to deactivate. Full workbook encryption is not supported by XlsxWriter since it requires a completely different file format and would take several man months to implement.

you can add password to the worksheet,but it is not a strong protected method

worksheet.protect(<password>)
Rajith Thennakoon
  • 3,975
  • 2
  • 14
  • 24
  • I specifically need the Workbook password that prevents opening the document. This is the only password that actually encrypts the data from my understanding. – royce3 Oct 30 '19 at 03:03
  • Thank you for finding the documentation that says it's not supported. I suppose that's the closest thing to an answer I'm going to get for now, thanks. – royce3 Oct 30 '19 at 03:07
  • what is the expected outcome of this? When I am doing it, nothing happens – PV8 Dec 13 '19 at 08:57
0

Are you try setting the password like this:

wb = Workbook()
ws = wb.worksheets[0]
ws.protect('abc123')
Juliano Pacheco
  • 521
  • 1
  • 5
  • 13
  • According to Microsoft "Worksheet level protection is not intended as a security feature. It simply prevents users from modifying locked cells within the worksheet." (https://support.microsoft.com/en-us/office/protection-and-security-in-excel-be0b34db-8cb6-44dd-a673-0b3e3475ac2d) The question above is asking about workbook passwords, but your suggested answer only applies a worksheet password. – royce3 Apr 05 '21 at 07:27