Bumblebee Export Diff
建立於 差異永不過期
# Copyright(c) 2016, David Mans, Konrad Sobon
# Copyright(c) 2016, David Mans, Konrad Sobon
# @arch_laboratory, http://archi-lab.net, http://neoarchaic.net
# @arch_laboratory, http://archi-lab.net, http://neoarchaic.net
import clr
import clr
import sys
import sys
import System
import System
from System import Array
from System import Array
from System.Collections.Generic import *
from System.Collections.Generic import *
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from Microsoft.Office.Interop import Excel
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo("en-US")
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo("en-US")
from System.Runtime.InteropServices import Marshal
from System.Runtime.InteropServices import Marshal
pyt_path = r'C:\Program Files (x86)\IronPython 2.7\Lib'
pyt_path = r'C:\Program Files (x86)\IronPython 2.7\Lib'
sys.path.append(pyt_path)
sys.path.append(pyt_path)
from os import path
from os import path
assemblies = System.AppDomain.CurrentDomain.GetAssemblies()
assemblies = System.AppDomain.CurrentDomain.GetAssemblies()
path1 = [a.Location for a in assemblies if 'bumblebee,' in a.FullName][0]
path1 = [a.Location for a in assemblies if 'bumblebee,' in a.FullName][0]
path2 = System.IO.Path.GetDirectoryName(path1).rsplit('\\',1)[0]
path2 = System.IO.Path.GetDirectoryName(path1).rsplit('\\',1)[0]
bb_path = '%s\\extra\\' %path2
bb_path = '%s\\extra\\' %path2
sys.path.append(bb_path)
sys.path.append(bb_path)
import bumblebee as bb
import bumblebee as bb
import os
import os
#The inputs to this node will be stored as a list in the IN variable.
#The inputs to this node will be stored as a list in the IN variable.
dataEnteringNode = IN
dataEnteringNode = IN
filePath = IN[0]
filePath = IN[0]
runMe = IN[1]
runMe = IN[1]
byColumn = IN[2]
byColumn = IN[2]
data = IN[3]
data = IN[3]
if filePath != None:
if filePath != None:
if filePath.GetType() == System.IO.FileInfo:
if filePath.GetType() == System.IO.FileInfo:
filePath = filePath.FullName
filePath = filePath.FullName
def LiveStream():
def LiveStream():
try:
try:
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp.Visible = True
xlApp.Visible = True
xlApp.DisplayAlerts = False
xlApp.DisplayAlerts = False
return xlApp
return xlApp
except:
except:
return None
return None
def SetUp(xlApp):
def SetUp(xlApp):
# supress updates and warning pop ups
# supress updates and warning pop ups
xlApp.Visible = False
xlApp.Visible = False
xlApp.DisplayAlerts = False
xlApp.DisplayAlerts = False
xlApp.ScreenUpdating = False
xlApp.ScreenUpdating = False
return xlApp
return xlApp
def WriteData(ws, data, byColumn, origin):
def WriteData(ws, data, byColumn, origin):
def FillData(x, y, x1, y1, ws, data, origin):
def FillData(x, y, x1, y1, ws, data, origin):
if origin != None:
if origin != None:
x = x + origin[1]
x = x + origin[1]
y = y + origin[0]
y = y + origin[0]
else:
else:
x = x + 1
x = x + 1
y = y + 1
y = y + 1
if y1 != None:
if y1 != None:
ws.Cells[x, y] = data[x1][y1]
ws.Cells[x, y] = data[x1][y1]
else:
else:
ws.Cells[x, y] = data[x1]
ws.Cells[x, y] = data[x1]
ws.Columns.AutoFit()
return ws
return ws
# if data is a nested list (multi column/row) use this
# if data is a nested list (multi column/row) use this
if any(isinstance(item, list) for item in data):
if any(isinstance(item, list) for item in data):
for i, valueX in enumerate(data):
for i, valueX in enumerate(data):
for j, valueY in enumerate(valueX):
for j, valueY in enumerate(valueX):
if byColumn:
if byColumn:
FillData(j,i,i,j, ws, data, origin)
FillData(j,i,i,j, ws, data, origin)
else:
else:
FillData(i,j,i,j, ws, data, origin)
FillData(i,j,i,j, ws, data, origin)
# if data is just a flat list (single column/row) use this
# if data is just a flat list (single column/row) use this
else:
else:
for i, valueX in enumerate(data):
for i, valueX in enumerate(data):
if byColumn:
if byColumn:
FillData(i,0,i,None, ws, data, origin)
FillData(i,0,i,None, ws, data, origin)
else:
else:
FillData(0,i,i,None, ws, data, origin)
FillData(0,i,i,None, ws, data, origin)
return ws
return ws
def ExitExcel(filePath, xlApp, wb, ws):
def ExitExcel(filePath, xlApp, wb, ws):
# clean up before exiting excel, if any COM object remains
# clean up before exiting excel, if any COM object remains
# unreleased then excel crashes on open following time
# unreleased then excel crashes on open following time
def CleanUp(_list):
def CleanUp(_list):
if isinstance(_list, list):
if isinstance(_list, list):
for i in _list:
for i in _list:
Marshal.ReleaseComObject(i)
Marshal.ReleaseComObject(i)
else:
else:
Marshal.ReleaseComObject(_list)
Marshal.ReleaseComObject(_list)
return None
return None
wb.SaveAs(unicode(filePath))
wb.SaveAs(unicode(filePath))
xlApp.ActiveWorkbook.Close(False)
xlApp.ActiveWorkbook.Close(False)
xlApp.ScreenUpdating = True
xlApp.ScreenUpdating = True
CleanUp([ws,wb,xlApp])
CleanUp([ws,wb,xlApp])
return None
return None
def Flatten(*args):
def Flatten(*args):
for x in args:
for x in args:
if hasattr(x, '__iter__'):
if hasattr(x, '__iter__'):
for y in Flatten(*x):
for y in Flatten(*x):
yield y
yield y
else:
else:
yield x
yield x
def WorksheetExists(wb, name):
def WorksheetExists(wb, name):
for i in wb.Sheets:
for i in wb.Sheets:
if i.Name == name:
if i.Name == name:
return True
return True
break
break
else:
else:
continue
continue
return False
return False
if isinstance(data, list):
if isinstance(data, list):
if any(isinstance(x, list) for x in data):
if any(isinstance(x, list) for x in data):
data = list(Flatten(data))
data = list(Flatten(data))
live = False
live = False
if runMe:
if runMe:
try:
try:
errorReport = None
errorReport = None
if filePath == None:
if filePath == None:
# run excel in live mode
# run excel in live mode
xlApp = LiveStream()
xlApp = LiveStream()
live = True
live = True
wb = xlApp.ActiveWorkbook
wb = xlApp.ActiveWorkbook
else:
else:
# run excel from a file on disk
# run excel from a file on disk
xlApp = SetUp(Excel.ApplicationClass())
xlApp = SetUp(Excel.ApplicationClass())
live = False
live = False
# if file exists open it
# if file exists open it
if os.path.isfile(unicode(filePath)):
if os.path.isfile(unicode(filePath)):
xlApp.Workbooks.open(unicode(filePath))
xlApp.Workbooks.open(unicode(filePath))
wb = xlApp.ActiveWorkbook
wb = xlApp.ActiveWorkbook
# if file doesn't exist just make a new one
# if file doesn't exist just make a new one
else:
else:
wb = xlApp.Workbooks.Add()
wb = xlApp.Workbooks.Add()
if not isinstance(data, list):
if not isinstance(data, list):
# add and rename worksheet
# add and rename worksheet
ws = wb.Worksheets[1]
ws = wb.Worksheets[1]
ws.Name = data.SheetName()
ws.Name = data.SheetName()
else:
else:
for i in data:
for i in data:
# if worksheet doesn't exist add it and name it
# if worksheet doesn't exist add it and name it
if not WorksheetExists(wb, i.SheetName()):
if not WorksheetExists(wb, i.SheetName()):
wb.Sheets.Add(After = wb.Sheets(wb.Sheets.Count), Count = 1)
wb.Sheets.Add(After = wb.Sheets(wb.Sheets.Count), Count = 1)
ws = wb.Worksheets[wb.Sheets.Count]
ws = wb.Worksheets[wb.Sheets.Count]
ws.Name = i.SheetName()
ws.Name = i.SheetName()
# data is a flat list - single sheet gets written
# data is a flat list - single sheet gets written
if not isinstance(data, list):
if not isinstance(data, list):
if WorksheetExists(wb, data.SheetName()):
if WorksheetExists(wb, data.SheetName()):
ws = xlApp.Sheets(data.SheetName())
ws = xlApp.Sheets(data.SheetName())
else:
else:
wb.Sheets.Add(After = wb.Sheets(wb.Sheets.Count), Count = 1)
wb.Sheets.Add(After = wb.Sheets(wb.Sheets.Count), Count = 1)
ws = wb.Worksheets[wb.Sheets.Count]
ws = wb.Worksheets[wb.Sheets.Count]
ws.Name = data.SheetName()
ws.Name = data.SheetName()
WriteData(ws, data.Data(), byColumn, data.Origin())
WriteData(ws, data.Data(), byColumn, data.Origin())
if not live:
if not live:
ExitExcel(filePath, xlApp, wb, ws)
ExitExcel(filePath, xlApp, wb, ws)
# data is a nested list - multiple sheets are written
# data is a nested list - multiple sheets are written
else:
else:
sheetNameSet = set([x.SheetName() for x in data])
sheetNameSet = set([x.SheetName() for x in data])
for i in data:
for i in data:
if WorksheetExists(wb, i.SheetName()):
if WorksheetExists(wb, i.SheetName()):
ws = xlApp.Sheets(i.SheetName())
ws = xlApp.Sheets(i.SheetName())
else:
else:
wb.Sheets.Add(After = wb.Sheets(wb.Sheets.Count), Count = 1)
wb.Sheets.Add(After = wb.Sheets(wb.Sheets.Count), Count = 1)
ws = wb.Worksheets[wb.Sheets.Count]
ws = wb.Worksheets[wb.Sheets.Count]
ws.Name = i.SheetName()
ws.Name = i.SheetName()
WriteData(ws, i.Data(), byColumn, i.Origin())
WriteData(ws, i.Data(), byColumn, i.Origin())
if not live:
if not live:
ExitExcel(filePath, xlApp, wb, ws)
ExitExcel(filePath, xlApp, wb, ws)
except:
except:
xlApp.Quit()
xlApp.Quit()
Marshal.ReleaseComObject(xlApp)
Marshal.ReleaseComObject(xlApp)
# if error accurs anywhere in the process catch it
# if error accurs anywhere in the process catch it
import traceback
import traceback
errorReport = traceback.format_exc()
errorReport = traceback.format_exc()
pass
pass
else:
else:
errorReport = "Set RunMe to True."
errorReport = "Set RunMe to True."
#Assign your output to the OUT variable
#Assign your output to the OUT variable
if errorReport == None:
if errorReport == None:
OUT = "Success!"
OUT = "Success!"
else:
else:
OUT = errorReport
OUT = errorReport