12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697 |
- # coding: utf-8
- import os
- import platform
- import time
- import subprocess as sp
- from json import JSONDecodeError
- import requests
- # import ujson
- # import openpyxl as xl
- # from openpyxl.utils import get_column_letter
- import xlsxwriter as xlsw
- def file_open(file_name):
- abs_path = os.path.abspath(file_name)
- if platform.system() == 'Windows':
- sp.run(f'start {abs_path}', shell=True)
- elif platform.system() == 'Linux':
- sp.run(['xdg-open', f'{abs_path}'])
- else:
- raise NotImplementedError
- def dict_to_list(s_dict, from_ind=None):
- if from_ind is None: n_list = [s_dict[i] for i in s_dict]
- else:
- n_list = [s_dict[i] for i in s_dict]
- n_list.pop(from_ind)
- return n_list
- def insert_keys(dict_, obj, pos):
- return {k: v for k, v in (list(dict_.items())[:pos] + list(obj.items()) + list(dict_.items())[pos:])}
- def check_list(xl_name, file_name=None, list_=None, excl_=[], _service_url='https://webservicesp.anaf.ro/PlatitorTvaRest/api/v6/ws/tva'):
- if file_name:
- with open(file_name, 'r') as fiscal_codes, open("lista_cf_new.txt", "w") as fileout:
- for code in fiscal_codes:
- if code[0:2] != 'HU' and code[0:2] != 'DE' and code[0:2] != 'GB' and code[0:2] != 'EL' and code[0:2] != 'LT' and 'SK' and code not in excl_:
- fileout.write(code)
- with open("lista_cf_new.txt", "r") as newfile:
- lista_cf_ro = ' '.join(newfile).replace(',', '').split()
- if list_:
- lista_cf_ro = list_
- dest_book = xlsw.Workbook(xl_name + '.xlsm')
- dest_book.add_vba_project('./vbaProject.bin')
- w_sheet = dest_book.add_worksheet('anaf')
- my_row, my_col = 0, 0
- # headers not needed in this version
- # insert = lambda dict_, obj, pos: {k: v for k, v in (list(dict_.items())[:pos] + list(obj.items()) + list(dict_.items())[pos:])}
- print('items in list', len(lista_cf_ro))
- for code in lista_cf_ro:
- payload = [{"cui": code.replace('RO', ''), "data": time.strftime('%Y-%m-%d')}]
- response = requests.post(_service_url, json=payload, headers={'Content-Type': 'application/json'})
- try:
- resp_dict = response.json()['found'][0]
- random_keys = ['nrRegCom', 'telefon', 'fax', 'codPostal', 'act', 'stare_inregistrare']
- pos = 4
- for random_key in random_keys:
- if random_key not in resp_dict.keys():
- # resp_dict[random_key] = 'NaN'
- resp_dict = insert_keys(resp_dict, {random_key: 'Nan'}, pos)
- pos += 1
- # print(resp_dict)
- new_dict = {resp_dict['cui']: dict_to_list(resp_dict, 0)}
- for i in new_dict:
- my_row += 1
- print(i)
- w_sheet.write(my_row, 0, code)
- w_sheet.write(my_row, 1, i)
- w_sheet.write_row(my_row, 2, new_dict[i])
- except (KeyError, JSONDecodeError):
- pass
- w_sheet.write(0, my_col, 'code mentor')
- for keys in resp_dict.keys():
- w_sheet.write(0, my_col + 1, keys)
- my_col += 1
- w_sheet.set_column(3, 3, 50)
- w_sheet.autofilter(0, 0, len(lista_cf_ro), len(resp_dict.keys()))
- w_sheet.freeze_panes(1, 0)
- # w_sheet.set_column(0, 1, 25)
- dest_book.close()
- print('{0}\\data.xlsx'.format(os.getcwd()))
- # autosize all the columns in wsheet
- # dims = {}
- # data = xl.load_workbook('data.xlsx')
- # w_sheet = data.worksheets[0]
- # for row in w_sheet.rows:
- # for cell in row:
- # if cell.value:
- # dims[cell.column_letter] = max((dims.get(cell.column_letter, 0), len(str(cell.value))))
- # for col, value in dims.items():
- # w_sheet.column_dimensions[col].width = value + 1
- # data.save('data.xlsx')
- # file_open('data')
- # os.startfile('{0}/data.xlsx'.format(os.getcwd())) # or subprocess.check_call('start {0}/data.xlsx'.format(os.getcwd()), shell=True)
- # with win32 : xl = Dispatch("Excel.Application")
- # xl.Visible = True
- # xl.Workbooks.Open(Filename=os.getcwd() + '\\data.xlsx')
|