intrastat/tools/parser_intrastat.py

import openpyxl as xl
from openpyxl.styles import (Font, NamedStyle)

def header_create(sheet, headers=[]):
    if len(headers) == 0:
        headers = ['DATA', 'FURNIZOR', 'CF', 'CountryOfOrigin', 'EXP', 'DeliveryTermsCode',
                   'ACode', 'Bcode', 'TransportCode', 'INVNR', 'VSCODE', 'NAME', 'GROUP',
                   'UM', 'Q', 'PRICE', 'NETWEIGHT', 'NETVALUE', 'SUPPL', 'SUPPLVALUE',
                   'Ppacking', 'Spacking', 'Paper', 'Plastic', 'Wooden']
    for n, h in enumerate(sheet.iter_cols(min_row=1, max_col=len(headers), max_row=1)):
        h[0].value = headers[n]
    for f in sheet['A1:Y1'][0]:
        f.font = Font(bold=True)

def format_after(sheet, ref=None):
    date_style = NamedStyle(name='datetime', number_format='DD/MMM/YYYY')
    if not ref:
        ref = f'A1:Y{sheet.max_row}'
    sheet.freeze_panes = 'A2'
    sheet.auto_filter.ref = ref
    for cell in sheet.iter_rows(min_row=2, max_col=1, max_row=sheet.max_row):
        cell[0].style = date_style

def parse_intrastat(articles, export, partners):
    ib = xl.load_workbook(articles)
    isheet = ib.worksheets[0]
    cb = xl.load_workbook(export)
    csheet = cb.worksheets[0]
    pb = xl.load_workbook(partners)
    psheet = pb.worksheets[0]
    xml_book = xl.Workbook()
    xml_book.create_sheet('intrastat', 0)
    xml_sheet = xml_book.worksheets[0]
    header_create(xml_sheet)
    for n, p in enumerate(csheet.iter_rows(min_row=2, max_col=14, max_row=csheet.max_row)):  # .max_row - 1
        xml_sheet[f'A{n+2}'] = p[4].value
        xml_sheet[f'B{n+2}'] = p[0].value
        xml_sheet[f'L{n+2}'] = p[9].value
        xml_sheet[f'N{n+2}'] = p[11].value
        xml_sheet[f'O{n+2}'] = p[10].value
        xml_sheet[f'P{n+2}'] = p[12].value
        xml_sheet[f'R{n+2}'] = p[13].value

        for c in psheet.iter_rows(min_row=2, max_col=9, max_row=psheet.max_row):
            if xml_sheet[f'B{n+2}'].value == c[1].value:
                xml_sheet[f'C{n+2}'] = c[2].value
                xml_sheet[f'D{n+2}'] = c[3].value
                xml_sheet[f'E{n+2}'] = c[4].value
                xml_sheet[f'F{n+2}'] = c[5].value
                xml_sheet[f'G{n+2}'] = c[6].value
                xml_sheet[f'H{n+2}'] = c[7].value
                xml_sheet[f'I{n+2}'] = c[8].value

        for i in isheet.iter_rows(min_row=2, max_col=10, max_row=isheet.max_row):
            if xml_sheet[f'L{n+2}'].value == i[1].value:
                xml_sheet[f'K{n+2}'] = i[3].value
                xml_sheet[f'Q{n+2}'] = i[9].value * xml_sheet[f'O{n+2}'].value
    format_after(xml_sheet)
    xml_book.save('intrastat.xlsx')


__all__ = ['xl', 'header_create', 'format_after', 'parse_intrastat']

if __name__ == '__main__':
    parse_intrastat('intrastat_art.xlsx', 'Export.xlsx', 'intrastat_part.xlsx')