Skip to main content

Script Tip Friday- Connecting Mechanical and Excel

james.derrick@ansys.com | 01.30.2023

Our favorite kind of Script Tip is one that comes from our page followers, just like this one! This week, Josef Běhal, an ACT/Mechanical engineer at SVS FEM, has just the tip for you if you've ever wondered how to connect Ansys Mechanical and MS Excel. Thanks Josef!

Iron Python scripts for data export from Ansys Mechanical to MS Excel.

About

Easy way how to export for example path result data of several results objects from ANSYS Mechanical to single MS Excel sheet. Developed and tested in ANSYS 2020R2.

Usage

Simply copy the code below to Ansys Mechanical scripting window, select objects with tabular data you want to export, and run the script.

#############################################################
#                                                           #
#   Functionalities to transfer data from Mechanical        #
#   to Excel                                                #
# --------------------------------------------------------- #
#                                                           #
#   Programmer:         Josef Behal                         #
#   Company:            SVS FEM --> www.svsfem.cz           #
#   Ansys version:      2020 R1                             #
#                                                           #
# --------------------------------------------------------- #
#                                                           #
#   Changes:                                                #
#       25.03.2020 - Initial version                        #
#                                                           #
#############################################################

#
#   IMPORTS
#
import clr                
clr.AddReference("Microsoft.Office.Interop.Excel") 
import Microsoft.Office.Interop.Excel as ExcelNS
import units as UnitsLibrary

#
#   MECHANICAL
#
class MechanicalActions(object):
    '''
    Reading Mechanical Tabular data
    '''

    @staticmethod
    def GetMechData(sheetName = "ANSYS_Export"):
        sheetData = {}
        objData = {} 
        aos = Tree.ActiveObjects

        # Get tabular data
        for ao in aos:
            ao.Activate()
            pane  = ExtAPI.UserInterface.GetPane(MechanicalPanelEnum.TabularData)
            table = pane.ControlUnknown                
            colls = []                            
            for mCol in range(2, table.ColumnsCount+1):                    
                isActive = table.Cell(1,mCol).CheckStateChecked
                col      = [isActive] + [table.Cell(mRow,mCol).Text for mRow in range(1, table.RowsCount+1)]
                colls   += [col]

            objData[ao.ObjectId] = colls                
            del(pane)

            type  = str(ao.GetType()).Split(".")[-1]
            uLab = ""
            try:    uLab = ao.MinimumTotal.Unit
            except: pass
            try:    uLab = ao.Minimum.Unit
            except: pass

            units = UnitsLibrary.Ansys.Core.Units.UnitsManager.GetQuantityNamesForUnit(uLab)            
            for unit in units: 
                if type.Contains(unit): break                        
            unit = "Displacement" if unit.ToLower() == "length" else unit
            aoUnit = "{} [{}]".format(unit,uLab)

            try:    sheetData[sheetName] += [[ao.Name, aoUnit, objData[ao.ObjectId]]]
            except: sheetData[sheetName]  = [[ao.Name, aoUnit, objData[ao.ObjectId]]]

        return sheetData

