So löschen Sie einen Bereich von Werten in einer Excel-Arbeitsmappe mit OpenPyXl

Ich habe eine Arbeitsmappe, die ich möchte, um klar eine Reihe von Werten mit OpenPyXI. Bisher habe ich die folgenden:

# 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'

Kurz gesagt, ich bin versucht zu tun, in OpenPyXL die gleiche Sache, die folgenden VBA-tut:

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

Danke!

InformationsquelleAutor Andrew | 2016-04-12
  1. 17

    Scheint es, dass die Bibliothek nicht über eine Methode zu löschen, oder legen Sie einen Zeitraum direkt. Also deine beste chance ist wahrscheinlich, um den Wert zu löschen, für jede Zelle:

    for row in ws['A1:G37']:
  for cell in row:
    cell.value = None
    • Das funktioniert wunderbar, danke!
    InformationsquelleAutor Florent B.
  2. 2

    Wenn Sie abgeschnitten werden soll (löschen und neu erstellen) eine ganze Blatt, einschließlich Stile, grid-Stile, alles, was Sie tun können, es auf diese Weise:

    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)
    InformationsquelleAutor MaxU
  3. 0

    Dieser arbeitet für mich:

    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)
    InformationsquelleAutor Gustave Coste
  4. 0
    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
    InformationsquelleAutor Karam Qusai
Schreibe einen Kommentar