XlsxWriter: lock only specific cells

Question

I'm creating xlsx files with xlsxwriter and want to protect specific cells (for example all cells in a range B2:B20). The documentation says that you can use worksheet.protect() method - it's turn on protection for whole worksheet by default - and then you can use workbook.add_format({'locked': 0}) for unlocking specific cells. But I want vice versa - I want to lock only specific range of cells and leave the rest of the worksheet unlocked. How can I do this?

Frontpage

Answer 1

The way to do this is the same as in Excel: set an unlock format for the entire sheet.

In Excel and XlsxWriter this translates to setting a unlock format for all the columns. Like this:

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()

Mesarch

+1 Because this also will lock images to cells, which then allows you to sort the sheet while preserving the order of all data – Illyes

great help dude. – Incognizant

Answer 2

You can use the following code to protect the specific range of cells for single or multi sheet excel file.

Note: this function assumes that the Excel file is already been generated.

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'

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

Call the function with all the required parameters

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

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

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

Deference