I'm trying to create a simple GUI, the purpose is automating some tasks in Excel, before applying those modifications, I can easily open the file in which I'm applying the updates, but after applying the modifications, I get an Excel warning that says the file may be corrupted or something like that as you can see in this screenshot https://prnt.sc/0OAwLe4c82x5 , so the issue is with the saving part of the code, but the weird thing is that after applying the modifications and trying to print the df.head(), python get it easily, but me I can't open the file, I suspect this can be the responsibility of the issue https://prnt.sc/kufVB0fKXZqY , this protected mode is preventing me from editing the file, I'll share only the code responsible of the updates for not confusing you:
import pandas as pd
import pyxlsb
import openpyxl
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import datetime as dt
import warnings
def func(filepath):
#Step 1:
df_ordering = pd.read_excel(filepath, sheet_name='lastest planning')
df_ordering_v2 = df_ordering.iloc[:, :10].copy()
df_ordering_v2 = df_ordering_v2.iloc[0:0]
df_edmand = pd.read_excel('C:/Users/dell/Desktop/gui_driss/e_Demand_A400M_DeliverySequence_W47_2022_official.xlsb', engine='pyxlsb', skiprows=[0, 1])
df_edmand['ODD Target'] = df_edmand['ODD Target'].apply(lambda x: dt.datetime(1900, 1, 1) + dt.timedelta(days=x))
df_edmand['ODD Target'] = df_edmand['ODD Target'].dt.strftime('%d/%m/%Y')
df_edemand_resized = df_edmand.loc[:,['Key', 'FAL Rank', 'MSN', 'VB Number', 'SAP Material', 'ODD Target','TR CUT', 'TR DEL']].copy()
df_edemand_resized = df_edemand_resized.rename(columns={'Key': 'clé', 'VB Number':'VB / VU / WP', 'ODD Target': 'ODD target', 'TR CUT': 'TR CUT '})
df_combined = pd.concat([df_ordering_v2, df_edemand_resized],ignore_index=True)
df_ordering = df_combined
return df_ordering
def func2(filepath):
warnings.filterwarnings("ignore")
df_planning_conf = pd.read_excel(filepath, sheet_name='planningconf')
df_planning_conf_copy = df_planning_conf.iloc[:,:10]
df_planning_conf_copy = df_planning_conf_copy.iloc[0:0]
df = func(filepath)
df_planning_conf_copy['clé'] = (df['VB / VU / WP'] + df['MSN'].astype(str)).str.replace('\s+','')
df_planning_conf_copy['HARNAIS'] = df['VB / VU / WP']
df_planning_conf_copy['MSN'] = df['MSN']
df_planning_conf_copy['lastest planning input'] = df['ODD target']
df_planning_conf_copy['lastest planning TRDEL'] = df['TR DEL']
df_ordering_lol = pd.read_excel(filepath, sheet_name='ORDERING', skiprows=[0])
temp_df = pd.merge(df_planning_conf_copy, df_ordering_lol, left_on = 'clé', right_on='Colonne2', how='inner')
df_planning_conf_copy['ODD'] = temp_df['ODD_y'].dt.strftime('%d/%m/%Y')
df_planning_conf_copy['ODD'] = df_planning_conf_copy['ODD']
df_planning_conf_copy['TRDEL'] = temp_df['TR livraison (pour JC D2D3)']
df_planning_conf_copy['TRDEL'] = df_planning_conf_copy['TRDEL'].astype(int)
df_planning_conf_copy['ODD diff'] = 'No'
df_planning_conf_copy['TRDEL diff'] = 'No'
for i, row in df_planning_conf_copy.iterrows():
if row['ODD'] != row['lastest planning input']:
df_planning_conf_copy.loc[i, 'ODD diff'] = 'Yes'
elif row['TRDEL'] != row['lastest planning TRDEL']:
df_planning_conf_copy.loc[i, 'TRDEL diff'] = 'Yes'
with pd.ExcelWriter(filepath, mode = 'w', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='lastest planning',index=False)
df_planning_conf_copy.to_excel(writer, sheet_name='planningconf',index=False)
print(df_planning_conf_copy.head())
print(df_planning_conf_copy.iloc[1189])
#df.to_excel('A400M_JIG_orderingARP_modified.xlsx',sheet_name='lastest planning',index=False)
#df_planning_conf_copy.to_excel('A400M_JIG_orderingARP_modified.xlsx', sheet_name='planningconf',index=False)
return df_planning_conf_copy
#func2('C:/Users/dell/Desktop/gui_driss/A400M_JIG_orderingARP.xlsm')
df_planning_conf = pd.read_excel('C:/Users/dell/Desktop/gui_driss/A400M_JIG_orderingARP.xlsm', sheet_name='planningconf')
print(df_planning_conf.head())