OpenPyXl을 사용하여 Excel 워크북의 값 범위를 지우는 방법

OpenPyXl을 사용하여 Excel 워크북의 값 범위를 지우는 방법

OpenPyXI를 사용하여 다양한 값을 지우고 싶은 워크북이 있습니다.지금까지 저는 다음을 알고 있습니다.

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

간단히 말해서, 저는 OpenPyXL에서 다음 VBA와 동일한 작업을 수행하려고 합니다.

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

감사해요!

라이브러리에서 직접 지우거나 범위를 설정할 수 있는 방법이 없는 것 같습니다.따라서 각 셀의 값을 지우는 것이 가장 좋은 방법일 것입니다.

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

스타일, 그리드 스타일을 포함한 전체 시트를 잘라내려면 다음과 같은 방법으로 수행할 수 있습니다.

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)

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

이것은 나에게 효과가 있습니다.

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)

언급URL : https://stackoverflow.com/questions/36582460/how-to-clear-a-range-of-values-in-an-excel-workbook-using-openpyxl