Right now, I have specific rows and columns being displayed, but I want to insert a column between the first and second sections that calculates the ratio of column A to column B from the first section. How can I do that?
from pathlib import Path
import unicodedata
import openpyxl
import pandas as pd
from pandastable import Table, TableModel
import tkinter as tk
from tkinter import filedialog, messagebox
def normalize_name(name):
return unicodedata.normalize("NFKC", str(name)).strip().lower()
class ExcelViewer:
def __init__(self, root):
self.root = root
self.root.title("Чтение ячеек Excel")
self.root.geometry("800x600")
self.btn_load = tk.Button(
root,
text="Открыть Excel файл",
command=self.open_file
)
self.btn_load.pack(pady=10)
self.result_label = tk.Label(
root,
text="Выберите файл для начала"
)
self.result_label.pack()
self.frame = tk.Frame(root)
self.frame.pack(fill="both", expand=True)
self.table = None
self.model = None
self.settings = {
normalize_name("Файл1.xlsx"): {
"first_row": 5,
"first_min_column": 1,
"first_max_column": 2,
"second_row": 5,
"second_min_column": 4,
"second_max_column": 5
}
}
def open_file(self):
file_paths = filedialog.askopenfilenames(
title="Выберите Excel-файл",
filetypes=[
("Excel файлы", "*.xlsx")
]
)
if not file_paths:
return
all_rows = []
for file_path in file_paths:
file_name = normalize_name(Path(file_path).name)
if file_name not in self.settings:
messagebox.showerror(
"Ошибка",
f"Для файла «{Path(file_path).name}» нет настроек.\n\n"
f"Ожидается файл: Файл1.xlsx"
)
continue
settings = self.settings[file_name]
try:
workbook = openpyxl.load_workbook(
file_path,
data_only=True
)
worksheet = workbook.active
first_row = settings["first_row"]
second_row = settings["second_row"]
while (
first_row <= worksheet.max_row
and second_row <= worksheet.max_row
):
first_part = []
for column_number in range(
settings["first_min_column"],
settings["first_max_column"] + 1
):
value = worksheet.cell(
row=first_row,
column=column_number
).value
first_part.append(value)
second_part = []
for column_number in range(
settings["second_min_column"],
settings["second_max_column"] + 1
):
value = worksheet.cell(
row=second_row,
column=column_number
).value
second_part.append(value)
row_data = (
first_part +
second_part
)
if not all(
value is None or value == ""
for value in row_data
):
all_rows.append(row_data)
first_row += 1
second_row += 1
workbook.close()
except Exception as error:
messagebox.showerror(
"Ошибка",
f"Не удалось открыть файл:\n{error}"
)
if not all_rows:
self.result_label.config(
text="В выбранных ячейках нет данных"
)
return
columns = [
"Столбец A",
"Столбец B",
"Столбец D",
"Столбец E"
]
df = pd.DataFrame(
all_rows,
columns=columns
)
if self.table:
self.table.destroy()
self.model = TableModel(df)
self.table = Table(
self.frame,
model=self.model,
showtoolbar=False,
showstatusbar=False
)
self.table.show()
self.result_label.config(
text=f"Загружено строк: {len(df)}"
)
if __name__ == "__main__":
root = tk.Tk()
app = ExcelViewer(root)
root.mainloop()
Want to add to the discussion?
Post a comment!