python - 截断，从一个 xlsx 复制并使用 openpyxl 粘贴到另一个：弄乱所有文本框、图表等

Question

我尝试在 python 3.7.3 中自动化我每周必须执行的一些步骤。基本上，这都是关于

• 截断 xlsx-1
• 从 xlsx-2 复制到截断的 xlsx-1
• 保存 xlsx-1

一切顺利，但在这些步骤之后，我的 xlsx-1 都搞砸了。所有公式仍在工作，但所有文本框（其中有和没有单元格引用）都消失了，所有图表都有不同的格式（例如新边框）。

为什么会发生这种情况，我可以做些什么来避免这些问题？

print("Please type in the date first (format: YYMMDD) > ")

last_wk = dt.date.today().isocalendar()[1] - 1
curr_wk = last_wk + 1

prefix = f"{input()}_CW{str(last_wk)}"
prefix_final = f"{input()}_CW{str(curr_wk)}"

p_basic = 'C:\\Users\\Don_Andrej\\OneDrive - Shared Services GmbH\\Profile\\Desktop\\180816_Start\\Reports'
p_export = os.path.join(p_basic, '_process_BE_NL_BRE_exports', f"{prefix}")
p_final = os.path.join(p_basic, '_process_BE_NL_BRE_exports', f"{prefix_final}")

files = ['Offline vs Online sales week', 
        'Gefactureerd week',
        'BE_onlineshopSales_oldOrder']

def truncate():
    for file in files:
        p = p_export + f"_{file}.xlsm"

        wb = opx.load_workbook(filename = p)
        ws = wb['rawData']

        for all_row in range(1, ws.max_row + 1):
            for all_col in range(1, ws.max_column + 1):
                ws.cell(row = all_row, column = all_col).value = None

        wb.save(p)
        wb.close()
        print(f"{file} has been truncated!")

def copy_paste():
    for file in files:
        p_from = p_export + f"_EXPORT_{file}.xlsx"
        p_to = p_export + f"_{file}.xlsx"

        wb_from = opx.load_workbook(filename = p_from)
        ws_from = wb_from['Sheet1']

        wb_to = opx.load_workbook(filename = p_to)
        ws_to = wb_to['rawData']


        for all_row in range(1, ws_from.max_row + 1):
            for all_col in range(1, ws_from.max_column + 1):
                ws_to.cell(row = all_row, column = all_col).value = ws_from.cell(row = all_row, column = all_col).value

        wb_to.save(p_to)
        wb_to.close()
        wb_from.close()
        print(f"The export were copied into {file}")


truncate()
copy_paste()

标签： pythonexcelpython-3.xopenpyxl