'''Created 16 May 2024 Levi''' import unittest from collections import defaultdict from datetime import date import openpyxl import shutil from lxml import etree from rich import print # noqa class Test(unittest.TestCase): def test_xml(self): vb_path = r'\\mozaiq\levente.marton\#mngm\Formulare\AUTORITATI\MINISTERUL FINANTELOR\Note explicative (finalizat)\2024\lista_firme.xlsm' backup_path = r'\\mozaiq\levente.marton\#mngm\Formulare\AUTORITATI\MINISTERUL FINANTELOR\Note explicative (finalizat)\2024\lista_firme.xlsm.bak' xml_path = r'c:\Users\DECEL\Bilant anual\Kat_Imob\2023\s1005_38063294_2023_12.xml' xml = etree.parse(xml_path) # root = xml.getroot() tree = xml.xpath('/xmlns:Bilant1005/*', namespaces={'xmlns': 'mfp:anaf:dgti:s1005:declaratie:v12'}) # print(tree[0].attrib['F10_0041']) print(tree) def F10() -> dict[int]: def_tree = defaultdict(lambda: 0) def_tree.update(tree[0].attrib) xpaths = {} xpaths['total_act_imob_C1'] = def_tree['F10_0041'] xpaths['total_act_imob_C2'] = def_tree['F10_0042'] xpaths['total_act_circ_C1'] = def_tree['F10_0091'] xpaths['total_act_circ_C2'] = def_tree['F10_0092'] xpaths['total_chelt_av_C1'] = def_tree['F10_0101'] xpaths['total_chelt_av_C2'] = def_tree['F10_0102'] xpaths['total_dat_tot_C1'] = sum([int(def_tree['F10_0131']), int(def_tree['F10_0161'])]) xpaths['total_dat_tot_C2'] = sum([int(def_tree['F10_0132']), int(def_tree['F10_0162'])]) xpaths['total_ven_av_C1'] = def_tree['F10_0181'] xpaths['total_ven_av_C2'] = def_tree['F10_0182'] xpaths['total_cap_soc_C1'] = def_tree['F10_0291'] xpaths['total_cap_soc_C2'] = def_tree['F10_0292'] xpaths['total_rezerve_C1'] = def_tree['F10_0371'] xpaths['total_rezerve_C2'] = def_tree['F10_0372'] xpaths['total_rez_reev_C1'] = def_tree['F10_0361'] xpaths['total_rez_reev_C2'] = def_tree['F10_0362'] xpaths['total_prof_rep_C1'] = def_tree['F10_0411'] xpaths['total_prof_rep_C2'] = def_tree['F10_0412'] xpaths['total_pierd_rep_C1'] = def_tree['F10_0421'] xpaths['total_pierd_rep_C2'] = def_tree['F10_0422'] xpaths['total_prof_cur_C1'] = def_tree['F10_0431'] xpaths['total_prof_cur_C2'] = def_tree['F10_0432'] xpaths['total_pierd_cur_C1'] = def_tree['F10_0441'] xpaths['total_pierd_cur_C2'] = def_tree['F10_0442'] xpaths['total_rep_prof_C1'] = def_tree['F10_0451'] xpaths['total_rep_prof_C2'] = def_tree['F10_0452'] xpaths['total_cap_propr_C1'] = def_tree['F10_0461'] xpaths['total_cap_propr_C2'] = def_tree['F10_0462'] for keys, values in xpaths.items(): xpaths[keys] = int(values) # print(xpaths) return xpaths # F10() def F20() -> dict[int]: def_tree = defaultdict(lambda: 0) def_tree.update(tree[1].attrib) xpaths = {} xpaths['total_ven_exp_C1'] = def_tree['F20_0161'] xpaths['total_ven_exp_C2'] = def_tree['F20_0162'] xpaths['total_chelt_exp_C1'] = def_tree['F20_0421'] xpaths['total_chelt_exp_C2'] = def_tree['F20_0422'] xpaths['total_ven_fin_C1'] = def_tree['F20_0521'] xpaths['total_ven_fin_C2'] = def_tree['F20_0522'] xpaths['total_chelt_fin_C1'] = def_tree['F20_0591'] xpaths['total_chelt_fin_C2'] = def_tree['F20_0592'] xpaths['total_imp_C1'] = sum([int(def_tree['F20_0661']), int(def_tree['F20_0681'])]) xpaths['total_imp_C2'] = sum([int(def_tree['F20_0662']), int(def_tree['F20_0682'])]) for keys, values in xpaths.items(): xpaths[keys] = int(values) # print(xpaths) return xpaths def F30() -> dict[int]: def_tree = defaultdict(lambda: 0) def_tree.update(tree[2].attrib) xpaths = {} xpaths['total_ven_exp_C1'] = def_tree['F30_1821'] xpaths['total_ven_exp_C2'] = def_tree['F30_1822'] xpaths['total_chelt_exp_C1'] = def_tree['F30_2081'] xpaths['total_chelt_exp_C2'] = def_tree['F30_2082'] xpaths['total_ven_fin_C1'] = def_tree['F30_2181'] xpaths['total_ven_fin_C2'] = def_tree['F30_2182'] xpaths['total_chelt_fin_C1'] = def_tree['F30_2251'] xpaths['total_chelt_fin_C2'] = def_tree['F30_2252'] xpaths['total_imp_C1'] = sum([int(def_tree['F30_2321']), int(def_tree['F30_2341'])]) xpaths['total_imp_C2'] = sum([int(def_tree['F30_2322']), int(def_tree['F30_2342'])]) for keys, values in xpaths.items(): xpaths[keys] = int(values) # print(xpaths) return xpaths # F20() # return F10, F20 firms = ['KATONA IMOBILIARE SRL'] shutil.copy2(vb_path, backup_path) wb = openpyxl.load_workbook(vb_path, keep_vba=True) sheet = wb.active for firm in firms: for row in sheet.iter_rows(min_row=1, max_col=sheet.max_column, max_row=sheet.max_row): if firm in str(row[0].value): print('hit:', firm) row[9].value = F10()['total_cap_soc_C2'] row[10].value = F10()['total_prof_cur_C2'] row[16].value = F10()['total_pierd_cur_C2'] row[18].value = F30()['total_ven_exp_C2'] row[19].value = F30()['total_ven_fin_C2'] row[22].value = F30()['total_chelt_exp_C2'] row[23].value = F30()['total_chelt_fin_C2'] row[26].value = F30()['total_imp_C2'] row[28].value = F10()['total_act_imob_C2'] row[29].value = F10()['total_act_circ_C2'] row[30].value = F10()['total_chelt_av_C2'] row[32].value = F10()['total_cap_propr_C2'] row[33].value = F10()['total_dat_tot_C2'] row[35].value = F10()['total_ven_av_C2'] row[47].value = 12 row[48].value = date.today().year - 1 row[49].value = date.today().strftime('%d.%m.%Y') wb.save(vb_path) if __name__ == "__main__": Test().test_xml() # print(F10()) # unittest.main()