123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197 |
- from xml.etree import ElementTree as ET
- import xlsxwriter as xlsw
- import openpyxl as xl
- from .declaration import Declaration
- class D390(Declaration):
- def __init__(self,
- cui=None,
- den=None,
- addr=None,
- month=None,
- year=None,
- control=None,
- decl_name=None,
- decl_func=None,
- rect=None,
- root=None,
- n_operations=None,
- bazaL=None,
- bazaT=None,
- bazaA=None,
- bazaP=None,
- bazaS=None,
- bazaR=None,
- total_baza=None,
- decl_type=None):
- super().__init__(cui=cui,
- den=den,
- addr=addr,
- month=month,
- year=year,
- control=control,
- decl_name=decl_name,
- decl_func=decl_func,
- rect=rect,
- root=root,
- decl_type=decl_type)
- self.n_operations = n_operations,
- self.bazaL = bazaL,
- self.bazaT = bazaT,
- self.bazaA = bazaA,
- self.bazaP = bazaP,
- self.bazaS = bazaS,
- self.bazaR = bazaR,
- self.total_baza = total_baza
- def from_xml(self, fp):
- xml = ET.parse(fp)
- root = xml.getroot()
- # self.root = root
- decl_type = root.tag.split("}", 1)[-1]
- return D390(cui=root.attrib['cui'],
- den=root.attrib['den'],
- addr=root.attrib['adresa'],
- month=int(root.attrib['luna']),
- year=int(root.attrib['an']),
- rect=int(root.attrib['d_rec']),
- control=int(root.attrib['totalPlata_A']),
- n_operations=int(root[0].attrib['nrOPI']),
- bazaL=int(root[0].attrib['bazaL']),
- bazaT=int(root[0].attrib['bazaT']),
- bazaA=int(root[0].attrib['bazaA']),
- bazaP=int(root[0].attrib['bazaP']),
- bazaS=int(root[0].attrib['bazaS']),
- bazaR=int(root[0].attrib['bazaR']),
- total_baza=int(root[0].attrib['total_baza']),
- root=root,
- decl_type=decl_type)
- def from_xml_all(self, fp):
- xml = ET.parse(fp)
- root = xml.getroot()
- op_list = []
- for ch in root:
- if 'tip' in ch.attrib:
- try:
- d = {
- root.attrib['cui']: [
- int(root.attrib['luna']),
- int(root.attrib['an']),
- ch.attrib['tip'],
- ch.attrib['tara'],
- ch.attrib['codO'],
- ch.attrib['denO'],
- ch.attrib['baza']
- ]
- }
- op_list.append(d)
- except KeyError:
- pass
- self.op_list = op_list
- return self.op_list
- @property
- def data(self):
- data = {
- self.cui:
- [
- self.den[0], self.addr[0], self.month[0], self.year[0],
- self.rect, self.control[0], self.n_operations[0],
- self.bazaT[0], self.bazaL[0], self.bazaA[0],
- self.bazaP[0], self.bazaS[0], self.bazaR[0], self.total_baza
- ]
- }
- self.dict_data = data
- return self.dict_data
- @property
- def headers(self):
- header = [h for h in self.root.attrib.keys()]
- ops = [h for h in self.root[0].attrib.keys()]
- del(ops[0])
- header.extend(ops)
- self.header = header
- return self.header
- 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 = D390() # 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()
|