def exportXls(self):
ex = Excel.ApplicationClass()
ex.Visible = True
ex.DisplayAlerts = False
workbook = ex.Workbooks.Add()
workbook.SaveAs(self.filepath)
ws = workbook.Worksheets[1]
nbr_row = len(self.lstData)
nbr_colum = len(self.lstData[0])
xlrange = ws.Range[ws.Cells(1, 1), ws.Cells(nbr_row, nbr_colum)]
a = Array.CreateInstance(object, nbr_row, nbr_colum)
for indexR, row in enumerate(self.lstData):
for indexC, value in enumerate(row):
a[indexR, indexC] = value
#copy Array in range
xlrange.Value2 = a
used_range = ws.UsedRange
for column in used_range.Columns:
column.AutoFit()
# apply style
missing = System.Type.Missing
try:
currentWs.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange,
xlrange, missing,
Excel.XlYesNoGuess.xlGuess,
missing).Name = "WFTableStyle"
currentWs.ListObjects[
"WFTableStyle"].TableStyle = "TableStyleMedium6"
except:
pass
def exporttoexcel(a, b):
#Getting Excel Started
excel = Excel.ApplicationClass()
excel.Visible = True
excel.DisplayAlerts = False
# creating a new one
workbook = excel.Workbooks.Add()
beamnum = len(a)
#segnum=len(a[0]) segnum每个射野不一样
#xnum=len(a[0][0]) xnum总为2
mlcnum = len(a[0][0][0])
for p1 in range(beamnum):
# adding a worksheet,每一个sheet代表一个射野
worksheet = workbook.Worksheets.Add()
worksheet.Name = b[p1]
for p2 in range(len(a[p1])):
#每一个sheet下的所有子野数目，分两列表示
for p4 in range(mlcnum):
#excel计数从1开始
worksheet.Cells(p4 + 1,
p2 * 2 + 1).Value = round(a[p1][p2][0][p4], 2)
worksheet.Cells(p4 + 1, p2 * 2 + 1 + 1).Value = round(
a[p1][p2][1][p4], 2)
def __init__(self, path, lstValue, sheetName):
self.setValue = set([int(x) for x in lstValue])
self.lsta = []
ex = Excel.ApplicationClass()
ex.Visible = True
lst_xls = []
workbook = ex.Workbooks.Open(path)
ws = workbook.Worksheets[sheetName]
##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"
])
##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"])
self.fullrange = ws.Range[ws.Cells(1, 1),
ws.Cells(rowCountF, colCountF)]
#self.fullvalue = self.fullrange.Value2
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))
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 exporttoexcel(patientname, total_outcome, plans):
#Getting Excel Started
excel = Excel.ApplicationClass()
excel.Visible = True
excel.DisplayAlerts = False
# creating a new one
workbook = excel.Workbooks.Add()
worksheet = workbook.Worksheets.Add()
worksheet.Cells(2, 1).Value = patientname
#excel坐标从1开始算，而range从0开始
#写入表头信息
i = 0
for p1 in range(len(plans)):
#多个字典
for d in total_outcome[p1]:
#字典遍历
for k, v in d.items():
if not (k == 'roiname'):
worksheet.Cells(
1,
2 + i).Value = plans[p1] + '_' + d['roiname'] + '_' + k
worksheet.Cells(2, 2 + i).Value = v
i += 1
def start_new_instance(self, _filename):
self.excel_app = Excel.ApplicationClass()
self.excel_app.DisplayAlerts = False
self.excel_app.EnableEvents = False
self.excel_app.Visible = True
self.excel_app.ScreenUpdating = True
self.filename = _filename
def __init__(self,
__revit__,
search_id=None,
excel_parameters=None,
xl_file_path=None,
xl_write_flag=False):
# region Get Document and Application
self.doc = __revit__.ActiveUIDocument.Document
self.uidoc = UIDocument(self.doc)
self.app = __revit__.Application
self.uiapp = UIApplication(self.app)
self.excel = Excel.ApplicationClass()
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo(
"en-US")
# endregion
# region Initial parameters
self._search_id = search_id
self._excel_parameters = excel_parameters
self._xl_file_path = xl_file_path
self._xl_write_flag = xl_write_flag
self.New_MEPSpaces = {}
self.Exist_MEPSpaces = {}
# Create a space collector instance
self._space_collector = FilteredElementCollector(
self.doc).WhereElementIsNotElementType().OfCategory(
BuiltInCategory.OST_MEPSpaces)
# endregion
# region Custom Events
self.startProgress = Event()
self.ReportProgress = Event()
self.endProgress = Event()
def readingFromExcel(self, _lib_path):
"""Context manager for handling open / close / cleanup for Excel App"""
# OG Ref: https://github.com/PH-Tools/LBT-2-PH
#Ref: https://stackoverflow.com/questions/158706/how-do-i-properly-clean-up-excel-interop-objects
#Ref: https://devblogs.microsoft.com/visualstudio/marshal-releasecomobject-considered-dangerous/
try:
# make temp copy
#----------------------------------------------------------------
self.saveDir = os.path.split(_lib_path)[0]
self.tempFile = '{}_temp.xlsx'.format(random.randint(0,1000))
self.tempFilePath = os.path.join(self.saveDir, self.tempFile)
copyfile(_lib_path, self.tempFilePath)
# Read from
self.ex = Excel.ApplicationClass()
self.ex.Visible = False
self.ex.DisplayAlerts = False
self.workbook = self.ex.Workbooks.Open(self.tempFilePath)
self.worksheets = self.workbook.Worksheets
yield
except:
self.workbook.Close()
self.ex.Quit()
self.ex = None
gc.collect()
os.remove(self.tempFilePath)
finally:
self.workbook.close()
self.ex.Quit()
self.ex = None
gc.collect()
os.remove(self.tempFilePath)
def initialise():
"""Get active Excel.Application COM object if available or create a new one"""
# If Excel is open, get it
try:
return Marshal.GetActiveObject("Excel.Application")
# Else open it
except EnvironmentError:
return Excel.ApplicationClass()
def __init__(self, filepath, nameSheets):
self.filepath = filepath
self.nameSheets = nameSheets if hasattr(
nameSheets, '__iter__') and not isinstance(
nameSheets, (str, System.String)) else [nameSheets]
self.ex = Excel.ApplicationClass()
self.ex.Visible = False
self.workbook = self.ex.Workbooks.Open(self.filepath)
def getApp(createApp, filepath="", cancelbringtofront=False, canceladdwb=False):
if createApp:
import clr
clr.AddReference("Microsoft.Office.Interop.Excel")
import Microsoft.Office.Interop.Excel as Excel
XL = Excel.ApplicationClass()
XL.Visible = True
else:
import System
try:
XL = System.Runtime.InteropServices.Marshal.GetActiveObject('Excel.Application')
XL.Visible = True
except:
XL, wp = getApp(True, filepath, cancelbringtofront, canceladdwb)
XL.Visible = True
if not cancelbringtofront:
BringExcelToFront(XL)
return XL, wp
#print XL.Caption
if filepath and not canceladdwb:
import os.path
try:
wpname = os.path.basename(filepath)
wp = XL.Workbooks(wpname)
except:
#print("wp is not open!")
if os.path.isfile(filepath):
try:
wp = XL.Workbooks.Open(filepath)
except:
#print("cant open wp, then add!")
wp = XL.Workbooks.Add()
else:
#print("wp file does not exists!")
wp = XL.Workbooks.Add()
if filepath[len(filepath)-4:]:
#print("lets save as!")
wp.SaveAs(filepath,52)
else:
try:
wp.SaveAs(filepath)
except:
print("ERROR [neoCL] : Was not possible to save the document!")
elif not canceladdwb:
wp = XL.Workbooks.Add()
try:
sh = wp.ActiveSheet
except:
print("ERROR [neoCL] : No work!")
wp = XL.ActiveWorkbook
if not cancelbringtofront:
BringExcelToFront(XL)
return XL, wp
def __init__(self, file):
"""
Inits a new ExcelWorkbook instance.
Args:
file (string): The path to the xls file
"""
excel = Excel.ApplicationClass()
self.workbook = excel.Workbooks.Open(file)
def startNewInstance(self):
"""Start a new Excel Application instance"""
self.ex = Excel.ApplicationClass()
self.ex.DisplayAlerts = False
self.ex.EnableEvents = False
self.userOpened = False
def load_from_excel(file):
testdata = []
excel = Excel.ApplicationClass()
excel.Visible = True
workbook = excel.Workbooks.Open(
os.path.join(os.path.dirname(os.path.abspath(__file__)),
"data/%s.xlsx" % file))
sheet = workbook.ActiveSheet
for i in range(6):
testdata.append(Group(name=sheet.Range["A%s" % (i + 1)].Value2))
excel.Quit()
return testdata
def load_from_excel(file):
excel = Excel.ApplicationClass()
excel.Visible = True
workbook = excel.Workbooks.Open(
os.path.join(os.path.dirname(os.path.abspath(__file__)),
"data/%s.xlsx" % file))
sheet = workbook.ActiveSheet
testdata = []
for item in sheet.UsedRange:
testdata.append(Group(str(item.Value2)))
excel.Quit()
return testdata
def __init__(self):
try:
ex = System.Runtime.InteropServices.Marshal.GetActiveObject(
"Excel.Application")
except:
ex = Excel.ApplicationClass()
ex.Visible = True
self.app = ex
self.error = None
self.data = []
self.workbook = ex.ActiveWorkbook
self.ws = ex.ActiveSheet
def load_from_xlsx(file):
excel = Excel.ApplicationClass()
excel.Visible = True
data_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data/%s.xlsx" % file)
workbook = excel.Workbooks.Open(data_file)
sheet = workbook.ActiveSheet
i = 1
test_data = []
while sheet.Range["A%s" % i].Value2:
test_data.append(Group(name=sheet.Range["A%s" % i].Value2))
i = i+1
excel.Quit()
return test_data
def load_from_xlsx(file):
f = os.path.join(os.path.dirname(os.path.abspath(__file__)),
"data\%s.xlsx" % file)
excel = Excel.ApplicationClass()
excel.Visible = False
workbook = excel.Workbooks.Open(r"%s" % f)
sheet = workbook.Worksheets[1]
testdata = []
for i in range(1, 7):
text = sheet.Rows[i].Value2[0, 0]
testdata.append(Group(name=text))
excel.Quit()
return testdata
def load_from_xlsx(f):
file = os.path.join(os.path.dirname(os.path.abspath(__file__)),
"data/%s.xlsx" % f)
excel = Excel.ApplicationClass()
workbook = excel.Workbooks.Open(file)
sheet = workbook.ActiveSheet
data = []
rows_with_test_data = sheet.UsedRange.Rows.Count
for i in range(rows_with_test_data):
data.append(str(sheet.Range["A%s" % (i + 1)].Value2))
workbook.Close(SaveChanges=False)
excel.Quit()
return data
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()
return result
def load_from_xlsx(file):
f = os.path.join(os.path.dirname(os.path.abspath(__file__)),
"data/%s.xlsx" % file)
excel = Excel.ApplicationClass()
workbook = excel.Workbooks.Open(f)
sheet = workbook.ActiveSheet
l = []
for i in range(65536):
value = sheet.Range["A%s" % (i + 1)].Value2
if not value:
break
l.append(value)
excel.Quit()
return l
def __init__(self, filename):
self._set_none()
self._filename = filename
self.application = Excel.ApplicationClass()
self.workbooks = self.application.Workbooks
try:
self.workbook = self.workbooks.Open(filename)
except:
print('ExcelFileReader| Incorrect path!')
raise
self.__del__
else:
self.worksheets = self.workbook.Worksheets
self.activesheet = self.worksheets[1]
self.application.Visible = False
self.application.DisplayAlerts = False
def __init__(self, path, dictcolor):
self.dictcolor = dictcolor
self.lsta = []
ex = Excel.ApplicationClass()
ex.Visible = True
lst_xls = []
workbook = ex.Workbooks.Open(path)
ws = workbook.Worksheets[1]
##get number of Rows not empty ##
rowCountF = ws.Columns[1].End(xlDirecDown).Row
##get number of Coloun not empty ##
colCountF = ws.Rows[1].End(xlDirecRight).Column
self.fullrange = ws.Range[ws.Cells(1, 1),
ws.Cells(rowCountF, colCountF)]
self.fullvalue = self.fullrange.Value2
def getDataFromExcel(_filePath):
# Pulls the Glass and Frame data from the Excel file
# Open an Excel Instance and the Temporary Lib File
print ' >Opening Excel document and reading contents....'
ex = Excel.ApplicationClass()
ex.Visible = False # False means excel is hidden as it works
ex.DisplayAlerts = False
workbook = ex.Workbooks.Open(_filePath)
# Find the Windows Worksheet
worksheets = workbook.Worksheets
try:
wsComponents = worksheets['Components']
print ' >Found the Excel Worksheet:', wsComponents.Name
except:
print "Could not find the 'Components' Worksheet in the taget file?"
# Read in the Components from Excel Worksheet
# Come in as 2D Arrays..... grrr.....
xlArrayGlazing_ = wsComponents.Range['IE15:IG113'].Value2
xlArrayFrames_ = wsComponents.Range['IL15:JC113'].Value2
workbook.Close() # Close the worbook itself
ex.Quit() # Close out the instance of Excel
os.remove(_filePath) # Remove the temporary read-file
return xlArrayGlazing_, xlArrayFrames_
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 exporttoexcel(Doses,Names):
#Getting Excel Started
excel=Excel.ApplicationClass()
excel.Visible=True
excel.DisplayAlerts=False
# creating a new one
workbook=excel.Workbooks.Add()
for p2 in range(len(Doses)):
# adding a worksheet,每一个sheet代表一个射野
worksheet=workbook.Worksheets.Add()
worksheet.Name=Names[p2]
#excel坐标从1开始算，而range从0开始
#写入表头信息
worksheet.Cells(1,1).Value='Volume(cc)'
worksheet.Cells(1,2).Value='Dose(cGy)'
for p1 in range(len(Doses[p2])):
worksheet.Cells(p1+2,1).Value=Doses[p2][p1][0]
worksheet.Cells(p1+2,2).Value=Doses[p2][p1][1]
def readingFromExcel(self, _lib_path):
""" Context Manager for handling open / close / cleanup for Excel App"""
#
#Ref: https://stackoverflow.com/questions/158706/how-do-i-properly-clean-up-excel-interop-objects
#Ref: https://devblogs.microsoft.com/visualstudio/marshal-releasecomobject-considered-dangerous/
#
# Appears that we can / should leave the Marshal.ReleaseComObect() off?
# Just using gc.collect() seems to catch all but the first instance, so
# at least they don't build up past the first. Still don't know why
# I can't kill that first instance though...
#
try:
#-----------------------------------------------------------
# Make a Temporary copy
self.saveDir = os.path.split(_lib_path)[0]
self.tempFile = '{}_temp.xlsx'.format(random.randint(0, 1000))
self.tempFilePath = os.path.join(self.saveDir, self.tempFile)
copyfile(_lib_path, self.tempFilePath)
#-----------------------------------------------------------
# Read from the Excel file
self.ex = Excel.ApplicationClass()
self.ex.Visible = False # False means excel is hidden as it works
self.ex.DisplayAlerts = False
self.workbook = self.ex.Workbooks.Open(self.tempFilePath)
self.worksheets = self.workbook.Worksheets
yield
except:
self.workbook.Close() # Close the worbook itself
self.ex.Quit() # Close out the instance of Excel
self.ex = None
gc.collect()
os.remove(self.tempFilePath) # Remove the temporary read-file
finally:
self.workbook.Close() # Close the worbook itself
self.ex.Quit() # Close out the instance of Excel
self.ex = None
gc.collect()
os.remove(self.tempFilePath) # Remove the temporary read-file
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 exporttoexcel(Doses, Names, title, p):
#Getting Excel Started
excel = Excel.ApplicationClass()
excel.Visible = True
excel.DisplayAlerts = False
# creating a new one
workbook = excel.Workbooks.Add()
for p2 in range(len(Names)):
# adding a worksheet,每一个sheet代表一个射野
worksheet = workbook.Worksheets.Add()
worksheet.Name = Names[p2]
#excel坐标从1开始算，而range从0开始
#写入表头信息
for p1 in range(len(Doses[p2])):
worksheet.Cells(1, 1 + p1 * 3).Value = title[p1]
worksheet.Cells(1, 2 + p1 * 3).Value = 'Dose(cGy)'
for p3 in range(len(Doses[p2][p1])):
worksheet.Cells(p3 + 2, 1 + p1 * 3).Value = p[p3]
worksheet.Cells(p3 + 2, 2 + p1 * 3).Value = round(
Doses[p2][p1][p3], 5)
def GetRulesOfChecks():
"""Load rule of checks from excel
Returns:
[type]: [description]
"""
PATH = "U:\__tmp\Аудит LOD_LOI.xlsx"
# fd = OpenFileDialog()
# fd.InitialDirectory = PATH
# fd.Filter = "All files (*.*)| *.*"
# fd.FilterIndex = 2
# fd.RestoreDirectory = False
# if fd.ShowDialog() == DialogResult.OK:
# file = fd.FileName
# else:
# raise SystemExit
ex = Excel.ApplicationClass()
book = ex.Workbooks.Open(PATH, ReadOnly=True)
sheet = book.Sheets[1]
Array = sheet.UsedRange.Value2
ex.Quit()
List = []
for i in range(Array.GetLength(0)):
List.append([])
for j in range(Array.GetLength(1)):
List[i].append(Array[i, j])
RulesList = []
return createListOfRules(List)