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)