How to interact with Excel from Mechanical (or other applications)
See the code below and copy/paste into a .py file.
"""
CodePath="<PathToCode>"
import sys
if not CodePath in sys.path:
sys.path.append(CodePath)
import ExcelManager; reload(ExcelManager)
from ExcelManager import *
XLManager = MsExcelManager()
XLManager.GetApp(CreateNew=True, Visible=True).OpenWorkbook()
XLManager.SetCellValue(1,1,"Hi There")
XLManager.SetCellValue(1,2,123.205)
print XLManager.GetCellValue(1,2)
Rng = XLManager.GetRangeByRowCol(1,1,1,2)
Vals = GetRangeValues(Rng)
print Vals
"""
#region imports and global
import clr
clr.AddReference("Microsoft.Office.Interop.Excel")
import Microsoft.Office.Interop.Excel as Excel
from System.Runtime.InteropServices import Marshal
import os
#endregion
class MsExcelManager:
"""
Helps to communicate and do things with MS Excel program
"""
def __init__(self):
self.App = None #The instance of the Excel application
self.StatusReportControl=None #Optional: Control used to report the status of what action is going on.
self.Log="" #Use this to keep a log of actions.
self.Status="Ready" #Use this as an internal status to know what is going on.
def GetApp(self,CreateNew=False,Visible = False):
"""
Gets the Excel Application. Opens a new application instance
Args: (CreateNew=False,visible = False)
CreateNew: if True, open a new instance of Excel. If False, try to get a currently open session.
visible: Determines if the program should be visible or not.
"""
#If the app is open it gets the current instance, and if not it opens it
App = None
if CreateNew==False:
#If you have anything as the "App" variable then use that.
if self.App!=None:
return self.App
else:
try:App = Marshal.GetActiveObject("Excel.Application") #If you dont have anything try to get an app that is open
except:pass
#If you still have nothing, try to make a new instance of the Excel program.
if App==None: App = Excel.ApplicationClass()
else: #CreateNew: True
App = Excel.ApplicationClass() #Open a new instance of application
try:
if Visible: App.Visible=True #Set visibility
except:pass
self.App = App #Set the class variable "App" to what you got from this method.
return App
def CloseApp(self):
"""
Closes the application
"""
try: self.App.Quit()
except:pass
self.App=None
def OpenWorkbook(self,FilePath):
"""
Open a workbook by its file path.
"""
Wb = self.WbIsOpen(FilePath)
if Wb==False: Wb = self.App.Workbooks.Open(FilePath)
return Wb
def WbIsOpen(self,FilePath):
for Wb in self.App.Workbooks:
if os.path.join(Wb.Path,Wb.Name)==FilePath:
return Wb
return False
def GetRangeByRowCol(self, RowStart,ColStart,RowEnd,ColEnd,Sheet=None):
if Sheet==None:
Sheet=self.GetActiveSheet()
Rng = Sheet.Range(Sheet.Cells(RowStart,ColStart),Sheet.Cells(RowEnd,ColEnd))
return Rng
def GetRangeByName(self,RangeName, WkBk=None):
"""
Get the range based on its name for an Excel Named Range.
"""
if WkBk==None: WkBk=self.GetActiveWorkbook()
return WkBk.Names.Item(RangeName).RefersToRange
def GetSelectionRange(self):return self.App.Selection
def GetActiveWorkbook(self):
if self.App.ActiveWorkbook==None:
WB = self.App.Workbooks.Add()
return self.App.ActiveWorkbook
def GetActiveSheet(self):
return self.GetActiveWorkbook().ActiveSheet
def SetCellValue(self, Row, Col, Val, Sheet=None):
if Sheet==None:
Sheet = self.GetActiveSheet()
Sheet.Cells(Row, Col).Value = Val
def GetCellValue(self, Row, Col, Sheet=None):
if Sheet==None:
Sheet = self.GetActiveSheet()
return Sheet.Cells(Row, Col).Value2
def GetSelectedRows(self):
"""
Get a list of the selected row integers.
Can be non-continuous.
"""
Rng=self.GetSelectionRange()
MyRows=set()
for i in range(1, Rng.Areas.Count+1):
StartRow = Rng.Areas[i].Row
EndRow = StartRow+ Rng.Areas[i].Rows.Count
MyRows.update(range(StartRow, EndRow))
return list(MyRows)
def GetRangeToLeft(self,Rng):
RowEnd = Rng.Row+Rng.Rows.Count
ColEnd = Rng.Column+Rng.Columns.Count
return self.GetRangeByRowCol(Rng.Row, Rng.Column,RowEnd, ColEnd, Rng.WorkSheet)
def GetNamedRangeValues(self,RangeName, WkBk=None):
"""
Get the values based on its name for an Excel Named Range.
"""
if WkBk==None: WkBk=self.GetActiveWorkbook()
Rng=self.GetRangeByName(RangeName, WkBk)
return Rng.Value()
def ReportStatus(self,Status):
if self.StatusReportControl!=None:
try:self.StatusReportControl.Text=Status
except Exception as e: pass
self.Log+='\n'+Status
#Helper Range Functions.
def GetRangeValues(Rng):
"""
Retrieve data of a range
Returns a list of nested lists with each nested list corresponding to row data.
Assumes a continuous range with no merged cells or disconnected Areas
"""
Vals =Rng.Value()
Data = []
for i in range(Rng.Rows.Count):
RowData = []
Data.append(RowData)
for j in range(Rng.Columns.Count):
RowData.append(Vals[i,j])
return Data
def SetRangeValues(Rng, Values):
"""
Retrieve data of a range
Returns a list of nested lists with each nested list corresponding to row data.
Assumes a continuous range with no merged cells or disconnected Areas
"""
Sh = Rng.Worksheet
for Cell in Rng.Cells:
r = Cell.Row
c = Cell.Column
i = Cell.Row-Rng.Row
j = Cell.Column-Rng.Column
Sh.Cells(r,c).Value=Values[i][j]
def GetRangeValuesByColumn(Rng):
"""
Retrieve data by columns from a range
Returns a list of nested lists with each nested list corresponding to column data.
"""
Vals =Rng.Value()
Data = []
for i in range(Rng.Columns.Count):
ColData = []
Data.append(ColData)
for j in range(Rng.Rows.Count):
ColData.append(Vals[j,i])
return Data
Comments
-
Hello Mike.Thompson Thankyou for your answer
I am unable to get the Excel manager module .how to add that ?
and further here is my code below
"""
RL= DataModel.GetObjectsByType(DataModelObjectCategory.RemoteForce)
RL1 = RL[0]
import clr
clr.AddReference("Microsoft.Office.Interop.Excel")
import Microsoft.Office.Interop.Excel as Excel
workbook = ex.Workbooks.Open(r"C:\FUN\PREPROCESSING_AUTOMATION\ALL_LOADS_H1.xlsx")
worksheet=workbook.worksheets("SHEET1")
ex.Visible = False
C1 = worksheet.Range["A2"].Text
RL1.XComponent.Output.DiscreteValues = [Quantity(str(C1)+' [N]')]from this code I am only able to get a single value of a cell instead I need to enter values from range A1 to A72
Thanks again0 -
Hello Mike.Thompson Thankyou for your answer
I am unable to get the Excel manager module .how to add that ?
and further here is my code below
"""
RL= DataModel.GetObjectsByType(DataModelObjectCategory.RemoteForce)
RL1 = RL[0]
import clr
clr.AddReference("Microsoft.Office.Interop.Excel")
import Microsoft.Office.Interop.Excel as Excel
workbook = ex.Workbooks.Open(r"C:\FUN\PREPROCESSING_AUTOMATION\ALL_LOADS_H1.xlsx")
worksheet=workbook.worksheets("SHEET1")
ex.Visible = False
C1 = worksheet.Range["A2"].Text
RL1.XComponent.Output.DiscreteValues = [Quantity(str(C1)+' [N]')]from this code I am only able to get a single value of a cell instead I need to enter values from range A1 to A72
Thanks again0 -
@Manoj , Make sure you are familiar with the "range" object from general VBA for Excel.
In the python environment you can get a 2D array by this method on a range:Vals =Rng.Value()
See the example below for getting the values as a python list form the 2D array. You can get a 'range' object by a named range, cell row | col integers, or by an address like: Rng=worksheet.Range["A2:A17"]
def GetRangeValues(Rng): Vals =Rng.Value() Data = [] for i in range(Rng.Rows.Count): RowData = [] Data.append(RowData) for j in range(Rng.Columns.Count): RowData.append(Vals[i,j]) return Data0
