Source code for andes.io.xlsx
"""
Excel reader and writer for ANDES power system parameters
This module utilizes openpyxl, xlsxwriter and pandas.Frame.
While I like the simplicity of the dome format,
spreadsheets are easier to view and edit.
"""
import logging
from andes.utils.paths import confirm_overwrite
from andes.shared import pd
logger = logging.getLogger(__name__)
[docs]def testlines(infile):
return True
[docs]def write(system, outfile, skip_empty=True, overwrite=None, add_book=None, **kwargs):
"""
Write loaded ANDES system data into an xlsx file
Parameters
----------
system : System
A loaded system with parameters
outfile : str
Path to the output file
skip_empty : bool
Skip output of empty models (n = 0)
overwrite : bool, optional
None to prompt for overwrite selection; True to overwrite; False to not overwrite
add_book : str, optional
An optional model to be added to the output spreadsheet
Returns
-------
bool
True if file written; False otherwise
"""
if not confirm_overwrite(outfile, overwrite=overwrite):
return False
writer = pd.ExcelWriter(outfile, engine='xlsxwriter')
writer = _write_system(system, writer, skip_empty)
writer = _add_book(system, writer, add_book)
writer.close()
logger.info('xlsx file written to "%s"', outfile)
return True
def _write_system(system, writer, skip_empty):
"""
Write the system to pandas ExcelWriter
"""
for name, instance in system.models.items():
if skip_empty and instance.n == 0:
continue
instance.cache.refresh("df_in")
instance.cache.df_in.to_excel(writer, sheet_name=name, freeze_panes=(1, 0))
return writer
def _add_book(system, writer, add_book):
"""
Add workbook to an existing pandas ExcelWriter
"""
if add_book is not None:
if ',' in add_book:
add_book = add_book.split(',')
else:
add_book = [add_book]
for item in add_book:
if item in system.models:
system.models[item].cache.df_in.to_excel(writer, sheet_name=item, freeze_panes=(1, 0))
logger.info('<%s> template sheet added.', item)
else:
logger.error('<%s> is not a valid model name.', item)
return writer
[docs]def read(system, infile):
"""
Read an xlsx file with ANDES model data into an empty system
Parameters
----------
system : System
Empty System instance
infile : str or file-like
Path to the input file, or a file-like object
Returns
-------
System
System instance after succeeded
"""
df_models = pd.read_excel(infile,
sheet_name=None,
index_col=None,
engine='openpyxl',
)
for name, df in df_models.items():
# drop rows that all nan
df.dropna(axis=0, how='all', inplace=True)
for row in df.to_dict(orient='records'):
system.add(name, row)
# --- for debugging ---
system.df_in = df_models
return system