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

Question

I have a workbook that I would like to clear a range of values with 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 am trying to do in OpenPyXL the same thing the following VBA does:

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

Thank you!

Answer 1

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

Answer 2

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] sheet
idx = wb.sheetnames.index(sheet_name)

# remove [sheet_name]
# old versions: wb.remove(writer.book.worksheets[idx])
# for new versions, tested with 3.0.3
ws = wb.get_sheet_by_name(sheet_name)
wb.remove(ws)

# create an empty sheet [sheet_name] using old index
wb.create_sheet(sheet_name, idx)

Answer 3

import openpyxl

book = openpyxl.load_workbook('sample.xlsx') #get the file name
sheet = book.get_sheet_by_name('Sheet') #get the sheet name

for a in sheet['A1':'A2']: #you can set the range here 
    for cell in a:
        cell.value = None #set a value or null here
book.save('sample.xlsx') #you can save it after if you like

Answer 4

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 conditional formatting
    conditional_formattings = list(worksheet.conditional_formatting._cf_rules.keys())
    for conditional_formatting in conditional_formattings:
        ranges_to_keep = [x for x in conditional_formatting.cells.ranges
                          if not range_contains(cell_range, x.coord)]

        if len(ranges_to_keep) != 0:
            conditional_formatting.cells.ranges = conditional_formatting.sqref.ranges = ranges_to_keep
        else:
            del worksheet.conditional_formatting._cf_rules[conditional_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)