How to clear a range of values ​​in an Excel workbook using OpenPyXl

Question

I have a workbook in which I would like to clear a range of values ​​using OpenPyXI. So far I have the following:

# Import OpenPyXl module. from openpyxl import load_workbook # Load workbook. wb = load_workbook(filename = 'testing.xlsx') # Make a variable with a worksheet you want to view/modify. sheet = wb['AR Cutoff'] # Change value of A3. sheet['A3'] = 'Assigned value' 

In short, I'm trying to do the same thing in OpenPyXL that the following VBA does:

 Worksheets("Sheet1").Range("A1:G37").Clear 

Thanks!

Answer 1

It appears that the library does not have a direct or direct dialing method. Therefore, your best chance is probably to clear the value for each cell:

 for row in ws['A1:G37']: for cell in row: cell.value = None 

Answer 2

If you want to truncate (drag and recreate) the whole sheet, including styles, mesh styles, you can do the rest as follows:

 wb = load_workbook(filename = 'testing.xlsx') sheet_name = 'AR Cutoff' # index of [sheet_name] sheet idx = wb.sheetnames.index(sheet_name) # remove [sheet_name] wb.remove(writer.book.worksheets[idx]) # create an empty sheet [sheet_name] using old index wb.create_sheet(sheet_name, idx) 

Answer 3

This one works for me:

 from openpyxl.utils import cols_from_range, range_boundaries def range_contains(range_1, range_2): """ Evaluates if a range contains another. Args: range_1 (str): Range to contain range_2 (str): Range to be contained Returns: bool: Examples: >>> range_contains('A1:F6', 'B2:D3') True >>> range_contains('B2:D3', 'A1:F6') False >>> range_contains('A1:F3', 'B2:D6') False >>> range_contains('A1:F3', 'A1:F3') True """ bound_1 = range_boundaries(range_1) bound_2 = range_boundaries(range_2) if bound_1[0] <= bound_2[0] and bound_1[1] <= bound_2[1] and bound_1[2] >= bound_2[2] and bound_1[3] >= bound_2[3]: return True else: return False def delete_cells(worksheet, cell_range): """ Removes cells from a worksheet (deletes value, conditional formatting, data validation and cell merging) Args: worksheet (Worksheet): cell_range (str): """ for column in cols_from_range(cell_range): for cell_coordinates in column: # Removing value worksheet[cell_coordinates].value = None # Removing style (applying the style of cell A1) worksheet[cell_coordinates]._style = worksheet['A1']._style # Removing conditionnal formatting conditionnal_formattings = list(worksheet.conditional_formatting._cf_rules.keys()) for conditionnal_formatting in conditionnal_formattings: ranges_to_keep = [x for x in conditionnal_formatting.cells.ranges if not range_contains(cell_range, x.coord)] if len(ranges_to_keep) != 0: conditionnal_formatting.cells.ranges = conditionnal_formatting.sqref.ranges = ranges_to_keep else: del worksheet.conditional_formatting._cf_rules[conditionnal_formatting] # Removing data validation for validation in worksheet.data_validations.dataValidation: for validation_range in validation.cells.ranges: if range_contains(cell_range, validation_range.coord): validation.cells.ranges.remove(validation_range) # Remove merge cells merge_cells_ranges = [x.coord for x in worksheet.merged_cells.ranges] for merged_cells_range in merge_cells_ranges: if range_contains(cell_range, merged_cells_range): worksheet.unmerge_cells(merged_cells_range)