Spaces:
Sleeping
Sleeping
| import openpyxl | |
| from PIL import Image, ImageDraw, ImageFont | |
| import base64 | |
| import requests | |
| import gradio as gr | |
| import os | |
| # OpenAI API Key | |
| api_key = os.environ.get("api_key") | |
| # Function to encode the image | |
| def encode_image(image_path): | |
| with open(image_path, "rb") as image_file: | |
| return base64.b64encode(image_file.read()).decode('utf-8') | |
| def take_screenshot_of_cells(file_path, sheet_name, start_cell, end_cell, query): | |
| # Load the workbook and the specified sheet | |
| workbook = openpyxl.load_workbook(file_path) | |
| sheet = workbook[sheet_name] | |
| # Get the coordinates of the start and end cells | |
| start_col, start_row = openpyxl.utils.cell.coordinate_to_tuple(start_cell) | |
| end_col, end_row = openpyxl.utils.cell.coordinate_to_tuple(end_cell) | |
| # Calculate the number of rows and columns | |
| num_cols = end_col - start_col + 1 | |
| num_rows = end_row - start_row + 1 | |
| # Set the width and height of each cell (you can adjust these values as needed) | |
| cell_width = 100 | |
| cell_height = 30 | |
| # Create a blank image with the calculated size | |
| image_width = cell_width * num_cols | |
| image_height = cell_height * num_rows | |
| image = Image.new('RGB', (image_width, image_height), 'white') | |
| draw = ImageDraw.Draw(image) | |
| # Load a font (you may need to specify the path to a font file on your system) | |
| font = ImageFont.load_default() | |
| # Draw the cells and their content | |
| for row in range(num_rows): | |
| for col in range(num_cols): | |
| cell_value = sheet.cell(row=start_row + row, column=start_col + col).value | |
| x0 = col * cell_width | |
| y0 = row * cell_height | |
| x1 = x0 + cell_width | |
| y1 = y0 + cell_height | |
| # Draw the cell border | |
| draw.rectangle([x0, y0, x1, y1], outline='black') | |
| # Draw the cell value | |
| if cell_value is not None: | |
| draw.text((x0 + 5, y0 + 5), str(cell_value), fill='black', font=font) | |
| # Save the image | |
| output_image_path = 'screenshot.png' | |
| image.save(output_image_path) | |
| # Encode the image to base64 | |
| base64_image = encode_image(output_image_path) | |
| # Send the image to the OpenAI API | |
| headers = { | |
| "Content-Type": "application/json", | |
| "Authorization": f"Bearer {api_key}" | |
| } | |
| payload = { | |
| "model": "gpt-4o", # Assuming the model name is 'gpt-4-vision' | |
| "messages": [ | |
| { | |
| "role": "user", | |
| "content": [ | |
| { | |
| "type": "text", | |
| "text": query | |
| }, | |
| { | |
| "type": "image_url", | |
| "image_url": { | |
| "url": f"data:image/jpeg;base64,{base64_image}" | |
| } | |
| } | |
| ] | |
| } | |
| ], | |
| "max_tokens": 300 | |
| } | |
| response = requests.post("https://api.openai.com/v1/chat/completions", headers=headers, json=payload) | |
| response=response.json() | |
| return response['choices'][0]['message']['content'] | |
| def gradio_interface(excel_file, sheet_name, start_cell, end_cell, query): | |
| return take_screenshot_of_cells(excel_file.name, sheet_name, start_cell, end_cell, query) | |
| # Create Gradio Interface | |
| inputs = [ | |
| gr.File(label="Upload Excel File"), | |
| gr.Textbox(label="Sheet Name"), | |
| gr.Textbox(label="Start Cell"), | |
| gr.Textbox(label="End Cell"), | |
| gr.Textbox(label="Query") | |
| ] | |
| outputs = gr.Textbox(label="Output") | |
| gr.Interface(fn=gradio_interface, inputs=inputs, outputs=outputs, title="Excel Screenshot Analyzer").launch() | |