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 getComAppObject(ComAppName):
# AppObj = win32com.client.Dispatch(ComAppName)
AppObj = Marshal.GetActiveObject(ComAppName)
AppObj = AppObj.Application
#AppObj.Visible = True
# AppObj=Outlook.ApplicationClass()
return AppObj
def 导出到CSV文件(self, srcfilename, tgrfilename, srcsheetnames, rootpath=""):
xlCSV = 6
xlCSVWindows = 23
#self.curApp = win32com.client.GetActiveObject("excel.application")
self.curApp = ApplicationClass()
self.curApp = Marshal.GetActiveObject("Excel.Application")
self.curApp.Application.DisplayAlerts = False
self.curApp.Application.Workbooks.Open(srcfilename)
self.curbook = self.curApp.Application.ActiveWorkbook
# dataPath = self.curbook.path
# print( dataPath)
if rootpath == "":
dataPath = self.curbook.path
else:
dataPath = rootpath
for sheetname in srcsheetnames:
try:
fileName = os.path.join(dataPath,
tgrfilename + sheetname + ".csv")
self.curSheet = self.curbook.Sheets(sheetname).Activate()
self.curbook.SaveAs(fileName, xlCSV, False)
print("Save to file Ok:", fileName)
except:
print("----Save to CVS Error.", sheetname, fileName)
# self.curbook.Close()
pass
print("导出表页,success.:", end="")
print(sheetname, " ,in file ", srcfilename)
self.curbook.Close()
def findExistingInstance(self):
"""Find an Excel instance that is already running
Returns True if an already running instance was found, False otherwise"""
#try:
self.userOpened=True
self.activeWorkbookName=="NEW_USER_FILE"
self.ex=Marshal.GetActiveObject("Excel.Application")
print(self.ex)
if self.ex==None:
return None
print(self.ex.Workbooks)
print(dir(self.ex.Workbooks))
print(self.ex.Workbooks.Count)
if(self.ex.Workbooks.Count>0):
for workbook in self.ex.Workbooks:
self.activeWorkbook=workbook
break
#there is definitely a better way of doing this
else:
return None
print(self.activeWorkbook==None)
if self.activeWorkbook==None:
return None
self.userOpened=True
self.activeWorkbookName="NEW_USER_FILE"
self.loadSheets()
return True
def LiveStream():
try:
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
return xlApp
except:
return None
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 清理CSV文件(self):
xlCSV = 6
self.curApp = ApplicationClass()
self.curApp = Marshal.GetActiveObject("Excel.Application")
# self.curApp = win32com.client.GetActiveObject("excel.application")
self.curApp.Application.DisplayAlerts = False
self.books = self.curApp.Application.Workbooks
for book in self.books:
filename = book.Name
extension = filename[-4:]
extension = extension.upper()
if extension == ".CSV":
pass
book.Close()
def 合并Xls(self, fileName):
xlCSV = 6
xlOpenXMLWorkbook = 51
xlExcel12 = 50
self.curApp = ApplicationClass()
self.curApp = Marshal.GetActiveObject("Excel.Application")
self.curApp.Application.DisplayAlerts = False
self.books = self.curApp.Application.Workbooks
newbooks = self.books.Add()
newbooks.SaveAs(fileName, xlExcel12)
AfterSheet = newbooks.Sheets(1)
for book in self.books:
filename = book.Name
extension = filename[-4:]
extension = extension.upper()
if extension == ".CSV":
pass
book.Sheets(1).Move(AfterSheet)
newbooks.Save()
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
def 合并2Xls(self, xlsfileName, srcFilenames=[]):
xlCSV = 6
xlOpenXMLWorkbook = 51
xlExcel12 = 50
self.curApp = ApplicationClass()
self.curApp = Marshal.GetActiveObject("Excel.Application")
self.curApp.Application.DisplayAlerts = False
self.books = self.curApp.Application.Workbooks
newbooks = self.books.Add()
newbooks.SaveAs(xlsfileName, xlExcel12)
AfterSheet = newbooks.Sheets(1)
for srcfile in srcFilenames:
try:
os.startfile(srcfile)
except:
pass
book = self.curApp.Application.ActiveWorkbook
rootpath, filename = os.path.split(srcfile)
if filename == book.Name:
book.Sheets(1).Move(AfterSheet)
newbooks.Save()
for row in csv_reader:
# decode UTF-8 back to Unicode, cell by cell:
yield [unicode(cell, 'utf-8') for cell in row]
def utf_8_encoder(unicode_csv_data):
for line in unicode_csv_data:
yield line.encode('utf-8')
title = doc.Title[:-4] + "_Schedules.xlsx"
fullpathsched = path + "\\" + title
try:
exapp = Marshal.GetActiveObject(
'Excel.Application') # throws error, if no exapp is found
# iterating over them
workbooks = [wb for wb in exapp.Workbooks if wb.Name == fullpathsched]
wb = workbooks[0]
print wb
#if not wb: raise Exception # True if wb is None
except:
# marshal = Marshal.ReleaseComObject(exapp)
print " Exception opening excelfile"
exapp = Excel.ApplicationClass()
openexcel = True
exapp.Visible = openexcel
exapp.DisplayAlerts = False
wd.Initialize('Sent CZI to MATLAB', 470, 200, True, True)
## add components to dialog
wd.AddLabel('Sends selected CZI image to MATLAB.', '0', '0')
wd.AddLabel('Uses MATLAB wrapper (bfopen) for BioFormats.', '1', '0')
wd.AddDropDown('czi', 'Select CZI Image Data', CZIfiles_short, 0, '2', '0')
## show the window
result = wd.Show()
## check, if Cancel button was clicked
if result.HasCanceled == True:
sys.exit('Macro aborted with Cancel!')
## get the input values and store them
cziname = result.GetValue('czi')
try:
MATLAB = Marshal.GetActiveObject('MATLAB.Application.9.0')
print 'ZEN-MATLAB bridge is OK.'
MLOK = True
except:
print 'MATLAB not running'
if MLOK == True:
## get current active document
CZIfolder = os.path.dirname(CZIdict[cziname])
print 'Transfer: ', CZIdict[cziname]
## create MATLAB variables
MATLAB.execute("filename = '" + CZIdict[cziname] + "'")
MATLAB.execute("CZIimage = ReadImage6D(filename);")
print 'Done'
#print "Horizontal:" + pipe.Name + " Z-offset: " + str(round(abs(endPoint.Z - startPoint.Z),2)) + " Pipe Offset from Level: " + str(pipeOffsetFromReferenceLevel ) + "mm - " + str(pipeLevelDescription) +" mm - Ref Level: " + str(pipeRefLevelElevation) +" mm - "
MF_SetParameterByName(pipe, "MF_Offset_Description",
pipeLevelDescription)
t.Commit()
for level in levels:
print level.Name + " ---- Elevation: " + str(level.ProjectElevation)
OUT = pipes, curves, linePoints, verticalPipes
excel = Excel.ApplicationClass()
from System.Runtime.InteropServices import Marshal
excel = Marshal.GetActiveObject("Excel.Application")
excel.Visible = True
excel.DisplayAlerts = False
filename = 'C:\Users\e.green\Desktop\SheetListDataExport.xlsx'
#Workbooks
# creating a new one
workbook = excel.Workbooks.Add()
# opening a workbook
#workbook = excel.Workbooks.Open(filename)
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo(
"en-US")
import clr
clr.AddReference('Microsoft.Office.Interop.Outlook')
import Microsoft.Office.Interop.Outlook as Outlook
#olApp = Outlook.ApplicationClass()
from System.Runtime.InteropServices import Marshal
#outlook = Marshal.GetActiveObject("Outlook.Application")
#mapi = outlook.GetNamespace("MAPI")
#print "Current User: " + mapi.CurrentUser.Name
newemail = Marshal.GetActiveObject('Outlook.Application').CreateItem(0)
newemail.Recipients.Add('*****@*****.**')
newemail.Subject = 'This is a test'
newemail.Body = 'This is a test email\nIt should have some text in the body'
newemail.Send()
import clr
clr.AddReference("Microsoft.Office.Interop.Outlook")
from System.Runtime.InteropServices import Marshal
mail= Marshal.GetActiveObject("Outlook.Application").CreateItem(0)
mail.Recipients.Add("*****@*****.**")
mail.Subject = "Subject here"
mail.Body = "Body here"
mail.Send();
def MF_ReadFromExcel(infilename, sheet):
excel = Excel.ApplicationClass()
from System.Runtime.InteropServices import Marshal
excel = Marshal.GetActiveObject("Excel.Application")
excel.Visible = True
excel.DisplayAlerts = False
###################################
desktop = os.path.join(os.path.join(os.environ['USERPROFILE']), 'Desktop')
filename = desktop + "\\" + infilename
# finding a workbook that's already open
workbooks = [wb for wb in excel.Workbooks if wb.FullName == filename]
if workbooks:
workbook = workbooks[0]
else:
#Workbooks
#if workbook exists, try to open it
try:
workbook = excel.Workbooks.Open(infilename)
except Exception as e:
print "Error opening workbook: " + str(e)
# choose sheet
if sheet:
try:
ws = workbook.Sheets.Item[sheet]
except Exception as e:
print "Error opening sheet: " + str(e)
else:
print "Sheets in Workbook: " + workbook.Sheets.Count
######################################################
#ws.Activate
lastRow = 5
rowCount = ws.UsedRange.Rows.Count
columnCount = ws.UsedRange.Columns.Count
lastRow = rowCount
lastColumn = columnCount
print "Rowcount: " + str(rowCount)
importData = []
i = 1
while i <= lastRow:
print "Reading Data: " + str(100 * i / rowCount) + " % complete"
importRow = []
j = 1
while j <= lastColumn:
importRow.append(ws.Cells(i, j).Text)
j += 1
importData.append(importRow)
i += 1
return importData
def MF_OpenExcelAndRead(infilename, sheet=None, rowLimit=None):
excel = Excel.ApplicationClass()
from System.Runtime.InteropServices import Marshal
excel = Marshal.GetActiveObject("Excel.Application")
excel.Visible = True
excel.DisplayAlerts = False
###################################
#desktop = os.path.join(os.path.join(os.environ['USERPROFILE']), 'Desktop')
filename = infilename
# finding a workbook that's already open
workbooks = [wb for wb in excel.Workbooks if wb.FullName == filename]
if workbooks:
workbook = workbooks[0]
else:
#Workbooks
#if workbook exists, try to open it
try:
workbook = excel.Workbooks.Open(infilename)
except Exception as e:
print "Error opening workbook: " + str(e)
# choose sheet
if sheet:
try:
ws = workbook.Sheets.Item[sheet]
except Exception as e:
print "Error opening sheet: " + str(e)
else:
# choose sheet
sheetOptions = [s.Name for s in workbook.Sheets]
options = sheetOptions
selected = forms.SelectFromList.show(
options,
title='Choose Sheet to Import From',
width=800,
height=800,
multiselect=False)
#print "Sheets in Workbook: " + str(workbook.Sheets(1).Name)
sheet = selected[0]
try:
ws = workbook.Sheets.Item[sheet]
except Exception as e:
print "Error opening sheet: " + str(e)
######################################################
#ws.Activate
lastRow = 5
rowCount = ws.UsedRange.Rows.Count
columnCount = ws.UsedRange.Columns.Count
lastRow = rowCount
lastColumn = columnCount
if rowLimit and rowLimit > 0:
lastRow = rowLimit
print "Rowcount: " + str(rowCount)
importData = []
i = 1
while i <= lastRow:
print "Reading Data: " + str(100 * i / rowCount) + " % complete"
importRow = []
j = 1
while j <= lastColumn:
importRow.append(ws.Cells(i, j).Text)
j += 1
importData.append(importRow)
i += 1
return importData
def MF_WriteToExcel(outfilename, sheet, data):
excel = Excel.ApplicationClass()
from System.Runtime.InteropServices import Marshal
excel = Marshal.GetActiveObject("Excel.Application")
excel.Visible = True
excel.DisplayAlerts = False
###################################
desktop = os.path.join(os.path.join(os.environ['USERPROFILE']), 'Desktop')
filename = desktop + "\\" + outfilename
# finding a workbook that's already open
workbooks = [wb for wb in excel.Workbooks if wb.FullName == filename]
if workbooks:
workbook = workbooks[0]
else:
#Workbooks
#if workbook exists, try to open it
try:
workbook = excel.Workbooks.Open(filename)
except:
# if not, create a new one
workbook = excel.Workbooks.Add()
#save it with the desired name
workbook.SaveAs(filename)
# oopen it
workbook = excel.Workbooks.Open(filename)
try:
ws = workbook.Sheets.Item[sheet]
except:
ws = workbook.Worksheets.Add()
ws.Name = sheet
######################################################
exportData = data
lastRow = len(exportData)
totalColumns = len(max(exportData, key=len))
lastColumn = totalColumns
lastColumnName = ColIdxToXlName(totalColumns)
xlrange = ws.Range["A1", lastColumnName + str(lastRow)]
a = Array.CreateInstance(object, len(exportData), totalColumns)
i = 0
while i < lastRow:
j = 0
while j < totalColumns:
a[i, j] = exportData[i][j]
j += 1
i += 1
xlrange.Value2 = a
ws.Range(ws.Cells(1, 1), ws.Cells(1, lastColumn)).Font.Bold = True
ws.Range(ws.Cells(1, 2), ws.Cells(lastRow, lastColumn)).Columns.AutoFit()
ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn)).AutoFilter()