#   
#   EXCEL
#       
class ExcelActions(object):
    '''
    MS Excel operations
    '''
    worksheets = []
    @staticmethod
    def CreateNewWB(maximaze = True, visible = True, scrUpdate = True):
            """
            wb = ExcelActions.CreateNewWB(maximaze = True, visible = True, scrUpdate = True) --> excel workbook
            """
            excel = ExcelNS.ApplicationClass()            
            wb    = excel.Workbooks.Add()

            if maximaze:
                window             = excel.Windows(1)
                window.WindowState = ExcelNS.XlWindowState.xlMaximized
            excel.Visible        = visible       
            excel.ScreenUpdating = scrUpdate

            ExcelActions.currentWB = wb
            ExcelActions.currentEX = excel
            return wb

    @staticmethod
    def CreateSheet(wb = None, name = "New Sheet"):
        if wb == None: wb = ExcelActions.CreateNewWB()
        ws      = wb.Worksheets.Add()
        ws.Name = name
        ExcelActions.worksheets += [ws]
        return ws

    class Table(object):
        def __init__(self,ws):
            self.ws       = ws

        def CreateTable(self,                    
                        title      = "",
                        units      = "",
                        pivot      = [], 
                        columns    = [],
                        actives    = [],
                        initCell   = [3,2]):
            """
            tab = Table()
            tab.AddData(title = [["","",""]], pivot = [["a","b","c"]], columns = [[1,2,3],[4,5,6],[7,8]])
            """
            self.units = units
            self.name  = title

            titleRng = self.AddDataSeries(dataLists = [[""] for i in range(len(pivot))],   initCell = [initCell[0]-2,initCell[1]])
            self.Format(titleRng, merge = True, bgColor = ExcelNS.XlRgbColor.rgbBlack , hAlign   = "xlCenter", vAlign   = "xlCenter", fntColor = 53758, fntSize  = 13, fntBold  = True,  wrapText = True)
            titleRng.Value2 = title
            self.titleRng   = titleRng

            self.pivotNames = pivot
            pivotRng = self.AddDataSeries(dataLists = [[title + ": " + p] for p in pivot],   initCell = [initCell[0]-1,initCell[1]], resizeDelim = ":")
            self.Format(pivotRng, bgColor = 53758 , hAlign   = "xlCenter", vAlign   = "xlCenter", fntBold  = True,  wrapText = True)
            self.pivotRng = pivotRng

            dataRng  = self.AddDataSeries(dataLists = columns, initCell = initCell)
            self.Format(dataRng, bgColor = ExcelNS.XlRgbColor.rgbWhite , hAlign   = "xlCenter", vAlign   = "xlCenter")
            self.dataRng = dataRng
            self.actives = actives

            return self

        def Format(self,
                    obj,
                    excelNS  = "ExcelNS",
                    merge    = False,
                    bgColor  = None, # 53758 (svsyellow), ExcelNS.XlRgbColor.rgbBlack, ...
                    hAlign   = None, # "xlCenter", "xlBottom", ...
                    vAlign   = None, # "xlCenter", "xlBottom", ...
                    fntColor = None, # 53758 (svsyellow), ExcelNS.XlRgbColor.rgbBlack, ...
                    fntSize  = None, # 13
                    fntBold  = False,
                    wrapText = False):

            if merge: obj.Merge()                
            if bgColor  != None: obj.Interior.Color      = bgColor
            if hAlign   != None: obj.HorizontalAlignment = eval("{}.Constants.{}".format(excelNS,hAlign))
            if vAlign   != None: obj.VerticalAlignment   = eval("{}.Constants.{}".format(excelNS,vAlign))                
            if fntColor != None: obj.Font.Color          = fntColor
            if fntSize  != None: obj.Font.Size           = fntSize
            obj.Font.Bold           = fntBold
            obj.WrapText            = wrapText

        def AddDataSeries(self, dataLists, initCell, resizeDelim = None):         
            """
            dataList = [["aaaaaaaaaaaaaaaaaaaaaaa","b","c"]]
            initCell = [1,1]            
            table    = Table()
            t = table.AddDataSeries(dataList, initCell)           
            """

            rowInd, colInd = initCell
            for mCol, data in enumerate(dataLists):
                colS     = self.ws.Cells(rowInd, colInd)
                colE     = self.ws.Cells(rowInd + len(data), colInd)
                colRange = self.ws.Range(colS,colE)

                colVals  = colRange.Value2                
                for rCol, val in enumerate(data): colVals.SetValue(val, rCol+1, 1)

                if resizeDelim:
                    column = self.ws.Columns(colInd)
                    width = column.ColumnWidth

                    valLen = max([len(word) for values in data for word in values.Split(resizeDelim)]) + 3                
                    if width < valLen: column.ColumnWidth = valLen

                colRange.Value2 = colVals                 
                colInd += 1

            maxCol = max([len(data) for data in dataLists])
            dataS  = self.ws.Cells(initCell[0],initCell[1])
            dataE  = self.ws.Cells(rowInd + maxCol -1, colInd - 1)

            dataRange = self.ws.Range(dataS, dataE)

            return dataRange

#
# UTILS
#
def Msg(msg):
    ExtAPI.Log.WriteMessage(msg)

def ExportData():
    '''
    main function for data export
    '''
    sheetData = MechanicalActions.GetMechData()
    wb = ExcelActions.CreateNewWB()
    for sheetName, sheetTabs in sorted(sheetData.iteritems()):
        ws = ExcelActions.CreateSheet(wb, name = sheetName)

        tabs = []
        iRow, iCol = 5 , 2
        for name, shObjUnit, tabData in sheetTabs:

            pivot   = [col[1] for col in tabData]
            actives = [col[0] for col in tabData]
            columns = [col[2:] for col in tabData]

            tab = ExcelActions.Table(ws)
            tab.CreateTable(title = name, pivot = pivot, columns = columns, actives = actives, initCell = [iRow, iCol], units = shObjUnit)
            iCol += (len(pivot)+1)
            tabs += [tab]

#
#   CALLS
#
ExportData()