xlsxwriter: protection unhiding

user_5913411 0
Viewed 137

UPDATE: Fixed. Sheet protected, selected column is possible to unhide.

Is it possible to lock all visible cells (protection from editing), but still allow the users to unhide certain columns?

I would like to export a pandas dataframe via pd.excel_writer().

worksheet.protect() doesn't allow for any arguments? I apply column-wise formatting and tried 'lock': False on the hidden columns, but that didn't work.

# init 
import pandas as pd
import random as rd
import string
random.seed(10)
S = 10 
string_col = []
number_col1 = [] # protect 
number_col2 = [] # hide 

# create testdata
for i in range(0, 20): 
    ran_str = ''.join(rd.choices(string.ascii_uppercase + string.digits, k = S))
    ran_num1 = ''.join(str(rd.randrange(S)))
    ran_num2 = ''.join(str(rd.randrange(S)))
    string_col.append(ran_str) 
    number_col1.append(ran_num1) 
    number_col2.append(ran_num2) 

testframe = pd.DataFrame(
    {'String_col': string_col,
     'Hide': number_col1,
     'Protect': number_col2
    })


# helperfunction for selecting columns 
def getColnameByPosition(pos):
    from string import ascii_uppercase   
    colnames = list(ascii_uppercase)
    for col in ascii_uppercase:
        for col2 in ascii_uppercase:
            colnames.append(col+col2)    
    return colnames[pos]

# export 
writer = pd.ExcelWriter("./FormatTest.xlsx", engine='xlsxwriter')
testframe.to_excel(writer, sheet_name="Sheet0", startrow=0, header=True, index=False)
workbook  = writer.book
worksheet = writer.sheets["Sheet0"]       

# protect and hide format 
format_protect =  workbook.add_format({'bg_color': '#ADD8E6','font_name': 'Calibri', 'font_size': 9, 'num_format': '#,##0', "locked": True})
format_hide =  workbook.add_format({'bg_color': '#FFFFFF', 'font_name': 'Calibri', 'font_size': 9, 'num_format': '#,##0', "locked": False})

prot_col = getColnameByPosition(testframe.columns.get_loc("Protect"))
hide_col = getColnameByPosition(testframe.columns.get_loc("Hide"))

    worksheet.set_column(prot_col+':'+prot_col, 10,  format_protect)
worksheet.set_column(hide_col+':'+hide_col, 10,  format_hide, {'hidden': True})

# FIX: allow for unhiding. 
worksheet.protect('', {'format_columns': True}) 

writer.save()
xlsxwriter protection python
1 Answers

You just need to set protect() to turn on worksheet protection but turn on the exceptions that you want. In this case turning on "Format Columns" should get you what you want:

# ...

worksheet.set_column(prot_col+':'+prot_col, 10,  format_protect)
worksheet.set_column(hide_col+':'+hide_col, 10,  format_hide, {'hidden': True})

worksheet.protect('', {'format_columns': True})

writer.save()

Also, set_column() can take (col, col) notation so you could drop the getColnameByPosition() code and just do this:

# ...

prot_col = testframe.columns.get_loc("Protect")
hide_col = testframe.columns.get_loc("Hide")

worksheet.set_column(prot_col, prot_col, 10, format_protect)
worksheet.set_column(hide_col, hide_col, 10, format_hide, {'hidden': True})

worksheet.protect('', {'format_columns': True})

writer.save()
user_102380
Related