XlsxWriter:仅锁定特定单元格

我正在使用 xlsxwriter 创建 xlsx 文件,并希望保护特定的单元格 (例如范围 B2:B20 中的所有单元格)。文档说您可以使用worksheet.protect()方法-默认情况下打开整个工作表的保护-然后您可以使用workbook.add_format({'locked':0})来解锁特定的单元格。但我希望反之亦然-我想只锁定特定范围的单元格,并将其余的

我正在使用 xlsxwriter 创建 xlsx 文件,并希望保护特定的单元格 (例如范围 B2:B20 中的所有单元格)。文档说您可以使用worksheet.protect()方法-默认情况下打开整个工作表的保护-然后您可以使用workbook.add_format({'locked': 0})来解锁特定的单元格。但我希望反之亦然-我想只锁定特定范围的单元格,并将其余的

11

执行此操作的方式与 Excel 中的相同:set an unlock format for the entire sheet

在 Excel 和 XlsxWriter 中,这转换为为所有列设置解锁格式。像这样:

import xlsxwriter
workbook = xlsxwriter.Workbook('protection.xlsx')
worksheet = workbook.add_worksheet()
# Create some cell formats with protection properties.
unlocked = workbook.add_format({'locked': False})
locked   = workbook.add_format({'locked': True})
# Format the worksheet to unlock all cells.
worksheet.set_column('A:XDF', None, unlocked)
# Turn worksheet protection on.
worksheet.protect()
# Write a locked and an unlocked cell.
worksheet.write('B1', 'Cell A1 is locked. It cannot be edited.')
worksheet.write('B2', 'Cell A2 is unlocked. It can be edited.')
worksheet.write('B3', 'Cell A3 is unlocked. It can be edited.')
worksheet.write('A1', 'Hello', locked  )  
worksheet.write('A2', 'Hello', unlocked)
worksheet.write('A3', 'Hello'          ) # Unlocked by default.
workbook.close()
1

您可以使用以下代码来保护单张或多张 Excel 文件的特定单元格范围。

注意:此函数假定 Excel 文件已经生成。

def protectCell(excelFilePath, protectCellRange='a1:b1', sheetName = 'Sheet1'):
    ''' 
    To protect the range of cell in an Excel file.
    arguments:
        1. excelFilePath : path of excel file.
        2. protectCellRange : Range of cells to protect. default is a1:b1
        3. sheetName : name of the sheet present in Excel file. Default is Sheet1
        
        Currently it supports column from A to Z only. i.e 0 to 25 columns 
        
    '''
    protectCellRange = protectCellRange.lower() 
    temp = list(string.ascii_lowercase)
    # refer = {'a':0, 'b':1, 'c':2, 'd':3, 'e':4}
    refer = {}
    for i, item in enumerate(temp):
        refer[item] = i
        
    writer = pd.ExcelWriter(excelFilePath, engine='xlsxwriter')
    TotalDf = pd.read_excel(excelFilePath, sheet_name = None, header= None)
    for sheet in TotalDf.keys():
        if sheet == sheetName:
            df = TotalDf[sheetName]
            # workbook = xlsxwriter.Workbook('excelFilePath='./temp/protection.xlsx')
            # worksheet = workbook.add_worksheet()
            df.to_excel(writer, sheet_name = sheetName, header= False, index=False)
            workbook = writer.book
            worksheet = writer.sheets[sheetName]
            # Create some cell formats with protection properties.
            unlocked = workbook.add_format({'locked': False, 'font_size': 9, 'text_wrap': True, 'align': 'left', 'valign': 'top',})
            locked   = workbook.add_format({'locked': True, 'font_size': 9, 'text_wrap': True, 'align': 'left', 'valign': 'top',})
            
            # Format the worksheet to unlock all cells.
            worksheet.set_column('A:C', 20, unlocked)
            
            # Turn worksheet protection on.
            worksheet.protect()
            
            startPointer = protectCellRange.split(':')[0]
            endPointer = protectCellRange.split(':')[-1]
            
            if len(startPointer) < 1 or len(endPointer) < 1:
                print(f'Please specify correct cell range. Specified range is {protectCellRange}')
                return 'Failure'
            
            colRange = range(refer[startPointer[0]],refer[endPointer[0]] + 1 ) 
            rowRange = range(int(startPointer[1:]), int(endPointer[1:]) + 1 )
            for col in colRange:
                for row in rowRange:
                    c = [k for k,v in refer.items() if v == col][0].upper()
                    print(col,row)
                    try:
                        value = df[col].iloc[row-1]
                        #print(f'value is ::: {value}')
                        worksheet.write(c+str(row), value, locked, )
                    except:
                        print(f'exception in Cell: {c+str(row)}')
        else:
            df = TotalDf[sheet]
            df.to_excel(writer,sheet_name = sheet, header= False, index=False)
            workbook = writer.book
            worksheet = writer.sheets[sheet]
            wrap_format = workbook.add_format({'text_wrap': True, 'align': 'left', 'valign': 'top'})
            worksheet.set_column('A:E', 20, wrap_format)
    workbook.worksheets_objs.sort(key=lambda x: x.name)    
    writer.save()
    writer.close()  
    workbook.close()
    print(f'Excel File is protected for section {protectCellRange}')
    return 'Success'

# = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

使用所有必需的参数调用函数

protectCell(excelFilePath='./Cell_protection.xlsx',
            protectCellRange = 'A4:C7', sheetName = 'ABC')

enter image description here

protectCell(excelFilePath='./Cell_protection.xlsx',
                protectCellRange = 'A4:', sheetName = 'ABC')
#Range is not mentioned properly 

enter image description here

protectCell(excelFilePath='./Cell_protection.xlsx',
                    protectCellRange = ':C7', sheetName = 'ABC')
#Range is not mentioned properly

enter image description here

本站系公益性非盈利分享网址,本文来自用户投稿,不代表码文网立场,如若转载,请注明出处

(601)
圣人的符号数学:( a* x* y+z).subs( x+ y= = b)
上一篇
centos8 firewalld错误'COMMAND_FAILED:' python-nftables'失败'
下一篇

相关推荐

发表评论

登录 后才能评论

评论列表(41条)