title: Modifying an excel sheet in a excel book with pandas
body: <p>There are a few suggested solutions to the problem of modifying an already existing sheet in an excel file that contains several sheets:
  <a href="https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas">How to save a new sheet in an existing excel file, using Pandas?</a> and <a href="https://github.com/pandas-dev/pandas/issues/3441" rel="nofollow noreferrer">https://github.com/pandas-dev/pandas/issues/3441</a>, but neither of the proposed solutions actually work. They either add extra sheets of replace all other sheets. I've made the function below that does the trick but it is very roundabout and pretty bad way to solve the problem. Are there any ideas on how to do this better? </p>
  
  <pre><code>def modify_sheet(file_name,sheet_to_change,new_sheet):
      """Add or modify sheets in an excel book
  
      Args:
          file_name (str): Name of the xlsx file
          sheet_to_change (str): Name of the sheet in question
          new_sheet (pd.DataFrame): The new dataframe
      """
      with pd.ExcelWriter(file_name, engine='xlsxwriter') as writer:
          df = pd.read_excel(file_name, sheetname=None, header=None, skiprows=0)
          if sheet_to_change not in df.keys():
              keys = list(df.keys())
              keys.append(sheet_to_change)
          else:
              keys = df.keys()  
  
          for key in keys:
              if sheet_to_change == key:
                  new_sheet.to_excel(writer, sheet_name=str(key), header= False, index =False)
              else:
                  df[key].to_excel(writer, sheet_name=str(key), header= False, index =False)
  
          writer.close()
          writer.save()
  
  
  file_name = "blub.xlsx"
  sheet_to_change = 'DKSheet1'
  new_sheet = pd.DataFrame(np.random.rand(20,6))
  modify_sheet(file_name,sheet_to_change,new_sheet)
  </code></pre>