Editing the Excel model inside a notebook

This notebook displays each sheet of the Excel input file. These sheets are interactive: edit them inline by clicking on the cells. Specify the excel filenames below.

In the longer term, notebooks will become the main way of specifying models and the Excel format will be depreciated.

import pandas as pd
from ipysheet import from_dataframe, to_dataframe
from openpyxl import load_workbook

excel_file = "test_file.xlsx"  # specify the name of your input excel file
book = load_workbook(excel_file)

reader = pd.ExcelWriter(excel_file, engine="openpyxl")
writer = pd.ExcelWriter(
    excel_file, engine="openpyxl", options={"strings_to_numbers": True}
)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
sheet_names = [
    "General",
    "Capacities",
    "Streams",
    "Converters",
    "Storages",
    "Time series",
    "System types",
]

dfr = []
sheetr = []
i = 0
# loop over the sheets by name and add them as dataframes by number
for current_sheet in sheet_names:
    dfr.append(pd.read_excel("test_file.xlsx", sheet_name=current_sheet))
    dfr[i] = dfr[i].fillna("")
    sheetr.append(from_dataframe(dfr[i]))
    i += 1

To save changes to a sheet, run the cell just below.

Important points to note when working with ipysheets: + ipysheets takes some time to save the changes from the edit to memory. These are then saved to the excel file from memory using the ‘save’ cell. + Review the changes to memory by rerunning the ipysheets cell again to make sure the changes persist in the sheet. Repeat if change isn’t seen. + When your change persists, run the ‘save’ cell. After executing, a DataFrame is displayed below to reflect the changes made to the excel file. + ipysheets can’t edit column headings, so we keep a blank row for this, don’t remove it.

Capacities

sheetr[1]

# Run to save changes
df_1 = to_dataframe(sheetr[1])
df_1.to_excel(
    writer, sheet_name=sheet_names[1], index=False, engine="openpyxl", na_rep=""
)
writer.save()
df_1

Streams

sheetr[2]

df_2 = to_dataframe(sheetr[2])
df_2.to_excel(
    writer, sheet_name=sheet_names[2], index=False, engine="openpyxl", na_rep=""
)
writer.save()
df_2

Converters

sheetr[3]

# Run to save changes
df_3 = to_dataframe(sheetr[3])
df_3.to_excel(
    writer, sheet_name=sheet_names[3], index=False, engine="openpyxl", na_rep=""
)
writer.save()
df_3

Storages

sheetr[4]

# Run to save changes
df_4 = to_dataframe(sheetr[4])
df_4.to_excel(
    writer, sheet_name=sheet_names[4], index=False, engine="openpyxl", na_rep=""
)
writer.save()
df_4

Time Series

sheetr[5]

# Run to save changes
df_5 = to_dataframe(sheetr[5])
df_5.to_excel(
    writer, sheet_name=sheet_names[5], index=False, engine="openpyxl", na_rep=""
)
writer.save()
df_5