1

There are a few suggested solutions to the problem of modifying an already existing sheet in an excel file that contains several sheets: How to save a new sheet in an existing excel file, using Pandas? and https://github.com/pandas-dev/pandas/issues/3441, 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? 

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)
ic_fl2
  • 831
  • 9
  • 29
  • do you want to completely replace one sheet, preserving other sheets or to change some rows/cells in one sheet, preserving all the rest? – MaxU - stand with Ukraine Jan 14 '18 at 18:03
  • you may want to check [this solution](https://stackoverflow.com/a/47740262/5741205) – MaxU - stand with Ukraine Jan 14 '18 at 18:08
  • @MaxU Yes, as an initial step, replacing one sheet with the content of another data frame is sufficient. The answer you linked to solves a related problem, that the code I suggest also solves, by having a new name for a sheet. But I believe there should be a more efficient way of solving this problem. Might be worth raising an issue if no one has an idea on how to solve it. – ic_fl2 Jan 16 '18 at 07:43

1 Answers1

0

In an already existing worksheet of an workbook with multiple sheets, you can use xlwings to insert the worksheet that has been modified with pandas. See the following example. First, clear the particular worksheet with .clear() and then insert the modified dataframe to that worksheet.

import xlwings as xw
import pandas as pd

filename = "test.xlsx"

df = pd.read_excel(filename, "SheetX")

# Do your modifications of the worksheet here. For example, the following line "df * 2".
df = df * 2 

app = xw.App(visible=False)
wb = xw.Book(filename)
ws = wb.sheets["SheetX"]

ws.clear()

# This is the important line here, which inserts the dataframe to the particular worksheet.
ws["A1"].options(pd.DataFrame, header=1, index=False, expand='table').value = df

# If formatting of column names and index is needed as xlsxwriter does it, the following lines will do it (if the dataframe is not multiindex).
ws["A1"].expand("right").api.Font.Bold = True
ws["A1"].expand("down").api.Font.Bold = True
ws["A1"].expand("right").api.Borders.Weight = 2
ws["A1"].expand("down").api.Borders.Weight = 2

wb.save(filename)
app.quit()
mouwsy
  • 1,457
  • 12
  • 20