class Excel(object):
"""A utility class to make it easier to get at Excel and manage
a single (1) running instance from one class."""
def __init__(self, **kwargs):
"""
xlApp: instacne of the excel application
xlBook: the active excel workbook
"""
visible = kwargs.get("visible", True) # The state of the current xl window
self.number_sheets = 1 # By default excel will open with one sheet
AppName = 'Excel.Application'
self.xlApp = None # The window frame
self.xlBook = None # The name of te file open inside the window (there can be many)
self.filename = None
if kwargs.get('runninginstance'):
# We have to attach to an existing application instance
try:
filename = kwargs.get('filename')
# GetObject(Class='Excel.Application') will get the first Excel instance over and over again
self.xlBook = GetObject(filename) #will return a running instance of the file/program (if exists), else starts a new instance
#self.xlApp = GetObject(Class=AppName)
self.xlApp = kwargs.get('instance', self.xlBook.Application) # The only way to access the parent instnace is to get it from the proccess that started it
except TypeError:
print "Could not attach to any %s" % AppName
return
except:
# Produces a com_error if the file is not avliable
self.xlApp = GetObject(Class=AppName)
self.xlBook = self.xlApp.ActiveWorkbook
else:
try:
if kwargs.get('newinstance'):
# Create a new instance of the application
if kwargs.get('earlybinding') or kwargs.get('staticproxy'):
self.xlApp = EnsureDispatch(AppName)
else:
self.xlApp = DispatchEx(AppName) # Will open a read only copy, if the file is already open
if kwargs.get('filename'):
try:
self.filename = kwargs.get('filename')
self.xlBook = self.xlApp.Workbooks.Open(self.filename)
except:
# The file does not exist
pass
#self.addWorkbook() # Binds a new xl workbook
#self.xlBook = self.xlApp.ActiveWorkbook
else:
pass
#Handled by line: "if kwargs.get('filename'):" for more details
#self.xlApp = Dispatch(AppName)
except TypeError:
print "Could not dispatch %s" % AppName
return
if kwargs.get('filename') and self.xlBook is None:
self.filename = kwargs.get('filename')
try:
#self.xlBook = self.xlApp.Workbooks.Open(self.filename)
self.xlBook = GetObject(self.filename) # This looks like it is opening new files in an existing instance regardless of the newinstance variable being set
self.xlApp = self.xlBook.Application
#self.xlApp.Windows(self.xlBook.Name).Visible = kwargs.get("BookVisible", True) # The visibility of the workbook in the instace of excel
except com_error:
# The file does not exist...
if self.xlApp:
# A new instance of excel was launched, but it has not loaded any workbooks, so ActiveWorkbook is None
self.xlApp.Workbooks.Add()
self.xlBook = self.xlApp.ActiveWorkbook
else:
# A new instance was not started, and the file doesn't exist, so start a new workbook in the running instace
self.xlApp = Dispatch(AppName)
self.xlApp.Workbooks.Add() # Binds a new xl workbook
self.xlBook = self.xlApp.ActiveWorkbook
elif self.xlApp is None:
self.xlApp = Dispatch(AppName)
self.xlApp.Workbooks.Add() # Binds a new xl workbook
self.xlBook = self.xlApp.ActiveWorkbook
# Launch the iHistorian Excel plug-in separately (no plug-ins are launched with the Excel COM object)
try:
#self.launchiHistorian() <-- there are problems loading the plugin for the latest V7 Proficy Hist
pass
except:
pass
self.xlApp.Windows(self.xlBook.Name).Visible = kwargs.get("BookVisible", True) # The visibility of the workbook in the instace of excel
self.xlApp.Visible = visible
self.FinalTests()
# (win32com.client)
# Dispatch
# Starts a new program process, but doesn't neccessarialy create a instance of the application
# DispatchEx
# Starts a new instance of the program (allows you to specify if you want the process on this machine or another one, in/out of proccess etc.)
# Historian add-in not available
# gencache.EnsureDispatch
# Ensures a static proxy exists by either creating it or returning an existing one
# Use in place of Dispatch() if you always want early binding
# GetObject(filename, Class="")
# Attaching to an existing application
# GetObject attaches to the last opened instance (using GetActiveObject), else starts a new instance (if no other instances of the program e.g. excel are open, else it binds to an existing excel instance)
def __repr__(self):
filename = self.filename
if not self.filename:
filename = "Excel: " + str(self.xlApp)
return filename
def launchiHistorian(self):
"""This is for loading the GE Proficy Historian Excel Add-in"""
iHistorianPath = r"C:\Program Files (x86)\Microsoft Office\Office14\Library\iHistorian.xla"
self.xlApp.DisplayAlerts = False
assert os.path.exists(iHistorianPath) == True, "Error: There was a problem locating the iHistorian.xla Excel plug-in."
self.xlApp.Workbooks.Open(iHistorianPath)
self.xlApp.RegisterXLL(iHistorianPath)
self.xlApp.Workbooks(iHistorianPath.split("\\")[-1]).RunAutoMacros = True
self.xlApp.DisplayAlerts = True
def FinalTests(self):
"""Ensure a static proxy exists"""
try:
c.xlDown
except:
from win32com.client import makepy
sys.argv = ["makepy", r"C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe"]
with Capturing() as printoutput:
makepy.main()
if len(printoutput):
if printoutput[0].startswith("Could not locate a type library matching"):
sys.argv = ["makepy", r"C:\Program Files (x86)\Microsoft Office\Office16\Excel.exe"]
with Capturing() as printoutput:
makepy.main()
if len(printoutput):
if printoutput[0].startswith("Could not locate a type library matching"):
sys.argv= [""]
print "Choose: Microsoft Excel 14.0 Object Library (1.7), or Excel 16.0 Object Library (1.9)"
with Capturing() as printoutput:
makepy.main()
if len(printoutput):
if printoutput[0].startswith("Could not locate a type library matching"):
print "Could not create a win32 proxy for Excel. Stopping now."
time.sleep(5)
sys.exit()
def save(self, newfilepath=None):
IntialAlertState = self.xlApp.DisplayAlerts
self.xlApp.DisplayAlerts = False
if newfilepath:
self.filename = newfilepath
directory = os.path.dirname(newfilepath)
if not os.path.exists(directory):
os.makedirs(directory)
self.xlBook.SaveAs(newfilepath)
else:
if os.path.exists(self.filename):
#os.remove(self.filename)
self.xlBook.Save()
else:
directory = os.path.dirname(self.filename)
if not os.path.exists(directory):
os.makedirs(directory)
self.xlBook.SaveAs(self.filename)
self.xlApp.DisplayAlerts = IntialAlertState # Probably be set back to true, but maybe not
def closeWorkbook(self, SaveChanges=0):
self.xlApp.Windows(self.xlBook.Name).Visible = True
self.xlBook.Close(SaveChanges) # Avoids a prompt when closing out
#self.xlApp.Quit()
#self.xlApp.Windows(self.xlBook.Name).Visible = 0
self.xlBook = None
del self.xlBook # Free-up memory
def closeApplication(self):
"""Closes the entire window/running instance,
including any open workbooks."""
numberOfBooks = 0
for book in self.xlApp.Windows:
numberOfBooks += 1
self.xlApp.Windows(self.xlBook.Name).Visible = True
book.Close(SaveChanges=0)
#self.xlBook.Close(SaveChanges=0) # Avoids a prompt when closing out
self.xlApp.Visible = 0 # Must do this, else the excel.exe process does not quit
self.xlApp.Quit()
self.xlApp = None
del self.xlApp # Free-up memory
def closeInstance(self):
"""Closes the currently attached instanace, but leaves the window running"""
self.xlApp = None
del self.xlApp
def SaveAsWebPage(self, sheetname, filepath):
"""Saves a single Excel sheet as a web page. Usefull for extracting charts as images"""
if not os.path.exists(filepath):
os.mkdir(filepath)
Publisher = self.xlBook.PublishObjects.Add(c.xlSourceSheet,
os.path.join(filepath, sheetname+".htm"),
sheetname, "", c.xlHtmlStatic, "Main name", "")
Publisher.Publish(True) # Setting True overwrites any existing published web page
class Usc:
"""
Survival mini-wrapper for a Unisim case
"""
def __init__(self, fname):
self.case = GetObject(os.path.abspath(fname))
self.fname = self.case.FullName
self.integrator = self.case.solver.Integrator
self.solver = self.case.solver
self.current_time = self.case.solver.Integrator.currenttimevalue
self.path = self.case.path
self.flsh = self.case.flowsheet
self.stream_names = [self.flsh.Streams[i]() for i in self.flsh.Streams]
self.streams = self.flsh.Streams
self.ops = [self.flsh.Operations[i]() for i in self.flsh.Operations]
self.stripcharts = {}
self.profiles = {}
self.pipes = {}
def extract_stripchart(self, stripchart='overall', expose_data=True):
"""
Extract a specific stripchard and exposes the data in the namespace
"""
csv_fname = self.fname.split(os.sep)[-1].replace(".usc", ".csv")
scp_fname = self.fname.split(os.sep)[-1].replace(".usc", ".SCP")
case_details = {
'case': self.fname.__repr__()[1:-1],
'stripchart': stripchart,
'target': self.path.__repr__()[1:-1] + csv_fname
}
script = STRIPCHART_EXTRACTION_TEMPLATE.substitute(case_details)
with open(self.path + scp_fname, 'w') as fobj:
fobj.write(script)
self.case.visible = True
self.case.application.playscript(self.path + scp_fname)
self.case.visible = False
os.remove(self.path + scp_fname)
if expose_data is True:
self.stripcharts[stripchart] = unisim_csv_formatting(
self.path, csv_fname)
if os.path.isdir(self.path + 'trends') is not True:
os.mkdir(self.path + 'trends')
shutil.copy(self.path + csv_fname,
self.path + 'trends\\{}.csv'.format(stripchart))
os.remove(self.path + csv_fname)
def extract_profiles(self, pipeline_name, expose_data=True):
"""
Extract all the profiles of a specific pipeline and exposes the data
in the namespace
"""
compas_pipe = self.__profile_definition(pipeline_name)
get_variable = compas_pipe.GEtUserVariable
if os.path.isdir(self.path + 'profiles') is not True:
os.mkdir(self.path + 'profiles')
target_dir = self.path + 'profiles'
if expose_data is True:
self.profiles[pipeline_name] = {}
for key in PROFILE_KEYS:
pipe = self.pipes[pipeline_name]
pipe['data'][key] = get_variable(PROFILE_KEYS[key]).Variable()
temp = {key: val for (key, val) in enumerate(pipe['data'][key])}
try:
data = pd.DataFrame(temp, index=pipe['grid'])
except ValueError:
data = pd.DataFrame(temp, index=pipe['non_st_grid'])
data.columns = self.pipes[pipeline_name]['timesteps']
data.to_csv('{}/{}-{}.csv'.format(target_dir, pipeline_name, key))
if expose_data is True:
self.profiles[pipeline_name][key] = data
def __profile_definition(self, pipeline_name):
"""
Prepare the profiles extraction from a specific profile
"""
pipe = self.flsh.Operations[pipeline_name]
x_st = pipe.GetUserVariable(PROFILE_LENGTH_ST).Variable()
x_non_st = pipe.GetUserVariable(PROFILE_LENGTH_NON_ST).Variable()
timesteps = pipe.GetUserVariable(PROFILE_TIME).Variable()
self.pipes[pipeline_name] = {
'grid': x_st,
'non_st_grid': x_non_st,
'timesteps': timesteps,
'data': {}
}
return pipe
def run_until(self, endtime, timeunit='minutes', save=True):
"""
Run a case untile the specifiend endtime
"""
integrator = self.case.solver.Integrator
integrator.rununtil(endtime, timeunit)
if save is True:
self.case.save()
def save(self, fname=''):
"""
Save the current case
"""
if fname is '':
self.case.save()
else:
self.case.SaveAs(self.path + os.sep + fname)
def close(self):
"""
Close the current case
"""
self.case.close()
