Overwrite sheets saving and the other sheets on excel

user_19282139 0
Viewed 35

i made a script that compare datas form diferent sheets, all godd, now i want to add this updates sheet instead of the old one on the entire excel and keeping the other sheets.

import numpy as np
import pandas as pd
from timestampdirectory import  createdir
import openpyxl
from openpyxl import workbook
from openpyxl import worksheet
import os
import time
def svnanalysis():

    dest = createdir()
    dfSvnUsers = pd.read_excel(os.path.join(dest, "SvnUsers.xlsx"))
    dfSvnGroupMembership = pd.read_excel(os.path.join(dest, "SvnGroupMembership.xlsx"))
    dfSvnRepoGroupAccess = pd.read_excel(os.path.join(dest, "SvnRepoGroupAccess.xlsx"))
    dfsvnReposSize = pd.read_excel(os.path.join(dest, "svnReposSize.xlsx"))
    dfsvnRepoLastChangeDate = pd.read_excel(os.path.join(dest, "svnRepoLastChangeDate.xlsx"))
    dfUserDetails = pd.read_excel(r"D:\GIT-files\Automate-Stats\SVN_sample_files\CM_UsersDetails.xlsx")

    timestr = time.strftime("%Y-%m-%d-")
    xlwriter = pd.ExcelWriter(os.path.join(dest,f'{timestr}Usage-SvnAnalysis.xlsx'))

    dfUserDetails.to_excel(xlwriter, sheet_name='UserDetails',index = False)
    dfSvnUsers.to_excel(xlwriter, sheet_name='SvnUsers', index = False )
    dfSvnGroupMembership.to_excel(xlwriter, sheet_name='SvnGroupMembership', index = False )
    dfSvnRepoGroupAccess.to_excel(xlwriter, sheet_name='SvnRepoGroupAccess', index = False)
    dfsvnReposSize.to_excel(xlwriter, sheet_name='svnReposSize', index = False)
    dfsvnRepoLastChangeDate.to_excel(xlwriter, sheet_name='svnRepoLastChangeDate',index= False)
    xlwriter.close()

whats above its in the same script where i used some xlsx files an create only 1 xlsx with those files as sheets, now below i make some changes in SvnUser sheet and i want to upload it on the excel instead of old sheet SvnUser, and keep the other sheets

   # xlwriter = pd.ExcelWriter(os.path.join(dest, f'{timestr}Usage-SvnAnalysis.xlsx'))
    svnUsers = pd.read_excel(os.path.join(dest,f'{timestr}Usage-SvnAnalysis.xlsx'), sheet_name="SvnUsers")
    details = pd.read_excel(os.path.join(dest,f'{timestr}Usage-SvnAnalysis.xlsx'), sheet_name="UserDetails")
    svnUsers = svnUsers.assign(SVNaccount=svnUsers["accountName"].isin(details["Account"]).astype(bool))

    print(svnUsers)
   # dfSvnUsers.to_excel(xlwriter, sheet_name='SvnUsers', index = False )

   # xlwriter.close()
openpyxl pandas pycharm openxml python
1 Answers

The easiest way to achieve that would be to overwrite the entire excel sheet, for example like this:

import pandas as pd

# create dataframe from excel file
df = pd.read_excel(
    '2022-06-15-Usage-SvnAnalysis.xlsx',
    engine='openpyxl',
    sheet_name='UserDetails'
)
res_df = ..... # calculate the df you want to write
# overwrite excel sheet with dataframe
with pd.ExcelWriter(
    '2022-06-15-Usage-SvnAnalysis.xlsx',
    engine='openpyxl',
    mode='a',
    if_sheet_exists='replace'
) as writer:
    res_df.to_excel(writer, sheet_name='UserDetails')
user_162737600
Related