def __init__(self, path):
ex = Excel.ApplicationClass()
ex.Visible = False
lst_xls = []
workbook = ex.Workbooks.Open(path)
ws = workbook.Worksheets[1]
rowCountF = max(ws.Range(i).End(xlDirecUp).Row for i in ["A65536", "B65536", "C65536", "D65536", "E65536", "F65536", "G65536", "H65536"])
# other method if column A is empty
# rowCountF = ws.Range("B65536").End(xlDirecUp).Row
# rowCountF = ws.Columns[1].End(xlDirecDown).Row
##get number of Coloun not empty ##
colCountF = max(ws.Range(i).End(xlDirecLeft).Column for i in ["ZZ1", "ZZ2", "ZZ3", "ZZ4", "ZZ5", "ZZ6", "ZZ7", "ZZ8", "ZZ9"])
# other methods
#colCountF = ws.Range("ZZ9").End(xlDirecLeft).Column
# colCountF = ws.Rows[1].End(xlDirecRight).Column
for i in range(1,rowCountF+1):
temp_lst = []
for j in range(1,colCountF+1):
try:
temp_lst.append(ws.Cells[i,j].Value2.ToString())
except:
temp_lst.append(ws.Cells[i,j].Value2)
lst_xls.append(temp_lst)
self.datas = lst_xls
self.first_flst = [x for x in lst_xls[0]] # or lst_xls[0]
#Get the specify index
self.type_fidx = self.first_flst.index("Type")
ex.Workbooks.Close()
ex.Quit()
#other proper way to make sure that you really closed and released all COM objects
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(ex)
def CleanUp(_list):
if isinstance(_list, list):
for i in _list:
Marshal.ReleaseComObject(i)
else:
Marshal.ReleaseComObject(_list)
return None
def closeProperly(self):
if self.document is not None:
Marshal.ReleaseComObject(self.document)
if self.wapp is not None:
Marshal.ReleaseComObject(self.wapp)
self.document = None
self.wapp = None
def closeProperly(self):
if self.workbook is not None:
Marshal.ReleaseComObject(self.workbook)
if self.app is not None:
Marshal.ReleaseComObject(self.app)
self.workbook = None
self.app = None
def ExitExcel(self):
self.workbook.Close()
self.ex.Workbooks.Close()
self.ex.Quit()
#other proper way to make sure that you really closed and released all COM objects
if self.workbook is not None:
Marshal.ReleaseComObject(self.workbook)
if self.ex is not None:
Marshal.ReleaseComObject(self.ex)
self.workbook = None
self.ex = None
def CleanUp(_list): # clean up before exiting excel, if any COM object remains # unreleased then excel crashes on open following time if isinstance(_list, list): for i in _list: try: Marshal.ReleaseComObject(i) except: pass else: try: Marshal.ReleaseComObject(_list) except: pass return None
def excel_read(origin = "A3", worksheetname="Levels"):
try:
xlapp = Marshal.GetActiveObject('Excel.Application')
ws = xlapp.sheets(worksheetname) #Name of the Excel Worksheet
except EnvironmentError:
try:
filepath = pick_file(file_ext='*')
except: sys.exit()
os.startfile(filepath)
from time import sleep
sleep(1)
try:
xlapp = Marshal.GetActiveObject('Excel.Application')
ws = xlapp.sheets(worksheetname) #Name of the Excel Worksheet
except:
forms.alert('Excel Application not open!\nOpen Excel file with worksheet "Levels" ')
dialogexcelnotopen.show()
sys.exit()
except:
print("Error")
import traceback
print(traceback.format_exc())
extent = ws.Cells(ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row,
ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column)
xlrng = ws.Range[origin, extent].Value2 # 2dimensional array
data_list = [[] for i in range(xlrng.GetUpperBound(0))]
for i in range(xlrng.GetLowerBound(0)-1, xlrng.GetUpperBound(0), 1):
for j in range(xlrng.GetLowerBound(1)-1, xlrng.GetUpperBound(1), 1):
data_list[i].append(xlrng[i,j])
Marshal.ReleaseComObject(xlapp)
return data_list
def __init__(self, path):
ex = Excel.ApplicationClass()
ex.Visible = False
lst_xls = []
workbook = ex.Workbooks.Open(path)
#get worksheet at index (start at 1)
ws = workbook.Worksheets[1]
ws.Activate
#plagefiltrevisible = ws.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Rows
#or
plagefiltrevisible = ws.UsedRange.SpecialCells(specsVisu).Rows
for row in plagefiltrevisible:
lst_xls.append(row.Value2)
self.datas = lst_xls
ex.Workbooks.Close()
ex.Quit()
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(ex)
def importXls(self):
ex = Excel.ApplicationClass()
ex.Visible = False
lst_xls = []
workbook = ex.Workbooks.Open(self.filepath)
ws = workbook.Worksheets[1]
##get number of Rows not empty ##
rowCountF = max(
ws.Range(i).End(xlDirecUp).Row for i in [
"A65536", "B65536", "C65536", "D65536", "E65536", "F65536",
"G65536", "H65536"
])
# other method if column A is empty
# rowCountF = ws.Range("B65536").End(xlDirecUp).Row
# rowCountF = ws.Columns[1].End(xlDirecDown).Row
##get number of Coloun not empty ##
colCountF = max(
ws.Range(i).End(xlDirecLeft).Column for i in
["ZZ1", "ZZ2", "ZZ3", "ZZ4", "ZZ5", "ZZ6", "ZZ7", "ZZ8", "ZZ9"])
# other methods
#colCountF = ws.Range("ZZ9").End(xlDirecLeft).Column
# colCountF = ws.Rows[1].End(xlDirecRight).Column
self.fullrange = ws.Range[ws.Cells(1, 1),
ws.Cells(rowCountF, colCountF)]
self.fullvalue = list(self.fullrange.Value2)
#split list into sublist with number of colum
n = colCountF
self.datas = list(self.fullvalue[i:i + n]
for i in range(0, len(self.fullvalue), n))
self.first_flst = [x for x in self.datas[0]]
ex.Workbooks.Close()
ex.Quit()
#other proper way to make sure that you really closed and released all COM objects
if workbook is not None:
Marshal.ReleaseComObject(workbook)
if ex is not None:
Marshal.ReleaseComObject(ex)
workbook = None
ex = None
def dispose(self):
'''
Releases all ComObjects which were generated during the lifetime of the AutoReleasingComObject-instance.
ComObjects are generated by accessing attributes or methods and are stored internally.
The attribute/method-access (see __getattr__) wraps these ComObjects in AutoReleasingComObject-instances.
This allows to store ComObjects which are generated further down the ComObjects-tree.
Dispose will go down this AutoReleasingComObject-tree and calls dispose on these instances as well.
Therefore, all ComObjects accessed in the object-tree are released by a single dispose-call.
'''
# release ComObjects generated further down the object-tree
for auto_release_com_obj in self._accessed_com_attributes:
auto_release_com_obj.dispose()
# release wrapped ComObject
if self._release_self:
Marshal.ReleaseComObject(self._comobj)
def exc_writearray(origin = "A3", worksheetname= "Sheets"):
originnr = [i for i in origin if i.isdigit()][0]
end = len(ex_row) - 1 + int(originnr)
xlapp = Marshal.GetActiveObject('Excel.Application')
worksheet = xlapp.sheets(worksheetname) #Name of the Excel Worksheet
xlrange_id = worksheet.Range["I" + str(originnr), "I" + str(end)]
#from System.Reflection import Missing
from System import Array
array_id = Array.CreateInstance(object, len(ex_row), 1)
# set array_id with values from dic_sheetGuid
for i,j in zip(range(len(ex_row)), ex_row):
if j[0] in dic_sheetGuid:
array_id[i, 0] = dic_sheetGuid[j[0]]
#write array to cellrange_sheetid
if (xlrange_id.Value2.GetLength(0), xlrange_id.Value2.GetLength(1)) \
== (array_id.GetLength(0), array_id.GetLength(1)): # (7,1) == ( 7,1)
xlrange_id.Value2 = array_id
Marshal.ReleaseComObject(xlapp)
return True
# Disable Alerts - Errors Ignore them, they're probably not important
ex.DisplayAlerts = False
# Workbook
workbook = ex.Workbooks.Open(path)
# WorkSheet
ws = workbook.Worksheets[1]
# Cell range
rowCollection = None
data = ws.UsedRange
for row in data.Rows:
if rowCollection is None:
rowCollection = ["Department"]
else:
rowCollection = [ws.Name]
allNull = True
for column in row.Columns:
value = column.Value2
if value is not None:
allNull = False
rowCollection.append(column.Value2)
if not allNull:
wsCollection.append(rowCollection)
ex.ActiveWorkbook.Close(False)
Marshal.ReleaseComObject(ws)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(ex)
except Exception as e:
wsCollection.append(e)
outList.append(wsCollection)
OUT = outList
for i in range(0, len(data), 1):
xlApp.Workbooks.Open(unicode(tempFilePath))
wb = xlApp.ActiveWorkbook
ws = xlApp.Sheets(sheetName)
rng = ws.Range(ws.Cells(1, 1), ws.Cells(len(data[i]), 1))
rng.Value = xlApp.Transpose(Array[str](data[i]))
ws = xlApp.Sheets(tempSheetName)
ws.Activate
xlApp.ActiveWorkbook.SaveAs(newFilePath + "\\" +
str(newFileName[i]) + ".xlsx")
xlApp.ActiveWorkbook.Close(False)
xlApp.screenUpdating = True
Marshal.ReleaseComObject(ws)
Marshal.ReleaseComObject(wb)
xlApp.Quit()
Marshal.ReleaseComObject(xlApp)
except:
xlApp.Quit()
Marshal.ReleaseComObject(xlApp)
# if error accurs anywhere in the process catch it
import traceback
errorReport = traceback.format_exc()
pass
else:
errorReport = None
message = "Run Me is set to False."
if errorReport == None:
equipTags = [
'-'.join([
CODE,
e.LookupParameter('partition').AsString(),
e.LookupParameter('Signal_designation').AsString(),
e.LookupParameter('EQ_TAG').AsString()
]) for e in equip
]
lol.append(equipTags)
a = Array[str](lol)
xlEquipRange = worksheet.Range["A" + str(len(excelEquipTags) + 1) + ":A" +
str(len(excelEquipTags) + len(equipTags))]
xlEquipRange.Value2 = a
MessageBox.Show(str(lol), "Предупреждение", MessageBoxButtons.OK,
MessageBoxIcon.Information)
workbook.Save
workbook.Close(1)
workbooks.Close()
excel.Quit()
Marshal.ReleaseComObject(uRange)
Marshal.ReleaseComObject(worksheet)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(workbooks)
Marshal.ReleaseComObject(excel)
#MessageBox.Show(str(dir(worksheet)), "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Information)
for index, (range, format) in enumerate(zip(cellRange, formatConditions)): origin = ws.Cells(bb.xlRange(range)[1], bb.xlRange(range)[0]) extent = ws.Cells(bb.xlRange(range)[3], bb.xlRange(range)[2]) ConditionFormatCells(origin, extent, ws, format) else: try: xlApp = SetUp(Excel.ApplicationClass()) if os.path.isfile(unicode(filePath)): xlApp.Workbooks.open(unicode(filePath)) wb = xlApp.ActiveWorkbook ws = xlApp.Sheets(sheetName) if not isinstance(cellRange, list): origin = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0]) extent = ws.Cells(bb.xlRange(cellRange)[3], bb.xlRange(cellRange)[2]) ConditionFormatCells(origin, extent, ws, formatConditions) Marshal.ReleaseComObject(extent) Marshal.ReleaseComObject(origin) else: for index, (range, format) in enumerate(zip(cellRange, formatConditions)): origin = ws.Cells(bb.xlRange(range)[1], bb.xlRange(range)[0]) extent = ws.Cells(bb.xlRange(range)[3], bb.xlRange(range)[2]) ConditionFormatCells(origin, extent, ws, format) Marshal.ReleaseComObject(extent) Marshal.ReleaseComObject(origin) ExitExcel(filePath, xlApp, wb, ws) except: xlApp.Quit() Marshal.ReleaseComObject(xlApp) except: # if error accurs anywhere in the process catch it import traceback
def autorelease(comobj):
"""COM auto release contextmanager"""
try:
yield comobj
finally:
Marshal.ReleaseComObject(comobj)
import clr
import time
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal
#time.sleep(3)
dirfichier = IN[0]
ex = Excel.ApplicationClass()
ex.Visible = True
ex.DisplayAlerts = False
wb = ex.Workbooks.Open(dirfichier)
ws = wb.Worksheets[1]
#time.sleep(3)
# run macro named 'Dynamo'
ex.Application.Run("Dynamo")
wb.SaveAs(dirfichier)
ex.Workbooks.Close()
ex.Quit()
if wb is not None:
Marshal.ReleaseComObject(wb)
if ex is not None:
Marshal.ReleaseComObject(ex)
wb = None
ex = None
OUT = "Dynamo Ok !"
def release(com_object):
"""Release given Excel.Application COM Object"""
Marshal.ReleaseComObject(com_object)
sheet.UsedRange.ClearContents()
except EnvironmentError:
print "Error", csvfilename
sheet = wb.Worksheets.Add()
sheet.Name = csvfilename
except:
print "Error: Something went wrong"
print traceback.format_exc()
#print wb
#print wb.Name
# read from the csvfile, write to Excel-sheet:
try:
with io.open(csvfile, 'r', encoding='utf_16') as f:
reader = unicode_csv_reader(f, delimiter='\t')
for i, row in enumerate(reader):
for j, colum in enumerate(row):
sheet.Cells(i + 1, j + 1).Value = colum
except UnicodeError:
print "Error ", csvfilename
import traceback
print traceback.format_exc()
# continue
except:
import traceback
print traceback.format_exc()
Marshal.ReleaseComObject(sheet)
wb.Save()
Marshal.ReleaseComObject(wb)
Marshal.ReleaseComObject(exapp)
xlApp = SetUp(Excel.ApplicationClass())
# if excel is closed and data is being written to single sheet
if not isinstance(styles, list):
xlApp.Workbooks.open(unicode(filePath))
wb = xlApp.ActiveWorkbook
ws = xlApp.Sheets(styles.SheetName())
StyleData(ws, styles.GraphicStyle(), styles.CellRange())
ExitExcel(filePath, xlApp, wb, ws)
# if excel is closed and data is being written to multiple sheets
else:
xlApp.Workbooks.open(unicode(filePath))
wb = xlApp.ActiveWorkbook
for i in styles:
ws = xlApp.Sheets(i.SheetName())
StyleData(ws, i.GraphicStyle(), i.CellRange())
ExitExcel(filePath, xlApp, wb, ws)
except:
xlApp.Quit()
Marshal.ReleaseComObject(xlApp)
except:
# if error accurs anywhere in the process catch it
import traceback
errorReport = traceback.format_exc()
else:
errorReport = "Run Me is set to False. Please set \nto True if you wish to write data \nto Excel."
if errorReport == None:
OUT = "Success!"
else:
OUT = errorReport