class Excel():
def __init__(self, excelFile):
'''
Parameters
file: str, excel file
'''
CoInitialize()
self.excelCOM = DispatchEx('Excel.Application')
self.excelBook = self.excelCOM.Workbooks.Open(excelFile)
def get_cell(self, sheet, loc=None, row=None, col=None):
'''
Parameters
sheet: str, sheet name
loc: cell location, equivalent to col+row
row: int or str, row index
col: str, column index
Returns
cellValue: num or str, cell value (after calculation)
'''
sht = self.excelBook.Worksheets(sheet)
if loc != None:
cellValue = sht.Evaluate(loc).Value
elif row != None and col != None:
cellValue = sht.Cells(row, col).Value
return cellValue
def set_cell(self, value, sheet, loc=None, row=None, col=None):
'''
Parameters
value: num or str, value to set
sheet: str, sheet name
loc: cell location, equivalent to col+row
row: int or str, row index
col: str, column index
'''
sht = self.excelBook.Worksheets(sheet)
if loc != None:
sht.Evaluate(loc).Value = value
elif row != None and col != None:
sht.Cells(row, col).Value = value
def load_aspenModel(self, aspenFile):
'''
Parameters
aspenFile: str, aspen file
'''
self.set_cell(aspenFile, 'Set-up', 'B1')
self.run_macro('sub_ClearSumData_ASPEN')
self.run_macro('sub_GetSumData_ASPEN')
def run_macro(self, macro):
'''
Parameters
macro: str, macro
'''
self.excelCOM.Run(macro)
def close(self):
self.excelBook.Close(SaveChanges=0)
Sub Insert_Sales()
Application.ScreenUpdating = False
Dim EndRow As Long
EndRow = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row
Sheets(3).Range("J3:J" & EndRow).Formula = "=VLOOKUP(A3,'GM GateKeeper'!C:Q,15,FALSE)"
Sheets(3).Range("K3:K" & EndRow).Formula = "=HLOOKUP(J3,M:O,P3,FALSE)"
End Sub
'''
mod = wb.VBProject.VBComponents.Add(1)
mod.CodeModule.AddFromString(salesCode)
xl.Run("Insert_Sales")
gkCode = '''
Sub Insert_Formula()
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
Sheets(1).Range("B2:B" & LastRow).Formula = "=CONCAT(G2,""|"",N2,""|"",Q2)"
Sheets(1).Range("R2:R" & LastRow).Formula = "=ROUND(S2*(T2+U2*.6)/5,0)*5"
Sheets(1).Range("S2:S" & LastRow).Formula = 1
Sheets(1).Range("T2:T" & LastRow).Formula = "=IFERROR(VLOOKUP(B2,'GM HISTORY'!A:AG,33,FALSE),"""")"
