Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import openpyxl
- area = {"PBT Couriers Napier Depot": "NAPIER", "PBT Couriers Hamilton Depot": "HAMILTON", "PBT Couriers Auckland West Depot": "AUCKLAND", "PBT Couriers Whangarei Depot": "WHANGAREI", "PBT Couriers Auckland Central Depot": "AUCKLAND", "PBT Couriers Christchurch Depot": "CHRISTCHURCH"}
- reportOnline = input("Input file (Online PBT report: ")
- reportAccounts = input("Input destination accounts file: ")
- # opens the online workbook
- workbookOnline = load_workbook(filename=reportOnline, read_only=True)
- sheetOnline = workbookOnline.active
- # insert lines
- sheetOnline.delete_rows(idx=1, amount=1)
- totalitems = len(sheetOnline["A"])
- # reorder online report
- sheetOnline.move_range("S:S", rows=0, cols=-13)
- for to in sheetOnline.iter_cols(min_row=1, min_col = 6, max_col=6, values_only=True):
- cell.value = area[to]
- sheetOnline.move_range("B:B", rows=0, cols=5)
- sheetOnline.move_range("D:D", rows=0, cols=-2)
- sheetOnline.move_range("C:C", rows=0, cols=1)
- sheetOnline.move_range("A:A", rows=0, cols=2)
- sheetOnline.move_range("E:E", rows=0, cols=-4)
- for date in sheetOnline.iter_cols(min_row=1, min_col=1, max_col=1, values_only=True):
- cell.value = "=TEXT(" + date + ", 'dd/mm/yyyy/'")
- sheetOnline.move_range("H:H", rows=0, cols=-3)
- sheetOnline.move_range("I:I", rows=0, cols=-1)
- sheetOnline.move_range("L:M", rows=0, cols=-3)
- for kl in sheetOnline.iter_cols(min_row=1, min_col=11, max_col=12, values_only=True):
- cell.value = 0
- sheetOnline.save(reportOnline)
- #opens the accounts workbook
- workbookAccounts = load_workbook(filename=reportAccounts, read_only=True)
- sheetAccounts = workbookAccounts.active
- sheetAccounts.insert_rows(idx=4, amount=totalitems)
- maxRow = sheetAccounts.max_row
- minRow = sheetAccounts.min_row
- for i in range(1, maxRow + 1):
- for j in range(1, minRow + 1):
- c = sheetOnline.cell(row = i, column = j)
- sheetAccounts(row = i+1, column = j).value = c.value
- sheetAccounts.save(str(reportAccounts))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement