update simply xls table destroy pivot on three sheets witch use this table
Created originally on Bitbucket by pka69
I just start to use openpyxl in python.
I open a excel file with four sheets: one with data in table and three sheets with pivot use the firs one.
after save the file, when I open a file in excel the pivot tables are destroyed by excel because there are mistakes.
def ExportOrders():
fileName = "Sales orders list EX .xlsx"
filePath = os.path.join('attachments', fileName)
wb = openpyxl.load_workbook(filePath)
ws = wb.active
report_date = ws["A1"].value
fileName = "Sales orders Report.xlsx"
filePath = os.path.join('attachments', fileName)
wb_master = openpyxl.load_workbook(filePath)
ws_master = wb_master.get_sheet_by_name('Sales orders list EX ')
row = 1
ws_master["S1"].value = report_date
while ws['A' + str(row + 6)].value:
for col in "ABCDEFGHIJKLM":
ws_master[col + str(row)].value = ws[col + str(row + 6)].value
row += 1
wb.close
ws_master = wb_master.get_sheet_by_name('By Product')
pivot = ws_master._pivots[0]
pivot.cache.refreshOnLoad = True
ws_master = wb_master.get_sheet_by_name('By Customers')
pivot = ws_master._pivots[0]
pivot.cache.refreshOnLoad = True
ws_master = wb_master.get_sheet_by_name('New Orders')
pivot = ws_master._pivots[0]
pivot.cache.refreshOnLoad = True
wb_master.save(filePath)
Attachments: Sales_orders_list_EX_.xlsxSales_orders_Report.xlsx20200624-Sales_orders_Report.xlsx