123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187 |
- from xml.etree import ElementTree as ET
- from collections import defaultdict
- import xlsxwriter as xlsw
- import openpyxl as xl
- from .declaration import Declaration
- class D300(Declaration):
- def __init__(self):
- super().__init__()
- def from_xml(self, fp):
- '''
- xml and pdf keys are not metching!!!
- '''
- xml = ET.parse(fp)
- root = xml.getroot()
- def_root = defaultdict(lambda: 0)
- def_root.update(root.attrib)
- self.def_root = def_root
- self.cui = def_root['cui']
- self.den = def_root['den']
- # addr=def_root['adresa'],
- self.month = int(def_root['luna'])
- self.year = int(def_root['an'])
- # rect=int(def_root['d_rec']),
- # bank=def_root['banca'],
- self.tip_decont = def_root['tip_decont']
- self.control = int(def_root['totalPlata_A'])
- self.li = int(def_root['R1_1'])
- self.pi = int(def_root['R3_1_1'])
- self.aib = int(def_root['R5_1_1'])
- self.tva_aib = int(def_root['R5_1_2'])
- self.ais = int(def_root['R7_1_1'])
- self.tva_ais = int(def_root['R7_1_2'])
- self.liv_19 = int(def_root['R9_1'])
- self.tvaL_19 = int(def_root['R9_2'])
- self.liv_9 = int(def_root['R10_1'])
- self.tvaL_9 = int(def_root['R10_2'])
- self.liv_5 = int(def_root['R11_1'])
- self.tvaL_5 = int(def_root['R11_2'])
- self.liv_txi = int(def_root['R13_1'])
- self.sc_cfdd = int(def_root['R14_1'])
- self.sc_fdd = int(def_root['R15_1'])
- self.reg_liv = int(def_root['R16_1'])
- self.tva_reg = int(def_root['R16_2'])
- self.tot_col = int(def_root['R17_1'])
- self.tva_tot_col = int(def_root['R17_2'])
- self.ach_19 = int(def_root['R22_1'])
- self.tvaA_19 = int(def_root['R22_2'])
- self.ach_9 = int(def_root['R23_1'])
- self.tvaA_9 = int(def_root['R23_2'])
- self.ach_5 = int(def_root['R24_1'])
- self.tvaA_5 = int(def_root['R24_2'])
- self.tot_ded = int(def_root['R32_2'])
- self.tot_plata = int(def_root['R41_2'])
- self.tot_rec = int(def_root['R42_2'])
- self.root = root
- self.decl_type = self.root.tag.split("}", 1)[-1]
- return self
- @property
- def data(self):
- data = {
- self.cui:
- [
- self.den,
- # addr=def_root['adresa'],
- self.month,
- self.year,
- # rect=int(def_root['d_rec']),
- # bank=def_root['banca'],
- self.tip_decont,
- self.control,
- self.li,
- self.pi,
- self.aib,
- self.tva_aib,
- self.ais,
- self.tva_ais,
- self.liv_19,
- self.tvaL_19,
- self.liv_9,
- self.tvaL_9,
- self.liv_5,
- self.tvaL_5,
- self.liv_txi,
- self.sc_cfdd,
- self.sc_fdd,
- self.reg_liv,
- self.tva_reg,
- self.tot_col,
- self.tva_tot_col,
- self.ach_19,
- self.tvaA_19,
- self.ach_9,
- self.tvaA_9,
- self.ach_5,
- self.tvaA_5,
- self.tot_ded,
- self.tot_plata,
- self.tot_rec
- ]
- }
- self.dict_data = data
- return self.dict_data
- def to_xlsx(self, xl_name, w_sheet_name, files, headers=[], obj={'type': None}, **kwargs):
- book = xlsw.Workbook(xl_name + '.xlsm')
- book.add_vba_project('./vbaProject.bin')
- sheet = book.add_worksheet(w_sheet_name)
- # sheet2 = book.add_worksheet('all')
- row, row2, col = 1, 1, 0
- headformat = book.add_format({'bold': True})
- hcol, hcol2 = 0, 0
- for h in headers:
- sheet.write(1, hcol, h, headformat)
- hcol += 1
- # for _ in range(8):
- # sheet2.write(1, hcol2, 'x', headformat)
- hcol2 += 1
- for file in files:
- d = D300() # obj['type']()
- d = d.from_xml(file)
- for i in d.data:
- row += 1
- sheet.write(row, col, i)
- print(f'write {row}', i)
- sheet.write_row(row, 1, d.data[i])
- # d_all = d.from_xml_all(file)
- # for el in d_all:
- # for dict_ in el:
- # row2 += 1
- # sheet2.write(row2, col, dict_)
- # sheet2.write_row(row2, 1, el[dict_])
- for k, a in kwargs.items():
- if k == 'autofilter':
- sheet.autofilter(a[0], a[1], 100, 100)
- # sheet2.autofilter(a[0], a[1], 1000, 1000)
- if k == 'freeze':
- sheet.freeze_panes(a[0], a[1])
- # sheet2.freeze_panes(a[0], a[1])
- book.close()
- # self._format_sh1(xl_name)
- # self._format_sh2(xl_name)
- def _format_sh1(self, xl_name):
- wb = xl.load_workbook(xl_name + '.xlsx')
- sh = wb.worksheets[0]
- colG = sh['G3':'G{}'.format(sh.max_row)]
- colIO = sh['I3':'O{}'.format(sh.max_row)]
- for valuesG, valuesIO in zip(colG, colIO):
- for value in valuesG:
- if isinstance(value.value, str):
- try:
- num_value = float(value.value)
- value.value = num_value
- except ValueError as ve:
- print(str(ve))
- value.number_format = '#,##0.00'
- for value in valuesIO:
- if isinstance(value.value, str):
- try:
- num_value = float(value.value)
- value.value = num_value
- except ValueError as ve:
- print(str(ve))
- value.number_format = '#,##0.00'
- wb.save(xl_name + '.xlsx')
- wb.close()
- def _format_sh2(self, xl_name):
- wb = xl.load_workbook(xl_name + '.xlsx')
- sh = wb.worksheets[1]
- colH = sh['H3':'H{}'.format(sh.max_row)]
- for valuesG in colH:
- for value in valuesG:
- if isinstance(value.value, str):
- try:
- num_value = float(value.value)
- value.value = num_value
- except ValueError as ve:
- print(str(ve))
- value.number_format = '#,##0.00'
- wb.save(xl_name + '.xlsx')
- wb.close()
|