Excel Input changes

[kritimathur28]

I’m working with the CashValue_SE model from lifelib and I have a question about editing Excel-based inputs.

What I want to do is:

Replace or update an existing table (for example, model_point_samples.xlsx) with a revised Excel file.
Add new tables into the model that are also read from Excel.
Edit/update the values in an existing ExcelRange so the model picks them up without having to rebuild from scratch.

Could you please guide me on the recommended approach? Specifically:

Should I use new_excel_range or new_cells_from_excel?
Is there a way to overwrite/update an existing Reference directly from a new Excel file or range?
How can I maintain the link between named ranges in Excel and the References inside the Input space?

Any example code snippets or best practices would be very helpful.

[fumitoh]

The simplest way to achieve this is to implement the inputs as Cells, not as a Reference.

Example:

https://github.com/lifelib-dev/lifelib/blob/main/lifelib/libraries/appliedlife/IntegratedLife/BaseData/__init__.py#L97

Notes:

• Place all input files outside the model (unlike CashValue_SE). In formulas, you can reference them with paths relative to the model directory via the special _model.path variable.
• Put the input Cells in a separate space from the space used for per-model-point projection; otherwise the data will be read every time a model-point run starts.
• When the source data changes, you can call the clear() method on the cells to refresh data loaded from Excel files.
• pd.read_excel() does not support Excel named ranges. Use openpyxl to read named ranges instead.

[kritimathur28]

Thank u for this.

If I update the excel tables, is there a way to auto refresh the contents of the tables in the model.

I am assuming we need to repoen the model in order to refresh the Excel inputs.

[fumitoh]

To refresh the contents read from an Excel file:

• Save the excel file
• Call clear() on the cells that read in the file contents

Then the cells reads from the file again.