Spaces:
Sleeping
Sleeping
| 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 and selects the first sheet.""" | |
| wb = openpyxl.load_workbook(file_path) | |
| sheet_name = wb.sheetnames[0] # Access the first sheet | |
| df = pd.read_excel(file_path, sheet_name=sheet_name) | |
| return df, wb, sheet_name | |
| def clear_sheet_colors(ws): | |
| """Removes all colors from the given worksheet.""" | |
| for row in ws.iter_rows(): | |
| for cell in row: | |
| cell.fill = PatternFill(fill_type=None) | |
| def highlight_pairs(df, wb, sheet_name, 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() | |
| ws = wb[sheet_name] | |
| clear_sheet_colors(ws) # Clear existing colors before applying new ones | |
| # 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, wb, sheet_name = load_excel(file_path) | |
| highlight_pairs(df, wb, sheet_name) | |
| return 'output.xlsx' | |
| # Gradio Interface | |
| gr_interface = gr.Interface( | |
| fn=gradio_app, | |
| inputs=gr.File(label="Upload Excel File"), | |
| outputs=gr.File(label="Download Processed Excel File"), | |
| title="Excel Highlighter" | |
| ) | |
| if __name__ == "__main__": | |
| gr_interface.launch() | |