Условное форматирование пустых ячеек Python openpyxl

#python #excel #openpyxl #conditional-formatting

#python #excel #openpyxl #условное форматирование

Вопрос:

У меня проблема с форматированием пустых ячеек. Мой код

 import setting_prices
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
import os
from datetime import datetime


def load_file_apply_format():

    wb = load_workbook(filename)
    writer = pd.ExcelWriter(filename, engine='openpyxl')
    writer.book = wb
    prices.to_excel(writer, sheet_name=today_as_str)
    ws = wb[today_as_str]

    redFill = PatternFill(start_color='EE1111',
                              end_color='EE1111',
                              fill_type='solid')
    # whiteFill = PatternFill(start_color='FFFFFF',
    #                           end_color='FFFFFF',
    #                           fill_type='solid')


    ws.conditional_formatting.add('B2:H99',
                                      CellIsRule(operator='lessThan',
                                                 formula=['$I2'],
                                                 stopIfTrue=False, fill=redFill))
    

    writer.save()


prices = setting_prices.df

today_as_str = datetime.strftime(datetime.now(), ' %d_%m_%y')
desktop_path = os.path.expanduser("~/Desktop")

filename = 'price_check.xlsx'


if os.path.exists(filename):
    load_file_apply_format()
else:
    prices.to_excel(filename, sheet_name=today_as_str)
    load_file_apply_format()

 

Моя формула работает просто отлично, но Excel обрабатывает пустые ячейки как 0, поэтому они всегда меньше столбца I и форматирует их. Я хотел бы пропустить пустые ячейки или отформатировать их так, чтобы они выглядели как обычные ячейки.

Я перепробовал почти все предложения с форума, но, похоже, я не могу это исправить.

Пожалуйста, дайте мне несколько предложений.

@Грег отвечает за использование :

    ws.conditional_formatting.add('B2:H99',
                                  CellIsRule(operator='between',
                                             formula=['1', '$I2'],
                                             stopIfTrue=False, fill=redFill))

 

приводит к форматированию ячеек, которые == в столбец ‘I’, чего я хочу избежать. Также «между» задает формат для всех пустых ячеек, если ячейка в столбце «I» пуста.

Пример изображения

Например: все ячейки для ProductA должны быть отформатированы по умолчанию, поскольку они равны ячейке в столбце ‘I’. Для productB единственной отформатированной ячейки должно быть G3 , потому что ниже I3 . Все ячейки для productC должны быть отформатированы по умолчанию, поскольку ячейка в I пуста.

Я подумал, что если я использую свой код форматирования для lessThen, и другая формула для пустых ячеек выполнит эту работу. Но я не смог заставить это работать.

Ответ №1:

Мне удается исправить мою проблему с помощью метода try / error. Я опубликую свое решение здесь и надеюсь, что кто-то найдет его полезным. Конечный результат создается:

  1. Размещение всех ячеек из строки в список
  2. Сравните все значения из списка с желаемым
  3. ЕСЛИ ячейка находится ниже -> применить форматирование

Окончательный код:

 import ...



def apply_format_to_cell(cell):

    """
    Set background and font color For the current cell
    """

    ft = Font(color="FF0000")
    fill_black = PatternFill(bgColor="FFC7CE", fill_type="solid")

    cell.font = ft
    cell.fill = fill_black

    return cell


def open_existing_file(file_name):
    """
    open an existing file to format cell
    which is meeting a condition
    """

    wb = load_workbook(file_name)
    writer = pd.ExcelWriter(file_name, engine='openpyxl')
    writer.book = wb
    prices.to_excel(writer, sheet_name=today_as_str)
    ws = wb[today_as_str]

    for row in ws.iter_rows(2, ws.max_row, 2):
        """
        1st parameter says to start from 2 row 
        2nd parameter stands for -> till the last row with data. 
        3th parameter says start from 2 COLUMN. 
            In this case this is B2
        """
        cells_in_row = []  # making a list of cells which we will compare
        for cells in row:
            cells_in_row.append(cells)
        for cell in cells_in_row:
            if cell.value is not None and type(cell.value) is not str 
                    and cells_in_row[-1].value is not None and type(cells_in_row[-1].value) is not str:
                """
                Checks if the cell value is not Empty or str ( '' ).
                """

                if cell.value < cells_in_row[-1].value:
                    apply_format_to_cell(cell)
    if wb[f'{today_as_str   "1"}']:
        """
        For the first run only!
        Because: prices.to_excel(writer, sheet_name=today_as_str) will make again sheet
        with the same name -> Excel will put '1' at the end of name 'Sheet_name' > 'Sheet_name1'
        This if will delete this unwanted sheet!
        """
        del wb[f'{today_as_str   "1"}']

    writer.save()


prices = setting_prices.df  # import df with prices

today_as_str = datetime.strftime(datetime.now(), ' %d_%m_%y')
desktop_path = os.path.expanduser("~/Desktop")

filename = 'price_check.xlsx'


if os.path.exists(filename):
    open_existing_file(filename)
else:
    prices.to_excel(filename, sheet_name=today_as_str)
    open_existing_file(filename)

 

Пример конечного результата:
Пример конечного результата