Unable to lock columns in an excel sheet - xlsxwriter

Question

I am trying to lock only a few columns of a excel sheet using xlsx writer. The writer has already generated worksheets from taking panda dataframes and converting them using to_excel.

When using the simple lock and protect of xlsxwriter (found here, the whole sheet ends up locked instead of the specific cells from the columns I've selected. Here is my code:

writer = pd.ExcelWriter('workbook.xlsx', engine='xlsxwriter')
df2.to_excel(writer, 'Except', index=False, header=False)
df1.to_excel(writer, 'Rec', index=False, header=False)
workbook = writer.book
rec_sheet = writer.sheets['Rec']
except_sheet = writer.sheets['Except']

# adding lock/unlock capabilities
locked_format = workbook.add_format()
locked_format.set_locked(True)  
unlocked_format= workbook.add_format()
unlocked_format.set_locked(False)

# unlocking columns that need to remain unlocked
rec_sheet.set_column('AC:AD', None, unlocked_format)
except_sheet.set_column('AC:AD', None, unlocked_format)

# Enable worksheet protection
except_sheet.protect()
rec_sheet.protect()
#lock columns that need to remain locked
rec_sheet.set_column('A:AB', None, locked_format)
except_sheet.set_column('A:AB', None, locked_format)

I expect the final workbook.xlsx to have two sheets that have columns locked up until column AC.

Answer 1

It should work. I added some sample dataframes to your example and I was able to write to cell AC3 (but not AB3):

import pandas as pd

df1 = pd.DataFrame([10, 20, 30, 20, 15])
df2 = pd.DataFrame([10, 20, 30, 20, 15])

writer = pd.ExcelWriter('workbook.xlsx', engine='xlsxwriter')
df2.to_excel(writer, 'Except', index=False, header=False)
df1.to_excel(writer, 'Rec', index=False, header=False)
workbook = writer.book
rec_sheet = writer.sheets['Rec']
except_sheet = writer.sheets['Except']

# Adding lock/unlock capabilities.
locked_format = workbook.add_format()
locked_format.set_locked(True)  
unlocked_format= workbook.add_format()
unlocked_format.set_locked(False)

# Unlocking columns that need to remain unlocked.
rec_sheet.set_column('AC:AD', None, unlocked_format)
except_sheet.set_column('AC:AD', None, unlocked_format)

# Enable worksheet protection.
except_sheet.protect()
rec_sheet.protect()

# Lock columns that need to remain locked.
rec_sheet.set_column('A:AB', None, locked_format)
except_sheet.set_column('A:AB', None, locked_format)

writer.save()

Output: