python - 无法锁定 Excel 工作表中的列 - xlsxwriter

Question

我正在尝试使用 xlsx 编写器仅锁定 excel 工作表的几列。作者已经通过获取 panda 数据帧并使用 to_excel 转换它们生成了工作表。

当使用 xlsxwriter 的简单锁定和保护时（在此处找到，整个工作表最终被锁定，而不是我选择的列中的特定单元格。这是我的代码：

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)

我希望最终的 workbook.xlsx 有两张表，它们的列被锁定到 AC 列。

标签： pythonpython-3.xpandasxlsxwriter

Answer 1

它应该工作。我在您的示例中添加了一些示例数据框，并且能够写入单元格 AC3（但不是 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()

输出：