Hello,
I’ve made a module that should create an excel file based on a template file.
I get the following error:
My guess is that BB Data doesn’t deliver the appropriate format for the Data node in the “new file by template” node.
tekeningenlijst2.dyn (101.0 KB)
Could somebody help me with this?
Can you connect the data from after List.Transpose to Data? Why did you use there BB Data?
Yes I can connect the data from after list.transpose. The problem is that I need an offset for my data in the excel sheet. It should write the data on row 8 and below that row.
New file by template doesn’t have an origing node.
I want to create an excel file from a template and fill it with specific data on a certain range.
You should edit node… what you looking for is here:
for i in range(0, len(data), 1):
xlApp.Workbooks.Open(unicode(tempFilePath))
wb = xlApp.ActiveWorkbook
ws = xlApp.Sheets(sheetName)
You can replace 0 with 8 or edit it and define a new parameter like offset.
I’ve tried that, but it’s not really working. I’m not familiar with Python and I’m actually looking for a way without altering the nodes
If it’s just not possible with “new file by template” , then I will look for an alternative way.
If you add a new input “offset” and then use it, it should work;
# Copyright(c) 2016, David Mans, Konrad Sobon
# @arch_laboratory, http://archi-lab.net, http://neoarchaic.net
import clr
import sys
import System
from System import Array
from System.Collections.Generic import *
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo("en-US")
from System.Runtime.InteropServices import Marshal
pyt_path = r'C:\Program Files (x86)\IronPython 2.7\Lib'
sys.path.append(pyt_path)
assemblies = System.AppDomain.CurrentDomain.GetAssemblies()
path1 = [a.Location for a in assemblies if 'bumblebee,' in a.FullName][0]
path2 = System.IO.Path.GetDirectoryName(path1).rsplit('\\',1)[0]
bb_path = '%s\\extra\\' %path2
sys.path.append(bb_path)
import bumblebee as bb
#The inputs to this node will be stored as a list in the IN variable.
dataEnteringNode = IN
tempFilePath = IN[0]
newFilePath = IN[1]
newFileName = IN[2]
data = IN[3]
sheetName = IN[4]
tempSheetName = IN[5]
RunIt = IN[6]
offset = IN[7]
if offset == None:
offset = 0
else:
offset= int(offset)
def SetUp(xlApp):
# supress updates and warning pop ups
xlApp.Visible = False
xlApp.DisplayAlerts = False
xlApp.ScreenUpdating = False
return xlApp
if RunIt:
message = None
try:
errorReport = None
message = "Success!"
xlApp = Excel.ApplicationClass()
SetUp(xlApp)
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(offset, 1), ws.Cells(offset-1+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:
OUT = OUT = '\n'.join('{:^35}'.format(s) for s in message.split('\n'))
else:
OUT = errorReport
Deniz_Maral:
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(offset, 1), ws.Cells(offset-1+len(data[i]), 1)) rng.Value = xlApp.Transpose(Array[str](data[i]))
I edited that part of code.