def excelWorkbookAction(workbook):
worksheets = workbook.Worksheets
worksheet = worksheets[worksheetName] if worksheetName is not None else worksheets[1]
WriteRowsToWorksheet(worksheet, rows)
Marshal.FinalReleaseComObject(worksheet)
Marshal.FinalReleaseComObject(worksheets)
return
def xlr2list(xlrange):
"""Converts excel range to python list
"""
rows = xlrange.Rows
columns = xlrange.Columns
n = rows.Count
m = columns.Count
vals = [[0] * m for i in range(n)]
for i in range(n):
for j in range(m):
tmp = xlrange[i + 1, j + 1].Value2
try:
if int(tmp) == tmp:
tmp = int(tmp)
except:
# tmp = tmp.unicode('utf8')
pass
# print tmp
vals[i][j] = tmp
Marshal.FinalReleaseComObject(rows)
Marshal.FinalReleaseComObject(columns)
Marshal.FinalReleaseComObject(xlrange)
rows = None
columns = None
xlrange = None
return vals
def excelWorkbookAction(workbook):
worksheets = workbook.Worksheets
worksheet = worksheets[worksheetName] if worksheetName is not None else worksheets[1]
rows = ReadRowsTextFromWorksheet(worksheet)
Marshal.FinalReleaseComObject(worksheet)
Marshal.FinalReleaseComObject(worksheets)
return rows
def excelWorkbookAction(workbook):
newWorksheetName = "Sheet1" if worksheetName is None else worksheetName
worksheets = workbook.Worksheets
worksheets.Add()
worksheet = worksheets[1]
worksheet.Name = newWorksheetName
WriteRowsToWorksheet(worksheet, rows)
Marshal.FinalReleaseComObject(worksheet)
Marshal.FinalReleaseComObject(worksheets)
return
def __del__(self):
self.workbook.Close(False)
self.application.Quit()
Marshal.FinalReleaseComObject(self.activesheet)
Marshal.FinalReleaseComObject(self.worksheets)
Marshal.FinalReleaseComObject(self.workbooks)
Marshal.FinalReleaseComObject(self.workbook)
Marshal.FinalReleaseComObject(self.application)
self._set_none()
def WriteRowsToWorksheet(worksheet, rows):
for rowIndex, row in enumerate(rows):
excelRows = worksheet.Rows
excelRow = excelRows[rowIndex + 1]
excelRow.NumberFormat = "@" # Set type to 'Text'
for cellIndex, cellValue in enumerate(row):
cells = excelRow.Cells
cell = cells[cellIndex + 1]
cell.Value2 = cellValue
Marshal.FinalReleaseComObject(cell)
Marshal.FinalReleaseComObject(cells)
Marshal.FinalReleaseComObject(excelRow)
Marshal.FinalReleaseComObject(excelRows)
return
def excelAppAction(app):
result = None
workbooks = None
workbook = None
try:
workbooks = app.Workbooks
workbook = workbooks.Open(excelFilePath)
result = workbookAction(workbook)
finally:
if workbook is not None:
workbook.Close(saveChanges)
Marshal.FinalReleaseComObject(workbook)
if workbooks is not None:
Marshal.FinalReleaseComObject(workbooks)
return result
def g_active_xlr2list(self):
"""Gets used range in an active sheet into a dictionary
"""
xlrange = self.activesheet.UsedRange
list1 = self.xlr2list(xlrange)
Marshal.FinalReleaseComObject(xlrange)
xlrange = None
return list1
def __len__(self):
cnt = 0
for dummy in self.worksheets:
cnt += 1
Marshal.FinalReleaseComObject(dummy)
dummy = None
return cnt
def __del__(self):
# release com reference to proxied object
if sys.platform == 'cli':
from System.Runtime.InteropServices import Marshal
obj = object.__getattribute__(self, '_proxyobj')
Marshal.FinalReleaseComObject(obj)
else:
obj = object.__getattribute__(self, '_proxyobj')
if hasattr(obj, 'Release'):
obj.Release()
def g_xlr2list(self, cell1, cell2):
"""Gets excel range into a list
"""
xlrange = self.activesheet.Range[cell1, cell2]
list1 = self.xlr2list(xlrange)
Marshal.FinalReleaseComObject(xlrange)
xlrange = None
return list1
def WithExcelApp(excelAppAction):
result = None
app = Excel.ApplicationClass()
app.Visible = False # Set this to False so Excel isn't visible to the user.
app.DisplayAlerts = False
app.ScreenUpdating = False
app.AskToUpdateLinks = False # Suppress prompt to update data links.
try:
result = excelAppAction(app)
finally:
app.DisplayAlerts = True
app.ScreenUpdating = True
app.Quit()
Marshal.FinalReleaseComObject(app)
return result
def On_MainForm_Closing(self, sender, args):
message = "Are you sure that you would like to close the form?"
caption = "Form Closing"
result = WinForms.MessageBox.Show(message, caption,
WinForms.MessageBoxButtons.YesNo,
WinForms.MessageBoxIcon.Question)
# If the no button was pressed ...
if result == WinForms.DialogResult.No:
# cancel the closure of the form.
args.Cancel = True
elif result == WinForms.DialogResult.Yes:
# Close Excel Application in Model class
if self._model.excel is not None:
self._model.excel.Quit()
Marshal.FinalReleaseComObject(self._model.excel)
def __exit__(self, exc_type, exc_val, traceback):
for obj in self.objs:
Marshal.FinalReleaseComObject(obj)
def __exit__(self, exc_type, exc_val, traceback):
Marshal.Release(Marshal.GetIDispatchForObject(self.ad))
UseAppDispatch.counter -= 1
if UseAppDispatch.counter == 0:
Marshal.FinalReleaseComObject(self.ad)