У меня есть функция, которая забирает данные из ексель таблицы и вставляет их в .docx документ, все данные четко забираются, кроме тех в которых есть ВПР формула, а именно значения section и dimensions
section - =ВПР(RC[-1];R2C21:R16C22;2;1)
dimensions - =ВПР(RC[-1];R18C21:R26C22;2;1)
Код:
def safe_get_number(value):
if pd.isna(value) or value == "":
return None
try:
num = float(str(value).replace(',', '.'))
return int(num) if num.is_integer() else num
except (ValueError, TypeError):
return None
async def process_matching_row(row, shu_number, index, message, bot):
try:
name_of_the_unit = row.iloc[2] if len(row) > 2 else "Не указано"
serial_number = row.iloc[3] if len(row) > 3 else "Не указано"
ip = int(row.iloc[18]) if len(row) > 17 else "Не указано"
weight = safe_get_number(row.iloc[15]) if len(row) > 15 else None
weight_display = f"{weight} кг" if weight is not None else "Не указано"
A = row.iloc[4] if len(row) > 4 else "Не указано"
section = row.iloc[6] if len(row) > 6 else "Не указано" # тут формула ВПР (выдает nan)
voltage = int(row.iloc[8] if len(row) > 8 else "Не указано")
rated_A = row.iloc[4] if len(row) > 4 else "Не указано"
dimensions = row.iloc[14] if len(row) > 6 else "Не указано". #тут формула ВПР (выдает nan)
sn = row.iloc[16] if len(row) > 16 else "Не указано"
logger.info(row)
try:
doc = Document(passport_config.get_target_file())
replacements = {
"Заводской номер:": f"Заводской номер: {str(serial_number)}",
"Проект №": f"Проект № ШУ {shu_number}",
"Шкаф №": f"Шкаф {str(name_of_the_unit)}",
"Дата:": f"Дата: {datetime.now().strftime('%d.%m.%Y')}",
"IP": f"IP{ip}",
"A": f"{A}A",
"`": f"{section}мм2",
"~": f"{dimensions}мм",
"± 10 %": f"{voltage}± 10 %",
"}": f"{weight}кг",
"{": f"{sn}"
}
def replace_text_with_style(paragraph, old_text, new_text):
if old_text in paragraph.text:
for run in paragraph.runs:
if old_text in run.text:
run.text = run.text.replace(old_text, new_text)
for paragraph in doc.paragraphs:
for old_text, new_text in replacements.items():
replace_text_with_style(paragraph, old_text, new_text)
for table in doc.tables:
for row in table.rows:
for cell in row.cells:
for paragraph in cell.paragraphs:
for old_text, new_text in replacements.items():
replace_text_with_style(paragraph, old_text, new_text)
output_filename = f"ШУ_{shu_number}_{serial_number}.docx"
desktop_path = SHUConfig.get_directory_path()
pattern = re.compile(r".*ШУ\s*" + re.escape(str(shu_number)) + r"\D*$", re.IGNORECASE)
matching_folders = []
for folder_name in os.listdir(desktop_path):
if pattern.search(folder_name):
matching_folders.append(folder_name)
save_path = "."
if matching_folders:
matching_folders.sort(reverse=True)
folder_path = os.path.join(desktop_path, matching_folders[0])
pasporta_path = os.path.join(folder_path, "Паспорта")
try:
os.makedirs(pasporta_path, exist_ok=True)
save_path = pasporta_path
except Exception as e:
save_path = folder_path
output_path = os.path.join(save_path, output_filename)
doc.save(output_path)
logger.info(Fore.GREEN + f"✓ Документ сохранен: {output_path}" + Style.RESET_ALL)
if matching_folders:
pdf_file = None
elektrika_path = os.path.join(folder_path, "электрика")
if os.path.exists(elektrika_path):
pdf_files = [f for f in os.listdir(elektrika_path)
if f.lower().endswith('.pdf') and f.startswith('1')]
if pdf_files:
pdf_files.sort()
pdf_file = os.path.join(elektrika_path, pdf_files[0])
if not pdf_file:
pdf_files = [f for f in os.listdir(folder_path)
if f.lower().endswith('.pdf') and f.startswith('1')]
if pdf_files:
pdf_files.sort()
pdf_file = os.path.join(folder_path, pdf_files[0])
if pdf_file:
if await process_pdf_attachment(pdf_file, pasporta_path, message, bot, shu_number):
else:
await bot.send_message(message.from_user.id,
"Не найден PDF файл электрики!")
except Exception as e:
await bot.send_message(ADMIN_ID,
f"× Ошибка в замене шаблона: {str(e)}")
logger.error(Fore.RED + f"× Ошибка: {str(e)}" + Style.RESET_ALL)
except Exception as e:
await bot.send_message(ADMIN_ID,
f"Ошибка при обработке строки {index + 1}: {e}")
logger.error(Fore.RED + f"Ошибка при обработке строки {index + 1}: {e}" + Style.RESET_ALL)
async def find_in_register(file_path, shu_number, message, bot):
try:
df = pd.read_excel(file_path)
pattern = fr'(?:^|\b)ШУ\s*{re.escape(shu_number)}(?:\b|$)'
mask = df.apply(lambda col: col.astype(str).str.contains(pattern, regex=True)).any(axis=1)
matching_rows = df[mask]
if not matching_rows.empty:
logger.info(Fore.YELLOW + f"\nВсего найдено {len(matching_rows)} совпадений в реестре для ШУ{shu_number}:" + Style.RESET_ALL)
for i, (_, row) in enumerate(matching_rows.iterrows()):
await process_matching_row(row, shu_number, i, message, bot)
else:
await bot.send_message(message.from_user.id, f"\nВ реестре не найдено записей для ШУ{shu_number}")
except Exception as e:
await bot.send_message(message.from_user.id, f"Ошибка при поиске в реестре: {e}")
Средний
