Excel 筛选功能

Excel 筛选功能

file_path = r"C:\Users\闫佳怡\Desktop\雅诗兰黛实施\PR\2823-202002.xlsx"
book = excel.open(file_path, visible=True, wps=False)
# sht = book.get_sheet('Sheet1')

def filter2(book, sheet_name, ranges, col, criteria1):
    """
    :criteria1: 筛选条件

    """
    from openpyxl.utils import get_column_letter
    allrow, allcol = book.get_sheet(sheet_name).get_row_col()
    # 处理range
    if ranges is None or ranges == "":
        end_position = get_column_letter(allcol)
        ranges = "A1:{0}{1}".format(end_position, allrow)
    sht = book.get_sheet(sheet_name).sht
    sht.Range(ranges).AutoFilter(Field=col, Criteria1=criteria1, Operator=7)
    filter_result = []
    for Area in sht.Range(ranges).SpecialCells(Type=12).Areas:
        for row in list(map(lambda x: list(x), Area.GetValue())):
            filter_result.append(row)
    return filter_result

print(filter2(book=book, sheet_name="Sheet1", ranges="", col="1", criteria1="<0"))