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

It seems that the library doesn't have a method to clear or set a range directly. So 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

If you want to truncate (drop and recreate) a whole sheet, including styles, grid styles, everything else you can do it this way:

wb = load_workbook(filename = 'testing.xlsx')sheet_name = 'AR Cutoff'# index of [sheet_name] sheetidx = wb.sheetnames.index(sheet_name)# remove [sheet_name]# old versions: wb.remove(writer.book.worksheets[idx])# for new versions, tested with 3.0.3ws = wb.get_sheet_by_name(sheet_name)wb.remove(ws)# create an empty sheet [sheet_name] using old indexwb.create_sheet(sheet_name, idx)

This one works for me:

from openpyxl.utils import cols_from_range, range_boundariesdef 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 Falsedef 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)