I’m attempting to access the conditional formatting rule applied in Excel, visible in the provided image.
The Python code extracts and processes these rules, but the output shows confusion about the ‘cellIs’ type. Seeking help to correctly interpret the rule ‘Cell Value > 32’.
import openpyxl
import pandas as pd
from datetime import datetime
from openpyxl.utils import get_column_letter
# Replace 'C:\\Users\\Rashmi bharti\\Desktop\\test1.xlsx' with your actual file path
file_path = 'C:\\Users\\Rashmi bharti\\Desktop\\test1.xlsx'
# Load the Excel workbook
workbook = openpyxl.load_workbook(file_path)
# Assume you get the sheet name dynamically or from user input
selected_sheet_name = 'Sheet1' # Replace this with your dynamic logic
# Load the selected sheet
sheet = workbook[selected_sheet_name]
# Get conditional formatting rules
conditional_formatting_rules = []
for range_ in sheet.conditional_formatting:
for rule in sheet.conditional_formatting[range_]:
# Extract the condition for 'cellIs' rule
if rule.type == 'cellIs':
# Handle both string and list cases
if isinstance(rule.formula, list):
formula = rule.formula[0]
else:
formula = rule.formula
condition = formula
else:
formula = rule.formula1
condition = formula
# Process formatting details
format_details = f"Background={rule.dxf.fill.bgColor.rgb} and Font={rule.dxf.font.color.rgb}"
rule_data = {
'RANGE': range_,
'TYPE': rule.type,
'FORMULA': condition,
'FORMAT': format_details,
}
conditional_formatting_rules.append(rule_data)
# Create a DataFrame from the list of conditional formatting rules
df = pd.DataFrame(conditional_formatting_rules)
# Extract the column 'range' to get 'A2:A5' from the range object
df['RANGE'] = df['RANGE'].apply(lambda x: x.sqref)
# Add file name, sheet name, and UTC time to the DataFrame with capitalized headers
df['FILE NAME'] = file_path.split('\\')[-1]
df['SHEET NAME'] = selected_sheet_name
df['UTC'] = datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S UTC')
# Write the DataFrame to a new sheet in the same Excel file
new_sheet_name = 'Conditional_Formatting_Data' # Replace with your desired sheet name
with pd.ExcelWriter(file_path, engine='openpyxl', mode='a') as writer:
# Check if the sheet already exists, and remove it
if new_sheet_name in writer.book.sheetnames:
writer.book.remove(writer.sheets[new_sheet_name])
df.to_excel(writer, sheet_name=new_sheet_name, index=False)
# Adjust cell sizes dynamically based on text content and header size
for idx, column in enumerate(df.columns, start=1):
max_len = max(df[column].astype(str).apply(len).max(), len(column))
adjusted_width = (max_len + 2) * 1.2
writer.sheets[new_sheet_name].column_dimensions[get_column_letter(idx)].width = adjusted_width
print(f"Data written to sheet '{new_sheet_name}' in file '{file_path}'.")
I receive
as output, but I’m uncertain about the interpretation of ‘cellIs’ in the type. My objective is to comprehend the rule applied in the image , specifically ‘Cell Value > 32’. I seek guidance on extracting the applied conditional formatting rule using python in Excel.