How to interact with Excel from Mechanical (or other applications)

Options
Mike.Thompson
Mike.Thompson Member, Employee Posts: 300
First Anniversary First Comment 5 Likes Ansys Employee
edited June 2023 in Structures

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

  • Manoj
    Manoj Member Posts: 5
    First Comment
    Options

    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 again

  • Manoj
    Manoj Member Posts: 5
    First Comment
    Options

    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 again

  • Mike.Thompson
    Mike.Thompson Member, Employee Posts: 300
    First Anniversary First Comment 5 Likes Ansys Employee
    edited January 2
    Options

    @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 Data