Spaces:
Sleeping
Sleeping
File size: 3,638 Bytes
c53b63b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill
import gradio as gr
def load_excel(file_path):
"""Loads the Excel file into a pandas DataFrame."""
df = pd.read_excel(file_path, sheet_name="Sheet1")
return df
def highlight_pairs(df, threshold=2.0):
"""Highlights pairs in columns D and E based on the threshold."""
colors = ["FF0000", "00FF00", "0000FF", "FFFF00", "FF00FF", "00FFFF"] # Hex colors for RGB
color_index = 0
df['High Result'] = ''
df['Low Result'] = ''
colored_pairs = set()
wb = openpyxl.load_workbook(file_path)
ws = wb['Sheet1']
# Function to apply color to cells
def apply_color(cell, color_hex):
cell.fill = PatternFill(start_color=color_hex, end_color=color_hex, fill_type="solid")
last_row = len(df)
def process_column(df, col_index, output_col, color_pairs, colors):
nonlocal color_index
for i in range(last_row - 1, 1, -1):
for j in range(i - 1, 1, -1):
if abs(df.iloc[i, col_index] - df.iloc[j, col_index]) <= threshold:
pair_key_i = (i, col_index)
pair_key_j = (j, col_index)
if pair_key_i not in color_pairs and pair_key_j not in color_pairs:
apply_color(ws.cell(row=i+2, column=col_index+1), colors[color_index])
apply_color(ws.cell(row=j+2, column=col_index+1), colors[color_index])
color_pairs.add(pair_key_i)
color_pairs.add(pair_key_j)
output_value = (max if col_index == 3 else min)(
int(df.iloc[i, col_index]), int(df.iloc[j, col_index])
) + (1 if col_index == 3 else -1)
color_output = False
for k in range(i - 1, 1, -1):
if ((df.iloc[k, col_index] > output_value and col_index == 3) or
(df.iloc[k, col_index] < output_value and col_index == 4)):
if ((df.iloc[k, 5] > max(df.iloc[i, col_index], df.iloc[j, col_index]) and col_index == 3) or
(df.iloc[k, 5] < min(df.iloc[i, col_index], df.iloc[j, col_index]) and col_index == 4)):
apply_color(ws.cell(row=j+2, column=output_col+1), colors[color_index])
color_output = True
break
ws.cell(row=j+2, column=output_col+1).value = output_value
if not color_output:
ws.cell(row=j+2, column=output_col+1).fill = PatternFill(fill_type=None)
color_index = (color_index + 1) % len(colors)
process_column(df, 3, 6, colored_pairs, colors) # Process Column D (High Price)
process_column(df, 4, 7, colored_pairs, colors) # Process Column E (Low Price)
wb.save('output.xlsx')
def gradio_app(file_path):
"""Main function to handle Gradio inputs and outputs."""
df = load_excel(file_path)
highlight_pairs(df)
return 'output.xlsx'
# Gradio Interface
gr_interface = gr.Interface(
fn=gradio_app,
inputs=gr.inputs.File(label="Upload Excel File"),
outputs=gr.outputs.File(label="Download Processed Excel File"),
title="Excel Highlighter"
)
if __name__ == "__main__":
gr_interface.launch()
|