1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 |
- 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')
|